首页 > 代码库 > JDBC数据库通用DAO

JDBC数据库通用DAO

2024-07-18 16:44:23   223人阅读

包括DAO、连接池、事务控制。 
连接池用的是 BoneCP。 
BoneCP运行时需要slf4j和guava的支持。
当然也可以随意换成其他的连接池。
dao的vo是用结果转换器控制的，需要继承ResultConverter。
这招是和spring的jdbcTemplete学的
日志组件用的是slf4j，需要的可自行更换其他日志组件。
demo的在 http://www.oschina.net/code/snippet_86510_4466
末尾有源码和试例包下载 


[1].[代码] 数据库连接池 跳至 [1] [2] [3] [4] [5] [6] [7] [8]

?
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
packageorg.dave.common.database;
 
importjava.sql.Connection;
importjava.sql.SQLException;
importjava.util.ResourceBundle;
 
importorg.slf4j.Logger;
importorg.slf4j.LoggerFactory;
 
importcom.jolbox.bonecp.BoneCP;
importcom.jolbox.bonecp.BoneCPConfig;
 
/**
 * 数据库连接池
 * @author David Day
 */
publicfinalclassDatabaseConnectionPool {
 
    privatestaticfinalLogger LOG = LoggerFactory.getLogger(DatabaseConnectionPool.class);
    privatestaticfinalResourceBundle BUNDLE = ResourceBundle.getBundle("connection");
    privatestaticfinalString DRIVER ="driver";
    privatestaticfinalString URL ="url";
    privatestaticfinalString USERNAME ="username";
    privatestaticfinalString PASSWORD ="password";
    privatestaticfinalString MAX_CONNECTION ="max_connection";
    privatestaticBoneCP pool;
     
    /**
     * 开启连接池
     */
    publicstaticvoidstartup() {
        try{
            Class.forName(BUNDLE.getString(DRIVER));
            BoneCPConfig config =newBoneCPConfig();
            config.setJdbcUrl(BUNDLE.getString(URL));
            config.setUsername(BUNDLE.getString(USERNAME));
            config.setPassword(BUNDLE.getString(PASSWORD));
            config.setMaxConnectionsPerPartition(Integer.parseInt(BUNDLE.getString(MAX_CONNECTION)));
            pool =newBoneCP(config);
        }catch(Exception e) {
            e.printStackTrace();
            LOG.error(e.getMessage(), e);
            thrownewDatabaseException(e);
        }
    }
     
    /**
     * 关闭连接池
     */
    publicstaticvoidshutdown() {
        pool.shutdown();
    }
     
    /**
     * @return 数据库连接
     */
    publicstaticConnection getConnection() {
        try{
            returnpool.getConnection();
        }catch(SQLException e) {
            e.printStackTrace();
            LOG.error(e.getMessage(), e);
            thrownewDatabaseException(e);
        }
    }
 
}

[2].[代码] 数据库异常 跳至 [1] [2] [3] [4] [5] [6] [7] [8]

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
packageorg.dave.common.database;
 
/**
 * 数据库异常
 * @author David Day
 */
@SuppressWarnings("serial")
publicclassDatabaseExceptionextendsRuntimeException {
     
    publicDatabaseException(Throwable cause) {
        super(cause);
    }
 
}

[3].[代码] 事务控制 跳至 [1] [2] [3] [4] [5] [6] [7] [8]

?
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
packageorg.dave.common.database;
 
importjava.sql.Connection;
importjava.sql.SQLException;
 
importorg.slf4j.Logger;
importorg.slf4j.LoggerFactory;
 
/**
 * 数据库事务
 * @author David Day
 */
publicclassDatabaseTransaction {
     
    /**
     * 日志工具
     */
    privatestaticfinalLogger LOG = LoggerFactory.getLogger(DatabaseTransaction.class);
     
    /**
     * 数据库连接
     */
    privateConnection conn;
 
    /**
     * 实例化一个默认连接的事务
     */
    publicDatabaseTransaction() {
        this(DatabaseConnectionPool.getConnection());
    }
 
    /**
     * 实例化一个默认连接的事务
     * @param isOpenTrans 是否打开事务
     */
    publicDatabaseTransaction(booleanisOpenTrans)throwsDatabaseException {
        this(DatabaseConnectionPool.getConnection(), isOpenTrans);
    }
 
    /**
     * 实例化一个默认连接的事务
     * @param conn 数据库连接
     */
    publicDatabaseTransaction(Connection conn) {
        this.conn = conn;
    }
 
    /**
     * 实例化一个默认连接的事务
     * @param conn 数据库连接
     * @param isOpenTrans 是否打开事务
     */
    publicDatabaseTransaction(Connection conn,booleanisOpenTrans)throwsDatabaseException {
        this.conn = conn;
        setAutoCommit(!isOpenTrans);
    }
 
    /**
     * @return 数据库连接
     */
    publicConnection getConnection() {
        returnconn;
    }
     
    /**
     * 设置是否自动提交
     * @param autoCommit 自动提交
     * @throws DatabaseException
     */
    privatevoidsetAutoCommit(booleanautoCommit)throwsDatabaseException {
        try{
            conn.setAutoCommit(autoCommit);
        }catch(SQLException e) {
            e.printStackTrace();
            LOG.error(e.getMessage(), e);
            thrownewDatabaseException(e);
        }
    }
     
    /**
     * 开始事务
     * @throws DatabaseException
     */
    publicvoidbegin()throwsDatabaseException {
        setAutoCommit(false);
    }
     
    /**
     * @return 是否打开事务
     * @throws DatabaseException
     */
    publicbooleanisBegin()throwsDatabaseException {
        try{
            return!conn.getAutoCommit();
        }catch(SQLException e) {
            e.printStackTrace();
            LOG.error(e.getMessage(), e);
            thrownewDatabaseException(e);
        }
    }
     
    /**
     * 提交
     * @throws DatabaseException
     */
    publicvoidcommit()throwsDatabaseException {
        try{
            conn.commit();
        }catch(SQLException e) {
            e.printStackTrace();
            LOG.error(e.getMessage(), e);
            thrownewDatabaseException(e);
        }
    }
     
    /**
     * 回滚
     * @throws DatabaseException
     */
    publicvoidrollback()throwsDatabaseException {
        try{
            conn.rollback();
        }catch(SQLException e) {
            e.printStackTrace();
            LOG.error(e.getMessage(), e);
            thrownewDatabaseException(e);
        }
    }
     
    /**
     * 关闭连接
     * @throws DatabaseException
     */
    publicvoidclose()throwsDatabaseException {
        try{
            conn.close();
        }catch(SQLException e) {
            e.printStackTrace();
            LOG.error(e.getMessage(), e);
            thrownewDatabaseException(e);
        }
    }
     
    /**
     * @return 连接是否关闭
     * @throws DatabaseException
     */
    publicbooleanisClose()throwsDatabaseException {
        try{
            returnconn.isClosed();
        }catch(SQLException e) {
            e.printStackTrace();
            LOG.error(e.getMessage(), e);
            thrownewDatabaseException(e);
        }
    }
 
}

[4].[代码] 通用数据模型 跳至 [1] [2] [3] [4] [5] [6] [7] [8]

?
1
2
3
4
5
6
packageorg.dave.common.database;
 
importjava.io.Serializable;
 
@SuppressWarnings("serial")
publicabstractclassDataModelimplementsSerializable { }

[5].[代码] 结果转换器 跳至 [1] [2] [3] [4] [5] [6] [7] [8]

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
packageorg.dave.common.database.convert;
 
importjava.sql.ResultSet;
importjava.sql.SQLException;
 
/**
 * 结果映射器
 * @author David Day
 */
publicinterfaceResultConverter<T> {
     
    /**
     * 映射
     * @param rs 结果集
     * @return 映射结果
     * @throws SQLException
     */
    publicT convert(ResultSet rs)throwsSQLException ;
 
}

[6].[代码] 通用dao 跳至 [1] [2] [3] [4] [5] [6] [7] [8]

?
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
packageorg.dave.common.database.access;
 
importjava.sql.Connection;
importjava.sql.PreparedStatement;
importjava.sql.ResultSet;
importjava.sql.SQLException;
importjava.sql.Statement;
importjava.util.ArrayList;
importjava.util.List;
 
importorg.dave.common.database.convert.ResultConverter;
importorg.slf4j.Logger;
importorg.slf4j.LoggerFactory;
 
/**
 * 数据存取类
 * @author David Day
 */
publicabstractclassDataAccess {
     
    /**
     * 日志工具
     */
    privatestaticfinalLogger LOG = LoggerFactory.getLogger(DataAccess.class);
    /**
     * 数据库连接
     */
    privateConnection conn;
 
    /**
     * @param conn 数据库连接
     */
    protectedDataAccess(Connection conn) {
        this.conn = conn;
    }
     
    /**
     * 插入数据
     * @param sql
     * @param generatedKeysConverter 主键映射
     * @param params
     * @return 主键
     * @throws DataAccessException
     */
    protected<T> T insert(String sql, ResultConverter<T> generatedKeysConverter, Object... params)throwsDataAccessException {
        try{
            PreparedStatement pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            setParameters(pstmt, params);
            executeUpdate(pstmt);
            ResultSet rs = pstmt.getGeneratedKeys();
            nextResult(rs);
            returnconvertResult(rs, generatedKeysConverter);
        }catch(SQLException e) {
            e.printStackTrace();
            LOG.error(e.getMessage(), e);
            thrownewDataAccessException(e);
        }
    }
     
    /**
     * 更新数据
     * @param sql
     * @param params
     * @return 影响行数
     * @throws DataAccessException
     */
    protectedintupdate(String sql, Object... params)throwsDataAccessException {
        returnexecuteUpdate(getPreparedStatement(sql, params));
    }
     
    /**
     * 查询单个结果
     * @param <T>
     * @param sql
     * @param converter
     * @param params
     * @return
     */
    protected<T> T queryForObject(String sql, ResultConverter<T> converter, Object... params) {
        ResultSet rs = executeQuery(sql, params);
        if(nextResult(rs)) {
            returnconvertResult(rs, converter);
        }else{
            returnnull;
        }
    }
     
    /**
     * 查询结果列表
     * @param <T>
     * @param sql
     * @param converter
     * @param params
     * @return
     */
    protected<T> List<T> queryForList(String sql, ResultConverter<T> converter, Object... params) {
        ResultSet rs = executeQuery(sql, params);
        List<T> list =newArrayList<T>();
        while(nextResult(rs)) {
            list.add(convertResult(rs, converter));
        }
        returnlist;
    }
     
    /**
     * @param sql SQL语句
     * @return 预编译声明
     */
    privatePreparedStatement getPreparedStatement(String sql, Object... params)throwsDataAccessException {
        PreparedStatement pstmt = getPreparedStatement(sql);
        setParameters(pstmt, params);
        returnpstmt;
    }
     
    /**
     * @param sql SQL语句
     * @return 预编译声明
     */
    privatePreparedStatement getPreparedStatement(String sql)throwsDataAccessException {
        try{
            returnconn.prepareStatement(sql);
        }catch(SQLException e) {
            e.printStackTrace();
            LOG.error(e.getMessage(), e);
            thrownewDataAccessException(e);
        }
    }
     
    /**
     * 为预编译声明传入参数
     * @param pstmt 预编译声明
     * @param params 参数
     * @throws DataAccessException
     */
    privatevoidsetParameters(PreparedStatement pstmt, Object... params)throwsDataAccessException {
        try{
            for(inti =0; i < params.length; i++) {
                pstmt.setObject(i +1, params[i]);
            }
        }catch(SQLException e) {
            e.printStackTrace();
            LOG.error(e.getMessage(), e);
            thrownewDataAccessException(e);
        }
    }
     
    /**
     * 执行更新操作
     * @param pstmt
     * @return 影响行数
     * @throws DataAccessException
     */
    privateintexecuteUpdate(PreparedStatement pstmt)throwsDataAccessException {
        try{
            returnpstmt.executeUpdate();
        }catch(SQLException e) {
            e.printStackTrace();
            LOG.error(e.getMessage(), e);
            thrownewDataAccessException(e);
        }
    }
     
    /**
     * 执行查询操作
     * @param pstmt 预编译声明
     * @return 结果集
     * @throws DataAccessException
     */
    privateResultSet executeQuery(PreparedStatement pstmt)throwsDataAccessException {
        try{
            returnpstmt.executeQuery();
        }catch(SQLException e) {
            e.printStackTrace();
            LOG.error(e.getMessage(), e);
            thrownewDataAccessException(e);
        }
    }
     
    /**
     * 执行查询操作
     * @param sql SQL语句
     * @param params 参数
     * @return 结果集
     * @throws DataAccessException
     */
    privateResultSet executeQuery(String sql, Object... params)throwsDataAccessException {
        returnexecuteQuery(getPreparedStatement(sql, params));
    }
     
    /**
     * 移动到下一行记录
     * @param rs 结果集
     * @return 是否有下一行记录
     * @throws DataAccessException
     */
    privatebooleannextResult(ResultSet rs)throwsDataAccessException {
        try{
            returnrs.next();
        }catch(SQLException e) {
            e.printStackTrace();
            LOG.error(e.getMessage(), e);
            thrownewDataAccessException(e);
        }
    }
     
    /**
     * 映射
     * @param rs 结果集
     * @return 映射结果
     * @throws DataAccessException
     */
    private<T> T convertResult(ResultSet rs, ResultConverter<T> converter)throwsDataAccessException {
        try{
            returnconverter.convert(rs);
        }catch(SQLException e) {
            e.printStackTrace();
            LOG.error(e.getMessage(), e);
            thrownewDataAccessException(e);
        }
    }
 
}

[7].[代码] 数据库存取异常 跳至 [1] [2] [3] [4] [5] [6] [7] [8]

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
packageorg.dave.common.database.access;
 
/**
 * 数据库存取异常
 * @author David Day
 */
@SuppressWarnings("serial")
publicclassDataAccessExceptionextendsRuntimeException {
     
    publicDataAccessException(Throwable cause) {
        super(cause);
    }
 
}

[8].[文件] 源码和DEMO包 ~ 13KB    下载(1680) 跳至 [1] [2] [3] [4] [5] [6] [7] [8]

文件不存在或者代码语言不存在


联系
我们