making web application programming easier is possible


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:

This is the source of class DBComplex:

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:

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:

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

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:

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.

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

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

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

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.