Java —— JDBC与批处理、事务

Zsprinkle

已于 2023-02-27 23:57:47 修改

阅读量743

点赞数

分类专栏： Java 文章标签： java 数据库 mysql

于 2023-02-25 16:23:00 首次发布

本文链接：https://blog.csdn.net/smallrain6/article/details/129216444

版权

Java 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

什么是JDBC

JDBC代表Java数据库连接(Java Database Connectivity)
用于Java编程语言和数据库之间进行数据库连接的标准Java API

前期准备1：创建表格

Navicat查看建表代码
双击要打开的表，右侧顶端点击ddl小方框

CREATE TABLE `s` (
  `id` int(6) NOT NULL,
  `name` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `gender` varchar(2) COLLATE utf8_bin DEFAULT NULL,
  `dept` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

前期准备2：插入数据

201801 刚子 18 男数学系 1
201802 严子 18 女计算机系 2
201803 谢子 20 男数学系 1
201804 周子 11 女计算机系 2
201805 喜子 22 男信息系 3
201806 万子 23 女数学系 1
201807 婷子 10 男计算机系 2
201808 周钱系 18 女信息系 3
201809 雨子 19 男计算机系 2
201810 小子 18 女信息系 3
201811 晓子 21 男计算机系 2

JDBC代码

package cs.kaoyan.mysql.com;

import com.mysql.jdbc.Driver;

import java.sql.*;
import java.util.LinkedHashMap;

public class Test1 {
    private static final String url =
            //jdbc:mysql:连接mysql的协议
            //db47指的数据库
            "jdbc:mysql://localhost:3306/db47?useSSL=false&characterEncoding=utf8";
    private static final String username = "root";
    private static final String password = "123456";

    public static void main(String[] args) throws SQLException {
        //第1步. 注册/加载驱动
        //用户要想操作数据库需要数据库的驱动
        //导import com.mysql.jdbc.Driver包
        //注册驱动写法1：需要排除异常
        DriverManager.registerDriver(new Driver());

        // 注册驱动写法2
        // new Driver();
        // 注册驱动写法3
        // Class.forName("com.mysql.jdbc.Driver");

        //第2步. 建立链接
        Connection connection = DriverManager.getConnection(url, username, password);
        
        // 3. 获取Statement对象
        // 数据库连接对象Connection并不能直接去发送SQL语句给MySQL服务器
        // 需要借助于Statement对象包装请求之后发送
        Statement statement = connection.createStatement();

        //提交sql语句
        //s指的是数据库db47中的一张表
        //新增一条二郎神的数据(增)
        /*String sql = "insert into s values (201812,'二郎神',2000,'男','计算机系',2);";
        statement.executeUpdate(sql);*/


        //更新二郎神的年龄为3000岁(改)
        /*String sql  = "update s set age = 3000 where id = 201812;";
        statement.executeUpdate(sql);*/

        //删除新插入的二郎神的数据(查)
        /*String sql = "delete from s where id = 201812;";
        statement.executeUpdate(sql);*/

        //使用有序结构LinkedHashMap用来存储查询到的数据
        LinkedHashMap<Integer, String> map = new LinkedHashMap<>();

        //statement连接中的executeQuery方法提供了查询功能
        //返回以恶搞结果集resultSet
        String sql = "select *  from s;";
        ResultSet resultSet = statement.executeQuery(sql);

        //查询结果集
        while (resultSet.next()){
            String name = resultSet.getString("name");
            int id = resultSet.getInt("id");

            map.put(id,name);
        }

        //输出结果
        System.out.println(map);
        
        //关闭链接
        connection.close();
        statement.close();
    }
}

数据库的批处理操作

在实际应用中，我们经常会遇到数据的批量添加问题。例如在学校的学生管理系统中，每年开学之际，学校都会新来一批同学，我们需要根据同学的报道情况统计出一个学生信息表(Excel表)，然后录入到管理系统中，后续可以进行选课操作，如何实现将excel数据导入数据库之中呢？

前期准备：创建学生表

CREATE TABLE `user` (
  `id` int(11) DEFAULT NULL,
  `username` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `userpassword` varchar(20) COLLATE utf8_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

code1 使用for循环单条插入(用时42s)

时间开销：

sql语句在网络中传输开销: 每一次的网络请求都是固定开销
sql被数据库服务编译的开销
数据库读外存和写外存的开销
无预编译，无批处理，向MySQL服务器发送了SQL语句 1w次，SQL语句被编译了1w次，SQL语句也被执行了1w次，故效率极慢

package cs.kaoyan.mysql.batch;

import com.mysql.jdbc.Driver;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Batch1 {
    //连接数据库需要的数据
    private static final String url =
            //jdbc:mysql:连接mysql的协议
            //db47指的数据库
            "jdbc:mysql://localhost:3306/db47?useSSL=false&characterEncoding=utf8";
    private static final String username = "root";
    private static final String password = "123456";

    public static void main(String[] args) throws SQLException {
        //注册驱动
        new Driver();

        //获取连接
        Connection connection = DriverManager.getConnection(url,username,password);
        //获取statement,需要抛出异常
        Statement statement = connection.createStatement();
        //插入数据(10000条数据)
        for (int i = 1; i <= 10000; i++) {
            //生成姓名
            String name = "stu" + i;
            String sql = "insert into user values (" + i + ",'" + name + "','123')";
            //执行插入操作
            statement.executeUpdate(sql);
        }

        //关闭连接
        connection.close();
        statement.close();
    }
}

code2 使用PreparedStatement方式(用时45s)

时间开销：

虽然只编译一次，但是仍然需要执行sql语句1w次，故效率不会差别太大
有预编译，无批处理，向MySQL服务器发送了SQL语句 1次，SQL语句被编译了一次，发送了数据1w次，SQL语句被执行了1w次

package cs.kaoyan.mysql.batch;

import com.mysql.jdbc.Driver;

import java.sql.*;

public class Batch2 {
    //连接数据库需要的数据
    private static final String url =
            //jdbc:mysql:连接mysql的协议
            //db47指的数据库
            "jdbc:mysql://localhost:3306/db47?useSSL=false&characterEncoding=utf8";
    private static final String username = "root";
    private static final String password = "123456";

    public static void main(String[] args) throws SQLException {
        //注册驱动
        new Driver();

        //获取连接
        Connection connection = DriverManager.getConnection(url,username,password);

        //sql语句,其中的？是占位符
        String sql = "insert into user values (?,?,?)";
        //使用预编译减少数据库的编译次数
        //只编译一次
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //插入数据
        for (int i = 1; i <= 10000; i++) {
            //生成姓名
            String name = "stu" + i;
            //把数据库db47中的user表的第1列设置为i
            preparedStatement.setInt(1,i);
            //把数据库db47中的user表的第2列设置为name
            preparedStatement.setString(2,name);
            //把数据库db47中的user表的第3列设置为123,即密码写死
            preparedStatement.setString(3,"123");

            //执行sql语句
            preparedStatement.execute();
        }
        //关闭连接
        connection.close();
        preparedStatement.close();
    }
}

code3 使用Statement的批处理方式(用时42s)

时间开销：
向MySQL服务器发送了SQL语句1次，这一次中包含1w条SQL语句信息，SQL语句被编译了1w次，SQL语句也被执行了1w次，时间开销大

package cs.kaoyan.mysql.batch;

import com.mysql.jdbc.Driver;

import java.sql.*;

public class Batch3 {
    //连接数据库需要的数据
    private static final String url =
            //jdbc:mysql:连接mysql的协议
            //db47指的数据库
            "jdbc:mysql://localhost:3306/db47?useSSL=false&characterEncoding=utf8";
    private static final String username = "root";
    private static final String password = "123456";

    public static void main(String[] args) throws SQLException {
        //注册驱动
        new Driver();

        //获取connection连接
        Connection connection = DriverManager.getConnection(url,username,password);
        //获取statement连接
        Statement statement = connection.createStatement();

        //插入数据
        for (int i = 1; i <= 10000; i++) {
            String name = "stu" + i;
            String sql = "insert into user values (" + i + ",'" + name + "','123')";

            //暂时没有发送请求(类似加入缓存)
            statement.addBatch(sql);
        }

        //执行批处理:一次把上面10000条sql语句发送走
        //数据库编译了10000条sql语句
        statement.executeBatch();
        //关闭连接
        connection.close();
        statement.close();
    }
}

code4 使用PreparedStatement的批处理方式(用时43s)

时间开销：

数据库编译了1条，连接发送了两次: 1次sql，1次批处理的数据
有预编译，有批处理，效率较好

package cs.kaoyan.mysql.batch;

import com.mysql.jdbc.Driver;

import java.sql.*;

public class Batch4 {
    //连接数据库需要的数据
    private static final String url =
            //jdbc:mysql:连接mysql的协议
            //db47指的数据库
            "jdbc:mysql://localhost:3306/db47?useSSL=false&characterEncoding=utf8";
    private static final String username = "root";
    private static final String password = "123456";

    public static void main(String[] args) throws SQLException {
        //注册驱动
        new Driver();

        //获取connection连接
        Connection connection = DriverManager.getConnection(url,username,password);

        //sql语句,其中的？是占位符
        String sql = "insert into user values (?,?,?)";
        //使用预编译减少数据库的编译次数
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //插入数据
        for (int i = 1; i <= 10000; i++) {
           String name = "stu" + i;
           preparedStatement.setInt(1,i);
           preparedStatement.setString(2,name);
           preparedStatement.setString(3,"123");
           //批处理
           preparedStatement.addBatch();
        }

        //执行sql
        preparedStatement.executeBatch();

        //关闭连接
        connection.close();
        preparedStatement.close();
    }
}

事务

假如我们在维护一个银行的用户表，在我们维护这个表的过程中比较常见的行为就是转账，假如在某个时刻两个用户存在相互转账的需求，我们在实现这个转账逻辑中又有可能遇到SQL错误、链接失败等问题，通过事务可以保证转账的正确性，防止一方付了钱但是另一方并未收到转账的情况出现。

前期准备1：创建表格

银行账户表：

CREATE TABLE `account` (
`id` int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` varchar(10) DEFAULT NULL,
`money` float DEFAULT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

前期准备2：插入数据

insert into account values(1, "鹅城黄四郎1",  30000 );
insert into account values(2, "鹅城家族2",  2000 );
insert into account values(3, "鹅城家族3",  2000 );
insert into account values(4, "鹅城家族4",  2000 );
insert into account values(5, "张麻子",  10 );

初始状态

在这里插入图片描述

code

package cs.kaoyan.mysql.transaction;

import com.mysql.jdbc.Driver;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class Demo1 {
    //连接数据库需要的数据
    private static final String url =
            //jdbc:mysql:连接mysql的协议
            //db47指的数据库
            "jdbc:mysql://localhost:3306/db47?useSSL=false&characterEncoding=utf8";
    private static final String username = "root";
    private static final String password = "123456";

    public static void main(String[] args) throws SQLException {
        //注册驱动
        new Driver();
        //connection连接
        Connection connection = DriverManager.getConnection(url, username, password);
        //statement连接
        Statement statement = connection.createStatement();
        //开启事务,同一个事务里面的操作要么都成功,要么都失败
        //保证转账的正确性
        connection.setAutoCommit(false);

        String sql1 = "update account set money = money - 200 where id = 1";
        String sql2 = "update account set money = money + 200 where id = 5";

        try{
            statement.executeUpdate(sql1);
            statement.executeUpdate(sql2);
        }catch (Exception e){
            //若sql语句出现了问题,则回滚事务
            //回滚事务之后,之前所有的操作都会失效
            connection.rollback();
        }

        //执行到这里说明sql没出问题,可以提交事务
        //提交事务之后,从开启事务到提交事务之间所有的操作才会生效
        connection.commit();

        //关闭资源
        connection.close();
        statement.close();
    }
}