Author: tomdz Date: Sun Apr 20 17:11:00 2008 New Revision: 649998 URL: http://svn.apache.org/viewvc?rev=649998&view=rev Log: Implementation of DDLUTILS-197
Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/Platform.java db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/PlatformImplBase.java db/ddlutils/trunk/src/test/org/apache/ddlutils/dynabean/TestDynaSqlQueries.java Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/Platform.java URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/java/org/apache/ddlutils/Platform.java?rev=649998&r1=649997&r2=649998&view=diff ============================================================================== --- db/ddlutils/trunk/src/java/org/apache/ddlutils/Platform.java (original) +++ db/ddlutils/trunk/src/java/org/apache/ddlutils/Platform.java Sun Apr 20 17:11:00 2008 @@ -949,6 +949,27 @@ public List fetch(Database model, String sql, Collection parameters, Table[] queryHints, int start, int end) throws DatabaseOperationException; /** + * Determines whether the given dyna bean is stored in the database. Note that this checks only + * checks the primary key, not the other attributes. + * + * @param model The database model to use + * @param dynaBean The bean + * @return <code>true</code> if a bean with this primary key exists in the database + */ + public boolean exists(Database model, DynaBean dynaBean); + + /** + * Determines whether the given dyna bean is stored in the database. Note that this checks only + * checks the primary key, not the other attributes. + * + * @param connection The connection + * @param model The database model to use + * @param dynaBean The bean + * @return <code>true</code> if a bean with this primary key exists in the database + */ + public boolean exists(Connection connection, Database model, DynaBean dynaBean); + + /** * Stores the given bean in the database, inserting it if there is no primary key * otherwise the bean is updated in the database. * @@ -956,6 +977,16 @@ * @param dynaBean The bean to store */ public void store(Database model, DynaBean dynaBean) throws DatabaseOperationException; + + /** + * Stores the given bean in the database, inserting it if there is no primary key + * otherwise the bean is updated in the database. + * + * @param connection The connection + * @param model The database model to use + * @param dynaBean The bean to store + */ + public void store(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException; /** * Returns the sql for inserting the given bean. Modified: db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/PlatformImplBase.java URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/PlatformImplBase.java?rev=649998&r1=649997&r2=649998&view=diff ============================================================================== --- db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/PlatformImplBase.java (original) +++ db/ddlutils/trunk/src/java/org/apache/ddlutils/platform/PlatformImplBase.java Sun Apr 20 17:11:00 2008 @@ -24,6 +24,7 @@ import java.lang.reflect.InvocationTargetException; import java.lang.reflect.Method; import java.math.BigDecimal; +import java.sql.BatchUpdateException; import java.sql.Blob; import java.sql.Clob; import java.sql.Connection; @@ -2160,7 +2161,16 @@ } catch (SQLException ex) { - throw new DatabaseOperationException("Error while inserting into the database", ex); + if (ex instanceof BatchUpdateException) + { + SQLException sqlEx = ((BatchUpdateException)ex).getNextException(); + + throw new DatabaseOperationException("Error while inserting into the database", sqlEx); + } + else + { + throw new DatabaseOperationException("Error while inserting into the database", ex); + } } } } @@ -2477,39 +2487,108 @@ } /** - * Determines whether the given dyna bean is stored in the database. - * - * @param dynaBean The bean - * @param connection The connection - * @return <code>true</code> if this dyna bean has a primary key + * [EMAIL PROTECTED] */ - protected boolean exists(Connection connection, DynaBean dynaBean) + public boolean exists(Database model, DynaBean dynaBean) { - // TODO: check for the pk value, and if present, query against database - return false; + Connection connection = borrowConnection(); + + try + { + return exists(connection, model, dynaBean); + } + finally + { + returnConnection(connection); + } } + /** * [EMAIL PROTECTED] */ - public void store(Database model, DynaBean dynaBean) throws DatabaseOperationException + public boolean exists(Connection connection, Database model, DynaBean dynaBean) { - Connection connection = borrowConnection(); + SqlDynaClass dynaClass = model.getDynaClassFor(dynaBean); + SqlDynaProperty[] primaryKeys = dynaClass.getPrimaryKeyProperties(); + + if (primaryKeys.length == 0) + { + return false; + } + + PreparedStatement stmt = null; try { - if (exists(connection, dynaBean)) + StringBuffer sql = new StringBuffer(); + + sql.append("SELECT * FROM "); + sql.append(_builder.getDelimitedIdentifier(dynaClass.getTable().getName())); + sql.append(" WHERE "); + + for (int idx = 0; idx < primaryKeys.length; idx++) { - update(connection, model, dynaBean); + String key = primaryKeys[idx].getColumn().getName(); + + if (idx > 0) + { + sql.append(" AND "); + } + sql.append(_builder.getDelimitedIdentifier(key)); + sql.append("=?"); } - else + + stmt = connection.prepareStatement(sql.toString()); + + for (int idx = 0; idx < primaryKeys.length; idx++) { - insert(connection, model, dynaBean); + setObject(stmt, idx + 1, dynaBean, primaryKeys[idx]); } + + ResultSet resultSet = stmt.executeQuery(); + + return resultSet.next(); + } + catch (SQLException ex) + { + throw new DatabaseOperationException("Error while reading from the database", ex); + } + finally + { + closeStatement(stmt); + } + } + + /** + * [EMAIL PROTECTED] + */ + public void store(Database model, DynaBean dynaBean) throws DatabaseOperationException + { + Connection connection = borrowConnection(); + + try + { + store(connection, model, dynaBean); } finally { returnConnection(connection); + } + } + + /** + * [EMAIL PROTECTED] + */ + public void store(Connection connection, Database model, DynaBean dynaBean) throws DatabaseOperationException + { + if (exists(connection, model, dynaBean)) + { + update(connection, model, dynaBean); + } + else + { + insert(connection, model, dynaBean); } } Modified: db/ddlutils/trunk/src/test/org/apache/ddlutils/dynabean/TestDynaSqlQueries.java URL: http://svn.apache.org/viewvc/db/ddlutils/trunk/src/test/org/apache/ddlutils/dynabean/TestDynaSqlQueries.java?rev=649998&r1=649997&r2=649998&view=diff ============================================================================== --- db/ddlutils/trunk/src/test/org/apache/ddlutils/dynabean/TestDynaSqlQueries.java (original) +++ db/ddlutils/trunk/src/test/org/apache/ddlutils/dynabean/TestDynaSqlQueries.java Sun Apr 20 17:11:00 2008 @@ -19,6 +19,7 @@ * under the License. */ +import java.util.ArrayList; import java.util.List; import org.apache.commons.beanutils.DynaBean; @@ -310,5 +311,267 @@ assertFalse(it.hasNext()); assertFalse(it.isConnectionOpen()); + } + + /** + * Tests the insert method. + */ + public void testInsertSingle() throws Exception + { + createDatabase( + "<?xml version='1.0' encoding='ISO-8859-1'?>\n"+ + "<database name='ddlutils'>\n"+ + " <table name='TestTable'>\n"+ + " <column name='TheId' type='INTEGER' primaryKey='true' required='true'/>\n"+ + " <column name='TheText' type='VARCHAR' size='15'/>\n"+ + " </table>\n"+ + "</database>"); + + SqlDynaClass dynaClass = SqlDynaClass.newInstance(getModel().getTable(0)); + DynaBean dynaBean = new SqlDynaBean(dynaClass); + + dynaBean.set("TheId", new Integer(1)); + dynaBean.set("TheText", "Text 1"); + + getPlatform().insert(getModel(), dynaBean); + + List beans = getPlatform().fetch(getModel(), + "SELECT * FROM TestTable", + new Table[] { getModel().getTable(0) }); + + assertEquals(1, + beans.size()); + + DynaBean bean = (DynaBean)beans.get(0); + + assertEquals(new Integer(1), + getPropertyValue(bean, "TheId")); + assertEquals("Text 1", + getPropertyValue(bean, "TheText")); + } + + /** + * Tests the insert method. + */ + public void testInsertMultiple() throws Exception + { + createDatabase( + "<?xml version='1.0' encoding='ISO-8859-1'?>\n"+ + "<database name='ddlutils'>\n"+ + " <table name='TestTable'>\n"+ + " <column name='TheId' type='INTEGER' primaryKey='true' required='true'/>\n"+ + " <column name='TheText' type='VARCHAR' size='15'/>\n"+ + " </table>\n"+ + "</database>"); + + SqlDynaClass dynaClass = SqlDynaClass.newInstance(getModel().getTable(0)); + DynaBean dynaBean1 = new SqlDynaBean(dynaClass); + DynaBean dynaBean2 = new SqlDynaBean(dynaClass); + DynaBean dynaBean3 = new SqlDynaBean(dynaClass); + + dynaBean1.set("TheId", new Integer(1)); + dynaBean1.set("TheText", "Text 1"); + dynaBean2.set("TheId", new Integer(2)); + dynaBean2.set("TheText", "Text 2"); + dynaBean3.set("TheId", new Integer(3)); + dynaBean3.set("TheText", "Text 3"); + + List dynaBeans = new ArrayList(); + + dynaBeans.add(dynaBean1); + dynaBeans.add(dynaBean2); + dynaBeans.add(dynaBean3); + + getPlatform().insert(getModel(), dynaBeans); + + List beans = getPlatform().fetch(getModel(), + "SELECT * FROM TestTable", + new Table[] { getModel().getTable(0) }); + + assertEquals(3, + beans.size()); + + DynaBean bean = (DynaBean)beans.get(0); + + assertEquals(new Integer(1), + getPropertyValue(bean, "TheId")); + assertEquals("Text 1", + getPropertyValue(bean, "TheText")); + + bean = (DynaBean)beans.get(1); + + assertEquals(new Integer(2), + getPropertyValue(bean, "TheId")); + assertEquals("Text 2", + getPropertyValue(bean, "TheText")); + + bean = (DynaBean)beans.get(2); + + assertEquals(new Integer(3), + getPropertyValue(bean, "TheId")); + assertEquals("Text 3", + getPropertyValue(bean, "TheText")); + } + + /** + * Tests the update method. + */ + public void testUpdate() throws Exception + { + createDatabase( + "<?xml version='1.0' encoding='ISO-8859-1'?>\n"+ + "<database name='ddlutils'>\n"+ + " <table name='TestTable'>\n"+ + " <column name='TheId' type='INTEGER' primaryKey='true' required='true'/>\n"+ + " <column name='TheText' type='VARCHAR' size='15'/>\n"+ + " </table>\n"+ + "</database>"); + + insertData( + "<?xml version='1.0' encoding='ISO-8859-1'?>\n"+ + "<data>\n"+ + " <TestTable TheId='1' TheText='Text 1'/>\n"+ + "</data>"); + + SqlDynaClass dynaClass = SqlDynaClass.newInstance(getModel().getTable(0)); + DynaBean dynaBean = new SqlDynaBean(dynaClass); + + dynaBean.set("TheId", new Integer(1)); + dynaBean.set("TheText", "Text 10"); + + getPlatform().update(getModel(), dynaBean); + + List beans = getPlatform().fetch(getModel(), + "SELECT * FROM TestTable", + new Table[] { getModel().getTable(0) }); + + assertEquals(1, + beans.size()); + + DynaBean bean = (DynaBean)beans.get(0); + + assertEquals(new Integer(1), + getPropertyValue(bean, "TheId")); + assertEquals("Text 10", + getPropertyValue(bean, "TheText")); + } + + /** + * Tests the exists method. + */ + public void testExists() throws Exception + { + createDatabase( + "<?xml version='1.0' encoding='ISO-8859-1'?>\n"+ + "<database name='ddlutils'>\n"+ + " <table name='TestTable'>\n"+ + " <column name='TheId' type='INTEGER' primaryKey='true' required='true'/>\n"+ + " <column name='TheText' type='VARCHAR' size='15'/>\n"+ + " </table>\n"+ + "</database>"); + + insertData( + "<?xml version='1.0' encoding='ISO-8859-1'?>\n"+ + "<data>\n"+ + " <TestTable TheId='1' TheText='Text 1'/>\n"+ + " <TestTable TheId='3' TheText='Text 3'/>\n"+ + "</data>"); + + SqlDynaClass dynaClass = SqlDynaClass.newInstance(getModel().getTable(0)); + DynaBean dynaBean1 = new SqlDynaBean(dynaClass); + DynaBean dynaBean2 = new SqlDynaBean(dynaClass); + DynaBean dynaBean3 = new SqlDynaBean(dynaClass); + + dynaBean1.set("TheId", new Integer(1)); + dynaBean1.set("TheText", "Text 1"); + dynaBean2.set("TheId", new Integer(2)); + dynaBean2.set("TheText", "Text 2"); + dynaBean3.set("TheId", new Integer(3)); + dynaBean3.set("TheText", "Text 30"); + + assertTrue(getPlatform().exists(getModel(), dynaBean1)); + assertFalse(getPlatform().exists(getModel(), dynaBean2)); + assertTrue(getPlatform().exists(getModel(), dynaBean3)); + } + + + /** + * Tests the store method. + */ + public void testStoreNew() throws Exception + { + createDatabase( + "<?xml version='1.0' encoding='ISO-8859-1'?>\n"+ + "<database name='ddlutils'>\n"+ + " <table name='TestTable'>\n"+ + " <column name='TheId' type='INTEGER' primaryKey='true' required='true'/>\n"+ + " <column name='TheText' type='VARCHAR' size='15'/>\n"+ + " </table>\n"+ + "</database>"); + + SqlDynaClass dynaClass = SqlDynaClass.newInstance(getModel().getTable(0)); + DynaBean dynaBean = new SqlDynaBean(dynaClass); + + dynaBean.set("TheId", new Integer(1)); + dynaBean.set("TheText", "Text 1"); + + getPlatform().store(getModel(), dynaBean); + + List beans = getPlatform().fetch(getModel(), + "SELECT * FROM TestTable", + new Table[] { getModel().getTable(0) }); + + assertEquals(1, + beans.size()); + + DynaBean bean = (DynaBean)beans.get(0); + + assertEquals(new Integer(1), + getPropertyValue(bean, "TheId")); + assertEquals("Text 1", + getPropertyValue(bean, "TheText")); + } + + /** + * Tests the store method. + */ + public void testStoreExisting() throws Exception + { + createDatabase( + "<?xml version='1.0' encoding='ISO-8859-1'?>\n"+ + "<database name='ddlutils'>\n"+ + " <table name='TestTable'>\n"+ + " <column name='TheId' type='INTEGER' primaryKey='true' required='true'/>\n"+ + " <column name='TheText' type='VARCHAR' size='15'/>\n"+ + " </table>\n"+ + "</database>"); + + insertData( + "<?xml version='1.0' encoding='ISO-8859-1'?>\n"+ + "<data>\n"+ + " <TestTable TheId='1' TheText='Text 1'/>\n"+ + "</data>"); + + SqlDynaClass dynaClass = SqlDynaClass.newInstance(getModel().getTable(0)); + DynaBean dynaBean = new SqlDynaBean(dynaClass); + + dynaBean.set("TheId", new Integer(1)); + dynaBean.set("TheText", "Text 10"); + + getPlatform().store(getModel(), dynaBean); + + List beans = getPlatform().fetch(getModel(), + "SELECT * FROM TestTable", + new Table[] { getModel().getTable(0) }); + + assertEquals(1, + beans.size()); + + DynaBean bean = (DynaBean)beans.get(0); + + assertEquals(new Integer(1), + getPropertyValue(bean, "TheId")); + assertEquals("Text 10", + getPropertyValue(bean, "TheText")); } }