Source code: org/acs/damsel/srvr/db/LowAssetDB.java
1 package org.acs.damsel.srvr.db;
2
3 import java.sql.*;
4 import java.util.*;
5
6 import org.acs.damsel.srvr.*;
7 import org.apache.log4j.*;
8
9 /**
10 * <p>Title: Low-level database access via JDBC</p>
11 * <p>Description:
12 * This Class contains the low level methods of the AssetDB class.
13 * Methods include the basic constructor, executeQuery and executeStatement
14 * </p>
15 * @version 1.0
16 */
17
18 public class LowAssetDB {
19 private Connection con = null;
20 private static AssetDB instance = null;
21 private static Logger log = Logger.getLogger(AssetDB.class);
22
23 public LowAssetDB() throws SQLException {
24 Config config = Config.instance();
25 BasicConfigurator.resetConfiguration();
26 PropertyConfigurator.configure(config.getLogPropertiesFileName());
27 try {
28 try {
29 Class.forName(config.getAssetDBDriverClass()).newInstance();
30 }
31 catch (Exception e) {
32 }
33 String connectionArg = config.getAssetDBConnection() + "?user=" +
34 config.getAssetDBUserName() + "&password=" + config.getAssetDBPassword();
35 con = DriverManager.getConnection(connectionArg);
36 }
37 catch (SQLException ex) {
38 log.warn(
39 "Unexpected sql exception while establishing connection.");
40 ex.printStackTrace();
41 throw new SQLException(ex.getMessage());
42 }
43 }
44
45 /**
46 * Executes a SQL query (i.e., a SELECT statement) and returns a Vector of
47 * Vectors containing the result table. A SQLException is thrown if there is
48 * a problem with the database or the SQL statement itself.
49 * @param query , a complete SQL statement to be executed
50 * @return a Vector of Vectors. The 'outer' vector contains the rows
51 * of the result set. The 'inner' vectors contain the columns of data, which
52 * are Strings.
53 * @throws SQLException
54 */
55 public Table executeQuery(String query) throws SQLException {
56 // if the connection is closed or messed up, reopen it
57 reopenConnection();
58
59 Vector rows = new Vector();
60 Vector col = null;
61 Vector metaData = new Vector();
62 Table resultTable = new Table();
63
64 log.info(query);
65
66 Statement s = con.createStatement();
67 ResultSet rs = s.executeQuery(query);
68 ResultSetMetaData rsmd = rs.getMetaData();
69 int columnCount = rsmd.getColumnCount();
70
71 // generate metaData
72 for (int i = 1; i <= columnCount; i++) {
73 metaData.add(rsmd.getColumnName(i));
74 }
75
76 // generate results
77 while (rs.next()) {
78 col = new Vector();
79 for (int i = 1; i <= columnCount; i++) {
80 col.add(rs.getString(i));
81 }
82 // decode the column of data so that quotes are displayed correctly
83 DBUtils.decode(col);
84 rows.add(col);
85 }
86 s.close();
87
88 resultTable.setMetaData(metaData);
89 resultTable.setResults(rows);
90 return resultTable;
91 }
92
93
94 /**
95 * A generic statement execution method. Not for use with queries as it does
96 * not return a resultset.
97 * @param statement , the SQL statement to be executed
98 * @return int, the number of rows affected by the statement
99 * @throws SQLException
100 */
101 public int executeStatement(String statement) throws SQLException {
102 // if the connection is closed or messed up, reopen it
103 reopenConnection();
104
105 Statement s = con.createStatement();
106 log.info(statement);
107 int numRowsAffected = s.executeUpdate(statement);
108 return numRowsAffected;
109 }
110
111 /**
112 * Creates a null SQL statement and attempts to execute it. If this causes
113 * an exception, then the database connection is closed and reopened.
114 * @todo check to see if this adds substantial overhead
115 * @todo check to see if there is a more efficient or canonical way to do this
116 * @throws SQLException
117 */
118 private void reopenConnection() throws SQLException {
119 Config config = Config.instance();
120 try {
121 Statement s = con.createStatement();
122 s.executeUpdate("COMMIT");
123 }
124 catch (Exception ex) {
125 con.close();
126 con = DriverManager.getConnection(config.getAssetDBConnection(),
127 config.getAssetDBUserName(), "");
128 log.warn(ex.getMessage());
129 }
130 }
131
132 }