jdbc数据库连接池(druid)及dbutils

jar 包

 druid配置文件以及log4j的配置文件

#druid.properties
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/myschool?rewriteBatchedStatements=true
username=root
password=123456



#log4j配置文件
### \u8BBE\u7F6E###
log4j.rootLogger = debug,stdout,D,E

### \u8F93\u51FA\u4FE1\u606F\u5230\u63A7\u5236\u62AC ###
log4j.appender.stdout = org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target = System.out
log4j.appender.stdout.layout = org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern = [%-5p] %d{yyyy-MM-dd HH:mm:ss,SSS} method:%l%n%m%n

### \u8F93\u51FADEBUG \u7EA7\u522B\u4EE5\u4E0A\u7684\u65E5\u5FD7\u5230=/home/hao/Desktop/log4jTest.log ###
log4j.appender.D = org.apache.log4j.DailyRollingFileAppender
log4j.appender.D.File = log4jTest.log
log4j.appender.D.Append = true
log4j.appender.D.Threshold = DEBUG 
log4j.appender.D.layout = org.apache.log4j.PatternLayout
log4j.appender.D.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss}  [ %t:%r ] - [ %p ]  %m%n

### \u8F93\u51FAERROR \u7EA7\u522B\u4EE5\u4E0A\u7684\u65E5\u5FD7\u5230=/home/hao/Desktop/log4jTest.log ###
log4j.appender.E = org.apache.log4j.DailyRollingFileAppender
log4j.appender.E.File =log4jTest.log
log4j.appender.E.Append = true
log4j.appender.E.Threshold = ERROR 
log4j.appender.E.layout = org.apache.log4j.PatternLayout
log4j.appender.E.layout.ConversionPattern = %-d{yyyy-MM-dd HH:mm:ss}  [ %t:%r ] - [ %p ]  %m%n

学生类对象（建议使用abstract修饰类） 

import com.mysql.cj.jdbc.Blob;

public  class Student extends BaseDao<Student>{
	private String no;
	private String pwd;
	private String name;
	private String sex;
	private int gradeid;
	private String phone;
	private String address;
	private String birndate;
	private String email;
	private String id;
	private Blob img;
	public String getNo() {
		return no;
	}
	public void setNo(String no) {
		this.no = no;
	}
	public String getPwd() {
		return pwd;
	}
	public void setPwd(String pwd) {
		this.pwd = pwd;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	public String getSex() {
		return sex;
	}
	public void setSex(String sex) {
		this.sex = sex;
	}
	public int getGradeid() {
		return gradeid;
	}
	public void setGradeid(int gradeid) {
		this.gradeid = gradeid;
	}
	public String getPhone() {
		return phone;
	}
	public void setPhone(String phone) {
		this.phone = phone;
	}
	public String getAddress() {
		return address;
	}
	public void setAddress(String address) {
		this.address = address;
	}
	public String getBirndate() {
		return birndate;
	}
	public void setBirndate(String birndate) {
		this.birndate = birndate;
	}
	public String getEmail() {
		return email;
	}
	public void setEmail(String email) {
		this.email = email;
	}
	public String getId() {
		return id;
	}
	public void setId(String id) {
		this.id = id;
	}
	public Blob getImg() {
		return img;
	}
	public void setImg(Blob img) {
		this.img = img;
	}
	public Student() {}
	public Student(String name) {
		this.name=name;
	}
	public Student(String no, String pwd, String name, String sex, int gradeid, String phone, String address,
			String birndate, String email, String id, Blob img) {
		super();
		this.no = no;
		this.pwd = pwd;
		this.name = name;
		this.sex = sex;
		this.gradeid = gradeid;
		this.phone = phone;
		this.address = address;
		this.birndate = birndate;
		this.email = email;
		this.id = id;
		this.img = img;
	}
	@Override
	public String toString() {
		return "Student [no=" + no + ", pwd=" + pwd + ", name=" + name + ", sex=" + sex + ", gradeid=" + gradeid
				+ ", phone=" + phone + ", address=" + address + ", birndate=" + birndate + ", email=" + email + ", id="
				+ id + ", img=" + img + "]";
	}
	
}

 封装的方法

import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Array;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import com.alibaba.druid.pool.DruidDataSourceFactory;


/**
 * 最终版本  version2.0（考虑事务）
 * @author 迷糊小马
 *
 */
public abstract class BaseDao<T> {
	/**
	 * 获取运行时类带有父类的泛型
	 */
	Class clazz=null;
	{
		Type genericSuperclass = this.getClass().getGenericSuperclass();
		ParameterizedType para=(ParameterizedType) genericSuperclass;
		Type[] actualTypeArguments = para.getActualTypeArguments();
		clazz=(Class) actualTypeArguments[0];
	}
	/**
	 * 添加log4j日志跟踪
	 */
		static Logger logger=Logger.getLogger(BaseDao.class.getName());
		/**
		 * 创建数据库连接池  切记连接池只创建一次
		 * 所有的数据库连接池都是DataSource的实现类；
		 *    			使用druid数据库连接池技术
		 */
		private static DataSource source=null;
		/**
		 * 获取连接
		 * @return
		 * @throws Exception
		 */
		static Properties pro=null;
		static {
			try {
				pro=new Properties();
				pro.load(new FileInputStream(new File("resources/druid.properties")));
			} catch (Exception e) {
					logger.info(e.getMessage());
			}
			
		}
		public  Connection getConnection() {
			Connection  conn=null;
			try {
				source=DruidDataSourceFactory.createDataSource(pro);
				conn = source.getConnection();		
			} catch (Exception e) {
				logger.info(e.getMessage());
			}
			
			return conn;
		}
		/**
		 * 增删改  考虑事务的连接
		 * @param conn  连接对象
		 * @param sql     待执行的SQL语句
		 * @param obj    可变长度的形参
		 * @return			受影响的行数
		 * @throws Exception 
		 */
		public int executeUpdate(Connection conn,String sql,Object...obj) throws Exception {
					QueryRunner runn=new QueryRunner();
					int count = runn.execute(conn, sql, obj);
					return count;
		}
		/**
		 * 查询  将查询到的字段封装到Map集合中，字段作为键
		 * @param conn
		 * @param sql
		 * @param obj
		 * @return   返回查询到的集合
		 * @throws SQLException
		 */
		public List<Map<String,Object>> executeQueryMap(Connection conn,String sql,Object...obj) throws SQLException{
			QueryRunner runn=new QueryRunner();
			MapListHandler handler=new MapListHandler();
			   List<Map<String, Object>> list = runn.query(conn, sql, handler, obj);
			   return  list;
		}
		/**
		 * 查询  将查询到的字段封装到list集合中
		 * @param conn	连接对象
		 * @param sql		数据库执行语句
		 * @param obj		可变形参
		 * @return	反悔查询到的内容
		 * @throws SQLException
		 */
		public List<T> executeQueryBean(Connection conn,String sql,Object...obj) throws SQLException{
				QueryRunner runn=new QueryRunner();
				BeanListHandler<T> handler =new BeanListHandler<T>(clazz);
				return runn.query(conn, sql, handler, obj);
		}
		/**
		 * 查询特殊字段
		 * @param conn	连接对象
		 * @param sql		待执行的SQL语句
		 * @param obj		可变长度的形参
		 * @return				返回查询到的特殊字段
		 * @throws SQLException
		 */
		public Object executeScalar(Connection conn,String sql,Object...obj) throws SQLException {
			QueryRunner runn=new QueryRunner();
			ScalarHandler<T> handler =new ScalarHandler<T>();
			return runn.execute(conn, sql, handler, obj);
		}
		/**
		 * 自定义查询表中的字段
		 * @param conn
		 * @param sql
		 * @param obj
		 * @return 	 返回自定义查询的集合
		 * @throws SQLException 
		 */
		public List<T> a(Connection conn,String sql,Object...obj) throws SQLException {
				QueryRunner runn=new QueryRunner();
				return runn.execute(conn, sql, new ResultSetHandler<T>() {
					@Override
					public T handle(ResultSet rs) throws SQLException {
						if(rs.next()) {
							String name = rs.getString("name");
							T t=null;
							try {
								t = (T) clazz.getDeclaredConstructor(String.class).newInstance(name);
							} catch (NoSuchMethodException | SecurityException 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 (IllegalArgumentException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							} catch (InvocationTargetException e) {
								// TODO Auto-generated catch block
								e.printStackTrace();
							}
							return t;
						}
						return null;
					}
				}, obj);
				
		}
		/**
		 * 关闭资源
		 * @param conn
		 */
		public static void  allClose(Connection conn) {
			if(conn!=null)
					try {
						 conn.close();
					} catch (SQLException e) {
						logger.info(e.getMessage());
					}
		}
}