通用建立连接操作
public void test4() throws Exception{
//1.读取配置文件中的4个基本信息
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");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
Connection conn = DriverManager.getConnection(url, user, password);
return conn;
jdbc.properties
user=root
password=soulxl
url=jdbc:mysql://localhost:3306/test
driverClass=com.mysql.cj.jdbc.Driver
通用关闭资源操作
//关闭资源操作(增删改)
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();
}
}
//关闭资源操作(查询)
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();
}
}
通用的增删改操作
public int update(Connection conn,String sql,Object ...args){//sql中占位符的个数与可变形参的长度相同!
PreparedStatement ps = null;
try {
//1.预编译sql语句,返回PreparedStatement的实例
ps = conn.prepareStatement(sql);
//2.填充占位符
for(int i = 0;i < args.length;i++){
ps.setObject(i + 1, args[i]);//小心参数声明错误!!
}
//3.执行
ps.executeUpdate();
} catch (Exception e) {
e.printStackTrace();
}finally{
//4.资源的关闭
JDBCUtils.closeResource(null, ps);
}
return 0;
}
1条数据的通用查询
public <T> T getInstance(Class<T> clazz,Connection conn,String sql, Object... args) {
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
// 获取结果集的元数据 :ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
// 通过ResultSetMetaData获取结果集中的列数
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
T t = clazz.getDeclaredConstructor().newInstance();
// 处理结果集一行数据中的每一个列
for (int i = 0; i < columnCount; i++) {
// 获取列值
Object columValue = rs.getObject(i + 1);
// 获取每个列的列名
// String columnName = rsmd.getColumnName(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
// 给t对象指定的columnName属性,赋值为columValue:通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columValue);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(null, ps, rs);
}
return null;
}
多条数据的通用查询
public <T> List<T> getForList(Class<T> clazz,Connection conn,String sql, Object... args){
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
for (int i = 0; i < args.length; i++) {
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
// 获取结果集的元数据 :ResultSetMetaData
ResultSetMetaData rsmd = rs.getMetaData();
// 通过ResultSetMetaData获取结果集中的列数
int columnCount = rsmd.getColumnCount();
//创建集合对象
ArrayList<T> list = new ArrayList<T>();
while (rs.next()) {
T t = clazz.getDeclaredConstructor().newInstance();
// 处理结果集一行数据中的每一个列:给t对象指定的属性赋值
for (int i = 0; i < columnCount; i++) {
// 获取列值
Object columValue = rs.getObject(i + 1);
// 获取每个列的列名
// String columnName = rsmd.getColumnName(i + 1);
String columnLabel = rsmd.getColumnLabel(i + 1);
// 给t对象指定的columnName属性,赋值为columValue:通过反射
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t, columValue);
}
list.add(t);
}
return list;
} catch (Exception e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(null, ps, rs);
}
return null;
}
批量插入数据
public void insertTest (){
Connection conn = null;//连接数据库
PreparedStatement ps = null;
try {
conn = JDBCUtils.getConnection();
ps = conn.prepareStatement("insert into customers/*表名*/(name/*属性名*/) values (?)");
conn.setAutoCommit(false);//设置不允许自动提交数据
for (int i = 0; i < 2/*次数*/; i++) {
ps.setObject(1/*属性列编号*/,"值" + i);
ps.addBatch();//攒sql
if (i % 500 == 0){//执行batch的条件
ps.executeBatch();//执行batch
ps.clearBatch();//清空batch
}
}
//提交数据
conn.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCUtils.closeResource(conn,ps);
}
}
用于查询特殊值的通用的方法
public <E> E getValue(Connection conn,String sql,Object...args){
PreparedStatement ps = null;
ResultSet rs = null;
try {
ps = conn.prepareStatement(sql);
for(int i = 0;i < args.length;i++){
ps.setObject(i + 1, args[i]);
}
rs = ps.executeQuery();
if(rs.next()){
return (E) rs.getObject(1);
}
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtils.closeResource(null, ps, rs);
}
return null;
}