jdbc 与 mysql 连接-数据库事物-抽象类和接口的封装

src 下的 jdbc.properties 文件

user=root
password=123456
url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements = true
driverClass=com.mysql.cj.jdbc.Driver

com.atguigu1.transaction包

package com.atguigu1.transaction;

import java.sql.Connection;

import org.junit.Test;

import com.atguigu1.util.JDBCUtils;

public class ConnectionTest {
	
	// 将 jdbc.properties 文件放入 src 目录下
	@Test
	public void testGetConnection() throws Exception {
		Connection conn = JDBCUtils.getConnection();
		System.out.println(conn);
	}
	
	
}

package com.atguigu1.transaction;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.junit.Test;

import com.atguigu1.util.JDBCUtils;
import com.mysql.cj.jdbc.result.ResultSetMetaData;
/*
 * 1.什么叫数据库事物？
 *   事物：一组逻辑操作单元，是数据从一种状态变换到另一种状态。
 *         > 一组逻辑操作单元：一个或多个 DML 操作。
 * 
 * 2.事务处理的原则：保证所有事物都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。
 *   当在一个事务中执行多个操作时，要么所有的事物都被提交(commit),那么这些修改就永久地保存
 *   下来：要么数据库管理系统将放弃所作的所有修改，整个事物回滚 ( rollback ) 到最初状态。
 * 
 * 3.数据一旦提交，就不可回滚
 * 
 * 4.哪些操作会导致数据的自动提交？(都避免)
 * 		> DDL 操作一旦执行，都会自动提交
 * 			> set autocommit = false 对 DDL 操作失效
 * 		> DML 默认情况下，一旦执行，就会自动提交。
 * 			> 我们可以通过 set autocommit = false 的方式取消 DML 操作的自动提交
 * 		> 默认在关闭连接时，会自动的提交数据
 */
public class TransactionTest {
	
	//****************************** 未考虑数据库事务情况下的转账操作 ******************************
	/**
	 * 针对于数据表 user_table 来说；
	 * 
	 * AA 用户给 BB 用户转账 100
	 * 
	 * update user_table set balance = balance - 100 where user = 'AA';
	 * 
	 * update user_table set balance = balance + 100 where user = 'BB';
	 * 
	 */
	@Test
	public void testUpdate() {
		
		String sql1 = " update user_table set balance = balance - 100 where user = ?";
		update(sql1,"AA");
		
		// 模拟网络异常
		System.out.println(10 / 0);
		
		String sql2 = " update user_table set balance = balance + 100 where user = ?";
		update(sql2,"BB");
		
		System.out.println("转账成功");
	}

	// 通用的 增删改查 操作 (增删改) --- version 1.0
	public int update(String sql,Object ...args){	// sql 中的占位符的个数与可变形参的长度相同
		Connection conn = null;
		PreparedStatement ps = null;
		try {
			//1.获取数据库的连接
			conn = JDBCUtils.getConnection();
			//2.预编译 sql 语句，返回 PreparedStatement 的实例
			ps = conn.prepareStatement(sql);
			//3.填充占位符
			for(int i = 0; i < args.length; i++) {
				ps.setObject(i+1, args[i]);//小心参数声明错误！
			}
			//4.执行
			return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			
			// 使用数据库连接池的时候，记得最后将默认值改回原来的
			// 修改其为自动提交数据
			// 主要针对于使用数据库连接池的使用
			try {
				conn.setAutoCommit(true);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			
			//5.资源的关闭
			JDBCUtils.closeResource(conn, ps);
		}
		return 0;
	}
	
	//****************************** 考虑数据库事务后的转账操作 ******************************
	
	@Test
	public void testUpdateWithTx() {	
		
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			System.out.println(conn.getAutoCommit());// true:默认情况下自动提交
			// 1.取消数据的自动提交
			conn.setAutoCommit(false); 
			
			String sql1 = " update user_table set balance = balance - 100 where user = ?";
			update(conn,sql1,"AA");
			
			// 模拟网络异常
			System.out.println(10 / 0);
			
			String sql2 = " update user_table set balance = balance + 100 where user = ?";
			update(conn,sql2,"BB");
			
			System.out.println("转账成功");
			
			// 2.提交数据
			conn.commit();
			
		} catch (Exception e) {
			e.printStackTrace();
			// 3.回滚数据
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			// 关闭的时候，数据自动提交
			JDBCUtils.closeResource(conn, null);
		}
	}
	
	// 通用的 增删改查 操作 (增删改) --- version 2.0 (考虑上事物)
	public int update(Connection conn,String sql,Object ...args){	// sql 中的占位符的个数与可变形参的长度相同
		PreparedStatement ps = null;
		try {
			//1.预编译 sql 语句，返回 PreparedStatement 的实例
			ps = conn.prepareStatement(sql);
			//2.填充占位符
			for(int i = 0; i < args.length; i++) {
				ps.setObject(i+1, args[i]);//小心参数声明错误！
			}
			//3.执行
			return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//4.资源的关闭
			JDBCUtils.closeResource(null,ps);
		}
		return 0;
	}
		
	//************************************************************	

	@Test
	public void testTransactionSelect() throws Exception {
		
		Connection conn = JDBCUtils.getConnection();
		// 获取当前连接的隔离级别
		// 此时在 Connection接口 中 TRANSACTION_REPEATABLE_READ  = 4
		System.out.println(conn.getTransactionIsolation());
		
		// 设置数据库的隔离级别(避免 脏读 就行)：
		conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
		
		// 取消自动提交数据
		conn.setAutoCommit(false);
		
		String sql = "select user,password,balance from user_table where user = ?";
		User user = getInstance(conn,User.class,sql,"CC");
		
		System.out.println(user);
	}
	
	@Test
	public void testTransactionUpdate() throws Exception {
		
		Connection conn = JDBCUtils.getConnection();
		
		// 取消自动提交数据
		conn.setAutoCommit(false);
		
		String sql = "update user_table set balance = ? where user = ?";
		update(conn,sql,5000,"CC");
		
		Thread.sleep(15000);
		System.out.println("修改结束");
		
	}
	
	
	// 通用的查询操作，用于返回数据表中的一条记录(version 2.0：考虑上事物)
	public <T> T getInstance(Connection conn,Class<T> clazz,String sql,Object...args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			
			ps = conn.prepareStatement(sql);
			for(int i = 0; i < args.length; i++) {
				ps.setObject(i+1,args[i]);
			}
			
			rs = ps.executeQuery();
			// 获取结果集的元数据：ResultSetMetaData
			ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
			// 通过 ResultSetMetaData 获取结果集中的列数
			int columnCount = rsmd.getColumnCount();
			
			if(rs.next()) {
				T t = clazz.newInstance();
				// 处理结果集一行数据中的每一个列
				for(int i = 0; i < columnCount; i++) {
					// 获取列值
					Object columValue = rs.getObject(i + 1);	
					
					// 获取每个列的列名
//					String columnName = rsmd.getColumnName(i + 1);
					// 获取每个列的别名
					String columnLabel = rsmd.getColumnLabel(i + 1);
					
					// 给 t 对象指定的 columnName 属性，赋值为 columValue：通过反射
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columValue);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps, rs);
		}
		return null;
	}
	
	
}

package com.atguigu1.transaction;

public class User {
	
	private String user;
	private String password;
	private int balance;
	public User() {
		super();
	}
	public User(String user, String password, int balance) {
		super();
		this.user = user;
		this.password = password;
		this.balance = balance;
	}
	public String getUser() {
		return user;
	}
	public void setUser(String user) {
		this.user = user;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	public int getBalance() {
		return balance;
	}
	public void setBalance(int balance) {
		this.balance = balance;
	}
	@Override
	public String toString() {
		return "User [user=" + user + ", password=" + password + ", balance=" + balance + "]";
	}
	
}

com.atguigu1.util 包

package com.atguigu1.util;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * 操作数据库的工具类，此工具类避免每次操纵数据库时，连接数据库，关闭资源。
 * @author 21115
 * 
 */

public class JDBCUtils {
	
	/**
	 * 获取数据库的连接
	 * @return
	 * @throws Exception
	 */
	public static Connection getConnection() throws Exception {
		//1.读取配置文件中的 4 个基本信息
		InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
		
		Properties pros = new Properties();
		pros.load(is);
		
		String user = pros.getProperty("user");
		String password = pros.getProperty("password");
		String url = pros.getProperty("url");
		String driverClass = pros.getProperty("driverClass");
		
		//2.加载驱动
		Class.forName(driverClass);
		
		//3.获取连接
		Connection conn = DriverManager.getConnection(url,user,password);
		return conn;
	}
	
	/**
	 * 关闭连接和 Statement 的操作
	 * @param conn
	 * @param ps
	 */
	public static void closeResource(Connection conn,Statement ps) {
		try {
			if(ps != null)
				ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	// 关闭资源操作
	public static void closeResource(Connection conn,Statement ps,ResultSet rs) {
		try {
			if(ps != null)
				ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			if(rs != null) 
				rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

com.atguigu2.bean 包

package com.atguigu2.bean;

import java.sql.Date;

/*
 * ORM 编程思想(object relational mapping)
 * 一个数据表对应一个 java 类
 * 表中的一条记录对应 java 类的一个对象
 * 表中的一个字段对应 java 类的一个属性
 * 
 * 
 *     Java 与 SQL 对应数据类型转换表
 *     
 * Java 类型						SQL 类型
 *   boolean					  BIT
 *   byte						  TINYINT
 *   short						  SMALLINT
 *   int 						  INTGER
 *   long						  BIGINT
 *   String						  CHAR,VARCHAR,LONGVARCHAR
 *   byte array					  BINARY,VAR BINARY
 *   java.sql.Date				  DATE
 *   java.sql.Time				  TIME
 *   java.sql.Timestamp			  TIMESTAMP
 * 
 */
public class Customer {

	private int id;
	private String name;
	private String email;
	private Date birth;
	public Customer() {
		super();
	}
	public Customer(int id, String name, String email, Date birth) {
		super();
		this.id = id;
		this.name = name;
		this.email = email;
		this.birth = birth;
	}
	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 getBirth() {
		return birth;
	}
	public void setBirth(Date birth) {
		this.birth = birth;
	}
	@Override
	public String toString() {
		return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";
	}
	
}

package com.atguigu2.bean;

import java.sql.Date;

public class Order {
	private int orderId;
	private String orderName;
	private Date orderDate;
	public Order() {
		super();
	}
	public Order(int orderId, String orderName, Date orderDate) {
		super();
		this.orderId = orderId;
		this.orderName = orderName;
		this.orderDate = orderDate;
	}
	public int getOrderId() {
		return orderId;
	}
	public void setOrderId(int orderId) {
		this.orderId = orderId;
	}
	public String getOrderName() {
		return orderName;
	}
	public void setOrderName(String orderName) {
		this.orderName = orderName;
	}
	public Date getOrderDate() {
		return orderDate;
	}
	public void setOrderDate(Date orderDate) {
		this.orderDate = orderDate;
	}
	@Override
	public String toString() {
		return "Order [orderId=" + orderId + ", orderName=" + orderName + ", orderDate=" + orderDate + "]";
	}
	

}

com.atguigu2.dao 包

package com.atguigu2.dao;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.atguigu1.util.JDBCUtils;
import com.mysql.cj.jdbc.result.ResultSetMetaData;

/*
 * DAO: data(base) access object
 * 
 * 封装了针对于数据表的通用的操作
 * 
 * 具体的表提供具体的 DAO
 */
public abstract class BaseDAO {

	// 通用的 增删改查 操作 (增删改) --- version 2.0 (考虑上事物)
	public int update(Connection conn,String sql,Object ...args){	// sql 中的占位符的个数与可变形参的长度相同
		PreparedStatement ps = null;
		try {
			//1.预编译 sql 语句，返回 PreparedStatement 的实例
			ps = conn.prepareStatement(sql);
			//2.填充占位符
			for(int i = 0; i < args.length; i++) {
				ps.setObject(i+1, args[i]);//小心参数声明错误！
			}
			//3.执行
			return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//4.资源的关闭
			JDBCUtils.closeResource(null,ps);
		}
		return 0;
	}
	
	// 通用的查询操作，用于返回数据表中的一条记录(version 2.0：考虑上事物)
	public <T> T getInstance(Connection conn,Class<T> clazz,String sql,Object...args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			
			ps = conn.prepareStatement(sql);
			for(int i = 0; i < args.length; i++) {
				ps.setObject(i+1,args[i]);
			}
			
			rs = ps.executeQuery();
			// 获取结果集的元数据：ResultSetMetaData
			ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
			// 通过 ResultSetMetaData 获取结果集中的列数
			int columnCount = rsmd.getColumnCount();
			
			if(rs.next()) {
				T t = clazz.newInstance();
				// 处理结果集一行数据中的每一个列
				for(int i = 0; i < columnCount; i++) {
					// 获取列值
					Object columValue = rs.getObject(i + 1);	
					
					// 获取每个列的列名
//						String columnName = rsmd.getColumnName(i + 1);
					// 获取每个列的别名
					String columnLabel = rsmd.getColumnLabel(i + 1);
					
					// 给 t 对象指定的 columnName 属性，赋值为 columValue：通过反射
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columValue);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps, rs);
		}
		return null;
	}
	
	// 通用的查询操作，用于返回数据表中的多条记录构成的集合(version 2.0：考虑上事物)
	public <T> List<T> getForList(Connection conn,Class<T> clazz,String sql,Object... args){
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			
			ps = conn.prepareStatement(sql);
			for(int i = 0; i < args.length; i++) {
				ps.setObject(i+1,args[i]);
			}
			
			rs = ps.executeQuery();
			// 获取结果集的元数据：ResultSetMetaData
			ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
			// 通过 ResultSetMetaData 获取结果集中的列数
			int columnCount = rsmd.getColumnCount();
			// 创建集合对象
			ArrayList<T> list = new ArrayList<T>();
			
			while (rs.next()) {
				T t = clazz.newInstance();
				// 处理结果集一行数据中的每一个列:给 t 对象指定的属性赋值
				for(int i = 0; i < columnCount; i++) {
					// 获取列值
					Object columValue = rs.getObject(i + 1);	
					
					// 获取每个列的列名
//					String columnName = rsmd.getColumnName(i + 1);
					// 获取每个列的别名
					String columnLabel = rsmd.getColumnLabel(i + 1);
					
					// 给 t 对象指定的 columnName 属性，赋值为 columValue：通过反射
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columValue);
				}
				list.add(t);
			}
			
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps, rs);
		}
		return null;
	}
	
	// 用于查询特殊值得通用的方法
	public <E> E getValue(Connection conn,String sql,Object...args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			for(int i = 0;i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			
			rs = ps.executeQuery();
			if(rs.next()) {
				return (E) rs.getObject(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps, rs);			
		}
		
		return null;
	}
}

package com.atguigu2.dao;

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

import com.atguigu2.bean.Customer;

/*
 *  此接口用于规范针对于 customers 表的常规操作
 */
public interface CustomerDAO {
	
	/**
	 * 将 cust 对象添加到数据库中
	 * 
	 */
	void insert(Connection conn,Customer cust);
	
	
	/**
	 * 针对指定的 id，删除表中的一条记录
	 * 
	 */
	void deleteById(Connection conn,int id);
	
	
	/**
	 * 针对内存中的 cust 对象，去修改数据表中指定的记录
	 * 
	 */
	void update(Connection conn,Customer cust);
	
	
	/**
	 * 针对于指定的 id 查询得到对应的 Customer 对象 
	 * 
	 */
	Customer getCustomerById(Connection conn,int id);
	
	
	/**
	 * 查询表中的所有记录构成的集合
	 * 
	 */
	List<Customer> getAll(Connection conn);
	
	
	/**
	 * 返回数据表中的数据的条目数
	 * 
	 */
	Long getCount(Connection conn);
	
	
	/**
	 * 返回数据表中最大的生日
	 * 
	 */
	Date getMaxBirth(Connection conn);
}

package com.atguigu2.dao;

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

import com.atguigu2.bean.Customer;

public class CustomerDAOImpl extends BaseDAO implements CustomerDAO {

	@Override
	public void insert(Connection conn, Customer cust) {
		String sql = "insert into customers(name,email,birth)values(?,?,?)";
		update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth());
	}

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

	@Override
	public void update(Connection conn, Customer cust) {
		String sql = "update customers set name = ?,email = ?,birth = ? where id = ?";
		update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());
	}

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

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

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

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

}

com.atguigu2.dao.junit 包

package com.atguigu2.dao.junit;

import static org.junit.Assert.*;

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

import org.junit.Test;

import com.atguigu1.util.JDBCUtils;
import com.atguigu2.bean.Customer;
import com.atguigu2.dao.CustomerDAOImpl;

public class CustomerDAOImplTest {
	
	private CustomerDAOImpl dao = new CustomerDAOImpl();

	@Test
	public void testInsert() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			Customer cust = new Customer(1,"于小飞","xiaofei@126.com",new Date(23453453232L));
			dao.insert(conn, cust);
			System.out.println("添加成功");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testDeleteById() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			
			dao.deleteById(conn, 13);
			
			System.out.println("删除成功");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testUpdateConnectionCustomer() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			
			Customer cust = new Customer(18,"贝多芬","beiduofen@126.com",new Date(3425214134L));
			dao.update(conn, cust);
			
			System.out.println("修改成功");
			// 修改成功
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testGetCustomerById() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			
			Customer cust = dao.getCustomerById(conn, 19);
			System.out.println(cust);
			
			System.out.println("添加成功");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testGetAll() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			
			List<Customer> list = dao.getAll(conn);
			list.forEach(System.out::println);
			
			System.out.println("");
			
//			Customer [id=1, name=汪峰, email=wf@126.com, birth=2010-02-02]
//			Customer [id=2, name=王菲, email=wangf@163.com, birth=1988-12-26]
//			Customer [id=4, name=汤唯, email=tangw@sina.com, birth=1986-06-13]
//			Customer [id=5, name=成龙, email=Jackey@gmai.com, birth=1955-07-14]
//			Customer [id=6, name=迪丽热巴, email=reba@163.com, birth=1983-05-17]
//			Customer [id=7, name=刘亦菲, email=liuyifei@qq.com, birth=1991-11-14]
//			Customer [id=8, name=陈道明, email=bdf@126.com, birth=2014-01-17]
//			Customer [id=10, name=周杰伦, email=zhoujl@sina.com, birth=1979-11-15]
//			Customer [id=12, name=黎明, email=LiM@126.com, birth=1998-09-08]
//			Customer [id=13, name=张学友, email=zhangxy@126.com, birth=1998-12-21]
//			Customer [id=16, name=朱茵, email=zhuyin@126.com, birth=2014-01-16]
//			Customer [id=18, name=贝多芬, email=beiduofen@126.com, birth=1970-02-09]
//			Customer [id=19, name=哪吒, email=nezha@gmail.com, birth=1000-01-01]
//			Customer [id=22, name=孙权, email=sunquan@126.com, birth=1999-01-01]
//			Customer [id=23, name=李阿豪, email=li@qq.com, birth=1992-09-08]
//			Customer [id=24, name=元昊, email=yuanhao@qq.com, birth=1992-09-08]

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testGetCount() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			
			Long count = dao.getCount(conn);
			
			System.out.println("表中的记录数为: " + count);
			// 表中的记录数为: 16
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testGetMaxBirth() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			
			Date maxBirth = dao.getMaxBirth(conn);
			
			System.out.println("最大的生日为: " + maxBirth);
			// 最大的生日为: 2014-01-17
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}

}

com.atguigu3.dao 包

package com.atguigu3.dao;

import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.atguigu1.util.JDBCUtils;
import com.mysql.cj.jdbc.result.ResultSetMetaData;

/*
 * DAO: data(base) access object
 * 
 * 封装了针对于数据表的通用的操作
 * 
 * 具体的表提供具体的 DAO
 */
public abstract class BaseDAO<T> {
	
	private Class<T> clazz = null;
	
//	public BaseDAO() {
//		// 一、在构造器中将 clazz 实例化
//	}
	
	    // 二、非静态代码块将 clazz 实例化
	{
		Type genericSuperclass = this.getClass().getGenericSuperclass();
		ParameterizedType paramType = (ParameterizedType) genericSuperclass;
		
		Type[] typeArguments = paramType.getActualTypeArguments();// 获取了父类的泛型参数
		clazz = (Class<T>) typeArguments[0];// 泛型的第一个参数
		
	}

	// 通用的 增删改查 操作 (增删改) --- version 2.0 (考虑上事物)
	public int update(Connection conn,String sql,Object ...args){	// sql 中的占位符的个数与可变形参的长度相同
		PreparedStatement ps = null;
		try {
			//1.预编译 sql 语句，返回 PreparedStatement 的实例
			ps = conn.prepareStatement(sql);
			//2.填充占位符
			for(int i = 0; i < args.length; i++) {
				ps.setObject(i+1, args[i]);//小心参数声明错误！
			}
			//3.执行
			return ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			//4.资源的关闭
			JDBCUtils.closeResource(null,ps);
		}
		return 0;
	}
	
	// 通用的查询操作，用于返回数据表中的一条记录(version 2.0：考虑上事物)
	public T getInstance(Connection conn, /* Class<T> clazz, BaseDAO没加泛型时*/String sql,Object...args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			
			ps = conn.prepareStatement(sql);
			for(int i = 0; i < args.length; i++) {
				ps.setObject(i+1,args[i]);
			}
			
			rs = ps.executeQuery();
			// 获取结果集的元数据：ResultSetMetaData
			ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
			// 通过 ResultSetMetaData 获取结果集中的列数
			int columnCount = rsmd.getColumnCount();
			
			if(rs.next()) {
				T t = clazz.newInstance();
				// 处理结果集一行数据中的每一个列
				for(int i = 0; i < columnCount; i++) {
					// 获取列值
					Object columValue = rs.getObject(i + 1);	
					
					// 获取每个列的列名
//						String columnName = rsmd.getColumnName(i + 1);
					// 获取每个列的别名
					String columnLabel = rsmd.getColumnLabel(i + 1);
					
					// 给 t 对象指定的 columnName 属性，赋值为 columValue：通过反射
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columValue);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps, rs);
		}
		return null;
	}
	
	// 通用的查询操作，用于返回数据表中的多条记录构成的集合(version 2.0：考虑上事物)
	public List<T> getForList(Connection conn, /* Class<T> clazz, BaseDAO没加泛型时*/String sql,Object... args){
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			
			ps = conn.prepareStatement(sql);
			for(int i = 0; i < args.length; i++) {
				ps.setObject(i+1,args[i]);
			}
			
			rs = ps.executeQuery();
			// 获取结果集的元数据：ResultSetMetaData
			ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();
			// 通过 ResultSetMetaData 获取结果集中的列数
			int columnCount = rsmd.getColumnCount();
			// 创建集合对象
			ArrayList<T> list = new ArrayList<T>();
			
			while (rs.next()) {
				T t = clazz.newInstance();
				// 处理结果集一行数据中的每一个列:给 t 对象指定的属性赋值
				for(int i = 0; i < columnCount; i++) {
					// 获取列值
					Object columValue = rs.getObject(i + 1);	
					
					// 获取每个列的列名
//					String columnName = rsmd.getColumnName(i + 1);
					// 获取每个列的别名
					String columnLabel = rsmd.getColumnLabel(i + 1);
					
					// 给 t 对象指定的 columnName 属性，赋值为 columValue：通过反射
					Field field = clazz.getDeclaredField(columnLabel);
					field.setAccessible(true);
					field.set(t, columValue);
				}
				list.add(t);
			}
			
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps, rs);
		}
		return null;
	}
	
	// 用于查询特殊值得通用的方法
	public <E> E getValue(Connection conn,String sql,Object...args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			for(int i = 0;i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			
			rs = ps.executeQuery();
			if(rs.next()) {
				return (E) rs.getObject(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps, rs);			
		}
		
		return null;
	}
}

package com.atguigu3.dao;

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

import com.atguigu2.bean.Customer;

/*
 *  此接口用于规范针对于 customers 表的常规操作
 */
public interface CustomerDAO {
	
	/**
	 * 将 cust 对象添加到数据库中
	 * 
	 */
	void insert(Connection conn,Customer cust);
	
	
	/**
	 * 针对指定的 id，删除表中的一条记录
	 * 
	 */
	void deleteById(Connection conn,int id);
	
	
	/**
	 * 针对内存中的 cust 对象，去修改数据表中指定的记录
	 * 
	 */
	void update(Connection conn,Customer cust);
	
	
	/**
	 * 针对于指定的 id 查询得到对应的 Customer 对象 
	 * 
	 */
	Customer getCustomerById(Connection conn,int id);
	
	
	/**
	 * 查询表中的所有记录构成的集合
	 * 
	 */
	List<Customer> getAll(Connection conn);
	
	
	/**
	 * 返回数据表中的数据的条目数
	 * 
	 */
	Long getCount(Connection conn);
	
	
	/**
	 * 返回数据表中最大的生日
	 * 
	 */
	Date getMaxBirth(Connection conn);
}

package com.atguigu3.dao;

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

import com.atguigu2.bean.Customer;

public class CustomerDAOImpl extends BaseDAO<Customer> implements CustomerDAO {
	
	@Override
	public void insert(Connection conn, Customer cust) {
		String sql = "insert into customers(name,email,birth)values(?,?,?)";
		update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth());
	}

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

	@Override
	public void update(Connection conn, Customer cust) {
		String sql = "update customers set name = ?,email = ?,birth = ? where id = ?";
		update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());
	}

	@Override
	public Customer getCustomerById(Connection conn, int id) {
		String sql = "select id,name,email,birth from customers where id = ?";
		Customer customer = getInstance(conn,/*Customer.class BaseDAO没加泛型时,*/sql,id);
		return customer;
	}

	@Override
	public List<Customer> getAll(Connection conn) {
		String sql = "select id,name,email,birth from customers";
		List<Customer> list = getForList(conn,/*Customer.class BaseDAO没加泛型时,*/sql);
		return list;
	}

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

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

}

com.atguigu3.dao.junit 包

package com.atguigu3.dao.junit;

import static org.junit.Assert.*;

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

import org.junit.Test;

import com.atguigu1.util.JDBCUtils;
import com.atguigu2.bean.Customer;
import com.atguigu3.dao.CustomerDAOImpl;

public class CustomerDAOImplTest {
	
	private CustomerDAOImpl dao = new CustomerDAOImpl();

	@Test
	public void testInsert() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			Customer cust = new Customer(1,"于小飞","xiaofei@126.com",new Date(23453453232L));
			dao.insert(conn, cust);
			System.out.println("添加成功");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testDeleteById() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			
			dao.deleteById(conn, 13);
			
			System.out.println("删除成功");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testUpdateConnectionCustomer() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			
			Customer cust = new Customer(18,"贝多芬","beiduofen@126.com",new Date(3425214134L));
			dao.update(conn, cust);
			
			System.out.println("修改成功");
			// 修改成功
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testGetCustomerById() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			
			Customer cust = dao.getCustomerById(conn, 19);
			System.out.println(cust);
			
			System.out.println("添加成功");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testGetAll() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			
			List<Customer> list = dao.getAll(conn);
			list.forEach(System.out::println);
			
			System.out.println("");
			
//			Customer [id=1, name=汪峰, email=wf@126.com, birth=2010-02-02]
//			Customer [id=2, name=王菲, email=wangf@163.com, birth=1988-12-26]
//			Customer [id=4, name=汤唯, email=tangw@sina.com, birth=1986-06-13]
//			Customer [id=5, name=成龙, email=Jackey@gmai.com, birth=1955-07-14]
//			Customer [id=6, name=迪丽热巴, email=reba@163.com, birth=1983-05-17]
//			Customer [id=7, name=刘亦菲, email=liuyifei@qq.com, birth=1991-11-14]
//			Customer [id=8, name=陈道明, email=bdf@126.com, birth=2014-01-17]
//			Customer [id=10, name=周杰伦, email=zhoujl@sina.com, birth=1979-11-15]
//			Customer [id=12, name=黎明, email=LiM@126.com, birth=1998-09-08]
//			Customer [id=13, name=张学友, email=zhangxy@126.com, birth=1998-12-21]
//			Customer [id=16, name=朱茵, email=zhuyin@126.com, birth=2014-01-16]
//			Customer [id=18, name=贝多芬, email=beiduofen@126.com, birth=1970-02-09]
//			Customer [id=19, name=哪吒, email=nezha@gmail.com, birth=1000-01-01]
//			Customer [id=22, name=孙权, email=sunquan@126.com, birth=1999-01-01]
//			Customer [id=23, name=李阿豪, email=li@qq.com, birth=1992-09-08]
//			Customer [id=24, name=元昊, email=yuanhao@qq.com, birth=1992-09-08]

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testGetCount() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			
			Long count = dao.getCount(conn);
			
			System.out.println("表中的记录数为: " + count);
			// 表中的记录数为: 16
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testGetMaxBirth() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			
			Date maxBirth = dao.getMaxBirth(conn);
			
			System.out.println("最大的生日为: " + maxBirth);
			// 最大的生日为: 2014-01-17
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}

}