packagecom.JDBCStudy3.PreparedStatement.crud;importjava.awt.List;importjava.lang.reflect.Field;importjava.sql.Connection;importjava.sql.PreparedStatement;importjava.sql.ResultSet;importjava.sql.ResultSetMetaData;importjava.util.ArrayList;importorg.junit.Test;importJDBC_util.JDBCutils;/** 使用PrepareStatement实现针对于不同表的通用的查询操作
**/
public class PrepaerdStatementQueryTest{
@Testpublic voidtestGetInstance() {
String sql= "select id,name,email from customers where id = ?";
Customer customer= getInstance(Customer.class, sql, 12);
System.out.println(customer);
sql= "select order_id orderId,order_name orderName from `order` where order_id = ?";
Order order= getInstance(Order.class, sql, 1);
System.out.println(order);
}/** 针对不同的表的通用查询操作,返回表中的一条记录*/@SuppressWarnings("finally")public T getInstance(Classclazz, String sql, Object... args) {
Connection conn= null;
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 rsmd =rs.getMetaData();//获取列数
int columnCount =rsmd.getColumnCount();if(rs.next()) {
@SuppressWarnings("deprecation")
T t=clazz.newInstance();for (int i = 0; i < columnCount; i++) {//获取每个列的列值:通过ResultSet
Object columnValue = rs.getObject(i + 1);/** 通过ResultSetMetaData 获取列的列名:getColumnName() -- 不推荐使用 获取列的别名:getColumnLabel()*/
//String columnName = rsmd.getColumnName(i + 1);
String columnLabel =rsmd.getColumnLabel(i);//通过反射,将对象指定名columnName的属性赋值为指定的值columnValue
Field field = Order.class.getDeclaredField((String) columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}returnt;
}
}catch(Exception e) {
e.printStackTrace();
}finally{
JDBCutils.closeResource(conn, ps, rs);return null;
}
}public voidtestGetForList() {
String sql= "select id,name,email from customers where id < ?";
ArrayList list = getForList(Customer.class, sql, 12);
list.forEach(System.out::println);
sql= "select order_id orderId,order_name orderName from `order` where order_id < ?";
ArrayList list1 = getForList(Order.class, sql, 5);
list1.forEach(System.out::println);
}
@SuppressWarnings("finally")public ArrayList getForList(Classclazz, String sql, Object... args) {
Connection conn= null;
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 rsmd =rs.getMetaData();//获取列数
int columnCount =rsmd.getColumnCount();//创建集合对象
ArrayList list = new ArrayList();while(rs.next()) {
@SuppressWarnings("deprecation")
T t=clazz.newInstance();for (int i = 0; i < columnCount; i++) {//获取每个列的列值:给t对象指定的属性赋值
Object columnValue = rs.getObject(i + 1);/** 通过ResultSetMetaData 获取列的列名:getColumnName() -- 不推荐使用 获取列的别名:getColumnLabel()*/
//String columnName = rsmd.getColumnName(i + 1);
String columnLabel =rsmd.getColumnLabel(i);//通过反射,将对象指定名columnName的属性赋值为指定的值columnValue
Field field = Order.class.getDeclaredField((String) columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
list.add(t);
}returnlist;
}catch(Exception e) {
e.printStackTrace();
}finally{
JDBCutils.closeResource(conn, ps, rs);return null;
}
}
}