Java jdbc 连接数据库、通用增删改查、事务回滚
1.先下载mysql-connector-java-5.1.40.jar包;
2.写代码;
获取数据库连接、通用查询和同意增删改代码:
public static Connection getConnection() { Connection con = null; try { Class.forName("com.mysql.jdbc.Driver"); System.out.println("数据库驱动加载成功"); } catch (ClassNotFoundException e) { e.printStackTrace(); } try { con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/maxinhai?characterEncoding=UTF-8", "maxinhai", "maxinhai"); System.out.println("数据库连接成功"); } catch (SQLException e) { e.printStackTrace(); } return con; } /** * 执行增加、删除、修改 sql操作 * * @auther maxinhai * @param con 连接对象 * @param sql 增加、删除、修改sql语句 * @param params 条件参数 * @return 查询结果 */ public static int executeUpdate(Connection con, String sql, List<Object> params) { int num = 0; PreparedStatement ps = null; try { // 预处理对象 System.out.println(sql); ps = con.prepareStatement(sql); // 填充参数 if (params.size() > 0) { for (int i = 0; i < params.size(); i++) { ps.setObject(i + 1, params.get(i)); } } // 执行sql num = ps.executeUpdate(); System.out.println("影响了" + num + "条信息"); } catch (SQLException e) { e.printStackTrace(); } finally { try { if (ps != null) ps.close(); if (con != null) con.close(); } catch (Exception e) { e.printStackTrace(); } } return num; } /** * 执行查询语句 * * @auther maxinhai * @param con 连接对象 * @param querySql 查询语句 * @param params 查询条件参数 * @return 查询结果 */ public static List<Map<String, Object>> executeQuery(Connection con, String querySql, List<Object> params) { List<Map<String, Object>> table = null; PreparedStatement ps = null; ResultSet queryResult = null; try { // 获取预处理对象 ps = con.prepareStatement(querySql); // 填充参数 if (params != null && params.size() > 0) { for (int i = 0; i < params.size(); i++) { ps.setObject(i + 1, params.get(i)); } } // 执行查询 queryResult = ps.executeQuery(); // 获取键名 ResultSetMetaData md = queryResult.getMetaData(); // 获取行的数量 int columnCount = md.getColumnCount(); table = new ArrayList(); while (queryResult.next()) { // 链表保证顺序 Map<String, Object> rowData = new LinkedHashMap<>(); for (int i = 1; i <= columnCount; i++) { // 获取键名及值 rowData.put(md.getColumnName(i), queryResult.getObject(i)); } table.add(rowData); } } catch (SQLException e) { e.printStackTrace(); } finally { try { if (ps != null) ps.close(); if (con != null) con.close(); if (queryResult != null) queryResult.close(); } catch (Exception e) { e.printStackTrace(); } } return table; }
测试代码:
public static void main(String[] args) throws InterruptedException { Connection connect = getConnection(); String sql = "select * from user_info where name=?"; List<Object> params = new ArrayList<>(1); params.add("tom"); List<Map<String, Object>> result = executeQuery(connect, sql, params); if (result.size() == 1) { result.get(0).forEach((k, v) -> { System.out.print(k + "=>" + v + "\t"); }); } else { result.forEach(item -> { item.forEach((k, v) -> { System.out.print(k + "=>" + v + "\t"); }); System.out.println(); }); } connect = getConnection(); String insert = "insert into user_info(name,sex,age,address) values(?,?,?,?)"; List<Object> paramList = new ArrayList<>(4); paramList.add("maxinhai"); paramList.add(1); paramList.add(12); paramList.add(1); int count = executeUpdate(connect, insert, paramList); }
范围查询in:
/** * in范围查询 * @param titleList * @return */ private static List<String> query(List<String> titleList) { List<String> titles = new ArrayList<>(); String sql = "SELECT title FROM `yalayi_image` where is_delete=0 and title in (?)"; String stringFromList = getStringFromList(titleList); String formatSql = String.format(sql.replace("?", "%s"), stringFromList); try { Connection connection = JdbcUtils.getConnection(); PreparedStatement preparedStatement = connection.prepareStatement(formatSql); ResultSet resultSet = preparedStatement.executeQuery(); while (resultSet.next()) { String title = resultSet.getString(1); titles.add(title); } } catch (SQLException e) { e.printStackTrace(); } return titles; } /** * 格式化参数 * @param paramList * @return */ private static String getStringFromList(List<String> paramList) { StringBuffer sb = new StringBuffer(); for (int i = 0; i < paramList.size(); i++) { if(i == paramList.size()-1) { sb.append("'" + paramList.get(i) + "'"); } else { sb.append("'" + paramList.get(i) + "'"); sb.append(","); } } return sb.toString(); } /** * 格式化参数 * @param paramList * @return */ private static String getLongFromList(List<Long> paramList) { StringBuffer sb = new StringBuffer(); for (int i = 0; i < paramList.size(); i++) { if(i == paramList.size()-1) { sb.append(paramList.get(i)); } else { sb.append(paramList.get(i)); sb.append(","); } } return sb.toString(); }
当in查询,参数字符串中出现单引号(')时,格式化参数方法会报错,需要用到下面的方式(替换调参数中的单引号):
private static String getStringFromList(List<String> paramList) { StringBuffer sb = new StringBuffer(); for (int i = 0; i < paramList.size(); i++) { if(i == paramList.size()-1) { if(paramList.get(i).indexOf("'") != -1) { sb.append("'" + paramList.get(i).replaceAll("'","''") + "'"); } else { sb.append("'" + paramList.get(i) + "'"); } } else { if(paramList.get(i).indexOf("'") != -1) { sb.append("'" + paramList.get(i).replaceAll("'","''") + "'"); } else { sb.append("'" + paramList.get(i) + "'"); } sb.append(","); } } return sb.toString(); }
事务回滚代码:
/** * jdbc事务 * @throws SQLException * @throws InterruptedException */ public static void transaction() throws SQLException, InterruptedException { Connection connect = getConnection(); //关闭自动提交事务 connect.setAutoCommit(false); int flag = 0; String insert1 = "insert into user_school(school_id,school_name,school_address,school_type) values(?,?,?,?)"; List<Object> params = new ArrayList<>(4); params.add(5003); params.add("清华大学"); params.add("中关村"); params.add("本科"); try { int i = 100/0; //报错 executeUpdate(connect, insert1, params); } catch (Exception e) { System.out.println("执行" + insert1 + "语句出错,回滚事务"); flag++; } Thread.sleep(3000); String insert2 = "insert into user_info(name,sex,age,address,school1) values(?,?,?,?,?)"; List<Object> paramList = new ArrayList<>(5); paramList.add("666先生的救赎"); paramList.add(1); paramList.add(12); paramList.add(1); paramList.add(5002); try { executeUpdate(connect, insert2, paramList); } catch (Exception e) { System.out.println("执行" + insert2 + "语句出错,回滚事务"); flag++; } if(flag > 0) { System.out.println("事务管理: 两个事务都会滚"); connect.rollback(); } connect.commit(); connect.close(); }
上面事务代码是调用通用增删改代码实现的,需要把通用代码里的关闭数据库连接代码注释掉,事务需要在同一个数据库连接里实现;