批处理 JDBC 语句提高处理速度

批处理:

向数据库表中插入n多条SQL语句, 如何处理一下, 提高执行的性能.

先看一下几种方式的对比, 向表中插入10万条数据:

1.只使用Statement

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
//JDBCTools.class:
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/*
 * 操作 JDBC 的工具类,封装了一些常用的方法
 */
public class JDBCTools {
    /**
	 * 获取数据库连接:
	 * 通过读取配置文件,根据配置信息从数据库服务器获取一个连接
	 * @return
	 * @throws Exception 
	 */
    public static Connection getConnection() throws Exception {
        InputStream is = JDBCTools.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties pro = new Properties();
        pro.load(is);
        String url = pro.getProperty("jdbc");
        String user = pro.getProperty("user");
        String password = pro.getProperty("password");
        Class.forName(pro.getProperty("driver"));
        Connection conn = DriverManager.getConnection(url, user, password);
        return conn;
    }
    /**
	 * 开始事务
	 * @param conn
	 */
    public static void beginTx(Connection conn) {
        if(conn != null) {
            try {
                conn.setAutoCommit(false);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    /**
	 * 提交事务
	 * @param conn
	 */
    public static void commit(Connection conn) {
        if(conn != null) {
            try {
                conn.commit();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    /**
	 * 回滚事务
	 * @param conn
	 */
    public static void rollback(Connection conn) {
        if(conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    /**
	 * 关闭 ResultSet Statement 和 Connection 资源
	 * @param rs
	 * @param state
	 * @param conn
	 */
    public static void release(ResultSet rs,Statement state,Connection conn) {
        if(rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(state != null) {
            try {
                state.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
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
//DAO.class:
import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.beanutils.BeanUtils;

public class DAO {
    /**
	 * 通用的更新方法,包括: Insert Delete Update
	 * @param sql
	 * @param args
	 */
    public void update(String sql, Object ... args) {
        Connection conn = null;
        PreparedStatement psmt = null;
        try {
            //1. 获取数据库连接
            conn = JDBCTools.getConnection();
            psmt = conn.prepareStatement(sql);
            for(int i = 0;i < args.length;i ++) {
                psmt.setObject(i+1, args[i]);
            }
            //2. 执行 SQL语句
            psmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JDBCTools.release(null, psmt, conn);
        }
    }
    /**
	 * 通用的查询方法,SQL为select类型.
	 * 查询结果最多是一行记录.
	 * @param clazz
	 * @param sql
	 * @param args
	 * @return
	 */
    public <T> T get(Class<T> clazz, String sql, Object ... args) {
        List<T> list = getForList(clazz, sql, args);
        if(list.size()>0) {
            return list.get(0);
        }
        return null;
    }
    /**
	 * 通用的查询方法,SQL为select类型.
	 * 查询结果可以为多行记录(0~n).
	 * @param clazz
	 * @param sql
	 * @param args
	 * @return
	 */
    public <T> List<T>  getForList(Class<T> clazz, String sql, Object ... args){
        List<T> list = new ArrayList<>();
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCTools.getConnection();
            psmt = conn.prepareStatement(sql);
            for(int i = 0;i < args.length;i ++) {
                psmt.setObject(i+1, args[i]);
            }
            //1. 获取连接再获取结果集
            rs = psmt.executeQuery();
            //2. 将结果集转换为Map的List
            List<Map<String, Object>> values = handleResultSetToMapList(rs);
            //3. 将 Map 的List 转换为 JavaBean 对应的List
            list = transferMapListToBeanList(clazz, values);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    /**
	 * 查询方法,结果为一行中的一列
	 * @param sql
	 * @param args
	 * @return
	 */
    @SuppressWarnings("unchecked")
    public <E> E getForValue(String sql, Object ... args) {
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCTools.getConnection();
            psmt = conn.prepareStatement(sql);
            for(int i = 0;i < args.length;i ++) {
                psmt.setObject(i+1, args[i]);
            }
            rs = psmt.executeQuery();
            if(rs.next()) {
                return (E)rs.getObject(1);
            }
        } catch(Exception ex) {
            ex.printStackTrace();
        }finally {
            JDBCTools.release(rs, psmt, conn);
        }
        return null;
    }
    /**
	 * 获取结果集的 ColumnLabel 对应的 List
	 * @param rs
	 * @return
	 * @throws Exception
	 */
    private List<String> getColumnLabels(ResultSet rs) throws SQLException{
        List<String> labels = new ArrayList<>();
        ResultSetMetaData rsmd = rs.getMetaData();
        for(int i = 0;i < rsmd.getColumnCount();i++) {
            labels.add(rsmd.getColumnLabel(i+1));
        }
        return labels;
    }
    /**
	 * 处理结果集得到一个 Map 的List, 其中一个 Map 代表一个记录.
	 * @param rs
	 * @return
	 * @throws Exception
	 */
    private List<Map<String, Object>> handleResultSetToMapList(ResultSet rs) throws SQLException{
        List<String> labels = this.getColumnLabels(rs);
        List<Map<String,Object>> values = new ArrayList<>();
        Map<String,Object> map = null;
        while(rs.next()){
            map = new HashMap<>();
            for(String key :labels) {
                Object value = rs.getObject(key);
                map.put(key, value);
            }
            values.add(map);
        }
        return values;
    }
    /**
	 * 将 Map 的 List 转换为 JavaBean对象的 List
	 * @param clazz
	 * @param values
	 * @return
	 * @throws InstantiationException
	 * @throws IllegalAccessException
	 * @throws InvocationTargetException
	 */
    private <T> List<T> transferMapListToBeanList(Class<T> clazz, List<Map<String, Object>> values)
			throws InstantiationException, IllegalAccessException, InvocationTargetException {
        List<T> list = new ArrayList<>();
        T bean = null;
        if(values.size()>0) {
            for(Map<String,Object> m:values) {
                bean = clazz.newInstance();
                for(Map.Entry<String, Object> entry:m.entrySet()) {
                    String propertyName = entry.getKey();
                    Object propertyValue = entry.getValue();
                    BeanUtils.setProperty(bean, propertyName, propertyValue);
                }
                list.add(bean);
            }
        }
        return list;
    }
}
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
@Test
public void testBatchWithStatement() {
    Connection conn = null;
    Statement state = null;
    String sql = null;
    try {
        conn = JDBCTools.getConnection();
        JDBCTools.beginTx(conn);
        state = conn.createStatement();
        long begin = System.currentTimeMillis();
        for(int i = 0;i < 100000;i ++) {
            sql = "INSERT INTO Student VALUES("+(i+1)
                +",'"+"name_"+i+"','alian@qq.com','1999-09-09')";
            state.executeUpdate(sql);
        }
        long end = System.currentTimeMillis();
        System.out.println("Time:"+(end-begin));//Time:30442 左右
        JDBCTools.commit(conn);
    } catch (Exception e) {
        e.printStackTrace();
        JDBCTools.rollback(conn);
    }finally {
        JDBCTools.release(null, state, conn);
    }
}

2.只使用PreparedStatement

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
@Test
public void testBatchWithPreparedStatement() {
    Connection conn = null;
    PreparedStatement psmt = null;
    String sql = null;
    try {
        conn = JDBCTools.getConnection();
        JDBCTools.beginTx(conn);
        sql = "INSERT INTO Student VALUES(?,?,?,?)";
        Date date = new Date(new java.util.Date().getTime());
        psmt = conn.prepareStatement(sql);
        long begin = System.currentTimeMillis();
        for(int i = 0;i < 100000;i ++) {
            psmt.setInt(1, i+1);
            psmt.setString(2, "name_"+i);
            psmt.setString(3, "name_"+i+"@qq.cp.com");
            psmt.setDate(4, date);
            psmt.executeUpdate();
        }
        long end = System.currentTimeMillis();
        System.out.println("Time:"+(end-begin));//Time:19754
        JDBCTools.commit(conn);
    } catch (Exception e) {
        e.printStackTrace();
        JDBCTools.rollback(conn);
    }finally {
        JDBCTools.release(null, psmt, conn);
    }
}

3.使用Batch

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
@Test
public void testBatch() {
    Connection conn = null;
    PreparedStatement psmt = null;
    String sql = null;
    try {
        conn = JDBCTools.getConnection();
        JDBCTools.beginTx(conn);
        sql = "INSERT INTO Student VALUES(?,?,?,?)";
        Date date = new Date(new java.util.Date().getTime());
        psmt = conn.prepareStatement(sql);
        long begin = System.currentTimeMillis();
        for(int i = 0;i < 100000;i ++) {
            psmt.setInt(1, i+1);
            psmt.setString(2, "name_"+i);
            psmt.setString(3, "name_"+i+"@qq.cp.com");
            psmt.setDate(4, date);
            // "积攒" SQL
            psmt.addBatch();
            //当"积攒"到一定程度时,统一向数据库发送一次SQL 并清空"积攒"
            if((i+1)%300==0) {
                psmt.executeBatch();
                psmt.clearBatch();
            }
        }
        if(100000%300 != 0) {
            psmt.executeBatch();
            psmt.clearBatch();
        }
        long end = System.currentTimeMillis();
        System.out.println("Time:"+(end-begin));//Time:2546
        JDBCTools.commit(conn);
    } catch (Exception e) {
        e.printStackTrace();
        JDBCTools.rollback(conn);
    }finally {
        JDBCTools.release(null, psmt, conn);
    }
}

博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议

本文永久链接是：https://crainyday.gitee.io/JDBC1-8.html

相关文章

评论