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); }
事务回滚代码:
/** * 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(); }
上面事务代码是调用通用增删改代码实现的,需要把通用代码里的关闭数据库连接代码注释掉,事务需要在同一个数据库连接里实现;