Java > Open Source Codes > org > openharmonise > commons > dsi > AbstractDataStoreInterface


1 /*
2  * The contents of this file are subject to the
3  * Mozilla Public License Version 1.1 (the "License");
4  * you may not use this file except in compliance with the License.
5  * You may obtain a copy of the License at http://www.mozilla.org/MPL/
6  *
7  * Software distributed under the License is distributed on an "AS IS"
8  * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied.
9  * See the License for the specific language governing rights and
10  * limitations under the License.
11  *
12  * The Initial Developer of the Original Code is Simulacra Media Ltd.
13  * Portions created by Simulacra Media Ltd are Copyright (C) Simulacra Media Ltd, 2004.
14  *
15  * All Rights Reserved.
16  *
17  * Contributor(s):
18  */
19 package org.openharmonise.commons.dsi;
20
21
22 import java.sql.*;
23
24 import java.text.*;
25
26 import java.util.*;
27 import java.util.logging.*;
28 import java.util.logging.Logger ;
29
30 import org.openharmonise.commons.dsi.ddl.*;
31 import org.openharmonise.commons.dsi.dml.*;
32
33 /**
34   * Class providing interface to the database.
35   * Contains general purpose functions that run queries
36   * and extract cached info such as the data schema and
37   * language-dependent text.
38   *
39   * @author Michael Bell
40   *
41   * @see java.sql
42   * @see oracle.sql
43   */
44 public abstract class AbstractDataStoreInterface extends Object {
45     
46     /**
47      * Constant indicating a pooled connection
48      */
49     public static final int POOLED_CONNECTION = 0;
50     
51     /**
52      * Constant indicating a non-pooled connection
53      */
54     public static final int OTHER_CONNECTION = 1;
55     
56     /**
57      * Consant indication a db connection broker connection
58      */
59     public static final int DB_CONNECTION_BROKER = 2;
60
61     /**
62      * Date format for database
63      */
64     protected static String DB_DATEFORMAT = "MM-dd-yyyy HH:mm:ss.SSS";
65     
66     /**
67      * Date format for output
68      */
69     private static final String DB_OUT_DATEFORMAT = "MMM dd yyyy hh:mma";
70     
71     /**
72      * JDBC driver class name
73      */
74     private String m_jdbc_driver = null;
75     
76     /**
77      * Database URI
78      */
79     private String m_db_url = null;
80     
81     /**
82      * Database user name
83      */
84     private String m_db_usr = null;
85     
86     /**
87      * Database user password
88      */
89     private String m_db_pwd = null;
90
91     /**
92       * Database connection.
93       */
94     private Connection m_connection = null;
95     
96     /**
97      * Database connection type
98      */
99     private int m_connectionType = 2;
100     
101     /**
102      * Boolean flag to indicate whether the DB supports the F421 National Character feature
103      */
104     static protected boolean m_bIsNationalCharacterSupported = false;
105
106     
107     /**
108      * Logger for this class
109      */
110     private static final Logger m_logger = Logger.getLogger(AbstractDataStoreInterface.class.getName());
111
112     /**
113       * Constructor an simple data store interface with no details set
114       */
115     public AbstractDataStoreInterface() {
116     }
117
118     /**
119      * Constructs a data store interface with the given connection parameters.
120      *
121      * @param sJDBCDriver the JDBC driver class name
122      * @param sDBurl the database URI
123      * @param sDBUsr the database user name
124      * @param sDBPwd the database user password
125      */
126     public AbstractDataStoreInterface(
127         String sJDBCDriver,
128         String sDBurl,
129         String sDBUsr,
130         String sDBPwd) {
131         m_jdbc_driver = sJDBCDriver;
132         m_db_url = sDBurl;
133         m_db_usr = sDBUsr;
134         m_db_pwd = sDBPwd;
135     }
136
137     /**
138      * Sets the connection details for this data store interface.
139      *
140      * @param sJDBCDriver the JDBC driver class name
141      * @param sDBurl the database URI
142      * @param sDBUsr the database user name
143      * @param sDBPwd the database user password
144      */
145     public void setDataStoreDetails(
146         String sJDBCDriver,
147         String sDBurl,
148         String sDBUsr,
149         String sDBPwd) {
150         m_jdbc_driver = sJDBCDriver;
151         m_db_url = sDBurl;
152         m_db_usr = sDBUsr;
153         m_db_pwd = sDBPwd;
154     }
155
156     /**
157      * Constructs a data store interface with the specified connection type.
158      *
159      * @param nConnectionType the connection type
160      * @throws Exception if the specified connection type is invalid
161      */
162     public AbstractDataStoreInterface(int nConnectionType) throws DataStoreException {
163         if ((nConnectionType != POOLED_CONNECTION)
164             && (nConnectionType != OTHER_CONNECTION)
165             && (nConnectionType != DB_CONNECTION_BROKER)) {
166             throw new DataStoreException("Invalid connection type");
167         } else {
168             m_connectionType = nConnectionType;
169         }
170     }
171
172     /**
173      * Initialise this data store interface with the specified connection type.
174      *
175      * @param nConnectionType the connection type
176      * @throws Exception if the specified connection type is invalid
177      */
178     public void initialise(int nConnectionType) throws Exception {
179         if ((nConnectionType != POOLED_CONNECTION)
180             && (nConnectionType != OTHER_CONNECTION)
181             && (nConnectionType != DB_CONNECTION_BROKER)) {
182             throw new Exception ("Invalid connection type");
183         } else {
184             m_connectionType = nConnectionType;
185         }
186     }
187
188     /**
189        * Disconnects from database.
190        *
191        * @exception SQLException if an error occurs closing the connection
192        */
193     public void disconnect() throws SQLException {
194         if (m_connection != null) {
195             m_connection.close();
196             m_connection = null;
197         }
198     }
199
200     /**
201      * Returns <code>true</code> if whether this data store interface uses a pooled connection.
202      *
203      * @return <code>true</code> if whether this data store interface uses a pooled connection.
204      */
205     public boolean isPooledConnection() {
206         if ((m_connectionType == POOLED_CONNECTION)
207             || (m_connectionType == DB_CONNECTION_BROKER)) {
208             return true;
209         } else {
210             return false;
211         }
212     }
213     /**
214      * Returns the database connection.
215      *
216      * @return the database connection.
217      * @throws DataStoreException if a connection can not be created
218      */
219     public Connection getConnection() throws DataStoreException {
220         Connection conn = null;
221
222         //check details first
223 if (this.m_jdbc_driver == null
224             || this.m_db_url == null
225             || this.m_db_usr == null
226             || this.m_db_pwd == null) {
227             throw new DataStoreException("Don't have enough config details to get connection");
228         }
229
230         try {
231             if (m_connectionType == OTHER_CONNECTION) {
232                 if (m_connection == null) {
233                     Class.forName(m_jdbc_driver);
234                     m_connection =
235                         DriverManager.getConnection(
236                             m_db_url,
237                             m_db_usr,
238                             m_db_pwd);
239                 }
240
241                 conn = m_connection;
242             } else if (m_connectionType == DB_CONNECTION_BROKER) {
243
244                 conn =
245                     DBConnectionPooler
246                         .getInstance(
247                             m_jdbc_driver,
248                             m_db_url,
249                             m_db_usr,
250                             m_db_pwd)
251                         .getConnection();
252             }
253         } catch (Exception e) {
254             m_logger.log(Level.WARNING, e.getLocalizedMessage(), e);
255             throw new DataStoreException(e.getMessage());
256         }
257
258         return conn;
259     }
260     
261     /**
262      * Returns the URL of the database.
263      *
264      * @return the URL of the database
265      */
266     public String getDatabaseURL() {
267         return m_db_url;
268     }
269
270     /**
271      * Returns the next value in the specified sequence.
272      *
273      * @param sSeqName name of sequence
274      * @return the next value in the specified sequence.
275      * @throws DataStoreException if there is an error building the query
276      * at the data store interface level
277      * @throws SQLException if there is an error at the jdbc level
278      */
279     public abstract int getSequenceNextValue(String sSeqName)
280         throws DataStoreException, SQLException;
281
282     /**
283      * Inserts CLOB data into database.
284      *
285      * @param sTable the table name
286      * @param sColumn the column name
287      * @param sClob the CLOB text
288      * @param sCondition the condition attached to the insert statement
289      * @throws DataStoreException if an error occurs
290      */
291     public abstract void insertClob(
292         String sTable,
293         String sColumn,
294         String sClob,
295         String sCondition)
296         throws DataStoreException;
297
298     /**
299      * Updates CLOB text in the database.
300      *
301      * @param sTable the table name
302      * @param sColumn the column name
303      * @param sClob the clob text
304      * @param sCondition the condition for locating the fields to update
305      * @throws DataStoreException if an error occurs
306      */
307     public abstract void updateClob(
308         String sTable,
309         String sColumn,
310         String sClob,
311         String sCondition)
312         throws DataStoreException;
313
314     /**
315      * Returns the text held in a CLOB field in the database.
316      *
317      * @param sTable the table name
318      * @param sColumn the colmn name
319      * @param sCondition the condition for location of the CLOB
320      * @return
321      * @throws DataStoreException
322      */
323     public abstract String getClob(
324         String sTable,
325         String sColumn,
326         String sCondition)
327         throws DataStoreException;
328
329     /**
330     * Executes SQL query specified by <code>sSql</code> on the database.
331     *
332     * @param sSql SQL Query
333     * @exception DataStoreException if an error occurs
334     */
335     public void execute(String sSql) throws DataStoreException {
336         Connection conn = null;
337         Statement stmt = null;
338
339         try {
340             conn = getConnection();
341             stmt = conn.createStatement();
342
343             stmt.setEscapeProcessing(true);
344
345             try {
346                 stmt.execute(sSql);
347             } catch (SQLException e) {
348                 throw new SQLException(e.getMessage() + sSql);
349             }
350
351             if (stmt != null) {
352                 stmt.close();
353             }
354
355             if (isPooledConnection() && (conn != null)) {
356                 this.closeConnection(conn);
357             }
358         } catch (SQLException e) {
359             m_logger.log(Level.WARNING, "SQL:" + sSql, e);
360             throw new DataStoreException("SQLException: " + e.getMessage());
361         }
362     }
363
364     /**
365     * Executes a specified SQL update statement on the database.
366     *
367     * @param sSql SQL update statement
368     * @return a code indicating success
369     * @exception DataStoreException
370     */
371     public int executeUpdate(String sSql) throws DataStoreException {
372         Connection conn = null;
373         Statement stmt = null;
374         int nReturn = -1;
375
376         try {
377             conn = getConnection();
378             stmt = conn.createStatement();
379
380             stmt.setEscapeProcessing(true);
381
382             try {
383                 nReturn = stmt.executeUpdate(sSql);
384             } catch (SQLException e) {
385                 throw new SQLException(e.getMessage() + sSql);
386             }
387
388             if (stmt != null) {
389                 stmt.close();
390             }
391
392             if ((isPooledConnection() == true) && (conn != null)) {
393                 this.closeConnection(conn);
394             }
395         } catch (SQLException e) {
396             throw new DataStoreException("SQLException: " + e.getMessage());
397         }
398
399         return nReturn;
400     }
401
402     /**
403     * Executes a specified SQL query on the database.
404     *
405     * @param sSql the SQL Query
406     * @return the result of the query
407     * @exception DataStoreException
408     */
409     public ResultSet executeQuery(String sSql) throws DataStoreException {
410         Connection conn = null;
411         Statement stmt = null;
412         ResultSet rs = null;
413
414         try {
415             conn = getConnection();
416             stmt = conn.createStatement();
417
418             stmt.setEscapeProcessing(true);
419             
420             if(m_logger.isLoggable(Level.FINEST)) {
421                 m_logger.logp(Level.FINEST, this.getClass().getName(), "executeQuery", sSql);
422             }
423
424             rs = stmt.executeQuery(sSql);
425
426             if (isPooledConnection() && (conn != null)) {
427                 this.closeConnection(conn);
428             }
429
430             rs = new HarmoniseResultSet(stmt, rs);
431         } catch (SQLException e) {
432             m_logger.log(Level.WARNING, "Error running SQL - " + sSql, e);
433             throw new DataStoreException(
434                 "SQLException: " + e.getMessage() + " " + sSql);
435         }
436
437         return rs;
438     }
439
440     /**
441      * Executes the given select statement on the database.
442      *
443      * @param query the select statement
444      * @return the resultant result set
445      * @throws DataStoreException if an error occurs
446      */
447     public ResultSet executeQuery(SelectStatement query)
448         throws DataStoreException {
449         String sSql = getSelectStatement(query);
450
451         return (executeQuery(sSql));
452     }
453
454     /**
455      * Executes the given select statement on the database.
456      *
457      * @param query the select statement
458      * @return the resultant result set
459      * @throws DataStoreException if an error occurs
460      */
461     public ResultSet execute(SelectStatement query) throws DataStoreException {
462         String sSql = getSelectStatement(query);
463
464         return (executeQuery(sSql));
465     }
466
467     /**
468      * Executes the given update statement on the database.
469      *
470      * @param update the update statement
471      * @return a success code
472      * @throws DataStoreException if an error occurs
473      */
474     public int executeUpdate(UpdateStatement update)
475         throws DataStoreException {
476         String sSql = getUpdateStatement(update);
477
478         return (executeUpdate(sSql));
479     }
480
481     /**
482      * Executes the given update statement on the database.
483      *
484      * @param update the update statement
485      * @return a success code
486      * @throws DataStoreException if an error occurs
487      */
488     public int execute(UpdateStatement update) throws DataStoreException {
489         String sSql = getUpdateStatement(update);
490
491         return (executeUpdate(sSql));
492     }
493
494     /**
495      * Executes the given insert statement on the database.
496      *
497      * @param insert the insert statement
498      * @throws DataStoreException if an error occurs
499      */
500     public void executeInsert(InsertStatement insert)
501         throws DataStoreException {
502         String sSql = getInsertStatement(insert);
503
504         execute(sSql);
505     }
506
507     /**
508      * Executes the given insert statement on the database.
509      *
510      * @param insert the insert statement
511      * @throws DataStoreException if an error occurs
512      */
513     public void execute(InsertStatement insert) throws DataStoreException {
514         String sSql = getInsertStatement(insert);
515
516         execute(sSql);
517     }
518     
519     /**
520      * Creates a new database table from the given table definition.
521      *
522      * @param tblDef the definition of the table to create
523      * @throws DataStoreException if an error occurs creating the new table
524      */
525     abstract public void createTable(TableDefinition tblDef) throws DataStoreException ;
526
527     /**
528      * Executes the given delete statement on the database.
529      *
530      * @param delete the delete statement
531      * @throws DataStoreException if an error occurs
532      */
533     public void execute(DeleteStatement delete) throws DataStoreException {
534         String sSql = getDeleteStatement(delete);
535
536         execute(sSql);
537     }
538     
539     /**
540     * Returns an escaped string which can be entered in to the database without
541     * 'special' characters causing a problem.
542     *
543     * @param sOldString contains the string to be checked
544     *
545     * @return the <code> String </code> of the new text, including any changes made if they were necessary
546     */
547     protected abstract String addEscapeChars(String sOldString);
548
549     /**
550      * Returns the SQL statement corresponding to the given
551      * <code>InsertStatement</code>.
552      *
553      * @param insert the insert statement
554      * @return the SQL statement
555      * @throws DataStoreException if any arrors occur
556      */
557     public String getInsertStatement(InsertStatement insert)
558         throws DataStoreException {
559         boolean bflag = false;
560         Vector saValues = new Vector(16);
561         Vector ColRefs = new Vector(16);
562
563         Map ValuePairs = insert.getColumnValuePairs();
564
565         Set set = ValuePairs.keySet();
566
567         Iterator iter = set.iterator();
568
569         while (iter.hasNext()) {
570             ColumnRef colref = (ColumnRef) iter.next();
571             ColRefs.add(colref);
572
573             saValues.add(ValuePairs.get(colref));
574         }
575
576         StringBuffer sSql = new StringBuffer ();
577
578         sSql.append("insert into ");
579
580         sSql.append(((ColumnRef) ColRefs.elementAt(0)).getTable());
581
582         if (insert.isColumnValuesBySelect()) {
583             sSql.append(" ");
584             sSql.append(getSelectStatement(insert.getColumnValuesSelect()));
585         } else {
586             sSql.append(" (");
587
588             for (int i = 0; i < ColRefs.size(); i++) {
589                 if (bflag) {
590                     sSql.append(",");
591                 }
592
593                 sSql.append(((ColumnRef) ColRefs.elementAt(i)).getColumn());
594                 bflag = true;
595             }
596
597             sSql.append(") values (");
598
599             for (int i = 0; i < saValues.size(); i++) {
600                 if (i > 0) {
601                     sSql.append(",");
602                 }
603                 try {
604
605                     if (saValues.elementAt(i) instanceof String ) {
606                         if(isNationalCharacterSupported()) {
607                             sSql.append("N");
608                         }
609                         sSql.append("'");
610                         sSql.append(
611                             addEscapeChars((String ) saValues.elementAt(i)));
612                         sSql.append("'");
613                     } else if (saValues.elementAt(i) instanceof Integer ) {
614                         sSql.append((Integer ) saValues.elementAt(i));
615                     } else if (
616                         saValues.elementAt(i) instanceof java.util.Date ) {
617                         SimpleDateFormat date_formatter =
618                             new SimpleDateFormat(DB_DATEFORMAT);
619                         String sDate =
620                             date_formatter.format(
621                                 (java.util.Date ) saValues.elementAt(i));
622                         sSql.append(getDateAsSQL(sDate));
623                     } else if (
624                         saValues.elementAt(i) instanceof SelectStatement) {
625                         sSql.append(
626                             getSelectStatement(
627                                 (SelectStatement) saValues.elementAt(i)));
628                     } else if(saValues.elementAt(i) == null) {
629                         sSql.append("null");
630                     } else {
631                         throw new DataStoreException(
632                             "Error creating Insert statement: "
633                                 + sSql.toString() + saValues.elementAt(i));
634                     }
635                 } catch (NullPointerException e) {
636                     throw new DataStoreException(
637                         "Null value in insert for "
638                             + ((ColumnRef) ColRefs.elementAt(i)).getColumn());
639                 }
640             }
641
642             sSql.append(")");
643         }
644
645         return (sSql.toString());
646     }
647
648     /**
649      * Returns the SQL update statement corresponding to the given
650      * <code>UpdateStatement</code>.
651      *
652      * @param update the update statement
653      * @return the SQL statement
654      * @throws DataStoreException if any errors occur
655      */
656     public String getUpdateStatement(UpdateStatement update)
657         throws DataStoreException {
658         boolean bflag = false;
659         Vector saValues = new Vector(16);
660         Vector ColRefs = new Vector(16);
661
662         Map ValuePairs = update.getColumnValuePairs();
663
664         Set set = ValuePairs.keySet();
665
666         Iterator iter = set.iterator();
667
668         while (iter.hasNext()) {
669             ColumnRef colref = (ColumnRef) iter.next();
670             ColRefs.add(colref);
671
672             saValues.add(ValuePairs.get(colref));
673         }
674
675         StringBuffer sSql = new StringBuffer ();
676
677         sSql.append("update ");
678
679         sSql.append(((ColumnRef) ColRefs.elementAt(0)).getTable());
680
681         sSql.append(" set ");
682
683         for (int i = 0; i < ColRefs.size(); i++) {
684             if (bflag) {
685                 sSql.append(",");
686             }
687
688             sSql.append(((ColumnRef) ColRefs.elementAt(i)).getColumn());
689             sSql.append("=");
690
691             if (saValues.elementAt(i) == null) {
692                 sSql.append("null");
693             } else if (saValues.elementAt(i) instanceof String ) {
694                 if(isNationalCharacterSupported()) {
695                     sSql.append("N");
696                 }
697                 sSql.append("'");
698                 sSql.append(addEscapeChars((String ) saValues.elementAt(i)));
699                 sSql.append("'");
700             } else if (saValues.elementAt(i) instanceof Integer ) {
701                 sSql.append(((Integer ) saValues.elementAt(i)).toString());
702             } else if (saValues.elementAt(i) instanceof java.util.Date ) {
703                 SimpleDateFormat date_formatter =
704                     new SimpleDateFormat(DB_DATEFORMAT);
705                 String sDate =
706                     date_formatter.format(
707                         (java.util.Date ) saValues.elementAt(i));
708                 sSql.append(getDateAsSQL(sDate));
709             } else {
710                 throw new DataStoreException(
711                     "Error creating Update statement: " + sSql.toString());
712             }
713
714             bflag = true;
715         }
716
717         sSql.append(" where ");
718
719         sSql.append(generateWhereClause(update));
720
721         return (sSql.toString());
722     }
723
724     /**
725      * Returns the SQL delete statement corresponding to the given
726      * <code>DeleteStatement</code>.
727      *
728      * @param delete the delete statement
729      * @return the SQL statement
730      * @throws DataStoreException if any errors occur
731      */
732     public String getDeleteStatement(DeleteStatement delete)
733         throws DataStoreException {
734         String sTable = delete.getTable();
735
736         if (!delete.hasWhereClause()) {
737             throw new DataStoreException("Delete statements without Where clauses are not allowed.");
738         }
739
740         WhereConditionGroup where = delete.getWhereConditions();
741
742         StringBuffer sSql = new StringBuffer ();
743
744         sSql.append("delete from ");
745         sSql.append(sTable);
746
747         sSql.append(" where");
748         sSql.append(generateWhereClause(delete));
749
750         return (sSql.toString());
751     }
752
753     /**
754      * Returns the SQL select statement corresponding to the given
755      * <code>SelectStatement</code>.
756      *
757      * @param select the select statement
758      * @return the SQL statement
759      * @throws DataStoreException if an error occurs
760      */
761     public String getSelectStatement(SelectStatement select)
762         throws DataStoreException {
763         List SelectCols = select.getSelectColumns();
764         List maxcols = select.getSelectMaxColumns();
765         JoinConditions join = select.getJoinConditions();
766         WhereConditionGroup where = select.getWhereConditions();
767
768         Set orderColSet = select.getOrderByColumns();
769
770         StringBuffer sSql = new StringBuffer ();
771
772         sSql.append("select ");
773
774         if (select.isDistinct()) {
775             sSql.append("distinct ");
776         }
777
778         if (select.isLimit()) {
779             sSql.append("top ");
780             sSql.append(select.getLimit());
781             sSql.append(" ");
782         }
783
784         if ((SelectCols == null) || (SelectCols.size() == 0)) {
785             sSql.append("*");
786         } else {
787             boolean bMax = false;
788
789             for (int i = 0; i < SelectCols.size(); i++) {
790                 if ((maxcols != null) && maxcols.contains(new Integer (i))) {
791                     bMax = true;
792                 }
793
794                 if (i > 0) {
795                     sSql.append(",");
796                 }
797
798                 if (SelectCols.get(i) instanceof Integer ) {
799                     sSql.append((Integer ) SelectCols.get(i));
800                 } else {
801                     if (bMax) {
802                         sSql.append("max(");
803                     }
804
805                     sSql.append(
806                         (String ) ((ColumnRef) SelectCols.get(i)).getFullRef());
807
808                     if (bMax) {
809                         sSql.append(")");
810                     }
811                 }
812             }
813
814             if (orderColSet.isEmpty() == false) {
815                 Iterator iter = orderColSet.iterator();
816                 int i=0;
817                 while (iter.hasNext()) {
818                     ColumnRef ordercol = (ColumnRef) iter.next();
819                     sSql.append(",");
820                     sSql.append(this.getOrderByRef(ordercol));
821                     sSql.append(" AS upperCol").append(i++);
822                 }
823                 
824             }
825         }
826
827         sSql.append(generateFromClause(select));
828
829         sSql.append(generateWhereClause(select));
830
831         if (orderColSet.isEmpty() == false) {
832             sSql.append(generateOrderByClause(select));
833         }
834
835         return (sSql.toString());
836     }
837
838     /**
839      * Returns the SQL where clause for the SQL statement corresponding to
840      * the given <code>SelectStatement</code>, including the necessary
841      * join conditions.
842      *
843      * @param select the select statement
844      * @return the SQL where clause
845      * @throws DataStoreException if an error occurs
846      */
847     protected String generateWhereClause(SelectStatement select)
848         throws DataStoreException {
849         JoinConditions join = select.getJoinConditions();
850
851         boolean bAnd = false;
852
853         if (!select.hasWhereClause() && !select.hasJoinConditions()) {
854             return "";
855         }
856
857         StringBuffer sSql = new StringBuffer ();
858
859         sSql.append(" where");
860
861         if (select.hasJoinConditions()) {
862             for (int i = 0; i < join.size(); i++) {
863                 if (bAnd) {
864                     sSql.append(" and");
865                 }
866
867                 sSql.append(" ");
868
869                 sSql.append(
870                     getJoinCondition(
871                         join.getLeftColumnRef(i),
872                         join.getRightColumnRef(i),
873                         join.isOuterJoin(i)));
874
875                 bAnd = true;
876             }
877         }
878
879         if (select.hasWhereClause()) {
880             if (select.hasJoinConditions()) {
881                 sSql.append(" and ");
882             }
883
884             sSql.append(generateWhereClause((AbstractDMLStatement) select));
885         }
886
887         return (sSql.toString());
888     }
889
890     /**
891      * Returns the SQL where clause for the given <code>AbstractDMLStatement</code>.
892      *
893      *
894      * @param DML the DML statement
895      * @return the SQL where clause
896      * @throws DataStoreException if an error occurs
897      */
898     protected String generateWhereClause(AbstractDMLStatement DML)
899         throws DataStoreException {
900         if (!DML.hasWhereClause()) {
901             return null;
902         }
903
904         WhereConditionGroup where = DML.getWhereConditions();
905
906         return (generateWhereClause(where));
907     }
908
909     /**
910      * Returns the SQL where clause for the given collection of where conditions.
911      *
912      * @param where the collection of where conditions
913      * @return the SQL where clause
914      * @throws DataStoreException if an error occurs
915      */
916     protected String generateWhereClause(WhereConditionGroup where)
917         throws DataStoreException {
918         boolean bAnd = false;
919
920         StringBuffer sSql = new StringBuffer ();
921
922         for (int i = 0; i < where.size(); i++) {
923             if (bAnd) {
924                 sSql.append(" ");
925                 sSql.append(where.getStringingOperator());
926             }
927
928             if (where.isWhereConditionsLeaf(i) == false) {
929                 WhereConditionGroup conds = (WhereConditionGroup) where.getCondition(i);
930                 if (conds.size() > 1) {
931                     sSql.append(" (");
932                 }
933                 sSql.append(generateWhereClause(conds));
934                 if (conds.size() > 1) {
935                     sSql.append(")");
936                 }
937
938             } else {
939
940                 String sCol = where.getFullColumnRef(i);
941
942                 if (where.getCondition(i)
943                     instanceof FunctionedWhereCondition) {
944                     FunctionedWhereCondition funcdWhere =
945                         (FunctionedWhereCondition) where.getCondition(i);
946                     Function func = funcdWhere.getFunction();
947                     sCol = getFunction(func);
948                 }
949
950                 String sOperator = where.getOperator(i);
951                 List Values = where.getValues(i);
952
953                 if (!sOperator.equalsIgnoreCase("NOT IN")
954                     && !sOperator.equalsIgnoreCase("IN")
955                     && !sOperator.equalsIgnoreCase("BETWEEN")
956                     && !sOperator.equalsIgnoreCase("OR")
957                     && !sOperator.equalsIgnoreCase("CONTAINS")) {
958                     
959                     for (int j = 0; j < Values.size(); j++) {
960                         sSql.append(" ");
961                         sSql.append(sCol);
962
963                         if (Values.get(j) == null) {
964                             if (sOperator.equals("=") == true
965                                 || sOperator.equals("is") == true) {
966                                 sSql.append(" is null");
967                             } else if (
968                                 sOperator.equals("!=") == true
969                                     || sOperator.equals("is not") == true) {
970                                 sSql.append(" is not null");
971                             }
972                             continue;
973                         }
974
975                         if (sOperator.equalsIgnoreCase("LIKE")
976                             || sOperator.equalsIgnoreCase("STARTS_WITH")) {
977                             sSql.append(" ");
978                             sSql.append("LIKE");
979                             sSql.append(" ");
980                         } else {
981                             sSql.append(sOperator);
982                         }
983
984                         if (Values.get(j) instanceof SelectStatement) {
985                             SelectStatement query =
986                                 (SelectStatement) Values.get(j);
987                             sSql.append("(");
988                             sSql.append(getSelectStatement(query));
989                             sSql.append(")");
990                         } else if (
991                             Values.get(j) instanceof java.util.Date ) {
992                             SimpleDateFormat date_formatter =
993                                 new SimpleDateFormat(DB_DATEFORMAT);
994                             String sDate =
995                                 date_formatter.format(
996                                     (java.util.Date ) Values.get(j));
997                             sSql.append(getDateAsSQL(sDate));
998                         } else if (Values.get(j) instanceof Function) {
999                             sSql.append(
1000                                getFunction((Function) Values.get(j)));
1001                        } else {
1002                            if ((Values.get(j) instanceof Integer )
1003                                == false) {
1004                                sSql.append("'");
1005                            }
1006
1007                            sSql.append(
1008                                addEscapeChars(Values.get(j).toString()));
1009
1010                            if ((Values.get(j) instanceof Integer )
1011                                == false) {
1012                                if (sOperator.equals("STARTS_WITH")) {
1013                                    sSql.append("%");
1014                                }
1015                                sSql.append("'");
1016                            }
1017                        }
1018                    }
1019                } else if (
1020                    sOperator.equalsIgnoreCase("IN")
1021                        || sOperator.equalsIgnoreCase("NOT IN")) {
1022                    sSql.append(" ");
1023                    sSql.append(sCol);
1024                    sSql.append(" ");
1025                    sSql.append(sOperator);
1026                    sSql.append(" (");
1027
1028                    for (int j = 0; j < Values.size(); j++) {
1029                        if (j != 0) {
1030                            sSql.append(",");
1031                        }
1032
1033                        if (Values.get(j) instanceof SelectStatement) {
1034                            SelectStatement query =
1035                                (SelectStatement) Values.get(j);
1036
1037                            sSql.append(getSelectStatement(query));
1038                        } else if (
1039                            Values.get(j) instanceof java.util.Date ) {
1040                            SimpleDateFormat date_formatter =
1041                                new SimpleDateFormat(DB_DATEFORMAT);
1042                            String sDate =
1043                                date_formatter.format(
1044                                    (java.util.Date ) Values.get(j));
1045                            sSql.append(getDateAsSQL(sDate));
1046                        } else {
1047                            if ((Values.get(j) instanceof Integer )
1048                                == false) {
1049                                sSql.append("'");
1050                            }
1051
1052                            sSql.append(
1053                                addEscapeChars(Values.get(j).toString()));
1054
1055                            if ((Values.get(j) instanceof Integer )
1056                                == false) {
1057                                sSql.append("'");
1058                            }
1059                        }
1060                    }
1061
1062                    sSql.append(")");
1063                } else if (sOperator.equalsIgnoreCase("BETWEEN")) {
1064                    sSql.append(" ");
1065                    sSql.append(sCol);
1066                    sSql.append(" ");
1067                    sSql.append(sOperator);
1068                    sSql.append(" ");
1069
1070                    if (Values.get(0) instanceof java.util.Date ) {
1071                        SimpleDateFormat date_formatter =
1072                            new SimpleDateFormat(DB_DATEFORMAT);
1073                        String sDate =
1074                            date_formatter.format(
1075                                (java.util.Date ) Values.get(0));
1076                        sSql.append(getDateAsSQL(sDate));
1077                    } else {
1078                        if ((Values.get(0) instanceof Integer )
1079                            == false) {
1080                            sSql.append("'");
1081                        }
1082
1083                        sSql.append(Values.get(0).toString());
1084
1085                        if ((Values.get(0) instanceof Integer )
1086                            == false) {
1087                            sSql.append("'");
1088                        }
1089                    }
1090
1091                    sSql.append(" AND ");
1092
1093                    if (Values.get(1) instanceof java.util.Date ) {
1094                        SimpleDateFormat date_formatter =
1095                            new SimpleDateFormat(DB_DATEFORMAT);
1096                        String sDate =
1097                            date_formatter.format(
1098                                (java.util.Date ) Values.get(1));
1099                        sSql.append(getDateAsSQL(sDate));
1100                    } else {
1101                        if ((Values.get(1) instanceof Integer )
1102                            == false) {
1103                            sSql.append("'");
1104                        }
1105
1106                        sSql.append(Values.get(1).toString());
1107
1108                        if ((Values.get(1) instanceof Integer )
1109                            == false) {
1110                            sSql.append("'");
1111                        }
1112                    }
1113                } else if (sOperator.equalsIgnoreCase("CONTAINS") == true) {
1114                    if (Values.size() > 1) {
1115                        sSql.append("(");
1116                    }
1117
1118                    for (int j = 0; j < Values.size(); j++) {
1119                        if (j != 0) {
1120                            sSql.append(" OR ");
1121                        }
1122
1123                        sSql.append(" ");
1124                        sSql.append(sCol);
1125                        sSql.append(" ");
1126                        sSql.append("LIKE");
1127                        sSql.append(" ");
1128
1129                        if ((Values.get(j) instanceof Integer )
1130                            == false) {
1131                            sSql.append("'%");
1132                        }
1133
1134                        sSql.append(
1135                            addEscapeChars(Values.get(j).toString()));
1136
1137                        sSql.append("%'");
1138
1139                    }
1140                    if (Values.size() > 1) {
1141                        sSql.append(")");
1142                    }
1143                }
1144            }
1145
1146            bAnd = true;
1147        }
1148
1149        return (sSql.toString());
1150    }
1151
1152    /**
1153     * Returns the SQL function for the given <code>Function</code>.
1154     *
1155     * @param func the function
1156     * @return the SQL function
1157     * @throws DataStoreException if an error occurs
1158     */
1159    abstract protected String getFunction(Function func)
1160        throws DataStoreException;
1161
1162    /**
1163     * Returns the SQL 'from' clause for the given <code>AbstractDMLStatement</code>
1164     * and the list of tables.
1165     *
1166     * @param select the select statement
1167     * @return the SQL 'from' clause
1168     * @throws DataStoreException if an error occurs
1169     */
1170    protected String generateFromClause(
1171        SelectStatement select)
1172        throws DataStoreException {
1173        StringBuffer sSql = new StringBuffer ();
1174        JoinConditions join = select.getJoinConditions();
1175        List SelectCols = select.getSelectColumns();
1176        WhereConditionGroup where = select.getWhereConditions();
1177        Vector saTables = null;
1178        
1179        sSql.append(" from ");
1180
1181        if (select.hasJoinConditions()) {
1182            List jvec = join.getTableList();
1183            saTables = new Vector(jvec);
1184
1185            if (select.hasWhereConditions()) {
1186                List wvec = where.getTableList();
1187
1188                for (int i = 0; i < wvec.size(); i++) {
1189                    if (!saTables.contains(wvec.get(i))) {
1190                        saTables.add(wvec.get(i));
1191                    }
1192                }
1193            }
1194        } else if (SelectCols.size() > 0) {
1195            saTables = new Vector(1);
1196            saTables.add((String ) ((ColumnRef) SelectCols.get(0)).getTable());
1197        } else {
1198            saTables = new Vector(1);
1199            if (select.hasWhereConditions()) {
1200                List wvec = where.getTableList();
1201                saTables.add(wvec.get(0));
1202            }
1203        }
1204
1205        for (int i = 0; i < saTables.size(); i++) {
1206            String sTable = (String ) saTables.elementAt(i);
1207
1208            if (i > 0) {
1209                sSql.append(",");
1210            }
1211
1212            if (select.isAlias(sTable)) {
1213                sSql.append(select.getTableName(sTable));
1214                sSql.append(" ");
1215            }
1216
1217            sSql.append(sTable);
1218        }
1219
1220        return (sSql.toString());
1221    }
1222
1223    /**
1224     * Returns the SQL 'order by' clause for the given <code>SelectStatement</code>.
1225     *
1226     * @param select the select statement
1227     * @return the SQL 'order by' clause
1228     */
1229    protected String generateOrderByClause(SelectStatement select) {
1230        StringBuffer sSql = new StringBuffer ();
1231        Set orderColSet = select.getOrderByColumns();
1232        
1233        Iterator iter = orderColSet.iterator();
1234        int i=0;
1235        
1236        if(iter.hasNext()) {
1237            sSql.append(" order by ");
1238            
1239            while (iter.hasNext()) {
1240                ColumnRef ordercol = (ColumnRef) iter.next();
1241                
1242                int nOrderColType = ordercol.getDataType();
1243                
1244                if ((nOrderColType == ColumnRef.TEXT)
1245                    || (nOrderColType == ColumnRef.LONG_TEXT)) {
1246                    sSql.append("upperCol").append(i++);
1247                } else {
1248                    sSql.append(getOrderByRef(ordercol));
1249                }
1250        
1251                sSql.append(" ");
1252                sSql.append(select.getOrderByDirection(ordercol));
1253                if(iter.hasNext()) {
1254                    sSql.append(",");
1255                }
1256            }
1257        }
1258
1259
1260        return (sSql.toString());
1261    }
1262
1263    /**
1264     * Returns the SQL string to include in to the SQL select statement
1265     * to ensure correct ordering of the result set.
1266     *
1267     * @param ordercol the column reference
1268     * @return the SQL string to include in the SQL select statement
1269     */
1270    protected String getOrderByRef(ColumnRef ordercol) {
1271        StringBuffer sSql = new StringBuffer ();
1272        int nOrderColType = ordercol.getDataType();
1273
1274        if ((nOrderColType == ColumnRef.TEXT)
1275            || (nOrderColType == ColumnRef.LONG_TEXT)) {
1276            sSql.append("upper(");
1277        }
1278
1279        if (nOrderColType == ColumnRef.LONG_TEXT) {
1280            sSql.append("convert(char(80),");
1281        }
1282
1283        sSql.append(ordercol.getFullRef());
1284
1285        if (nOrderColType == ColumnRef.LONG_TEXT) {
1286            sSql.append(")");
1287        }
1288
1289        if ((nOrderColType == ColumnRef.TEXT)
1290            || (nOrderColType == ColumnRef.LONG_TEXT)) {
1291            sSql.append(")");
1292        }
1293
1294        return sSql.toString();
1295    }
1296
1297    /**
1298     * Returns the given date formatted for use in a SQL statement.
1299     *
1300     * @param date the date as a string
1301     * @return the SQL date representation
1302     */
1303    abstract protected String getDateAsSQL(String date);
1304
1305    /**
1306     * Returns the given <code>String</code> as a <code>Date</code>,
1307     * using the default date format for parsing.
1308     *
1309     * @param sDate the string representation for the date
1310     * @return the date object
1311     * @throws ParseException if a parse error occurs
1312     */
1313    public static java.util.Date parseDate(String sDate) throws ParseException {
1314        java.util.Date tempDate = new java.util.Date ();
1315
1316        SimpleDateFormat dFormat =
1317            new SimpleDateFormat(AbstractDataStoreInterface.DB_OUT_DATEFORMAT);
1318
1319        java.util.Date newDate = dFormat.parse(sDate);
1320
1321        return newDate;
1322    }
1323
1324    /**
1325     * Closes the connection to the database.
1326     *
1327     * @param conn the connection
1328     */
1329    public void closeConnection(Connection conn) {
1330        try {
1331            if (m_connectionType == POOLED_CONNECTION) {
1332                conn.close();
1333            } else if (m_connectionType == DB_CONNECTION_BROKER) {
1334                DBConnectionPooler
1335                    .getInstance(m_jdbc_driver, m_db_url, m_db_usr, m_db_pwd)
1336                    .freeConnection(conn);
1337            }
1338        } catch (Exception e) {
1339            throw new RuntimeException (e.getMessage());
1340        }
1341    }
1342
1343    /**
1344     * Returns the SQL date datatype for this data store interface.
1345     *
1346     * @return the SQL date datatype
1347     */
1348    abstract public String getDateDataType();
1349
1350    /**
1351     * Returns the SQL CLOB datatype for this data store interface.
1352     *
1353     * @return the SQL CLOB datatype
1354     */
1355    abstract public String getCLOBDataType();
1356
1357    /**
1358     * Returns the boolean datatype for this data store interface.
1359     *
1360     * @return the boolean datatype for this data store interface
1361     */
1362    abstract public String getBooleanDataType();
1363
1364    /**
1365     * Returns the list of tables contained in the database.
1366     *
1367     * @return the list of tables contained in the database
1368     * @throws DataStoreException if an error occurs
1369     */
1370    abstract public List getTableList() throws DataStoreException;
1371
1372    /**
1373     * Returns <code>true</code> of the specified table exists.
1374     *
1375     * @param sTableName the table name
1376     * @return <code>true</code> of the specified table exists
1377     * @throws DataStoreException if an error occurs
1378     */
1379    public boolean isTableExist(String sTableName) throws DataStoreException {
1380        return getTableList().contains(sTableName);
1381    }
1382    
1383    /**
1384     * Returns the list of sequences available in the database.
1385     *
1386     * @return the list of sequences available in the database
1387     * @throws DataStoreException
1388     */
1389    abstract public List getSequenceList() throws DataStoreException;
1390
1391    /**
1392     * Returns the SQL join condition statement joining the two given
1393     * column references, taking in to account whether the join should be
1394     * an outer join.
1395     *
1396     * @param ref1 the column reference on the left side of the join
1397     * @param ref2 the column reference on the right side of the join
1398     * @param bIsOuter <code>true</code> if the join is an outer join
1399     * @return the SQL join condition
1400     */
1401    abstract public String getJoinCondition(
1402        ColumnRef ref1,
1403        ColumnRef ref2,
1404        boolean bIsOuter);
1405    
1406    /**
1407     * Returns <code>true</code> if the DB supports the F421 SQL
1408     * National Character feature
1409     *
1410     * @return <code>true</code> if the DB supports the F421 SQL
1411     * National Character feature
1412     */
1413    protected boolean isNationalCharacterSupported() {
1414        return m_bIsNationalCharacterSupported;
1415    }
1416    
1417    /**
1418     * Sets whether the DB supports the F421 SQL
1419     * National Character feature
1420     *
1421     * @param bIsSupported <code>true</code> if the DB supports the F421 SQL
1422     * National Character feature
1423     */
1424    protected void setNationalCharacterSupport(boolean bIsSupported) {
1425        m_bIsNationalCharacterSupported = bIsSupported;
1426    }
1427}

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags