JDBC(三):用JDBC实现增删改/JDBC事务/悲观锁

于 2021-04-01 10:32:17 发布
阅读量165 收藏
点赞数
分类专栏： 锦到说Java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_43568982/article/details/115368437
版权

用JDBC实现增删改

用于批量增比较方便

增：核心代码

 String sql = "insert into dept(deptno,dname,loc)values(?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            //写Sql语句
            preparedStatement.setInt(1, 50);
            preparedStatement.setString(2, "Technology");
            preparedStatement.setString(3, "Chengdu");
            int count = preparedStatement.executeUpdate();
            System.out.println(count);

完整代码

package BUPT20210330;

import java.sql.*;

public class JDBCTest05 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql", "root", "333");
            String sql = "insert into dept(deptno,dname,loc)values(?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            //写Sql语句
            preparedStatement.setInt(1, 50);
            preparedStatement.setString(2, "Technology");
            preparedStatement.setString(3, "Chengdu");
            int count = preparedStatement.executeUpdate();
            System.out.println(count);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}


mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | BEIJING  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | BEIJING  |
|     40 | OPERATIONS | BOSTON   |
|     50 | Technology | Chengdu  |
+--------+------------+----------+

批量添加

preparedstatement.addBatch();

将sql语句添加到一个集合中

然后再统一一次性进行执行，提高效率

preparedstatement.executeBatch();

package BUPT;


import java.sql.*;

public class JDBCTest02 {
    public static void main(String[] args) {
        String sql = "insert into dept(deptno,dname,loc)values(?,?,?)";
        PreparedStatement preparedstatement = null;
        Connection connection = null;
        try {
            //1.注册驱动
            Driver driver = new com.mysql.cj.jdbc.Driver();
            DriverManager.registerDriver(driver);
            //2.获取连接
            String url = "jdbc:mysql://localhost:3306/mysql";
            String user = "root";
            String password = "333";
            connection = DriverManager.getConnection(url, user, password);
            //3.获取数据库操作对象(Statement)
            preparedstatement = connection.prepareStatement(sql);
            //执行sql
            for (int i = 71; i < 80; i++) {
                preparedstatement.setInt(1, i);
                preparedstatement.setString(2, "Sales");
                preparedstatement.setString(3, "Huanying");
                preparedstatement.addBatch();
            }
            //执行DML语句,这里记录了影响的数据库条数
            preparedstatement.executeBatch();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {

            //6.释放资源
            //写在finally中保证一定执行
            //释放顺序从小到大
            try {
                if (preparedstatement != null) {
                    preparedstatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {

                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

删：核心代码

 String sql="delete from dept where deptno=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 40);
            int count = preparedStatement.executeUpdate();
            System.out.println(count);

改：核心代码

 String sql="update dept set dname=?,loc=? where deptno=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(3, 50);
            preparedStatement.setString(1, "Technology");
            preparedStatement.setString(2, "Beijing");
            int count = preparedStatement.executeUpdate();
            System.out.println(count);

document.querySelector('video').playbackRate=2.5

JDBC事务

比较一下MySQL事务

打一个断点，看看是否是一运行就提交
在这里插入图片描述
结果发现在debug中，刚运行完该条语句，数据库就更新了
也就是说JDBC支持自动提交机制

建立一个转账过程
写一个t_act 的sql
创建俩账户
在这里插入图片描述

模拟转账10000的过程

String sql = "update t_act set balance=? where actno=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setDouble(1, 10000);
            preparedStatement.setInt(2, 111);
            int count = preparedStatement.executeUpdate();
            System.out.println(count);
            //在这里 引发一个异常
            String x = null;
            x.toString();
            preparedStatement.setDouble(1, 10000);
            preparedStatement.setInt(2, 222);
            count = preparedStatement.executeUpdate();
            System.out.println(count);

引发异常后，后面的部分不执行，但是前面的已经提交了，造成了转账系统的出错

关闭自动提交机制

 connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql", "root", "333");
            connection.setAutoCommit(false);
 //最后中间代码块执行完，再恢复
 connection.commit();

catch后面还要加上回滚机制

catch (Exception e) {
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            e.printStackTrace();
        }

关键的三行代码

connection.setAutoCommit(false);
connection.commit();
connection.rollback();

包装套路代码为工具类

package BUPT20210330;

import utils.DButil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class JDBCTest08 {
    public static void main(String[] args) throws SQLException {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = DButil.getConnection();
            String sql = "select ename from emp ordey by ename";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.println(resultSet.getString("ename"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DButil.close(resultSet, connection, preparedStatement);
        }
    }
}


package utils;

import java.sql.*;

public class DButil {
    //定义一个私有方法，因为工具类的方法都是静态的
    //不需要new对象，直接用类名调用
    private DButil() {

    }

    //静态代码块只在类加载的时候执行一次，只要一调用方法，这个自动执行
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //这里选择抛异常，因为整个语句块中有catch
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://localhost:3306/mysql", "root", "333");
    }
    //传statement和preparedStatement都可以用statement接受
    public static void close(ResultSet resultSet, Connection connection, Statement statement) {
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

悲观锁/行级锁

回顾一下锁
在这里插入图片描述
我们写两个程序

程序1

conn = DButil.getConnection();
            conn.setAutoCommit(false);
            String sql = "select ename,job,sal from emp where job = ? for update";
            ps=conn.prepareStatement(sql);
            ps.setString(1, "Clerk");
            rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("ename") + "," + rs.getString("job") + "," + rs.getString("sal"));
            }
            conn.commit();

通过在SQL里面写上for update，实现悲观锁
此时在conn.commit()处打上断点，debug运行
再去执行程序2

conn = DButil.getConnection();
            conn.setAutoCommit(false);
            String sql = "select ename,job,sal from emp where job = ? for update";
            ps=conn.prepareStatement(sql);
            ps.setString(1, "Clerk");
            rs = ps.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("ename") + "," + rs.getString("job") + "," + rs.getString("sal"));
            }
            conn.commit();

程序2会一直卡住，直到程序1继续运行后才能执行

结束时间：2021-04-01

高开低走。
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值