0001: /*
0002: * JBoss, Home of Professional Open Source.
0003: * Copyright 2006, Red Hat Middleware LLC, and individual contributors
0004: * as indicated by the @author tags. See the copyright.txt file in the
0005: * distribution for a full listing of individual contributors.
0006: *
0007: * This is free software; you can redistribute it and/or modify it
0008: * under the terms of the GNU Lesser General Public License as
0009: * published by the Free Software Foundation; either version 2.1 of
0010: * the License, or (at your option) any later version.
0011: *
0012: * This software is distributed in the hope that it will be useful,
0013: * but WITHOUT ANY WARRANTY; without even the implied warranty of
0014: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
0015: * Lesser General Public License for more details.
0016: *
0017: * You should have received a copy of the GNU Lesser General Public
0018: * License along with this software; if not, write to the Free
0019: * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
0020: * 02110-1301 USA, or see the FSF site: http://www.fsf.org.
0021: */
0022: package org.jboss.ejb.plugins.cmp.jdbc;
0023:
0024: import java.sql.Connection;
0025: import java.sql.DatabaseMetaData;
0026: import javax.sql.DataSource;
0027: import java.sql.SQLException;
0028: import java.sql.Statement;
0029: import java.sql.ResultSet;
0030: import java.util.zip.CRC32;
0031: import java.util.ArrayList;
0032:
0033: import org.jboss.deployment.DeploymentException;
0034: import org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCEntityBridge;
0035: import org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCFieldBridge;
0036: import org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCAbstractEntityBridge;
0037: import org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCAbstractCMRFieldBridge;
0038: import org.jboss.logging.Logger;
0039:
0040: import java.util.Vector;
0041:
0042: /**
0043: * SQLUtil helps with building sql statements.
0044: *
0045: * @author <a href="mailto:dain@daingroup.com">Dain Sundstrom</a>
0046: * @author <a href="mailto:alex@jboss.org">Alex Loubyansky</a>
0047: * @author <a href="joachim@cabsoft.be">Joachim Van der Auwera</a>
0048: * @version $Revision: 63348 $
0049: */
0050: public final class SQLUtil {
0051: public static final String EMPTY_STRING = "";
0052: public static final String INSERT_INTO = "INSERT INTO ";
0053: public static final String VALUES = " VALUES ";
0054: public static final String SELECT = "SELECT ";
0055: public static final String DISTINCT = "DISTINCT ";
0056: public static final String FROM = " FROM ";
0057: public static final String WHERE = " WHERE ";
0058: public static final String ORDERBY = " ORDER BY ";
0059: public static final String DELETE_FROM = "DELETE FROM ";
0060: public static final String AND = " AND ";
0061: public static final String OR = " OR ";
0062: public static final String NOT = " NOT ";
0063: public static final String EXISTS = "EXISTS ";
0064: public static final String COMMA = ", ";
0065: public static final String LEFT_JOIN = " LEFT JOIN ";
0066: public static final String LEFT_OUTER_JOIN = " LEFT OUTER JOIN ";
0067: public static final String ON = " ON ";
0068: public static final String NOT_EQUAL = "<>";
0069: public static final String CREATE_TABLE = "CREATE TABLE ";
0070: public static final String DROP_TABLE = "DROP TABLE ";
0071: public static final String CREATE_INDEX = "CREATE INDEX ";
0072: public static final String NULL = "NULL";
0073: public static final String IS = " IS ";
0074: public static final String IN = " IN ";
0075: public static final String EMPTY = "EMPTY";
0076: public static final String BETWEEN = " BETWEEN ";
0077: public static final String LIKE = " LIKE ";
0078: public static final String MEMBER_OF = " MEMBER OF ";
0079: public static final String CONCAT = "CONCAT";
0080: public static final String SUBSTRING = "SUBSTRING";
0081: public static final String LCASE = "LCASE";
0082: public static final String UCASE = "UCASE";
0083: public static final String LENGTH = "LENGTH";
0084: public static final String LOCATE = "LOCATE";
0085: public static final String ABS = "ABS";
0086: public static final String MOD = "MOD";
0087: public static final String SQRT = "SQRT";
0088: public static final String COUNT = "COUNT";
0089: public static final String MAX = "MAX";
0090: public static final String MIN = "MIN";
0091: public static final String AVG = "AVG";
0092: public static final String SUM = "SUM";
0093: public static final String ASC = " ASC";
0094: public static final String DESC = " DESC";
0095: public static final String OFFSET = " OFFSET ";
0096: public static final String LIMIT = " LIMIT ";
0097: public static final String UPDATE = "UPDATE ";
0098: public static final String SET = " SET ";
0099: public static final String TYPE = " TYPE ";
0100: private static final String DOT = ".";
0101:
0102: private static final String EQ_QUESTMARK = "=?";
0103:
0104: private static final Vector rwords = new Vector();
0105:
0106: public static String getTableNameWithoutSchema(String tableName) {
0107: final int dot = tableName.indexOf('.');
0108: if (dot != -1) {
0109: char firstChar = tableName.charAt(0);
0110: tableName = tableName.substring(dot + 1);
0111: if (firstChar == '"' || firstChar == '\'') {
0112: tableName = firstChar + tableName;
0113: }
0114: }
0115: return tableName;
0116: }
0117:
0118: public static String getSchema(String tableName) {
0119: String schema = null;
0120: final int dot = tableName.indexOf('.');
0121: if (dot != -1) {
0122: char firstChar = tableName.charAt(0);
0123: final boolean quoted = firstChar == '"'
0124: || firstChar == '\'';
0125: schema = tableName.substring(quoted ? 1 : 0, dot);
0126: }
0127: return schema;
0128: }
0129:
0130: public static String fixTableName(String tableName,
0131: DataSource dataSource) throws DeploymentException {
0132: // don't fix the quited table name
0133: char firstChar = tableName.charAt(0);
0134: if (firstChar == '"' || firstChar == '\'') {
0135: return tableName;
0136: }
0137:
0138: // Separate schema name and table name
0139: String strSchema = "";
0140: int iIndex;
0141: if ((iIndex = tableName.indexOf('.')) != -1) {
0142: strSchema = tableName.substring(0, iIndex);
0143: tableName = tableName.substring(iIndex + 1);
0144: }
0145:
0146: // check for SQL reserved word and escape it with prepending a "X"
0147: // IMHO one should reject reserved words and throw a
0148: // DeploymentException - pilhuhn
0149: if (rwords != null) {
0150: for (int i = 0; i < rwords.size(); i++) {
0151: if (((String) rwords.elementAt(i))
0152: .equalsIgnoreCase(tableName)) {
0153: tableName = "X" + tableName;
0154: break;
0155: }
0156: }
0157: }
0158:
0159: Connection con = null;
0160: try {
0161: con = dataSource.getConnection();
0162: DatabaseMetaData dmd = con.getMetaData();
0163:
0164: // fix length
0165: int maxLength = dmd.getMaxTableNameLength();
0166: if (maxLength > 0 && tableName.length() > maxLength) {
0167: CRC32 crc = new CRC32();
0168: crc.update(tableName.getBytes());
0169: String nameCRC = Long.toString(crc.getValue(), 36);
0170:
0171: tableName = tableName.substring(0, maxLength
0172: - nameCRC.length() - 2);
0173: tableName += "_" + nameCRC;
0174: }
0175:
0176: // fix case
0177: if (dmd.storesLowerCaseIdentifiers()) {
0178: tableName = tableName.toLowerCase();
0179: } else if (dmd.storesUpperCaseIdentifiers()) {
0180: tableName = tableName.toUpperCase();
0181: }
0182: // now put the schema name back on the table name
0183: if (strSchema.length() > 0) {
0184: tableName = strSchema + "." + tableName;
0185: }
0186: return tableName;
0187: } catch (SQLException e) {
0188: // This should not happen. A J2EE compatiable JDBC driver is
0189: // required fully support metadata.
0190: throw new DeploymentException(
0191: "Error while fixing table name", e);
0192: } finally {
0193: JDBCUtil.safeClose(con);
0194: }
0195: }
0196:
0197: public static void addToRwords(String word) {
0198: if (!rwords.contains(word))
0199: rwords.add(word);
0200: }
0201:
0202: public static String fixConstraintName(String name,
0203: DataSource dataSource) throws DeploymentException {
0204: return fixTableName(name, dataSource).replace('.', '_');
0205: }
0206:
0207: // =======================================================================
0208: // Create Table Columns Clause
0209: // columnName0 sqlType0
0210: // [, columnName1 sqlType0
0211: // [, columnName2 sqlType0 [...]]]
0212: // =======================================================================
0213: public static String getCreateTableColumnsClause(
0214: JDBCFieldBridge[] fields) {
0215: StringBuffer buf = new StringBuffer(100);
0216: boolean comma = false;
0217: for (int i = 0; i < fields.length; ++i) {
0218: JDBCType type = getJDBCType(fields[i]);
0219: if (type != null) {
0220: if (comma)
0221: buf.append(COMMA);
0222: else
0223: comma = true;
0224: buf.append(getCreateTableColumnsClause(type));
0225: }
0226: }
0227: return buf.toString();
0228: }
0229:
0230: /**
0231: * Returns columnName0 sqlType0
0232: * [, columnName1 sqlType0
0233: * [, columnName2 sqlType0 [...]]]
0234: */
0235: public static String getCreateTableColumnsClause(JDBCType type) {
0236: String[] columnNames = type.getColumnNames();
0237: String[] sqlTypes = type.getSQLTypes();
0238: boolean[] notNull = type.getNotNull();
0239:
0240: StringBuffer buf = new StringBuffer();
0241: for (int i = 0; i < columnNames.length; i++) {
0242: if (i != 0)
0243: buf.append(COMMA);
0244: buf.append(columnNames[i]).append(' ').append(sqlTypes[i]);
0245: if (notNull[i])
0246: buf.append(NOT).append(NULL);
0247: }
0248: return buf.toString();
0249: }
0250:
0251: // =======================================================================
0252: // Column Names Clause
0253: // columnName0 [, columnName1 [AND columnName2 [...]]]
0254: // =======================================================================
0255:
0256: /**
0257: * Returns columnName0 [, columnName1 [AND columnName2 [...]]]
0258: */
0259: public static StringBuffer getColumnNamesClause(
0260: JDBCFieldBridge[] fields, StringBuffer sb) {
0261: return getColumnNamesClause(fields, "", sb);
0262: }
0263:
0264: /**
0265: * Returns columnName0 [, columnName1 [AND columnName2 [...]]]
0266: */
0267: public static StringBuffer getColumnNamesClause(
0268: JDBCFieldBridge[] fields, String identifier,
0269: StringBuffer buf) {
0270: boolean comma = false;
0271: for (int i = 0; i < fields.length; ++i) {
0272: JDBCType type = getJDBCType(fields[i]);
0273: if (type != null) {
0274: if (comma)
0275: buf.append(COMMA);
0276: else
0277: comma = true;
0278: getColumnNamesClause(type, identifier, buf);
0279: }
0280: }
0281: return buf;
0282: }
0283:
0284: /**
0285: * Returns columnName0 [, columnName1 [AND columnName2 [...]]]
0286: */
0287: public static StringBuffer getSearchableColumnNamesClause(
0288: JDBCFieldBridge[] fields, String identifier,
0289: StringBuffer buf) {
0290: boolean comma = false;
0291: for (int i = 0; i < fields.length; ++i) {
0292: JDBCType type = getJDBCType(fields[i]);
0293: if (type != null && type.isSearchable()) {
0294: if (comma)
0295: buf.append(COMMA);
0296: else
0297: comma = true;
0298: getColumnNamesClause(type, identifier, buf);
0299: }
0300: }
0301: return buf;
0302: }
0303:
0304: /**
0305: * Returns columnName0 [, columnName1 [AND columnName2 [...]]]
0306: */
0307: public static StringBuffer getColumnNamesClause(
0308: JDBCEntityBridge.FieldIterator loadIter, StringBuffer sb) {
0309: if (loadIter.hasNext())
0310: getColumnNamesClause(loadIter.next(), sb);
0311: while (loadIter.hasNext()) {
0312: sb.append(COMMA);
0313: getColumnNamesClause(loadIter.next(), sb);
0314: }
0315: return sb;
0316: }
0317:
0318: /**
0319: * Returns columnName0 [, columnName1 [, columnName2 [...]]]
0320: */
0321: public static StringBuffer getColumnNamesClause(
0322: JDBCFieldBridge field, StringBuffer sb) {
0323: return getColumnNamesClause(field.getJDBCType(), sb);
0324: }
0325:
0326: /**
0327: * Returns identifier.columnName0
0328: * [, identifier.columnName1
0329: * [, identifier.columnName2 [...]]]
0330: */
0331: public static StringBuffer getColumnNamesClause(
0332: JDBCFieldBridge field, String identifier, StringBuffer sb) {
0333: return getColumnNamesClause(field.getJDBCType(), identifier, sb);
0334: }
0335:
0336: /**
0337: * Returns identifier.columnName0
0338: * [, identifier.columnName1
0339: * [, identifier.columnName2 [...]]]
0340: */
0341: private static StringBuffer getColumnNamesClause(JDBCType type,
0342: String identifier, StringBuffer buf) {
0343: String[] columnNames = type.getColumnNames();
0344: boolean hasIdentifier = identifier.length() > 0;
0345: if (hasIdentifier)
0346: buf.append(identifier).append(DOT);
0347: buf.append(columnNames[0]);
0348: int i = 1;
0349: while (i < columnNames.length) {
0350: buf.append(COMMA);
0351: if (hasIdentifier)
0352: buf.append(identifier).append(DOT);
0353: buf.append(columnNames[i++]);
0354: }
0355: return buf;
0356: }
0357:
0358: /**
0359: * Returns ', columnName0 [, columnName1 [AND columnName2 [...]]]'
0360: */
0361: public static StringBuffer appendColumnNamesClause(
0362: JDBCAbstractEntityBridge entity, String eagerLoadGroup,
0363: StringBuffer sb) {
0364: return appendColumnNamesClause(entity, eagerLoadGroup, "", sb);
0365: }
0366:
0367: /**
0368: * Returns ', columnName0 [, columnName1 [AND columnName2 [...]]]'
0369: */
0370: public static StringBuffer appendColumnNamesClause(
0371: JDBCAbstractEntityBridge entity, String eagerLoadGroup,
0372: String alias, StringBuffer sb) {
0373: return appendColumnNamesClause(entity.getTableFields(), entity
0374: .getLoadGroupMask(eagerLoadGroup), alias, sb);
0375: }
0376:
0377: /**
0378: * Returns ', columnName0 [, columnName1 [AND columnName2 [...]]]'
0379: */
0380: public static StringBuffer appendColumnNamesClause(
0381: JDBCFieldBridge[] fields, boolean[] mask,
0382: String identifier, StringBuffer buf) {
0383: for (int i = 0; i < fields.length; ++i) {
0384: if (mask[i]) {
0385: JDBCType type = getJDBCType(fields[i]);
0386: if (type != null) {
0387: buf.append(COMMA);
0388: getColumnNamesClause(type, identifier, buf);
0389: }
0390: }
0391: }
0392: return buf;
0393: }
0394:
0395: /**
0396: * Returns ', columnName0 [, columnName1 [AND columnName2 [...]]]'
0397: */
0398: public static StringBuffer appendColumnNamesClause(
0399: JDBCFieldBridge[] fields, String identifier,
0400: StringBuffer buf) {
0401: for (int i = 0; i < fields.length; ++i) {
0402: JDBCType type = getJDBCType(fields[i]);
0403: if (type != null) {
0404: buf.append(COMMA);
0405: getColumnNamesClause(type, identifier, buf);
0406: }
0407: }
0408: return buf;
0409: }
0410:
0411: /**
0412: * Returns identifier.columnName0
0413: * [, identifier.columnName1
0414: * [, identifier.columnName2 [...]]]
0415: */
0416: private static StringBuffer getColumnNamesClause(JDBCType type,
0417: StringBuffer buf) {
0418: String[] columnNames = type.getColumnNames();
0419: buf.append(columnNames[0]);
0420: int i = 1;
0421: while (i < columnNames.length) {
0422: buf.append(COMMA).append(columnNames[i++]);
0423: }
0424: return buf;
0425: }
0426:
0427: // =======================================================================
0428: // Set Clause
0429: // columnName0=? [, columnName1=? [, columnName2=? [...]]]
0430: // =======================================================================
0431:
0432: /**
0433: * Returns columnName0=? [, columnName1=? [, columnName2=? [...]]]
0434: */
0435: public static StringBuffer getSetClause(
0436: JDBCEntityBridge.FieldIterator fieldsIter, StringBuffer buf) {
0437: JDBCType type = getJDBCType(fieldsIter.next());
0438: getSetClause(type, buf);
0439: while (fieldsIter.hasNext()) {
0440: type = getJDBCType(fieldsIter.next());
0441: buf.append(COMMA);
0442: getSetClause(type, buf);
0443: }
0444: return buf;
0445: }
0446:
0447: /**
0448: * Returns columnName0=? [, columnName1=? [, columnName2=? [...]]]
0449: */
0450: private static StringBuffer getSetClause(JDBCType type,
0451: StringBuffer buf) {
0452: String[] columnNames = type.getColumnNames();
0453: buf.append(columnNames[0]).append(EQ_QUESTMARK);
0454: int i = 1;
0455: while (i < columnNames.length) {
0456: buf.append(COMMA).append(columnNames[i++]).append(
0457: EQ_QUESTMARK);
0458: }
0459: return buf;
0460: }
0461:
0462: // =======================================================================
0463: // Values Clause
0464: // ? [, ? [, ? [...]]]
0465: // =======================================================================
0466:
0467: /**
0468: * Returns ? [, ? [, ? [...]]]
0469: */
0470: public static StringBuffer getValuesClause(
0471: JDBCFieldBridge[] fields, StringBuffer buf) {
0472: boolean comma = false;
0473: for (int i = 0; i < fields.length; ++i) {
0474: JDBCType type = getJDBCType(fields[i]);
0475: if (type != null) {
0476: if (comma)
0477: buf.append(COMMA);
0478: else
0479: comma = true;
0480: getValuesClause(type, buf);
0481: }
0482: }
0483: return buf;
0484: }
0485:
0486: /**
0487: * Returns ? [, ? [, ? [...]]]
0488: */
0489: private static StringBuffer getValuesClause(JDBCType type,
0490: StringBuffer buf) {
0491: int columnCount = type.getColumnNames().length;
0492: buf.append('?');
0493: int i = 1;
0494: while (i++ < columnCount)
0495: buf.append(COMMA).append('?');
0496: return buf;
0497: }
0498:
0499: // =======================================================================
0500: // Where Clause
0501: // columnName0=? [AND columnName1=? [AND columnName2=? [...]]]
0502: // =======================================================================
0503:
0504: /**
0505: * Returns columnName0=? [AND columnName1=? [AND columnName2=? [...]]]
0506: */
0507: public static StringBuffer getWhereClause(JDBCFieldBridge[] fields,
0508: StringBuffer buf) {
0509: return getWhereClause(fields, "", buf);
0510: }
0511:
0512: /**
0513: * Returns identifier.columnName0=?
0514: * [AND identifier.columnName1=?
0515: * [AND identifier.columnName2=? [...]]]
0516: */
0517: public static StringBuffer getWhereClause(JDBCFieldBridge[] fields,
0518: String identifier, StringBuffer buf) {
0519: boolean and = false;
0520: for (int i = 0; i < fields.length; ++i) {
0521: JDBCType type = getJDBCType(fields[i]);
0522: if (type != null) {
0523: if (and)
0524: buf.append(AND);
0525: else
0526: and = true;
0527: getWhereClause(type, identifier, buf);
0528: }
0529: }
0530: return buf;
0531: }
0532:
0533: /**
0534: * Returns columnName0=? [AND columnName1=? [AND columnName2=? [...]]]
0535: */
0536: public static StringBuffer getWhereClause(JDBCFieldBridge[] fields,
0537: long mask, StringBuffer buf) {
0538: return getWhereClause(fields, mask, "", buf);
0539: }
0540:
0541: /**
0542: * Returns columnName0=? [AND columnName1=? [AND columnName2=? [...]]]
0543: */
0544: private static StringBuffer getWhereClause(
0545: JDBCFieldBridge[] fields, long mask, String identifier,
0546: StringBuffer buf) {
0547: boolean and = false;
0548: long fieldMask = 1;
0549: for (int i = 0; i < fields.length; ++i) {
0550: if ((fieldMask & mask) > 0) {
0551: JDBCType type = getJDBCType(fields[i]);
0552: if (type != null) {
0553: if (and)
0554: buf.append(AND);
0555: else
0556: and = true;
0557: getWhereClause(type, identifier, buf);
0558: }
0559: }
0560: fieldMask <<= 1;
0561: }
0562: return buf;
0563: }
0564:
0565: /**
0566: * Returns columnName0=? [AND columnName1=? [AND columnName2=? [...]]]
0567: */
0568: public static StringBuffer getWhereClause(JDBCFieldBridge field,
0569: StringBuffer buf) {
0570: return getWhereClause(field.getJDBCType(), "", buf);
0571: }
0572:
0573: /**
0574: * Returns identifier.columnName0=?
0575: * [AND identifier.columnName1=?
0576: * [AND identifier.columnName2=? [...]]]
0577: */
0578: public static StringBuffer getWhereClause(JDBCType type,
0579: String identifier, StringBuffer buf) {
0580: if (identifier.length() > 0) {
0581: identifier += '.';
0582: }
0583:
0584: String[] columnNames = type.getColumnNames();
0585: buf.append(identifier).append(columnNames[0]).append(
0586: EQ_QUESTMARK);
0587: int i = 1;
0588: while (i < columnNames.length) {
0589: buf.append(AND).append(identifier).append(columnNames[i++])
0590: .append(EQ_QUESTMARK);
0591: }
0592: return buf;
0593: }
0594:
0595: /**
0596: * Returns identifier.columnName0{comparison}?
0597: * [AND identifier.columnName1{comparison}?
0598: * [AND identifier.columnName2{comparison}? [...]]]
0599: */
0600: public static StringBuffer getWhereClause(JDBCType type,
0601: String identifier, String comparison, StringBuffer buf) {
0602: if (identifier.length() > 0) {
0603: identifier += '.';
0604: }
0605:
0606: String[] columnNames = type.getColumnNames();
0607: buf.append(identifier).append(columnNames[0])
0608: .append(comparison).append('?');
0609: int i = 1;
0610: while (i < columnNames.length) {
0611: buf.append(AND).append(identifier).append(columnNames[i++])
0612: .append(comparison).append('?');
0613: }
0614: return buf;
0615: }
0616:
0617: // =======================================================================
0618: // Is [Not] Null Clause
0619: // columnName0 IS [NOT] NULL [AND columnName1 IS [NOT] NULL [...]]
0620: // =======================================================================
0621:
0622: /**
0623: * Returns identifier.columnName0 IS [NOT] NULL
0624: * [AND identifier.columnName1 IS [NOT] NULL
0625: * [AND identifier.columnName2 IS [NOT] NULL [...]]]
0626: */
0627: public static StringBuffer getIsNullClause(boolean not,
0628: JDBCFieldBridge[] fields, String identifier,
0629: StringBuffer buf) {
0630: boolean and = false;
0631: for (int i = 0; i < fields.length; ++i) {
0632: JDBCType type = getJDBCType(fields[i]);
0633: if (type != null) {
0634: if (and)
0635: buf.append(AND);
0636: else
0637: and = true;
0638: getIsNullClause(not, type, identifier, buf);
0639: }
0640: }
0641: return buf;
0642: }
0643:
0644: /**
0645: * Returns identifier.columnName0 IS [NOT] NULL
0646: * [AND identifier.columnName1 IS [NOT] NULL
0647: * [AND identifier.columnName2 IS [NOT] NULL [...]]]
0648: */
0649: public static StringBuffer getIsNullClause(boolean not,
0650: JDBCFieldBridge field, String identifier, StringBuffer buf) {
0651: return getIsNullClause(not, field.getJDBCType(), identifier,
0652: buf);
0653: }
0654:
0655: /**
0656: * Returns identifier.columnName0 IS [NOT] NULL
0657: * [AND identifier.columnName1 IS [NOT] NULL
0658: * [AND identifier.columnName2 IS [NOT] NULL [...]]]
0659: */
0660: private static StringBuffer getIsNullClause(boolean not,
0661: JDBCType type, String identifier, StringBuffer buf) {
0662: if (identifier.length() > 0) {
0663: identifier += '.';
0664: }
0665:
0666: String[] columnNames = type.getColumnNames();
0667:
0668: buf.append(identifier).append(columnNames[0]).append(IS);
0669: (not ? buf.append(NOT) : buf).append(NULL);
0670: int i = 1;
0671: while (i < columnNames.length) {
0672: buf.append(AND).append(identifier).append(columnNames[i++])
0673: .append(IS);
0674: (not ? buf.append(NOT) : buf).append(NULL);
0675: }
0676: return buf;
0677: }
0678:
0679: // =======================================================================
0680: // Join Clause
0681: // parent.pkColumnName0=child.fkColumnName0
0682: // [AND parent.pkColumnName1=child.fkColumnName1
0683: // [AND parent.pkColumnName2=child.fkColumnName2 [...]]]
0684: // =======================================================================
0685:
0686: public static StringBuffer getJoinClause(
0687: JDBCAbstractCMRFieldBridge cmrField, String parentAlias,
0688: String childAlias, StringBuffer buf) {
0689: JDBCAbstractEntityBridge parentEntity = cmrField.getEntity();
0690: JDBCAbstractEntityBridge childEntity = (JDBCAbstractEntityBridge) cmrField
0691: .getRelatedEntity();
0692:
0693: JDBCFieldBridge parentField;
0694: JDBCFieldBridge childField;
0695:
0696: if (cmrField.hasForeignKey()) {
0697: // parent has the foreign keys
0698: JDBCFieldBridge[] parentFkFields = cmrField
0699: .getForeignKeyFields();
0700: int i = 0;
0701: while (i < parentFkFields.length) {
0702: parentField = parentFkFields[i++];
0703: childField = (JDBCFieldBridge) childEntity
0704: .getFieldByName(parentField.getFieldName());
0705: getJoinClause(parentField, parentAlias, childField,
0706: childAlias, buf);
0707: if (i < parentFkFields.length)
0708: buf.append(AND);
0709: }
0710: } else {
0711: // child has the foreign keys
0712: JDBCFieldBridge[] childFkFields = cmrField
0713: .getRelatedCMRField().getForeignKeyFields();
0714: int i = 0;
0715: while (i < childFkFields.length) {
0716: childField = childFkFields[i++];
0717: parentField = (JDBCFieldBridge) parentEntity
0718: .getFieldByName(childField.getFieldName());
0719:
0720: // add the sql
0721: getJoinClause(parentField, parentAlias, childField,
0722: childAlias, buf);
0723: if (i < childFkFields.length) {
0724: buf.append(AND);
0725: }
0726: }
0727: }
0728: return buf;
0729: }
0730:
0731: public static StringBuffer getRelationTableJoinClause(
0732: JDBCAbstractCMRFieldBridge cmrField, String parentAlias,
0733: String relationTableAlias, StringBuffer buf) {
0734: JDBCAbstractEntityBridge parentEntity = cmrField.getEntity();
0735: JDBCFieldBridge parentField;
0736: JDBCFieldBridge relationField;
0737:
0738: // parent to relation table join
0739: JDBCFieldBridge[] parentFields = cmrField.getTableKeyFields();
0740: int i = 0;
0741: while (i < parentFields.length) {
0742: relationField = parentFields[i++];
0743: parentField = (JDBCFieldBridge) parentEntity
0744: .getFieldByName(relationField.getFieldName());
0745: getJoinClause(parentField, parentAlias, relationField,
0746: relationTableAlias, buf);
0747: if (i < parentFields.length)
0748: buf.append(AND);
0749: }
0750: return buf;
0751: }
0752:
0753: /**
0754: * Returns parent.pkColumnName0=child.fkColumnName0
0755: * [AND parent.pkColumnName1=child.fkColumnName1
0756: * [AND parent.pkColumnName2=child.fkColumnName2 [...]]]
0757: */
0758: private static StringBuffer getJoinClause(JDBCFieldBridge pkField,
0759: String parent, JDBCFieldBridge fkField, String child,
0760: StringBuffer buf) {
0761: return getJoinClause(pkField.getJDBCType(), parent, fkField
0762: .getJDBCType(), child, buf);
0763: }
0764:
0765: public static StringBuffer getJoinClause(
0766: JDBCFieldBridge[] pkFields, String parent,
0767: JDBCFieldBridge[] fkFields, String child, StringBuffer buf) {
0768: if (pkFields.length != fkFields.length) {
0769: throw new IllegalArgumentException(
0770: "Error createing theta join clause:"
0771: + " pkField.size()=" + pkFields.length
0772: + " fkField.size()=" + fkFields.length);
0773: }
0774:
0775: boolean and = false;
0776: for (int i = 0; i < pkFields.length; ++i) {
0777: // these types should not be null
0778: JDBCType pkType = getJDBCType(pkFields[i]);
0779: JDBCType fkType = getJDBCType(fkFields[i]);
0780: if (and)
0781: buf.append(AND);
0782: else
0783: and = true;
0784: getJoinClause(pkType, parent, fkType, child, buf);
0785: }
0786: return buf;
0787: }
0788:
0789: /**
0790: * Returns parent.pkColumnName0=child.fkColumnName0
0791: * [AND parent.pkColumnName1=child.fkColumnName1
0792: * [AND parent.pkColumnName2=child.fkColumnName2 [...]]]
0793: */
0794: private static StringBuffer getJoinClause(JDBCType pkType,
0795: String parent, JDBCType fkType, String child,
0796: StringBuffer buf) {
0797: if (parent.length() > 0) {
0798: parent += '.';
0799: }
0800: if (child.length() > 0) {
0801: child += '.';
0802: }
0803:
0804: String[] pkColumnNames = pkType.getColumnNames();
0805: String[] fkColumnNames = fkType.getColumnNames();
0806: if (pkColumnNames.length != fkColumnNames.length) {
0807: throw new IllegalArgumentException(
0808: "PK and FK have different number of columns");
0809: }
0810:
0811: buf.append(parent).append(pkColumnNames[0]).append('=').append(
0812: child).append(fkColumnNames[0]);
0813: int i = 1;
0814: while (i < pkColumnNames.length) {
0815: buf.append(AND).append(parent).append(pkColumnNames[i])
0816: .append('=').append(child).append(
0817: fkColumnNames[i++]);
0818: }
0819: return buf;
0820: }
0821:
0822: // =======================================================================
0823: // Self Compare Where Clause
0824: // fromIdentifier.pkColumnName0=toIdentifier.fkColumnName0
0825: // [AND fromIdentifier.pkColumnName1=toIdentifier.fkColumnName1
0826: // [AND fromIdentifier.pkColumnName2=toIdentifier.fkColumnName2 [...]]]
0827: // =======================================================================
0828:
0829: public static StringBuffer getSelfCompareWhereClause(
0830: JDBCFieldBridge[] fields, String fromIdentifier,
0831: String toIdentifier, StringBuffer buf) {
0832: boolean and = false;
0833: for (int i = 0; i < fields.length; ++i) {
0834: JDBCType type = getJDBCType(fields[i]);
0835: if (type != null) {
0836: if (and)
0837: buf.append(AND);
0838: else
0839: and = true;
0840: getSelfCompareWhereClause(type, fromIdentifier,
0841: toIdentifier, buf);
0842: }
0843: }
0844: return buf;
0845: }
0846:
0847: private static StringBuffer getSelfCompareWhereClause(
0848: JDBCType type, String fromIdentifier, String toIdentifier,
0849: StringBuffer buf) {
0850: if (fromIdentifier.length() > 0)
0851: fromIdentifier += '.';
0852: if (toIdentifier.length() > 0)
0853: toIdentifier += '.';
0854:
0855: String[] columnNames = type.getColumnNames();
0856:
0857: buf.append(fromIdentifier).append(columnNames[0]).append('=')
0858: .append(toIdentifier).append(columnNames[0]);
0859: int i = 1;
0860: while (i < columnNames.length) {
0861: buf.append(AND).append(fromIdentifier).append(
0862: columnNames[i]).append('=').append(toIdentifier)
0863: .append(columnNames[i++]);
0864: }
0865: return buf;
0866: }
0867:
0868: public static StringBuffer getSelfCompareWhereClause(
0869: JDBCFieldBridge fromField, JDBCFieldBridge toField,
0870: String fromIdentifier, String toIdentifier,
0871: String comparison, StringBuffer buf) {
0872: return getSelfCompareWhereClause(fromField.getJDBCType(),
0873: toField.getJDBCType(), fromIdentifier, toIdentifier,
0874: comparison, buf);
0875: }
0876:
0877: private static StringBuffer getSelfCompareWhereClause(
0878: JDBCType fromType, JDBCType toType, String fromIdentifier,
0879: String toIdentifier, String comparison, StringBuffer buf) {
0880: if (fromIdentifier.length() > 0)
0881: fromIdentifier += '.';
0882: if (toIdentifier.length() > 0)
0883: toIdentifier += '.';
0884:
0885: String[] fromColumnNames = fromType.getColumnNames();
0886: String[] toColumnNames = toType.getColumnNames();
0887:
0888: buf.append(fromIdentifier).append(fromColumnNames[0]).append(
0889: comparison).append(toIdentifier).append(
0890: toColumnNames[0]);
0891: int i = 1;
0892: while (i < fromColumnNames.length) {
0893: buf.append(AND).append(fromIdentifier).append(
0894: fromColumnNames[i]).append(comparison).append(
0895: toIdentifier).append(toColumnNames[i++]);
0896: }
0897: return buf;
0898: }
0899:
0900: public static boolean tableExists(String tableName,
0901: DataSource dataSource) throws DeploymentException {
0902: Connection con = null;
0903: ResultSet rs = null;
0904: try {
0905: con = dataSource.getConnection();
0906:
0907: // (a j2ee spec compatible jdbc driver has to fully
0908: // implement the DatabaseMetaData)
0909: DatabaseMetaData dmd = con.getMetaData();
0910: String catalog = con.getCatalog();
0911: String schema = null;
0912: String quote = dmd.getIdentifierQuoteString();
0913: if (tableName.startsWith(quote)) {
0914: if (tableName.endsWith(quote) == false) {
0915: throw new DeploymentException(
0916: "Mismatched quote in table name: "
0917: + tableName);
0918: }
0919: int quoteLength = quote.length();
0920: tableName = tableName.substring(quoteLength, tableName
0921: .length()
0922: - quoteLength);
0923: if (dmd.storesLowerCaseQuotedIdentifiers())
0924: tableName = tableName.toLowerCase();
0925: else if (dmd.storesUpperCaseQuotedIdentifiers())
0926: tableName = tableName.toUpperCase();
0927: } else {
0928: if (dmd.storesLowerCaseIdentifiers())
0929: tableName = tableName.toLowerCase();
0930: else if (dmd.storesUpperCaseIdentifiers())
0931: tableName = tableName.toUpperCase();
0932: }
0933:
0934: // Patch #927759: Split tablename into "schema" and "table" separated by '.'
0935: int dotIndex;
0936: if ((dotIndex = tableName.indexOf('.')) != -1) {
0937: // Yank out schema name ...
0938: schema = tableName.substring(0, dotIndex);
0939: tableName = tableName.substring(dotIndex + 1);
0940: }
0941:
0942: rs = dmd.getTables(catalog, schema, tableName, null);
0943: return rs.next();
0944: } catch (SQLException e) {
0945: // This should not happen. A J2EE compatiable JDBC driver is
0946: // required fully support metadata.
0947: throw new DeploymentException(
0948: "Error while checking if table aleady exists "
0949: + tableName, e);
0950: } finally {
0951: JDBCUtil.safeClose(rs);
0952: JDBCUtil.safeClose(con);
0953: }
0954: }
0955:
0956: public static OldColumns getOldColumns(String tableName,
0957: DataSource dataSource) throws DeploymentException {
0958: Connection con = null;
0959: ResultSet rs = null;
0960: ArrayList columnNames = new ArrayList();
0961: ArrayList typeNames = new ArrayList();
0962: ArrayList columnSizes = new ArrayList();
0963: try {
0964: con = dataSource.getConnection();
0965:
0966: // (a j2ee spec compatible jdbc driver has to fully
0967: // implement the DatabaseMetaData)
0968: DatabaseMetaData dmd = con.getMetaData();
0969: String catalog = con.getCatalog();
0970: String schema = null;
0971: String quote = dmd.getIdentifierQuoteString();
0972: if (tableName.startsWith(quote)) {
0973: if (tableName.endsWith(quote) == false) {
0974: throw new DeploymentException(
0975: "Mismatched quote in table name: "
0976: + tableName);
0977: }
0978: int quoteLength = quote.length();
0979: tableName = tableName.substring(quoteLength, tableName
0980: .length()
0981: - quoteLength);
0982: if (dmd.storesLowerCaseQuotedIdentifiers())
0983: tableName = tableName.toLowerCase();
0984: else if (dmd.storesUpperCaseQuotedIdentifiers())
0985: tableName = tableName.toUpperCase();
0986: } else {
0987: if (dmd.storesLowerCaseIdentifiers())
0988: tableName = tableName.toLowerCase();
0989: else if (dmd.storesUpperCaseIdentifiers())
0990: tableName = tableName.toUpperCase();
0991: }
0992:
0993: // Patch #927759: Split tablename into "schema" and "table" separated by '.'
0994: int dotIndex;
0995: if ((dotIndex = tableName.indexOf('.')) != -1) {
0996: // Yank out schema name ...
0997: schema = tableName.substring(0, dotIndex);
0998: tableName = tableName.substring(dotIndex + 1);
0999: }
1000:
1001: rs = dmd.getColumns(catalog, schema, tableName, null);
1002: while (rs.next()) {
1003: String columnName = rs.getString("COLUMN_NAME");
1004: columnNames.add(columnName == null ? null : columnName
1005: .toUpperCase());
1006: typeNames.add(rs.getString("TYPE_NAME"));
1007: columnSizes.add(new Integer(rs.getInt("COLUMN_SIZE")));
1008: }
1009: return new OldColumns(columnNames, typeNames, columnSizes);
1010:
1011: } catch (SQLException e) {
1012: // This should not happen. A J2EE compatiable JDBC driver is
1013: // required fully support metadata.
1014: throw new DeploymentException(
1015: "Error while geting column names", e);
1016: } finally {
1017: JDBCUtil.safeClose(rs);
1018: JDBCUtil.safeClose(con);
1019: }
1020: }
1021:
1022: public static OldIndexes getOldIndexes(String tableName,
1023: DataSource dataSource) throws DeploymentException {
1024: tableName = unquote(tableName, dataSource);
1025:
1026: Connection con = null;
1027: ResultSet rs = null;
1028: ArrayList indexNames = new ArrayList();
1029: ArrayList columnNames = new ArrayList();
1030: ArrayList ascDesc = new ArrayList();
1031: try {
1032: con = dataSource.getConnection();
1033:
1034: // (a j2ee spec compatible jdbc driver has to fully
1035: // implement the DatabaseMetaData)
1036: DatabaseMetaData dmd = con.getMetaData();
1037: String catalog = con.getCatalog();
1038: String schema = null;
1039: if (dmd.storesLowerCaseIdentifiers())
1040: tableName = tableName.toLowerCase();
1041: else if (dmd.storesUpperCaseIdentifiers())
1042: tableName = tableName.toUpperCase();
1043:
1044: // Patch #927759: Split tablename into "schema" and "table" separated by '.'
1045: int dotIndex;
1046: if ((dotIndex = tableName.indexOf('.')) != -1) {
1047: // Yank out schema name ...
1048: schema = tableName.substring(0, dotIndex);
1049: tableName = tableName.substring(dotIndex + 1);
1050: }
1051:
1052: rs = dmd.getIndexInfo(catalog, schema, tableName, false,
1053: false);
1054: while (rs.next()) {
1055: indexNames.add(rs.getString("INDEX_NAME"));
1056: columnNames.add(rs.getString("COLUMN_NAME"));
1057: ascDesc.add(rs.getString("ASC_OR_DESC"));
1058: }
1059: return new OldIndexes(indexNames, columnNames, ascDesc);
1060:
1061: } catch (SQLException e) {
1062: // This should not happen. A J2EE compatiable JDBC driver is
1063: // required fully support metadata.
1064: throw new DeploymentException(
1065: "Error while geting column names", e);
1066: } finally {
1067: JDBCUtil.safeClose(rs);
1068: JDBCUtil.safeClose(con);
1069: }
1070: }
1071:
1072: public static String unquote(String tableName, DataSource ds)
1073: throws DeploymentException {
1074: Connection con = null;
1075: try {
1076: con = ds.getConnection();
1077: String quote = con.getMetaData().getIdentifierQuoteString();
1078: if (tableName.startsWith(quote)) {
1079: if (tableName.endsWith(quote) == false) {
1080: throw new DeploymentException(
1081: "Mismatched quote in table name: "
1082: + tableName);
1083: }
1084: int quoteLength = quote.length();
1085: tableName = tableName.substring(quoteLength, tableName
1086: .length()
1087: - quoteLength);
1088: }
1089: } catch (SQLException e) {
1090: throw new DeploymentException(
1091: "Failed to get datasource connection");
1092: } finally {
1093: JDBCUtil.safeClose(con);
1094: }
1095: return tableName;
1096: }
1097:
1098: private static JDBCType getJDBCType(JDBCFieldBridge field) {
1099: JDBCType type = field.getJDBCType();
1100: if (type != null && type.getColumnNames().length > 0) {
1101: return type;
1102: }
1103: return null;
1104: }
1105:
1106: public static void dropTable(DataSource dataSource, String tableName)
1107: throws DeploymentException {
1108: Logger log = Logger.getLogger("CLEANER");
1109: String sql = "DROP TABLE " + tableName;
1110: try {
1111: Connection con = null;
1112: Statement statement = null;
1113: try {
1114: // execute sql
1115: con = dataSource.getConnection();
1116: statement = con.createStatement();
1117: statement.executeUpdate(sql);
1118: } finally {
1119: // make sure to close the connection and statement before
1120: // comitting the transaction or XA will break
1121: JDBCUtil.safeClose(statement);
1122: JDBCUtil.safeClose(con);
1123: }
1124: } catch (Exception e) {
1125: throw new DeploymentException("Error while droping table "
1126: + tableName, e);
1127: }
1128: log.info("Dropped table " + tableName + " succesfuly");
1129: }
1130:
1131: /**
1132: * utility class to store the information returned by getOldColumns()
1133: */
1134: public static class OldColumns {
1135: private ArrayList columnNames;
1136: private ArrayList typeNames;
1137: private ArrayList columnSizes;
1138:
1139: private OldColumns(ArrayList cn, ArrayList tn, ArrayList cs) {
1140: columnNames = cn;
1141: typeNames = tn;
1142: columnSizes = cs;
1143: }
1144:
1145: public ArrayList getColumnNames() {
1146: return columnNames;
1147: }
1148:
1149: public ArrayList getTypeNames() {
1150: return typeNames;
1151: }
1152:
1153: public ArrayList getColumnSizes() {
1154: return columnSizes;
1155: }
1156: }
1157:
1158: /**
1159: * utility class to store the information returned by getOldColumns()
1160: */
1161: public static class OldIndexes {
1162: private ArrayList indexNames;
1163: private ArrayList columnNames;
1164: private ArrayList columnAscDesc;
1165:
1166: private OldIndexes(ArrayList in, ArrayList cn, ArrayList ad) {
1167: indexNames = in;
1168: columnNames = cn;
1169: columnAscDesc = ad;
1170: }
1171:
1172: public ArrayList getColumnNames() {
1173: return columnNames;
1174: }
1175:
1176: public ArrayList getIndexNames() {
1177: return indexNames;
1178: }
1179:
1180: public ArrayList getColumnAscDesc() {
1181: return columnAscDesc;
1182: }
1183: }
1184:
1185: }
|