0001: /*
0002: * The contents of this file are subject to the
0003: * Mozilla Public License Version 1.1 (the "License");
0004: * you may not use this file except in compliance with the License.
0005: * You may obtain a copy of the License at http://www.mozilla.org/MPL/
0006: *
0007: * Software distributed under the License is distributed on an "AS IS"
0008: * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied.
0009: * See the License for the specific language governing rights and
0010: * limitations under the License.
0011: *
0012: * The Initial Developer of the Original Code is Simulacra Media Ltd.
0013: * Portions created by Simulacra Media Ltd are Copyright (C) Simulacra Media Ltd, 2004.
0014: *
0015: * All Rights Reserved.
0016: *
0017: * Contributor(s):
0018: */
0019: package org.openharmonise.commons.dsi;
0020:
0021: import java.sql.*;
0022:
0023: import java.text.*;
0024:
0025: import java.util.*;
0026: import java.util.logging.*;
0027: import java.util.logging.Logger;
0028:
0029: import org.openharmonise.commons.dsi.ddl.*;
0030: import org.openharmonise.commons.dsi.dml.*;
0031:
0032: /**
0033: * Class providing interface to the database.
0034: * Contains general purpose functions that run queries
0035: * and extract cached info such as the data schema and
0036: * language-dependent text.
0037: *
0038: * @author Michael Bell
0039: *
0040: * @see java.sql
0041: * @see oracle.sql
0042: */
0043: public abstract class AbstractDataStoreInterface extends Object {
0044:
0045: /**
0046: * Constant indicating a pooled connection
0047: */
0048: public static final int POOLED_CONNECTION = 0;
0049:
0050: /**
0051: * Constant indicating a non-pooled connection
0052: */
0053: public static final int OTHER_CONNECTION = 1;
0054:
0055: /**
0056: * Consant indication a db connection broker connection
0057: */
0058: public static final int DB_CONNECTION_BROKER = 2;
0059:
0060: /**
0061: * Date format for database
0062: */
0063: protected static String DB_DATEFORMAT = "MM-dd-yyyy HH:mm:ss.SSS";
0064:
0065: /**
0066: * Date format for output
0067: */
0068: private static final String DB_OUT_DATEFORMAT = "MMM dd yyyy hh:mma";
0069:
0070: /**
0071: * JDBC driver class name
0072: */
0073: private String m_jdbc_driver = null;
0074:
0075: /**
0076: * Database URI
0077: */
0078: private String m_db_url = null;
0079:
0080: /**
0081: * Database user name
0082: */
0083: private String m_db_usr = null;
0084:
0085: /**
0086: * Database user password
0087: */
0088: private String m_db_pwd = null;
0089:
0090: /**
0091: * Database connection.
0092: */
0093: private Connection m_connection = null;
0094:
0095: /**
0096: * Database connection type
0097: */
0098: private int m_connectionType = 2;
0099:
0100: /**
0101: * Boolean flag to indicate whether the DB supports the F421 National Character feature
0102: */
0103: static protected boolean m_bIsNationalCharacterSupported = false;
0104:
0105: /**
0106: * Logger for this class
0107: */
0108: private static final Logger m_logger = Logger
0109: .getLogger(AbstractDataStoreInterface.class.getName());
0110:
0111: /**
0112: * Constructor an simple data store interface with no details set
0113: */
0114: public AbstractDataStoreInterface() {
0115: }
0116:
0117: /**
0118: * Constructs a data store interface with the given connection parameters.
0119: *
0120: * @param sJDBCDriver the JDBC driver class name
0121: * @param sDBurl the database URI
0122: * @param sDBUsr the database user name
0123: * @param sDBPwd the database user password
0124: */
0125: public AbstractDataStoreInterface(String sJDBCDriver,
0126: String sDBurl, String sDBUsr, String sDBPwd) {
0127: m_jdbc_driver = sJDBCDriver;
0128: m_db_url = sDBurl;
0129: m_db_usr = sDBUsr;
0130: m_db_pwd = sDBPwd;
0131: }
0132:
0133: /**
0134: * Sets the connection details for this data store interface.
0135: *
0136: * @param sJDBCDriver the JDBC driver class name
0137: * @param sDBurl the database URI
0138: * @param sDBUsr the database user name
0139: * @param sDBPwd the database user password
0140: */
0141: public void setDataStoreDetails(String sJDBCDriver, String sDBurl,
0142: String sDBUsr, String sDBPwd) {
0143: m_jdbc_driver = sJDBCDriver;
0144: m_db_url = sDBurl;
0145: m_db_usr = sDBUsr;
0146: m_db_pwd = sDBPwd;
0147: }
0148:
0149: /**
0150: * Constructs a data store interface with the specified connection type.
0151: *
0152: * @param nConnectionType the connection type
0153: * @throws Exception if the specified connection type is invalid
0154: */
0155: public AbstractDataStoreInterface(int nConnectionType)
0156: throws DataStoreException {
0157: if ((nConnectionType != POOLED_CONNECTION)
0158: && (nConnectionType != OTHER_CONNECTION)
0159: && (nConnectionType != DB_CONNECTION_BROKER)) {
0160: throw new DataStoreException("Invalid connection type");
0161: } else {
0162: m_connectionType = nConnectionType;
0163: }
0164: }
0165:
0166: /**
0167: * Initialise this data store interface with the specified connection type.
0168: *
0169: * @param nConnectionType the connection type
0170: * @throws Exception if the specified connection type is invalid
0171: */
0172: public void initialise(int nConnectionType) throws Exception {
0173: if ((nConnectionType != POOLED_CONNECTION)
0174: && (nConnectionType != OTHER_CONNECTION)
0175: && (nConnectionType != DB_CONNECTION_BROKER)) {
0176: throw new Exception("Invalid connection type");
0177: } else {
0178: m_connectionType = nConnectionType;
0179: }
0180: }
0181:
0182: /**
0183: * Disconnects from database.
0184: *
0185: * @exception SQLException if an error occurs closing the connection
0186: */
0187: public void disconnect() throws SQLException {
0188: if (m_connection != null) {
0189: m_connection.close();
0190: m_connection = null;
0191: }
0192: }
0193:
0194: /**
0195: * Returns <code>true</code> if whether this data store interface uses a pooled connection.
0196: *
0197: * @return <code>true</code> if whether this data store interface uses a pooled connection.
0198: */
0199: public boolean isPooledConnection() {
0200: if ((m_connectionType == POOLED_CONNECTION)
0201: || (m_connectionType == DB_CONNECTION_BROKER)) {
0202: return true;
0203: } else {
0204: return false;
0205: }
0206: }
0207:
0208: /**
0209: * Returns the database connection.
0210: *
0211: * @return the database connection.
0212: * @throws DataStoreException if a connection can not be created
0213: */
0214: public Connection getConnection() throws DataStoreException {
0215: Connection conn = null;
0216:
0217: //check details first
0218: if (this .m_jdbc_driver == null || this .m_db_url == null
0219: || this .m_db_usr == null || this .m_db_pwd == null) {
0220: throw new DataStoreException(
0221: "Don't have enough config details to get connection");
0222: }
0223:
0224: try {
0225: if (m_connectionType == OTHER_CONNECTION) {
0226: if (m_connection == null) {
0227: Class.forName(m_jdbc_driver);
0228: m_connection = DriverManager.getConnection(
0229: m_db_url, m_db_usr, m_db_pwd);
0230: }
0231:
0232: conn = m_connection;
0233: } else if (m_connectionType == DB_CONNECTION_BROKER) {
0234:
0235: conn = DBConnectionPooler.getInstance(m_jdbc_driver,
0236: m_db_url, m_db_usr, m_db_pwd).getConnection();
0237: }
0238: } catch (Exception e) {
0239: m_logger.log(Level.WARNING, e.getLocalizedMessage(), e);
0240: throw new DataStoreException(e.getMessage());
0241: }
0242:
0243: return conn;
0244: }
0245:
0246: /**
0247: * Returns the URL of the database.
0248: *
0249: * @return the URL of the database
0250: */
0251: public String getDatabaseURL() {
0252: return m_db_url;
0253: }
0254:
0255: /**
0256: * Returns the next value in the specified sequence.
0257: *
0258: * @param sSeqName name of sequence
0259: * @return the next value in the specified sequence.
0260: * @throws DataStoreException if there is an error building the query
0261: * at the data store interface level
0262: * @throws SQLException if there is an error at the jdbc level
0263: */
0264: public abstract int getSequenceNextValue(String sSeqName)
0265: throws DataStoreException, SQLException;
0266:
0267: /**
0268: * Inserts CLOB data into database.
0269: *
0270: * @param sTable the table name
0271: * @param sColumn the column name
0272: * @param sClob the CLOB text
0273: * @param sCondition the condition attached to the insert statement
0274: * @throws DataStoreException if an error occurs
0275: */
0276: public abstract void insertClob(String sTable, String sColumn,
0277: String sClob, String sCondition) throws DataStoreException;
0278:
0279: /**
0280: * Updates CLOB text in the database.
0281: *
0282: * @param sTable the table name
0283: * @param sColumn the column name
0284: * @param sClob the clob text
0285: * @param sCondition the condition for locating the fields to update
0286: * @throws DataStoreException if an error occurs
0287: */
0288: public abstract void updateClob(String sTable, String sColumn,
0289: String sClob, String sCondition) throws DataStoreException;
0290:
0291: /**
0292: * Returns the text held in a CLOB field in the database.
0293: *
0294: * @param sTable the table name
0295: * @param sColumn the colmn name
0296: * @param sCondition the condition for location of the CLOB
0297: * @return
0298: * @throws DataStoreException
0299: */
0300: public abstract String getClob(String sTable, String sColumn,
0301: String sCondition) throws DataStoreException;
0302:
0303: /**
0304: * Executes SQL query specified by <code>sSql</code> on the database.
0305: *
0306: * @param sSql SQL Query
0307: * @exception DataStoreException if an error occurs
0308: */
0309: public void execute(String sSql) throws DataStoreException {
0310: Connection conn = null;
0311: Statement stmt = null;
0312:
0313: try {
0314: conn = getConnection();
0315: stmt = conn.createStatement();
0316:
0317: stmt.setEscapeProcessing(true);
0318:
0319: try {
0320: stmt.execute(sSql);
0321: } catch (SQLException e) {
0322: throw new SQLException(e.getMessage() + sSql);
0323: }
0324:
0325: if (stmt != null) {
0326: stmt.close();
0327: }
0328:
0329: if (isPooledConnection() && (conn != null)) {
0330: this .closeConnection(conn);
0331: }
0332: } catch (SQLException e) {
0333: m_logger.log(Level.WARNING, "SQL:" + sSql, e);
0334: throw new DataStoreException("SQLException: "
0335: + e.getMessage());
0336: }
0337: }
0338:
0339: /**
0340: * Executes a specified SQL update statement on the database.
0341: *
0342: * @param sSql SQL update statement
0343: * @return a code indicating success
0344: * @exception DataStoreException
0345: */
0346: public int executeUpdate(String sSql) throws DataStoreException {
0347: Connection conn = null;
0348: Statement stmt = null;
0349: int nReturn = -1;
0350:
0351: try {
0352: conn = getConnection();
0353: stmt = conn.createStatement();
0354:
0355: stmt.setEscapeProcessing(true);
0356:
0357: try {
0358: nReturn = stmt.executeUpdate(sSql);
0359: } catch (SQLException e) {
0360: throw new SQLException(e.getMessage() + sSql);
0361: }
0362:
0363: if (stmt != null) {
0364: stmt.close();
0365: }
0366:
0367: if ((isPooledConnection() == true) && (conn != null)) {
0368: this .closeConnection(conn);
0369: }
0370: } catch (SQLException e) {
0371: throw new DataStoreException("SQLException: "
0372: + e.getMessage());
0373: }
0374:
0375: return nReturn;
0376: }
0377:
0378: /**
0379: * Executes a specified SQL query on the database.
0380: *
0381: * @param sSql the SQL Query
0382: * @return the result of the query
0383: * @exception DataStoreException
0384: */
0385: public ResultSet executeQuery(String sSql)
0386: throws DataStoreException {
0387: Connection conn = null;
0388: Statement stmt = null;
0389: ResultSet rs = null;
0390:
0391: try {
0392: conn = getConnection();
0393: stmt = conn.createStatement();
0394:
0395: stmt.setEscapeProcessing(true);
0396:
0397: if (m_logger.isLoggable(Level.FINEST)) {
0398: m_logger.logp(Level.FINEST, this .getClass().getName(),
0399: "executeQuery", sSql);
0400: }
0401:
0402: rs = stmt.executeQuery(sSql);
0403:
0404: if (isPooledConnection() && (conn != null)) {
0405: this .closeConnection(conn);
0406: }
0407:
0408: rs = new HarmoniseResultSet(stmt, rs);
0409: } catch (SQLException e) {
0410: m_logger.log(Level.WARNING, "Error running SQL - " + sSql,
0411: e);
0412: throw new DataStoreException("SQLException: "
0413: + e.getMessage() + " " + sSql);
0414: }
0415:
0416: return rs;
0417: }
0418:
0419: /**
0420: * Executes the given select statement on the database.
0421: *
0422: * @param query the select statement
0423: * @return the resultant result set
0424: * @throws DataStoreException if an error occurs
0425: */
0426: public ResultSet executeQuery(SelectStatement query)
0427: throws DataStoreException {
0428: String sSql = getSelectStatement(query);
0429:
0430: return (executeQuery(sSql));
0431: }
0432:
0433: /**
0434: * Executes the given select statement on the database.
0435: *
0436: * @param query the select statement
0437: * @return the resultant result set
0438: * @throws DataStoreException if an error occurs
0439: */
0440: public ResultSet execute(SelectStatement query)
0441: throws DataStoreException {
0442: String sSql = getSelectStatement(query);
0443:
0444: return (executeQuery(sSql));
0445: }
0446:
0447: /**
0448: * Executes the given update statement on the database.
0449: *
0450: * @param update the update statement
0451: * @return a success code
0452: * @throws DataStoreException if an error occurs
0453: */
0454: public int executeUpdate(UpdateStatement update)
0455: throws DataStoreException {
0456: String sSql = getUpdateStatement(update);
0457:
0458: return (executeUpdate(sSql));
0459: }
0460:
0461: /**
0462: * Executes the given update statement on the database.
0463: *
0464: * @param update the update statement
0465: * @return a success code
0466: * @throws DataStoreException if an error occurs
0467: */
0468: public int execute(UpdateStatement update)
0469: throws DataStoreException {
0470: String sSql = getUpdateStatement(update);
0471:
0472: return (executeUpdate(sSql));
0473: }
0474:
0475: /**
0476: * Executes the given insert statement on the database.
0477: *
0478: * @param insert the insert statement
0479: * @throws DataStoreException if an error occurs
0480: */
0481: public void executeInsert(InsertStatement insert)
0482: throws DataStoreException {
0483: String sSql = getInsertStatement(insert);
0484:
0485: execute(sSql);
0486: }
0487:
0488: /**
0489: * Executes the given insert statement on the database.
0490: *
0491: * @param insert the insert statement
0492: * @throws DataStoreException if an error occurs
0493: */
0494: public void execute(InsertStatement insert)
0495: throws DataStoreException {
0496: String sSql = getInsertStatement(insert);
0497:
0498: execute(sSql);
0499: }
0500:
0501: /**
0502: * Creates a new database table from the given table definition.
0503: *
0504: * @param tblDef the definition of the table to create
0505: * @throws DataStoreException if an error occurs creating the new table
0506: */
0507: abstract public void createTable(TableDefinition tblDef)
0508: throws DataStoreException;
0509:
0510: /**
0511: * Executes the given delete statement on the database.
0512: *
0513: * @param delete the delete statement
0514: * @throws DataStoreException if an error occurs
0515: */
0516: public void execute(DeleteStatement delete)
0517: throws DataStoreException {
0518: String sSql = getDeleteStatement(delete);
0519:
0520: execute(sSql);
0521: }
0522:
0523: /**
0524: * Returns an escaped string which can be entered in to the database without
0525: * 'special' characters causing a problem.
0526: *
0527: * @param sOldString contains the string to be checked
0528: *
0529: * @return the <code> String </code> of the new text, including any changes made if they were necessary
0530: */
0531: protected abstract String addEscapeChars(String sOldString);
0532:
0533: /**
0534: * Returns the SQL statement corresponding to the given
0535: * <code>InsertStatement</code>.
0536: *
0537: * @param insert the insert statement
0538: * @return the SQL statement
0539: * @throws DataStoreException if any arrors occur
0540: */
0541: public String getInsertStatement(InsertStatement insert)
0542: throws DataStoreException {
0543: boolean bflag = false;
0544: Vector saValues = new Vector(16);
0545: Vector ColRefs = new Vector(16);
0546:
0547: Map ValuePairs = insert.getColumnValuePairs();
0548:
0549: Set set = ValuePairs.keySet();
0550:
0551: Iterator iter = set.iterator();
0552:
0553: while (iter.hasNext()) {
0554: ColumnRef colref = (ColumnRef) iter.next();
0555: ColRefs.add(colref);
0556:
0557: saValues.add(ValuePairs.get(colref));
0558: }
0559:
0560: StringBuffer sSql = new StringBuffer();
0561:
0562: sSql.append("insert into ");
0563:
0564: sSql.append(((ColumnRef) ColRefs.elementAt(0)).getTable());
0565:
0566: if (insert.isColumnValuesBySelect()) {
0567: sSql.append(" ");
0568: sSql.append(getSelectStatement(insert
0569: .getColumnValuesSelect()));
0570: } else {
0571: sSql.append(" (");
0572:
0573: for (int i = 0; i < ColRefs.size(); i++) {
0574: if (bflag) {
0575: sSql.append(",");
0576: }
0577:
0578: sSql.append(((ColumnRef) ColRefs.elementAt(i))
0579: .getColumn());
0580: bflag = true;
0581: }
0582:
0583: sSql.append(") values (");
0584:
0585: for (int i = 0; i < saValues.size(); i++) {
0586: if (i > 0) {
0587: sSql.append(",");
0588: }
0589: try {
0590:
0591: if (saValues.elementAt(i) instanceof String) {
0592: if (isNationalCharacterSupported()) {
0593: sSql.append("N");
0594: }
0595: sSql.append("'");
0596: sSql.append(addEscapeChars((String) saValues
0597: .elementAt(i)));
0598: sSql.append("'");
0599: } else if (saValues.elementAt(i) instanceof Integer) {
0600: sSql.append((Integer) saValues.elementAt(i));
0601: } else if (saValues.elementAt(i) instanceof java.util.Date) {
0602: SimpleDateFormat date_formatter = new SimpleDateFormat(
0603: DB_DATEFORMAT);
0604: String sDate = date_formatter
0605: .format((java.util.Date) saValues
0606: .elementAt(i));
0607: sSql.append(getDateAsSQL(sDate));
0608: } else if (saValues.elementAt(i) instanceof SelectStatement) {
0609: sSql
0610: .append(getSelectStatement((SelectStatement) saValues
0611: .elementAt(i)));
0612: } else if (saValues.elementAt(i) == null) {
0613: sSql.append("null");
0614: } else {
0615: throw new DataStoreException(
0616: "Error creating Insert statement: "
0617: + sSql.toString()
0618: + saValues.elementAt(i));
0619: }
0620: } catch (NullPointerException e) {
0621: throw new DataStoreException(
0622: "Null value in insert for "
0623: + ((ColumnRef) ColRefs.elementAt(i))
0624: .getColumn());
0625: }
0626: }
0627:
0628: sSql.append(")");
0629: }
0630:
0631: return (sSql.toString());
0632: }
0633:
0634: /**
0635: * Returns the SQL update statement corresponding to the given
0636: * <code>UpdateStatement</code>.
0637: *
0638: * @param update the update statement
0639: * @return the SQL statement
0640: * @throws DataStoreException if any errors occur
0641: */
0642: public String getUpdateStatement(UpdateStatement update)
0643: throws DataStoreException {
0644: boolean bflag = false;
0645: Vector saValues = new Vector(16);
0646: Vector ColRefs = new Vector(16);
0647:
0648: Map ValuePairs = update.getColumnValuePairs();
0649:
0650: Set set = ValuePairs.keySet();
0651:
0652: Iterator iter = set.iterator();
0653:
0654: while (iter.hasNext()) {
0655: ColumnRef colref = (ColumnRef) iter.next();
0656: ColRefs.add(colref);
0657:
0658: saValues.add(ValuePairs.get(colref));
0659: }
0660:
0661: StringBuffer sSql = new StringBuffer();
0662:
0663: sSql.append("update ");
0664:
0665: sSql.append(((ColumnRef) ColRefs.elementAt(0)).getTable());
0666:
0667: sSql.append(" set ");
0668:
0669: for (int i = 0; i < ColRefs.size(); i++) {
0670: if (bflag) {
0671: sSql.append(",");
0672: }
0673:
0674: sSql.append(((ColumnRef) ColRefs.elementAt(i)).getColumn());
0675: sSql.append("=");
0676:
0677: if (saValues.elementAt(i) == null) {
0678: sSql.append("null");
0679: } else if (saValues.elementAt(i) instanceof String) {
0680: if (isNationalCharacterSupported()) {
0681: sSql.append("N");
0682: }
0683: sSql.append("'");
0684: sSql.append(addEscapeChars((String) saValues
0685: .elementAt(i)));
0686: sSql.append("'");
0687: } else if (saValues.elementAt(i) instanceof Integer) {
0688: sSql.append(((Integer) saValues.elementAt(i))
0689: .toString());
0690: } else if (saValues.elementAt(i) instanceof java.util.Date) {
0691: SimpleDateFormat date_formatter = new SimpleDateFormat(
0692: DB_DATEFORMAT);
0693: String sDate = date_formatter
0694: .format((java.util.Date) saValues.elementAt(i));
0695: sSql.append(getDateAsSQL(sDate));
0696: } else {
0697: throw new DataStoreException(
0698: "Error creating Update statement: "
0699: + sSql.toString());
0700: }
0701:
0702: bflag = true;
0703: }
0704:
0705: sSql.append(" where ");
0706:
0707: sSql.append(generateWhereClause(update));
0708:
0709: return (sSql.toString());
0710: }
0711:
0712: /**
0713: * Returns the SQL delete statement corresponding to the given
0714: * <code>DeleteStatement</code>.
0715: *
0716: * @param delete the delete statement
0717: * @return the SQL statement
0718: * @throws DataStoreException if any errors occur
0719: */
0720: public String getDeleteStatement(DeleteStatement delete)
0721: throws DataStoreException {
0722: String sTable = delete.getTable();
0723:
0724: if (!delete.hasWhereClause()) {
0725: throw new DataStoreException(
0726: "Delete statements without Where clauses are not allowed.");
0727: }
0728:
0729: WhereConditionGroup where = delete.getWhereConditions();
0730:
0731: StringBuffer sSql = new StringBuffer();
0732:
0733: sSql.append("delete from ");
0734: sSql.append(sTable);
0735:
0736: sSql.append(" where");
0737: sSql.append(generateWhereClause(delete));
0738:
0739: return (sSql.toString());
0740: }
0741:
0742: /**
0743: * Returns the SQL select statement corresponding to the given
0744: * <code>SelectStatement</code>.
0745: *
0746: * @param select the select statement
0747: * @return the SQL statement
0748: * @throws DataStoreException if an error occurs
0749: */
0750: public String getSelectStatement(SelectStatement select)
0751: throws DataStoreException {
0752: List SelectCols = select.getSelectColumns();
0753: List maxcols = select.getSelectMaxColumns();
0754: JoinConditions join = select.getJoinConditions();
0755: WhereConditionGroup where = select.getWhereConditions();
0756:
0757: Set orderColSet = select.getOrderByColumns();
0758:
0759: StringBuffer sSql = new StringBuffer();
0760:
0761: sSql.append("select ");
0762:
0763: if (select.isDistinct()) {
0764: sSql.append("distinct ");
0765: }
0766:
0767: if (select.isLimit()) {
0768: sSql.append("top ");
0769: sSql.append(select.getLimit());
0770: sSql.append(" ");
0771: }
0772:
0773: if ((SelectCols == null) || (SelectCols.size() == 0)) {
0774: sSql.append("*");
0775: } else {
0776: boolean bMax = false;
0777:
0778: for (int i = 0; i < SelectCols.size(); i++) {
0779: if ((maxcols != null)
0780: && maxcols.contains(new Integer(i))) {
0781: bMax = true;
0782: }
0783:
0784: if (i > 0) {
0785: sSql.append(",");
0786: }
0787:
0788: if (SelectCols.get(i) instanceof Integer) {
0789: sSql.append((Integer) SelectCols.get(i));
0790: } else {
0791: if (bMax) {
0792: sSql.append("max(");
0793: }
0794:
0795: sSql
0796: .append((String) ((ColumnRef) SelectCols
0797: .get(i)).getFullRef());
0798:
0799: if (bMax) {
0800: sSql.append(")");
0801: }
0802: }
0803: }
0804:
0805: if (orderColSet.isEmpty() == false) {
0806: Iterator iter = orderColSet.iterator();
0807: int i = 0;
0808: while (iter.hasNext()) {
0809: ColumnRef ordercol = (ColumnRef) iter.next();
0810: sSql.append(",");
0811: sSql.append(this .getOrderByRef(ordercol));
0812: sSql.append(" AS upperCol").append(i++);
0813: }
0814:
0815: }
0816: }
0817:
0818: sSql.append(generateFromClause(select));
0819:
0820: sSql.append(generateWhereClause(select));
0821:
0822: if (orderColSet.isEmpty() == false) {
0823: sSql.append(generateOrderByClause(select));
0824: }
0825:
0826: return (sSql.toString());
0827: }
0828:
0829: /**
0830: * Returns the SQL where clause for the SQL statement corresponding to
0831: * the given <code>SelectStatement</code>, including the necessary
0832: * join conditions.
0833: *
0834: * @param select the select statement
0835: * @return the SQL where clause
0836: * @throws DataStoreException if an error occurs
0837: */
0838: protected String generateWhereClause(SelectStatement select)
0839: throws DataStoreException {
0840: JoinConditions join = select.getJoinConditions();
0841:
0842: boolean bAnd = false;
0843:
0844: if (!select.hasWhereClause() && !select.hasJoinConditions()) {
0845: return "";
0846: }
0847:
0848: StringBuffer sSql = new StringBuffer();
0849:
0850: sSql.append(" where");
0851:
0852: if (select.hasJoinConditions()) {
0853: for (int i = 0; i < join.size(); i++) {
0854: if (bAnd) {
0855: sSql.append(" and");
0856: }
0857:
0858: sSql.append(" ");
0859:
0860: sSql
0861: .append(getJoinCondition(join
0862: .getLeftColumnRef(i), join
0863: .getRightColumnRef(i), join
0864: .isOuterJoin(i)));
0865:
0866: bAnd = true;
0867: }
0868: }
0869:
0870: if (select.hasWhereClause()) {
0871: if (select.hasJoinConditions()) {
0872: sSql.append(" and ");
0873: }
0874:
0875: sSql
0876: .append(generateWhereClause((AbstractDMLStatement) select));
0877: }
0878:
0879: return (sSql.toString());
0880: }
0881:
0882: /**
0883: * Returns the SQL where clause for the given <code>AbstractDMLStatement</code>.
0884: *
0885: *
0886: * @param DML the DML statement
0887: * @return the SQL where clause
0888: * @throws DataStoreException if an error occurs
0889: */
0890: protected String generateWhereClause(AbstractDMLStatement DML)
0891: throws DataStoreException {
0892: if (!DML.hasWhereClause()) {
0893: return null;
0894: }
0895:
0896: WhereConditionGroup where = DML.getWhereConditions();
0897:
0898: return (generateWhereClause(where));
0899: }
0900:
0901: /**
0902: * Returns the SQL where clause for the given collection of where conditions.
0903: *
0904: * @param where the collection of where conditions
0905: * @return the SQL where clause
0906: * @throws DataStoreException if an error occurs
0907: */
0908: protected String generateWhereClause(WhereConditionGroup where)
0909: throws DataStoreException {
0910: boolean bAnd = false;
0911:
0912: StringBuffer sSql = new StringBuffer();
0913:
0914: for (int i = 0; i < where.size(); i++) {
0915: if (bAnd) {
0916: sSql.append(" ");
0917: sSql.append(where.getStringingOperator());
0918: }
0919:
0920: if (where.isWhereConditionsLeaf(i) == false) {
0921: WhereConditionGroup conds = (WhereConditionGroup) where
0922: .getCondition(i);
0923: if (conds.size() > 1) {
0924: sSql.append(" (");
0925: }
0926: sSql.append(generateWhereClause(conds));
0927: if (conds.size() > 1) {
0928: sSql.append(")");
0929: }
0930:
0931: } else {
0932:
0933: String sCol = where.getFullColumnRef(i);
0934:
0935: if (where.getCondition(i) instanceof FunctionedWhereCondition) {
0936: FunctionedWhereCondition funcdWhere = (FunctionedWhereCondition) where
0937: .getCondition(i);
0938: Function func = funcdWhere.getFunction();
0939: sCol = getFunction(func);
0940: }
0941:
0942: String sOperator = where.getOperator(i);
0943: List Values = where.getValues(i);
0944:
0945: if (!sOperator.equalsIgnoreCase("NOT IN")
0946: && !sOperator.equalsIgnoreCase("IN")
0947: && !sOperator.equalsIgnoreCase("BETWEEN")
0948: && !sOperator.equalsIgnoreCase("OR")
0949: && !sOperator.equalsIgnoreCase("CONTAINS")) {
0950:
0951: for (int j = 0; j < Values.size(); j++) {
0952: sSql.append(" ");
0953: sSql.append(sCol);
0954:
0955: if (Values.get(j) == null) {
0956: if (sOperator.equals("=") == true
0957: || sOperator.equals("is") == true) {
0958: sSql.append(" is null");
0959: } else if (sOperator.equals("!=") == true
0960: || sOperator.equals("is not") == true) {
0961: sSql.append(" is not null");
0962: }
0963: continue;
0964: }
0965:
0966: if (sOperator.equalsIgnoreCase("LIKE")
0967: || sOperator
0968: .equalsIgnoreCase("STARTS_WITH")) {
0969: sSql.append(" ");
0970: sSql.append("LIKE");
0971: sSql.append(" ");
0972: } else {
0973: sSql.append(sOperator);
0974: }
0975:
0976: if (Values.get(j) instanceof SelectStatement) {
0977: SelectStatement query = (SelectStatement) Values
0978: .get(j);
0979: sSql.append("(");
0980: sSql.append(getSelectStatement(query));
0981: sSql.append(")");
0982: } else if (Values.get(j) instanceof java.util.Date) {
0983: SimpleDateFormat date_formatter = new SimpleDateFormat(
0984: DB_DATEFORMAT);
0985: String sDate = date_formatter
0986: .format((java.util.Date) Values
0987: .get(j));
0988: sSql.append(getDateAsSQL(sDate));
0989: } else if (Values.get(j) instanceof Function) {
0990: sSql.append(getFunction((Function) Values
0991: .get(j)));
0992: } else {
0993: if ((Values.get(j) instanceof Integer) == false) {
0994: sSql.append("'");
0995: }
0996:
0997: sSql.append(addEscapeChars(Values.get(j)
0998: .toString()));
0999:
1000: if ((Values.get(j) instanceof Integer) == false) {
1001: if (sOperator.equals("STARTS_WITH")) {
1002: sSql.append("%");
1003: }
1004: sSql.append("'");
1005: }
1006: }
1007: }
1008: } else if (sOperator.equalsIgnoreCase("IN")
1009: || sOperator.equalsIgnoreCase("NOT IN")) {
1010: sSql.append(" ");
1011: sSql.append(sCol);
1012: sSql.append(" ");
1013: sSql.append(sOperator);
1014: sSql.append(" (");
1015:
1016: for (int j = 0; j < Values.size(); j++) {
1017: if (j != 0) {
1018: sSql.append(",");
1019: }
1020:
1021: if (Values.get(j) instanceof SelectStatement) {
1022: SelectStatement query = (SelectStatement) Values
1023: .get(j);
1024:
1025: sSql.append(getSelectStatement(query));
1026: } else if (Values.get(j) instanceof java.util.Date) {
1027: SimpleDateFormat date_formatter = new SimpleDateFormat(
1028: DB_DATEFORMAT);
1029: String sDate = date_formatter
1030: .format((java.util.Date) Values
1031: .get(j));
1032: sSql.append(getDateAsSQL(sDate));
1033: } else {
1034: if ((Values.get(j) instanceof Integer) == false) {
1035: sSql.append("'");
1036: }
1037:
1038: sSql.append(addEscapeChars(Values.get(j)
1039: .toString()));
1040:
1041: if ((Values.get(j) instanceof Integer) == false) {
1042: sSql.append("'");
1043: }
1044: }
1045: }
1046:
1047: sSql.append(")");
1048: } else if (sOperator.equalsIgnoreCase("BETWEEN")) {
1049: sSql.append(" ");
1050: sSql.append(sCol);
1051: sSql.append(" ");
1052: sSql.append(sOperator);
1053: sSql.append(" ");
1054:
1055: if (Values.get(0) instanceof java.util.Date) {
1056: SimpleDateFormat date_formatter = new SimpleDateFormat(
1057: DB_DATEFORMAT);
1058: String sDate = date_formatter
1059: .format((java.util.Date) Values.get(0));
1060: sSql.append(getDateAsSQL(sDate));
1061: } else {
1062: if ((Values.get(0) instanceof Integer) == false) {
1063: sSql.append("'");
1064: }
1065:
1066: sSql.append(Values.get(0).toString());
1067:
1068: if ((Values.get(0) instanceof Integer) == false) {
1069: sSql.append("'");
1070: }
1071: }
1072:
1073: sSql.append(" AND ");
1074:
1075: if (Values.get(1) instanceof java.util.Date) {
1076: SimpleDateFormat date_formatter = new SimpleDateFormat(
1077: DB_DATEFORMAT);
1078: String sDate = date_formatter
1079: .format((java.util.Date) Values.get(1));
1080: sSql.append(getDateAsSQL(sDate));
1081: } else {
1082: if ((Values.get(1) instanceof Integer) == false) {
1083: sSql.append("'");
1084: }
1085:
1086: sSql.append(Values.get(1).toString());
1087:
1088: if ((Values.get(1) instanceof Integer) == false) {
1089: sSql.append("'");
1090: }
1091: }
1092: } else if (sOperator.equalsIgnoreCase("CONTAINS") == true) {
1093: if (Values.size() > 1) {
1094: sSql.append("(");
1095: }
1096:
1097: for (int j = 0; j < Values.size(); j++) {
1098: if (j != 0) {
1099: sSql.append(" OR ");
1100: }
1101:
1102: sSql.append(" ");
1103: sSql.append(sCol);
1104: sSql.append(" ");
1105: sSql.append("LIKE");
1106: sSql.append(" ");
1107:
1108: if ((Values.get(j) instanceof Integer) == false) {
1109: sSql.append("'%");
1110: }
1111:
1112: sSql.append(addEscapeChars(Values.get(j)
1113: .toString()));
1114:
1115: sSql.append("%'");
1116:
1117: }
1118: if (Values.size() > 1) {
1119: sSql.append(")");
1120: }
1121: }
1122: }
1123:
1124: bAnd = true;
1125: }
1126:
1127: return (sSql.toString());
1128: }
1129:
1130: /**
1131: * Returns the SQL function for the given <code>Function</code>.
1132: *
1133: * @param func the function
1134: * @return the SQL function
1135: * @throws DataStoreException if an error occurs
1136: */
1137: abstract protected String getFunction(Function func)
1138: throws DataStoreException;
1139:
1140: /**
1141: * Returns the SQL 'from' clause for the given <code>AbstractDMLStatement</code>
1142: * and the list of tables.
1143: *
1144: * @param select the select statement
1145: * @return the SQL 'from' clause
1146: * @throws DataStoreException if an error occurs
1147: */
1148: protected String generateFromClause(SelectStatement select)
1149: throws DataStoreException {
1150: StringBuffer sSql = new StringBuffer();
1151: JoinConditions join = select.getJoinConditions();
1152: List SelectCols = select.getSelectColumns();
1153: WhereConditionGroup where = select.getWhereConditions();
1154: Vector saTables = null;
1155:
1156: sSql.append(" from ");
1157:
1158: if (select.hasJoinConditions()) {
1159: List jvec = join.getTableList();
1160: saTables = new Vector(jvec);
1161:
1162: if (select.hasWhereConditions()) {
1163: List wvec = where.getTableList();
1164:
1165: for (int i = 0; i < wvec.size(); i++) {
1166: if (!saTables.contains(wvec.get(i))) {
1167: saTables.add(wvec.get(i));
1168: }
1169: }
1170: }
1171: } else if (SelectCols.size() > 0) {
1172: saTables = new Vector(1);
1173: saTables.add((String) ((ColumnRef) SelectCols.get(0))
1174: .getTable());
1175: } else {
1176: saTables = new Vector(1);
1177: if (select.hasWhereConditions()) {
1178: List wvec = where.getTableList();
1179: saTables.add(wvec.get(0));
1180: }
1181: }
1182:
1183: for (int i = 0; i < saTables.size(); i++) {
1184: String sTable = (String) saTables.elementAt(i);
1185:
1186: if (i > 0) {
1187: sSql.append(",");
1188: }
1189:
1190: if (select.isAlias(sTable)) {
1191: sSql.append(select.getTableName(sTable));
1192: sSql.append(" ");
1193: }
1194:
1195: sSql.append(sTable);
1196: }
1197:
1198: return (sSql.toString());
1199: }
1200:
1201: /**
1202: * Returns the SQL 'order by' clause for the given <code>SelectStatement</code>.
1203: *
1204: * @param select the select statement
1205: * @return the SQL 'order by' clause
1206: */
1207: protected String generateOrderByClause(SelectStatement select) {
1208: StringBuffer sSql = new StringBuffer();
1209: Set orderColSet = select.getOrderByColumns();
1210:
1211: Iterator iter = orderColSet.iterator();
1212: int i = 0;
1213:
1214: if (iter.hasNext()) {
1215: sSql.append(" order by ");
1216:
1217: while (iter.hasNext()) {
1218: ColumnRef ordercol = (ColumnRef) iter.next();
1219:
1220: int nOrderColType = ordercol.getDataType();
1221:
1222: if ((nOrderColType == ColumnRef.TEXT)
1223: || (nOrderColType == ColumnRef.LONG_TEXT)) {
1224: sSql.append("upperCol").append(i++);
1225: } else {
1226: sSql.append(getOrderByRef(ordercol));
1227: }
1228:
1229: sSql.append(" ");
1230: sSql.append(select.getOrderByDirection(ordercol));
1231: if (iter.hasNext()) {
1232: sSql.append(",");
1233: }
1234: }
1235: }
1236:
1237: return (sSql.toString());
1238: }
1239:
1240: /**
1241: * Returns the SQL string to include in to the SQL select statement
1242: * to ensure correct ordering of the result set.
1243: *
1244: * @param ordercol the column reference
1245: * @return the SQL string to include in the SQL select statement
1246: */
1247: protected String getOrderByRef(ColumnRef ordercol) {
1248: StringBuffer sSql = new StringBuffer();
1249: int nOrderColType = ordercol.getDataType();
1250:
1251: if ((nOrderColType == ColumnRef.TEXT)
1252: || (nOrderColType == ColumnRef.LONG_TEXT)) {
1253: sSql.append("upper(");
1254: }
1255:
1256: if (nOrderColType == ColumnRef.LONG_TEXT) {
1257: sSql.append("convert(char(80),");
1258: }
1259:
1260: sSql.append(ordercol.getFullRef());
1261:
1262: if (nOrderColType == ColumnRef.LONG_TEXT) {
1263: sSql.append(")");
1264: }
1265:
1266: if ((nOrderColType == ColumnRef.TEXT)
1267: || (nOrderColType == ColumnRef.LONG_TEXT)) {
1268: sSql.append(")");
1269: }
1270:
1271: return sSql.toString();
1272: }
1273:
1274: /**
1275: * Returns the given date formatted for use in a SQL statement.
1276: *
1277: * @param date the date as a string
1278: * @return the SQL date representation
1279: */
1280: abstract protected String getDateAsSQL(String date);
1281:
1282: /**
1283: * Returns the given <code>String</code> as a <code>Date</code>,
1284: * using the default date format for parsing.
1285: *
1286: * @param sDate the string representation for the date
1287: * @return the date object
1288: * @throws ParseException if a parse error occurs
1289: */
1290: public static java.util.Date parseDate(String sDate)
1291: throws ParseException {
1292: java.util.Date tempDate = new java.util.Date();
1293:
1294: SimpleDateFormat dFormat = new SimpleDateFormat(
1295: AbstractDataStoreInterface.DB_OUT_DATEFORMAT);
1296:
1297: java.util.Date newDate = dFormat.parse(sDate);
1298:
1299: return newDate;
1300: }
1301:
1302: /**
1303: * Closes the connection to the database.
1304: *
1305: * @param conn the connection
1306: */
1307: public void closeConnection(Connection conn) {
1308: try {
1309: if (m_connectionType == POOLED_CONNECTION) {
1310: conn.close();
1311: } else if (m_connectionType == DB_CONNECTION_BROKER) {
1312: DBConnectionPooler.getInstance(m_jdbc_driver, m_db_url,
1313: m_db_usr, m_db_pwd).freeConnection(conn);
1314: }
1315: } catch (Exception e) {
1316: throw new RuntimeException(e.getMessage());
1317: }
1318: }
1319:
1320: /**
1321: * Returns the SQL date datatype for this data store interface.
1322: *
1323: * @return the SQL date datatype
1324: */
1325: abstract public String getDateDataType();
1326:
1327: /**
1328: * Returns the SQL CLOB datatype for this data store interface.
1329: *
1330: * @return the SQL CLOB datatype
1331: */
1332: abstract public String getCLOBDataType();
1333:
1334: /**
1335: * Returns the boolean datatype for this data store interface.
1336: *
1337: * @return the boolean datatype for this data store interface
1338: */
1339: abstract public String getBooleanDataType();
1340:
1341: /**
1342: * Returns the list of tables contained in the database.
1343: *
1344: * @return the list of tables contained in the database
1345: * @throws DataStoreException if an error occurs
1346: */
1347: abstract public List getTableList() throws DataStoreException;
1348:
1349: /**
1350: * Returns <code>true</code> of the specified table exists.
1351: *
1352: * @param sTableName the table name
1353: * @return <code>true</code> of the specified table exists
1354: * @throws DataStoreException if an error occurs
1355: */
1356: public boolean isTableExist(String sTableName)
1357: throws DataStoreException {
1358: return getTableList().contains(sTableName);
1359: }
1360:
1361: /**
1362: * Returns the list of sequences available in the database.
1363: *
1364: * @return the list of sequences available in the database
1365: * @throws DataStoreException
1366: */
1367: abstract public List getSequenceList() throws DataStoreException;
1368:
1369: /**
1370: * Returns the SQL join condition statement joining the two given
1371: * column references, taking in to account whether the join should be
1372: * an outer join.
1373: *
1374: * @param ref1 the column reference on the left side of the join
1375: * @param ref2 the column reference on the right side of the join
1376: * @param bIsOuter <code>true</code> if the join is an outer join
1377: * @return the SQL join condition
1378: */
1379: abstract public String getJoinCondition(ColumnRef ref1,
1380: ColumnRef ref2, boolean bIsOuter);
1381:
1382: /**
1383: * Returns <code>true</code> if the DB supports the F421 SQL
1384: * National Character feature
1385: *
1386: * @return <code>true</code> if the DB supports the F421 SQL
1387: * National Character feature
1388: */
1389: protected boolean isNationalCharacterSupported() {
1390: return m_bIsNationalCharacterSupported;
1391: }
1392:
1393: /**
1394: * Sets whether the DB supports the F421 SQL
1395: * National Character feature
1396: *
1397: * @param bIsSupported <code>true</code> if the DB supports the F421 SQL
1398: * National Character feature
1399: */
1400: protected void setNationalCharacterSupport(boolean bIsSupported) {
1401: m_bIsNationalCharacterSupported = bIsSupported;
1402: }
1403: }
|