A generic JDBC login Action Framework (5)-using the jdbctemplate class

Using the jdbctemplate class

Use the jdbctemplate class

Now that we 've looked at the implementation, let's seeCom. interface21.jdbc. CorePackage in action.

We have already discussed the implementation of this feature so that we can understand it in the application.Com. interface21.jdbc. Core Package.

Deming queries

Execute Query

UsingJdbctemplateClass, we can accomplish the JDBC query for seat IDs, shown underMotivationAbove, as follows, using anonymous inner classes to implementRowcallbackhandlerAndPreparedstatementcreatorInterfaces. This implementationRowcallbackhandlerSaves data in a list of integers defined in the enclosing method:

With the jdbctemplate class, we can complete the JDBC query of the seat ID. Below, we use the anonymous internal class to implementRowcallbackhandlerAndPreparedstatementcreatorInterface.

Rowcallbackhandler stores data in an integer list.

 Public List getavailableseatidswithjdbctemplate (
Datasource ds, Final   Int Performanceid, Final   Int Seattype)
 Throws Dataaccessexception {
Jdbctemplate T = New Jdbctemplate (DS );
 Final List 1 = New Vertex list ();
Preparedstatementcreator PSC = New Preparedstatementcreator (){
Public Preparedstatement createpreparedstatement (connection conn)
 Throws Sqlexception {
Preparedstatement PS = conn. preparestatement (
 "Select seat_id as ID from available_seats where" +
 "Performance_id =? And price_band_id =? " );
PS. setint (1, performanceid );
PS. setint (2, seattype );
 Return PS;
}
};
Rowcallbackhandler RCH =New Rowcallbackhandler (){
 Public   Void Processrow (resultset RS) Throws Sqlexception {
 Int Seatid = Rs. getint (1 );
1. Add ( New INTEGER (seatid ));
}
};
T. Query (PSC, RCH );
 Return 1;
}

This halves the amount of code required and addresses most of the problems we identified in using the jdbc api directly. we can work with preparedstatement and resultset objects with a minimum of irrelevant code. most importantly, using the jdbctemplate class eliminates the major causes of errors. there is no risk that the connection won't be closed: The jdbctemplate class ensures this. application code doesn' t need to catch checked, uninformative sqlexceptions : Our generic data access exception hierarchy offers a richer classification of exceptions,, since data access exceptions are unchecked, application code can typically leave exceptions to be dealt with by the application server.

This part is requiredCodeAnd solved most of the problems we encountered with direct JDBC APIs. We canPreparedstatementAndResultsetWork together with a minimum irrelevant code. More importantly, useJdbctemplateClass to eliminate the main cause of the error. There is no risk that the connection will not be closed:JdbctemplateClass. The application code does not need to capture and check exceptions, and does not provideSqlexceptionsInformation: Our General Data Access exception level provides a richer exception level. However, because of data access exceptions that are not checked, application code can usually leave exceptions to be processed by the Application Service.

Ming updates

Execute update

If we use the jdbc api directly, updates call for a similar amount of code to queries. Again error handling is dominant and the SQL we want to execute is obscured. AgainJdbctemplateClass can deliver real benefits.Jdbctemplate Update ()Method is capable of running updates usingPreparedstatementcreatorCallback interface we 've already seen. The following example (not part of our sample application !) Wocould delete all seat reservations and bookings for a special seat type in a given performance. The example assumes we already haveJdbctemplateObject in scope.JdbctemplateObjects are threadsafe, We will normally keep one as an instance variable in Dao implementations usingjdbc:

If we use the jdbc api directly, the update code is similar to the query code. It is clear that the SQL statement we want to execute is overwritten. Using the jdbctemplate class again can bring real benefits.

The Update () method of jdbctemplate can be usedPreparedstatementcreatorThe callback interface runs updates.

Class Performancecleanerpsc Implements Preparedstatementcreator {
 Private   Int PID;
 Private   Int PB;
 Public Performancecleanerpsc ( Int PID, Int PB ){
 This . PID = PID;
 This . Pb = Pb;
}
 Public Preparedstatement createpreparedstatement (connection conn)
 Throws Sqlexception {
Preparedstatement PS = conn. preparestatement ( "Update seat_status" +
 "Set booking_id = NULL where performance_id =? And" +
 "Price_band_id =? " );
PS. setint (1, pid );
PS. setint (2, Pb );
 Return PS;
}
};
Preparedstatementcreator PSC = New Performancecleanerpsc (1, 1 );
 Int Rowsaffected = jdbctemplate. Update (PSC );

Updates using static SQL are even easier. The following example wocould mark all seats in our ticketing application as available, without any need to implement callback interfaces:

Template. Update ("Update seat_status set booking_id = NULL");