配置文件dbconfig.xml如下：
1
<config>
2 <dbinfo>
3 <url>jdbc:oracle:thin:@133.1.72.44:1521:test</url>
4 <user>test</user>
5 <pwd>test</pwd>
6 <connNumber>10</connNumber>
7 </dbinfo>
8</config>
9
这样数据库连接类部分就完成了
下面写一个简单数据访问类，由Table、RowSet和Row三个类组成：
Table.java代码如下
1package com.bat.afp.DAOComm;
2
3import java.sql.Connection;
4import java.sql.ResultSet;
5import java.sql.ResultSetMetaData;
6import java.sql.SQLException;
7import java.sql.Statement;
8import org.apache.log4j.Logger;
9
10
/**//**
11
* @author liuyf
12
*/
13public class Table 
{
14
15 private static final Logger logger = Logger.getLogger(Table.class);
16
17 private String tableName;
18
19
public Table(String name) {
20 this.tableName = name;
21
}
22
23 /**//**
24 * 根据条件查出该Table中的某些列
25 *
26 * @param sql
27 * @param cols
28 * @return
29 * @throws SQLException
30 */
31 private RowSet executeSQL(String criteria, String[] columns) throws SQLException {
32 Connection conn = null;
33 Statement st = null;
34 ResultSet rs = null;
35 RowSet rows = new RowSet();
36 try {
37 conn = DBUtil.getInstance().getConnection();
38 st = conn.createStatement();
39 StringBuffer buffer = new StringBuffer();
40 for (int i = 0; i < columns.length - 1; ++i) {
41 buffer.append(columns[i]);
42 buffer.append(",");
43 }
44 buffer.append(columns[columns.length - 1]);
45 String column = buffer.toString();
46 rs = st.executeQuery("select " + column + " from " + tableName
47 + (criteria == null ? "" : (" where " + criteria)));
48 int cols = columns.length;
49 while (rs.next()) {
50 Row row = new Row();
51 for (int i = 0; i < cols; ++i) {
52 String name = columns[i];
53 String value = rs.getString(i + 1);
54 row.put(name, value);
55 }
56 rows.add(row);
57 }
58 }
59 finally {
60 try {
61 if (st != null)
62 st.close();
63 } catch (Exception e) {
64 }
65 try {
66 if (conn != null)
67 conn.close();
68 } catch (Exception e) {
69 }
70 }
71 return rows;
72 }
73
74 private RowSet executeSQL(String sql) throws SQLException {
75 Connection conn = null;
76 Statement st = null;
77 ResultSet rs = null;
78 RowSet rows = new RowSet();
79 try {
80 conn = DBUtil.getInstance().getConnection();
81 st = conn.createStatement();
82 rs = st.executeQuery(sql);
83 ResultSetMetaData rsmd = rs.getMetaData();
84 int cols = rsmd.getColumnCount();
85 while (rs.next()) {
86 Row row = new Row();
87 for (int i = 0; i < cols; ++i) {
88 String name = rsmd.getColumnName(i + 1);
89 String value = rs.getString(i + 1);
90 row.put(name, value);
91 }
92 rows.add(row);
93 }
94 }
95 finally {
96 try {
97 if (st != null)
98 st.close();
99 } catch (Exception e) {
100 }
101 try {
102 if (conn != null)
103 conn.close();
104 } catch (Exception e) {
105 }
106 }
107 return rows;
108 }
109
110 private RowSet execute(String criteria) throws SQLException {
111 Connection conn = null;
112 Statement st = null;
113 ResultSet rs = null;
114 RowSet rows = new RowSet();
115 try {
116 conn = DBUtil.getInstance().getConnection();
117 st = conn.createStatement();
118 rs = st.executeQuery("select * from " + tableName
119 + (criteria == null ? "" : (" where " + criteria)));
120 ResultSetMetaData rsmd = rs.getMetaData();
121 int cols = rsmd.getColumnCount();
122 while (rs.next()) {
123 Row row = new Row();
124 for (int i = 0; i < cols; ++i) {
125 String name = rsmd.getColumnName(i + 1);
126 String value = rs.getString(i + 1);
127 row.put(name, value);
128 }
129 rows.add(row);
130 }
131 }
132 finally {
133 try {
134 if (st != null)
135 st.close();
136 } catch (Exception e) {
137 }
138 try {
139 if (conn != null)
140 conn.close();
141 } catch (Exception e) {
142 }
143 }
144 return rows;
145 }
146
147 /**//**
148 * 根据条件和给出的列名查询某些列的值
149 *
150 * @param criteria
151 * @param columns
152 * @return
153 * @throws SQLException
154 */
155 public RowSet getRowsOfSomeColumn(String criteria, String[] columns) throws SQLException {
156 RowSet rs = executeSQL(criteria, columns);
157 return rs;
158 }
159
160 /**//**
161 * 根据SQL语句查询并返回行集
162 *
163 * @param sql
164 * @return
165 * @throws SQLException
166 */
167 public RowSet getRowsBySql(String sql) throws SQLException {
168 RowSet rs = executeSQL(sql);
169 return rs;
170 }
171
172 /**//**
173 * 根据查询条件返回查到的第一行数据
174 *
175 * @param criteria
176 * @return
177 * @throws SQLException
178 */
179 public Row getRow(String criteria) throws SQLException {
180 RowSet rs = execute(criteria);
181 return rs.get(0);
182 }
183
184 /**//**
185 * 根据查询条件返回行集 查询条件为sql语句中where后的条件 为null则无条件
186 *
187 * @param criteria
188 * @return
189 * @throws SQLException
190 */
191 public RowSet getRows(String criteria) throws SQLException {
192 RowSet rs = execute(criteria);
193 return rs;
194 }
195
196 public RowSet getRows(String columnName, String columnvalue) throws SQLException {
197 if(columnName == null || columnName.equals("")){
198 return null;
199 }
200 String SQL = "select * from " + tableName + " where " + columnName + " = " + columnvalue;
201 RowSet rs = executeSQL(SQL);
202 return rs;
203
204 }
205
206 /**//**
207 * 查询该Table的所有数据
208 *
209 * @return
210 * @throws SQLException
211 */
212 public RowSet getRows() throws SQLException {
213 return getRows(null);
214 }
215
216 /**//**
217 * 插入行
218 *
219 * @param row
220 * @throws SQLException
221 */
222 public void putRow(Row row) throws SQLException {
223 putRow(row, null);
224 }
225
226 /**//**
227 * 插入行或更新行 如conditions为null，则为插入 如conditions不为null，则为更新
228 *
229 * @param row
230 * @param conditions
231 * @throws SQLException
232 */
233 public void putRow(Row row, String conditions) throws SQLException {
234 Connection conn = null;
235 Statement st = null;
236 try {
237 String ss = "";
238 if (conditions == null) {
239 ss = "INSERT INTO " + tableName + " VALUES (";
240 for (int i = 0; i < row.length(); ++i) {
241 String v = row.get(i);
242 ss += "'" + v + "'";
243 if (i != row.length() - 1)
244 ss += ", ";
245 }
246 ss += ")";
247 } else {
248 ss = "UPDATE " + tableName + " SET ";
249 for (int i = 0; i < row.length(); ++i) {
250 String k = row.getKey(i);
251 String v = row.get(i);
252 ss += k + "='" + v + "'";
253 if (i != row.length() - 1)
254 ss += ", ";
255 }
256 ss += " WHERE ";
257 ss += conditions;
258 }
259 logger.debug("Sql: " + ss);
260 conn = DBUtil.getInstance().getConnection();
261 st = conn.createStatement();
262 st.executeUpdate(ss);
263 }
264 finally {
265 try {
266 if (st != null)
267 st.close();
268 } catch (Exception e) {
269 }
270 try {
271 if (conn != null)
272 conn.close();
273 } catch (Exception e) {
274 }
275 }
276 }
277
278 public void delRow(Row row) throws SQLException {
279 Connection conn = null;
280 Statement st = null;
281 try {
282 String ss = "";
283 ss = "delete from " + tableName + " where ";
284 for (int i = 0; i < row.length(); ++i) {
285 String k = row.getKey(i);
286 String v = row.get(i);
287 ss += k + "='" + v + "'";
288 if (i != row.length() - 1)
289 ss += " and ";
290 }
291 conn = DBUtil.getInstance().getConnection();
292 st = conn.createStatement();
293 st.executeUpdate(ss);
294 }
295 finally {
296 try {
297 if (st != null)
298 st.close();
299 } catch (Exception e) {
300 }
301 try {
302 if (conn != null)
303 conn.close();
304 } catch (Exception e) {
305 }
306 }
307 }
308
309 public void delRow(String conditions) throws SQLException {
310 Connection conn = null;
311 Statement st = null;
312 try {
313 String ss = "";
314 ss = "delete from " + tableName + " where ";
315 ss += conditions;
316 conn = DBUtil.getInstance().getConnection();
317 st = conn.createStatement();
318 st.executeUpdate(ss);
319 } catch (SQLException se) {
320 se.printStackTrace();
321 }
322 finally {
323 try {
324 if (st != null)
325 st.close();
326 } catch (Exception e) {
327 }
328 try {
329 if (conn != null)
330 conn.close();
331 } catch (Exception e) {
332 }
333 }
334 }