本文共 8754 字，大约阅读时间需要 29 分钟。
// 通用的增删改操作---version 2.0 （考虑上事务） 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.执行 return ps.executeUpdate(); } catch (Exception e) { e.printStackTrace(); } finally { // 4.资源的关闭 DBCUtil.close(null, ps,null); } return 0; } 测试：
@Test public void testUpdate() { Connection conn = null; try { conn = DBCUtil.getConnection(); System.out.println(conn.getAutoCommit());//true //1.取消数据的自动提交 conn.setAutoCommit(false); String sql1 = "update user_table set balance = balance - 100 where user = ?"; update(conn,sql1, "AA"); //模拟网络异常 System.out.println(10 / 0); String sql2 = "update user_table set balance = balance + 100 where user = ?"; update(conn,sql2, "BB"); System.out.println("转账成功"); //2.提交数据 conn.commit(); } catch (Exception e) { e.printStackTrace(); //3.回滚数据 try { conn.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } }finally{ DBCUtil.close(conn, null,null); } } publicT query(Connection conn,Class clazz,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 rsmd = rs.getMetaData(); 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); Field field = clazz.getDeclaredField(columnLabel); field.setAccessible(true); field.set(t,columnValue); } return t; } } catch (SQLException e) { e.printStackTrace(); } catch (InstantiationException e) { e.printStackTrace(); } catch (IllegalAccessException e) { e.printStackTrace(); } catch (NoSuchFieldException e) { e.printStackTrace(); } finally { DBCUtil.close(null,ps,rs); } return null; }
测试：
@Test public void testQuery(){ Connection conn = null; try { conn = DBCUtil.getConnection(); conn.setAutoCommit(false); String sql = "select user,password,balance from user_table where user = ?"; User user = query(conn, User.class, sql, "CC"); System.out.println(user); conn.commit(); } catch (Exception e) { e.printStackTrace(); if(conn != null){ try { conn.rollback(); } catch (SQLException ex) { ex.printStackTrace(); } } } finally { DBCUtil.close(conn,null,null); } } publicList getForList(Connection conn,Class clazz, 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 rsmd = rs.getMetaData(); int columnCount = rsmd.getColumnCount(); //创建集合对象 ArrayList list = new ArrayList<>(); while(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); Field field = clazz.getDeclaredField(columnLabel); field.setAccessible(true); field.set(t,columnValue); } list.add(t); } return list; } catch (Exception e) { e.printStackTrace(); } finally { DBCUtil.close(null,ps,rs); } return null; }
测试：
@Test public void testGetForList(){ Connection conn = null; try { conn = DBCUtil.getConnection(); conn.setAutoCommit(false); String sql = "select * from user_table"; List users = getForList(conn, User.class, sql); for (User user : users) { System.out.println(user); } conn.commit(); } catch (Exception e) { e.printStackTrace(); if(conn != null){ try { conn.rollback(); } catch (SQLException ex) { ex.printStackTrace(); } } } finally { DBCUtil.close(conn,null,null); } } //用于查询特殊值的通用的方法 publicE 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; }
package loey.DBCUtil;import java.io.IOException;import java.io.InputStream;import java.sql.*;import java.util.Properties;/** * JDBC工具类，简化JDBC编程 * */public class DBCUtil { /** * 工具类中的构造方法是私有的 * 因为工具类中的方法都是静态的，直接通过类名去调即可。 */ private DBCUtil() { } private static Properties getProperties(){ //ResourceBundle bundle = ResourceBundle.getBundle("jdbc"); //String driver = bundle.getString("driver"); //String url = bundle.getString("url"); //String user = bundle.getString("user"); //String password = bundle.getString("password"); //String sql = bundle.getString("sql"); // 1.读取配置文件中的4个基本信息 Properties pros = new Properties(); InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties"); try { pros.load(is); } catch (IOException e) { e.printStackTrace(); } return pros; } /** * 静态代码块，类加载的时候执行 * 把注册驱动程序的代码放在静态代码块中，避免多次获取连接对象时重复调用 */ static{ Properties pros = getProperties(); String driver = pros.getProperty("driver"); try { Class.forName(driver); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /** * @return 获取连接 * @throws SQLException */ public static Connection getConnection() throws Exception { Properties pros = getProperties(); String url = pros.getProperty("url"); String user = pros.getProperty("user"); String password = pros.getProperty("password"); Connection conn = DriverManager.getConnection(url, user, password); return conn; } public static void close(Connection conn, Statement ps, ResultSet rs) { if (rs != null) { try { rs.close(); } catch (SQLException e) { e.printStackTrace(); } } if (ps != null) { try { ps.close(); } catch (SQLException e) { e.printStackTrace(); } } if (conn != null) { try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } }} jdbc.properties
driver=com.mysql.jdbc.Driverurl=jdbc:mysql://localhost:3306/testuser=rootpassword=1127
转载地址：http://yauki.baihongyu.com/