PreparedStatement针对不同表的通用查询操作
备注：看不懂该文章可以先看JDBC-03：PreparedStatement如何实现对数据库的增删改查操作
可以理解为其一个叠加版本
查询操作1：获取一个对象
使用PreparedStatement针对不同表的通用查询操作
代码准备
- 已经包装好的JDBCUtils类（详情见jdbc03）
package com.util;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/*
操作数据库的工具类
*/
public class JDBCUtils {
/*
获取数据库的连接
*/
public static Connection getCollections() throws Exception {
//1.读取配置文件中的4个基本信息（通过类的加载器）
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties p1 = new Properties();
p1.load(is);
String user = p1.getProperty("user");
String password = p1.getProperty("password");
String url = p1.getProperty("url");
String driverClass = p1.getProperty("driverClass");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
Connection c3 = DriverManager.getConnection(url, user, password);
return c3;
}
public static void closeResource(Connection connection, Statement ps){
try {
if(connection!=null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (ps!=null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
//关闭资源的操作
public static void closeResource(Connection connection, Statement ps, ResultSet resultSet){
try {
if(connection!=null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (ps!=null)
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(resultSet!=null)
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
- Customer类
/*
*/
package com.jsm2;
import java.sql.Date;
public class Customer {
private int id;
private String name;
private String email;
private Date birth;
public Customer() {
}
public Customer(int id, String name, String email, Date birth) {
this.id = id;
this.name = name;
this.email = email;
this.birth = birth;
}
@Override
public String toString() {
return "Customer{" +
"id=" + id +
", name='" + name + '\'' +
", email='" + email + '\'' +
", 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;
}
}
- Order类
package com.jsm2;
import java.sql.Date;
public class Order {
private int orderId;
private String orderName;
private Date orderDate;
public Order(int orderId, String orderName, Date orderDate) {
this.orderId = orderId;
this.orderName = orderName;
this.orderDate = orderDate;
}
public Order() {
}
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 "OrderTest{" +
"orderId=" + orderId +
", orderName='" + orderName + '\'' +
", orderDate=" + orderDate +
'}';
}
}
- 原本的针对Order表的查询（详细也看jdbc03，文章片头有连接）
@Test
public static Order test3(String sql,Object...args) throws Exception {
Connection conn = JDBCUtils.getCollections();
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 0; i <args.length ; i++) {
ps.setObject(i+1,args[i]);//填充占位符
}
//执行，获取结果集
ResultSet rs = ps.executeQuery();
//获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
//获取列数
int columnCount = rsmd.getColumnCount();
if (rs.next()){
Order order = new Order();
for (int i = 0; i <columnCount ; i++) {
//获取每个列的列值：通过结果集：ResultSet
Object value = rs.getObject(i + 1);
//获取每个列的列名：通过：ResultSetMetaData
//获取列的列名：getColumnName()
//获取列的别名：getColumnLabel()
// String columnName = rsmd.getColumnName(i+1);-----不推荐使用
String columnLabel = rsmd.getColumnLabel(i+1);
//按照之前针对Customer的方法来说下面需要反射了
//通过反射：将对象指定名columnName的属性赋值为指定的值columnValue
Field field = Order.class.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(order,value);
}
return order;
}
JDBCUtils.closeResource(conn,ps,rs);
return null;
}
对上述代码分析：若要针对不同的表要写通用的操作，就找不通用的地方，首先连接数据库一样的，填充占位符，等等其实都一样，在第17行18行代码开始，要新建Order类的对象的时候开始不通用，因为不同的表要新建的数据肯定是不一样的，这里new了什么对象页决定了返回了什么对象，这里的解决办法是参数中要加入泛型，如下
public <T>T test3(Class<T> clazz,String sql, Object...args) throws Exception {
如上，加入泛型参数并且也设置为泛型类，这样T就决定了返回的对象就是T类型，这里带泛型参数就把方法也写成泛型方法
泛型见：Java泛型01：什么是泛型？
解决上述出现的问题
代码：1（并未实现最后结果，循序渐进看过程）
/*
*/
package com.jsm3;
import com.jsm2.Customer;
import com.jsm2.Order;
import com.util.JDBCUtils;
import org.junit.Test;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
/*
使用PreparedStatement针对不同表的通用查询操作
*/
public class Test1 {
@Test
public void test()throws Exception{
String sql="select id,name,email from customers where id = ?";
Customer customer = test3(Customer.class, sql, 12);
System.out.println(customer);
}
public <T>T test3(Class<T> clazz,String sql, Object...args) throws Exception {
Connection conn = JDBCUtils.getCollections();
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 0; i <args.length ; i++) {
ps.setObject(i+1,args[i]);//填充占位符
}
ResultSet 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 value = rs.getObject(i + 1);
String columnLabel = rsmd.getColumnLabel(i+1);
Field field = Customer.class.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,value);
}
return t;
}
JDBCUtils.closeResource(conn,ps,rs);
return null;
}
}
代码解释：这里的“通用方法”测试了Customer类，结果是出来了的运行正确的，但是如果换成别的类，是不行的，所以这里这样子加泛型，还是没有彻底解决问题，见44行代码处的Customer.class，应该改为clazz，最终代码如下
package com.jsm3;
import com.jsm2.Customer;
import com.util.JDBCUtils;
import org.junit.Test;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
/*
使用PreparedStatement针对不同表的通用查询操作
*/
public class Test11 {
@Test
public void test()throws Exception{
String sql="select id,name,email from customers where id = ?";
Customer customer = test3(Customer.class, sql, 12);
System.out.println(customer);
}
public <T>T test3(Class<T> clazz,String sql, Object...args) throws Exception {
Connection conn = JDBCUtils.getCollections();
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 0; i <args.length ; i++) {
ps.setObject(i+1,args[i]);
}
ResultSet 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 value = rs.getObject(i + 1);
String columnLabel = rsmd.getColumnLabel(i+1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,value);
}
return t;
}
JDBCUtils.closeResource(conn,ps,rs);
return null;
}
}
查询操作2：获取多个对象
返回多条记录，不再是一条记录
package com.jsm3;
import com.jsm2.Customer;
import com.util.JDBCUtils;
import org.junit.Test;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.List;
/*
使用PreparedStatement针对不同表的通用查询操作，获取多条信息
*/
public class Test12 {
@Test
public void testTest() throws Exception{
String sql="select id,name,email from customers where id < ? ";
List<Customer> list1 = test4(Customer.class, sql, 12);
list1.forEach(System.out::println);
}
public <T> List<T> test4(Class<T> clazz, String sql, Object... args) throws Exception {
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getCollections();
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<T> list = new ArrayList<>();
while (rs.next()) {
T t = clazz.newInstance();
//给T对象指定的属性赋值的过程
for (int i = 0; i < columnCount; i++) {
Object value = rs.getObject(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, value);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(conn, ps, rs);
}
return null;
}
}
抛出异常版本代码（没有用try/catch）方便查看代码
@Test
public void testTest() throws Exception{
String sql="select id,name,email from customers where id < ? ";
List<Customer> list1 = test4(Customer.class, sql, 12);
list1.forEach(System.out::println);
/*
for(Object object:list1){
System.out.println(object);
}
*/
}
public <T> List<T> test4(Class<T> clazz, String sql, Object... args) throws Exception {
Connection conn = JDBCUtils.getCollections();
PreparedStatement ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);//填充占位符
}
ResultSet rs = ps.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
//创建集合对象：
ArrayList<T> list = new ArrayList<>();
while (rs.next()) {
T t = clazz.newInstance();
//给T对象指定的属性赋值的过程
for (int i = 0; i < columnCount; i++) {
Object value = rs.getObject(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, value);
}
list.add(t);
}
JDBCUtils.closeResource(conn, ps, rs);
return list;
}
4055
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
