Index: cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/AbstractAfterKeyGenerator.java =================================================================== --- cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/AbstractAfterKeyGenerator.java (revision 8343) +++ cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/AbstractAfterKeyGenerator.java (working copy) @@ -46,6 +46,8 @@ import org.castor.core.util.Messages; import org.castor.cpa.CPAProperties; import org.castor.cpa.persistence.sql.engine.SQLStatementInsertCheck; +import org.castor.cpa.persistence.sql.query.Insert; +import org.castor.cpa.persistence.sql.query.QueryContext; import org.castor.jdo.engine.DatabaseContext; import org.castor.jdo.engine.DatabaseRegistry; import org.castor.jdo.engine.SQLTypeInfos; @@ -84,23 +86,35 @@ * should be used. */ private final boolean _useJDBC30; - /** An sql statement. */ - private String _statement; - /** Name of the Table extracted from Class descriptor. */ private String _mapTo; + + /** QueryContext for SQL query building, specifying database specific quotations + * and parameters binding. */ + private final QueryContext _ctx; + + /** Use a database trigger to generate key. */ + private boolean _triggerPresent; + + /** Name of the Sequence. */ + private String _seqName; - /** * Constructor. * * @param factory Persistence factory for the database engine the entity is persisted in. * Used to format the SQL statement */ - public AbstractAfterKeyGenerator(final PersistenceFactory factory) { + public AbstractAfterKeyGenerator(final PersistenceFactory factory, final Properties params) { _factory = factory; + _ctx = new QueryContext(_factory); AbstractProperties properties = CPAProperties.getInstance(); _useJDBC30 = properties.getBoolean(CPAProperties.USE_JDBC30, false); + + if (params != null) { + _triggerPresent = "true".equals(params.getProperty("trigger", "false")); + _seqName = params.getProperty("sequence", "{0}_seq"); + } } /** @@ -118,17 +132,8 @@ ClassDescriptor clsDesc = _engine.getDescriptor(); _engineType = clsDesc.getJavaClass().getName(); _mapTo = new ClassDescriptorJDONature(clsDesc).getTableName(); + Insert insert = new Insert(_mapTo); - StringBuffer insert = new StringBuffer(); - insert.append("INSERT INTO "); - insert.append(_factory.quoteName(_mapTo)); - insert.append(" ("); - - StringBuffer values = new StringBuffer(); - values.append(" VALUES ("); - - int count = 0; - // is it right to omit all identities in this case? // maybe we should support to define a separat keygen // for every identity or complex/custom keygen that @@ -139,55 +144,19 @@ if (fields[i].isStore()) { SQLColumnInfo[] columns = fields[i].getColumnInfo(); for (int j = 0; j < columns.length; j++) { - if (count > 0) { - insert.append(','); - values.append(','); - } - insert.append(_factory.quoteName(columns[j].getName())); - values.append('?'); - ++count; + insert.addInsert(columns[j].getName()); } } + } + + SQLColumnInfo[] ids = _engine.getColumnInfoForIdentities(); + if (_seqName != null && !_triggerPresent) { + insert.addSequence(_seqName, ids[0].getName()); } - - // it is possible to have no fields in INSERT statement - if (count == 0) { - // is it neccessary to omit "()" after table name in case - // the table holds only identities? maybe this depends on - // the database engine. - - // cut " (" - insert.setLength(insert.length() - 2); - } else { - insert.append(')'); - } - values.append(')'); - - _statement = insert.append(values).toString(); - - try { - SQLColumnInfo[] ids = _engine.getColumnInfoForIdentities(); - _statement = this.patchSQL(_statement, ids[0].getName()); - } catch (MappingException except) { - LOG.fatal(except); - - // proceed without this stupid key generator - FieldDescriptor fldDesc = _engine.getDescriptor().getIdentity(); - int[] sqlTypes = new FieldDescriptorJDONature(fldDesc).getSQLType(); - int sqlType = (sqlTypes == null) ? 0 : sqlTypes[0]; - try { - NoKeyGeneratorFactory noKeyGenFac = new NoKeyGeneratorFactory(); - - KeyGenerator keyGen = noKeyGenFac.getKeyGenerator(_factory, null, sqlType); - keyGen.buildStatement(_engine); - return keyGen; - } catch (MappingException ex) { - LOG.fatal(ex); - } - } - + insert.toString(_ctx); + if (LOG.isTraceEnabled()) { - LOG.trace(Messages.format("jdo.creating", _engineType, _statement)); + LOG.trace(Messages.format("jdo.creating", _engineType, _ctx.toString())); } return this; @@ -216,26 +185,24 @@ if ((internalIdentity == null) && _useJDBC30) { Field field = Statement.class.getField("RETURN_GENERATED_KEYS"); - Integer rgk = (Integer) field.get(_statement); + Integer rgk = (Integer) field.get(_ctx.toString()); Class[] types = new Class[] {String.class, int.class}; - Object[] args = new Object[] {_statement, rgk}; + Object[] args = new Object[] {_ctx.toString(), rgk}; Method method = Connection.class.getMethod("prepareStatement", types); stmt = (PreparedStatement) method.invoke(conn, args); // stmt = conn.prepareStatement(_statement, Statement.RETURN_GENERATED_KEYS); } else { - stmt = conn.prepareStatement(_statement); + stmt = conn.prepareStatement(_ctx.toString()); } if (LOG.isTraceEnabled()) { LOG.trace(Messages.format("jdo.creating", _engineType, stmt.toString())); } - - // must remember that SQL column index is base one. - int count = 1; - count = bindFields(entity, stmt, count); + bindFields(entity, stmt); + if (LOG.isDebugEnabled()) { LOG.debug(Messages.format("jdo.creating", _engineType, stmt.toString())); } @@ -282,7 +249,7 @@ return internalIdentity; } catch (SQLException except) { - LOG.fatal(Messages.format("jdo.storeFatal", _engineType, _statement), except); + LOG.fatal(Messages.format("jdo.storeFatal", _engineType, _ctx.toString()), except); Boolean isDupKey = _factory.isDuplicateKeyException(except); if (Boolean.TRUE.equals(isDupKey)) { @@ -322,16 +289,14 @@ /** * Binds parameters values to the PreparedStatement. * - * @param entity + * @param entity Entity instance from which field values to be fetached to + * bind with sql insert statement. * @param stmt PreparedStatement object containing sql staatement. - * @param count Offset. - * @return final Offset * @throws SQLException If a database access error occurs. * @throws PersistenceException If identity size mismatches. */ - private int bindFields(final ProposedEntity entity, final PreparedStatement stmt, - final int count) throws SQLException, PersistenceException { - int internalCount = count; + private void bindFields(final ProposedEntity entity, final PreparedStatement stmt + ) throws SQLException, PersistenceException { SQLFieldInfo[] fields = _engine.getInfo(); for (int i = 0; i < fields.length; ++i) { SQLColumnInfo[] columns = fields[i].getColumnInfo(); @@ -339,7 +304,8 @@ Object value = entity.getField(i); if (value == null) { for (int j = 0; j < columns.length; j++) { - stmt.setNull(internalCount++, columns[j].getSqlType()); + _ctx.bindParameter(stmt, columns[j].getName(), null, + columns[j].getSqlType()); } } else if (value instanceof Identity) { Identity identity = (Identity) value; @@ -347,21 +313,20 @@ throw new PersistenceException("Size of identity field mismatch!"); } for (int j = 0; j < columns.length; j++) { - SQLTypeInfos.setValue(stmt, internalCount++, + _ctx.bindParameter(stmt, columns[j].getName(), columns[j].toSQL(identity.get(j)), columns[j].getSqlType()); } } else { if (columns.length != 1) { throw new PersistenceException("Complex field expected!"); } - SQLTypeInfos.setValue(stmt, internalCount++, columns[0].toSQL(value), + _ctx.bindParameter(stmt, columns[0].getName(), columns[0].toSQL(value), columns[0].getSqlType()); } } } - return internalCount; } - + /** * Generates the key. * Index: cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/AbstractBeforeKeyGenerator.java =================================================================== --- cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/AbstractBeforeKeyGenerator.java (revision 8343) +++ cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/AbstractBeforeKeyGenerator.java (working copy) @@ -24,9 +24,10 @@ import org.apache.commons.logging.LogFactory; import org.castor.core.util.Messages; import org.castor.cpa.persistence.sql.engine.SQLStatementInsertCheck; +import org.castor.cpa.persistence.sql.query.Insert; +import org.castor.cpa.persistence.sql.query.QueryContext; import org.castor.jdo.engine.DatabaseContext; import org.castor.jdo.engine.DatabaseRegistry; -import org.castor.jdo.engine.SQLTypeInfos; import org.castor.persist.ProposedEntity; import org.exolab.castor.jdo.Database; import org.exolab.castor.jdo.DuplicateIdentityException; @@ -66,12 +67,13 @@ /** Represents the engine type obtained from clas descriptor. */ private String _engineType = null; - - /** An sql statement. */ - private String _statement; /** Name of the Table extracted from Class descriptor. */ private String _mapTo; + + /** QueryContext for SQL query building, specifying database specific quotations + * and parameters binding. */ + private final QueryContext _ctx; /** * Constructor. @@ -81,7 +83,9 @@ */ public AbstractBeforeKeyGenerator(final PersistenceFactory factory) { _factory = factory; + _ctx = new QueryContext(_factory); } + /** * {@inheritDoc} */ @@ -96,27 +100,12 @@ _engine = engine; ClassDescriptor clsDesc = _engine.getDescriptor(); _engineType = clsDesc.getJavaClass().getName(); - _mapTo = new ClassDescriptorJDONature(clsDesc).getTableName(); - - StringBuffer insert = new StringBuffer(); - insert.append("INSERT INTO "); - insert.append(_factory.quoteName(_mapTo)); - insert.append(" ("); - - StringBuffer values = new StringBuffer(); - values.append(" VALUES ("); - - int count = 0; - - SQLColumnInfo[] ids = _engine.getColumnInfoForIdentities(); + _mapTo = new ClassDescriptorJDONature(clsDesc).getTableName(); + Insert insert = new Insert(_mapTo); + + SQLColumnInfo[] ids = _engine.getColumnInfoForIdentities(); for (int i = 0; i < ids.length; i++) { - if (count > 0) { - insert.append(','); - values.append(','); - } - insert.append(_factory.quoteName(ids[i].getName())); - values.append('?'); - ++count; + insert.addInsert(ids[i].getName()); } SQLFieldInfo[] fields = _engine.getInfo(); @@ -124,24 +113,14 @@ if (fields[i].isStore()) { SQLColumnInfo[] columns = fields[i].getColumnInfo(); for (int j = 0; j < columns.length; j++) { - if (count > 0) { - insert.append(','); - values.append(','); - } - insert.append(_factory.quoteName(columns[j].getName())); - values.append('?'); - ++count; + insert.addInsert(columns[j].getName()); } } - } + } + insert.toString(_ctx); - insert.append(')'); - values.append(')'); - - _statement = insert.append(values).toString(); - if (LOG.isTraceEnabled()) { - LOG.trace(Messages.format("jdo.creating", _engineType, _statement)); + LOG.trace(Messages.format("jdo.creating", _engineType, _ctx.toString())); } return this; @@ -173,29 +152,14 @@ internalIdentity = generateKey(database, conn, null); // we only need to care on JDBC 3.0 at after INSERT. - stmt = conn.prepareStatement(_statement); - - if (LOG.isTraceEnabled()) { - LOG.trace(Messages.format("jdo.creating", _engineType, stmt.toString())); - } + stmt = conn.prepareStatement(_ctx.toString()); - SQLColumnInfo[] ids = _engine.getColumnInfoForIdentities(); - if (internalIdentity.size() != ids.length) { - throw new PersistenceException("Size of identity field mismatched!"); - } + //bind Identities + bindIdentity(internalIdentity, stmt); - // must remember that SQL column index is base one. - int count = 1; - for (int i = 0; i < ids.length; i++) { - stmt.setObject(count++, ids[i].toSQL(internalIdentity.get(i))); - } - - if (LOG.isTraceEnabled()) { - LOG.trace(Messages.format("jdo.creating", _engineType, stmt.toString())); - } - - count = bindFields(entity, stmt, count); - + //bind Fields + bindFields(entity, stmt); + if (LOG.isDebugEnabled()) { LOG.debug(Messages.format("jdo.creating", _engineType, stmt.toString())); } @@ -206,7 +170,7 @@ return internalIdentity; } catch (SQLException except) { - LOG.fatal(Messages.format("jdo.storeFatal", _engineType, _statement), except); + LOG.fatal(Messages.format("jdo.storeFatal", _engineType, _ctx.toString()), except); Boolean isDupKey = _factory.isDuplicateKeyException(except); if (Boolean.TRUE.equals(isDupKey)) { @@ -236,18 +200,37 @@ } /** + * Binds the identity values. + * + * @param internalIdentity Identity values. + * @param stmt PreapraedStatement containing the sql insert statement. + * @throws SQLException If a database access error occurs. + * @throws PersistenceException If identity size mismatches. + */ + public void bindIdentity(final Identity internalIdentity, final PreparedStatement stmt) + throws SQLException, PersistenceException { + SQLColumnInfo[] ids = _engine.getColumnInfoForIdentities(); + if (internalIdentity.size() != ids.length) { + throw new PersistenceException("Size of identity field mismatched!"); + } + + for (int i = 0; i < ids.length; i++) { + _ctx.bindParameter(stmt, ids[i].getName(), ids[i].toSQL(internalIdentity.get(i)), + ids[i].getSqlType()); + } + } + + /** * Binds parameters values to the PreparedStatement. * - * @param entity + * @param entity Entity instance from which field values to be fetached to + * bind with sql insert statement. * @param stmt PreparedStatement object containing sql staatement. - * @param count Offset. - * @return final Offset * @throws SQLException If a database access error occurs. * @throws PersistenceException If identity size mismatches. */ - private int bindFields(final ProposedEntity entity, final PreparedStatement stmt, - final int count) throws SQLException, PersistenceException { - int internalCount = count; + private void bindFields(final ProposedEntity entity, final PreparedStatement stmt + ) throws SQLException, PersistenceException { SQLFieldInfo[] fields = _engine.getInfo(); for (int i = 0; i < fields.length; ++i) { SQLColumnInfo[] columns = fields[i].getColumnInfo(); @@ -255,7 +238,8 @@ Object value = entity.getField(i); if (value == null) { for (int j = 0; j < columns.length; j++) { - stmt.setNull(internalCount++, columns[j].getSqlType()); + _ctx.bindParameter(stmt, columns[j].getName(), null, + columns[j].getSqlType()); } } else if (value instanceof Identity) { Identity identity = (Identity) value; @@ -263,21 +247,20 @@ throw new PersistenceException("Size of identity field mismatch!"); } for (int j = 0; j < columns.length; j++) { - SQLTypeInfos.setValue(stmt, internalCount++, + _ctx.bindParameter(stmt, columns[j].getName(), columns[j].toSQL(identity.get(j)), columns[j].getSqlType()); } } else { if (columns.length != 1) { throw new PersistenceException("Complex field expected!"); } - SQLTypeInfos.setValue(stmt, internalCount++, columns[0].toSQL(value), + _ctx.bindParameter(stmt, columns[0].getName(), columns[0].toSQL(value), columns[0].getSqlType()); } } } - return internalCount; } - + /** * Generates the key. * Index: cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/HighLowKeyGenerator.java =================================================================== --- cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/HighLowKeyGenerator.java (revision 8335) +++ cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/HighLowKeyGenerator.java (working copy) @@ -277,12 +277,5 @@ return _sameConnection; } - /** - * {@inheritDoc} - */ - public String patchSQL(final String insert, final String primKeyName) { - return insert; - } - //----------------------------------------------------------------------------------- } \ No newline at end of file Index: cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/IdentityKeyGenerator.java =================================================================== --- cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/IdentityKeyGenerator.java (revision 8341) +++ cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/IdentityKeyGenerator.java (working copy) @@ -66,7 +66,7 @@ */ public IdentityKeyGenerator(final PersistenceFactory factory, final int sqlType) throws MappingException { - super(factory); + super(factory, null); _factory = factory; @@ -138,13 +138,6 @@ public boolean isInSameConnection() { return true; } - - /** - * {@inheritDoc} - */ - public String patchSQL(final String insert, final String primKeyName) { - return insert; - } //----------------------------------------------------------------------------------- } Index: cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/KeyGenerator.java =================================================================== --- cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/KeyGenerator.java (revision 8343) +++ cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/KeyGenerator.java (working copy) @@ -87,31 +87,6 @@ boolean isInSameConnection(); /** - * Gives a possibility to patch the Castor-generated SQL statement - * for INSERT (indended mainly for DURING_INSERT style of key generators, - * other key generators usually simply return the passed parameter). - * The original statement contains primary key column on the first place - * for BEFORE_INSERT style and doesn't contain it for the other styles. - * This method is called once for each class and must return String - * with '?' that can be passed to CallableStatement (for DURING_INSERT - * style) or to PreparedStatement (for the others). - * Then for each record being created actual field values are substituted, - * starting from the primary key value for BEFORE_INSERT style, of starting - * from the first of other fields for the other styles. - * The DURING_INSERT key generator must add one OUT parameter to the end - * of the parameter list, which will return the generated identity. - * For example, ReturningKeyGenerator for Oracle8i transforms - * "INSERT INTO tbl (pk, fld1, ...,fldN) VALUES (?,?...,?)" to - * "INSERT INTO tbl (pk, fld1, ...) VALUES (seq.nextval,?....,?) - * RETURNING pk INTO ?". - * DURING_INSERT key generator also may be implemented as a stored procedure. - * - * @param insert Castor-generated INSERT statement - * @param primKeyName The primary key name - */ - String patchSQL(String insert, String primKeyName) throws MappingException; - - /** * Executes the SQL statement after preparing the PreparedStatement. * * @param database Index: cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/MaxKeyGenerator.java =================================================================== --- cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/MaxKeyGenerator.java (revision 8335) +++ cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/MaxKeyGenerator.java (working copy) @@ -157,13 +157,6 @@ return true; } - /** - * {@inheritDoc} - */ - public String patchSQL(final String insert, final String primKeyName) { - return insert; - } - //----------------------------------------------------------------------------------- } Index: cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/NoKeyGenerator.java =================================================================== --- cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/NoKeyGenerator.java (revision 8343) +++ cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/NoKeyGenerator.java (working copy) @@ -24,7 +24,8 @@ import org.apache.commons.logging.LogFactory; import org.castor.core.util.Messages; import org.castor.cpa.persistence.sql.engine.SQLStatementInsertCheck; -import org.castor.jdo.engine.SQLTypeInfos; +import org.castor.cpa.persistence.sql.query.Insert; +import org.castor.cpa.persistence.sql.query.QueryContext; import org.castor.persist.ProposedEntity; import org.exolab.castor.jdo.Database; import org.exolab.castor.jdo.DuplicateIdentityException; @@ -60,14 +61,15 @@ * class is responsible for. Holds all required information of the entity type. */ private SQLEngine _engine; - /** An sql statement. */ - private String _statement; - /** Name of the Table extracted from Class descriptor. */ private String _mapTo; /** Represents the engine type obtained from clas descriptor. */ private String _engineType = null; + + /** QueryContext for SQL query building, specifying database specific quotations + * and parameters binding. */ + private final QueryContext _ctx; /** * Constructor. @@ -77,6 +79,7 @@ */ public NoKeyGenerator(final PersistenceFactory factory) { _factory = factory; + _ctx = new QueryContext(_factory); } /** @@ -111,27 +114,12 @@ _engine = engine; ClassDescriptor clsDesc = _engine.getDescriptor(); _engineType = clsDesc.getJavaClass().getName(); - _mapTo = new ClassDescriptorJDONature(clsDesc).getTableName(); + _mapTo = new ClassDescriptorJDONature(clsDesc).getTableName(); + Insert insert = new Insert(_mapTo); - StringBuffer insert = new StringBuffer(); - insert.append("INSERT INTO "); - insert.append(_factory.quoteName(_mapTo)); - insert.append(" ("); - - StringBuffer values = new StringBuffer(); - values.append(" VALUES ("); - - int count = 0; - - SQLColumnInfo[] ids = _engine.getColumnInfoForIdentities(); + SQLColumnInfo[] ids = _engine.getColumnInfoForIdentities(); for (int i = 0; i < ids.length; i++) { - if (count > 0) { - insert.append(','); - values.append(','); - } - insert.append(_factory.quoteName(ids[i].getName())); - values.append('?'); - ++count; + insert.addInsert(ids[i].getName()); } SQLFieldInfo[] fields = _engine.getInfo(); @@ -139,24 +127,14 @@ if (fields[i].isStore()) { SQLColumnInfo[] columns = fields[i].getColumnInfo(); for (int j = 0; j < columns.length; j++) { - if (count > 0) { - insert.append(','); - values.append(','); - } - insert.append(_factory.quoteName(columns[j].getName())); - values.append('?'); - ++count; + insert.addInsert(columns[j].getName()); } } - } + } + insert.toString(_ctx); - insert.append(')'); - values.append(')'); - - _statement = insert.append(values).toString(); - if (LOG.isTraceEnabled()) { - LOG.trace(Messages.format("jdo.creating", _engineType, _statement)); + LOG.trace(Messages.format("jdo.creating", _engineType, _ctx.toString())); } return this; @@ -188,29 +166,14 @@ } // we only need to care on JDBC 3.0 at after INSERT. - stmt = conn.prepareStatement(_statement); - - if (LOG.isTraceEnabled()) { - LOG.trace(Messages.format("jdo.creating", _engineType, stmt.toString())); - } + stmt = conn.prepareStatement(_ctx.toString()); - SQLColumnInfo[] ids = _engine.getColumnInfoForIdentities(); - if (internalIdentity.size() != ids.length) { - throw new PersistenceException("Size of identity field mismatched!"); - } + //bind Identities + bindIdentity(internalIdentity, stmt); - // must remember that SQL column index is base one. - int count = 1; - for (int i = 0; i < ids.length; i++) { - stmt.setObject(count++, ids[i].toSQL(internalIdentity.get(i))); - } + //bind Fields + bindFields(entity, stmt); - if (LOG.isTraceEnabled()) { - LOG.trace(Messages.format("jdo.creating", _engineType, stmt.toString())); - } - - count = bindFields(entity, stmt, count); - if (LOG.isDebugEnabled()) { LOG.debug(Messages.format("jdo.creating", _engineType, stmt.toString())); } @@ -221,7 +184,7 @@ return internalIdentity; } catch (SQLException except) { - LOG.fatal(Messages.format("jdo.storeFatal", _engineType, _statement), except); + LOG.fatal(Messages.format("jdo.storeFatal", _engineType, _ctx.toString()), except); Boolean isDupKey = _factory.isDuplicateKeyException(except); if (Boolean.TRUE.equals(isDupKey)) { @@ -251,18 +214,37 @@ } /** + * Binds the identity values. + * + * @param internalIdentity Identity values. + * @param stmt PreapraedStatement containing the sql insert statement. + * @throws SQLException If a database access error occurs. + * @throws PersistenceException If identity size mismatches. + */ + public void bindIdentity(final Identity internalIdentity, final PreparedStatement stmt) + throws SQLException, PersistenceException { + SQLColumnInfo[] ids = _engine.getColumnInfoForIdentities(); + if (internalIdentity.size() != ids.length) { + throw new PersistenceException("Size of identity field mismatched!"); + } + + for (int i = 0; i < ids.length; i++) { + _ctx.bindParameter(stmt, ids[i].getName(), ids[i].toSQL(internalIdentity.get(i)), + ids[i].getSqlType()); + } + } + + /** * Binds parameters values to the PreparedStatement. * - * @param entity + * @param entity Entity instance from which field values to be fetached to + * bind with sql insert statement. * @param stmt PreparedStatement object containing sql staatement. - * @param count Offset. - * @return final Offset * @throws SQLException If a database access error occurs. * @throws PersistenceException If identity size mismatches. */ - private int bindFields(final ProposedEntity entity, final PreparedStatement stmt, - final int count) throws SQLException, PersistenceException { - int internalCount = count; + private void bindFields(final ProposedEntity entity, final PreparedStatement stmt + ) throws SQLException, PersistenceException { SQLFieldInfo[] fields = _engine.getInfo(); for (int i = 0; i < fields.length; ++i) { SQLColumnInfo[] columns = fields[i].getColumnInfo(); @@ -270,7 +252,8 @@ Object value = entity.getField(i); if (value == null) { for (int j = 0; j < columns.length; j++) { - stmt.setNull(internalCount++, columns[j].getSqlType()); + _ctx.bindParameter(stmt, columns[j].getName(), null, + columns[j].getSqlType()); } } else if (value instanceof Identity) { Identity identity = (Identity) value; @@ -278,19 +261,18 @@ throw new PersistenceException("Size of identity field mismatch!"); } for (int j = 0; j < columns.length; j++) { - SQLTypeInfos.setValue(stmt, internalCount++, + _ctx.bindParameter(stmt, columns[j].getName(), columns[j].toSQL(identity.get(j)), columns[j].getSqlType()); } } else { if (columns.length != 1) { throw new PersistenceException("Complex field expected!"); } - SQLTypeInfos.setValue(stmt, internalCount++, columns[0].toSQL(value), + _ctx.bindParameter(stmt, columns[0].getName(), columns[0].toSQL(value), columns[0].getSqlType()); } } } - return internalCount; } //----------------------------------------------------------------------------------- Index: cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/SequenceAfterKeyGenerator.java =================================================================== --- cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/SequenceAfterKeyGenerator.java (revision 8341) +++ cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/SequenceAfterKeyGenerator.java (working copy) @@ -22,7 +22,6 @@ import java.sql.Types; import java.text.MessageFormat; import java.util.Properties; -import java.util.StringTokenizer; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; @@ -112,8 +111,7 @@ /** * Initialize the SEQUENCE key generator for AFTER_INSERT style - * {@link #generateKey} is called after INSERT. {@link #patchSQL} - * may be used but usually doesn't. + * {@link #generateKey} is called after INSERT. * * @param factory A PersistenceFactory instance. * @param params @@ -123,7 +121,7 @@ */ public SequenceAfterKeyGenerator(final PersistenceFactory factory, final Properties params, final int sqlType) throws MappingException { - super(factory); + super(factory, params); _factory = factory; _triggerPresent = "true".equals(params.getProperty("trigger", "false")); @@ -191,79 +189,5 @@ return true; } - /** - * Gives a possibility to patch the Castor-generated SQL statement - * for INSERT (makes sense for DURING_INSERT key generators). - */ - public String patchSQL(final String insert, final String primKeyName) - throws MappingException { - StringTokenizer st; - String tableName; - String seqName; - String nextval; - StringBuffer sb; - int lp1; // the first left parenthesis, which starts fields list - int lp2; // the second left parenthesis, which starts values list - - // First find the table name - st = new StringTokenizer(insert); - if (!st.hasMoreTokens() || !st.nextToken().equalsIgnoreCase("INSERT")) { - throw new MappingException(Messages.format("mapping.keyGenCannotParse", insert)); - } - if (!st.hasMoreTokens() || !st.nextToken().equalsIgnoreCase("INTO")) { - throw new MappingException(Messages.format("mapping.keyGenCannotParse", insert)); - } - if (!st.hasMoreTokens()) { - throw new MappingException(Messages.format("mapping.keyGenCannotParse", insert)); - } - tableName = st.nextToken(); - - // remove every double quote in the tablename - int idxQuote = tableName.indexOf('"'); - if (idxQuote >= 0) { - StringBuffer buffer2 = new StringBuffer(); - int pos = 0; - - do { - buffer2.append(tableName.substring(pos, idxQuote)); - pos = idxQuote + 1; - idxQuote = tableName.indexOf('"', pos); - } while (idxQuote != -1); - - buffer2.append(tableName.substring(pos)); - - tableName = buffer2.toString(); - } - - // due to varargs in 1.5, see CASTOR-1097 - seqName = MessageFormat.format(_seqName, new Object[] {tableName, primKeyName}); - nextval = _factory.quoteName(seqName + ".nextval"); - lp1 = insert.indexOf('('); - lp2 = insert.indexOf('(', lp1 + 1); - if (lp1 < 0) { - throw new MappingException(Messages.format("mapping.keyGenCannotParse", insert)); - } - sb = new StringBuffer(insert); - // if no onInsert triggers in the DB, we have to supply the Key values manually - if (!_triggerPresent) { - if (lp2 < 0) { - // Only one pk field in the table, the INSERT statement would be - // INSERT INTO table VALUES () - lp2 = lp1; - lp1 = insert.indexOf(" VALUES "); - // don't change the order of lines below, - // otherwise index becomes invalid - sb.insert(lp2 + 1, nextval); - sb.insert(lp1 + 1, "(" + _factory.quoteName(primKeyName) + ") "); - } else { - // don't change the order of lines below, - // otherwise index becomes invalid - sb.insert(lp2 + 1, nextval + ","); - sb.insert(lp1 + 1, _factory.quoteName(primKeyName) + ","); - } - } - return sb.toString(); - } - //----------------------------------------------------------------------------------- } Index: cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/SequenceBeforeKeyGenerator.java =================================================================== --- cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/SequenceBeforeKeyGenerator.java (revision 8335) +++ cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/SequenceBeforeKeyGenerator.java (working copy) @@ -141,8 +141,7 @@ /** * Initialize the SEQUENCE key generator for BEFORE_INSERT style - * {@link #generateKey} is called before INSERT. {@link #patchSQL} may be - * used but usually doesn't. + * {@link #generateKey} is called before INSERT. * * @param factory A PersistenceFactory instance. * @param params @@ -227,14 +226,5 @@ return true; } - /** - * Gives a possibility to patch the Castor-generated SQL statement - * for INSERT (makes sense for DURING_INSERT key generators). - */ - public String patchSQL(final String insert, final String primKeyName) - throws MappingException { - return insert; - } - //----------------------------------------------------------------------------------- } Index: cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/SequenceDuringKeyGenerator.java =================================================================== --- cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/SequenceDuringKeyGenerator.java (revision 8343) +++ cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/SequenceDuringKeyGenerator.java (working copy) @@ -23,13 +23,11 @@ import java.sql.Types; import java.text.MessageFormat; import java.util.Properties; -import java.util.StringTokenizer; import org.apache.commons.logging.Log; import org.apache.commons.logging.LogFactory; import org.exolab.castor.jdo.PersistenceException; import org.castor.core.util.Messages; -import org.exolab.castor.mapping.FieldDescriptor; import org.exolab.castor.mapping.MappingException; import org.exolab.castor.persist.spi.PersistenceFactory; import org.exolab.castor.jdo.Database; @@ -38,9 +36,9 @@ import org.exolab.castor.jdo.engine.SQLEngine; import org.exolab.castor.jdo.engine.SQLFieldInfo; import org.exolab.castor.jdo.engine.nature.ClassDescriptorJDONature; -import org.exolab.castor.jdo.engine.nature.FieldDescriptorJDONature; import org.castor.cpa.persistence.sql.engine.SQLStatementInsertCheck; -import org.castor.jdo.engine.SQLTypeInfos; +import org.castor.cpa.persistence.sql.query.Insert; +import org.castor.cpa.persistence.sql.query.QueryContext; import org.castor.persist.ProposedEntity; import org.exolab.castor.mapping.ClassDescriptor; import org.exolab.castor.persist.spi.Identity; @@ -126,19 +124,23 @@ private SQLEngine _engine; /** An sql statement. */ - private String _statement; + // private String _statement; /** Name of the Table extracted from Class descriptor. */ private String _mapTo; /** Represents the engine type obtained from clas descriptor. */ private String _engineType = null; + + /** QueryContext for SQL query building, specifying database specific quotations + * and parameters binding. */ + private final QueryContext _ctx; //----------------------------------------------------------------------------------- /** * Initialize the SEQUENCE key generator for DURING_INSERT style - * {@link #generateKey} is never called, all work is done by {@link #patchSQL}. + * {@link #generateKey} is never called. * * @param factory A PersistenceFactory instance. * @param params @@ -151,6 +153,7 @@ _factory = factory; _triggerPresent = "true".equals(params.getProperty("trigger", "false")); _seqName = params.getProperty("sequence", "{0}_seq"); + _ctx = new QueryContext(_factory); initSqlTypeHandler(sqlType); initType(); @@ -206,86 +209,6 @@ } /** - * Gives a possibility to patch the Castor-generated SQL statement - * for INSERT (makes sense for DURING_INSERT key generators). - */ - public String patchSQL(final String insert, final String primKeyName) - throws MappingException { - StringTokenizer st; - String tableName; - String seqName; - String nextval; - StringBuffer sb; - int lp1; // the first left parenthesis, which starts fields list - int lp2; // the second left parenthesis, which starts values list - - // First find the table name - st = new StringTokenizer(insert); - if (!st.hasMoreTokens() || !st.nextToken().equalsIgnoreCase("INSERT")) { - throw new MappingException(Messages.format("mapping.keyGenCannotParse", insert)); - } - if (!st.hasMoreTokens() || !st.nextToken().equalsIgnoreCase("INTO")) { - throw new MappingException(Messages.format("mapping.keyGenCannotParse", insert)); - } - if (!st.hasMoreTokens()) { - throw new MappingException(Messages.format("mapping.keyGenCannotParse", insert)); - } - tableName = st.nextToken(); - - // remove every double quote in the tablename - int idxQuote = tableName.indexOf('"'); - if (idxQuote >= 0) { - StringBuffer buffer2 = new StringBuffer(); - int pos = 0; - - do { - buffer2.append(tableName.substring(pos, idxQuote)); - pos = idxQuote + 1; - idxQuote = tableName.indexOf('"', pos); - } while (idxQuote != -1); - - buffer2.append(tableName.substring(pos)); - - tableName = buffer2.toString(); - } - - // due to varargs in 1.5, see CASTOR-1097 - seqName = MessageFormat.format(_seqName, new Object[] {tableName, primKeyName}); - nextval = _factory.quoteName(seqName + ".nextval"); - lp1 = insert.indexOf('('); - lp2 = insert.indexOf('(', lp1 + 1); - if (lp1 < 0) { - throw new MappingException(Messages.format("mapping.keyGenCannotParse", insert)); - } - sb = new StringBuffer(insert); - // if no onInsert triggers in the DB, we have to supply the Key values manually - if (!_triggerPresent) { - if (lp2 < 0) { - // Only one pk field in the table, the INSERT statement would be - // INSERT INTO table VALUES () - lp2 = lp1; - lp1 = insert.indexOf(" VALUES "); - // don't change the order of lines below, - // otherwise index becomes invalid - sb.insert(lp2 + 1, nextval); - sb.insert(lp1 + 1, "(" + _factory.quoteName(primKeyName) + ") "); - } else { - // don't change the order of lines below, - // otherwise index becomes invalid - sb.insert(lp2 + 1, nextval + ","); - sb.insert(lp1 + 1, _factory.quoteName(primKeyName) + ","); - } - } - - // append 'RETURNING primKeyName INTO ?' - sb.append(" RETURNING "); - sb.append(_factory.quoteName(primKeyName)); - sb.append(" INTO ?"); - - return sb.toString(); - } - - /** * {@inheritDoc} */ public KeyGenerator buildStatement(final SQLEngine engine) { @@ -294,15 +217,7 @@ _engineType = clsDesc.getJavaClass().getName(); _mapTo = new ClassDescriptorJDONature(clsDesc).getTableName(); - StringBuffer insert = new StringBuffer(); - insert.append("INSERT INTO "); - insert.append(_factory.quoteName(_mapTo)); - insert.append(" ("); - - StringBuffer values = new StringBuffer(); - values.append(" VALUES ("); - - int count = 0; + Insert insert = new Insert(_mapTo); // is it right to omit all identities in this case? // maybe we should support to define a separat keygen @@ -314,57 +229,21 @@ if (fields[i].isStore()) { SQLColumnInfo[] columns = fields[i].getColumnInfo(); for (int j = 0; j < columns.length; j++) { - if (count > 0) { - insert.append(','); - values.append(','); - } - insert.append(_factory.quoteName(columns[j].getName())); - values.append('?'); - ++count; + insert.addInsert(columns[j].getName()); + } } } - - // it is possible to have no fields in INSERT statement - if (count == 0) { - // is it neccessary to omit "()" after table name in case - // the table holds only identities? maybe this depends on - // the database engine. - - // cut " (" - insert.setLength(insert.length() - 2); - } else { - insert.append(')'); + SQLColumnInfo[] ids = _engine.getColumnInfoForIdentities(); + //_statement = this.patchSQL(_statement, ids[0].getName()); + if (!_triggerPresent) { + insert.addSequence(_seqName, ids[0].getName()); } - values.append(')'); - _statement = insert.append(values).toString(); - - try { - SQLColumnInfo[] ids = _engine.getColumnInfoForIdentities(); - _statement = this.patchSQL(_statement, ids[0].getName()); - _statement = "{call " + _statement + "}"; - } catch (MappingException except) { - LOG.fatal(except); - - // proceed without this stupid key generator - FieldDescriptor fldDesc = _engine.getDescriptor().getIdentity(); - int[] sqlTypes = new FieldDescriptorJDONature(fldDesc).getSQLType(); - int sqlType = (sqlTypes == null) ? 0 : sqlTypes[0]; - try { - NoKeyGeneratorFactory noKeyGenFac = new NoKeyGeneratorFactory(); - - KeyGenerator keyGen = noKeyGenFac.getKeyGenerator(_factory, null, sqlType); - keyGen.buildStatement(_engine); - - return keyGen; - } catch (MappingException ex) { - LOG.fatal(ex); - } - } - + insert.toString(_ctx); + if (LOG.isTraceEnabled()) { - LOG.trace(Messages.format("jdo.creating", _engineType, _statement)); + LOG.trace(Messages.format("jdo.creating", _engineType, _ctx.toString())); } return this; } @@ -390,27 +269,32 @@ } } - stmt = conn.prepareCall(_statement); + String statement; + SQLColumnInfo[] ids = _engine.getColumnInfoForIdentities(); + + statement = _ctx.toString(); + statement += " RETURNING "; + statement += _factory.quoteName(ids[0].getName()); + statement += " INTO ?"; + statement = "{call " + statement + "}"; + + stmt = conn.prepareCall(statement); if (LOG.isTraceEnabled()) { LOG.trace(Messages.format("jdo.creating", _engineType, stmt.toString())); } - - // must remember that SQL column index is base one. - int count = 1; - count = bindFields(entity, stmt, count); + bindFields(entity, stmt); + if (LOG.isTraceEnabled()) { LOG.trace(Messages.format("jdo.creating", _engineType, stmt.toString())); } - SQLColumnInfo[] ids = _engine.getColumnInfoForIdentities(); - // generate key during INSERT. CallableStatement cstmt = (CallableStatement) stmt; int sqlType = ids[0].getSqlType(); - cstmt.registerOutParameter(count, sqlType); + cstmt.registerOutParameter(_ctx.parameterSize() + 1, sqlType); if (LOG.isDebugEnabled()) { LOG.debug(Messages.format("jdo.creating", _engineType, cstmt.toString())); @@ -428,9 +312,9 @@ // workaround for INTEGER type in Oracle getObject returns BigDecimal. Object temp; if (sqlType == java.sql.Types.INTEGER) { - temp = new Integer(cstmt.getInt(count)); + temp = new Integer(cstmt.getInt(_ctx.parameterSize() + 1)); } else { - temp = cstmt.getObject(count); + temp = cstmt.getObject(_ctx.parameterSize() + 1); } internalIdentity = new Identity(ids[0].toJava(temp)); @@ -438,7 +322,7 @@ return internalIdentity; } catch (SQLException except) { - LOG.fatal(Messages.format("jdo.storeFatal", _engineType, _statement), except); + LOG.fatal(Messages.format("jdo.storeFatal", _engineType, _ctx.toString()), except); Boolean isDupKey = _factory.isDuplicateKeyException(except); if (Boolean.TRUE.equals(isDupKey)) { @@ -466,20 +350,18 @@ throw new PersistenceException(Messages.format("persist.nested", except), except); } } - + /** * Binds parameters values to the PreparedStatement. * - * @param entity + * @param entity Entity instance from which field values to be fetached to + * bind with sql insert statement. * @param stmt PreparedStatement object containing sql staatement. - * @param count Offset. - * @return final Offset * @throws SQLException If a database access error occurs. * @throws PersistenceException If identity size mismatches. */ - private int bindFields(final ProposedEntity entity, final PreparedStatement stmt, - final int count) throws SQLException, PersistenceException { - int internalCount = count; + private void bindFields(final ProposedEntity entity, final PreparedStatement stmt + ) throws SQLException, PersistenceException { SQLFieldInfo[] fields = _engine.getInfo(); for (int i = 0; i < fields.length; ++i) { SQLColumnInfo[] columns = fields[i].getColumnInfo(); @@ -487,7 +369,8 @@ Object value = entity.getField(i); if (value == null) { for (int j = 0; j < columns.length; j++) { - stmt.setNull(internalCount++, columns[j].getSqlType()); + _ctx.bindParameter(stmt, columns[j].getName(), null, + columns[j].getSqlType()); } } else if (value instanceof Identity) { Identity identity = (Identity) value; @@ -495,19 +378,18 @@ throw new PersistenceException("Size of identity field mismatch!"); } for (int j = 0; j < columns.length; j++) { - SQLTypeInfos.setValue(stmt, internalCount++, + _ctx.bindParameter(stmt, columns[j].getName(), columns[j].toSQL(identity.get(j)), columns[j].getSqlType()); } } else { if (columns.length != 1) { throw new PersistenceException("Complex field expected!"); } - SQLTypeInfos.setValue(stmt, internalCount++, columns[0].toSQL(value), + _ctx.bindParameter(stmt, columns[0].getName(), columns[0].toSQL(value), columns[0].getSqlType()); } } } - return internalCount; } //----------------------------------------------------------------------------------- Index: cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/UUIDKeyGenerator.java =================================================================== --- cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/UUIDKeyGenerator.java (revision 8335) +++ cpa/src/main/java/org/castor/cpa/persistence/sql/keygen/UUIDKeyGenerator.java (working copy) @@ -115,12 +115,5 @@ return true; } - /** - * {@inheritDoc} - */ - public String patchSQL(final String insert, final String primKeyName) { - return insert; - } - //----------------------------------------------------------------------------------- } \ No newline at end of file Index: cpa/src/main/java/org/castor/cpa/persistence/sql/query/Insert.java =================================================================== --- cpa/src/main/java/org/castor/cpa/persistence/sql/query/Insert.java (revision 8314) +++ cpa/src/main/java/org/castor/cpa/persistence/sql/query/Insert.java (working copy) @@ -15,6 +15,7 @@ */ package org.castor.cpa.persistence.sql.query; +import java.text.MessageFormat; import java.util.List; import java.util.ArrayList; import java.util.Iterator; @@ -41,6 +42,12 @@ /** Parameter values needs to be inserted. */ private List _values; + /** Sequence expression of the form SEQUENCENAME.nextval. */ + private String _seqExpression; + + /** ID of the table. */ + private String _primKeyName; + //----------------------------------------------------------------------------------- /** @@ -87,6 +94,27 @@ addInsert(new Column(new Table(qualifier), name), new Parameter(name)); } + /** + * Appends sequence to the insert statement. + * + * @param seqName Name of the sequence. + * @param primKeyName ID of the Table. + */ + public void addSequence(final String seqName, final String primKeyName) { + _primKeyName = primKeyName; + _seqExpression = MessageFormat.format(seqName, + new Object[] {this._qualifier.name(), _primKeyName}); + _seqExpression += ".nextval"; + } + + /** + * + * @return {code}true{code} If sequence has been added to this instance + * of insert hierarchy. + */ + public boolean hasSequence() { + return !(_seqExpression == null); + } //----------------------------------------------------------------------------------- @Override @@ -98,8 +126,20 @@ _qualifier.toString(ctx); - ctx.append(QueryConstants.LPAREN); + if (hasSequence()) { + ctx.append(QueryConstants.LPAREN); + ctx.append(ctx.quoteName(_primKeyName)); + if (_fields != null) { + ctx.append(QueryConstants.SEPERATOR); + ctx.append(QueryConstants.SPACE); + } + } + if (_fields != null) { + if (!hasSequence()) { + ctx.append(QueryConstants.LPAREN); + } + for (Iterator iter = _fields.iterator(); iter.hasNext(); ) { iter.next().toString(ctx); if (iter.hasNext()) { @@ -108,11 +148,23 @@ } } } - ctx.append(QueryConstants.RPAREN); + if (hasSequence() || _fields != null) { + ctx.append(QueryConstants.RPAREN); + } + ctx.append(QueryConstants.SPACE); ctx.append(QueryConstants.VALUES); - ctx.append(QueryConstants.LPAREN); + ctx.append(QueryConstants.LPAREN); + + if (hasSequence()) { + ctx.append(ctx.quoteName(_seqExpression)); + if (_values != null) { + ctx.append(QueryConstants.SEPERATOR); + ctx.append(QueryConstants.SPACE); + } + } + if (_values != null) { for (Iterator iter = _values.iterator(); iter.hasNext(); ) { iter.next().toString(ctx); Index: cpa/src/main/java/org/castor/cpa/persistence/sql/query/QueryContext.java =================================================================== --- cpa/src/main/java/org/castor/cpa/persistence/sql/query/QueryContext.java (revision 8285) +++ cpa/src/main/java/org/castor/cpa/persistence/sql/query/QueryContext.java (working copy) @@ -155,5 +155,14 @@ } } + /** + * Returns the number of parameter in sql insert statement. + * + * @return parameter size. + */ + public final int parameterSize() { + return _parameters.size(); + } + //----------------------------------------------------------------------------------- } Index: src/doc/release-notes.xml =================================================================== --- src/doc/release-notes.xml (revision 8346) +++ src/doc/release-notes.xml (working copy) @@ -91,6 +91,26 @@ ]]> + + + Builds statements with Insert class hierarchy where no special syntax is required. + + + Ahmad Hassan + ahmad.hassan@gmail.com + + + Ralf Joachim + ralf.joachim@syscon.eu + + + Ahmad Hassan + ahmad.hassan@gmail.com + + Enh. + JDO + 20090804 + Move buildStatement from SQLStatementCreate to Key Generators.