JDBC核心基础——考虑事务后的代码实现和优化DAO类

本文链接：https://blog.csdn.net/LEXUS_SPEED/article/details/104774615

〇：考虑了数据库事务后的代码实现步骤

获取数据库的连接
1. 可以选择手动获取连接
2. 可以选择数据库连接池
事务的体现：conn.setAutoCommit(false);
诸多的 DML 操作，作为一个事务出现
1. 可以选择手动使用 PreparedStatement 实现
2. 可以选择使用 Apache 提供的 dbutils.jar 中的 QueryRunner 类
如果出现了异常就：conn.rollback();
关闭资源
1. 可以选择手动关闭资源
2. 可以选择使用 Apache 提供的 Dbutils 类的关闭方法

I：提供一个操作数据表的基本的DAO类（Version 2.0）

在 BaseDAO 抽象类中声明一个 T 类型的泛型参数
在 BaseDAO 类中声明一个 Class<T> 类型的属性 clazz，提供给内部的方法使用
获取对象前对 clazz 属性进行赋值，可以使用显式，构造器，代码块赋值，此处使用代码块赋值
获取当前对象的类，再获取此类的带泛型的父类 gsc
将带泛型的父类 gsc 强转成 pt
通过 pt 调用方法获取父类泛型的参数数组 aty
获取泛型的第一个参数进行强转并给 clazz 赋值

@SuppressWarnings("unchecked")
public abstract class BaseDAO<T> {
	private Class<T> clazz = null;

	{
		Type gsc = this.getClass().getGenericSuperclass();
		ParameterizedType pt = (ParameterizedType) gsc;
		Type[] aty = pt.getActualTypeArguments();
		clazz = (Class<T>) aty[0];
	}

	public int Update(Connection conn, String sql, Object... args) {
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			int i = ps.executeUpdate();
			return i;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps);
		}
		return 0;
	}

	public T Query(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();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			if (rs.next()) {
				T t = clazz.newInstance();
				for (int i = 0; i < columnCount; i++) {
					Object columnValue = rs.getObject(i + 1);
					String ColumnLabel = rsmd.getColumnLabel(i + 1);
					Field field = clazz.getDeclaredField(ColumnLabel);
					field.setAccessible(true);
					field.set(t, columnValue);
				}
				return t;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps, rs);
		}
		return null;
	}

	public List<T> QueryAll(Connection conn, String sql, Object... args) {
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<T> list;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			rs = ps.executeQuery();
			ResultSetMetaData rsmd = rs.getMetaData();
			int columnCount = rsmd.getColumnCount();
			list = new ArrayList<T>();
			while (rs.next()) {
				T t = clazz.newInstance();
				for (int i = 0; i < columnCount; i++) {
					Object columnValue = rs.getObject(i + 1);
					String ColumnLabel = rsmd.getColumnLabel(i + 1);
					Field field = clazz.getDeclaredField(ColumnLabel);
					field.setAccessible(true);
					field.set(t, columnValue);
				}
				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;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				ps.setObject(i + 1, args[i]);
			}
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				Object object = rs.getObject(1);
				return (E) object;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(null, ps);
		}
		return null;
	}
}

II：操作指定数据表的接口

public interface CustomerDAO {
	void insert(Connection conn, Customer cust);

	void deleteById(Connection conn, int id);

	void update(Connection conn, Customer cust);

	Customer getCustomerById(Connection conn, int id);

	List<Customer> getAll(Connection conn);

	Long getCount(Connection conn);
	
	Date getMaxBirth(Connection conn);
}

III：升级操作指定数据表的类（Version 2.0）

在继承父类 BaseDAO 的泛型参数中指明要操作的具体的类，此处为 Customer 类
设法获取父类的泛型参数，才可以使用反射机制，为 BaseDAO 中的方法获取运行时类的对象

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 = Query(conn, sql, id);
		return customer;
	}

	@Override
	public List<Customer> getAll(Connection conn) {
		String sql = "select id,name,email,birth from customers";
		List<Customer> list = QueryAll(conn, 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);
	}
}

IV：对实现类中所实现的方法进行单元测试（Version 2.0）

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

	@Test
	public void testInsert() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
			java.util.Date date = sdf.parse("2020-3-8");
			Customer cust = new Customer("尚硅谷", "atguigu@gmail.com", new java.sql.Date(date.getTime()));
			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, 25);
			System.out.println("删除成功");
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}

	@Test
	public void testUpdate() {
		Connection conn = null;
		try {
			conn = JDBCUtils.getConnection();
			Customer cust = new Customer(21, "康师傅", "shkstart@gmail.com", new java.sql.Date(3235879865235L));
			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, 21);
			System.out.println(cust);
		} 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);
			Iterator<Customer> iterator = list.iterator();
			while (iterator.hasNext()) {
				Customer customer = iterator.next();
				System.out.println(customer);
			}
		} 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);
		} 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);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.closeResource(conn, null);
		}
	}
}