001: /*
002: Copyright (C) 2003 Know Gate S.L. All rights reserved.
003: C/Oņa, 107 1š2 28050 Madrid (Spain)
004:
005: Redistribution and use in source and binary forms, with or without
006: modification, are permitted provided that the following conditions
007: are met:
008:
009: 1. Redistributions of source code must retain the above copyright
010: notice, this list of conditions and the following disclaimer.
011:
012: 2. The end-user documentation included with the redistribution,
013: if any, must include the following acknowledgment:
014: "This product includes software parts from hipergate
015: (http://www.hipergate.org/)."
016: Alternately, this acknowledgment may appear in the software itself,
017: if and wherever such third-party acknowledgments normally appear.
018:
019: 3. The name hipergate must not be used to endorse or promote products
020: derived from this software without prior written permission.
021: Products derived from this software may not be called hipergate,
022: nor may hipergate appear in their name, without prior written
023: permission.
024:
025: This library is distributed in the hope that it will be useful,
026: but WITHOUT ANY WARRANTY; without even the implied warranty of
027: MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
028:
029: You should have received a copy of hipergate License with this code;
030: if not, visit http://www.hipergate.org or mail to info@hipergate.org
031: */
032:
033: package com.knowgate.jdc;
034:
035: import java.util.Map;
036: import java.util.Date;
037:
038: import java.sql.SQLException;
039: import java.sql.SQLWarning;
040: import java.sql.DatabaseMetaData;
041: import java.sql.Statement;
042: import java.sql.PreparedStatement;
043: import java.sql.CallableStatement;
044: import java.sql.ResultSet;
045: import java.sql.Connection;
046: import java.sql.Savepoint;
047: import java.sql.Types;
048: import java.sql.Timestamp;
049:
050: import com.knowgate.debug.DebugFile;
051: import com.knowgate.dataobjs.DBColumn;
052:
053: /**
054: * JDBC Connection Wrapper
055: * @author Sergio Montoro Ten
056: * @version 2.2
057: */
058: public final class JDCConnection implements Connection {
059:
060: public static final short IdClass = 100;
061:
062: public static final int DBMS_GENERIC = 0;
063: public static final int DBMS_MYSQL = 1;
064: public static final int DBMS_POSTGRESQL = 2;
065: public static final int DBMS_MSSQL = 3;
066: public static final int DBMS_ORACLE = 5;
067:
068: private static final int DBMS_UNKNOWN = -1;
069: private static final int DBMS_SYBASE = 4;
070: private static final int DBMS_B2 = 6;
071: private static final int DBMS_INFORMIX = 7;
072: private static final int DBMS_CLOUDSCAPE = 8;
073:
074: private JDCConnectionPool pool;
075: private Connection conn;
076: private boolean inuse;
077: private long timestamp;
078: private int dbms;
079: private String name;
080: private String schema;
081:
082: private static final String DBMSNAME_MSSQL = "Microsoft SQL Server";
083: private static final String DBMSNAME_POSTGRESQL = "PostgreSQL";
084: private static final String DBMSNAME_ORACLE = "Oracle";
085: private static final String DBMSNAME_MYSQL = "MySQL";
086:
087: public JDCConnection(Connection conn, JDCConnectionPool pool,
088: String schemaname) {
089: this .dbms = DBMS_UNKNOWN;
090: this .conn = conn;
091: this .pool = pool;
092: this .inuse = false;
093: this .timestamp = 0;
094: this .name = null;
095: this .schema = schemaname;
096: }
097:
098: public JDCConnection(Connection conn, JDCConnectionPool pool) {
099: this .dbms = DBMS_UNKNOWN;
100: this .conn = conn;
101: this .pool = pool;
102: this .inuse = false;
103: this .timestamp = 0;
104: this .name = null;
105: this .schema = null;
106: }
107:
108: public boolean lease(String sConnectionName) {
109: if (inuse) {
110: return false;
111: } else {
112: inuse = true;
113: name = sConnectionName;
114: timestamp = System.currentTimeMillis();
115: return true;
116: }
117: }
118:
119: public boolean validate() {
120: boolean bValid;
121:
122: if (DebugFile.trace) {
123: DebugFile.writeln("Begin JDCConnection.validate()");
124: DebugFile.incIdent();
125: }
126: try {
127: conn.getMetaData();
128: bValid = true;
129: } catch (Exception e) {
130: DebugFile.writeln(new Date().toString() + " "
131: + e.getMessage());
132: bValid = false;
133: }
134:
135: if (DebugFile.trace) {
136: DebugFile.decIdent();
137: DebugFile.writeln("End JDCConnection.validate()");
138: }
139:
140: return bValid;
141: }
142:
143: public boolean inUse() {
144: return inuse;
145: }
146:
147: public JDCConnectionPool getPool() {
148: return pool;
149: }
150:
151: public long getLastUse() {
152: return timestamp;
153: }
154:
155: public String getName() {
156: return name;
157: }
158:
159: public int getDataBaseProduct() throws SQLException {
160: DatabaseMetaData mdat;
161: String prod;
162:
163: if (DBMS_UNKNOWN == dbms) {
164: try {
165: mdat = conn.getMetaData();
166: prod = mdat.getDatabaseProductName();
167:
168: if (prod.equals(DBMSNAME_MSSQL))
169: dbms = DBMS_MSSQL;
170: else if (prod.equals(DBMSNAME_POSTGRESQL))
171: dbms = DBMS_POSTGRESQL;
172: else if (prod.equals(DBMSNAME_ORACLE))
173: dbms = DBMS_ORACLE;
174: else if (prod.equals(DBMSNAME_MYSQL))
175: dbms = DBMS_MYSQL;
176: else
177: dbms = DBMS_GENERIC;
178: } catch (NullPointerException npe) {
179: if (DebugFile.trace)
180: DebugFile
181: .writeln("NullPointerException at JDCConnection.getDataBaseProduct()");
182: dbms = DBMS_GENERIC;
183: }
184: }
185: return dbms;
186: }
187:
188: public String getSchemaName() throws SQLException {
189: String sname;
190:
191: if (null == schema) {
192: DatabaseMetaData mdat = conn.getMetaData();
193: ResultSet rset = mdat.getSchemas();
194:
195: if (rset.next())
196: sname = rset.getString(1);
197: else
198: sname = null;
199:
200: rset.close();
201: } else
202: sname = schema;
203:
204: return sname;
205: }
206:
207: public void setSchemaName(String sname) {
208: schema = sname;
209: }
210:
211: public void close() throws SQLException {
212: if (DebugFile.trace) {
213: DebugFile.writeln("Begin JDCConnection.close()");
214: DebugFile.incIdent();
215: }
216:
217: if (pool == null) {
218: inuse = false;
219: name = null;
220: conn.close();
221: } else {
222: pool.returnConnection(this );
223: }
224:
225: if (DebugFile.trace) {
226: DebugFile.decIdent();
227: DebugFile.writeln("End JDCConnection.close()");
228: }
229: }
230:
231: public void close(String sCaller) throws SQLException {
232: if (DebugFile.trace) {
233: DebugFile.writeln("Begin JDCConnection.close(" + sCaller
234: + ")");
235: DebugFile.incIdent();
236: }
237: if (pool == null) {
238: inuse = false;
239: name = null;
240: conn.close();
241: } else {
242: pool.returnConnection(this , sCaller);
243: }
244:
245: if (DebugFile.trace) {
246: DebugFile.decIdent();
247: DebugFile.writeln("End JDCConnection.close(" + sCaller
248: + ")");
249: }
250: }
251:
252: protected void expireLease() {
253: inuse = false;
254: name = null;
255: }
256:
257: protected Connection getConnection() {
258: return conn;
259: }
260:
261: public Statement createStatement(int i, int j) throws SQLException {
262: return conn.createStatement(i, j);
263: }
264:
265: public Statement createStatement(int i, int j, int k)
266: throws SQLException {
267: return conn.createStatement(i, j, k);
268: }
269:
270: public PreparedStatement prepareStatement(String sql)
271: throws SQLException {
272: return conn.prepareStatement(sql);
273: }
274:
275: public PreparedStatement prepareStatement(String sql,
276: String[] params) throws SQLException {
277: return conn.prepareStatement(sql, params);
278: }
279:
280: public PreparedStatement prepareStatement(String sql, int i)
281: throws SQLException {
282: return conn.prepareStatement(sql, i);
283: }
284:
285: public PreparedStatement prepareStatement(String sql, int i, int j)
286: throws SQLException {
287: return conn.prepareStatement(sql, i, j);
288: }
289:
290: public PreparedStatement prepareStatement(String sql, int i, int j,
291: int k) throws SQLException {
292: return conn.prepareStatement(sql, i, j, k);
293: }
294:
295: public PreparedStatement prepareStatement(String sql, int[] params)
296: throws SQLException {
297: return conn.prepareStatement(sql, params);
298: }
299:
300: public CallableStatement prepareCall(String sql)
301: throws SQLException {
302: return conn.prepareCall(sql);
303: }
304:
305: public CallableStatement prepareCall(String sql, int i, int j)
306: throws SQLException {
307: return conn.prepareCall(sql, i, j);
308: }
309:
310: public CallableStatement prepareCall(String sql, int i, int j, int k)
311: throws SQLException {
312: return conn.prepareCall(sql, i, j, k);
313: }
314:
315: public Statement createStatement() throws SQLException {
316: return conn.createStatement();
317: }
318:
319: public String nativeSQL(String sql) throws SQLException {
320: return conn.nativeSQL(sql);
321: }
322:
323: public void setAutoCommit(boolean autoCommit) throws SQLException {
324: conn.setAutoCommit(autoCommit);
325: }
326:
327: public boolean getAutoCommit() throws SQLException {
328: return conn.getAutoCommit();
329: }
330:
331: public int getHoldability() throws SQLException {
332: return conn.getHoldability();
333: }
334:
335: public void setHoldability(int h) throws SQLException {
336: conn.setHoldability(h);
337: }
338:
339: public Savepoint setSavepoint() throws SQLException {
340: return conn.setSavepoint();
341: }
342:
343: public Savepoint setSavepoint(String s) throws SQLException {
344: return conn.setSavepoint(s);
345: }
346:
347: public void commit() throws SQLException {
348: conn.commit();
349: }
350:
351: public void rollback() throws SQLException {
352: conn.rollback();
353: }
354:
355: public void rollback(Savepoint p) throws SQLException {
356: conn.rollback(p);
357: }
358:
359: public boolean isClosed() throws SQLException {
360: return conn.isClosed();
361: }
362:
363: public DatabaseMetaData getMetaData() throws SQLException {
364: return conn.getMetaData();
365: }
366:
367: public void setReadOnly(boolean readOnly) throws SQLException {
368: conn.setReadOnly(readOnly);
369: }
370:
371: public boolean isReadOnly() throws SQLException {
372: return conn.isReadOnly();
373: }
374:
375: public void setCatalog(String catalog) throws SQLException {
376: conn.setCatalog(catalog);
377: }
378:
379: public String getCatalog() throws SQLException {
380: return conn.getCatalog();
381: }
382:
383: public void setTransactionIsolation(int level) throws SQLException {
384: conn.setTransactionIsolation(level);
385: }
386:
387: public int getTransactionIsolation() throws SQLException {
388: return conn.getTransactionIsolation();
389: }
390:
391: public Map getTypeMap() throws SQLException {
392: return conn.getTypeMap();
393: }
394:
395: public void setTypeMap(Map typemap) throws SQLException {
396: conn.setTypeMap(typemap);
397: }
398:
399: public SQLWarning getWarnings() throws SQLException {
400: return conn.getWarnings();
401: }
402:
403: public void clearWarnings() throws SQLException {
404: conn.clearWarnings();
405: }
406:
407: public void releaseSavepoint(Savepoint p) throws SQLException {
408: conn.releaseSavepoint(p);
409: }
410:
411: /**
412: * Checks if an object exists at database
413: * Checking is done directly against database catalog tables,
414: * if current user does not have enought priviledges for reading
415: * database catalog tables methos may fail or return a wrong result.
416: * @param sObjectName Objeto name
417: * @param sObjectType Objeto type
418: * C = CHECK constraint
419: * D = Default or DEFAULT constraint
420: * F = FOREIGN KEY constraint
421: * L = Log
422: * P = Stored procedure
423: * PK = PRIMARY KEY constraint (type is K)
424: * RF = Replication filter stored procedure
425: * S = System table
426: * TR = Trigger
427: * U = User table
428: * UQ = UNIQUE constraint (type is K)
429: * V = View
430: * X = Extended stored procedure
431: * @return <b>true</b> if object exists, <b>false</b> otherwise
432: * @throws SQLException
433: * @throws UnsupportedOperationException If current database management system is not supported for this method
434: */
435:
436: public boolean exists(String sObjectName, String sObjectType)
437: throws SQLException, UnsupportedOperationException {
438: boolean bRetVal;
439: PreparedStatement oStmt;
440: ResultSet oRSet;
441:
442: if (DebugFile.trace) {
443: DebugFile
444: .writeln("Begin JDCConnection.exists([Connection], "
445: + sObjectName + ", " + sObjectType + ")");
446: DebugFile.incIdent();
447: }
448:
449: switch (this .getDataBaseProduct()) {
450:
451: case JDCConnection.DBMS_MSSQL:
452: if (DebugFile.trace)
453: DebugFile
454: .writeln("Connection.prepareStatement(SELECT id FROM sysobjects WHERE name='"
455: + sObjectName
456: + "' AND xtype='"
457: + sObjectType + "' OPTION (FAST 1))");
458:
459: oStmt = this
460: .prepareStatement(
461: "SELECT id FROM sysobjects WHERE name=? AND xtype=? OPTION (FAST 1)",
462: ResultSet.TYPE_FORWARD_ONLY,
463: ResultSet.CONCUR_READ_ONLY);
464: oStmt.setString(1, sObjectName);
465: oStmt.setString(2, sObjectType);
466: oRSet = oStmt.executeQuery();
467: bRetVal = oRSet.next();
468: oRSet.close();
469: oStmt.close();
470: break;
471:
472: case JDCConnection.DBMS_POSTGRESQL:
473: if (DebugFile.trace)
474: DebugFile
475: .writeln("Conenction.prepareStatement(SELECT relname FROM pg_class WHERE relname='"
476: + sObjectName + "')");
477:
478: oStmt = this
479: .prepareStatement(
480: "SELECT tablename FROM pg_tables WHERE tablename=?",
481: ResultSet.TYPE_FORWARD_ONLY,
482: ResultSet.CONCUR_READ_ONLY);
483: oStmt.setString(1, sObjectName);
484: oRSet = oStmt.executeQuery();
485: bRetVal = oRSet.next();
486: oRSet.close();
487: oStmt.close();
488: break;
489:
490: case JDCConnection.DBMS_ORACLE:
491: if (DebugFile.trace)
492: DebugFile
493: .writeln("Conenction.prepareStatement(SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME='"
494: + sObjectName + "')");
495:
496: oStmt = this
497: .prepareStatement(
498: "SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME=?",
499: ResultSet.TYPE_FORWARD_ONLY,
500: ResultSet.CONCUR_READ_ONLY);
501: oStmt.setString(1, sObjectName.toUpperCase());
502: oRSet = oStmt.executeQuery();
503: bRetVal = oRSet.next();
504: oRSet.close();
505: oStmt.close();
506: break;
507:
508: case JDCConnection.DBMS_MYSQL:
509: if (DebugFile.trace)
510: DebugFile
511: .writeln("Conenction.prepareStatement(SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name='"
512: + sObjectName + "')");
513:
514: oStmt = this
515: .prepareStatement(
516: "SELECT table_name FROM INFORMATION_SCHEMA.TABLES WHERE table_name=?",
517: ResultSet.TYPE_FORWARD_ONLY,
518: ResultSet.CONCUR_READ_ONLY);
519: oStmt.setString(1, sObjectName);
520: oRSet = oStmt.executeQuery();
521: bRetVal = oRSet.next();
522: oRSet.close();
523: oStmt.close();
524: break;
525:
526: default:
527: throw new UnsupportedOperationException("Unsupported DBMS");
528: } // end switch()
529:
530: if (DebugFile.trace) {
531: DebugFile.decIdent();
532: DebugFile.writeln("End JDCConnection.exists() : "
533: + String.valueOf(bRetVal));
534: }
535:
536: return bRetVal;
537: } // exists()
538:
539: /**
540: * <p>Get operating system process identifier for this connection</p>
541: * @return String For PostgreSQL the id of the UNIX process attending this connection.
542: * For Oracle a Session Id.
543: * @throws SQLException
544: * @since 2.2
545: */
546: public String pid() throws SQLException {
547: Statement oStmt;
548: ResultSet oRSet;
549: String sPId = null;
550: switch (getDataBaseProduct()) {
551: case DBMS_POSTGRESQL:
552: oStmt = createStatement();
553: oRSet = oStmt.executeQuery("SELECT pg_backend_pid()");
554: if (oRSet.next())
555: sPId = String.valueOf(oRSet.getInt(1));
556: oRSet.close();
557: oStmt.close();
558: break;
559: case DBMS_ORACLE:
560: oStmt = createStatement();
561: oRSet = oStmt
562: .executeQuery("SELECT SYS_CONTEXT('USERENV','SESIONID') FROM DUAL");
563: if (oRSet.next())
564: sPId = oRSet.getString(1);
565: oRSet.close();
566: oStmt.close();
567: break;
568: }
569: return sPId;
570: } // pid
571:
572: // ---------------------------------------------------------------------------
573:
574: /**
575: * <p>Bind parameter into a PreparedStatement</p>
576: * @param oStmt PreparedStatement where values is to be binded
577: * @param iParamIndex int Starting with 1
578: * @param oParamValue Object
579: * @param iSQLType int
580: * @throws SQLException
581: */
582: public void bindParameter(PreparedStatement oStmt, int iParamIndex,
583: Object oParamValue, int iSQLType) throws SQLException {
584:
585: Class oParamClass;
586:
587: switch (getDataBaseProduct()) {
588:
589: case JDCConnection.DBMS_ORACLE:
590: if (oParamValue != null) {
591:
592: oParamClass = oParamValue.getClass();
593:
594: if (DebugFile.trace)
595: DebugFile.writeln("binding "
596: + oParamClass.getName() + " as SQL "
597: + DBColumn.typeName(iSQLType));
598:
599: if (oParamClass.equals(Short.class)
600: || oParamClass.equals(Integer.class)
601: || oParamClass.equals(Float.class)
602: || oParamClass.equals(Double.class))
603: oStmt.setBigDecimal(iParamIndex,
604: new java.math.BigDecimal(oParamValue
605: .toString()));
606:
607: // New for hipergate v2.1 support for Oracle 9.2 change of behaviour with regard of DATE and TIMESTAMP columns
608: // see http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm#08_01 for more details
609: // If the binded parameter if a java.sql.Timestamp and the underlying database column is a DATE
610: // Then create a new java.util.Date object from the Timestamp miliseconds and pass it to the
611: // JDBC driver instead of the original Timestamp
612: else if ((oParamClass.getName().equals(
613: "java.sql.Timestamp") || oParamClass.getName()
614: .equals("java.util.Date"))
615: && iSQLType == Types.DATE) {
616: try {
617: Class[] aTimestamp = new Class[1];
618: aTimestamp[0] = Class
619: .forName("java.sql.Timestamp");
620: Class cDATE = Class.forName("oracle.sql.DATE");
621: java.lang.reflect.Constructor cNewDATE = cDATE
622: .getConstructor(aTimestamp);
623: Object oDATE;
624: if (oParamClass.getName().equals(
625: "java.sql.Timestamp")) {
626: oDATE = cNewDATE
627: .newInstance(new Object[] { oParamValue });
628: } else {
629: oDATE = cNewDATE
630: .newInstance(new Object[] { new Timestamp(
631: ((java.util.Date) oParamValue)
632: .getTime()) });
633: }
634: oStmt.setObject(iParamIndex, oDATE, iSQLType);
635: } catch (ClassNotFoundException cnf) {
636: throw new SQLException(
637: "ClassNotFoundException oracle.sql.DATE "
638: + cnf.getMessage());
639: } catch (NoSuchMethodException nsm) {
640: throw new SQLException("NoSuchMethodException "
641: + nsm.getMessage());
642: } catch (IllegalAccessException iae) {
643: throw new SQLException(
644: "IllegalAccessException "
645: + iae.getMessage());
646: } catch (InstantiationException ine) {
647: throw new SQLException(
648: "InstantiationException "
649: + ine.getMessage());
650: } catch (java.lang.reflect.InvocationTargetException ite) {
651: throw new SQLException(
652: "InvocationTargetException "
653: + ite.getMessage());
654: }
655: } else if (oParamClass.getName().equals(
656: "java.util.Date")
657: && iSQLType == Types.TIMESTAMP) {
658: oStmt.setTimestamp(iParamIndex, new Timestamp(
659: ((java.util.Date) oParamValue).getTime()));
660: } else {
661: oStmt.setObject(iParamIndex, oParamValue, iSQLType);
662: }
663: } else
664: oStmt.setObject(iParamIndex, null, iSQLType);
665: break;
666:
667: default:
668: String sParamClassName;
669: if (null != oParamValue)
670: sParamClassName = oParamValue.getClass().getName();
671: else
672: sParamClassName = "null";
673:
674: if ((Types.TIMESTAMP == iSQLType) && (oParamValue != null)) {
675: if (sParamClassName.equals("java.util.Date")) {
676: if (DebugFile.trace)
677: DebugFile
678: .writeln("binding java.sql.Timestamp as SQL "
679: + DBColumn
680: .typeName(Types.TIMESTAMP));
681: oStmt.setTimestamp(iParamIndex, new Timestamp(
682: ((java.util.Date) oParamValue).getTime()));
683: } else {
684: if (DebugFile.trace)
685: DebugFile.writeln("binding " + sParamClassName
686: + " as SQL "
687: + DBColumn.typeName(iSQLType));
688: oStmt.setObject(iParamIndex, oParamValue, iSQLType);
689: }
690: } else if ((Types.DATE == iSQLType)
691: && (oParamValue != null)) {
692: if (sParamClassName.equals("java.util.Date")) {
693: if (DebugFile.trace)
694: DebugFile
695: .writeln("binding java.sql.Date as SQL "
696: + DBColumn.typeName(Types.DATE));
697: oStmt.setDate(iParamIndex, new java.sql.Date(
698: ((java.util.Date) oParamValue).getTime()));
699: } else {
700: if (DebugFile.trace)
701: DebugFile.writeln("binding " + sParamClassName
702: + " as SQL "
703: + DBColumn.typeName(iSQLType));
704: oStmt.setObject(iParamIndex, oParamValue, iSQLType);
705: }
706: } else {
707: if ((oParamValue != null) && DebugFile.trace) {
708: if (DebugFile.trace)
709: DebugFile.writeln("binding " + sParamClassName
710: + " as SQL "
711: + DBColumn.typeName(iSQLType));
712: }
713: oStmt.setObject(iParamIndex, oParamValue, iSQLType);
714: }
715: }
716: } // bindParameter
717:
718: // ---------------------------------------------------------------------------
719:
720: public void bindParameter(PreparedStatement oStmt, int iParamIndex,
721: Object oParamValue) throws SQLException {
722:
723: if (getDataBaseProduct() == JDCConnection.DBMS_ORACLE) {
724: if (oParamValue.getClass().equals(Integer.class)
725: || oParamValue.getClass().equals(Short.class)
726: || oParamValue.getClass().equals(Float.class)
727: || oParamValue.getClass().equals(Double.class)) {
728: bindParameter(oStmt, iParamIndex, oParamValue,
729: Types.NUMERIC);
730: } else if (oParamValue.getClass().getName().equals(
731: "java.util.Date")
732: || oParamValue.getClass().getName().equals(
733: "java.sql.Timestamp")) {
734: bindParameter(oStmt, iParamIndex, oParamValue,
735: Types.DATE);
736: } else {
737: oStmt.setObject(iParamIndex, oParamValue);
738: }
739: } else {
740: oStmt.setObject(iParamIndex, oParamValue);
741: }
742: } // bindParameter
743: }
|