1
2
3
4
5
6 package com.rc.celeritas.db;
7
8 import com.rc.celeritas.exception.CeleritasException;
9 import com.rc.celeritas.exception.LessColumnInLookupSQLException;
10 import com.rc.celeritas.generics.PropertyHelper;
11 import com.rc.celeritas.i18n.ResourceHelper;
12 import com.rc.celeritas.query.Column;
13 import com.rc.celeritas.query.QueryHelper;
14 import java.sql.Statement;
15 import java.sql.Connection;
16 import java.sql.ResultSet;
17 import java.sql.ResultSetMetaData;
18 import java.util.ArrayList;
19 import java.util.HashMap;
20 import java.util.ResourceBundle;
21 import java.util.TreeSet;
22 import javax.naming.InitialContext;
23 import javax.sql.DataSource;
24 import org.apache.commons.beanutils.RowSetDynaClass;
25 import org.apache.commons.lang.StringUtils;
26 import org.apache.log4j.Logger;
27
28
29
30
31
32 public class DBHelper {
33
34 private static TreeSet<String> ds;
35 private static boolean initialized = false;
36 private static Logger log = Logger.getLogger(DBHelper.class);
37 private static DataSource dsn;
38 private static String dateFormat = "MM/dd/yyyy";
39
40
41
42
43
44 public static String getDateFormat() {
45 return dateFormat;
46 }
47
48
49
50
51
52
53
54
55 public static HashMap getRowMap(String sql) throws CeleritasException {
56 HashMap<String, String> row = null;
57 Connection conn = null;
58 try {
59 conn = getConnection();
60 Statement stmt = conn.createStatement();
61 ResultSet rs = stmt.executeQuery(sql);
62 if(rs != null && rs.next()){
63 row = new HashMap<String, String>();
64 ResultSetMetaData rsmd = rs.getMetaData();
65 int columnCount = rsmd.getColumnCount();
66 for(int i = 1 ; i <= columnCount; i++){
67 String columnName = rsmd.getColumnName(i);
68 row.put(columnName, rs.getString(columnName));
69 }
70 }
71 } catch (Exception e) {
72 log.error("Error in getting Row Map for sql : " + sql + "\n" + e.toString());
73 throw new CeleritasException(e);
74 } finally {
75 closeConnection(conn);
76 }
77 return row;
78 }
79
80
81
82
83
84 private static void setDateFormat(String dateFormat) {
85 if(StringUtils.isNotEmpty(dateFormat)){
86 DBHelper.dateFormat = dateFormat;
87 }
88 }
89
90
91
92
93
94
95 public static synchronized HashMap<String, String> generateLU(String sql) {
96 HashMap<String, String> lookups = null;
97 Connection conn = null;
98 try {
99 conn = getConnection();
100 Statement stmt = conn.createStatement();
101 ResultSet rs = stmt.executeQuery(sql);
102 if(rs != null){
103 lookups = new HashMap<String, String>();
104 while(rs.next()){
105 String key = rs.getString(1);
106 String val = null;
107 if(rs.getMetaData().getColumnCount() == 1){
108 val = rs.getString(1);
109 } else {
110 val = rs.getString(2);
111 }
112 lookups.put(key, val);
113 }
114 }
115 } catch (CeleritasException ce) {
116 log.error(ce.toString());
117 } catch (Exception e){
118 log.error("Error occurred in fetching LU data for sql : " + sql + "\n" + e.toString());
119 }finally {
120 closeConnection(conn);
121 }
122 return lookups;
123 }
124
125
126
127
128
129
130 public static String generatePValFromSequence(String seq) {
131
132
133 return null;
134 }
135
136
137
138
139
140
141
142 public static ArrayList getQueryResults(String sql) throws CeleritasException {
143 Connection conn = null;
144 ArrayList rows = null;
145 try {
146 conn = getConnection();
147 Statement stmt = conn.createStatement();
148 ResultSet rs = stmt.executeQuery(sql);
149 RowSetDynaClass rsdc = new RowSetDynaClass(rs);
150 rows = new ArrayList(rsdc.getRows());
151 } catch (Exception e) {
152 log.error("Error in generating Query Results for SQL : " + sql + "\n" + e.toString());
153 } finally {
154 closeConnection(conn);
155 }
156 return rows;
157 }
158
159
160
161
162
163
164
165 public static boolean init() throws CeleritasException{
166 try {
167 boolean rhStatus = ResourceHelper.init();
168 log.info("ResourceHelper Initialized " + rhStatus);
169 ResourceBundle dbprop = ResourceBundle.getBundle("celeritas-dbprop");
170 if(ds == null){
171 ds = new TreeSet<String>();
172 }
173 String dsnames[] = dbprop.getString("ds_jndi").split(",");
174 if(dsnames == null || dsnames.length == 0){
175 throw new Exception("No DataSource Defined in the dbprop file. \n Please define atleast one data source using ds_jndi");
176 }
177
178 InitialContext inctx = new InitialContext();
179 log.info("Acquiring dsn for " + DBConstants.JNDI_PREFIX + dsnames[0]);
180 dsn = (DataSource)inctx.lookup(DBConstants.JNDI_PREFIX+dsnames[0]);
181
182 addDs(dsnames);
183
184 setDateFormat(dbprop.getString("date-format"));
185
186 } catch (Exception e) {
187 log.error("Error in Initializing DB Properties : " + e.toString());
188 throw new CeleritasException(e);
189 }
190 return initialized = true;
191 }
192
193
194
195
196
197
198
199
200 public static boolean addDs(String datasource) throws CeleritasException, ClassCastException{
201 if(ds!=null){
202 return ds.add(datasource);
203 } else {
204 return false;
205 }
206 }
207
208
209
210
211
212
213
214
215 public static boolean addDs(String []datasources) throws CeleritasException, ClassCastException{
216 if(datasources != null){
217 for(int i = 0;i< datasources.length; i++){
218 addDs(datasources[i]);
219 }
220 return true;
221 }else{
222 return false;
223 }
224 }
225
226
227
228
229
230
231 public static Connection getConnection() throws CeleritasException{
232 Connection conn = null;
233 if(dsn != null){
234 try {
235 conn = dsn.getConnection();
236 } catch (Exception e) {
237 log.error("Error in Getting DB Connection : " + e.toString());
238 throw new CeleritasException(e);
239 }
240 }
241 return conn;
242 }
243
244
245
246
247
248
249
250 public static ArrayList<String> getColumnNames(String table) throws CeleritasException{
251 String sql = PropertyHelper.getDBValue("column.sql", "celeritas.database.type" ).replace("%1", table);
252 Connection conn = getConnection();
253 return GenericDAO.getSingleColumnList(conn, sql);
254 }
255
256
257
258
259
260
261
262 public static ArrayList<Column> getColumns(String table) throws CeleritasException {
263 ArrayList<Column> columns = null;
264 String sql = PropertyHelper.getDBValue("column.sql", "celeritas.database.type" ).replace("%1", table);
265 Connection conn = null;
266 try {
267 conn = getConnection();
268 Statement stmt = conn.createStatement();
269 ResultSet rs = stmt.executeQuery(sql);
270 if(rs != null){
271 columns = new ArrayList<Column>();
272 while(rs.next()){
273 Column column = new Column();
274 column.setColumnName(rs.getString("COLUMN_NAME"));
275 column.setColumnType(rs.getString("DATA_TYPE"));
276 columns.add(column);
277 }
278 }
279 } catch (Exception e) {
280 log.error("Error in fetching Column Data for Table : " + table + " with SQL : " + sql + "\n" + e.toString());
281 } finally {
282 closeConnection(conn);
283 }
284 return columns;
285 }
286
287
288
289
290
291
292
293 public static ArrayList<Column> getColumnsFromSQL(String sql) throws CeleritasException {
294 ArrayList<Column> columns = null;
295 Connection conn = null;
296 try {
297 conn = getConnection();
298 Statement stmt = conn.createStatement();
299 stmt.setMaxRows(1);
300 ResultSet rs = stmt.executeQuery(sql);
301 if(rs != null){
302 columns = new ArrayList<Column>();
303 for(int i=1;i<=rs.getMetaData().getColumnCount();i++){
304 Column column = new Column();
305 column.setColumnName(rs.getMetaData().getColumnLabel(i));
306 column.setColumnType(rs.getMetaData().getColumnTypeName(i));
307 columns.add(column);
308 }
309 }
310 } catch (Exception e) {
311 log.error("Error in fetching Column Data for Table : " + sql + "\n" + e.toString());
312 e.printStackTrace();
313 } finally {
314 closeConnection(conn);
315 }
316 return columns;
317 }
318
319
320
321
322
323 public static void closeConnection(Connection conn) {
324 try{
325 if(conn != null && !conn.isClosed()){
326 conn.close();
327 conn = null;
328 }
329 }catch(Exception e){
330
331 }
332 }
333
334
335
336
337
338
339
340
341
342
343 public static HashMap<String, String> insert(String insertSQL, String table, String pk, String pVal) throws CeleritasException{
344 int rows = execute(insertSQL);
345 log.info(rows + " Inserted Succesfully");
346 HashMap<String, String> row = null;
347 if(rows > 0){
348 row = fetchRow(table, pk, pVal);
349 }
350 return row;
351 }
352
353
354
355
356
357
358
359 public static int execute(String sql) throws CeleritasException {
360 Connection conn = null;
361 int rows = -1;
362 try {
363 conn = getConnection();
364 Statement stmt = conn.createStatement();
365 rows = stmt.executeUpdate(sql);
366 } catch (Exception e) {
367 log.error("Error in execute sql : " + sql + "\n" + e.toString());
368 throw new CeleritasException(e);
369 } finally {
370 closeConnection(conn);
371 }
372 return rows;
373 }
374
375
376
377
378
379
380
381
382
383 public static HashMap<String, String> fetchRow(String table, String pk, String pVal) throws CeleritasException{
384 String sql = QueryHelper.buildQuerySQLWithPKey(table, pk, pVal);
385 return fetchRow(sql);
386 }
387
388
389
390
391
392
393
394 public static HashMap<String, String> fetchRow(String sql) throws CeleritasException {
395 Connection conn = null;
396 HashMap<String, String> rowMap = null;
397 try {
398 conn = getConnection();
399 Statement stmt = conn.createStatement();
400 ResultSet rs = stmt.executeQuery(sql);
401 if(rs != null){
402 int coulumnCount = rs.getMetaData().getColumnCount();
403 rowMap = new HashMap<String, String>();
404 while(rs.next()){
405 for(int i = 1;i <= coulumnCount; i++){
406 String tempColumnName = rs.getMetaData().getColumnName(i);
407 rowMap.put(tempColumnName, rs.getString(tempColumnName));
408 }
409 }
410 }
411 } catch (Exception e) {
412 log.error("Error in Fetching Row SQL : " + sql);
413 throw new CeleritasException(e);
414 } finally {
415 closeConnection(conn);
416 }
417 return rowMap;
418 }
419
420
421
422
423
424
425
426
427
428
429 public static HashMap<String, String> update(String updateSQL, String table, String pk, String pVal) throws CeleritasException {
430 int rows = execute(updateSQL);
431 HashMap<String, String> row = null;
432 if(rows > 0){
433 row = fetchRow(table, pk, pVal);
434 }
435 return row;
436 }
437 }