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);

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

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);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

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);
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

二、JDBC操作大数据

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

类型：TEXT、LONGTEXT
1

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(resultSet.next()){
            Reader r = resultSet.getCharacterStream("C_CLOB");
            FileWriter fw = new FileWriter("copy.txt");
            char[] cs = new char[1024];
            int len;
            while((len=r.read(cs)) != -1){
                fw.write(cs,0,len);
            }
            r.close();
            fw.close();
        }

        DBUtil.close(connection,statement,resultSet);

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56

2.BLOB：将二进制文件存储到数据库中

类型：BLOB、LONGBLOB
1

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 Test02 {

    @Test
    public void test01() throws SQLException, FileNotFoundException {
        //将二进制数据写入到数据库中

        Connection connection = DBUtil.getConnection();

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

        //设置参数
        File file = new File("海底世界.jpg");
        FileInputStream fis = new FileInputStream(file);
        statement.setBinaryStream(1, fis,(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(resultSet.next()){
            InputStream in = resultSet.getBinaryStream("B_BLOB");
            FileOutputStream out = new FileOutputStream("copy.jpg");
            byte[] bs = new byte[1024];
            int len;
            while((len = in.read(bs)) != -1){
                out.write(bs,0,len);
            }
            in.close();
            out.close();
        }

        DBUtil.close(connection,statement,resultSet);

    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

三、数据库工具类的封装

JDBC所有内容应结合DBUtil工具包的使用

因为部分结构方法被封装，一劳永逸

封装一次永久使用，复制到项目即可使用

import java.io.IOException;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

//数据库工具类
public class DBUtil {

    private  static  String url;
    private  static  String username;
    private  static  String password;


    static {
        //获取配置文件对象
        Properties properties = new Properties();

        try {
            //加载配置文件
            properties.load(DBUtil.class.getClassLoader().getResourceAsStream("DBConfig.properties"));
        } catch (IOException e) {
            e.printStackTrace();
        }
        String driverName = properties.getProperty("driverName");
        url = properties.getProperty("url");
        username = properties.getProperty("username");
        password = properties.getProperty("password");


        try {
            //导入驱动包
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    private static  ThreadLocal<Connection>local = new ThreadLocal<>();

    //开启事务
    public  static void startTransaction() throws SQLException {
        Connection connection = getConnection();
        connection.setAutoCommit(false);
    }

    //提交事务
    public static  void commit() throws SQLException {
        Connection connection = local.get();
        if(connection != null){
            connection.commit();
            connection.close();
            local.set(null);
        }
    }

    //回滚事务
    public static void rollback() throws SQLException {
        Connection connection = local.get();
        if (connection != null) {
            connection.rollback();
            connection.close();
            local.set(null);
        }
    }
    //获取连接对象
    public  static Connection getConnection() throws SQLException {
        Connection connection = local.get();
        if (connection == null) {
            connection = DriverManager.getConnection(url, username, password);
            local.set(connection);
        }
        return connection;
    }

    //关闭资源
    public static void close(Connection connection, Statement statement, ResultSet resultSet){
        if(resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection != null){
            try {
                if (connection.getAutoCommit()){//没有开启事务，MySQL自动管理提交
                    connection.close();
                    local.set(null);
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    //sql -- insert into xxx(username,name,password,salary,age) values(?,?,?,?,?)
    //参数

    //设置sql参数的方法
    private static void setParameter(PreparedStatement statement,Object... params) throws SQLException {
        for (int i = 0; i < params.length; i++) {
            statement.setObject(i+1,params[i]);
        }
    }
    //更新数据 -- （添加、删除、修改）
    public static int commonUpdate(String sql,Object... params){

        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = getConnection();
            statement = connection.prepareStatement(sql);
            setParameter(statement,params);
            int num = statement.executeUpdate();
            return num;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection,statement,null);
        }
        return -1;
    }

    //主键回填
    public static int commonInsert(String sql,Object... params){
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = getConnection();
            statement = connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
            setParameter(statement,params);
            statement.executeUpdate();

            //获取主键
            ResultSet resultSet = statement.getGeneratedKeys();
            if(resultSet.next()){
                int primaryKey = resultSet.getInt(1);
                return primaryKey;
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(connection,statement,null);
        }
        return -1;
    }

    //select * from user where id < 6

    //查询数据
    public static <T> List<T> commonQuery(Class<T> clazz,String sql,Object... params){

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = getConnection();
            statement = connection.prepareStatement(sql);
            setParameter(statement,params);

            //获取表信息
            ResultSetMetaData metaData = statement.getMetaData();
            //获取表中字段的个数
            int columnCount = metaData.getColumnCount();

            //创建集合
            List<T> list = new ArrayList<>();

            resultSet = statement.executeQuery();
            while(resultSet.next()){//1 女娲 女 22 12000 java 00000000

                //利用反射创建实体类对象
                T obj = clazz.newInstance();

                //循环获取字段名，并利用反射机制添加到实体类对象中
                for (int i = 1; i <= columnCount; i++) {
                    //字段名
                    String columnName = metaData.getColumnName(i);
                    //获取字段名对应的值
                    Object columnValue = resultSet.getObject(columnName);

                    //获取属性对象
                    Field field = getField(clazz, columnName);
                    if(field != null){
                        field.setAccessible(true);
                        field.set(obj,columnValue);
                    }
                }

                list.add(obj);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } finally {
            close(connection,statement,resultSet);
        }
        return null;
    }

    //利用反射获取实体类中的属性对象
    private static Field getField(Class<?> clazz,String fieldName){

        for(Class<?> c = clazz;c != null;c=c.getSuperclass()){
            try {
                Field field = c.getDeclaredField(fieldName);
                return field;
            } catch (NoSuchFieldException e) {
            }
        }
        return null;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229

四、使用数据库工具类 操作数据库

public class Student {

    private int id;
    private String name;
    private String sex;
    private  int age;
    private float salary;
    private String course;
    private String password;

    public Student() {
    }

    public Student(int id, String name, String sex, int age, float salary, String course, String password) {
        this.id = id;
        this.name = name;
        this.sex = sex;
        this.age = age;
        this.salary = salary;
        this.course = course;
        this.password = password;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public float getSalary() {
        return salary;
    }

    public void setSalary(float salary) {
        this.salary = salary;
    }

    public String getCourse() {
        return course;
    }

    public void setCourse(String course) {
        this.course = course;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", age=" + age +
                ", salary=" + salary +
                ", course='" + course + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92

public class User {
    private int id;
    private String name;
    private float money;

    public User() {
    }

    public User(int id, String name, float money) {
        this.id = id;
        this.name = name;
        this.money = money;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public float getMoney() {
        return money;
    }

    public void setMoney(float money) {
        this.money = money;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", money=" + money +
                '}';
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47

import com.qf.pojo.Student;
import com.qf.pojo.User;
import com.qf.utils.DBUtil;
import org.junit.Test;

import java.sql.*;
import java.util.List;

public class Test01 {
    @Test
    public void test01() {
        //添加数据
        String sql = "INSERT INTO student(name,sex,age,salary,course,password) VALUES(?,?,?,?,?,?);";
        DBUtil.commonUpdate(sql,"abc","男",22,12000,"Java","12344321");
    }

    @Test
    public void test02() {
        //删除数据
        String sql = "delete from student where id>?";
        DBUtil.commonUpdate(sql,10);
    }

    @Test
    public void test03() {
        //修改数据
        String sql = "update student set salary=? where id=?";
        DBUtil.commonUpdate(sql,30000,3);
    }

    @Test
    public void test04() {
        //主键回填

        String sql = "INSERT INTO student(name,sex,age,salary,course,password) VALUES(?,?,?,?,?,?);";
        int primaryKey = DBUtil.commonInsert(sql, "孙策", "男", 22, 12000, "Java", "12344321");
        System.out.println(primaryKey);
    }

    @Test
    public void test05() {
        //查询数据 -- 查询学生表

        String sql = "select * from student where id < ?";
        List<Student> stuList = DBUtil.commonQuery(Student.class, sql, 5);
        for (Student stu:stuList) {
            System.out.println(stu);
        }
    }

    @Test
    public void test06() {
        //查询数据 -- 查询用户表

        String sql = "select * from user";
        List<User> userList = DBUtil.commonQuery(User.class, sql);
        for (User user:userList) {
            System.out.println(user);
        }
    }

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62