3.在2的基础上修改DBUtil.java,新建DBDataSource.java和db.properties属性文件
项目结构：
修改后的DBUtil.java
package com.hpe.util;
import java.beans.BeanInfo;
import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class DBUtil {
/*
* 获取数据库连接
*/
// public static Connection getConn() throws ClassNotFoundException, SQLException{
// Class.forName("com.mysql.jdbc.Driver");
//
// String url = "jdbc:mysql://localhost:3306/work?useUnicode=true&characterEncoding=utf-8";
// String username="root";
// String password="root";
// Connection conn=DriverManager.getConnection(url, username, password);
// return conn;
// }
/*
* 关闭数据库资源
*/
public static void close(ResultSet rs,PreparedStatement ps,Connection conn){
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/*
* Class<T> type创建什么类型的对象
* T用来保存一个参数类型
*/
public static <T> List<T> select(String sql,Object[] params,Class<T> type){
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
List<T>list =new ArrayList<>();
try {
conn=DBDataSource.getConn();
ps=conn.prepareStatement(sql);
if(params != null){
for(int i=0;i<params.length;i++){
ps.setObject(i+1, params[i]);
}
}
rs = ps.executeQuery();
ResultSetMetaData metaData = rs.getMetaData();//
int colCount = metaData.getColumnCount();//获取列的数量
while(rs.next()){
/*
* 把数据库查询出来的通过内省丢给Object
*/
T obj = type.newInstance();
BeanInfo beanInfo = Introspector.getBeanInfo(type);//obj的属性
PropertyDescriptor[] properties = beanInfo.getPropertyDescriptors();//属性信息的数组
for (int i = 0; i < colCount; i++) {
String cloName = metaData.getColumnName(i+1); //获取到列名
Object value = rs.getObject(i+1);//列的值
/*
* 遍历properties数组，找到一个名字和cloName一样的属性
*/
for(int j=0;j<properties.length;j++){
PropertyDescriptor property = properties[j];
String propertyName = property.getName();//属性名字
Method method = property.getWriteMethod();//写方法
if(propertyName.equals(cloName)){
//属性名与数据表的列名一样，将值传给obj
method.invoke(obj, value);
}
}
}
list.add(obj);
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InstantiationException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalAccessException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IntrospectionException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IllegalArgumentException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (InvocationTargetException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
close(rs, ps, conn);
}
return list;
}
public static <T> T selectObject(String sql,Object[] params,Class<T> type){
List<T> list = select(sql, params,type);
T resObj = null;
if(list.size()>0){
resObj=list.get(0);
}
return resObj;
}
/*
* UPdate方法, 支持增,删,改
*/
public static int update(String sql, Object[] params){
Connection conn = null;
PreparedStatement ps = null;
int res = 0;
try {
conn = DBDataSource.getConn();
ps = conn.prepareStatement(sql);
if (params != null){
for (int i=0; i<params.length; i++){
ps.setObject(i+1, params[i]);
}
}
res = ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
close(null, ps, conn);
}
return res;
}
}
数据源DBDataSource.java
package com.hpe.util;
import java.beans.PropertyVetoException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;
import com.mchange.v2.c3p0.ComboPooledDataSource;
/*
* 提供数据库连接对象，使用C3P0
*/
public class DBDataSource {
public static String driver;//驱动
public static String url;//数据库地址
public static String username;//数据库帐号
public static String password;//数据库密码
public static int maxPoolSize;//最大连接池数
public static int minPoolSize;//最小连接池数
public static int initialPoolSize;//初始化连接池数
public static int checkoutTimeout;//超时
private static ComboPooledDataSource dataSource =null;//数据源
static{
try{
Properties properties = new Properties();//加载后缀是properties的属性文件
InputStream inputStream = DBDataSource.class.getClassLoader().getResourceAsStream("db.properties");
properties.load(inputStream);
driver = properties.getProperty("drivername");
url = properties.getProperty("url");
username = properties.getProperty("username");
password = properties.getProperty("password");
maxPoolSize =Integer.parseInt(properties.getProperty("maxPoolSize"));
minPoolSize =Integer.parseInt(properties.getProperty("minPoolSize"));
initialPoolSize =Integer.parseInt(properties.getProperty("initialPoolSize"));
checkoutTimeout =Integer.parseInt(properties.getProperty("checkoutTimeout"));
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**************** c3p0 数据库连接池 启动方法******************/
private static void initDataSource() {
dataSource = new ComboPooledDataSource();
try {
dataSource.setDriverClass(driver);
dataSource.setJdbcUrl(url);
dataSource.setUser(username);
dataSource.setPassword(password);
dataSource.setMaxPoolSize(maxPoolSize);
dataSource.setMinPoolSize(minPoolSize);
dataSource.setInitialPoolSize(initialPoolSize);
dataSource.setCheckoutTimeout(checkoutTimeout);
} catch (PropertyVetoException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* c3p0数据库连接入
* @return
* @throws Exception
*/
public static Connection getConn() throws SQLException{
if(dataSource == null){
initDataSource();
}
Connection conn = dataSource.getConnection();
return conn;
}
}
############
drivername=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/work?useUnicode=true&characterEncoding=utf-8
username=root
password=root
##########//c3p0
maxPoolSize=200
minPoolSize=5
initialPoolSize=10
checkoutTimeout=300000