4, JDBC
(1) JDBC summary
l makes JDBC more easily, less error
l Handling the creation and release of resources by the framework
l Manage exception handling by the framework
(2) JDBCTemplate
l Execute SQL queries, update statements and stored procedure calls
l Cyclic traverses the parameter value returned by the returned
l example:
DataSource DS = DatasourceIls.getdataSourceFromjndi ("MyDS");
JDBCTemplate JDBC = New JDBCTemplate (DS);
JDBC.execute ("Drop Table Temp");
JDBC.UPDATE ("Update Employee Set Firstnme =? Where lastname =?",
NEW STRING [] {"Joe", "Lee"});
l Use the convenient method to query
Int maxage = jdbc.queryforint ("SELECT MAX (AGE) from Employee);
String name = (string) jdbc.queryforObject (
"SELECT Firstnme from Employee WHERE Lastname = 'Lee'", String.class);
List Employees = JDBC.QueryforList (
"SELECT EMPNO, FIRSTNME, LastName from Employee";
Returns an ArrayList (an entry corresponding to a row) (an entry corresponds to a column, using the column name to do key)
l Use the callback method to query
Final List Employees = New LinkedList ();
JDBC.Query ("SELECT Empno, Firstnme, Lastname from Employee,
New rowcallbackhandler () {
Public Void ProcessRow (ResultSet RS) throws sqlexception {
Employee E = New Employee ();
E.sempno (rs.getstring (1));
E.SetFirstName (Rs.getstring (2));
E.SetLastName (Rs.getstring (3));
Employees.Add (e);
}
});
l Storage procedure
Jdbc.call (new callablestatementcreator () {
Public CallableStatement CreateCallablestatement (Connection Conn)
Throws sqlexception {
Return Conn.PrepareCall ("My Query");
}
}, params);
l Batch update
BatchPreparedStatementSetter setter =
New batchpreparedStatementSetter () {
Public void setvalues (preparedStatement PS, INT i)
Throws sqlexception {
...
}
Public int getbatchsize () {
Return ...
}
}
JDBC.BatchUpdate ("Update ...", setter;
(3) SQLQuery / SQLUPDATE object
l Package query and update to the Java class
Class EmployeeQuery Extends mappingsqlquery {
Public EmployeeQuery (DataSource DS) {
Super (DS, "SELECT EMPNO, FIRSTNME, Lastname from Employee WHERE Empno =?");
DeclareParameter (New Sqlparameter (Types.char));
COMPILE ();
}
Protected Object MapRow (ResultSet RS, INT Rownum) throws sqlexception {
Employee E = New Employee ();
E.sempno (RS.GetString ("Empno"));
E.SetFirstName (RS.GetString ("firstnme");
E.SetLastName (Rs.getstring ("LastName"));
Return E;
}
Public Employee FindemPloyee (String ID) {
Return (EMPLOYEE) FindObject (ID);
}
}
Map result set to a Java object
(4) SQLFunction
l Package returns a single line of query
SQLFunction sf = new SQLFunction (DataSource,
"SELECT Count (*) from myTable");
sf.compile ();
INT rUN ();
(5) abnormal treatment
l Convert SQLEXECPTION to the DataAccessException level
Ø Universal, more information, unrelated to DB / JDBC (SQL error code is mapped to abnormal)
l Use RuntimeException (no check)
l We can cover unchecked data access exceptions
Try {
// Do Work
} catch (OptimisticLockingFailureException ex) {
// I'm interested in this
}
(6) Database connection
l Datasource (), getConnection (), getDatsourceFromjndi (), CloseConnectionIfnecessary ()
l DriverManagerDataSource
Ø Return to a new connection every time
Ø Can be used outside the container or test
l SingleConnectionDataSource
Ø Return the same connection every time
Ø Can be used outside the container or test
