001: package org.claros.commons.db;
002:
003: import java.sql.SQLException;
004: import java.util.HashMap;
005: import java.util.List;
006:
007: import org.apache.commons.configuration.Configuration;
008: import org.apache.commons.dbutils.QueryRunner;
009: import org.apache.commons.dbutils.ResultSetHandler;
010: import org.apache.commons.dbutils.handlers.BeanHandler;
011: import org.apache.commons.logging.Log;
012: import org.apache.commons.logging.LogFactory;
013: import org.claros.commons.configuration.PropertyFile;
014: import org.claros.commons.db.handler.ItemResultSetHandler;
015: import org.claros.commons.db.handler.ListResultSetHandler;
016:
017: /**
018: * @author Umut Gokbayrak
019: *
020: */
021: public class DBManager {
022: private static Log log = LogFactory.getLog(DBManager.class);
023: private static Configuration sqls = null;
024:
025: public static List queryList(String dbId, String sqlKey,
026: Object params[]) throws SQLException {
027: String sql = getSqlFromKey(sqlKey);
028: log.debug("Running List query sql: " + sql + " with params: "
029: + ((params == null) ? null : params.toString())
030: + " on dbId: " + dbId);
031: QueryRunner run = new QueryRunner(DbConfigList
032: .getDataSourceById(dbId));
033: List result = null;
034: try {
035: result = (List) run.query(sql, params,
036: new ListResultSetHandler());
037: } catch (SQLException e) {
038: log.error("Error while querying... Key:" + sqlKey
039: + " params: " + params, e);
040: throw e;
041: }
042: log.debug("Sql List query returned "
043: + ((result == null) ? null : result.toString()));
044: return result;
045: }
046:
047: public static HashMap queryItem(String dbId, String sqlKey,
048: Object params[]) throws SQLException {
049: String sql = getSqlFromKey(sqlKey);
050: log.debug("Running Item query sql: " + sql + " with params: "
051: + ((params == null) ? null : params.toString())
052: + " on dbId: " + dbId);
053: QueryRunner run = new QueryRunner(DbConfigList
054: .getDataSourceById(dbId));
055: HashMap result = null;
056: try {
057: result = (HashMap) run.query(sql, params,
058: new ItemResultSetHandler());
059: } catch (SQLException e) {
060: log.error("Error while querying... Key:" + sqlKey
061: + " params: " + params, e);
062: throw e;
063: }
064: log.debug("Sql Item query returned "
065: + ((result == null) ? null : result.toString()));
066: return result;
067: }
068:
069: public static Object queryBean(String dbId, String sqlKey,
070: Object params[], Class clsBean) throws SQLException {
071: String sql = getSqlFromKey(sqlKey);
072: log.debug("Running Bean query sql: " + sql + " with params: "
073: + ((params == null) ? null : params.toString())
074: + " on dbId: " + dbId + " on bean: " + clsBean);
075: QueryRunner run = new QueryRunner(DbConfigList
076: .getDataSourceById(dbId));
077: ResultSetHandler h = new BeanHandler(clsBean);
078: Object result = run.query(getSqlFromKey(sqlKey), params, h);
079: log.debug("Sql Bean query returned "
080: + ((result == null) ? null : result.toString()));
081: return result;
082: }
083:
084: public static void update(String dbId, String sqlKey,
085: Object params[]) throws SQLException {
086: String sql = getSqlFromKey(sqlKey);
087: log.debug("Running sql update, sql: " + sql + " with params: "
088: + ((params == null) ? null : params.toString())
089: + " on dbId: " + dbId);
090: QueryRunner run = new QueryRunner(DbConfigList
091: .getDataSourceById(dbId));
092: int result = run.update(sql, params);
093: log.debug("Sql Update returned Result: " + result);
094: }
095:
096: public static void delete(String dbId, String sqlKey,
097: Object params[]) throws SQLException {
098: String sql = getSqlFromKey(sqlKey);
099: log.debug("Running sql delete, sql: " + sql + " with params: "
100: + ((params == null) ? null : params.toString())
101: + " on dbId: " + dbId);
102: QueryRunner run = new QueryRunner(DbConfigList
103: .getDataSourceById(dbId));
104: int result = run.update(sql, params);
105: log.debug("Sql Delete returned Result: " + result);
106: }
107:
108: public static void insert(String dbId, String sqlKey,
109: Object params[]) throws SQLException {
110: String sql = getSqlFromKey(sqlKey);
111: log.debug("Running sql insert, sql: " + sql + " with params: "
112: + ((params == null) ? null : params.toString())
113: + " on dbId: " + dbId);
114: QueryRunner run = new QueryRunner(DbConfigList
115: .getDataSourceById(dbId));
116: int result = run.update(sql, params);
117: log.debug("Sql Insert returned Result: " + result);
118: }
119:
120: private static String getSqlFromKey(String sqlKey) {
121: return sqls.getString(sqlKey);
122: }
123:
124: static {
125: try {
126: sqls = PropertyFile
127: .getConfiguration("/resources/sql.properties");
128: } catch (Exception e) {
129: log
130: .fatal(
131: "Can not read sql.properties file. Look what is happening. It will crash probably!!!",
132: e);
133: }
134: }
135: }
|