/**
* 由于本代码涉及到 IO流和数据库连接,需要关闭资源,因此对异常的正确处理
* 方式为:try...catch...finally。但这样做会颠倒步骤的顺序,代码的主体将
* 被不必要的捕捉异常所掩盖。为清晰显示步骤的前后连贯性,本代码使用throws
* 抛出异常。
*/
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @Description 操作数据库的工具类
*/
public class JDBCUtils {
//获取数据库的连接
public static Connection getConnection() throws Exception {
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//加载驱动
Class.forName(driverClass);
//获取连接
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
}
//资源的关闭
public static void closeResource(Connection conn, PreparedStatement ps){
//资源关闭
if(ps != null) {
try {
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if(conn != null) {
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
//用于查询语句中结果集的资源关闭
public static void closeResource1(Connection conn, PreparedStatement ps, ResultSet rs){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
ps.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
try {
rs.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public class Test1 (){
public <T> List<T> getForList(Class <T> clazz,String sql,Object...args) throws Exception {
Connection conn = JDBCUtils.getConnection();
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();
//通过ResultSetMetaData获取结果集的列数
int columnCount = rsmd.getColumnCount();
//创建集合对象
ArrayList<T> list = new ArrayList<T>();
while (rs.next()){
T t = clazz.newInstance();
//处理结果集一行数据中的每一个列:给t对象指定的属性赋值
for (int i = 0; i < columnCount; i++) {
//columnValue是该列的值
Object columnValue = rs.getObject(i + 1);
//获取每个列的列名
String columnLabel = rsmd.getColumnLabel(i + 1);
//给cust对象指定的columnName属性赋值为columnValue:通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columnValue);
}
list.add(t);
}
JDBCUtils.closeResource1(conn,ps,rs);
return list;
}
}
原数据库
//测试代码1
@Test
public void test3() throws Exception {
String sql = "select id,name,email from customers where id = ?";
List<Customer> list = getForList(Customer.class, sql, 12);
list.forEach(System.out::println);
}
测试代码1的结果
//测试代码2
@Test
public void test2() throws Exception {
String sql = "select id,name,email from customers where id < ?";
List<Customer> list = getForList(Customer.class, sql, 12);
list.forEach(System.out::println);
}
测试代码2的结果