JDBC_DAO封装完整版

• DAO：Data Access Object访问数据信息的类和接口，包括了对数据的CRUD（Create、Retrival、Update、Delete），而不包含任何业务相关的信息。有时也称作：BaseDAO
• 作用：为了实现功能的模块化，更有利于代码的维护和升级。
  

【jdbcDruid.properties】

url=jdbc:mysql://localhost:3306/test
username=root
password=wkq12345
driverClassName=com.mysql.jdbc.Driver

initialSize=10
maxActive=10

【DruidUtile.java】

package druid;


import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.Properties;

import javax.sql.DataSource;

import org.apache.commons.dbutils.DbUtils;
import org.junit.Test;

import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.mysql.jdbc.PreparedStatement;
/**
 * 
 * @Description 使用德鲁伊数据库连接池技术  
 * @author wkq  
 * @version  
 * @date 2020年4月8日下午4:40:08 
 *
 */
public class DruidUtile {
	private static DataSource ds = null;
	static {
		Properties pro = new Properties();
		InputStream is = ClassLoader.getSystemResourceAsStream("jdbcDruid.properties");
		try {
			pro.load(is);
			ds = DruidDataSourceFactory.createDataSource(pro);
		} catch (Exception e){
			e.printStackTrace();
		}		
	}
	@Test
	public static Connection getConnection() throws Exception {
		Connection conn = ds.getConnection();
		return conn;
	}
	public static void closeResource(Connection conn) {
		DbUtils.closeQuietly(conn);
	}
	public static void closeResource(PreparedStatement ps,Connection conn) {
		DbUtils.closeQuietly(ps);
		DbUtils.closeQuietly(conn);
	}
	public static void closeResource(PreparedStatement ps,Connection conn,ResultSet rs) {
		DbUtils.closeQuietly(ps);
		DbUtils.closeQuietly(conn);
		DbUtils.closeQuietly(rs);
	}
}

【Customer.java】

package comm.bean;

import java.sql.Date;
/*
 * @Description ORM编程思想  （object relational mapping）
 * 				一个数据表对应一个java类
 * 				表中的一条记录对应java类的一个对象
 *			 	表中的一个字段对应java类的一个属性 
 * @author wkq  
 * @version  
 * @date 2020年4月8日下午4:40:08 
 * 
 */

public class Customer {
	private int id;
	private String name;
	private String email;
	private Date birth;

	public Customer(int id, String name, String email, Date birth) {
		super();
		this.id = id;
		this.name = name;
		this.email = email;
		this.birth = birth;
	}

	public Customer(String name, String email, Date birth) {
		super();
		this.name = name;
		this.email = email;
		this.birth = birth;
	}

	public Customer() {
		super();
	}

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getEmail() {
		return email;
	}

	public void setEmail(String email) {
		this.email = email;
	}

	public Date getDate() {
		return birth;
	}

	public void setDate(Date birth) {
		this.birth = birth;
	}

	@Override
	public String toString() {
		return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";
	}

}

【BaseDAO.java】

package 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 org.apache.commons.dbutils.handlers.ScalarHandler;

@SuppressWarnings("unchecked")
public abstract class BaseDAO<T> {
	private QueryRunner runner = new QueryRunner();
	// 定义一个变量来接收泛型的类型
	private Class<T> clazz = null;
	{
		// 获取父类的类型
		// getGenericSuperclass()获得带有泛型的父类
		// ParameterizedType参数化类型，即泛型
		ParameterizedType parameterizedType = (ParameterizedType) this.getClass().getGenericSuperclass();
		// 获取具体的泛型类型 getActualTypeArguments获取具体的泛型的类型
		// 这个方法会返回一个Type的数组
		Type[] types = parameterizedType.getActualTypeArguments();
		// 获取具体的泛型的类型
		this.clazz = (Class<T>) types[0];
	}

	public int update(Connection conn, String sql, Object... params) {
		int count = 0;
		try {
			count = runner.update(conn, sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	public T getBean(Connection conn, String sql, Object... params) {
		BeanHandler<T> handler = new BeanHandler<>(clazz);
		T query = null;
		try {
			query = runner.query(conn, sql, handler, params);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return query;
	}

	public List<T> getBeanList(Connection conn, String sql) {
		BeanListHandler<T> handler = new BeanListHandler<T>(clazz);
		List<T> query = null;
		try {
			query = runner.query(conn, sql, handler);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return query;
	}

	// public Object getValue(Connection conn, String sql) {
	// ScalarHandler handler = new ScalarHandler();
	// Object query = null;
	// try {
	// query = runner.query(conn, sql, handler);
	// } catch (SQLException e) {
	// e.printStackTrace();
	// }
	// return query;
	// }

	public <E> E getValue(Connection conn, String sql) {
		ScalarHandler handler = new ScalarHandler();
		E query = null;
		try {
			query = (E) runner.query(conn, sql, handler);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return query;
	}

}

【CustomerDAO.java】

package DAO;

import java.sql.Connection;
import java.sql.Date;
import java.util.List;

import comm.bean.Customer;

public interface CustomerDAO {
	/**
	 * 
	 * @Description 向customer表中插入数据
	 * @author wkq
	 * @date 2020年4月9日下午11:17:02
	 * @param conn
	 * @param sustomer
	 */
	int insert(Connection conn ,Customer customer);
	/**
	 * 
	 * @Description 根据id删除customer表中的数据
	 * @author wkq
	 * @date 2020年4月9日下午11:17:56
	 * @param conn
	 * @param id
	 */

	int delectById(Connection conn,int id);
	/**
	 * 
	 * @Description 根据id获取customer表中的customer信息
	 * @author wkq
	 * @date 2020年4月9日下午11:20:03
	 * @param conn
	 * @param id
	 * @return
	 */
	Customer getById(Connection conn,int id);
	/**
	 * 
	 * @Description 获取所有customer信息
	 * @author wkq
	 * @date 2020年4月9日下午11:19:48
	 * @param conn
	 * @return
	 */
	List<Customer> getAll(Connection conn);
	/**
	 * 
	 * @Description 获取表中的列数
	 * @author wkq
	 * @date 2020年4月9日下午11:21:10
	 * @param conn
	 * @return
	 */
	long getCount(Connection conn);
	/**
	 * 
	 * @Description 获取最大日期
	 * @author wkq
	 * @date 2020年4月9日下午11:20:40
	 * @param conn
	 * @return
	 */
	Date getMaxDate(Connection conn);
}

【CustomerDaoImpl.java】

package DAO;


import java.sql.Connection;
import java.sql.Date;
import java.util.List;

import comm.bean.Customer;

public class CustomerDaoImpl extends BaseDAO<Customer> implements CustomerDAO{

	@Override
	public int insert(Connection conn, Customer customer) {
		String sql = "insert into customers(name,email,birth) values(?,?,?)";
		int count = update(conn, sql, customer.getName(),customer.getEmail(),customer.getDate());
		return count;
	}

	@Override
	public int delectById(Connection conn, int id) {
		String sql = "delete from customers where id = ?";
		int count = update(conn, sql, id);
		return count;
	}

	@Override
	public Customer getById(Connection conn, int id) {
		String sql = "select id,name,email,birth from customers where id = ?";
		Customer customer = getBean(conn, sql, id);
		return customer;
	}

	@Override
	public List<Customer> getAll(Connection conn) {
		String sql = "select id,name,email,birth from customers";
		List<Customer> beanList = getBeanList(conn, sql);
		return beanList;
	}

	@Override
	public long getCount(Connection conn) {
		String sql = "select count(*) from customers";
		long count = (long)getValue(conn, sql);
		return count;
	}

	@Override
	public Date getMaxDate(Connection conn) {
		String sql = "select max(birth) from customers";
		Date birth = (Date) getValue(conn, sql);
		return birth;
	}
}

【CutomerDaoImplTest.java】测试代码

package DAO;

import java.sql.Connection;
import java.sql.Date;
import java.util.List;

import org.junit.Test;

import comm.bean.Customer;
import druid.DruidUtile;

public class CutomerDaoImplTest {
	private CustomerDaoImpl dao = new CustomerDaoImpl();

	/**
	 * 
	 * @Description 测试插入操作
	 * @author wkq
	 * @date 2020年4月10日下午12:22:08
	 */
	@Test
	public void testInsert() {
		Connection conn = null;
		try {
			conn = DruidUtile.getConnection();
			dao.insert(conn, new Customer("wkq","wkq123@qq.com",new Date(12345648)));
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DruidUtile.closeResource( conn);
		}
	}
	/**
	 * 
	 * @Description测试删除操作
	 * @author wkq
	 * @date 2020年4月10日下午12:22:31
	 */
	@Test
	public void testDelectById() {
		Connection conn = null;
		try {
			conn = DruidUtile.getConnection();
			dao.delectById(conn, 69);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DruidUtile.closeResource( conn);
		}
	}
	/**
	 * 
	 * @Description测试获取customer对象操作
	 * @author wkq
	 * @date 2020年4月10日下午12:22:55
	 */
	@Test
	public void testGetById() {
		Connection conn = null;
		try {
			conn = DruidUtile.getConnection();
			Customer cust = dao.getById(conn, 1);
			System.out.println(cust);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {			
			DruidUtile.closeResource(conn);
		}
	}
	
	
	/**
	 * 
	 * @Description测试获取所有cutomer操作
	 * @author wkq
	 * @date 2020年4月10日下午12:23:13
	 */
	@Test
	public void testGetAll() {
		Connection conn = null;
		try {
			conn = DruidUtile.getConnection();
			List<Customer> listCust = dao.getAll(conn);
			listCust.forEach(System.out::println);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DruidUtile.closeResource(conn);
		}
	}
	/**
	 * 
	 * @Description获取表中的数据有多少行
	 * @author wkq
	 * @date 2020年4月10日下午12:23:45
	 */
	@Test
	public void testGetCount() {
		Connection conn = null;
		try {
			conn = DruidUtile.getConnection();
			long count = dao.getCount(conn);
			System.out.println(count);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DruidUtile.closeResource(conn);
		}
	}
	/**
	 * 
	 * @Description 测试最大日期操作
	 * @author wkq
	 * @date 2020年4月10日下午12:24:04
	 */
	@Test
	public void testGetMaxDate() {
		Connection conn = null;
		try {
			conn = DruidUtile.getConnection();
			Date maxDate = dao.getMaxDate(conn);
			System.out.println(maxDate);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DruidUtile.closeResource(conn);
		}
	}
}