J F W

HOW TO : CORE

execute a simple sql command

Execute a sql command, like a select, update, delete, ... , becomes more easy using the class QueryGateway. You can use this class directly from an action or you can use a 2 or 3 layer architecture.
In this example we will use a 2 layer architecture. The first layer is the action and the second layer the class used for defining the query.

We suppose that we want to execute a select command like this:

select 1 as ONE from dual

The above query will return just one row with one column named "ONE". The value of the column "ONE" is the constant 1.

Here is the code of the class which defines the query:

public class SearchDB implements QueryGatewayInterface { protected String className = getClass().getName(); protected static String query; static { StringBuffer querySB = new StringBuffer(); querySB.append("select 1 as ONE from dual"); query = querySB.toString(); } public void setData(Object data) { } public void execute(HashMap outputdata) throws DatabaseException { QueryGateway queryGateway = null; try { queryGateway = new QueryGateway(); queryGateway.executeQuery(query, null); queryGateway.getAllData(outputdata); } 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(); } } } }

The class SearchDB implements the interface QueryGatewayInterface and that means that it has to implement methods setData and execute.
The setData method is used to pass the data needed by the query. In our example, no data is needed by the query so the method is blank.
The execute method is the one called for executing the query. The parameter outputdata will be used as container of the data returned by the query.
There is also a static method: in this method is defined the query to use. We can define more queries in that method, save them in different attributes and use them in the execute method based on some criteria, but if it is not necessary, it is much better to have one class for one query.

Let take a closer look at the code in the execute method.

QueryGateway queryGateway = null; ... queryGateway = new QueryGateway(); queryGateway.executeQuery(query, null); queryGateway.getAllData(outputdata);

For first we create a QueryGateway instance. As you can see in javadoc of QueryGateway this class has more constructors that differ on the setting of the database resource to use for executing the query. Since in our example, the default database resource will be used and this is a stand alone execution (not a transcation) the constructor with no parameters is used.
Then the method executeQuery is used for executing the query. The parameters of the method are the query, the one defined in the static method, and the parameters to use in the query, null in this case.
The method getAllData is used for reading the data returned by the query (the result set). The data is saved in the HashMap outputdata under the key ALL_ROWS_KEY:

If, for example, a query return 3 rows, and every row has three columns with names "ONE", "TWO" and "THREE", then using method getAllData will have this:

It is possible to read the data returned by the query directly. In this case we have to substitute the line "queryGateway.getAllData(outputdata);" with something like this:

//in every cycle we read the data of a single row while(queryGateway.next()) { String one = queryGateway.getString("ONE")); String two = queryGateway.getString("THREE")); String three = queryGateway.getString("TWO")); }

When we have finished reading the data, we have to release all the objects used to execute the query:

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

In the close method of QueryGateway is closed the result set and prepared statement objects and released the database connection.

Here is the code we have to add in the method myExecute of an action:

public ActionForward myExecute( ... ) { ... SearchDB searchDB = new SearchDB(); searchDB.execute(outputdata); ... }

We can view the data, saved in outputdata by the method getAllData, in a jsp using the logic:iterate tag or using the mtl:generic tag of JFW:

<mtl:generic> <?repeat ALL_ROWS_KEY?> <?if ROW_TYPE=ROW_ODD?> <then> <p style="background-color: green"> </then> <else> <p style="background-color: red"> </else> </if> row <?ALL_ROWS_KEY_count?><br/> column ONE: <?ONE?><br/> column TWO: <?TWO?><br/> column THREE: <?THREE?> </p> </repeat> </mtl:generic>