访问数据库的工具类

1、工具类

 1 package cn.zmh.Utils;
 2 
 3 
 4 import java.sql.*;
 5 /*
 6  * 访问数据库的工具类
 7  * */
 8 public class JdbcUtils {
 9     // 1 可以把几个字符串定义成常量
10     private static final String USER = "root";
11     private static final String UPWD = "root";
12     private static final String URL = "jdbc:mysql:///qy97";
13     private static final String DRIVER = "com.mysql.jdbc.Driver";
14     // 2 注册驱动
15     static{
16         try {
17             Class.forName(DRIVER);
18         } catch (ClassNotFoundException e) {
19             e.printStackTrace();
20         }
21     }
22     // 3 得到数据库连接
23     public static Connection getConnection() throws SQLException {
24         return DriverManager.getConnection(URL,USER,UPWD);
25     }
26     // 4 关闭连接  执行 的打开资源
27     public static void close(Connection connection, Statement statement){
28         if(statement!=null){
29             try {
30                 statement.close();
31             } catch (SQLException e) {
32                 e.printStackTrace();
33             }
34         }
35         if(connection!=null){
36             try {
37                 connection.close();
38             } catch (SQLException e) {
39                 e.printStackTrace();
40             }
41         }
42     }
43     // 5 关闭所有的打开资源
44     public static void close(Connection connection, Statement statement, ResultSet rs){
45         if(statement!=null){
46             try {
47                 statement.close();
48             } catch (SQLException e) {
49                 e.printStackTrace();
50             }
51         }
52         if(connection!=null){
53             try {
54                 connection.close();
55             } catch (SQLException e) {
56                 e.printStackTrace();
57             }
58         }
59         if(rs!=null){
60             try {
61                 rs.close();
62             } catch (SQLException e) {
63                 e.printStackTrace();
64             }
65         }
66     }
67 }

2、事物调用工具类模拟银行转账

 1 package cn.zmh.Transcation转账;
 2 
 3 import cn.zmh.Utils.JdbcUtils;
 4 
 5 import java.sql.Connection;
 6 import java.sql.PreparedStatement;
 7 import java.sql.SQLException;
 8 /**
 9  * 事物   模拟银行转账
10  * */
11 public class TranscationDemo {
12     public static void main(String[] args) {
13         // 声明变量
14         Connection conn = null;
15         PreparedStatement ps = null;
16         //1 获取连接
17         try {
18             conn = JdbcUtils.getConnection();
19             //2 开启事物
20             conn.setAutoCommit(false);
21             //3 获取PreparedStatement
22             ps = conn.prepareStatement("update ccount set money=money-? where sname=?");
23             //4 使用PreparedStatement
24             ps.setDouble(1,1000);
25             ps.setString(2, "李四");
26             ps.executeUpdate();
27             System.out.println(11/0);
28             ps = conn.prepareStatement("update ccount set money=money+? where sname=?");
29             ps.setDouble(1,1000);
30             ps.setString(2, "张三");
31             ps.executeUpdate();
32             // 5 提交事物
33             conn.commit();
34             System.out.println("转账成功");
35         } catch (Exception e) {
36             // 6 事物回滚
37             try {
38                 conn.rollback();
39             } catch (SQLException e1) {
40                 e1.printStackTrace();
41             }
42             System.out.println("转账失败");
43         }finally{
44             // 7 关闭资源
45             JdbcUtils.close(conn, ps);
46         }
47 
48     }
49 }