SQL批量处理+JDBC操作大数据及工具类的封装

SQL批量处理+JDBC操作大数据及工具类的封装

一、批处理 — 批量处理sql语句

在jdbc的url中添加rewriteBatchedStatements=true参数，可以提高批处理执行效率。

在我们进行大批量数据操作的时候，需要采用批处理的方式来提高程序的运行性能，目的是减少跟数据库交互的次数

1.批量执行多条sql语句，sql语句不相同（statement.addBatch(inserSql)）

import com.qf.utils.DBUtil;

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

public class Test01 {
   
    public static void main(String[] args) throws SQLException {
   

        Connection connection = DBUtil.getConnection();
        Statement statement = connection.createStatement();

        String sql1 = "INSERT INTO student(name,sex,age,salary,course,password) VALUES('小橘子','男',25,15000,'Java','12345678');";
        String sql2 = "UPDATE student set password = '66666666'";

        //将SQL命令添加到Batch包
        statement.addBatch(sql1);
        statement.addBatch(sql2);

        //将Batch包发送给MySQL
        statement.executeBatch();

        DBUtil.close(connection,statement,null);

    }
}

2.批量插入100条记录，sql语句相同，只是参数值不同(statement.addBatch()😉

import com.qf.utils.DBUtil;

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

public class Test02 {

    public static void main(String[] args) throws SQLException {

        Connection connection = DBUtil.getConnection();

        String sql = "INSERT INTO student (name,sex,age,salary,course,password) VALUES(?,?,?,?,?,?); ";
        PreparedStatement statement = connection.prepareStatement(sql);

        for (int i = 1;i <= 1000;i++){
            statement.setString(1,"盘古" + i);
            statement.setString(2,"男");
            statement.setInt(3,200);
            statement.setFloat(4,15000);
            statement.setString(5,"Java");
            statement.setString(6,"123456789");

            //将当前SQL命令添加到Batch包中
            statement.addBatch();
        }
            statement.executeBatch();
            DBUtil.close(connection,statement,null);
    }
}

3.加入事务，批量插入10004条记录，sql语句相同，只是参数值不同，并且分批次发送

import com.qf.utils.DBUtil;

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

public class Test03 {
   

    public static void main(String[] args) throws SQLException {
   

        DBUtil.startTransaction();

        Connection connection = DBUtil.getConnection();

        String sql = "INSERT INTO student (name,sex,age,salary,course,password) VALUES(?,?,?,?,?,?); ";
        PreparedStatement statement = connection.prepareStatement(sql);

        for (int i = 1;i <= 10005;i++){
   
            statement.setString(1,"女娲" + i);
            statement.setString(2,"女");
            statement.setInt(3,100);
            statement.setFloat(4,10000);
            statement.setString(5,"Java");
            statement.setString(6,"123456789");

            //将当前SQL命令添加到Batch包中
            statement.addBatch();

            if(i %1000 == 0){
   
                statement.executeBatch();
                //清空Batch包中的SQL命令
                statement.clearBatch();

            }
        }
            statement.executeBatch();
            DBUtil.commit();
            DBUtil.close(connection,statement,null);
    }
}

二、JDBC操作大数据

1.CLOB：将长文本数据存储到数据库中

类型：TEXT、LONGTEXT

import com.qf.utils.DBUtil;
import org.junit.Test;

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

public class Test01 {
   

    @Test
    public void test01() throws SQLException, FileNotFoundException {
   
        //将长文本文本数据写入到数据库中

        Connection connection = DBUtil.getConnection();

        String sql = "insert into cblob(C_CLOB) values(?)";
        PreparedStatement statement = connection.prepareStatement(sql);

        //设置参数
        File file = new File("小说.txt");
        FileReader fr = new FileReader(file);
        statement.setCharacterStream(1,fr,(int)file.length());

        statement.executeUpdate();

        DBUtil.close(connection,statement,null);
    }

    @Test
    public void test02() throws SQLException, IOException {
   
        //获取数据库中的长文本文本数据

        Connection connection = DBUtil.getConnection();

        String sql = " select * from cblob where id=1";
        PreparedStatement statement = connection.prepareStatement(sql);
        ResultSet resultSet = statement.executeQuery();

        if(r