Author: tfischer
Date: Fri Dec 27 22:40:44 2013
New Revision: 1553756
URL: http://svn.apache.org/r1553756
Log:
TORQUE-305 fix insert into ... select statements when primary key is not given
and sequences are used to generate primary keys
Modified:
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/oid/AbstractIdGenerator.java
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java
db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/DoInsertTest.java
Modified:
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/oid/AbstractIdGenerator.java
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/oid/AbstractIdGenerator.java?rev=1553756&r1=1553755&r2=1553756&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/oid/AbstractIdGenerator.java
(original)
+++
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/oid/AbstractIdGenerator.java
Fri Dec 27 22:40:44 2013
@@ -73,7 +73,7 @@ public abstract class AbstractIdGenerato
*
* @throws TorqueException if a database error occurs.
*/
- public int getIdAsInt(Connection connection, Object keyInfo)
+ public int getIdAsInt(final Connection connection, final Object keyInfo)
throws TorqueException
{
return getId(connection, keyInfo, new IntegerMapper());
@@ -89,7 +89,7 @@ public abstract class AbstractIdGenerato
*
* @throws TorqueException if a database error occurs.
*/
- public long getIdAsLong(Connection connection, Object keyInfo)
+ public long getIdAsLong(final Connection connection, final Object keyInfo)
throws TorqueException
{
return getId(connection, keyInfo, new LongMapper());
@@ -105,7 +105,7 @@ public abstract class AbstractIdGenerato
*
* @throws TorqueException if a database error occurs.
*/
- public BigDecimal getIdAsBigDecimal(Connection connection, Object keyInfo)
+ public BigDecimal getIdAsBigDecimal(final Connection connection, final
Object keyInfo)
throws TorqueException
{
return getId(connection, keyInfo, new BigDecimalMapper());
@@ -122,7 +122,7 @@ public abstract class AbstractIdGenerato
*
* @throws TorqueException if a database error occurs.
*/
- public String getIdAsString(Connection connection, Object keyInfo)
+ public String getIdAsString(final Connection connection, final Object
keyInfo)
throws TorqueException
{
return getId(connection, keyInfo, new StringMapper());
@@ -159,21 +159,36 @@ public abstract class AbstractIdGenerato
* appropriate java object.
*
* @return The generated id.
- * @exception TorqueException if a database error occurs.
+ * @throws TorqueException if a database error occurs.
*/
protected <T> T getId(
- Connection connection,
- Object keyInfo,
- RecordMapper<T> mapper)
+ final Connection connection,
+ final Object keyInfo,
+ final RecordMapper<T> mapper)
throws TorqueException
{
- String tableName = SqlBuilder.getFullTableName(
- String.valueOf(keyInfo),
- databaseName);
- String idSql = adapter.getIDMethodSQL(tableName);
+ String idSql = getIdSql(keyInfo);
BasePeerImpl<T> peer = new BasePeerImpl<T>(mapper, null, databaseName);
List<T> result = peer.doSelect(idSql, connection);
return result.get(0);
}
+
+ /**
+ * Returns the SQL to retrieve the next id.
+ *
+ * @param keyInfo an Object that contains additional info.
+ *
+ * @return the SQL to retrieve the next id.
+ *
+ * @throws TorqueException
+ */
+ public String getIdSql(final Object keyInfo) throws TorqueException
+ {
+ String tableName = SqlBuilder.getFullTableName(
+ String.valueOf(keyInfo),
+ databaseName);
+ String idSql = adapter.getIDMethodSQL(tableName);
+ return idSql;
+ }
}
Modified:
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java?rev=1553756&r1=1553755&r2=1553756&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java
(original)
+++
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java
Fri Dec 27 22:40:44 2013
@@ -35,6 +35,7 @@ import org.apache.commons.lang.StringUti
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.torque.Column;
+import org.apache.torque.ColumnImpl;
import org.apache.torque.Database;
import org.apache.torque.TooManyRowsException;
import org.apache.torque.Torque;
@@ -48,6 +49,7 @@ import org.apache.torque.map.ColumnMap;
import org.apache.torque.map.MapHelper;
import org.apache.torque.map.TableMap;
import org.apache.torque.oid.IdGenerator;
+import org.apache.torque.oid.SequenceIdGenerator;
import org.apache.torque.om.NumberKey;
import org.apache.torque.om.ObjectKey;
import org.apache.torque.om.SimpleKey;
@@ -636,7 +638,8 @@ public class BasePeerImpl<T> implements
/**
* Executes a insert into...select statement.
*
- * @param toInsertInto the columns in which to insert, not null.
+ * @param toInsertInto the columns in which to insert, not null,
+ * must not contain null.
* @param criteria the criteria which selects the values to insert,
* not null.
* @param dbName the database name, or null to take the database name
@@ -654,15 +657,74 @@ public class BasePeerImpl<T> implements
final Connection connection)
throws TorqueException
{
- if (dbName == null)
+ if (dbName == null)
{
dbName = getDatabaseName();
}
+ ColumnMap pk = getTableMap().getPrimaryKey();
+ boolean pkExistsInColumnMap = false;
+
List<String> columnNames = new ArrayList<String>();
for (Column column : toInsertInto)
{
columnNames.add(column.getColumnName());
+ if (pk != null
+ && column.getSqlExpression().equals(pk.getSqlExpression()))
+ {
+ pkExistsInColumnMap = true;
+ }
+ }
+ if (!pkExistsInColumnMap)
+ {
+ IDMethod idMethod = getTableMap().getPrimaryKeyMethod();
+ Adapter adapter = Torque.getAdapter(dbName);
+ if (idMethod == IDMethod.ID_BROKER)
+ {
+ log.debug("pk does not exist in column map and id method is "
+ + idMethod
+ + ", SQL will fail if column has no default value");
+ // try SQL, maybe database has a default value set
+ }
+ else if (idMethod == IDMethod.SEQUENCE
+ || (idMethod == IDMethod.NATIVE
+ && adapter.getIDMethodType() == IDMethod.SEQUENCE))
+ {
+ IdGenerator keyGen = Torque.getDatabase(dbName).getIdGenerator(
+ getTableMap().getPrimaryKeyMethod());
+ if (keyGen instanceof SequenceIdGenerator)
+ {
+ SequenceIdGenerator sequenceIdGenerator
+ = (SequenceIdGenerator) keyGen;
+ String idSql = sequenceIdGenerator.getIdSql(
+ getIdMethodInfo());
+ // This is a bit of a hack.
+ // The idSql is usually a stand-alone statement, but we
+ // need the part to be inserted as a column.
+ // Therefore we extract the complete word containing
+ // the term nextval
+ int nextvalPos = idSql.toLowerCase().indexOf("nextval");
+ int spacePos = idSql.lastIndexOf(" ", nextvalPos);
+ if (spacePos != -1)
+ {
+ idSql = idSql.substring(spacePos + 1);
+ }
+ spacePos = idSql.indexOf(" ");
+ if (spacePos != -1)
+ {
+ idSql = idSql.substring(0, idSql.indexOf(" "));
+ }
+ columnNames.add(pk.getColumnName());
+ criteria.addSelectColumn(
+ new ColumnImpl(null, null, null, idSql));
+ }
+ else
+ {
+ log.warn("id method is sequence "
+ + "but keyGen is no Sequence id generator, "
+ + "cannot add sequence generation info");
+ }
+ }
}
String fullTableName = SqlBuilder.getFullTableName(
Modified:
db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/DoInsertTest.java
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/DoInsertTest.java?rev=1553756&r1=1553755&r2=1553756&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/DoInsertTest.java
(original)
+++
db/torque/torque4/trunk/torque-test/src/test/java/org/apache/torque/generated/peer/DoInsertTest.java
Fri Dec 27 22:40:44 2013
@@ -23,13 +23,19 @@ import java.math.BigDecimal;
import java.sql.Types;
import java.util.List;
+import org.apache.commons.logging.Log;
+import org.apache.commons.logging.LogFactory;
import org.apache.torque.BaseDatabaseTestCase;
+import org.apache.torque.Column;
import org.apache.torque.ColumnImpl;
+import org.apache.torque.adapter.IDMethod;
import org.apache.torque.criteria.Criteria;
import org.apache.torque.om.ObjectKey;
import org.apache.torque.test.dbobject.Author;
+import org.apache.torque.test.dbobject.Book;
import org.apache.torque.test.dbobject.IntegerType;
import org.apache.torque.test.peer.AuthorPeer;
+import org.apache.torque.test.peer.BookPeer;
import org.apache.torque.test.peer.IntegerTypePeer;
import org.apache.torque.util.ColumnValues;
import org.apache.torque.util.JdbcTypedValue;
@@ -43,6 +49,8 @@ public class DoInsertTest extends BaseDa
{
private List<Author> authorList;
+ private static Log log =LogFactory.getLog(DoInsertTest.class);
+
@Override
public void setUp() throws Exception
{
@@ -131,4 +139,120 @@ public class DoInsertTest extends BaseDa
((BigDecimal) objectKey.getValue()).intValue(),
allIntegerTypes.get(0).getId());
}
+
+ /**
+ * Tests the doInsert method where the inserted values are selected
+ * from the table.
+ *
+ * @throws Exception if a database error occurs.
+ */
+ public void testDoInsertWithSelectSingleRecordIdSet()
+ throws Exception
+ {
+ Criteria selectCriteria = new Criteria()
+ .where(AuthorPeer.AUTHOR_ID, authorList.get(0).getAuthorId())
+ .addSelectColumn(new ColumnImpl(
+ AuthorPeer.AUTHOR_ID.getSchemaName(),
+ AuthorPeer.AUTHOR_ID.getTableName(),
+ AuthorPeer.AUTHOR_ID.getColumnName(),
+ AuthorPeer.AUTHOR_ID.getSqlExpression() + " + 100"))
+ .addSelectColumn(AuthorPeer.NAME);
+
+ // execute
+ int numberOfInsertedRows = AuthorPeer.doInsert(
+ new Column[] {AuthorPeer.AUTHOR_ID, AuthorPeer.NAME},
+ selectCriteria);
+
+ // verify
+ assertEquals(1, numberOfInsertedRows);
+ Author author = new Author();
+ author.setAuthorId(authorList.get(0).getAuthorId() + 100);
+ author.setName(authorList.get(0).getName());
+ authorList.add(author);
+ verifyBookstore(authorList);
+ }
+
+ /**
+ * Tests the doInsert method where the inserted values are selected
+ * from the table.
+ *
+ * @throws Exception if a database error occurs.
+ */
+ public void testDoInsertWithSelectSingleRecord()
+ throws Exception
+ {
+ if ( IDMethod.ID_BROKER ==
AuthorPeer.getTableMap().getPrimaryKeyMethod())
+ {
+ log.warn("Cannot test insert.. select statement with autoincrement"
+ + " id method");
+ return;
+ }
+ Criteria selectCriteria = new Criteria()
+ .where(AuthorPeer.AUTHOR_ID, authorList.get(0).getAuthorId())
+ .addSelectColumn(AuthorPeer.NAME);
+
+ // execute
+ int numberOfInsertedRows = AuthorPeer.doInsert(
+ new Column[] {AuthorPeer.NAME},
+ selectCriteria);
+
+ // verify
+ assertEquals(1, numberOfInsertedRows);
+ Author author = new Author();
+ author.setAuthorId(authorList.get(9).getAuthorId() + 1);
+ author.setName(authorList.get(0).getName());
+ authorList.add(author);
+ verifyBookstore(authorList);
+ }
+
+ /**
+ * Tests the doInsert method where the inserted values are selected
+ * from the table.
+ *
+ * @throws Exception if a database error occurs.
+ */
+ public void testDoInsertWithSelectMultipleRecords()
+ throws Exception
+ {
+ if ( IDMethod.ID_BROKER ==
AuthorPeer.getTableMap().getPrimaryKeyMethod())
+ {
+ log.warn("Cannot test insert.. select statement with autoincrement"
+ + " id method");
+ return;
+ }
+ Criteria selectCriteria = new Criteria()
+ .where(BookPeer.AUTHOR_ID, authorList.get(0).getAuthorId())
+ .addSelectColumn(BookPeer.TITLE);
+
+ // execute
+ int numberOfInsertedRows = AuthorPeer.doInsert(
+ new Column[] {AuthorPeer.NAME},
+ selectCriteria);
+
+ // verify
+ assertEquals(10, numberOfInsertedRows);
+ List<Author> selectedAuthorList = AuthorPeer.doSelect(new Criteria());
+ // check size of List
+ assertEquals(20, selectedAuthorList.size());
+ for (Author author : selectedAuthorList)
+ {
+ if (author.getAuthorId() > authorList.get(9).getAuthorId())
+ {
+ boolean found = false;
+ for (Book book : authorList.get(0).getBooks())
+ {
+ if (author.getName().equals(book.getTitle()))
+ {
+ found = true;
+ authorList.get(0).getBooks().remove(book);
+ break;
+ }
+ }
+ if (!found)
+ {
+ fail("unexpected author with name " + author.getName());
+ }
+ }
+ }
+ }
}
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-dev-unsubscr...@db.apache.org
For additional commands, e-mail: torque-dev-h...@db.apache.org
