Author: tfischer
Date: Sun Dec 15 13:10:57 2013
New Revision: 1551020
URL: http://svn.apache.org/r1551020
Log:
TORQUE-307 do not use qualified column names with updates
TORQUE-305 first step towards implementing insert into ... select statements
Modified:
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/Query.java
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/util/BasePeerImpl.java
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/util/BasePeerImplTest.java
Modified:
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/Query.java
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/Query.java?rev=1551020&r1=1551019&r2=1551020&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/Query.java
(original)
+++
db/torque/torque4/trunk/torque-runtime/src/main/java/org/apache/torque/sql/Query.java
Sun Dec 15 13:10:57 2013
@@ -570,7 +570,13 @@ public class Query
{
stringBuilder.append(",");
}
- stringBuilder.append(entry.getKey().getSqlExpression());
+ Column column = entry.getKey();
+ String columnName = column.getColumnName();
+ if (columnName == null)
+ {
+ columnName = column.getSqlExpression();
+ }
+ stringBuilder.append(columnName);
if (entry.getValue().getSqlExpression() == null)
{
stringBuilder.append("=?");
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=1551020&r1=1551019&r2=1551020&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
Sun Dec 15 13:10:57 2013
@@ -551,6 +551,174 @@ public class BasePeerImpl<T> implements
}
/**
+ * Executes a insert into...select statement.
+ *
+ * @param toInsertInto the columns in which to insert, not null.
+ * @param criteria the criteria which selects the values to insert,
+ * not null.
+ *
+ * @return the number of inserted rows, not null.
+ *
+ * @throws TorqueException if a database error occurs.
+ */
+ public int doInsert(
+ final Column[] toInsertInto,
+ final Criteria criteria)
+ throws TorqueException
+ {
+ return doInsert(toInsertInto, criteria, (String) null);
+ }
+
+ /**
+ * Executes a insert into...select statement.
+ *
+ * @param toInsertInto the columns in which to insert, not 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
+ * from getDatabaseName().
+ *
+ * @return the number of inserted rows, not null.
+ *
+ * @throws TorqueException if a database error occurs.
+ */
+ public int doInsert(
+ final Column[] toInsertInto,
+ final Criteria criteria,
+ final String dbName)
+ throws TorqueException
+ {
+ String databaseName = dbName;
+ if (databaseName == null)
+ {
+ databaseName = getDatabaseName();
+ }
+ Connection connection = null;
+ try
+ {
+ connection = Transaction.begin(dbName);
+ int numberOfInsertedRows
+ = doInsert(toInsertInto, criteria, dbName, connection);
+ Transaction.commit(connection);
+ connection = null;
+ return numberOfInsertedRows;
+ }
+ finally
+ {
+ if (connection != null)
+ {
+ Transaction.safeRollback(connection);
+ }
+ }
+ }
+
+ /**
+ * Executes a insert into...select statement.
+ *
+ * @param toInsertInto the columns in which to insert, not null.
+ * @param criteria the criteria which selects the values to insert,
+ * not null.
+ * @param connection the database connection to use, not null.
+ *
+ * @return the number of inserted rows, not null.
+ *
+ * @throws TorqueException if a database error occurs.
+ */
+ public int doInsert(
+ final Column[] toInsertInto,
+ final Criteria criteria,
+ final Connection connection)
+ throws TorqueException
+ {
+ return doInsert(toInsertInto, criteria, null, connection);
+ }
+
+ /**
+ * Executes a insert into...select statement.
+ *
+ * @param toInsertInto the columns in which to insert, not 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
+ * from getDatabaseName().
+ * @param connection the database connection to use, not null.
+ *
+ * @return the number of inserted rows, not null.
+ *
+ * @throws TorqueException if a database error occurs.
+ */
+ public int doInsert(
+ final Column[] toInsertInto,
+ final Criteria criteria,
+ String dbName,
+ final Connection connection)
+ throws TorqueException
+ {
+ if (dbName == null)
+ {
+ dbName = getDatabaseName();
+ }
+
+ List<String> columnNames = new ArrayList<String>();
+ for (Column column : toInsertInto)
+ {
+ columnNames.add(column.getColumnName());
+ }
+
+ String fullTableName = SqlBuilder.getFullTableName(
+ getTableMap().getFullyQualifiedTableName(),
+ dbName);
+ Query selectQuery = SqlBuilder.buildQuery(criteria);
+ StringBuilder query = new StringBuilder("INSERT INTO ")
+ .append(fullTableName)
+ .append("(")
+ .append(StringUtils.join(columnNames, ","))
+ .append(") ")
+ .append(selectQuery);
+
+ int numberOfInsertedRows = 0;
+ PreparedStatement preparedStatement = null;
+ try
+ {
+ preparedStatement = connection.prepareStatement(query.toString());
+ List<Object> replacements = setPreparedStatementReplacements(
+ preparedStatement,
+ selectQuery.getPreparedStatementReplacements(),
+ 0);
+ long startTime = System.currentTimeMillis();
+ log.debug("Executing insert " + query.toString()
+ + " using parameters " + replacements);
+
+ numberOfInsertedRows = preparedStatement.executeUpdate();
+ long queryEndTime = System.currentTimeMillis();
+ log.trace("insert took " + (queryEndTime - startTime)
+ + " milliseconds");
+
+ preparedStatement.close();
+ preparedStatement = null;
+ }
+ catch (SQLException e)
+ {
+ throw ExceptionMapper.getInstance().toTorqueException(e);
+ }
+ finally
+ {
+ if (preparedStatement != null)
+ {
+ try
+ {
+ preparedStatement.close();
+ }
+ catch (SQLException e)
+ {
+ log.warn("error closing prepared statement", e);
+ }
+ }
+ }
+ return numberOfInsertedRows;
+ }
+
+ /**
* Returns the idMethodInfo for the table for this Peer class.
*
* @return the idMethodInfo, not null.
Modified:
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/util/BasePeerImplTest.java
URL:
http://svn.apache.org/viewvc/db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/util/BasePeerImplTest.java?rev=1551020&r1=1551019&r2=1551020&view=diff
==============================================================================
---
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/util/BasePeerImplTest.java
(original)
+++
db/torque/torque4/trunk/torque-runtime/src/test/java/org/apache/torque/util/BasePeerImplTest.java
Sun Dec 15 13:10:57 2013
@@ -34,6 +34,7 @@ import java.sql.Types;
import java.util.List;
import org.apache.torque.BaseTestCase;
+import org.apache.torque.Column;
import org.apache.torque.ColumnImpl;
import org.apache.torque.Torque;
import org.apache.torque.TorqueException;
@@ -394,6 +395,43 @@ public class BasePeerImplTest extends Ba
}
/**
+ * Check that a doInsert with subselects works.
+ *
+ * @throws Exception if the test fails.
+ */
+ public void testDoInsertWithSubselect() throws Exception
+ {
+ // prepare
+ Criteria criteria = new Criteria();
+ criteria.addSelectColumn(stringColumnMap);
+ criteria.addSelectColumn(stringColumnMap2);
+ criteria.addSelectColumn(stringColumnMap3);
+ criteria.addSelectColumn(integerColumnMap);
+ criteria.where(stringColumnMap2, stringColumnMap3);
+
+ // execute
+ int result = basePeerImpl.doInsert(
+ new Column[] {
+ stringColumnMap2,
+ stringColumnMap3,
+ stringColumnMap,
+ integerColumnMap},
+ criteria,
+ databaseMap.getName());
+
+ // verify mock (verification order not relevant)
+ verify(connection).prepareStatement(
+ "INSERT INTO TABLE(COLUMN2,COLUMN3,COLUMN1,COLUMN4) SELECT
TABLE.COLUMN1, TABLE.COLUMN2, TABLE.COLUMN3, TABLE.COLUMN4 FROM TABLE WHERE
TABLE.COLUMN2=TABLE.COLUMN3");
+ verify(preparedStatement).executeUpdate();
+ verify(preparedStatement).close();
+ verify(transactionManager).begin(databaseMap.getName());
+ verify(transactionManager).commit(connection);
+ verifyNoMoreInteractions(connection, preparedStatement, resultSet,
transactionManager);
+ // verify result
+ assertEquals(1, result);
+ }
+
+ /**
* Check that Exception handling for doInsert works.
*
* @throws Exception if the test fails.
@@ -495,10 +533,10 @@ public class BasePeerImplTest extends Ba
// verify mock (verification order not relevant)
verify(connection).prepareStatement(
- "UPDATE TABLE SET TABLE.COLUMN1=?,"
- + "TABLE.COLUMN2=TABLE.COLUMN3,"
- + "TABLE.COLUMN3=?,"
- + "TABLE.COLUMN4=? "
+ "UPDATE TABLE SET COLUMN1=?,"
+ + "COLUMN2=TABLE.COLUMN3,"
+ + "COLUMN3=?,"
+ + "COLUMN4=? "
+ "WHERE TABLE.COLUMN4=?");
verify(preparedStatement).executeUpdate();
verify(preparedStatement).setObject(
@@ -539,9 +577,9 @@ public class BasePeerImplTest extends Ba
// verify mock (verification order not relevant)
verify(connection).prepareStatement(
- "UPDATE TABLE SET TABLE.COLUMN1=?,"
- + "TABLE.COLUMN2=someDbFunction,"
- + "TABLE.COLUMN3=? "
+ "UPDATE TABLE SET COLUMN1=?,"
+ + "COLUMN2=someDbFunction,"
+ + "COLUMN3=? "
+ "WHERE TABLE.COLUMN4=?");
verify(preparedStatement).executeUpdate();
verify(preparedStatement).setObject(
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
