Author: kentam
Date: Mon Oct 18 09:26:39 2004
New Revision: 55020
Modified:
incubator/beehive/trunk/controls/test/src/controls/org/apache/beehive/controls/test/controls/database/DatabaseControlImpl.jcs
incubator/beehive/trunk/controls/test/src/controls/org/apache/beehive/controls/test/controls/database/SQLParameter.java
incubator/beehive/trunk/controls/test/src/controls/org/apache/beehive/controls/test/controls/database/SQLStatement.java
incubator/beehive/trunk/controls/test/src/controls/org/apache/beehive/controls/test/controls/database/test/TestDBControl.jcx
incubator/beehive/trunk/controls/test/src/drivers/org/apache/beehive/controls/test/driver/database/DriveDatabaseControl.java
incubator/beehive/trunk/controls/test/src/units/org/apache/beehive/controls/test/java/database/ParserTest.java
incubator/beehive/trunk/controls/test/src/units/org/apache/beehive/controls/test/java/database/SQLStatementTest.java
Log:
Database control now supports parameter substitution by name. Parameter
substitution
by position is no longer supported, as substitution by name is a much better
approach.
Contributor:
Hoi Lam
Modified:
incubator/beehive/trunk/controls/test/src/controls/org/apache/beehive/controls/test/controls/database/DatabaseControlImpl.jcs
==============================================================================
---
incubator/beehive/trunk/controls/test/src/controls/org/apache/beehive/controls/test/controls/database/DatabaseControlImpl.jcs
(original)
+++
incubator/beehive/trunk/controls/test/src/controls/org/apache/beehive/controls/test/controls/database/DatabaseControlImpl.jcs
Mon Oct 18 09:26:39 2004
@@ -31,6 +31,8 @@
private static final String ERROR_MISSING_STATEMENT_MEMBER = "Statement
member not defined in SQL attribute";
private static final String ERROR_ITERATOR_ELEMENT_TYPE_NOT_SPECIFIED =
"Iterator element type is not specified.";
private static final String ERROR_RESULT_NOT_MATCH_RETURN_TYPE = "The
results of the SQL provided does not match return type of method";
+ private static final String ERROR_INVALID_ARGUMENT_NAME = "Invalid
argument name in SQL statement";
+ private static final String ERROR_PARAMETER_SUBSTITUTION_FAILED = "Failed
to substitute parameter by name";
@org.apache.beehive.controls.api.context.Context ControlBeanContext
context;
@org.apache.beehive.controls.api.context.Context ResourceContext
resourceContext;
@@ -130,7 +132,9 @@
SQLParser parser = new SQLParser(statement);
SQLStatement sql = parser.parse();
//Set the parameters of the SQL statement based on the method arguments
- sql.setParameterValues(args);
+ //sql.setParameterValuesByPosition(args);
+ this.setParameterValues(sql.getParameters(), method, args);
+
PreparedStatement ps = sql.getPreparedStatement(this.getConnection());
//Set the max. rows returned
@@ -209,4 +213,26 @@
return returnObject;
}
+ private void setParameterValues(SQLParameter[] params, Method method,
Object[] args)
+ {
+ for (int i = 0; i < params.length; i++)
+ {
+ SQLParameter param = params[i];
+ String firstNameComponent =
param.getFirstNameComponent();
+
+ try
+ {
+ Object value =
context.getParameterValue(method, firstNameComponent, args);
+ param.setValue(value);
+ }
+ catch (IllegalArgumentException iae)
+ {
+ throw new
ControlException(ERROR_INVALID_ARGUMENT_NAME);
+ }
+ catch (Exception e)
+ {
+ throw new
ControlException(ERROR_PARAMETER_SUBSTITUTION_FAILED, e);
+ }
+ }
+ }
}
Modified:
incubator/beehive/trunk/controls/test/src/controls/org/apache/beehive/controls/test/controls/database/SQLParameter.java
==============================================================================
---
incubator/beehive/trunk/controls/test/src/controls/org/apache/beehive/controls/test/controls/database/SQLParameter.java
(original)
+++
incubator/beehive/trunk/controls/test/src/controls/org/apache/beehive/controls/test/controls/database/SQLParameter.java
Mon Oct 18 09:26:39 2004
@@ -1,21 +1,27 @@
package org.apache.beehive.controls.test.controls.database;
import java.sql.Types;
+import java.lang.reflect.Field;
+import java.lang.reflect.Method;
+import java.util.Map;
/**
* A parameter in the SQLStatment.
*/
public class SQLParameter
{
+ private static final String ERROR_INVOKING_GETTER_METHOD = "Failed to
invoke a getter method while setting parameter value";
+ private static final String ERROR_SET_PARAMETER_VALUE_FAILED = "Failed
to set parameter value";
+
/**
* Parameter name
*/
- public String name;
-
+ private String name;
+
/**
* Parameter value
*/
- public Object value;
+ private Object value;
/**
* Constructor
@@ -34,11 +40,176 @@
public SQLParameter(String name, Object value)
{
this.name = name;
- this.value = value;
+ try
+ {
+ this.setValue(value);
+ }
+ catch (Exception e)
+ {
+ //TODO log exception
+ }
}
+ /**
+ * @return a copy of this parameter
+ */
public Object clone()
{
return new SQLParameter(name, value);
}
+
+ /**
+ * Sets the name of this parameter
+ *
+ * @param name name of the parameter
+ */
+ public void setName(String name)
+ {
+ this.name = name;
+ }
+
+ /**
+ * Gets the name of this parameter
+ *
+ * @return name of the parameter
+ */
+ public String getName()
+ {
+ return this.name;
+ }
+
+ /**
+ * The parameter name can be made up of multiple components.
+ * e.g. "employee.address.street"
+ * This method returns each component in a String array.
+ * e.g. {"employee", "address", "street"}
+ *
+ * @return components of the parameter name
+ */
+ public String[] getNameComponents()
+ {
+ return this.name.split("\\.");
+ }
+
+ /**
+ * This method returns the first component in the parameter name.
+ * e.g. It returns "employee" when the name is
"employee.address.street"
+ *
+ * @return the first component in the parameter name
+ */
+ public String getFirstNameComponent()
+ {
+ String[] nameComponents = this.getNameComponents();
+ return nameComponents[0];
+ }
+
+ /**
+ * Returns the value of this parameter.
+ *
+ * @return the value of this parameter
+ */
+ public Object getValue()
+ {
+ return this.value;
+ }
+
+ /**
+ * Sets the value of this parameter
+ *
+ * @param aValue value of this parameter
+ */
+ public void setValue(Object aValue) throws Exception
+ {
+ String[] nameComponents = this.getNameComponents();
+ this.value = aValue;
+ for (int i = 1; i < nameComponents.length; i++)
+ {
+ this.value = extractValue(this.value,
nameComponents[i]);
+ }
+ }
+
+ /**
+ * Returns the value of a field in object where the name of the field
matches fieldName.
+ */
+ private Object extractValue(Object object, String fieldName) throws
Exception
+ {
+ Class objectClass = object.getClass();
+ Object value = null;
+
+ String fieldNameCapped =
Character.toUpperCase(fieldName.charAt(0)) + fieldName.substring(1);
+ Method getMethod = null;
+ Method[] methods = objectClass.getMethods();
+
+ //Check if a is<fieldName>() method is available in object.
+ String methodName = "is" + fieldNameCapped;
+ for (int i = 0; i < methods.length; i++)
+ {
+ if (methods[i].getName().equals(methodName) &&
+ methods[i].getParameterTypes().length == 0 &&
+
(methods[i].getReturnType().equals(Boolean.class) ||
+
methods[i].getReturnType().equals(Boolean.TYPE)))
+ {
+ getMethod = methods[i];
+ break;
+ }
+ }
+
+ if (getMethod == null)
+ {
+ //Check if a get<fieldName>() method is available in
object.
+ methodName = "get" + fieldNameCapped;
+ for (int i = 0; i < methods.length; i++)
+ {
+ if (methods[i].getName().equals(methodName) &&
+ methods[i].getParameterTypes().length
== 0)
+ {
+ getMethod = methods[i];
+ break;
+ }
+ }
+ }
+
+ //If object contains a getter method for fieldName, invoke the
method
+ //to get the value of fieldName.
+ if (getMethod != null)
+ {
+ try
+ {
+ value = getMethod.invoke(object,
(Object[])null);
+ }
+ catch (Exception e)
+ {
+ throw new
Exception(ERROR_INVOKING_GETTER_METHOD, e);
+ }
+ return value;
+ }
+
+ //If a field named fieldName exists, return the value of this
field.
+ Field[] fields = objectClass.getFields();
+ for (int i = 0; i < fields.length; i++)
+ {
+ if (fields[i].getName().equals(fieldName))
+ {
+ try
+ {
+ return fields[i].get(object);
+ }
+ catch (Exception e)
+ {
+ throw new
Exception(ERROR_INVOKING_GETTER_METHOD, e);
+ }
+ }
+ }
+
+ //If object is a Map and it contains a key equals fieldName,
then return the value
+ //mapped to key.
+ if (object instanceof Map &&
((Map)object).containsKey(fieldName))
+ {
+ return ((Map)object).get(fieldName);
+ }
+
+ //Failed to get the value of fieldName from object.
+ throw new Exception(ERROR_SET_PARAMETER_VALUE_FAILED);
+ }
+
}
Modified:
incubator/beehive/trunk/controls/test/src/controls/org/apache/beehive/controls/test/controls/database/SQLStatement.java
==============================================================================
---
incubator/beehive/trunk/controls/test/src/controls/org/apache/beehive/controls/test/controls/database/SQLStatement.java
(original)
+++
incubator/beehive/trunk/controls/test/src/controls/org/apache/beehive/controls/test/controls/database/SQLStatement.java
Mon Oct 18 09:26:39 2004
@@ -158,27 +158,33 @@
return this;
}
- /**
+ /*
* Assigns values to the parameters in the SQL parameter by order
* @param values values to be assigned to the SQL statement's parameters.
* @throws ControlException
- */
- public void setParameterValues(Object[] values) throws Exception{
- for (SQLParameter param : parameters) {
+
+ public void setParameterValuesByPosition(Object[] values) throws Exception{
+ for (SQLParameter param : parameters)
+ {
int pos;
- try {
+ try
+ {
pos = Integer.parseInt(param.name);
//offset for zero base values array.
pos -= 1;
- } catch (NumberFormatException nfe) {
+ }
+ catch (NumberFormatException nfe)
+ {
throw new Exception("Parameter name is not an integer.", nfe);
}
- if (pos < 0 || pos >= values.length) {
+ if (pos < 0 || pos >= values.length)
+ {
throw new Exception("No argument provided for parameter: " +
pos);
}
param.value = values[pos];
}
}
+ */
/**
* This method returns a prepared statement constructed based on the
@@ -188,11 +194,12 @@
public PreparedStatement getPreparedStatement(Connection connection)
throws SQLException
{
PreparedStatement ps = connection.prepareStatement(getSQL());
- for (int i = 0; i < this.parameters.size(); i++) {
+ for (int i = 0; i < this.parameters.size(); i++)
+ {
//prepared statement index is 1-based not zero-based as the
parameters list.
int pos = i + 1;
SQLParameter param = this.parameters.get(i);
- PreparedStatementHelper.setPreparedStatementParameter(ps, pos,
param.value, calendar);
+ PreparedStatementHelper.setPreparedStatementParameter(ps, pos,
param.getValue(), calendar);
}
return ps;
}
Modified:
incubator/beehive/trunk/controls/test/src/controls/org/apache/beehive/controls/test/controls/database/test/TestDBControl.jcx
==============================================================================
---
incubator/beehive/trunk/controls/test/src/controls/org/apache/beehive/controls/test/controls/database/test/TestDBControl.jcx
(original)
+++
incubator/beehive/trunk/controls/test/src/controls/org/apache/beehive/controls/test/controls/database/test/TestDBControl.jcx
Mon Oct 18 09:26:39 2004
@@ -22,12 +22,11 @@
@SQL(statement="INSERT INTO EMPLOYEE " +
"(id, fName, lName, title) " +
- "VALUES ({1}, {2}, {3}, {4})")
- public void insertEmployee(int p_id, String p_fName, String p_lName,
- String p_title) throws SQLException;
+ "VALUES ({emp.id}, {emp.fName}, {emp.lName}, {emp.title})")
+ public void insertEmployee(Employee emp) throws SQLException;
- @SQL(statement="SELECT * FROM EMPLOYEE WHERE id={1}")
- public Employee selectEmployee(int p_id) throws SQLException;
+ @SQL(statement="SELECT * FROM EMPLOYEE WHERE id={id}")
+ public Employee selectEmployee(int id) throws SQLException;
@SQL(statement="SELECT * FROM EMPLOYEE ORDER BY id")
public Employee[] selectEmployees() throws SQLException;
@@ -41,9 +40,9 @@
@SQL(statement="SELECT * FROM EMPLOYEE ORDER BY id", maxRows=1)
public Employee[] selectOneEmployee() throws SQLException;
- @SQL(statement="UPDATE EMPLOYEE SET title = {2} WHERE id = {1}")
- public void changeTitle(int p_id, String p_title) throws SQLException;
+ @SQL(statement="UPDATE EMPLOYEE SET title = {title} WHERE id = {id}")
+ public void changeTitle(int id, String title) throws SQLException;
- @SQL(statement="DELETE FROM EMPLOYEE WHERE id = {1}")
- public void deleteEmployee(int p_id) throws SQLException;
+ @SQL(statement="DELETE FROM EMPLOYEE WHERE id = {id}")
+ public void deleteEmployee(int id) throws SQLException;
}
Modified:
incubator/beehive/trunk/controls/test/src/drivers/org/apache/beehive/controls/test/driver/database/DriveDatabaseControl.java
==============================================================================
---
incubator/beehive/trunk/controls/test/src/drivers/org/apache/beehive/controls/test/driver/database/DriveDatabaseControl.java
(original)
+++
incubator/beehive/trunk/controls/test/src/drivers/org/apache/beehive/controls/test/driver/database/DriveDatabaseControl.java
Mon Oct 18 09:26:39 2004
@@ -92,7 +92,7 @@
//Insert test employees
for (int i = 0; i < emps.length; i++)
{
- testDBControl.insertEmployee(emps[i].id,
emps[i].fName, emps[i].lName, emps[i].title);
+ testDBControl.insertEmployee(emps[i]);
}
//Retrieve the two employees just inserted, and verify
they match those
Modified:
incubator/beehive/trunk/controls/test/src/units/org/apache/beehive/controls/test/java/database/ParserTest.java
==============================================================================
---
incubator/beehive/trunk/controls/test/src/units/org/apache/beehive/controls/test/java/database/ParserTest.java
(original)
+++
incubator/beehive/trunk/controls/test/src/units/org/apache/beehive/controls/test/java/database/ParserTest.java
Mon Oct 18 09:26:39 2004
@@ -39,8 +39,8 @@
SQLParameter[] params = stmt.getParameters();
assertNotNull(params);
assertEquals(2, params.length);
- assertEquals("1", params[0].name);
- assertEquals("2", params[1].name);
+ assertEquals("1", params[0].getName());
+ assertEquals("2", params[1].getName());
}
@Freq("checkin")
Modified:
incubator/beehive/trunk/controls/test/src/units/org/apache/beehive/controls/test/java/database/SQLStatementTest.java
==============================================================================
---
incubator/beehive/trunk/controls/test/src/units/org/apache/beehive/controls/test/java/database/SQLStatementTest.java
(original)
+++
incubator/beehive/trunk/controls/test/src/units/org/apache/beehive/controls/test/java/database/SQLStatementTest.java
Mon Oct 18 09:26:39 2004
@@ -53,7 +53,8 @@
String fName = "Tom";
String lName = "Clancy";
String title = "Writer";
- try {
+ try
+ {
int rowsInserted = insertRow(fName, lName, title);
assertEquals(1, rowsInserted);
Employee emp = selectByName(fName, lName);
@@ -61,7 +62,9 @@
assertEquals(fName, emp.fName);
assertEquals(lName, emp.lName);
assertEquals(title, emp.title);
- } catch (Exception e) {
+ }
+ catch (Exception e)
+ {
assertTrue("The following error occurred in testInsert(): " + e,
false);
}
@@ -72,22 +75,26 @@
PreparedStatement ps = null;
ResultSet rs = null;
- try {
+ try
+ {
int rowsInserted = insertRow(this.firstName, this.lastName,
this.title);
assertEquals(1, rowsInserted);
String sql = "DELETE FROM SQLTEST_EMPLOYEE WHERE ID=?";
Object[] values = {this.id};
- SQLParameter[] params = {new SQLParameter("1")};
+ SQLParameter[] params = {new SQLParameter("id", this.id)};
SQLStatement stmt = new SQLStatement(sql, params);
- stmt.setParameterValues(values);
ps = stmt.getPreparedStatement(con);
int rowsDeleted = ps.executeUpdate();
assertEquals(1, rowsDeleted);
Employee emp = selectById();
assertNull(emp);
- } catch (Exception e) {
+ }
+ catch (Exception e)
+ {
assertTrue("The following error occurred in testDelete(): " + e,
false);
- } finally {
+ }
+ finally
+ {
try { rs.close(); } catch (Throwable t) {}
try { ps.close(); } catch (Throwable t) {}
}
@@ -97,15 +104,18 @@
public void testUpdate() {
PreparedStatement ps = null;
- try {
+ try
+ {
int rowsInserted = insertRow(this.firstName, this.lastName,
this.title);
assertEquals(1, rowsInserted);
String sql = "UPDATE SQLTEST_EMPLOYEE SET FNAME=? WHERE ID=?";
- Object[] values = {this.id, this.newFirstName};
- SQLParameter[] params = { new SQLParameter("2"), new
SQLParameter("1")};
+ SQLParameter[] params =
+ {
+ new SQLParameter("FNAME",
this.newFirstName),
+ new SQLParameter("ID", this.id)
+ };
SQLStatement stmt = new SQLStatement(sql, params);
- stmt.setParameterValues(values);
ps = stmt.getPreparedStatement(this.con);
int rowsUpdated = ps.executeUpdate();
assertEquals(1, rowsUpdated);
@@ -114,9 +124,13 @@
assertNotNull(emp);
assertEquals(this.newFirstName, emp.fName);
- } catch (Exception e) {
+ }
+ catch (Exception e)
+ {
assertTrue("The following error occurred in testUpdate(): " + e,
false);
- } finally {
+ }
+ finally
+ {
try { ps.close(); } catch (Throwable t) {}
}
@@ -125,16 +139,22 @@
private int insertRow(String fName, String lName, String title) throws
Exception
{
String sql = "INSERT INTO SQLTEST_EMPLOYEE (ID, FNAME, LNAME, TITLE)
VALUES (?,?,?,?)";
- Object[] values = {++this.id, fName, lName, title};
- SQLParameter[] params = { new SQLParameter("1"), new SQLParameter("2"),
- new SQLParameter("3"), new SQLParameter("4")};
+ SQLParameter[] params =
+ {
+ new SQLParameter("ID", ++this.id),
+ new SQLParameter("FNAME", fName),
+ new SQLParameter("LNAME", lName),
+ new SQLParameter("TITLE", title)
+ };
PreparedStatement ps = null;
- try {
+ try
+ {
SQLStatement stmt = new SQLStatement(sql, params);
- stmt.setParameterValues(values);
ps = stmt.getPreparedStatement(con);
return ps.executeUpdate();
- } finally {
+ }
+ finally
+ {
try { ps.close(); } catch (Throwable t) {}
}
@@ -145,13 +165,15 @@
PreparedStatement ps = null;
ResultSet rs = null;
- try {
- String sql = "SELECT * FROM SQLTEST_EMPLOYEE WHERE fName=? and
lName=?";
- String[] values = {fName, lName};
- SQLParameter[] params = { new SQLParameter("1"), new
SQLParameter("2")};
-
+ try
+ {
+ String sql = "SELECT * FROM SQLTEST_EMPLOYEE WHERE FNAME=? and
LNAME=?";
+ SQLParameter[] params =
+ {
+ new SQLParameter("FNAME", fName),
+ new SQLParameter("LNAME", lName)
+ };
SQLStatement stmt = new SQLStatement(sql, params);
- stmt.setParameterValues(values);
ps = stmt.getPreparedStatement(con);
rs = ps.executeQuery();
@@ -160,7 +182,9 @@
else
return null;
- } finally {
+ }
+ finally
+ {
try { rs.close(); } catch (Throwable t) {}
try { ps.close(); } catch (Throwable t) {}
}
@@ -172,13 +196,13 @@
PreparedStatement ps = null;
ResultSet rs = null;
- try {
+ try
+ {
String sql = "SELECT * FROM SQLTEST_EMPLOYEE WHERE ID=?";
- Object[] values = {new Integer(this.id)};
- SQLParameter[] params = { new SQLParameter("1")};
+ SQLParameter[] params =
+ { new SQLParameter("ID", new Integer(this.id))};
SQLStatement stmt = new SQLStatement(sql, params);
- stmt.setParameterValues(values);
ps = stmt.getPreparedStatement(con);
rs = ps.executeQuery();
@@ -187,7 +211,9 @@
else
return null;
- } finally {
+ }
+ finally
+ {
try { rs.close(); } catch (Throwable t) {}
try { ps.close(); } catch (Throwable t) {}
}
@@ -197,10 +223,13 @@
private static Connection getConnection() {
Connection con = null;
- try {
+ try
+ {
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
con =
DriverManager.getConnection("jdbc:derby:build/databaseControlTestDB;create=true");
- } catch (Exception e) {
+ }
+ catch (Exception e)
+ {
e.printStackTrace();
}
return con;
@@ -212,13 +241,18 @@
"FNAME VARCHAR(20), LNAME VARCHAR(20), TITLE VARCHAR(15))";
PreparedStatement ps = null;
- try {
+ try
+ {
SQLStatement stmt = new SQLStatement(sql);
ps = stmt.getPreparedStatement(con);
ps.execute();
- } catch (Exception e) {
+ }
+ catch (Exception e)
+ {
assertTrue("The following error occurred in createTable(): " + e,
false);
- } finally {
+ }
+ finally
+ {
try { ps.close(); } catch (Throwable t) {}
}
}
@@ -226,13 +260,18 @@
private void dropTable() {
String sql = "DROP TABLE SQLTEST_EMPLOYEE";
PreparedStatement ps = null;
- try {
+ try
+ {
SQLStatement stmt = new SQLStatement(sql);
ps = stmt.getPreparedStatement(con);
ps.execute();
- } catch (Exception e) {
+ }
+ catch (Exception e)
+ {
//ignore
- } finally {
+ }
+ finally
+ {
try { ps.close(); } catch (Throwable t) {}
}
}
@@ -243,7 +282,8 @@
public String lName;
public String title;
- public Employee(String fName, String lName, String title) {
+ public Employee(String fName, String lName, String title)
+ {
this.fName = fName;
this.lName = lName;
this.title = title;
