001: /*
002: * BEGIN_HEADER - DO NOT EDIT
003: *
004: * The contents of this file are subject to the terms
005: * of the Common Development and Distribution License
006: * (the "License"). You may not use this file except
007: * in compliance with the License.
008: *
009: * You can obtain a copy of the license at
010: * https://open-esb.dev.java.net/public/CDDLv1.0.html.
011: * See the License for the specific language governing
012: * permissions and limitations under the License.
013: *
014: * When distributing Covered Code, include this CDDL
015: * HEADER in each file and include the License file at
016: * https://open-esb.dev.java.net/public/CDDLv1.0.html.
017: * If applicable add the following below this CDDL HEADER,
018: * with the fields enclosed by brackets "[]" replaced with
019: * your own identifying information: Portions Copyright
020: * [year] [name of copyright owner]
021: */
022:
023: /*
024: * @(#)ChartDataPersistence.java
025: * Copyright 2004-2007 Sun Microsystems, Inc. All Rights Reserved.
026: *
027: * END_HEADER - DO NOT EDIT
028: */
029: package org.openesb.tools.charting.persist;
030:
031: import java.util.List;
032: import java.sql.Connection;
033: import java.sql.Statement;
034: import java.sql.ResultSet;
035: import java.sql.SQLException;
036: import java.sql.PreparedStatement;
037: import java.sql.Statement;
038: import java.sql.ResultSet;
039: import java.util.ArrayList;
040: import java.util.logging.Level;
041: import javax.naming.InitialContext;
042: import javax.naming.NamingException;
043: import javax.sql.DataSource;
044: import java.util.logging.Logger;
045: import org.openesb.tools.charting.exception.ChartingException;
046:
047: /**
048: *
049: * @author rdwivedi
050: */
051: public class ChartDataPersistence {
052:
053: /** Creates a new instance of ChartDataPersistence */
054: String mDefaultDBJndi = "jdbc/__default";
055: private static Logger mLogger = Logger
056: .getLogger(ChartDataPersistence.class.getName());
057: private static boolean INIT = false;
058:
059: public ChartDataPersistence() {
060: if (!INIT) {
061: try {
062: setChartSystemDB();
063: INIT = true;
064: } catch (Exception e) {
065: mLogger.severe("Error while Initializing "
066: + e.getMessage());
067: }
068: }
069: }
070:
071: List getAllAvailableChartIDS() throws ChartingException {
072: String queryString = "Select CHART_ID,CHART_D_NAME,DIS_NAME,D_A_ID,C_TYPE,GRP_ID from CHCHARTS,CHGROUPS where "
073: + "CHCHARTS.GRP_ID = CHGROUPS.ID";
074: ArrayList list = new ArrayList();
075: Connection con = null;
076: Statement stmt = null;
077: ResultSet rset = null;
078: try {
079: con = getConnectionJNDI();
080: stmt = con.createStatement();
081: rset = stmt.executeQuery(queryString);
082:
083: while (rset.next()) {
084: ChartBean cBean = new ChartBean();
085: cBean.setChartID(rset.getString(1));
086: cBean.setChartName(rset.getString(2));
087:
088: cBean.setChartParentName(rset.getString(3));
089: cBean.setChartDataBeanID(rset.getString(4));
090: cBean.setChartPropertiesForDataset(null, rset
091: .getString(5));
092: cBean.setParentID(rset.getString(6));
093: list.add(cBean);
094: }
095: rset.close();
096: stmt.close();
097:
098: } catch (SQLException e) {
099: throw new ChartingException(e);
100:
101: } finally {
102: if (con != null) {
103: try {
104: con.close();
105: } catch (SQLException e) {
106: // do nothing.
107: }
108: }
109:
110: }
111: return list;
112: }
113:
114: List getAllAvailableDataAccess() throws ChartingException {
115: String queryString = "Select CHDATAACC.ID,GRP_ID,D_NAME,DIS_NAME,D_TYPE,D_JNDI_N,DATA from CHDATAACC,CHGROUPS where "
116: + " CHDATAACC.GRP_ID = CHGROUPS.ID";
117: ArrayList list = new ArrayList();
118: Connection con = null;
119: Statement stmt = null;
120: ResultSet rset = null;
121: try {
122: con = getConnectionJNDI();
123: stmt = con.createStatement();
124: rset = stmt.executeQuery(queryString);
125:
126: while (rset.next()) {
127: DataBean cBean = new DataBean(rset.getString(3), rset
128: .getString(4));
129: cBean.setID(rset.getString(1));
130: cBean.setDataSetType(rset.getString(5));
131: cBean.setJNDIName(rset.getString(6));
132: cBean.setQuery(rset.getString(7));
133: list.add(cBean);
134: }
135: rset.close();
136: stmt.close();
137:
138: } catch (SQLException e) {
139: mLogger.severe("Error while executing " + queryString);
140: throw new ChartingException(e);
141:
142: } finally {
143: if (con != null) {
144: try {
145: con.close();
146: } catch (SQLException e) {
147: // do nothing.
148: }
149: }
150:
151: }
152: return list;
153: }
154:
155: public void upsertGroup(String id, String disname)
156: throws ChartingException {
157:
158: /**
159: "Create table CHGROUPS (ID varchar(100) NOT NULL , DIS_NAME varchar(20)," +
160: "CONSTRAINT GRPID_PK PRIMARY KEY(ID))";
161: **/
162: Connection con = null;
163: String queryString = "Select ID from CHGROUPS where ID=?";
164: try {
165: con = getConnectionJNDI();
166: PreparedStatement qStmt = con.prepareStatement(queryString);
167: PreparedStatement pstmt = con
168: .prepareStatement("UPDATE CHGROUPS SET DIS_NAME = ? WHERE ID = ?");
169: PreparedStatement istmt1 = con
170: .prepareStatement("Insert into CHGROUPS values(?,?)");
171: qStmt.setString(1, id);
172: qStmt.execute();
173: if (qStmt.execute() && qStmt.getResultSet().next()) {
174: pstmt.setString(1, disname);
175: pstmt.setString(2, id);
176: pstmt.executeUpdate();
177: } else {
178: istmt1.setString(1, id);
179: istmt1.setString(2, disname);
180:
181: //istmt1.executeUpdate();
182: istmt1.executeUpdate();
183:
184: }
185: con.commit();
186: pstmt.close();
187: istmt1.close();
188: } catch (SQLException e) {
189: try {
190: con.rollback();
191: } catch (SQLException e1) {
192: mLogger.log(Level.SEVERE, "Roll back failed.");
193: }
194:
195: e.printStackTrace();
196: mLogger.log(Level.SEVERE, "Unable to save chart data ", e);
197: throw new ChartingException("Unable to save chart data ", e);
198:
199: } finally {
200: if (con != null) {
201: try {
202: con.close();
203: } catch (SQLException e) {
204: e.printStackTrace();
205: mLogger.log(Level.SEVERE,
206: "Failed to close the connection.");
207: }
208: }
209:
210: }
211: }
212:
213: public void persistDataAccessBean(DataBean bean)
214: throws ChartingException {
215: Connection con = null;
216:
217: String queryString = "Select ID from CHDATAACC where ID=?";
218: mLogger.info("Saving DA object" + bean.getDisplayName());
219: // CHDATAACC ("ID varchar(200), GRP_ID integer," +
220: // "D_NAME varchar(200), D_TYPE varchar(40), D_JNDI_N varchar(40)" +
221: // "DATA varchar(20000)
222:
223: try {
224: con = getConnectionJNDI();
225: PreparedStatement qStmt = con.prepareStatement(queryString);
226: PreparedStatement pstmt = con
227: .prepareStatement("UPDATE CHDATAACC SET D_NAME = ?,"
228: + "D_TYPE = ?,D_JNDI_N=?,DATA=? WHERE ID = ?");
229: PreparedStatement istmt1 = con
230: .prepareStatement("Insert into CHGROUPS values(?,?)");
231: PreparedStatement istmt2 = con
232: .prepareStatement("Insert into CHDATAACC values(?,?,?,?,?,?)");
233:
234: qStmt.setString(1, bean.getID());
235: qStmt.execute();
236:
237: if (qStmt.execute() && qStmt.getResultSet().next()) {
238: mLogger.info("update Called. " + bean.getDisplayName());
239: pstmt.setString(1, bean.getDisplayName());
240: pstmt.setString(2, bean.getDataSetType());
241: pstmt.setString(3, bean.getJNDIName());
242: pstmt.setString(4, bean.getQuery());
243: pstmt.setString(5, bean.getID());
244: pstmt.executeUpdate();
245: } else {
246: mLogger.info("insert Called. " + bean.getDisplayName());
247: // this needs to be done some where else.
248: //istmt1.setString(1, bean.getGroupID());
249: //istmt1.setString(2, bean.getParentDisplayName());
250:
251: istmt2.setString(1, bean.getID());
252: istmt2.setString(2, bean.getGroupID());
253: istmt2.setString(3, bean.getDisplayName());
254: istmt2.setString(4, bean.getDataSetType());
255: istmt2.setString(5, bean.getJNDIName());
256: istmt2.setString(6, bean.getQuery());
257:
258: //istmt1.executeUpdate();
259: istmt2.executeUpdate();
260:
261: }
262: con.commit();
263: pstmt.close();
264: istmt1.close();
265: istmt2.close();
266: mLogger
267: .info("Save Data complete. "
268: + bean.getDisplayName());
269: } catch (SQLException e) {
270: try {
271: con.rollback();
272: } catch (SQLException e1) {
273: mLogger.log(Level.SEVERE, "Roll back failed.");
274: }
275:
276: e.printStackTrace();
277: mLogger.log(Level.SEVERE, "Unable to save chart data ", e);
278: throw new ChartingException("Unable to save chart data ", e);
279:
280: } finally {
281: if (con != null) {
282: try {
283: con.close();
284: } catch (SQLException e) {
285: e.printStackTrace();
286: mLogger.log(Level.SEVERE,
287: "Failed to close the connection.");
288: }
289: }
290:
291: }
292: }
293:
294: public void persistChartBean(ChartBean bean)
295: throws ChartingException {
296: Connection con = null;
297:
298: String queryString = "Select CHART_ID from CHCHARTS where CHART_ID=?";
299:
300: // Statement stmt = null;
301: // ResultSet rset = null;
302: /** CHCHARTS (CHART_ID varchar(200), GRP_ID integer," +
303: " D_A_ID integer , CHART_D_NAME varchar(200), C_TYPE varchar(40)" +
304: "CHART_DATA varchar(20000)," +
305: "CONSTRAINT FK_GRPID " +
306: " FOREIGN KEY (GRP_ID) " +
307: " REFERENCES CHGROUPS(ID)," +
308: "CONSTRAINT FK_DAGRPID " +
309: " FOREIGN KEY (D_A_ID) " +
310: " REFERENCES CHGROUPS(ID))";
311: *
312: * **/
313: try {
314: con = getConnectionJNDI();
315: PreparedStatement qStmt = con.prepareStatement(queryString);
316: PreparedStatement pstmt = con
317: .prepareStatement("UPDATE CHCHARTS SET D_A_ID = ?,"
318: + "CHART_D_NAME = ?,C_TYPE=?,CHART_DATA=? WHERE CHART_ID = ?");
319: PreparedStatement istmt1 = con
320: .prepareStatement("Insert into CHGROUPS values(?,?)");
321: PreparedStatement istmt2 = con
322: .prepareStatement("Insert into CHCHARTS values(?,?,?,?,?,?)");
323:
324: qStmt.setString(1, bean.getChartID());
325:
326: //stmt = con.createStatement ();
327: //rset = stmt.executeQuery(queryString);
328: if (qStmt.execute() && qStmt.getResultSet().next()) {
329: pstmt.setString(1, bean.getChartDataBeanID());
330: pstmt.setString(2, bean.getChartName());
331: pstmt.setString(3, bean.getChartType());
332: StringBuffer buffer = new StringBuffer();
333: bean.getChartProperties().toXML(buffer);
334: pstmt.setString(4, buffer.toString());
335: pstmt.setString(5, bean.getChartID());
336: pstmt.executeUpdate();
337: } else {
338:
339: //istmt1.setString(1, bean.getParentID());
340: //istmt1.setString(2, bean.getChartParentName());
341:
342: istmt2.setString(1, bean.getChartID());
343: istmt2.setString(2, bean.getParentID());
344: istmt2.setString(3, bean.getChartDataBeanID());
345: istmt2.setString(4, bean.getChartName());
346: istmt2.setString(5, bean.getChartType());
347: StringBuffer buffer = new StringBuffer();
348: bean.getChartProperties().toXML(buffer);
349: istmt2.setString(6, buffer.toString());
350:
351: //istmt1.executeUpdate();
352: istmt2.executeUpdate();
353:
354: }
355: //rset.close();
356: //stmt.close();
357: pstmt.close();
358: istmt1.close();
359: istmt2.close();
360: } catch (SQLException e) {
361: mLogger.log(Level.SEVERE, "Unable to save chart data ", e);
362: throw new ChartingException("Unable to save chart data ", e);
363:
364: } finally {
365: if (con != null) {
366: try {
367: con.close();
368: } catch (SQLException e) {
369: // do nothing.
370: }
371: }
372:
373: }
374: }
375:
376: private Connection getConnectionJNDI() throws SQLException {
377: try {
378: InitialContext ctx = new InitialContext();
379: DataSource ds = (DataSource) ctx.lookup(mDefaultDBJndi);
380: Connection con = ds.getConnection();
381: return con;
382: } catch (NamingException e) {
383: mLogger
384: .severe("Naming Exception occured "
385: + e.getMessage());
386: }
387: return null;
388: }
389:
390: public void setChartSystemDB() throws ChartingException {
391: String checkQuery = "Select CHART_ID from CHCHARTS";
392: Connection con = null;
393: Statement stmt = null;
394: ResultSet rset = null;
395: try {
396: con = getConnectionJNDI();
397: stmt = con.createStatement();
398: rset = stmt.executeQuery(checkQuery);
399: rset.close();
400: stmt.close();
401:
402: } catch (SQLException e) {
403: tryCreateSystemTables();
404:
405: } finally {
406: if (con != null) {
407: try {
408: con.close();
409: } catch (SQLException e) {
410: // do nothing.
411: }
412: }
413:
414: }
415: }
416:
417: private void tryCreateSystemTables() throws ChartingException {
418:
419: String q = "Create table CHCHARTS (CHART_ID varchar(100), GRP_ID varchar(100),"
420: + " D_A_ID varchar(100) , CHART_D_NAME varchar(200), C_TYPE varchar(40),"
421: + "CHART_DATA varchar(20000),"
422: + "CONSTRAINT FK_GRPID "
423: + " FOREIGN KEY (GRP_ID) "
424: + " REFERENCES CHGROUPS(ID),"
425: + "CONSTRAINT FK_DAGRPID_1 "
426: + " FOREIGN KEY (D_A_ID) "
427: + " REFERENCES CHDATAACC(ID))";
428: String q2 = "Create table CHGROUPS (ID varchar(100) NOT NULL , DIS_NAME varchar(20),"
429: + "CONSTRAINT GRPID_PK PRIMARY KEY(ID))";
430:
431: String q3 = "Create table CHDATAACC (ID varchar(100) NOT NULL, GRP_ID varchar(100),"
432: + "D_NAME varchar(200), D_TYPE varchar(40), D_JNDI_N varchar(40),"
433: + "DATA varchar(20000),"
434: + " CONSTRAINT DA_PK PRIMARY KEY(ID),"
435: + " CONSTRAINT FK_GRPID_2 "
436: + " FOREIGN KEY (GRP_ID) "
437: + " REFERENCES CHGROUPS(ID))";
438: Connection con = null;
439: Statement stmt = null;
440:
441: try {
442: con = getConnectionJNDI();
443: stmt = con.createStatement();
444: stmt.addBatch(q2);
445:
446: stmt.addBatch(q3);
447: stmt.addBatch(q);
448: stmt.executeBatch();
449: stmt.close();
450:
451: } catch (SQLException e) {
452: e.printStackTrace();
453: while ((e = e.getNextException()) != null) {
454: e.printStackTrace();
455: }
456: throw new ChartingException(
457: "Unable to create system tables", e);
458:
459: } finally {
460: if (con != null) {
461: try {
462: con.close();
463: } catch (SQLException e) {
464: e.printStackTrace();
465: }
466: }
467:
468: }
469: }
470:
471: }
|