DAO详解
day20
DAO的实现代码
package com.atguigu.jdbc;
import java.lang.reflect.InvocationTargetException;
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.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.BeanUtils;
public class DAO {
// INSERT, UPDATE, DELETE 操作都可以包含在其中
public void update(String sql, Object... args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCTools.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.releaseDB(null, preparedStatement, connection);
}
}
// 查询一条记录, 返回对应的对象
public <T> T get(Class<T> clazz, String sql, Object... args) {
List<T> result = getForList(clazz, sql, args);
if(result.size() > 0){
return result.get(0);
}
return null;
}
/**
* 传入 SQL 语句和 Class 对象, 返回 SQL 语句查询到的记录对应的 Class 类的对象的集合
* @param clazz: 对象的类型
* @param sql: SQL 语句
* @param args: 填充 SQL 语句的占位符的可变参数.
* @return
*/
public <T> List<T> getForList(Class<T> clazz,
String sql, Object... args) {
List<T> list = new ArrayList<>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
//1. 得到结果集
connection = JDBCTools.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
resultSet = preparedStatement.executeQuery();
//2. 处理结果集, 得到 Map 的 List, 其中一个 Map 对象
//就是一条记录. Map 的 key 为 reusltSet 中列的别名, Map 的 value
//为列的值.
List<Map<String, Object>> values =
handleResultSetToMapList(resultSet);
//3. 把 Map 的 List 转为 clazz 对应的 List
//其中 Map 的 key 即为 clazz 对应的对象的 propertyName,
//而 Map 的 value 即为 clazz 对应的对象的 propertyValue
list = transfterMapListToBeanList(clazz, values);
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.releaseDB(resultSet, preparedStatement, connection);
}
return list;
}
public <T> List<T> transfterMapListToBeanList(Class<T> clazz,
List<Map<String, Object>> values) throws InstantiationException,
IllegalAccessException, InvocationTargetException {
List<T> result = new ArrayList<>();
T bean = null;
if (values.size() > 0) {
for (Map<String, Object> m : values) {
bean = clazz.newInstance();
for (Map.Entry<String, Object> entry : m.entrySet()) {
String propertyName = entry.getKey();
Object value = entry.getValue();
BeanUtils.setProperty(bean, propertyName, value);
}
// 13. 把 Object 对象放入到 list 中.
result.add(bean);
}
}
return result;
}
/**
* 处理结果集, 得到 Map 的一个 List, 其中一个 Map 对象对应一条记录
*
* @param resultSet
* @return
* @throws SQLException
*/
public List<Map<String, Object>> handleResultSetToMapList(
ResultSet resultSet) throws SQLException {
// 5. 准备一个 List<Map<String, Object>>:
// 键: 存放列的别名, 值: 存放列的值. 其中一个 Map 对象对应着一条记录
List<Map<String, Object>> values = new ArrayList<>();
List<String> columnLabels = getColumnLabels(resultSet);
Map<String, Object> map = null;
// 7. 处理 ResultSet, 使用 while 循环
while (resultSet.next()) {
map = new HashMap<>();
for (String columnLabel : columnLabels) {
Object value = resultSet.getObject(columnLabel);
map.put(columnLabel, value);
}
// 11. 把一条记录的一个 Map 对象放入 5 准备的 List 中
values.add(map);
}
return values;
}
/**
* 获取结果集的 ColumnLabel 对应的 List
*
* @param rs
* @return
* @throws SQLException
*/
private List<String> getColumnLabels(ResultSet rs) throws SQLException {
List<String> labels = new ArrayList<>();
ResultSetMetaData rsmd = rs.getMetaData();
for (int i = 0; i < rsmd.getColumnCount(); i++) {
labels.add(rsmd.getColumnLabel(i + 1));
}
return labels;
}
// 返回某条记录的某一个字段的值 或 一个统计的值(一共有多少条记录等.)
public <E> E getForValue(String sql, Object... args) {
//1. 得到结果集: 该结果集应该只有一行, 且只有一列
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
//1. 得到结果集
connection = JDBCTools.getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
return (E) resultSet.getObject(1);
}
} catch(Exception ex){
ex.printStackTrace();
} finally{
JDBCTools.releaseDB(resultSet, preparedStatement, connection);
}
//2. 取得结果
return null;
}
}
JDBCTools
package com.atguigu.jdbc;
import java.io.IOException;
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;
/**
* JDBC 的工具类
*
* 其中包含: 获取数据库连接, 关闭数据库资源等方法.
*/
public class JDBCTools {
public static Connection getConnection() throws Exception {
Properties properties = new Properties();
InputStream inStream = JDBCTools.class.getClassLoader()
.getResourceAsStream("jdbc.properties");
properties.load(inStream);
// 1. 准备获取连接的 4 个字符串: user, password, jdbcUrl, driverClass
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String jdbcUrl = properties.getProperty("jdbcUrl");
String driverClass = properties.getProperty("driverClass");
// 2. 加载驱动: Class.forName(driverClass)
Class.forName(driverClass);
// 3. 调用
// DriverManager.getConnection(jdbcUrl, user, password)
// 获取数据库连接
Connection connection = DriverManager.getConnection(jdbcUrl, user,
password);
return connection;
}
public static void releaseDB(ResultSet resultSet, Statement statement,
Connection connection) {
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
ReflectionUtils.java
package com.atguigu.jdbc;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
/**
* 反射的 Utils 函数集合
* 提供访问私有变量, 获取泛型类型 Class, 提取集合中元素属性等 Utils 函数
* @author Administrator
*
*/
public class ReflectionUtils {
/**
* 通过反射, 获得定义 Class 时声明的父类的泛型参数的类型
* 如: public EmployeeDao extends BaseDao<Employee, String>
* @param clazz
* @param index
* @return
*/
@SuppressWarnings("unchecked")
public static Class getSuperClassGenricType(Class clazz, int index){
Type genType = clazz.getGenericSuperclass();
if(!(genType instanceof ParameterizedType)){
return Object.class;
}
Type [] params = ((ParameterizedType)genType).getActualTypeArguments();
if(index >= params.length || index < 0){
return Object.class;
}
if(!(params[index] instanceof Class)){
return Object.class;
}
return (Class) params[index];
}
/**
* 通过反射, 获得 Class 定义中声明的父类的泛型参数类型
* 如: public EmployeeDao extends BaseDao<Employee, String>
* @param <T>
* @param clazz
* @return
*/
@SuppressWarnings("unchecked")
public static<T> Class<T> getSuperGenericType(Class clazz){
return getSuperClassGenricType(clazz, 0);
}
/**
* 循环向上转型, 获取对象的 DeclaredMethod
* @param object
* @param methodName
* @param parameterTypes
* @return
*/
public static Method getDeclaredMethod(Object object, String methodName, Class<?>[] parameterTypes){
for(Class<?> superClass = object.getClass(); superClass != Object.class; superClass = superClass.getSuperclass()){
try {
//superClass.getMethod(methodName, parameterTypes);
return superClass.getDeclaredMethod(methodName, parameterTypes);
} catch (NoSuchMethodException e) {
//Method 不在当前类定义, 继续向上转型
}
//..
}
return null;
}
/**
* 使 filed 变为可访问
* @param field
*/
public static void makeAccessible(Field field){
if(!Modifier.isPublic(field.getModifiers())){
field.setAccessible(true);
}
}
/**
* 循环向上转型, 获取对象的 DeclaredField
* @param object
* @param filedName
* @return
*/
public static Field getDeclaredField(Object object, String filedName){
for(Class<?> superClass = object.getClass(); superClass != Object.class; superClass = superClass.getSuperclass()){
try {
return superClass.getDeclaredField(filedName);
} catch (NoSuchFieldException e) {
//Field 不在当前类定义, 继续向上转型
}
}
return null;
}
/**
* 直接调用对象方法, 而忽略修饰符(private, protected)
* @param object
* @param methodName
* @param parameterTypes
* @param parameters
* @return
* @throws InvocationTargetException
* @throws IllegalArgumentException
*/
public static Object invokeMethod(Object object, String methodName, Class<?> [] parameterTypes,
Object [] parameters) throws InvocationTargetException{
Method method = getDeclaredMethod(object, methodName, parameterTypes);
if(method == null){
throw new IllegalArgumentException("Could not find method [" + methodName + "] on target [" + object + "]");
}
method.setAccessible(true);
try {
return method.invoke(object, parameters);
} catch(IllegalAccessException e) {
System.out.println("不可能抛出的异常");
}
return null;
}
/**
* 直接设置对象属性值, 忽略 private/protected 修饰符, 也不经过 setter
* @param object
* @param fieldName
* @param value
*/
public static void setFieldValue(Object object, String fieldName, Object value){
Field field = getDeclaredField(object, fieldName);
if (field == null)
throw new IllegalArgumentException("Could not find field [" + fieldName + "] on target [" + object + "]");
makeAccessible(field);
try {
field.set(object, value);
} catch (IllegalAccessException e) {
System.out.println("不可能抛出的异常");
}
}
/**
* 直接读取对象的属性值, 忽略 private/protected 修饰符, 也不经过 getter
* @param object
* @param fieldName
* @return
*/
public static Object getFieldValue(Object object, String fieldName){
Field field = getDeclaredField(object, fieldName);
if (field == null)
throw new IllegalArgumentException("Could not find field [" + fieldName + "] on target [" + object + "]");
makeAccessible(field);
Object result = null;
try {
result = field.get(object);
} catch (IllegalAccessException e) {
System.out.println("不可能抛出的异常");
}
return result;
}
}
MetaData.java
package com.atguigu.jdbc;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import org.junit.Test;
public class MetaDataTest {
/**
* ResultSetMetaData: 描述结果集的元数据.
* 可以得到结果集中的基本信息: 结果集中有哪些列, 列名, 列的别名等.
* 结合反射可以写出通用的查询方法.
*/
@Test
public void testResultSetMetaData(){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCTools.getConnection();
String sql = "SELECT id, name customerName, email, birth " +
"FROM customers";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
//1. 得到 ResultSetMetaData 对象
ResultSetMetaData rsmd = resultSet.getMetaData();
//2. 得到列的个数
int columnCount = rsmd.getColumnCount();
System.out.println(columnCount);
for(int i = 0 ; i < columnCount; i++){
//3. 得到列名
String columnName = rsmd.getColumnName(i + 1);
//4. 得到列的别名
String columnLabel = rsmd.getColumnLabel(i + 1);
System.out.println(columnName + ", " + columnLabel);
}
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCTools.releaseDB(resultSet, preparedStatement, connection);
}
}
/**
* DatabaseMetaData 是描述 数据库 的元数据对象.
* 可以由 Connection 得到.
* 了解.
*/
@Test
public void testDatabaseMetaData(){
Connection connection = null;
ResultSet resultSet = null;
try {
connection = JDBCTools.getConnection();
DatabaseMetaData data = connection.getMetaData();
//可以得到数据库本身的一些基本信息
//1. 得到数据库的版本号
int version = data.getDatabaseMajorVersion();
System.out.println(version);
//2. 得到连接到数据库的用户名
String user = data.getUserName();
System.out.println(user);
//3. 得到 MySQL 中有哪些数据库
resultSet = data.getCatalogs();
while(resultSet.next()){
System.out.println(resultSet.getString(1));
}
//...
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCTools.releaseDB(resultSet, null, connection);
}
}
}
TranscationTest.java
package com.atguigu.jdbc;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
public class TransactionTest {
/**
* 测试事务的隔离级别 在 JDBC 程序中可以通过 Connection 的 setTransactionIsolation 来设置事务的隔离级别.
*/
@Test
public void testTransactionIsolationUpdate() {
Connection connection = null;
try {
connection = JDBCTools.getConnection();
connection.setAutoCommit(false);
String sql = "UPDATE users SET balance = "
+ "balance - 500 WHERE id = 1";
update(connection, sql);
connection.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
}
}
@Test
public void testTransactionIsolationRead() {
String sql = "SELECT balance FROM users WHERE id = 1";
Integer balance = getForValue(sql);
System.out.println(balance);
}
// 返回某条记录的某一个字段的值 或 一个统计的值(一共有多少条记录等.)
public <E> E getForValue(String sql, Object... args) {
// 1. 得到结果集: 该结果集应该只有一行, 且只有一列
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
// 1. 得到结果集
connection = JDBCTools.getConnection();
System.out.println(connection.getTransactionIsolation());
// connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);
connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
return (E) resultSet.getObject(1);
}
} catch (Exception ex) {
ex.printStackTrace();
} finally {
JDBCTools.releaseDB(resultSet, preparedStatement, connection);
}
// 2. 取得结果
return null;
}
/**
* Tom 给 Jerry 汇款 500 元.
*
* 关于事务: 1. 如果多个操作, 每个操作使用的是自己的单独的连接, 则无法保证事务. 2. 具体步骤: 1). 事务操作开始前, 开始事务:
* 取消 Connection 的默认提交行为. connection.setAutoCommit(false); 2). 如果事务的操作都成功,
* 则提交事务: connection.commit(); 3). 回滚事务: 若出现异常, 则在 catch 块中回滚事务:
*/
@Test
public void testTransaction() {
Connection connection = null;
try {
connection = JDBCTools.getConnection();
System.out.println(connection.getAutoCommit());
// 开始事务: 取消默认提交.
connection.setAutoCommit(false);
String sql = "UPDATE users SET balance = "
+ "balance - 500 WHERE id = 1";
update(connection, sql);
int i = 10 / 0;
System.out.println(i);
sql = "UPDATE users SET balance = " + "balance + 500 WHERE id = 2";
update(connection, sql);
// 提交事务
connection.commit();
} catch (Exception e) {
e.printStackTrace();
// 回滚事务
try {
connection.rollback();
} catch (SQLException e1) {
e1.printStackTrace();
}
} finally {
JDBCTools.releaseDB(null, null, connection);
}
/*
* try {
*
* //开始事务: 取消默认提交. connection.setAutoCommit(false);
*
* //...
*
* //提交事务 connection.commit(); } catch (Exception e) { //...
*
* //回滚事务 try { connection.rollback(); } catch (SQLException e1) {
* e1.printStackTrace(); } } finally{ JDBCTools.releaseDB(null, null,
* connection); }
*/
// DAO dao = new DAO();
//
// String sql = "UPDATE users SET balance = " +
// "balance - 500 WHERE id = 1";
// dao.update(sql);
//
// int i = 10 / 0;
// System.out.println(i);
//
// sql = "UPDATE users SET balance = " +
// "balance + 500 WHERE id = 2";
// dao.update(sql);
}
public void update(Connection connection, String sql, Object... args) {
PreparedStatement preparedStatement = null;
try {
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCTools.releaseDB(null, preparedStatement, null);
}
}
}
Student.java
package com.atguigu.jdbc;
public class Student {
// 流水号
private int flowId;
// 考试的类型
private int type;
// 身份证号
private String idCard;
// 准考证号
private String examCard;
// 学生名
private String studentName;
// 学生地址
private String location;
// 考试分数.
private int grade;
public int getFlowId() {
return flowId;
}
public void setFlowId(int flowId) {
this.flowId = flowId;
}
public int getType() {
return type;
}
public void setType(int type) {
this.type = type;
}
//getIdCard -- idCard
public String getIdCard() {
return idCard;
}
public void setIdCard(String idCard) {
this.idCard = idCard;
}
public String getExamCard() {
return examCard;
}
public void setExamCard(String examCard) {
this.examCard = examCard;
}
public String getStudentName() {
return studentName;
}
public void setStudentName(String studentName) {
this.studentName = studentName;
}
public String getLocation() {
return location;
}
public void setLocation(String location) {
this.location = location;
}
public int getGrade() {
return grade;
}
public void setGrade(int grade) {
this.grade = grade;
}
public Student(int flowId, int type, String idCard, String examCard,
String studentName, String location, int grade) {
super();
this.flowId = flowId;
this.type = type;
this.idCard = idCard;
this.examCard = examCard;
this.studentName = studentName;
this.location = location;
this.grade = grade;
}
public Student() {
// TODO Auto-generated constructor stub
}
@Override
public String toString() {
return "Student [flowId=" + flowId + ", type=" + type + ", idCard="
+ idCard + ", examCard=" + examCard + ", studentName="
+ studentName + ", location=" + location + ", grade=" + grade
+ "]\n";
}
}
JDBCToolsTest.java
package com.atguigu.jdbc;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import org.junit.Test;
public class JDBCTest {
/**
* 读取 blob 数据:
* 1. 使用 getBlob 方法读取到 Blob 对象
* 2. 调用 Blob 的 getBinaryStream() 方法得到输入流。再使用 IO 操作即可.
*/
@Test
public void readBlob(){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCTools.getConnection();
String sql = "SELECT id, name customerName, email, birth, picture "
+ "FROM customers WHERE id = 13";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
if(resultSet.next()){
int id = resultSet.getInt(1);
String name = resultSet.getString(2);
String email = resultSet.getString(3);
System.out.println(id + ", " + name + ", " + email);
Blob picture = resultSet.getBlob(5);
InputStream in = picture.getBinaryStream();
System.out.println(in.available());
OutputStream out = new FileOutputStream("flower.jpg");
byte [] buffer = new byte[1024];
int len = 0;
while((len = in.read(buffer)) != -1){
out.write(buffer, 0, len);
}
in.close();
out.close();
}
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCTools.releaseDB(resultSet, preparedStatement, connection);
}
}
/**
* 插入 BLOB 类型的数据必须使用 PreparedStatement：因为 BLOB 类型
* 的数据时无法使用字符串拼写的。
*
* 调用 setBlob(int index, InputStream inputStream)
*/
@Test
public void testInsertBlob(){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCTools.getConnection();
String sql = "INSERT INTO customers(name, email, birth, picture)"
+ "VALUES(?,?,?,?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, "ABCDE");
preparedStatement.setString(2, "[email protected]");
preparedStatement.setDate(3,
new Date(new java.util.Date().getTime()));
InputStream inputStream = new FileInputStream("Hydrangeas.jpg");
preparedStatement.setBlob(4, inputStream);
preparedStatement.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCTools.releaseDB(null, preparedStatement, connection);
}
}
/**
* 取得数据库自动生成的主键
*/
@Test
public void testGetKeyValue() {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCTools.getConnection();
String sql = "INSERT INTO customers(name, email, birth)" +
"VALUES(?,?,?)";
// preparedStatement = connection.prepareStatement(sql);
//使用重载的 prepareStatement(sql, flag)
//来生成 PreparedStatement 对象
preparedStatement = connection.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS);
preparedStatement.setString(1, "ABCDE");
preparedStatement.setString(2, "[email protected]");
preparedStatement.setDate(3,
new Date(new java.util.Date().getTime()));
preparedStatement.executeUpdate();
//通过 getGeneratedKeys() 获取包含了新生成的主键的 ResultSet 对象
//在 ResultSet 中只有一列 GENERATED_KEY, 用于存放新生成的主键值.
ResultSet rs = preparedStatement.getGeneratedKeys();
if(rs.next()){
System.out.println(rs.getObject(1));
}
ResultSetMetaData rsmd = rs.getMetaData();
for(int i = 0; i < rsmd.getColumnCount(); i++){
System.out.println(rsmd.getColumnName(i + 1));
}
} catch (Exception e) {
e.printStackTrace();
} finally{
JDBCTools.releaseDB(null, preparedStatement, connection);
}
}
}
BeanUtilsTest.java
package com.atguigu.jdbc;
import java.lang.reflect.InvocationTargetException;
import org.apache.commons.beanutils.BeanUtils;
import org.junit.Test;
public class BeanUtilsTest {
@Test
public void testGetProperty() throws IllegalAccessException, InvocationTargetException, NoSuchMethodException{
Object object = new Student();
System.out.println(object);
BeanUtils.setProperty(object, "idCard", "211121196509091876");
System.out.println(object);
Object val = BeanUtils.getProperty(object, "idCard");
System.out.println(val);
}
@Test
public void testSetProperty() throws IllegalAccessException, InvocationTargetException {
Object object = new Student();
System.out.println(object);
BeanUtils.setProperty(object, "idCard2", "211121196509091876");
System.out.println(object);
}
}