c3p0-Dbutils/db.properties

---

连接池连接

I.引入jar包

• mysql-connector-java-5.1.0-bin.jar
• commons-dbutils-1.3.jar
• c3p0-0.9.1.2.jar

II.创建类
c3p0-config.xml配置文件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE xml>
<c3p0-config>
  <default-config name="c3p0">
    <property name="dirverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl">jdbc:mysql://localhost:3306/bookstore_0917?useUnicode=true&amp;characterEncoding=utf-8</property>
    <property name="user">root</property>
    <property name="password">abc123</property>
     <!-- 初始化连接池中的连接数，取值应在minPoolSize与maxPoolSize之间，默认为3-->
    <property name="initialPoolSize">10</property>
     <!--最大空闲时间，60秒内未使用则连接被丢弃。若为0则永不丢弃。默认值: 0 -->
    <property name="maxIdleTime">30</property>
     <!--连接池中保留的最大连接数。默认值: 15 -->
    <property name="maxPoolSize">100</property>
    <!-- 连接池中保留的最小连接数，默认为：3--> 
    <property name="minPoolSize">10</property>
  </default-config> 
</c3p0-config>

jdbcUtils类：

package cn.kgc.util;

import java.sql.Connection;
import java.sql.SQLException;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

/**
 * 通用连接方法
 * @author Administrator
 *
 */
public class jdbcUtils {
	//注意这里一定要写成单例模式，不然每次都会创建一个连接池，连接池满了，就会出异常。
	private static DataSource dataSource = new ComboPooledDataSource("c3p0");
	
	public static Connection getConnection(){
		Connection connection=null;
		try {
			connection = dataSource.getConnection();
			System.out.println(connection);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connection;
	} 
	/**
	 * 通用的关闭方法
	 * @param connection
	 */
	public static void close(Connection connection){
		if(connection!=null){
			try {
				connection.close();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	
}

BaseDao类：

package cn.kgc.Dao;

import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import cn.kgc.util.jdbcUtils;

public class BaseDao<T> {
	QueryRunner qr= new QueryRunner();
	Class<T> type;
	public BaseDao(){
		//获取泛型父类
		ParameterizedType pt = (ParameterizedType) this.getClass().getGenericSuperclass();
		//获取泛型，返回数组
		Type[] types = pt.getActualTypeArguments();
		//把第一个值赋值给type
		this.type=(Class<T>) types[0];
	}
	/**
	 * 通用的增删改
	 */
	public int update(String sql,Object...params){
		int count=0;
		Connection connection = jdbcUtils.getConnection();
		try {
			count = qr.update(connection,sql,params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			jdbcUtils.close(connection);
		}
		return count;
	}
	/**
	 * 通用的查询一张表中一个值的方法
	 */
	public T getBean(String sql,Object...params){
		T t=null;
		Connection connection = jdbcUtils.getConnection();
		try {
			t = qr.query(connection, sql, new BeanHandler<T>(type),params);
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			jdbcUtils.close(connection);
		}
		
		return t;
	}
	/**
	 * 通用的查询一张表中多个值的方法
	 */
	public List<T> getListBean(String sql,Object...params){
		List<T> list=null;
		Connection connection = jdbcUtils.getConnection();
		try {
			list = qr.query(connection, sql, new BeanListHandler<T>(type),params);
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			jdbcUtils.close(connection);
		}
		return list;
	}
	/**
	 * 多表查询
	 * @param sql
	 * @param params
	 * @return
	 */
	public List getMoreBeanList(String sql,Object...params){
		Connection conn=null;
		conn = jdbcUtils.getConn();
		List<Map<String,Object>> list=null;
		try {
			list = qr.query(conn, sql, new MapListHandler(), params);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			jdbcUtils.close(conn);
		}
		return list;
	}
	/**
	 * 通用查询聚合函数一个值的
	 */
	public Object getScalar(String sql,Object...params){
		Connection conn=null;
		conn = jdbcUtils.getConn();
		Object query=null;
		try {
			query = qr.query(conn, sql, new ScalarHandler(), params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			jdbcUtils.close(conn);
		}
		return query;
	}
	/**
	 * 事物批量修改和删除的方法
	 */	
	public boolean bactchUpdate(String sql,Object[][] param){			
		int[] result=new int[param.length];			
		int r=1;						
		conn = jdbcUtils.getConn(); //得到连接							
		try {				
			result=qr.batch(conn,sql,param);// 批量进行数据库操作			
		} catch (SQLException e) {
			e.printStackTrace();			
		} finally {				
			jdbcUtils.close(conn);//关闭数据库连接			
		}			
		for(int i=0;i<result.length;i++){				
			r*=result[i];			
		}			
		return r>0?true:false;	
	}

DaoImpl继承BaseDao类，进行相应的增删改查操作

文件连接

引入jar包

• mysql-connector-java-5.1.0-bin.jar

1. src下新建db.properties，文件如下：

JdbcDriver=com.mysql.jdbc.Driver
JdbcUrl=jdbc:mysql://localhost:3306/kd31
user=root
password=abc123

2. JdbcUtil :

package cn.kgc.util;

import java.io.IOException;
import java.lang.reflect.Field;
import java.nio.file.PathMatcher;
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.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.management.Query;

import com.mchange.v1.lang.NullUtils;



public class JdbcUtil {
	/**
	 * 连接数据库
	 * @return
	 */
	public static Connection getConnection(){
		Connection connection=null;
		Properties properties = new Properties();
		try {
			properties.load(JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties"));
			String JdbcDriver = properties.getProperty("JdbcDriver");
			String JdbcUrl = properties.getProperty("JdbcUrl");
			String user = properties.getProperty("user");
			String password = properties.getProperty("password");
			Class.forName(JdbcDriver);
			connection = DriverManager.getConnection(JdbcUrl,user,password);
			System.out.println();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return connection;
	}
	/**
	 * 公共的关闭方法
	 */
	public static void close(ResultSet rs,Connection connection, PreparedStatement preparedStatement){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(connection!=null){
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(preparedStatement!=null){
			try {
				preparedStatement.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	/**
	 * 公用的增删改
	 */
	public static void update(String sql,Object...params){
		Connection conn=null;
		PreparedStatement pstm=null;
		try {
			conn=getConnection();
			pstm = conn.prepareStatement(sql);
			for(int i=0;i<params.length;i++){
				pstm.setObject(i+1, params[i]);
			}
			pstm.executeUpdate(); 
		} catch (Exception e) {
			// TODO: handle exception
		}finally{
			close(null,conn,pstm);
		}
	}
	/**
	 * 通用查询一条数据方法
	 */
	public static<T>T Query(Class<T> clazz,String sql,Object...paras) {
		Connection conn =null;
		PreparedStatement pstm=null;
		ResultSet rs=null;
		try {
			conn= getConnection();
			pstm = conn.prepareStatement(sql);
			for (int i = 0; i < paras.length; i++) {
				pstm.setObject(i+1, paras[i]);
			}
			rs = pstm.executeQuery();
			ResultSetMetaData mData = rs.getMetaData();
			int count = mData.getColumnCount();
			T t = clazz.newInstance();
			while(rs.next()) {
				for (int i = 0; i < count; i++) {
					Object value = rs.getObject(i+1);
					String label = mData.getColumnLabel(i+1);
					Field field = clazz.getDeclaredField(label);
					field.setAccessible(true);
					field.set(t, value);
				}
			}
			return t;
		} catch (Exception e) {
		}
		return null;
	}
	/**
	 * 通用查询多条数据方法
	 */
	public static <T> List<T> Query1(Class<T> clazz,String sql,Object...paras) {
		Connection conn=null;
		PreparedStatement pstm=null;
		ResultSet rSet=null;
		List<T> list = new ArrayList<T>();
		try {
			conn = getConnection();
			pstm  = conn.prepareStatement(sql);
			for (int i = 0; i < paras.length; i++) {
				pstm.setObject(i+1, paras[i]);
			}
			rSet=pstm.executeQuery(); 
			//通过结果集获取字段
			ResultSetMetaData metaData = rSet.getMetaData();
			//通过字段得到字段的数量
			int count = metaData.getColumnCount();
			T t = clazz.newInstance();
			while(rSet.next()) {
				for (int i = 0; i < count; i++) {
					//通过结果集获取值
					Object value = rSet.getObject(i+1);
					//获取字段别名
					String label = metaData.getColumnLabel(i+1);
					//获取类中的变量名
					Field declaredField = clazz.getDeclaredField(label);
					//可以访问私有变量，默认是false
					declaredField.setAccessible(true);
					//给变量赋值
					declaredField.set(t, value);
				}
				list.add(t);
			}
			return list;
		} catch (Exception e) {
			// TODO: handle exception
		}finally {
			close(null,conn, pstm);
		}
		return null;
	}
	
}