DBUtil数据库工具封装

Java代码


1. package org.idcn.util
2.
3. import java.io.InputStream;
4. import java.sql.Connection;
5. import java.sql.Date;
6. import java.sql.PreparedStatement;
7. import java.sql.ResultSet;
8. import java.sql.SQLException;
9. import java.sql.Time;
10. import java.sql.Timestamp;
11. import javax.sql.DataSource;
12.
13. /**
14. * This class encapsulation the Connection and PreparedStatement
15. * deal with database
16. *
17. * It be designed as a singleton class
18. *
19. * @author zdxue
20. *
21. */
22. public class DBUtil {
23. private Connection conn = null; //Connection object
24. private PreparedStatement prepStmt = null; //PreparedStatement object
25.
26. /**
27. * create DBUtil with Connection and sql
28. *
29. * @param conn Connection
30. * @param sql sql statement
31. * @throws SQLException if occur database access wrong
32. */
33. private DBUtil(Connection conn, String sql) throws SQLException {
34. this.conn = conn;
35. prepStmt = conn.prepareStatement(sql,
36. ResultSet.TYPE_SCROLL_INSENSITIVE,
37. ResultSet.CONCUR_READ_ONLY);
38. }
39.
40. /**
41. * create DBUtil with dataSource and sql
42. *
43. * @param ds DataSource
44. * @param sql sql statement
45. * @throws SQLException if occur database access wrong
46. */
47. private DBUtil(DataSource ds, String sql) throws SQLException {
48. conn = ds.getConnection();
49. prepStmt = conn.prepareStatement(sql,
50. ResultSet.TYPE_SCROLL_INSENSITIVE,
51. ResultSet.CONCUR_READ_ONLY);
52. }
53.
54. /**
55. * the static method to get DBUtil instance
56. *
57. * @param connection java.sql.Connection
58. * @param sql sql statement
59. * @return DBUtil DBUtil instance
60. * @throws SQLException if occur database access wrong
61. */
62. public static DBUtil getInstance(Connection connection, String sql) throws SQLException {
63. return new DBUtil(connection, sql);
64. }
65.
66. /**
67. * static method to get DBUtil instance
68. *
69. * @param dataSource dataSource
70. * @param sql sql statement
71. * @return DBUtil DBUtil instance
72. * @throws SQLException if occur database access wrong
73. */
74. public static DBUtil getInstance(DataSource dataSource, String sql) throws SQLException {
75. return new DBUtil(dataSource, sql);
76. }
77.
78. /**
79. * get Connection
80. *
81. * @return connection java.sql.Conncetion instance
82. */
83. public Connection getConnection() {
84. return conn;
85. }
86.
87. /**
88. * get preparedStatement
89. *
90. * @return preparedStatement java.sql.preparedStatement instance
91. */
92. public PreparedStatement getPreparedStatement() {
93. return prepStmt;
94. }
95.
96. /**
97. * execute Query from database
98. *
99. * @return ResultSet
100. * @throws SQLException if occur database access wrong
101. */
102. public ResultSet executeQuery() throws SQLException {
103. return prepStmt.executeQuery();
104. }
105.
106. /**
107. * execute update to the database
108. *
109. * @throws SQLException if occur database access wrong
110. */
111. public int executeUpdate() throws SQLException {
112. if(prepStmt == null)
113. return 0;
114. return prepStmt.executeUpdate();
115. }
116.
117. /**
118. * close the connection and preparedStatment
119. */
120. public void close() {
121. try {
122. if (prepStmt != null) {
123. prepStmt.close();
124. prepStmt = null;
125. }
126. if(conn != null) {
127. conn.close();
128. conn = null;
129. }
130. } catch (Exception e) {
131. }
132. }
133.
134. /**
135. * set the String value for the preparedStatement
136. *
137. * @param index the first parameter is 1, seconed is 2, and so on.
138. * @param value String parameter value
139. * @throws SQLException if occur database access wrong
140. */
141. public void setString(int index,String value) throws SQLException {
142. prepStmt.setString(index,value);
143. }
144.
145. /**
146. * set the int value for the preparedStatement
147. *
148. * @param index the first parameter is 1, seconed is 2, and so on.
149. * @param value int parameter value
150. * @throws SQLException if occur database access wrong
151. */
152. public void setInt(int index,int value) throws SQLException {
153. prepStmt.setInt(index,value);
154. }
155.
156. /**
157. * set the Double value for the preparedStatement
158. *
159. * @param index the first parameter is 1, seconed is 2, and so on.
160. * @param value Double parameter value
161. * @throws SQLException if occur database access wrong
162. */
163. public void setDouble(int index,Double value) throws SQLException {
164. prepStmt.setDouble(index, value);
165. }
166.
167. /**
168. * set the boolean value for the preparedStatement
169. * @param index the first parameter is 1, seconed is 2, and so on.
170. * @param value boolean parameter value
171. * @throws SQLException if occur database access wrong
172. */
173. public void setBoolean(int index,boolean value) throws SQLException {
174. prepStmt.setBoolean(index,value);
175. }
176.
177. /**
178. * set the Date value for the preparedStatement
179. *
180. * @param index the first parameter is 1, seconed is 2, and so on.
181. * @param value Date parameter value
182. * @throws SQLException if occur database access wrong
183. */
184. public void setDate(int index,Date value) throws SQLException {
185. prepStmt.setDate(index,value);
186. }
187.
188. /**
189. * set the Time value for the preparedStatement
190. *
191. * @param index the first parameter is 1, seconed is 2, and so on.
192. * @param value Time parameter value
193. * @throws SQLException if occur database access wrong
194. */
195. public void setTime(int index,Time value) throws SQLException {
196. prepStmt.setTime(index,value);
197. }
198.
199. /**
200. * set the TimeStampe value for the preparedStatement
201. *
202. * @param index the first parameter is 1, seconed is 2, and so on.
203. * @param value java.sql.Timestamp parameter value
204. * @throws SQLException if occur database access wrong
205. */
206. public void setTimestamp(int index,Timestamp value) throws SQLException {
207. prepStmt.setTimestamp(index,value);
208. }
209.
210. /**
211. * set the long value for the preparedStatement
212. *
213. * @param index the first parameter is 1, seconed is 2, and so on.
214. * @param value long parameter value
215. * @throws SQLException if occur database access wrong
216. */
217. public void setLong(int index,long value) throws SQLException {
218. prepStmt.setLong(index,value);
219. }
220.
221. /**
222. * set the float value for the preparedStatement
223. *
224. * @param index the first parameter is 1, seconed is 2, and so on.
225. * @param value float parameter value
226. * @throws SQLException if occur database access wrong
227. */
228. public void setFloat(int index,float value) throws SQLException {
229. prepStmt.setFloat(index,value);
230. }
231.
232. /**
233. * set the Object value for the preparedStatement
234. *
235. * @param index the first parameter is 1, seconed is 2, and so on.
236. * @param obj Object parameter value
237. * @throws SQLException if occur database access wrong
238. */
239. public void setObject(int index, Object obj) throws SQLException {
240. prepStmt.setObject(index, obj);
241. }
242.
243. /**
244. * set binaryStream
245. *
246. * @param index the first parameter is 1, seconed is 2, and so on.
247. * @param in binayStream
248. * @param length the byte length of the stream
249. * @throws SQLException if occur database access wrong
250. */
251. public void setBinaryStream(int index,InputStream in,int length) throws SQLException {
252. prepStmt.setBinaryStream(index,in,length);
253. }
254.
255. /**
256. * transaction commit
257. */
258. public void commit() {
259. try {
260. conn.commit();
261. } catch(Exception e) {
262. e.printStackTrace();
263. }
264. }
265.
266. /**
267. * transaction rollback
268. */
269. public void rollback() {
270. try {
271. conn.rollback();
272. }
273. catch(Exception e) {
274. e.printStackTrace();
275. }
276. }
277. }


package org.idcn.util


import java.io.InputStream;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Time;
import java.sql.Timestamp;
import javax.sql.DataSource;


/**
* This class encapsulation the Connection and PreparedStatement
* deal with database
*
* It be designed as a singleton class
*
* @author zdxue
*
*/
public class DBUtil {
private Connection conn = null; //Connection object
private PreparedStatement prepStmt = null; //PreparedStatement object


/**
* create DBUtil with Connection and sql
*
* @param conn Connection
* @param sql sql statement
* @throws SQLException if occur database access wrong
*/
private DBUtil(Connection conn, String sql) throws SQLException {
this.conn = conn;
prepStmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
}


/**
* create DBUtil with dataSource and sql
*
* @param ds DataSource
* @param sql sql statement
* @throws SQLException if occur database access wrong
*/
private DBUtil(DataSource ds, String sql) throws SQLException {
conn = ds.getConnection();
prepStmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
}


/**
* the static method to get DBUtil instance
*
* @param connection java.sql.Connection
* @param sql sql statement
* @return DBUtil DBUtil instance
* @throws SQLException if occur database access wrong
*/
public static DBUtil getInstance(Connection connection, String sql) throws SQLException {
return new DBUtil(connection, sql);
}


/**
* static method to get DBUtil instance
*
* @param dataSource dataSource
* @param sql sql statement
* @return DBUtil DBUtil instance
* @throws SQLException if occur database access wrong
*/
public static DBUtil getInstance(DataSource dataSource, String sql) throws SQLException {
return new DBUtil(dataSource, sql);
}


/**
* get Connection
*
* @return connection java.sql.Conncetion instance
*/
public Connection getConnection() {
return conn;
}


/**
* get preparedStatement
*
* @return preparedStatement java.sql.preparedStatement instance
*/
public PreparedStatement getPreparedStatement() {
return prepStmt;
}


/**
* execute Query from database
*
* @return ResultSet
* @throws SQLException if occur database access wrong
*/
public ResultSet executeQuery() throws SQLException {
return prepStmt.executeQuery();
}


/**
* execute update to the database
*
* @throws SQLException if occur database access wrong
*/
public int executeUpdate() throws SQLException {
if(prepStmt == null)
return 0;
return prepStmt.executeUpdate();
}


/**
* close the connection and preparedStatment
*/
public void close() {
try {
if (prepStmt != null) {
prepStmt.close();
prepStmt = null;
}
if(conn != null) {
conn.close();
conn = null;
}
} catch (Exception e) {
}
}


/**
* set the String value for the preparedStatement
*
* @param index the first parameter is 1, seconed is 2, and so on.
* @param value String parameter value
* @throws SQLException if occur database access wrong
*/
public void setString(int index,String value) throws SQLException {
prepStmt.setString(index,value);
}


/**
* set the int value for the preparedStatement
*
* @param index the first parameter is 1, seconed is 2, and so on.
* @param value int parameter value
* @throws SQLException if occur database access wrong
*/
public void setInt(int index,int value) throws SQLException {
prepStmt.setInt(index,value);
}


/**
* set the Double value for the preparedStatement
*
* @param index the first parameter is 1, seconed is 2, and so on.
* @param value Double parameter value
* @throws SQLException if occur database access wrong
*/
public void setDouble(int index,Double value) throws SQLException {
prepStmt.setDouble(index, value);
}


/**
* set the boolean value for the preparedStatement
* @param index the first parameter is 1, seconed is 2, and so on.
* @param value boolean parameter value
* @throws SQLException if occur database access wrong
*/
public void setBoolean(int index,boolean value) throws SQLException {
prepStmt.setBoolean(index,value);
}


/**
* set the Date value for the preparedStatement
*
* @param index the first parameter is 1, seconed is 2, and so on.
* @param value Date parameter value
* @throws SQLException if occur database access wrong
*/
public void setDate(int index,Date value) throws SQLException {
prepStmt.setDate(index,value);
}


/**
* set the Time value for the preparedStatement
*
* @param index the first parameter is 1, seconed is 2, and so on.
* @param value Time parameter value
* @throws SQLException if occur database access wrong
*/
public void setTime(int index,Time value) throws SQLException {
prepStmt.setTime(index,value);
}


/**
* set the TimeStampe value for the preparedStatement
*
* @param index the first parameter is 1, seconed is 2, and so on.
* @param value java.sql.Timestamp parameter value
* @throws SQLException if occur database access wrong
*/
public void setTimestamp(int index,Timestamp value) throws SQLException {
prepStmt.setTimestamp(index,value);
}


/**
* set the long value for the preparedStatement
*
* @param index the first parameter is 1, seconed is 2, and so on.
* @param value long parameter value
* @throws SQLException if occur database access wrong
*/
public void setLong(int index,long value) throws SQLException {
prepStmt.setLong(index,value);
}


/**
* set the float value for the preparedStatement
*
* @param index the first parameter is 1, seconed is 2, and so on.
* @param value float parameter value
* @throws SQLException if occur database access wrong
*/
public void setFloat(int index,float value) throws SQLException {
prepStmt.setFloat(index,value);
}


/**
* set the Object value for the preparedStatement
*
* @param index the first parameter is 1, seconed is 2, and so on.
* @param obj Object parameter value
* @throws SQLException if occur database access wrong
*/
public void setObject(int index, Object obj) throws SQLException {
prepStmt.setObject(index, obj);
}


/**
* set binaryStream
*
* @param index the first parameter is 1, seconed is 2, and so on.
* @param in binayStream
* @param length the byte length of the stream
* @throws SQLException if occur database access wrong
*/
public void setBinaryStream(int index,InputStream in,int length) throws SQLException {
prepStmt.setBinaryStream(index,in,length);
}


/**
* transaction commit
*/
public void commit() {
try {
conn.commit();
} catch(Exception e) {
e.printStackTrace();
}
}


/**
* transaction rollback
*/
public void rollback() {
try {
conn.rollback();
}
catch(Exception e) {
e.printStackTrace();
}
}
}


声明：JavaEye文章版权属于作者，受法律保护。没有作者书面许可不得转载。
推荐链接


* 在繁琐中挣扎还是简化自主管理？
* IBM Rational软件开发高峰论坛9月揭幕
* 下载免费的 IBM DB2 Express-C 数据库


返回顶楼
最后修改：2008-11-27


* hanjs
* 等级: 初级会员
* 用户头像
* 文章: 111
* 积分: 0
* 来自: 大连
*



发表时间：2008-12-26
引用 收藏
1、传入的conn应该先设置autocommit＝false
2、应该使用threadlocal，可以处理多个方法调用的时候，在一个连接内控制事务