- 获取单条数据
- 获取多条数据
一、查询单条数据
/**
* 功能描述:获取实体
* @param clazz
* @param sql
* @param objects
* @param <T>
* @return
*/
public <T> T getInstance(Class<T> clazz, String sql, Object... objects){
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try{
connection = JDBCUtils.getConnection();
ps = connection.prepareStatement(sql);
for (int i = 0; i < objects.length; i++) {
ps.setObject(i+1, objects[i]);
}
rs = ps.executeQuery();
// 获取结果集的元数据 :ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
// 通过ResultSetMetaData获取结果集中的列数
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);
// 给t对象指定的columnName属性,赋值为columValue:通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columnValue);
}
return t;
}
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(connection, ps, rs);
}
return null;
}
二、查询多条数据
public <T> List<T> queryList(Class<T> clazz, String sql, Object... args){
Connection connection = null;
PreparedStatement ps = null;
ResultSet rs = null;
try{
// 1、获取连接
connection = JDBCUtils.getConnection();
// 预编译
ps = connection.prepareStatement(sql);
if (args != null){
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
}
// 执行sql,获取结果集
rs = ps.executeQuery();
// 获取结果集的元数据
ResultSetMetaData rsmd = rs.getMetaData();
// 获取元数据的列数
int columnCount = rsmd.getColumnCount();
// 返回结果
List<T> resultList = new ArrayList<>();
while(rs.next()){
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
String columnLabel = rsmd.getColumnLabel(i + 1);
Object object = rs.getObject(i + 1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, object);
}
resultList.add(t);
}
return resultList;
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(connection, ps, rs);
}
return null;
}
JDBCUtils工具类
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* 功能描述:操作数据库的工具类
* @author leo-zu
* @date 2022-01-10
*
*/
public class JDBCUtils {
/**
* 功能描述:获取数据库连接
* @return 连接
* @throws Exception
*/
public static Connection getConnection() throws Exception{
// 1、加载配置文件
InputStream inputStream = JDBCUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
Properties pro = new Properties();
pro.load(inputStream);
String driverClass = pro.getProperty("driverClass");
String url = pro.getProperty("url");
String user = pro.getProperty("user");
String password = pro.getProperty("password");
// 2、加载驱动
Class.forName(driverClass);
// 3、获取连接
return DriverManager.getConnection(url, user, password);
}
/**
* 功能描述:关闭资源
* @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();
}
}
/**
* 功能描述:关闭资源
* @param conn
* @param ps
* @param rs
*/
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();
}
}
}