0001: /*
0002: * JFolder, Copyright 2001-2006 Gary Steinmetz
0003: *
0004: * Distributable under LGPL license.
0005: * See terms of license at gnu.org.
0006: */
0007:
0008: package org.jfolder.platforms.stores.instances.hsql;
0009:
0010: //base classes
0011: import java.math.BigDecimal;
0012: import java.sql.Connection;
0013: import java.sql.PreparedStatement;
0014: import java.sql.ResultSet;
0015: import java.sql.SQLException;
0016: import java.sql.Statement;
0017: import java.sql.Types;
0018: import java.util.ArrayList;
0019:
0020: //project specific classes
0021: import org.jfolder.common.UnexpectedSystemException;
0022: import org.jfolder.common.utils.misc.MiscHelper;
0023: import org.jfolder.platforms.stores.base.CreateStatement;
0024: import org.jfolder.platforms.stores.base.DeleteStatement;
0025: import org.jfolder.platforms.stores.base.DropStatement;
0026: import org.jfolder.platforms.stores.base.InsertStatement;
0027: import org.jfolder.platforms.stores.base.SelectStatement;
0028: import org.jfolder.platforms.stores.base.SequenceStatement;
0029: import org.jfolder.platforms.stores.base.SystemResultSet;
0030: import org.jfolder.platforms.stores.base.SystemStatement;
0031: import org.jfolder.platforms.stores.base.SystemStore;
0032: import org.jfolder.platforms.stores.base.UpdateStatement;
0033: import org.jfolder.platforms.stores.base.WhereStatement;
0034:
0035: //other classes
0036:
0037: public class HSQLStore extends SystemStore {
0038:
0039: //
0040: private final static String ID_PK = "ID_PK";
0041: private final static String SERIES_SEQUENCE = "SERIES_SEQUENCE";
0042: //
0043: private final static String PARAM = "?";
0044: private final static String NULL = "null";
0045:
0046: //
0047: private int constraintCount = 0;
0048:
0049: //
0050: public HSQLStore() {
0051: }
0052:
0053: public static String getDisplayName() {
0054: return "Hypersonic";
0055: }
0056:
0057: public void prepareConnection(Connection inConn)
0058: throws SQLException {
0059: PreparedStatement ps = null;
0060: //
0061: try {
0062: //inConn.commit();
0063: ps = inConn.prepareStatement("SET AUTOCOMMIT FALSE");
0064: ps.execute();
0065: } catch (SQLException sqle) {
0066: throw new UnexpectedSystemException(sqle);
0067: } finally {
0068: closeSQLObjects(null, ps, null);
0069: }
0070: }
0071:
0072: public void execute(SystemStatement inWs, Connection inConn) {
0073:
0074: try {
0075: this .constraintCount = 0;
0076:
0077: if (inWs instanceof CreateStatement) {
0078: //
0079: //SELECT NEXT VALUE FOR mysequence, col1, col2 FROM mytable
0080: CreateStatement wcs = (CreateStatement) inWs;
0081: processCreateStatement(wcs, inConn);
0082: } else if (inWs instanceof DropStatement) {
0083: //
0084: //SELECT NEXT VALUE FOR mysequence, col1, col2 FROM mytable
0085: DropStatement wds = (DropStatement) inWs;
0086: processDropStatement(wds, inConn);
0087: } else if (inWs instanceof InsertStatement) {
0088: //
0089: InsertStatement wis = (InsertStatement) inWs;
0090: processInsertStatement(wis, inConn);
0091: } else if (inWs instanceof SelectStatement) {
0092: //
0093: SelectStatement wss = (SelectStatement) inWs;
0094: wss.setResults(processSelectStatement(wss, inConn));
0095: } else if (inWs instanceof DeleteStatement) {
0096: //
0097: DeleteStatement wds = (DeleteStatement) inWs;
0098: processDeleteStatement(wds, inConn);
0099: } else if (inWs instanceof UpdateStatement) {
0100: //
0101: UpdateStatement wus = (UpdateStatement) inWs;
0102: processUpdateStatement(wus, inConn);
0103: } else {
0104: throw UnexpectedSystemException.notImplemented();
0105: }
0106: } catch (SQLException sqle) {
0107: throw new UnexpectedSystemException(sqle);
0108: } finally {
0109: this .constraintCount = 0;
0110: }
0111: }
0112:
0113: public void commit(Connection inConn) {
0114: //TO DO: try to determine how to set autocommit=false through prop file
0115: PreparedStatement ps = null;
0116: //
0117: try {
0118: //inConn.commit();
0119: ps = inConn.prepareStatement("COMMIT");
0120: ps.execute();
0121: } catch (SQLException sqle) {
0122: throw new UnexpectedSystemException(sqle);
0123: } finally {
0124: closeSQLObjects(null, ps, null);
0125: }
0126: }
0127:
0128: protected void prepareParameters(PreparedStatement inPs,
0129: ArrayList inTypes, ArrayList inValues) throws SQLException {
0130:
0131: if (inTypes != null && inValues != null) {
0132: if (inTypes.size() == inValues.size()) {
0133: for (int i = 0; i < inTypes.size(); i++) {
0134: //
0135: Integer nextType = (Integer) inTypes.get(i);
0136: Object nextValue = inValues.get(i);
0137: //
0138: //MiscHelper.println("HSQLStore nextType = " + nextType);
0139: //MiscHelper.println("HSQLStore nextValue = " + nextValue);
0140: //
0141: if (nextType.equals(SystemStatement.DECIMAL)) {
0142: inPs.setBigDecimal((i + 1),
0143: (BigDecimal) nextValue);
0144: } else if (nextType
0145: .equals(SystemStatement.SHORT_STRING)) {
0146: inPs.setString((i + 1), (String) nextValue);
0147: } else if (nextType
0148: .equals(SystemStatement.LONG_STRING)) {
0149: inPs.setString((i + 1), (String) nextValue);
0150: } else if (nextType.equals(SystemStatement.BOOLEAN)) {
0151: Boolean nextBoolean = (Boolean) nextValue;
0152: if (nextBoolean != null) {
0153: inPs.setBoolean((i + 1), nextBoolean
0154: .booleanValue());
0155: } else {
0156: inPs.setNull((i + 1), Types.BOOLEAN);
0157: }
0158: } else if (nextType
0159: .equals(SystemStatement.BINARY_OBJECT)) {
0160: inPs.setBytes((i + 1), (byte[]) nextValue);
0161: } else {
0162: throw UnexpectedSystemException.unknownState();
0163: }
0164: }
0165: } else {
0166: throw UnexpectedSystemException.unknownState();
0167: }
0168: }
0169: }
0170:
0171: //
0172: private void processCreateStatement(CreateStatement inWcs,
0173: Connection inConn) throws SQLException {
0174:
0175: //
0176: if (inWcs.isSeriesSequenceColumnPresent()) {
0177: //
0178: startStatement();
0179: simpleAndPrint("CREATE SEQUENCE "
0180: + getActualSeriesSequenceName(inWcs.getName()));
0181: endStatement(inConn, null, null, null, null, false);
0182: }
0183: //
0184: startStatement();
0185: //
0186: printAndIndent("CREATE TABLE "
0187: + getActualTableName(inWcs.getName()) + " (");
0188: //
0189: ArrayList subStatements = new ArrayList();
0190: //
0191: if (inWcs.isIdColumnPresent()) {
0192: subStatements.add(ID_PK + " BIGINT IDENTITY PRIMARY KEY");
0193: }
0194: //
0195: if (inWcs.isSeriesSequenceColumnPresent()) {
0196: subStatements.add(SERIES_SEQUENCE + " DECIMAL NOT NULL");
0197: }
0198: //
0199: for (int i = 0; i < inWcs.getColumnCount(); i++) {
0200: String nextColumnName = inWcs.getColumnName(i);
0201: boolean nextColumnNull = inWcs.getColumnNull(i);
0202: Integer nextColumnDataTypeCode = inWcs.getColumnDataType(i);
0203: String nextForeignTable = inWcs.getColumnForeignKey(i);
0204:
0205: //
0206: String nextColumnType = null;
0207: if (nextForeignTable != null) {
0208: nextColumnType = "BIGINT";
0209: } else if (nextColumnDataTypeCode
0210: .equals(SystemStatement.DECIMAL)) {
0211: nextColumnType = "DECIMAL";
0212: } else if (nextColumnDataTypeCode
0213: .equals(SystemStatement.SHORT_STRING)) {
0214: nextColumnType = "VARCHAR";
0215: } else if (nextColumnDataTypeCode
0216: .equals(SystemStatement.LONG_STRING)) {
0217: nextColumnType = "VARCHAR";
0218: } else if (nextColumnDataTypeCode
0219: .equals(SystemStatement.BOOLEAN)) {
0220: nextColumnType = "BOOLEAN";
0221: } else if (nextColumnDataTypeCode
0222: .equals(SystemStatement.BINARY_OBJECT)) {
0223: nextColumnType = "BINARY";
0224: }
0225: //else if (nextColumnTypeCode.equals(
0226: // WorkflowStatement.BINARY_STREAM)) {
0227: //}
0228: else {
0229: throw UnexpectedSystemException.unknownState();
0230: }
0231:
0232: //
0233: if (!nextColumnNull) {
0234: subStatements.add(nextColumnName + " " + nextColumnType
0235: + " NOT NULL");
0236: } else {
0237: subStatements
0238: .add(nextColumnName + " " + nextColumnType);
0239: }
0240: }
0241: //
0242: for (int i = 0; i < inWcs.getColumnCount(); i++) {
0243: String nextColumnName = inWcs.getColumnName(i);
0244: String nextForeignTable = inWcs.getColumnForeignKey(i);
0245: //
0246: if (nextForeignTable != null) {
0247: subStatements.add("CONSTRAINT "
0248: + getConstraintName(inWcs.getName())
0249: + " FOREIGN KEY (" + nextColumnName + ")"
0250: + " REFERENCES "
0251: + getActualTableName(nextForeignTable) + " ("
0252: + ID_PK + ")");
0253: }
0254: }
0255: //
0256: for (int i = 0; i < inWcs.getUniqueConstraintCount(); i++) {
0257: ArrayList nextUniqueList = inWcs.getUniqueConstraint(i);
0258: //
0259: subStatements.add("CONSTRAINT "
0260: + getConstraintName(inWcs.getName()) + " UNIQUE ("
0261: + convertToList(nextUniqueList) + ")");
0262: }
0263: //
0264: if (inWcs.isSeriesSequenceColumnPresent()) {
0265: subStatements.add("CONSTRAINT "
0266: + getConstraintName(inWcs.getName()) + " UNIQUE ("
0267: + SERIES_SEQUENCE + ")");
0268: }
0269: //
0270: for (int i = 0; i < subStatements.size(); i++) {
0271: if (i < (subStatements.size() - 1)) {
0272: simpleAndPrint(subStatements.get(i).toString() + ",");
0273: } else {
0274: simpleAndPrint(subStatements.get(i).toString());
0275: }
0276: }
0277: //
0278: revertAndPrint(")");
0279: //
0280: endStatement(inConn, null, null, null, null, false);
0281: }
0282:
0283: //
0284: private void processDropStatement(DropStatement inWds,
0285: Connection inConn) throws SQLException {
0286:
0287: CreateStatement wcs = inWds.getEntity();
0288:
0289: SQLException previousSqle = null;
0290:
0291: //
0292: try {
0293: startStatement();
0294: simpleAndPrint("DROP TABLE "
0295: + getActualTableName(wcs.getName()));
0296: endStatement(inConn, null, null, null, null, false);
0297: } catch (SQLException sqle) {
0298: previousSqle = sqle;
0299: }
0300:
0301: //
0302: if (wcs.isSeriesSequenceColumnPresent()) {
0303: startStatement();
0304: simpleAndPrint("DROP SEQUENCE "
0305: + getActualSeriesSequenceName(wcs.getName()));
0306: endStatement(inConn, null, null, null, null, false);
0307: }
0308:
0309: //
0310: if (previousSqle != null) {
0311: throw previousSqle;
0312: }
0313: }
0314:
0315: //
0316: private void processInsertStatement(InsertStatement inWis,
0317: Connection inConn) throws SQLException {
0318:
0319: CreateStatement wcs = inWis.getEntity();
0320:
0321: ArrayList columnNames = new ArrayList();
0322: ArrayList columnTypes = new ArrayList();
0323: ArrayList columnParams = new ArrayList();
0324: ArrayList columnValues = new ArrayList();
0325:
0326: if (wcs.isSeriesSequenceColumnPresent()) {
0327: //
0328: BigDecimal seriesSequence = getNextSeriesSequence(wcs
0329: .getName(), inConn);
0330: inWis.setSeriesSequenceColumnValue(seriesSequence);
0331: //
0332: columnNames.add(SERIES_SEQUENCE);
0333: columnTypes.add(SystemStatement.DECIMAL);
0334: columnParams.add(PARAM);
0335: columnValues.add(seriesSequence);
0336: }
0337:
0338: for (int i = 0; i < inWis.getColumnCount(); i++) {
0339: columnNames.add(inWis.getColumnName(i));
0340: columnTypes.add(inWis.getColumnType(i));
0341: columnParams.add(PARAM);
0342: columnValues.add(inWis.getColumnValue(i));
0343: }
0344:
0345: startStatement();
0346: simpleAndPrint("INSERT INTO "
0347: + getActualTableName(wcs.getName()) + " ("
0348: + convertToList(columnNames) + ") VALUES ("
0349: + convertToList(columnParams) + ")");
0350: endStatement(inConn, columnTypes, columnValues, null, null,
0351: false);
0352:
0353: //
0354: Statement s = null;
0355: ResultSet rs = null;
0356:
0357: if (inWis.getEntity().isIdColumnPresent()) {
0358: try {
0359: s = inConn.createStatement();
0360: s.execute("CALL IDENTITY();");
0361: rs = s.getResultSet();
0362: if (rs.next()) {
0363: inWis.setIdColumnValue(rs.getBigDecimal(1));
0364: } else {
0365: throw UnexpectedSystemException.unknownState();
0366: }
0367: } finally {
0368: //s.close();
0369: closeSQLObjects(null, s, rs);
0370: }
0371: }
0372: }
0373:
0374: //
0375: private void processDeleteStatement(DeleteStatement inWds,
0376: Connection inConn) throws SQLException {
0377:
0378: //
0379: ArrayList parameterTypes = new ArrayList();
0380: ArrayList parameterValues = new ArrayList();
0381: //
0382: String where = createWhereClause(inWds, parameterTypes,
0383: parameterValues, 0);
0384:
0385: //
0386: int deleteCount[] = new int[1];
0387: String tableName = getActualTableName(inWds.getEntity()
0388: .getName());
0389: //
0390: startStatement();
0391: simpleAndPrint("DELETE FROM " + tableName);
0392: if (where != null) {
0393: simpleAndPrint(where);
0394: }
0395: endStatement(inConn, parameterTypes, parameterValues, null,
0396: deleteCount, false);
0397:
0398: //
0399: inWds.setDeleteCount(deleteCount[0]);
0400: }
0401:
0402: //
0403: private void processUpdateStatement(UpdateStatement inWus,
0404: Connection inConn) throws SQLException {
0405:
0406: //
0407: CreateStatement table = inWus.getEntity();
0408: int updateCount[] = new int[1];
0409: //
0410: ArrayList parameterTypes = new ArrayList();
0411: ArrayList parameterValues = new ArrayList();
0412: //
0413: ArrayList parameterPlaceholders = new ArrayList();
0414: for (int i = 0; i < inWus.getUpdateColumnCount(); i++) {
0415: //
0416: String nextColumnName = inWus.getUpdateColumnName(i);
0417: Object nextColumnValue = inWus.getUpdateColumnValue(i);
0418: Integer nextColumnDataType = table
0419: .getColumnDataType(nextColumnName);
0420:
0421: //
0422: parameterTypes.add(nextColumnDataType);
0423: parameterValues.add(nextColumnValue);
0424: //
0425: parameterPlaceholders.add(getActualTableName(table
0426: .getName())
0427: + "." + nextColumnName + " = " + PARAM);
0428: }
0429: //
0430: String where = createWhereClause(inWus, parameterTypes,
0431: parameterValues, 0);
0432:
0433: //
0434: startStatement();
0435: simpleAndPrint("UPDATE " + getActualTableName(table.getName()));
0436: simpleAndPrint("SET " + convertToList(parameterPlaceholders));
0437: if (where != null) {
0438: simpleAndPrint(where);
0439: }
0440: endStatement(inConn, parameterTypes, parameterValues, null,
0441: updateCount, false);
0442:
0443: //
0444: inWus.setUpdateCount(updateCount[0]);
0445: }
0446:
0447: //
0448: private SystemResultSet processSelectStatement(
0449: SelectStatement inWss, Connection inConn)
0450: throws SQLException {
0451:
0452: SystemResultSet outValue = null;
0453:
0454: ResultSet rs = null;
0455: PreparedStatement ps[] = new PreparedStatement[1];
0456:
0457: try {
0458: StringBuffer statement = new StringBuffer();
0459: ArrayList parameterTypes = new ArrayList();
0460: ArrayList parameterValues = new ArrayList();
0461:
0462: createSelectStatement(inWss, statement, parameterTypes,
0463: parameterValues, 0, false);
0464:
0465: startStatement();
0466: simpleAndPrint(statement.toString());
0467: rs = endStatement(inConn, parameterTypes, parameterValues,
0468: ps, null, true);
0469: //MiscHelper.println("Got result set = " + inWss.getQuerySize());
0470:
0471: outValue = SystemStatement.newResultSet(inWss
0472: .getQuerySize());
0473: //
0474: for (int i = 0; i < inWss.getQuerySize() && rs.next(); i++) {
0475: //MiscHelper.println("Reading next row = "
0476: // + inWss.getSelectColumnCount());
0477: ArrayList nextRowTypes = new ArrayList();
0478: ArrayList nextRowValues = new ArrayList();
0479: for (int j = 0; j < inWss.getSelectColumnCount(); j++) {
0480: //
0481: String nextColumnName = inWss
0482: .getSelectColumnName(j);
0483: Integer nextColumnMetaType = inWss
0484: .getSelectColumnMetaType(j);
0485: //MiscHelper.println("nextColumnName = "
0486: // + nextColumnName);
0487: //MiscHelper.println("nextColumnMetaType = "
0488: // + nextColumnMetaType);
0489: CreateStatement nextTable = inWss
0490: .getSelectColumnTable(j);
0491: //
0492: if (nextColumnMetaType
0493: .equals(SystemStatement.NORMAL_COLUMN)) {
0494: //
0495: //
0496: String nextTableName = getActualTableName(nextTable
0497: .getName());
0498: Integer nextColumnDataType = nextTable
0499: .getColumnDataType(nextColumnName);
0500: //MiscHelper.println("nextColumnDataType = "
0501: // + nextColumnDataType);
0502: //
0503: nextRowTypes.add(nextColumnDataType);
0504: if (nextColumnDataType
0505: .equals(SystemStatement.DECIMAL)) {
0506: //
0507: nextRowValues.add(rs.getBigDecimal(j + 1));
0508: } else if (nextColumnDataType
0509: .equals(SystemStatement.SHORT_STRING)) {
0510: //
0511: nextRowValues.add(rs.getString(j + 1));
0512: } else if (nextColumnDataType
0513: .equals(SystemStatement.LONG_STRING)) {
0514: //
0515: nextRowValues.add(rs.getString(j + 1));
0516: } else if (nextColumnDataType
0517: .equals(SystemStatement.BOOLEAN)) {
0518: //
0519: nextRowValues.add(new Boolean(rs
0520: .getBoolean(j + 1)));
0521: } else if (nextColumnDataType
0522: .equals(SystemStatement.BINARY_OBJECT)) {
0523: //
0524: nextRowValues.add(rs.getBytes(j + 1));
0525: } else {
0526: throw UnexpectedSystemException
0527: .unknownState();
0528: }
0529: } else {
0530: nextRowTypes.add(SystemStatement.DECIMAL);
0531: nextRowValues.add(rs.getBigDecimal(j + 1));
0532: }
0533: }
0534: //MiscHelper.println("HSQLStore nextRowVal = " + nextRowValues);
0535: outValue.addRow(nextRowTypes, nextRowValues);
0536: }
0537: } finally {
0538: closeSQLObjects(null, ps[0], rs);
0539: }
0540:
0541: return outValue;
0542: }
0543:
0544: private void createSelectStatement(SelectStatement inWss,
0545: StringBuffer inStatement, ArrayList inParameterTypes,
0546: ArrayList inParameterValues, int inIndent,
0547: boolean inTopSelect) throws SQLException {
0548:
0549: //construct from
0550: ArrayList fromList = new ArrayList();
0551: ArrayList fromListWithTables = inWss.getAllTables();
0552: for (int i = 0; i < fromListWithTables.size(); i++) {
0553: CreateStatement wcs = (CreateStatement) fromListWithTables
0554: .get(i);
0555: fromList.add(getActualTableName(wcs.getName()));
0556: }
0557:
0558: //construct where
0559: String where = createWhereClause(inWss, inParameterTypes,
0560: inParameterValues, inIndent);
0561:
0562: //construct group by
0563: ArrayList groupByList = new ArrayList();
0564: //for (int i = 0; i < fromListWithTables.size(); i++) {
0565: // CreateStatement nextTable =
0566: // (CreateStatement)fromListWithTables.get(i);
0567: // String nextTableName = getActualTableName(nextTable.getName());
0568: // if (nextTable.isSeriesSequenceColumnPresent()) {
0569: // for (int j = 0; j < nextTable.getGroupByColumnCount(); j++) {
0570: // String nextGroupByName =
0571: // nextTable.getGroupByColumnName(j);
0572: // Integer nextGroupByType =
0573: // nextTable.getGroupByColumnType(j);
0574: // //
0575: // if (SelectStatement.NORMAL_COLUMN.equals(
0576: // nextGroupByType)) {
0577: // groupByList.add(
0578: // nextTableName + "." + nextGroupByName);
0579: // }
0580: // else if (
0581: // SelectStatement.SERIES_SEQUENCE_COLUMN.equals(
0582: // nextGroupByType)) {
0583: // groupByList.add(
0584: // nextTableName + "." + SERIES_SEQUENCE);
0585: // }
0586: // else if (SelectStatement.ID_COLUMN.equals(
0587: // nextGroupByType)) {
0588: // groupByList.add(nextTableName + "." + ID_PK);
0589: // }
0590: // else {
0591: // throw new UnexpectedSystemException("UnknownState");
0592: // }
0593: // }
0594: // }
0595: //}
0596: for (int i = 0; i < inWss.getGroupByColumnCount(); i++) {
0597: //
0598: String nextGroupByColumnName = inWss
0599: .getGroupByColumnName(i);
0600: Integer nextGroupByColumnType = inWss
0601: .getGroupByColumnType(i);
0602: //
0603: CreateStatement nextGroupByColumnTable = inWss
0604: .getGroupByColumnTable(i);
0605: String nextGroupByColumnTableName = getActualTableName(nextGroupByColumnTable
0606: .getName());
0607:
0608: if (SelectStatement.NORMAL_COLUMN
0609: .equals(nextGroupByColumnType)) {
0610:
0611: groupByList.add(nextGroupByColumnTableName + "."
0612: + nextGroupByColumnName);
0613: } else if (SelectStatement.SERIES_SEQUENCE_COLUMN
0614: .equals(nextGroupByColumnType)) {
0615:
0616: groupByList.add(nextGroupByColumnTableName + "."
0617: + SERIES_SEQUENCE);
0618: } else if (SelectStatement.ID_COLUMN
0619: .equals(nextGroupByColumnType)) {
0620:
0621: groupByList.add(nextGroupByColumnTableName + "."
0622: + ID_PK);
0623: } else {
0624: throw UnexpectedSystemException.unknownState();
0625: }
0626: }
0627: StringBuffer groupBy = null;
0628: if (groupByList.size() > 0) {
0629: groupBy = new StringBuffer();
0630: groupBy.append("GROUP BY ");
0631: groupBy.append(convertToList(groupByList));
0632: }
0633:
0634: ////construct having
0635: //ArrayList havingList = new ArrayList();
0636: //for (int i = 0; i < fromListWithTables.size(); i++) {
0637: // CreateStatement nextTable =
0638: // (CreateStatement)fromListWithTables.get(i);
0639: // String nextTableName = getActualTableName(nextTable.getName());
0640: // if (nextTable.isSeriesSequenceColumnPresent()) {
0641: // havingList.add(nextTableName + "." + SERIES_SEQUENCE
0642: // + " = MAX(" + nextTableName + "." + SERIES_SEQUENCE
0643: // + ")");
0644: // }
0645: //}
0646: //StringBuffer having = null;
0647: //if (havingList.size() > 0) {
0648: // having = new StringBuffer();
0649: // having.append("HAVING ");
0650: // having.append(convertToList(havingList, " AND "));
0651: //}
0652:
0653: //construct select
0654: ArrayList selectList = new ArrayList();
0655: for (int i = 0; i < inWss.getSelectColumnCount(); i++) {
0656: //
0657: String nextColumnName = inWss.getSelectColumnName(i);
0658: Integer nextColumnMetaType = inWss
0659: .getSelectColumnMetaType(i);
0660: CreateStatement nextTable = inWss.getSelectColumnTable(i);
0661: String nextTableName = getActualTableName(nextTable
0662: .getName());
0663: Integer nextColumnQuantifier = inWss
0664: .getSelectColumnQuantifier(i);
0665: //
0666: if (SelectStatement.NORMAL_COLUMN
0667: .equals(nextColumnMetaType)) {
0668: //
0669: String nextColumn = nextTableName + "."
0670: + nextColumnName;
0671: //
0672: //if (groupBy != null && !groupByList.contains(nextColumn)) {
0673: // throw UnexpectedSystemException.unknownState();
0674: //}
0675: //else {
0676: selectList.add(quantify(nextColumn,
0677: nextColumnQuantifier));
0678: //}
0679: } else if (SelectStatement.SERIES_SEQUENCE_COLUMN
0680: .equals(nextColumnMetaType)) {
0681: //
0682: String nextColumn = nextTableName + "."
0683: + SERIES_SEQUENCE;
0684: //if (groupBy != null) {
0685: // selectList.add("MAX(" + nextColumn + ")");
0686: //}
0687: //else {
0688: selectList.add(quantify(nextColumn,
0689: nextColumnQuantifier));
0690: //}
0691: } else if (SelectStatement.ID_COLUMN
0692: .equals(nextColumnMetaType)) {
0693: //
0694: String nextColumn = nextTableName + "." + ID_PK;
0695: //if (groupBy != null) {
0696: // selectList.add("MAX(" + nextColumn + ")");
0697: //}
0698: //else {
0699: selectList.add(quantify(nextColumn,
0700: nextColumnQuantifier));
0701: //}
0702: } else {
0703: throw UnexpectedSystemException.unknownState();
0704: }
0705: }
0706:
0707: //construct order by
0708: StringBuffer orderBy = new StringBuffer("ORDER BY ");
0709: orderBy.append(selectList.get(0));
0710: //orderBy.append(getActualTableName(inWss.getOrderByTable().getName()));
0711: //orderBy.append(".");
0712: //Integer orderByType = inWss.getOrderByType();
0713: //
0714: //if (SelectStatement.NORMAL_COLUMN.equals(orderByType)) {
0715: // orderBy.append(inWss.getOrderByName());
0716: //}
0717: //else if (SelectStatement.SERIES_SEQUENCE_COLUMN.equals(
0718: // orderByType)) {
0719: // orderBy.append(SERIES_SEQUENCE);
0720: //}
0721: //else if (SelectStatement.ID_COLUMN.equals(orderByType)) {
0722: // orderBy.append(ID_PK);
0723: //}
0724: //else {
0725: // throw UnexpectedSystemException.unknownState();
0726: //}
0727: //
0728: if (inWss.isListOrderAscending()) {
0729: orderBy.append(" ASC");
0730: } else {
0731: orderBy.append(" DESC");
0732: }
0733:
0734: //MiscHelper.println("@@@@@@@@@@@@@@@ HSQLStore REMOVING HAVING");
0735: ////if (having != null) {
0736: //// selectList.clear();
0737: //// selectList.add("T_JF_DOCS_RO.SECTION");
0738: ////}
0739: //having = null;
0740: //orderBy = null;
0741:
0742: //construct statement
0743: //-select-
0744: if (selectList.size() > 0) {
0745: inStatement.append(MiscHelper.fixString("", inIndent));
0746: inStatement.append("SELECT " + convertToList(selectList));
0747: inStatement.append("\n");
0748: }
0749: //-from-
0750: if (fromList.size() > 0) {
0751: inStatement.append(MiscHelper.fixString("", inIndent));
0752: inStatement.append("FROM " + convertToList(fromList));
0753: inStatement.append("\n");
0754: }
0755: //-where-
0756: if (where != null) {
0757: inStatement.append(where);
0758: }
0759: //-group by-
0760: if (groupBy != null) {
0761: inStatement.append(MiscHelper.fixString("", inIndent));
0762: inStatement.append(groupBy);
0763: inStatement.append("\n");
0764: }
0765: //-having-
0766: //if (having != null) {
0767: // inStatement.append(MiscHelper.fixString("", inIndent));
0768: // inStatement.append(having);
0769: // inStatement.append("\n");
0770: //}
0771: //-order by-
0772: if (orderBy != null) {
0773: inStatement.append(MiscHelper.fixString("", inIndent));
0774: inStatement.append(orderBy);
0775: inStatement.append("\n");
0776: }
0777: //-limit-
0778: if (inTopSelect) {
0779: inStatement.append(MiscHelper.fixString("", inIndent));
0780: inStatement.append("LIMIT " + inWss.getStartRow() + " "
0781: + inWss.getQuerySize());
0782: inStatement.append("\n");
0783: }
0784: }
0785:
0786: private String quantify(String inName, Integer inQuantifier) {
0787:
0788: String outValue = inName;
0789:
0790: if (inQuantifier != null) {
0791: if (inQuantifier.equals(SystemStatement.MAX_COLUMN)) {
0792: outValue = "MAX(" + outValue + ")";
0793: } else {
0794: throw UnexpectedSystemException.unknownState();
0795: }
0796: }
0797:
0798: return outValue;
0799: }
0800:
0801: private void constructCompare(StringBuffer inStatement,
0802: ArrayList inParameterTypes, ArrayList inParameterValues,
0803: CreateStatement inTable, Integer inMetaType,
0804: String inColumn, Integer inDataType,
0805: String inCompareSymbol, Object inValue) {
0806: //
0807: inStatement.append(getActualTableName(inTable.getName()));
0808: inStatement.append(".");
0809: if (inMetaType.equals(SystemStatement.NORMAL_COLUMN)) {
0810: inStatement.append(inColumn);
0811: } else if (inMetaType.equals(SystemStatement.ID_COLUMN)) {
0812: inStatement.append(ID_PK);
0813: } else if (inMetaType
0814: .equals(SystemStatement.SERIES_SEQUENCE_COLUMN)) {
0815: inStatement.append(SERIES_SEQUENCE);
0816: } else {
0817: throw UnexpectedSystemException.unknownState();
0818: }
0819: inStatement.append(" ");
0820: inStatement.append(inCompareSymbol);
0821: if (inValue != null) {
0822: inStatement.append(" ");
0823: inStatement.append("?");
0824: }
0825: //
0826: //MiscHelper.println("HSQLStore1 inDataType = " + inDataType);
0827: if (inDataType == null) {
0828: throw new UnexpectedSystemException("Null Data Type");
0829: }
0830: inParameterTypes.add(inDataType);
0831: inParameterValues.add(inValue);
0832: }
0833:
0834: private String createWhereClause(WhereStatement inWws,
0835: ArrayList inParameterTypes, ArrayList inParameterValues,
0836: int inIndent) throws SQLException {
0837:
0838: String outValue = null;
0839:
0840: //construct where
0841: ArrayList fromListWithTables = inWws.getAllTables();
0842: //
0843: ArrayList foreignKeyList = new ArrayList();
0844: for (int i = 0; i < fromListWithTables.size(); i++) {
0845: CreateStatement nextTable = (CreateStatement) fromListWithTables
0846: .get(i);
0847: String nextTableName = getActualTableName(nextTable
0848: .getName());
0849: for (int j = 0; j < nextTable.getColumnCount(); j++) {
0850: String nextColumnName = nextTable.getColumnName(j);
0851: String nextColumnForeign = nextTable
0852: .getColumnForeignKey(j);
0853: //
0854: if (!inWws.isForeignKeyBlocked(nextTable,
0855: nextColumnName)
0856: && nextColumnForeign != null
0857: && inWws
0858: .isPresentWithinAllTables(nextColumnForeign)) {
0859: //
0860: foreignKeyList.add(nextTableName + "."
0861: + nextColumnName + " = "
0862: + getActualTableName(nextColumnForeign)
0863: + "." + ID_PK);
0864: }
0865: }
0866: }
0867: //
0868: StringBuffer customWhere = new StringBuffer();
0869: for (int i = 0; i < inWws.getWhereColumnCount(); i++) {
0870: //
0871: customWhere.append(MiscHelper.fixString("", inIndent));
0872: //
0873: String nextColumnName = inWws.getWhereColumnName(i);
0874: Integer nextColumnMetaType = inWws
0875: .getWhereColumnMetaType(i);
0876: //
0877: CreateStatement nextColumnTable = inWws
0878: .getWhereColumnTable(i);
0879: Integer nextColumnDataType = null;
0880: //
0881: if (nextColumnMetaType
0882: .equals(SystemStatement.NORMAL_COLUMN)
0883: && nextColumnName != null) {
0884: //
0885: nextColumnDataType = nextColumnTable
0886: .getColumnDataType(nextColumnName);
0887: } else if (nextColumnMetaType
0888: .equals(SystemStatement.ID_COLUMN)) {
0889: nextColumnDataType = SystemStatement.DECIMAL;
0890: } else if (nextColumnMetaType
0891: .equals(SystemStatement.SERIES_SEQUENCE_COLUMN)) {
0892: nextColumnDataType = SystemStatement.DECIMAL;
0893: } else {
0894: throw UnexpectedSystemException.unknownState();
0895: }
0896: //
0897: CreateStatement nextColumnAlternateTable = inWws
0898: .getWhereColumnAlternateTable(i);
0899: Object nextColumnAlternateValue = inWws
0900: .getWhereColumnAlternateValue(i);
0901: String nextColumnAlternateName = inWws
0902: .getWhereColumnAlternateName(i);
0903: Integer nextColumnAlternateMetaType = inWws
0904: .getWhereColumnAlternateMetaType(i);
0905: //
0906: String nextColumnConjunction = inWws
0907: .getWhereColumnConjunction(i);
0908: Integer nextColumnComparison = inWws
0909: .getWhereColumnComparison(i);
0910: //
0911: int nextColumnParenthesis = inWws
0912: .getWhereColumnParenthesis(i);
0913: // convertToParenthesis(inWss.getWhereColumnParenthesis(i));
0914:
0915: if (nextColumnParenthesis > 0) {
0916: if (i > 0) {
0917: customWhere.append(" ");
0918: customWhere.append(nextColumnConjunction);
0919: customWhere.append(" ");
0920: }
0921: customWhere
0922: .append(convertToParenthesis(nextColumnParenthesis));
0923: } else if (nextColumnParenthesis < 0) {
0924: customWhere
0925: .append(convertToParenthesis(nextColumnParenthesis));
0926: if (i > 0) {
0927: customWhere.append(" ");
0928: customWhere.append(nextColumnConjunction);
0929: customWhere.append(" ");
0930: }
0931: } else {
0932: if (i > 0) {
0933: customWhere.append(" ");
0934: customWhere.append(nextColumnConjunction);
0935: customWhere.append(" ");
0936: }
0937: }
0938:
0939: //
0940: //MiscHelper.println("HSQLStore::769 nextColumnAlternateTable = "
0941: // + nextColumnAlternateTable);
0942: //MiscHelper.println("HSQLStore::769 nextColumnAlternateValue = "
0943: // + nextColumnAlternateValue);
0944: if (nextColumnAlternateTable != null
0945: && nextColumnAlternateValue == null) {
0946: //
0947: throw UnexpectedSystemException.notImplemented();
0948: } else if (nextColumnAlternateTable == null
0949: && nextColumnAlternateValue != null) {
0950: //
0951: if (SelectStatement.EQUALS.equals(nextColumnComparison)) {
0952: //
0953: constructCompare(customWhere, inParameterTypes,
0954: inParameterValues, nextColumnTable,
0955: nextColumnMetaType, nextColumnName,
0956: nextColumnDataType, "=",
0957: nextColumnAlternateValue);
0958: } else if (SelectStatement.GREATER_THAN
0959: .equals(nextColumnComparison)) {
0960: //
0961: constructCompare(customWhere, inParameterTypes,
0962: inParameterValues, nextColumnTable,
0963: nextColumnMetaType, nextColumnName,
0964: nextColumnDataType, ">",
0965: nextColumnAlternateValue);
0966: } else if (SelectStatement.GREATER_THAN_OR_EQUAL
0967: .equals(nextColumnComparison)) {
0968: //
0969: constructCompare(customWhere, inParameterTypes,
0970: inParameterValues, nextColumnTable,
0971: nextColumnMetaType, nextColumnName,
0972: nextColumnDataType, ">=",
0973: nextColumnAlternateValue);
0974: } else if (SelectStatement.NOT_EQUAL
0975: .equals(nextColumnComparison)) {
0976: //
0977: constructCompare(customWhere, inParameterTypes,
0978: inParameterValues, nextColumnTable,
0979: nextColumnMetaType, nextColumnName,
0980: nextColumnDataType, "<>",
0981: nextColumnAlternateValue);
0982: } else if (SelectStatement.LESS_THAN
0983: .equals(nextColumnComparison)) {
0984: //
0985: constructCompare(customWhere, inParameterTypes,
0986: inParameterValues, nextColumnTable,
0987: nextColumnMetaType, nextColumnName,
0988: nextColumnDataType, "<",
0989: nextColumnAlternateValue);
0990: } else if (SelectStatement.LESS_THAN_OR_EQUAL
0991: .equals(nextColumnComparison)) {
0992: //
0993: constructCompare(customWhere, inParameterTypes,
0994: inParameterValues, nextColumnTable,
0995: nextColumnMetaType, nextColumnName,
0996: nextColumnDataType, "<=",
0997: nextColumnAlternateValue);
0998: } else if (SelectStatement.LIKE
0999: .equals(nextColumnComparison)) {
1000: //
1001: constructCompare(customWhere, inParameterTypes,
1002: inParameterValues, nextColumnTable,
1003: nextColumnMetaType, nextColumnName,
1004: nextColumnDataType, "LIKE",
1005: nextColumnAlternateValue);
1006: } else if (SelectStatement.NOT_LIKE
1007: .equals(nextColumnComparison)) {
1008: //
1009: constructCompare(customWhere, inParameterTypes,
1010: inParameterValues, nextColumnTable,
1011: nextColumnMetaType, nextColumnName,
1012: nextColumnDataType, "NOT LIKE",
1013: nextColumnAlternateValue);
1014: } else if (SelectStatement.IS_NULL
1015: .equals(nextColumnComparison)) {
1016: //
1017: constructCompare(customWhere, inParameterTypes,
1018: inParameterValues, nextColumnTable,
1019: nextColumnMetaType, nextColumnName,
1020: nextColumnDataType, "IS NULL", null);
1021: } else if (SelectStatement.IS_NOT_NULL
1022: .equals(nextColumnComparison)) {
1023: //
1024: constructCompare(customWhere, inParameterTypes,
1025: inParameterValues, nextColumnTable,
1026: nextColumnMetaType, nextColumnName,
1027: nextColumnDataType, "IS NOT NULL", null);
1028: } else if (SelectStatement.IN
1029: .equals(nextColumnComparison)) {
1030: //
1031: if (nextColumnAlternateValue instanceof ArrayList) {
1032: ArrayList compareList = (ArrayList) nextColumnAlternateValue;
1033: ArrayList paramList = new ArrayList();
1034: for (int j = 0; j < compareList.size(); j++) {
1035: paramList.add("?");
1036: //MiscHelper.println("HSQLStore2 inDataType = "
1037: // + nextColumnDataType);
1038: inParameterTypes.add(nextColumnDataType);
1039: inParameterValues.add(compareList.get(j));
1040: }
1041: customWhere
1042: .append(getActualTableName(nextColumnTable
1043: .getName()));
1044: customWhere.append(".");
1045: //
1046: if (nextColumnMetaType
1047: .equals(SystemStatement.NORMAL_COLUMN)) {
1048:
1049: customWhere.append(nextColumnName);
1050: } else if (nextColumnMetaType
1051: .equals(SystemStatement.ID_COLUMN)) {
1052:
1053: customWhere.append(ID_PK);
1054: } else if (nextColumnMetaType
1055: .equals(SystemStatement.SERIES_SEQUENCE_COLUMN)) {
1056:
1057: customWhere.append(SERIES_SEQUENCE);
1058: } else {
1059: throw UnexpectedSystemException
1060: .unknownState();
1061: }
1062: //
1063: //customWhere.append(nextColumnName);
1064: customWhere.append(" IN (");
1065: customWhere.append(convertToList(paramList));
1066: customWhere.append(")");
1067: } else if (nextColumnAlternateValue instanceof SelectStatement) {
1068: //
1069: customWhere
1070: .append(getActualTableName(nextColumnTable
1071: .getName()));
1072: customWhere.append(".");
1073: //
1074: if (nextColumnMetaType
1075: .equals(SystemStatement.NORMAL_COLUMN)) {
1076:
1077: customWhere.append(nextColumnName);
1078: } else if (nextColumnMetaType
1079: .equals(SystemStatement.ID_COLUMN)) {
1080:
1081: customWhere.append(ID_PK);
1082: } else if (nextColumnMetaType
1083: .equals(SystemStatement.SERIES_SEQUENCE_COLUMN)) {
1084:
1085: customWhere.append(SERIES_SEQUENCE);
1086: } else {
1087: throw UnexpectedSystemException
1088: .unknownState();
1089: }
1090: //
1091: //customWhere.append(nextColumnName);
1092: customWhere.append(" IN (");
1093: //
1094: customWhere.append("\n");
1095: createSelectStatement(
1096: (SelectStatement) nextColumnAlternateValue,
1097: customWhere, inParameterTypes,
1098: inParameterValues, inIndent + 4, false);
1099: customWhere.append(MiscHelper.fixString("",
1100: inIndent));
1101: //
1102: customWhere.append(")");
1103: } else {
1104: throw UnexpectedSystemException.unknownState();
1105: }
1106: } else if (SelectStatement.NOT_IN
1107: .equals(nextColumnComparison)) {
1108: //
1109: } else {
1110: throw UnexpectedSystemException.notImplemented();
1111: }
1112: //
1113: //
1114: } else {
1115: throw UnexpectedSystemException.unknownState();
1116: }
1117: //
1118: customWhere.append("\n");
1119: }
1120: //
1121: if (customWhere.length() > 0) {
1122: customWhere.append(convertToParenthesis(inWws
1123: .getLastWhereColumnParenthesis()));
1124: } else {
1125: customWhere = null;
1126: }
1127:
1128: //
1129: StringBuffer statement = new StringBuffer();
1130: if (foreignKeyList.size() > 0 && customWhere != null) {
1131: statement.append(MiscHelper.fixString("", inIndent));
1132: statement.append("WHERE ("
1133: + convertToList(foreignKeyList, " AND ")
1134: + ") AND (" + customWhere + ")");
1135: statement.append("\n");
1136: } else if (foreignKeyList.size() > 0 && customWhere == null) {
1137: statement.append(MiscHelper.fixString("", inIndent));
1138: statement.append("WHERE "
1139: + convertToList(foreignKeyList, " AND "));
1140: statement.append("\n");
1141: } else if (foreignKeyList.size() == 0 && customWhere != null) {
1142: statement.append(MiscHelper.fixString("", inIndent));
1143: statement.append("WHERE " + customWhere);
1144: statement.append("\n");
1145: } else {
1146: //do nothing
1147: statement = null;
1148: }
1149:
1150: //
1151: if (statement != null) {
1152: outValue = statement.toString();
1153: } else {
1154: outValue = null;
1155: }
1156:
1157: return outValue;
1158: }
1159:
1160: private BigDecimal getNextSeriesSequence(String inName,
1161: Connection inConn) throws SQLException {
1162:
1163: BigDecimal outValue = null;
1164:
1165: ResultSet rs = null;
1166: PreparedStatement ps[] = new PreparedStatement[1];
1167:
1168: try {
1169: startStatement();
1170:
1171: simpleAndPrint("SELECT NEXT VALUE FOR "
1172: + getActualSeriesSequenceName(inName)
1173: + " FROM SYSTEM_SEQUENCES");
1174:
1175: rs = endStatement(inConn, null, null, ps, null, false);
1176:
1177: if (rs.next()) {
1178: outValue = new BigDecimal(rs.getLong(1));
1179: } else {
1180: throw UnexpectedSystemException.unknownState();
1181: }
1182: } finally {
1183: closeSQLObjects(null, ps[0], rs);
1184: }
1185:
1186: return outValue;
1187: }
1188:
1189: private String getConstraintName(String inTable) {
1190:
1191: StringBuffer outValue = new StringBuffer();
1192:
1193: //
1194: outValue.append("CON__");
1195: outValue.append(getActualTableName(inTable));
1196: outValue.append("__");
1197: outValue.append(this .constraintCount);
1198: //
1199: this.constraintCount++;
1200:
1201: return outValue.toString();
1202: }
1203: }
|