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:
This is the source of class DBComplex
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
To set the parameters for the sql command we use an ArrayList
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
the sql command:
In this case is used the constructor of QueryGateway
that takes an object of type
. The close
method in finally
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
and finally the transaction will be closed:
method of the Transaction
before release the connection
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
to use sql functions in your query.