mysql学习笔记（七）事务&批处理和JDBC的使用爬坑

原创

逆回十六夜

修改于 2020-02-11 14:59:10

1.3K0

修改于 2020-02-11 14:59:10

文章被收录于专栏：逆回十六夜逆回十六夜

事务

概述

一个事务执行多个操作时，要么所有事务被提交，对数据库的操作成功。要么中途出现问题或者反悔回滚

mysql默认是自动提交事务，一句指令提交一次，可以通过set autocommit=true/false来实现对自动提交的设置，同时，该设置仅针对本次连接，新的连接需要重新设置。

start transaction;

XXXXXX

commit;或者rollback;

事务的commit中间的代码是属于DCL，对create drop等DDL语句无效

事务的隔离级别

原文链接：https://blog.csdn.net/l1394049664/article/details/81814090#MySQL%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E5%9B%9B%E7%A7%8D%E4%BA%8B%E5%8A%A1%E9%9A%94%E7%A6%BB%E7%BA%A7%E5%88%AB

MySQL数据库的四种事务隔离级别

Read Uncommitted（读取未提交内容）

在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）；

Read Committed（读取提交内容）

这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果；

Repeatable Read（可重读）

这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读（Phantom Read）。

简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。

InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题

Serializable（可串行化）

这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。

这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：

脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。

不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。

幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。

在MySQL中，实现了这四种隔离级别，分别有可能产生问题如下所示：

　　① Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

　　② Repeatable read (可重复读)：可避免脏读、不可重复读的发生。

　　③ Read committed (读已提交)：可避免脏读的发生。

　　④ Read uncommitted (读未提交)：最低级别，任何情况都无法保证。

　　以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，当然级别越高，执行效率就越低。像Serializable这样的级别，就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。在MySQL数据库中默认的隔离级别为Repeatable read (可重复读)。

————————————————

原文链接：https://blog.csdn.net/l1394049664/article/details/81814090

批处理

statement/preparedstatement的方法addbatch()：将之前已经添加的sql语句添加到批处理中。

int[] executeBatch=preparedstatement.executeBatch()：将已经添加的批处理全部提交执行。

PS：mysql默认关闭批处理，解决方法是在url后面添加参数rewriteBatchedStatements=true;

JDBC

使用方法:

1.下载基于mysql的jdbc连接jar包

mysql-connector-java-5.1.18-bin.jar

2.配置进项目

3.编写代码

遇到的问题

1.在代码编写完整后尝试连接，出现关于MySQL密码验证，Client does not support authentication protocol requested by server的问题。解决方法https://www.cnblogs.com/iliuqiang/p/9737288.html

重新安装配置，采用传统的手段。

2.在解决完问题1后出现，

java.sql.SQLException: Unknown initial character set index '255' received from server. Initial client character set can be forced via the 'characterEncoding' property.

解决方法https://blog.csdn.net/qq_38674645/article/details/81105266

https://blog.csdn.net/oldinaction/article/details/50489056

这是因为MYSQL 5.5 之前， UTF8 编码只支持1-3个字节;从MYSQL5.5开始，可支持4个字节UTF编码utf8mb4;

更新驱动包

3.Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.

解决方法：如提示所述。

4.The server time zone value 'ÖÐ¹ú±ê×¼Ê±¼ä' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

解决方法：https://blog.csdn.net/Peacock__/article/details/87879709

这是高版本mysql的问题，需要在url中指定当前时区

5.Sun Feb 09 14:01:17 CST 2020 WARN: Caught while disconnecting...

EXCEPTION STACK TRACE:

** BEGIN NESTED EXCEPTION **

javax.net.ssl.SSLException

MESSAGE: closing inbound before receiving peer's close_notify

STACKTRACE:

javax.net.ssl.SSLException: closing inbound before receiving peer's close_notify

at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:133)

at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:117)

at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:307)

at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:263)

at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:254)

at java.base/sun.security.ssl.SSLSocketImpl.shutdownInput(SSLSocketImpl.java:645)

at java.base/sun.security.ssl.SSLSocketImpl.shutdownInput(SSLSocketImpl.java:624)

at com.mysql.cj.protocol.a.NativeProtocol.quit(NativeProtocol.java:1312)

at com.mysql.cj.NativeSession.quit(NativeSession.java:182)

at com.mysql.cj.jdbc.ConnectionImpl.realClose(ConnectionImpl.java:1750)

at com.mysql.cj.jdbc.ConnectionImpl.close(ConnectionImpl.java:720)

at dbaccess.LoginDAO.registerOp(LoginDAO.java:40)

at dbaccess.LoginDAO.registerOp(LoginDAO.java:45)

at launcher.launcher.main(launcher.java:11)

** END NESTED EXCEPTION **

解决方法:https://blog.csdn.net/qq_34075488/article/details/85106860

禁用SSL

代码编写样本

连接数据库:

public class LoginDAO {
    private static LoginDAO instance;
    //定义私有的构造函数规定只有自己类内部才能创建该对象的实例
    private Connection connection;
    private LoginDAO(){
        //连接数据库
        String url = "jdbc:mysql://localhost:3306/eazynotedb?serverTimezone=Asia/Shanghai&useSSL=false";        //连接数据库的URL
        try {
            //注册驱动，把驱动类加载到内存中并初始化这个类
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(url,"root","nicai");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e){
            e.printStackTrace();
        }
    }

    public static LoginDAO getInstance(){
        synchronized (LoginDAO.class){
            if(instance==null){
                instance = new LoginDAO();
            }
            return instance;
        }
    }
}

操作数据库

通过statement直接执行sql语句操作

public int registerOp(String name,String account,String password) throws SQLException {
    String sql="insert into user (name,account,password) values (\""+name+"\",\""+account+"\",\""+password+"\")";
    Statement statement = connection.createStatement();
    int len = statement.executeUpdate(sql);
    statement.close();
    if(statement.isClosed()) {
        return 1;
    }else{
        return 0;
    }
}

public int loginOp(String account,String password) throws SQLException {
    String sql="SELECT user.name,user.account FROM user where user.account=\"736872721@qq.com\"&user.password=\"nicai\";";
    Statement statement = connection.createStatement();
    ResultSet resultSet = statement.executeQuery(sql);
    while(resultSet.next()){
        System.out.println(resultSet.getString("name"));
        System.out.println(resultSet.getString("account"));
    }
    resultSet.close();            //resultSet也需要关闭，若statement关闭，则会自动关闭resultSet
    statement.close();
    if(statement.isClosed()) {
        return 1;
    }else{
        return 0;
    }
}

通过PreparedStatement实现数据库操作

有效防止SQL注入

何为SQL注入？

public class launcher {
    public static void main(String[] args){
        LoginDAO loginDAO = LoginDAO.getInstance();
        try {
            loginDAO.loginOp("乱写的账户 or 1=1","乱写的密码 or 1=1");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

PreparedStatement的用法：

//String sql="insert into user (name,account,password) values (\""+name+"\",\""+account+"\",\""+password+"\")";
        String preparedSql="insert into user (name,account,password) values(?,?,?)";
        PreparedStatement preparedStatement = connection.prepareStatement(preparedSql);//此处带上presql
        preparedStatement.setString(1,name);
        preparedStatement.setString(2,account);
        preparedStatement.setString(3,password);
        int len = preparedStatement.executeUpdate();    //此处与statement的区别就是不带参数sql
        System.out.println(len>0?"注册，注册成功":"注册，注册失败");
        preparedStatement.close();
        if(preparedStatement.isClosed()){
            return 1;
        }else{
            return 0;
        }

public int loginOp(String account,String password) throws SQLException {
        String preparedSql="SELECT user.name,user.account FROM user where user.account=? and user.password=?;";
        PreparedStatement preparedStatement = connection.prepareStatement(preparedSql);
        preparedStatement.setString(1,account);
        preparedStatement.setString(2,password);
        //ResultSet resultSet = statement.executeQuery(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        if(resultSet.next()){
            System.out.println(resultSet.getString("name"));
            System.out.println(resultSet.getString("account"));
            System.out.println("登录，登录成功");
        }else{
            System.out.println("登录，登录失败");
        }
        resultSet.close();
        preparedStatement.close();
        if(preparedStatement.isClosed()){
            return 1;
        }else{
            return 0;
        }
    }

注：preparedstatement能用and尽量用and，本人在实验中发现用&无法避免sql注入，用and才能避免sql注入。