1.封装数据库连接和关闭操作
*由于每次进行增删改操作都要执行数据库连接和资源关闭的操作,所以将这两个操作封装成一个工具类
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JDBCUtiles {
//获取连接
public static Connection getConnection() throws SQLException, IOException, ClassNotFoundException {
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pro = new Properties();
pro.load(is);
//读配置文件
String user = pro.getProperty("user");
String password = pro.getProperty("password");
String url = pro.getProperty("url");
String driverClass = pro.getProperty("driverClass");
//注册驱动
Class.forName(driverClass);
//获取连接
return DriverManager.getConnection(url, user, password);
}
//关闭资源
public static void closeResource(Connection conn, PreparedStatement ps){
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void closeResource(Connection conn, PreparedStatement ps, ResultSet rs){
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2.使用PrepartedStatment实现通用的增删改操作
public void update(String sql,Object ...args){
//由于增删改操作sql语句和占位符不一样,所以把二者作为参数传入
//由于占位符的个数不确定,所以使用可变形参
Connection conn = null;
PreparedStatement ps = null;
try {
conn = JDBCUtiles.getConnection();
ps = conn.prepareStatement(sql);
//填充占位符
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1,args[i]);
}
ps.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtiles.closeResource(conn,ps);
}
}
3.使用PrepartedStatment实现通用的查操作
//使用PrepartedStatment实现通用的查询操作
//sql语句和占位不一样
public <T> ArrayList<T> queryForCustomers(Class<T> clazz,String sql, Object ...args) {
ArrayList<T> ts = new ArrayList<T>();
Connection conn = null;
PreparedStatement ps = null;
ResultSet resultSet = null;
try {
conn = JDBCUtiles.getConnection();
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i+1,args[i]);
}
resultSet = ps.executeQuery();
//获取结果集的元数据
ResultSetMetaData metaData = resultSet.getMetaData();
//获取结果集的列数
int columnCount = metaData.getColumnCount();
while (resultSet.next()){
T t = clazz.newInstance();
for (int i = 0; i < columnCount; i++) {
Object columnValue = resultSet.getObject(i + 1);
//给customer赋值.需要知道列名
//获取value对应的列名
String columnName = metaData.getColumnLabel(i + 1);
//*********************************************************//
//通过反射给customer的指定属性赋值
Field field = clazz.getDeclaredField(columnName);
field.setAccessible(true);
field.set(t,columnValue);
}
ts.add(t);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtiles.closeResource(conn,ps,resultSet);
}
return ts;
}