MySQL_(Java)【事物操作】使用JDBC模拟银行转账向数据库发起修改请求
MySQL_(Java)使用JDBC向数据库发起查询请求 传送门
MySQL_(Java)使用JDBC向数据库中插入(insert)数据 传送门
MySQL_(Java)使用JDBC向数据库中删除(delete)数据 传送门
MySQL_(Java)使用JDBC向数据库中修改(update)数据 传送门
MySQL_(Java)【连接池】简单在JDBCUtils.java中创建连接池 传送门
数据库中事务：
我们之前分享数据库的数据操作，无外乎对数据库的数据进行增、删、改、查。就比如我们去买东西，一般都是先付钱，卖家收到钱再发货。这个用数据库来表示就是，第一步：从用户的账户中减去一部分金额。第二步，再把减去的金额添加到商家的账户上。
但是万一遇到特殊的情况，你成功的完成了第一步，从用户的数据库中扣除了钱，这时候突然停电，系统出现了故障，没有完成第二步。这就尴尬了，用户的钱少了，商家也没收到钱。
为了应对这种情况的发生，数据库就出现了一个功能事务，事务就是一组由 SQL 语句组成的业务逻辑，当事务内的所有 SQL语句都成功的执行，整个事务才算成功，否则就是失败。失败意味着整个的数据操作没有意义，就要把数据恢复到执行事务操作之前的状态。
Learn
一、未添加事务且普通转账正常
二、在扣款和收款操作之间添加一个隐式异常
三、添加事物
数据库表结构:
一、未添加事务且普通转账正常
由a向b转账1000元
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBC01 { public static void main(String[] args) throws SQLException { transferAccount("a","b",1000); } public static void selectAll() throws SQLException { //注册驱动 使用驱动连接数据库 Connection con = null; Statement stmt = null; ResultSet rs = null; try { //数据库的连接 con = JDBCUtils.getConnection(); //数据库的增删改查 stmt = con.createStatement(); //返回一个结果集 rs =stmt.executeQuery("select * from garytb"); while(rs.next()) { //System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3)); System.out.println(rs.getString("id")+","+rs.getString("username")+","+rs.getString("password")); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { JDBCUtils.close(rs, stmt, con); } } //校验用户 public static boolean selectByUernamePassword(String username,String password) throws SQLException { Connection con=null; Statement stmt = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false"; con = DriverManager.getConnection(url,"root","123456"); stmt =con.createStatement(); String sql = "select * from garytb where username = '"+username+"' and password = '"+password+"'"; //System.out.println(sql); rs = stmt.executeQuery(sql); if(rs.next()) { return true; }else { return false; } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { if(rs!=null) rs.close(); if(stmt!=null) stmt.close(); if(con!=null) con.close(); } return false; } public static boolean selectByUP2(String username,String password) throws SQLException{ Connection con=null; Statement stmt = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false"; con = DriverManager.getConnection(url,"root","123456"); String sql = "select * from garytb where username = ? and password = ?"; PreparedStatement pstmt = con.prepareStatement(sql); //添加参数 pstmt.setString(1, username); pstmt.setString(2, password); //进行查询 rs = pstmt.executeQuery(); if(rs.next()) { return true; }else { return false; } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { if(rs!=null) rs.close(); if(stmt!=null) stmt.close(); if(con!=null) con.close(); } return false; } //pageNumber是页数，第几页，pageCount是每页显示多少个数据 public static void selectUserByPage(int pageNumber,int pageCount) throws SQLException { //注册驱动 使用驱动连接数据库 Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); //String url ="jdbc:mysql://localhost:3306/garysql"; //指定编码查询数据库 String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false"; String user = "root"; String password = "123456"; //建立和数据库的连接 con = DriverManager.getConnection(url,user,password); stmt = con.prepareStatement("select * from garytb limit ?,?"); stmt.setInt(1, (pageNumber-1)*pageCount ); stmt.setInt(2, pageCount); rs = stmt.executeQuery(); while(rs.next()) { //System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3)); System.out.println(rs.getString("id")+","+rs.getString("username")+","+rs.getString("password")); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { if(rs!=null) rs.close(); if(stmt!=null) stmt.close(); if(con!=null) con.close(); } } //crud: create read update delete //插入语句 public static void insert(String username,String password) throws SQLException { //注册驱动 使用驱动连接数据库 Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); String sql = "insert into garytb(username,password) values(?,?)"; stmt = con.prepareStatement(sql); stmt.setString(1, username); stmt.setString(2, password); int result =stmt.executeUpdate();// 返回值代表收到影响的行数 System.out.println("插入成功"+username); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { JDBCUtils.close(rs, stmt, con); } } //删除语句 public static void delete(int id) throws SQLException { //注册驱动 使用驱动连接数据库 Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); String sql = "delete from garytb where id = ?"; stmt = con.prepareStatement(sql); stmt.setInt(1, id); int result =stmt.executeUpdate();// 返回值代表收到影响的行数 if(result>0) { System.out.println("删除成功"); }else { System.out.println("删除失败"); } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.close(rs, stmt, con); } } //修改语句 public static void update(int id,String newPassword) throws SQLException { Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); String sql = "update garytb set password = ? where id = ?"; stmt = con.prepareStatement(sql); stmt.setString(1, newPassword); stmt.setInt(2, id); int result =stmt.executeUpdate();// 返回值代表收到影响的行数 if(result>0) { System.out.println("修改成功"); }else { System.out.println("修改失败"); } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.close(rs, stmt, con); } } //事物操作 //由username1向username2转账金额 public static void transferAccount(String username1,String username2,int money) throws SQLException { Connection con = null; PreparedStatement stmt1 = null; PreparedStatement stmt2 = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); String sql = "update garytb set balance = balance - ? where username = ?"; stmt1 = con.prepareStatement(sql); stmt1.setInt(1, money); stmt1.setString(2, username1); stmt1.executeUpdate();// 返回值代表收到影响的行数 sql = "update garytb set balance = balance + ? where username = ?"; stmt2 = con.prepareStatement(sql); stmt2.setInt(1, money); stmt2.setString(2, username2); stmt2.executeUpdate();// 返回值代表收到影响的行数 System.out.println("操作成功！！"); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.close(rs, stmt1, con); } } }
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBCUtils { private static final String connectionURL = "jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false"; private static final String username = "root"; private static final String password = "123"; //创建数据库的连接 public static Connection getConnection() { try { Class.forName("com.mysql.jdbc.Driver"); return DriverManager.getConnection(connectionURL,username,password); } catch (Exception e) { e.printStackTrace(); } return null; } //关闭数据库的连接 public static void close(ResultSet rs,Statement stmt,Connection con) throws SQLException { if(rs!=null) rs.close(); if(stmt!=null) stmt.close(); if(con!=null) con.close(); } }
转账资源操作【资源关闭】优化版
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBC01 { public static void main(String[] args) throws SQLException { transferAccount("a","b",1000); } public static void selectAll() throws SQLException { //注册驱动 使用驱动连接数据库 Connection con = null; Statement stmt = null; ResultSet rs = null; try { //数据库的连接 con = JDBCUtils.getConnection(); //数据库的增删改查 stmt = con.createStatement(); //返回一个结果集 rs =stmt.executeQuery("select * from garytb"); while(rs.next()) { //System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3)); System.out.println(rs.getString("id")+","+rs.getString("username")+","+rs.getString("password")); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { JDBCUtils.close(rs, stmt, con); } } //校验用户 public static boolean selectByUernamePassword(String username,String password) throws SQLException { Connection con=null; Statement stmt = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false"; con = DriverManager.getConnection(url,"root","123456"); stmt =con.createStatement(); String sql = "select * from garytb where username = '"+username+"' and password = '"+password+"'"; //System.out.println(sql); rs = stmt.executeQuery(sql); if(rs.next()) { return true; }else { return false; } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { if(rs!=null) rs.close(); if(stmt!=null) stmt.close(); if(con!=null) con.close(); } return false; } public static boolean selectByUP2(String username,String password) throws SQLException{ Connection con=null; Statement stmt = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false"; con = DriverManager.getConnection(url,"root","123456"); String sql = "select * from garytb where username = ? and password = ?"; PreparedStatement pstmt = con.prepareStatement(sql); //添加参数 pstmt.setString(1, username); pstmt.setString(2, password); //进行查询 rs = pstmt.executeQuery(); if(rs.next()) { return true; }else { return false; } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { if(rs!=null) rs.close(); if(stmt!=null) stmt.close(); if(con!=null) con.close(); } return false; } //pageNumber是页数，第几页，pageCount是每页显示多少个数据 public static void selectUserByPage(int pageNumber,int pageCount) throws SQLException { //注册驱动 使用驱动连接数据库 Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); //String url ="jdbc:mysql://localhost:3306/garysql"; //指定编码查询数据库 String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false"; String user = "root"; String password = "123456"; //建立和数据库的连接 con = DriverManager.getConnection(url,user,password); stmt = con.prepareStatement("select * from garytb limit ?,?"); stmt.setInt(1, (pageNumber-1)*pageCount ); stmt.setInt(2, pageCount); rs = stmt.executeQuery(); while(rs.next()) { //System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3)); System.out.println(rs.getString("id")+","+rs.getString("username")+","+rs.getString("password")); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { if(rs!=null) rs.close(); if(stmt!=null) stmt.close(); if(con!=null) con.close(); } } //crud: create read update delete //插入语句 public static void insert(String username,String password) throws SQLException { //注册驱动 使用驱动连接数据库 Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); String sql = "insert into garytb(username,password) values(?,?)"; stmt = con.prepareStatement(sql); stmt.setString(1, username); stmt.setString(2, password); int result =stmt.executeUpdate();// 返回值代表收到影响的行数 System.out.println("插入成功"+username); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { JDBCUtils.close(rs, stmt, con); } } //删除语句 public static void delete(int id) throws SQLException { //注册驱动 使用驱动连接数据库 Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); String sql = "delete from garytb where id = ?"; stmt = con.prepareStatement(sql); stmt.setInt(1, id); int result =stmt.executeUpdate();// 返回值代表收到影响的行数 if(result>0) { System.out.println("删除成功"); }else { System.out.println("删除失败"); } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.close(rs, stmt, con); } } //修改语句 public static void update(int id,String newPassword) throws SQLException { Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); String sql = "update garytb set password = ? where id = ?"; stmt = con.prepareStatement(sql); stmt.setString(1, newPassword); stmt.setInt(2, id); int result =stmt.executeUpdate();// 返回值代表收到影响的行数 if(result>0) { System.out.println("修改成功"); }else { System.out.println("修改失败"); } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.close(rs, stmt, con); } } //事物操作 //由username1向username2转账金额 public static void transferAccount(String username1,String username2,int money) throws SQLException { Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); String sql = "update garytb set balance = balance - ? where username = ?"; stmt = con.prepareStatement(sql); stmt.setInt(1, money); stmt.setString(2, username1); stmt.executeUpdate();// 返回值代表收到影响的行数 sql = "update garytb set balance = balance + ? where username = ?"; stmt = con.prepareStatement(sql); stmt.setInt(1, money); stmt.setString(2, username2); stmt.executeUpdate();// 返回值代表收到影响的行数 System.out.println("操作成功！！"); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.close(rs, stmt, con); } } }
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; public class JDBCUtils { private static final String connectionURL = "jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false"; private static final String username = "root"; private static final String password = "123"; private static ArrayList<Connection> conList = new ArrayList<Connection>(); static { for(int i =0;i<5;i++) { Connection con = createConnection(); conList.add(con); } } public static Connection getConnection() { if(conList.isEmpty()==false) { Connection con = conList.get(0); conList.remove(con); return con; }else { return createConnection(); } } private static Connection createConnection() { try { Class.forName("com.mysql.jdbc.Driver"); return DriverManager.getConnection(connectionURL, username, password); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } public static void close(ResultSet rs,Statement stmt,Connection con) { closeResultSet(rs); closeStatement(stmt); closeConnection(con); } public static void close(Statement stmt1,Statement stmt2,Connection con) { closeStatement(stmt1); closeStatement(stmt2); closeConnection(con); } private static void closeResultSet(ResultSet rs ) { try { if(rs!=null)rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } private static void closeStatement(Statement stmt) { try { if(stmt!=null) stmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } private static void closeConnection(Connection con) { // try { // if(con!=null)con.close(); // } catch (SQLException e) { // // TODO Auto-generated catch block // e.printStackTrace(); // } conList.add(con); } }
//由username1向username2转账金额 public static void transferAccount(String username1,String username2,int money) throws SQLException { Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); String sql = "update garytb set balance = balance - ? where username = ?"; stmt = con.prepareStatement(sql); stmt.setInt(1, money); stmt.setString(2, username1); stmt.executeUpdate();// 返回值代表收到影响的行数 sql = "update garytb set balance = balance + ? where username = ?"; stmt = con.prepareStatement(sql); stmt.setInt(1, money); stmt.setString(2, username2); stmt.executeUpdate();// 返回值代表收到影响的行数 System.out.println("操作成功！！"); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.close(rs, stmt, con); } }
二、在扣款和收款操作之间添加一个隐式异常
当程序运行到异常代码块时，程序不会往下继续执行,因此a方1000元已扣除但b方未收到1000元款项
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBC01 { public static void main(String[] args) throws SQLException { transferAccount("a","b",1000); } public static void selectAll() throws SQLException { //注册驱动 使用驱动连接数据库 Connection con = null; Statement stmt = null; ResultSet rs = null; try { //数据库的连接 con = JDBCUtils.getConnection(); //数据库的增删改查 stmt = con.createStatement(); //返回一个结果集 rs =stmt.executeQuery("select * from garytb"); while(rs.next()) { //System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3)); System.out.println(rs.getString("id")+","+rs.getString("username")+","+rs.getString("password")); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { JDBCUtils.close(rs, stmt, con); } } //校验用户 public static boolean selectByUernamePassword(String username,String password) throws SQLException { Connection con=null; Statement stmt = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false"; con = DriverManager.getConnection(url,"root","123456"); stmt =con.createStatement(); String sql = "select * from garytb where username = '"+username+"' and password = '"+password+"'"; //System.out.println(sql); rs = stmt.executeQuery(sql); if(rs.next()) { return true; }else { return false; } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { if(rs!=null) rs.close(); if(stmt!=null) stmt.close(); if(con!=null) con.close(); } return false; } public static boolean selectByUP2(String username,String password) throws SQLException{ Connection con=null; Statement stmt = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false"; con = DriverManager.getConnection(url,"root","123456"); String sql = "select * from garytb where username = ? and password = ?"; PreparedStatement pstmt = con.prepareStatement(sql); //添加参数 pstmt.setString(1, username); pstmt.setString(2, password); //进行查询 rs = pstmt.executeQuery(); if(rs.next()) { return true; }else { return false; } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { if(rs!=null) rs.close(); if(stmt!=null) stmt.close(); if(con!=null) con.close(); } return false; } //pageNumber是页数，第几页，pageCount是每页显示多少个数据 public static void selectUserByPage(int pageNumber,int pageCount) throws SQLException { //注册驱动 使用驱动连接数据库 Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); //String url ="jdbc:mysql://localhost:3306/garysql"; //指定编码查询数据库 String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false"; String user = "root"; String password = "123456"; //建立和数据库的连接 con = DriverManager.getConnection(url,user,password); stmt = con.prepareStatement("select * from garytb limit ?,?"); stmt.setInt(1, (pageNumber-1)*pageCount ); stmt.setInt(2, pageCount); rs = stmt.executeQuery(); while(rs.next()) { //System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3)); System.out.println(rs.getString("id")+","+rs.getString("username")+","+rs.getString("password")); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { if(rs!=null) rs.close(); if(stmt!=null) stmt.close(); if(con!=null) con.close(); } } //crud: create read update delete //插入语句 public static void insert(String username,String password) throws SQLException { //注册驱动 使用驱动连接数据库 Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); String sql = "insert into garytb(username,password) values(?,?)"; stmt = con.prepareStatement(sql); stmt.setString(1, username); stmt.setString(2, password); int result =stmt.executeUpdate();// 返回值代表收到影响的行数 System.out.println("插入成功"+username); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { JDBCUtils.close(rs, stmt, con); } } //删除语句 public static void delete(int id) throws SQLException { //注册驱动 使用驱动连接数据库 Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); String sql = "delete from garytb where id = ?"; stmt = con.prepareStatement(sql); stmt.setInt(1, id); int result =stmt.executeUpdate();// 返回值代表收到影响的行数 if(result>0) { System.out.println("删除成功"); }else { System.out.println("删除失败"); } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.close(rs, stmt, con); } } //修改语句 public static void update(int id,String newPassword) throws SQLException { Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); String sql = "update garytb set password = ? where id = ?"; stmt = con.prepareStatement(sql); stmt.setString(1, newPassword); stmt.setInt(2, id); int result =stmt.executeUpdate();// 返回值代表收到影响的行数 if(result>0) { System.out.println("修改成功"); }else { System.out.println("修改失败"); } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.close(rs, stmt, con); } } //事物操作 //由username1向username2转账金额 public static void transferAccount(String username1,String username2,int money) { Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); String sql = "update garytb set balance = balance - ? where username = ?"; stmt = con.prepareStatement(sql); stmt.setInt(1, money); stmt.setString(2, username1); stmt.executeUpdate();// 返回值代表收到影响的行数 //显示异常throw new Exception("出现错误"); //隐示异常 空指针异常 String s = null; s.charAt(2); sql = "update garytb set balance = balance + ? where username = ?"; stmt = con.prepareStatement(sql); stmt.setInt(1, money); stmt.setString(2, username2); stmt.executeUpdate();// 返回值代表收到影响的行数 System.out.println("操作成功！！"); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.close(rs, stmt, con); } } }
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; public class JDBCUtils { private static final String connectionURL = "jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false"; private static final String username = "root"; private static final String password = "123"; private static ArrayList<Connection> conList = new ArrayList<Connection>(); static { for(int i =0;i<5;i++) { Connection con = createConnection(); conList.add(con); } } public static Connection getConnection() { if(conList.isEmpty()==false) { Connection con = conList.get(0); conList.remove(con); return con; }else { return createConnection(); } } private static Connection createConnection() { try { Class.forName("com.mysql.jdbc.Driver"); return DriverManager.getConnection(connectionURL, username, password); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } public static void close(ResultSet rs,Statement stmt,Connection con) { closeResultSet(rs); closeStatement(stmt); closeConnection(con); } public static void close(Statement stmt1,Statement stmt2,Connection con) { closeStatement(stmt1); closeStatement(stmt2); closeConnection(con); } private static void closeResultSet(ResultSet rs ) { try { if(rs!=null)rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } private static void closeStatement(Statement stmt) { try { if(stmt!=null) stmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } private static void closeConnection(Connection con) { // try { // if(con!=null)con.close(); // } catch (SQLException e) { // // TODO Auto-generated catch block // e.printStackTrace(); // } conList.add(con); } }
//由username1向username2转账金额 public static void transferAccount(String username1,String username2,int money) { Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); String sql = "update garytb set balance = balance - ? where username = ?"; stmt = con.prepareStatement(sql); stmt.setInt(1, money); stmt.setString(2, username1); stmt.executeUpdate();// 返回值代表收到影响的行数 //显示异常throw new Exception("出现错误"); //隐示异常 空指针异常 String s = null; s.charAt(2); sql = "update garytb set balance = balance + ? where username = ?"; stmt = con.prepareStatement(sql); stmt.setInt(1, money); stmt.setString(2, username2); stmt.executeUpdate();// 返回值代表收到影响的行数 System.out.println("操作成功！！"); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.close(rs, stmt, con); } }
三、添加事物
一旦事物之间出现异常，则不会提交事物给数据库
//开启事物 是否自动提交 con.setAutoCommit(false); //当事物中所有事物都完成了才会提交 con.commit();
当a向b转账1000元后出现异常，b未收到1000元，这一系列操作处于同一事物中，此事物有异常不会提交给程序
import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; public class JDBC01 { public static void main(String[] args) throws SQLException { transferAccount("a","b",1000); } public static void selectAll() throws SQLException { //注册驱动 使用驱动连接数据库 Connection con = null; Statement stmt = null; ResultSet rs = null; try { //数据库的连接 con = JDBCUtils.getConnection(); //数据库的增删改查 stmt = con.createStatement(); //返回一个结果集 rs =stmt.executeQuery("select * from garytb"); while(rs.next()) { //System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3)); System.out.println(rs.getString("id")+","+rs.getString("username")+","+rs.getString("password")); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { JDBCUtils.close(rs, stmt, con); } } //校验用户 public static boolean selectByUernamePassword(String username,String password) throws SQLException { Connection con=null; Statement stmt = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false"; con = DriverManager.getConnection(url,"root","123456"); stmt =con.createStatement(); String sql = "select * from garytb where username = '"+username+"' and password = '"+password+"'"; //System.out.println(sql); rs = stmt.executeQuery(sql); if(rs.next()) { return true; }else { return false; } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { if(rs!=null) rs.close(); if(stmt!=null) stmt.close(); if(con!=null) con.close(); } return false; } public static boolean selectByUP2(String username,String password) throws SQLException{ Connection con=null; Statement stmt = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false"; con = DriverManager.getConnection(url,"root","123456"); String sql = "select * from garytb where username = ? and password = ?"; PreparedStatement pstmt = con.prepareStatement(sql); //添加参数 pstmt.setString(1, username); pstmt.setString(2, password); //进行查询 rs = pstmt.executeQuery(); if(rs.next()) { return true; }else { return false; } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { if(rs!=null) rs.close(); if(stmt!=null) stmt.close(); if(con!=null) con.close(); } return false; } //pageNumber是页数，第几页，pageCount是每页显示多少个数据 public static void selectUserByPage(int pageNumber,int pageCount) throws SQLException { //注册驱动 使用驱动连接数据库 Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { Class.forName("com.mysql.jdbc.Driver"); //String url ="jdbc:mysql://localhost:3306/garysql"; //指定编码查询数据库 String url ="jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false"; String user = "root"; String password = "123456"; //建立和数据库的连接 con = DriverManager.getConnection(url,user,password); stmt = con.prepareStatement("select * from garytb limit ?,?"); stmt.setInt(1, (pageNumber-1)*pageCount ); stmt.setInt(2, pageCount); rs = stmt.executeQuery(); while(rs.next()) { //System.out.println(rs.getString(1)+","+rs.getString(2)+","+rs.getString(3)); System.out.println(rs.getString("id")+","+rs.getString("username")+","+rs.getString("password")); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { if(rs!=null) rs.close(); if(stmt!=null) stmt.close(); if(con!=null) con.close(); } } //crud: create read update delete //插入语句 public static void insert(String username,String password) throws SQLException { //注册驱动 使用驱动连接数据库 Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); String sql = "insert into garytb(username,password) values(?,?)"; stmt = con.prepareStatement(sql); stmt.setString(1, username); stmt.setString(2, password); int result =stmt.executeUpdate();// 返回值代表收到影响的行数 System.out.println("插入成功"+username); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally { JDBCUtils.close(rs, stmt, con); } } //删除语句 public static void delete(int id) throws SQLException { //注册驱动 使用驱动连接数据库 Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); String sql = "delete from garytb where id = ?"; stmt = con.prepareStatement(sql); stmt.setInt(1, id); int result =stmt.executeUpdate();// 返回值代表收到影响的行数 if(result>0) { System.out.println("删除成功"); }else { System.out.println("删除失败"); } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.close(rs, stmt, con); } } //修改语句 public static void update(int id,String newPassword) throws SQLException { Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); String sql = "update garytb set password = ? where id = ?"; stmt = con.prepareStatement(sql); stmt.setString(1, newPassword); stmt.setInt(2, id); int result =stmt.executeUpdate();// 返回值代表收到影响的行数 if(result>0) { System.out.println("修改成功"); }else { System.out.println("修改失败"); } } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.close(rs, stmt, con); } } //事物操作 //由username1向username2转账金额 public static void transferAccount(String username1,String username2,int money) { Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); //开启事物 是否自动提交 con.setAutoCommit(false); String sql = "update garytb set balance = balance - ? where username = ?"; stmt = con.prepareStatement(sql); stmt.setInt(1, money); stmt.setString(2, username1); stmt.executeUpdate();// 返回值代表收到影响的行数 //显示异常throw new Exception("出现错误"); //隐示异常 空指针异常 sql = "update garytb set balance = balance + ? where username = ?"; stmt = con.prepareStatement(sql); stmt.setInt(1, money); stmt.setString(2, username2); stmt.executeUpdate();// 返回值代表收到影响的行数 System.out.println("操作成功！！"); //提交事务 //当事物中所有事物都完成了才会提交 con.commit(); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.close(rs, stmt, con); } } }
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; public class JDBCUtils { private static final String connectionURL = "jdbc:mysql://localhost:3306/garysql?useUnicode=true&characterEncoding=UTF8&useSSL=false"; private static final String username = "root"; private static final String password = "123"; private static ArrayList<Connection> conList = new ArrayList<Connection>(); static { for(int i =0;i<5;i++) { Connection con = createConnection(); conList.add(con); } } public static Connection getConnection() { if(conList.isEmpty()==false) { Connection con = conList.get(0); conList.remove(con); return con; }else { return createConnection(); } } private static Connection createConnection() { try { Class.forName("com.mysql.jdbc.Driver"); return DriverManager.getConnection(connectionURL, username, password); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } return null; } public static void close(ResultSet rs,Statement stmt,Connection con) { closeResultSet(rs); closeStatement(stmt); closeConnection(con); } public static void close(Statement stmt1,Statement stmt2,Connection con) { closeStatement(stmt1); closeStatement(stmt2); closeConnection(con); } private static void closeResultSet(ResultSet rs ) { try { if(rs!=null)rs.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } private static void closeStatement(Statement stmt) { try { if(stmt!=null) stmt.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } private static void closeConnection(Connection con) { // try { // if(con!=null)con.close(); // } catch (SQLException e) { // // TODO Auto-generated catch block // e.printStackTrace(); // } conList.add(con); } }
//事物操作 //由username1向username2转账金额 public static void transferAccount(String username1,String username2,int money) { Connection con = null; PreparedStatement stmt = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); //开启事物 是否自动提交 con.setAutoCommit(false); String sql = "update garytb set balance = balance - ? where username = ?"; stmt = con.prepareStatement(sql); stmt.setInt(1, money); stmt.setString(2, username1); stmt.executeUpdate();// 返回值代表收到影响的行数 //显示异常throw new Exception("出现错误"); //隐示异常 空指针异常 sql = "update garytb set balance = balance + ? where username = ?"; stmt = con.prepareStatement(sql); stmt.setInt(1, money); stmt.setString(2, username2); stmt.executeUpdate();// 返回值代表收到影响的行数 System.out.println("操作成功！！"); //提交事务 //当事物中所有事物都完成了才会提交 con.commit(); } catch (Exception e) { e.printStackTrace(); } finally { JDBCUtils.close(rs, stmt, con); } }
(如需转载学习，请标明出处)
