J F W

HOW TO : CORE

execute a complex sql command

Let's now see a more complex sql command. In this example, we will see how to execute two update commands that are part of the same transaction (the two commands will commit if both are success or rollback if one them is failure). In this example, will be used a 3 layer architecture.

This is the code of the action:

public ActionForward myExecute( ... ) { ... DBComplex.operation(outputdata); ... }

This is the source of class DBComplex:

public final class DBComplex { private static String className = new DBComplex().getClass().getName(); public static void operation(HashMap outputdata) throws Exception { Transaction transaction = null; boolean commit = false; try { transaction = new Transaction(); Update1 update1 = new Update1(transaction); HashMap data1 = new HashMap(); data1.put(Update1.PARAM_1, "1"); data1.put(Update1.PARAM_2, "34"); update1.setData(data1); update1.execute(outputdata); Update2 update2 = new Update2(transaction); HashMap data2 = new HashMap(); data2.put(Update2.PARAM_1, "3"); update2.setData(data2); update2.execute(outputdata); commit = true; } catch(DatabaseException de) { throw de; } catch(Throwable t) { JFWLogger.error(className, "operation", "", "", t.toString(), t); throw new DatabaseException(t.toString()); } finally { if(transaction != null) transaction.close(commit); } } }

The Transaction class is used when we want to execute more than one sql commands using the same database connection. It is possible to create a Transaction object for a particular database resource using the right constructor. In this example, the default database resource will be used.

Now the first class, Update1, will be called:

Update1 update1 = new Update1(transaction); HashMap data1 = new HashMap(); data1.put(Update1.PARAM_1, "1"); data1.put(Update1.PARAM_2, "34"); update1.setData(data1); update1.execute(outputdata);

In this case, since the sql command has parameters, we set them using the method setData. The setData accepts any kind of object; if the data has been submitted from an html page probably will pass an ActionForm. Of course, a simpler object like a String can be used.

The code of class Update1 will be:

public final class Update1 implements QueryGatewayInterface { protected String className = getClass().getName(); protected static String query; private Transaction transaction; public static final String PARAM_1 = "PARAM_1"; public static final String PARAM_2 = "PARAM_2"; private HashMap data; static { StringBuffer querySB = new StringBuffer(); querySB.append("update table1"); querySB.append(" set column1=?"); querySB.append(" where column2=? and column1<>?"); query = querySB.toString(); } public Update1(Transaction transaction) { this.transaction = transaction; } public void setData(Object data) { this.data = (HashMap)data; } public void execute(HashMap outputdata) throws DatabaseException { QueryGateway queryGateway = null; try { ArrayList parameters = new ArrayList(); parameters.add(data.get(PARAM_1)); parameters.add(data.get(PARAM_2)); parameters.add(data.get(PARAM_1)); queryGateway = new QueryGateway(transaction); int changedRows = queryGateway.execute(query, parameters); } catch(DatabaseException de) { throw de; } catch(Throwable t) { JFWLogger.error(className, "execute", "", "", t.toString(), t); throw new DatabaseException(t.toString()); } finally { if(queryGateway != null) { queryGateway.close(); } } } }

To set the parameters for the sql command we use an ArrayList like this:

ArrayList parameters = new ArrayList(); parameters.add(data.get(PARAM_1)); parameters.add(data.get(PARAM_2)); parameters.add(data.get(PARAM_1));

The parameters must be added into the ArrayList in the same order used in the sql command. If a parameter is used more times must be added more times in the order is used.

Then we create an instance of QueryGateway and use the method execute to execute the sql command:

queryGateway = new QueryGateway(transaction); int changedRows = queryGateway.execute(query, parameters);

In this case is used the constructor of QueryGateway that takes an object of type Transaction. The close method in finally will close the prepared statement used for execute the sql command but will not release the connection since this is a transaction.

finally { if(queryGateway != null) { queryGateway.close(); } }

If no exception has been thrown then the second class, Update2, will be called.

Update2 update2 = new Update2(transaction); HashMap data2 = new HashMap(); data2.put(Update2.PARAM_1, "3"); update2.setData(data2); update2.execute(outputdata);

The source of this class will be similar to the one of Update1:

public final class Update2 implements QueryGatewayInterface { protected String className = getClass().getName(); protected static String query; private Transaction transaction; public static final String PARAM_1 = "PARAM_1"; private HashMap data; static { StringBuffer querySB = new StringBuffer(); querySB.append("update table2"); querySB.append(" set column1=?"); query = querySB.toString(); } public Update2(Transaction transaction) { this.transaction = transaction; } public void setData(Object data) { this.data = (HashMap)data; } public void execute(HashMap outputdata) throws DatabaseException { QueryGateway queryGateway = null; try { ArrayList parameters = new ArrayList(); parameters.add(data.get(PARAM_1)); queryGateway = new QueryGateway(transaction); int changedRows = queryGateway.execute(query, parameters); } catch(DatabaseException de) { throw de; } catch(Throwable t) { JFWLogger.error(className, "execute", "", "", t.toString(), t); throw new DatabaseException(t.toString()); } finally { if(queryGateway != null) { queryGateway.close(); } } } }

If no exception is thrown, then the value of attribute commit will be set to true and finally the transaction will be closed:

finally { if(transaction != null) transaction.close(commit); }

The close method of the Transaction before release the connection will commit or rollback all the operations occurred, based on the value of the boolean parameter.

Look on javadoc of class QueryGateway to see how to pass parameters to use in clause in and to use sql functions in your query.