JDBC操作封装工具类JdbcUtils及其使用方法

发表于 | 分类于 |

简介

在进行后端业务开发大量使用ORM框架的情况下，已经很少有机会直接使用到JDBC进行数据的写入查询了。
最近正好有机会，于是整理码了一个JdbcUtils工具类，包含数据源连接、断连、数据查询、数据更新及命令执行，方便直接使用Jdbc进行数据操作。

设计

废话不多说，直接上完整源码：

JdbcUtils.java

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
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * Create by xuyh at 2019/9/19 15:49.
 */
public class JdbcUtils {
    private static Logger logger = LoggerFactory.getLogger(JdbcUtils.class);

    /**
     * Connect to database.
     *
     * @param driver   {@link Driver} driver implement class name.
     * @param url      db connection url
     * @param name     db authentication name
     * @param password db authentication password
     * @return {@link Connection}
     */
    public static Connection connect(String driver, String url, String name, String password) {
        Connection connection = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, name, password);
        } catch (Exception e) {
            logger.warn(e.getMessage(), e);
        }
        return connection;
    }

    /**
     * Disconnect from a database.
     *
     * @param connection {@link Connection}
     */
    public static void disconnect(Connection connection) {
        try {
            if (connection != null) {
                connection.close();
            }
        } catch (Exception e) {
            logger.warn(e.getMessage(), e);
        }
    }

    /**
     * Execute command.
     *
     * @param connection {@link Connection}
     * @param cmd        Command
     * @return true/false
     */
    public static boolean execute(Connection connection, String cmd) {
        if (connection == null || cmd == null || cmd.isEmpty()) {
            return false;
        }
        Statement statement = null;
        try {
            statement = connection.createStatement();
            statement.execute(cmd);
            return true;
        } catch (Exception e) {
            logger.warn(e.getMessage(), e);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                    logger.warn(e.getMessage(), e);
                }
            }
        }
        return false;
    }

    /**
     * Execute query sql.
     *
     * @param connection {@link Connection}
     * @param sql        Query sql statement
     * @param params     Query sql parameters
     * @return result rows [{"id":1}, {"id":2}, {"id":3}, {"id":4}]
     */
    public static List<Map<String, Object>> query(Connection connection, String sql, Object... params) {
        return query(connection, Command.build().sql(sql).params(params));
    }

    /**
     * Execute query sql.
     *
     * @param connection {@link Connection}
     * @param command    {@link Command}
     * @return result rows [{"id":1}, {"id":2}, {"id":3}, {"id":4}]
     */
    public static List<Map<String, Object>> query(Connection connection, Command command) {
        List<Map<String, Object>> rows = new ArrayList<>();
        query(connection, command, (row, number) -> rows.add(row));
        return rows;
    }

    /**
     * Execute query sql.
     *
     * @param connection         {@link Connection}
     * @param command            {@link Command}
     * @param resultRowProcessor {@link ResultRowProcessor}
     * @return result row count
     */
    public static int query(Connection connection, Command command, ResultRowProcessor... resultRowProcessor) {
        if (connection == null || command == null || command.getSql() == null
                || resultRowProcessor == null || resultRowProcessor.length == 0) {
            return 0;
        }
        AtomicInteger dataCount = new AtomicInteger(0);
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(command.getSql());
            if (command.getParams() != null) {
                for (int i = 0; i < command.getParams().size(); i++) {
                    statement.setObject(i + 1, command.getParams().get(i));
                }
            }
            resultSet = statement.executeQuery();
            List<String> columns = new ArrayList<>();
            for (int i = 0; i < resultSet.getMetaData().getColumnCount(); i++) {
                columns.add(resultSet.getMetaData().getColumnName(i + 1));
            }
            while (resultSet.next()) {
                int currentRowNum = dataCount.incrementAndGet();
                Map<String, Object> row = new LinkedHashMap<>();
                for (String column : columns) {
                    row.put(column, resultSet.getObject(column));
                }
                for (ResultRowProcessor processor : resultRowProcessor) {
                    processor.process(row, currentRowNum);
                }
            }
        } catch (Exception e) {
            logger.warn(e.getMessage(), e);
        } finally {
            if (resultSet != null) {
                try {
                    resultSet.close();
                } catch (Exception e) {
                    logger.warn(e.getMessage(), e);
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                    logger.warn(e.getMessage(), e);
                }
            }
        }
        return dataCount.get();
    }

    /**
     * Execute update sql statement.
     *
     * @param connection {@link Connection}
     * @param sql        Update sql statement
     * @param params     Update sql parameters
     * @return affected rows
     */
    public static int update(Connection connection, String sql, Object... params) {
        return update(connection, Command.build().sql(sql).params(params));
    }

    /**
     * Execute update sql statement.
     *
     * @param connection {@link Connection}
     * @param command    {@link Command}
     * @return affected rows
     */
    public static int update(Connection connection, Command command) {
        if (connection == null || command == null || command.getSql() == null) {
            return 0;
        }
        int result = 0;
        PreparedStatement statement = null;
        try {
            statement = connection.prepareStatement(command.getSql());
            if (command.getParams() != null) {
                for (int i = 0; i < command.getParams().size(); i++) {
                    statement.setObject(i + 1, command.getParams().get(i));
                }
            }
            result = statement.executeUpdate();
        } catch (Exception e) {
            logger.warn(e.getMessage(), e);
        } finally {
            if (statement != null) {
                try {
                    statement.close();
                } catch (Exception e) {
                    logger.warn(e.getMessage(), e);
                }
            }
        }
        return result;
    }

    /**
     * Execute update sql transaction.
     *
     * @param connection {@link Connection}
     * @param commands   sql commands {@link Command}
     * @return true/false
     */
    public static boolean transaction(Connection connection, Command... commands) {
        if (connection == null || commands == null || commands.length == 0) {
            return false;
        }
        List<PreparedStatement> statements = new ArrayList<>();
        try {
            connection.setAutoCommit(false);
            for (Command command : commands) {
                String sql = command.getSql();
                List<Object> params = command.getParams();
                PreparedStatement statement = connection.prepareStatement(sql);
                if (params != null) {
                    for (int i = 0; i < params.size(); i++) {
                        statement.setObject(i + 1, params.get(i));
                    }
                }
                statements.add(statement);
                if (statement.executeUpdate() <= 0) {
                    throw new RuntimeException("Rollback.");
                }
            }
            connection.commit();
            return true;
        } catch (Exception e) {
            logger.warn(e.getMessage(), e);
            try {
                connection.rollback();
            } catch (Exception ex) {
                logger.warn(ex.getMessage(), ex);
            }
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (Exception e) {
                logger.warn(e.getMessage(), e);
            }
            for (PreparedStatement statement : statements) {
                try {
                    statement.close();
                } catch (Exception e) {
                    logger.warn(e.getMessage(), e);
                }
            }
        }
        return false;
    }

    /**
     * Build an empty sql command.
     *
     * @return {@link Command}
     */
    public static Command buildCommand() {
        return Command.build();
    }

    /**
     * Build a sql command with sql statement and parameters.
     *
     * @param sql    sql statement
     * @param params sql parameters
     * @return {@link Command}
     */
    public static Command buildCommand(String sql, Object... params) {
        return Command.build().sql(sql).params(params);
    }

    /**
     * SQL command.
     * <pre>
     *     sql statement
     *     sql parameters
     * </pre>
     */
    public static class Command {
        private String sql;
        private List<Object> params;

        public static Command build() {
            return new Command();
        }

        public String getSql() {
            return sql;
        }

        public void setSql(String sql) {
            this.sql = sql;
        }

        public Command sql(String sql) {
            this.sql = sql;
            return this;
        }

        public List<Object> getParams() {
            return params;
        }

        public void setParams(List<Object> params) {
            this.params = params;
        }

        public Command params(Object... params) {
            this.params = Arrays.asList(params);
            return this;
        }

        @Override
        public String toString() {
            return "Command{" +
                    "sql='" + sql + '\'' +
                    ", params=" + params +
                    '}';
        }
    }

    public static interface ResultRowProcessor {
        /**
         * Process row.
         *
         * @param row       current row data
         * @param rowNumber current row number
         */
        void process(Map<String, Object> row, int rowNumber);
    }
}

用法示例

执行命令

代码

1
2
3
4
5
6
7
Connection connection = JdbcUtils.connect(Driver.class.getCanonicalName(), "jdbc:mysql://192.168.100.203:3306/test_commons?useSSL=false", "root", "123456");
System.out.println(JdbcUtils.execute(connection, "create table test(\n" +
        "id int(11) not null auto_increment primary key,\n" +
        "name varchar(255) null,\n" +
        "description text null\n" +
        ")"));
JdbcUtils.disconnect(connection);

运行结果

1
true

新增

代码

1
2
3
Connection connection = JdbcUtils.connect(Driver.class.getCanonicalName(), "jdbc:mysql://192.168.100.203:3306/test_commons?useSSL=false", "root", "123456");
System.out.println(JdbcUtils.update(connection, "insert into test(name,description) values(?, ?)", "Johnson-1", "test-description-1"));
JdbcUtils.disconnect(connection);

运行结果

1
1

更新

代码

1
2
3
Connection connection = JdbcUtils.connect(Driver.class.getCanonicalName(), "jdbc:mysql://192.168.100.203:3306/test_commons?useSSL=false", "root", "123456");
System.out.println(JdbcUtils.update(connection, JdbcUtils.buildCommand("update test set description = ? where name = ?", "abc-1", "johnson-1")));
JdbcUtils.disconnect(connection);

运行结果

1
1

删除

代码

1
2
3
Connection connection = JdbcUtils.connect(Driver.class.getCanonicalName(), "jdbc:mysql://192.168.100.203:3306/test_commons?useSSL=false", "root", "123456");
System.out.println(JdbcUtils.update(connection, JdbcUtils.Command.build().sql("delete from test where name = ?").params("johnson-1")));
JdbcUtils.disconnect(connection);

运行结果

1
1

查询

代码

1
2
3
Connection connection = JdbcUtils.connect(Driver.class.getCanonicalName(), "jdbc:mysql://192.168.100.203:3306/test_commons?useSSL=false", "root", "123456");
JdbcUtils.query(connection, JdbcUtils.buildCommand("select * from test"), (row, rowNumber) -> System.out.println(String.format("row: %s, data: %s", rowNumber, row)));
JdbcUtils.disconnect(connection);

运行结果

1
2
3
row: 1, data: {id=2, name=Johnson-1, description=test-description-1}
row: 2, data: {id=3, name=Johnson-2, description=test-description-2}
row: 3, data: {id=4, name=Johnson-3, description=test-description-3}

执行事务

代码

1
2
3
4
5
Connection connection = JdbcUtils.connect(Driver.class.getCanonicalName(), "jdbc:mysql://192.168.100.203:3306/test_commons?useSSL=false", "root", "123456");
System.out.println(JdbcUtils.transaction(connection,
        JdbcUtils.buildCommand("insert into test(name,description) values(?, ?)", "Johnson-4", "test-description-4"),
        JdbcUtils.buildCommand("insert into test(name,description) values(?, ?)", "Johnson-5", "test-description-5")));
JdbcUtils.disconnect(connection);

运行结果

1
true

代码仓库

https://github.com/johnsonmoon/commons/blob/master/src/main/java/com/github/johnsonmoon/commons/JdbcUtils.java