Chapter 7:
Using JDBC with Spring
1) A Simpler Approach ... 7-2 2) The JdbcTemplate Class ... 7-3 3) Exception Translation ... 7-7 4) Updating with the JdbcTemplate... 7-9 5) Queries Using the JdbcTemplate ... 7-13 6) Mapping Results to Java Objects ... 7-16
Evaluation
Copy
Evaluation
Copy
A Simpler Approach
• The Spring JDBC framework is built on top of the Java SE
JDBC API. The focus of Spring's approach is to make it
easier to use the strengths of JDBC while abstracting
away the cumbersome parts.
• A typical JDBC operation requires the programmer to:
define connection parameters;
open the connection;
specify the SQL statement;
prepare and execute the statement;
write loop to iterate through results;
do some processing for each iteration;
process any exceptions;
handle transactions; and
close the connection.
• When using Spring, the developer only codes the items in
bold. The Spring Framework takes care of all the lower-
level details.
• The following packages comprise the Spring JDBC
abstraction framework.
org.springframework.jdbc.core
org.springframework.jdbc.datasource
org.springframework.jdbc.object
org.springframework.jdbc.support
Evaluation
Copy
Evaluation
Copy
The JdbcTemplate Class
• The JdbcTemplate class is the central class in the
org.springframework.jdbc.core package. This
class provides the following benefits.
Creates and releases resources
Avoids common errors such as forgetting to close the
connection
Executes queries, updates, or stored procedures
Catches SQLExceptions and translates them to unchecked
exceptions
• The simplest way to use the JdbcTemplate is to provide
a DataSource and then use the execute() method to
run an SQL command. Our first example is a program
that allows the user to create or drop a table.
TableUtilityTest.java
1. package examples.jbdc;
2.
3. // import's not shown 4.
5. public class TableUtilityTest { 6.
7. public static void main(String[] args) { 8.
9. BeanFactory factory = new XmlBeanFactory 10. (new ClassPathResource ("jdbc.xml"));
11.
12. TableUtility util = (TableUtility) 13. factory.getBean("tableUtility");
14.
15. util.run();
16. } 17. }
Evaluation
Copy
Evaluation
Copy
The JdbcTemplate Class
TableUtility.java
1. package examples.jdbc;
2.
3. import java.io.*;
4. import javax.sql.DataSource;
5.
6. import org.springframework.jdbc.core.JdbcTemplate;
7.
8. public class TableUtility { 9.
10. private JdbcTemplate template;
11. private BufferedReader br;
12.
13. public TableUtility(DataSource ds) { 14.
15. template = new JdbcTemplate(ds);
16.
17. br = new BufferedReader
18. (new InputStreamReader(System.in));
19. } 20.
21. public void run() { 22.
23. int choice;
24.
25. while (true) { 26.
27. choice = 0;
28. while (choice < 1 || choice > 3) { 29. choice = getMenuChoice();
30. } 31.
32. if (choice == 3) { 33. break;
34. } 35.
36. processMenuChoice(choice);
37. } 38. }
Evaluation
Copy
Evaluation
Copy
The JdbcTemplate Class
TableUtility.java (continued)
39. private void processMenuChoice(int choice) { 40.
41. String sqlCmd, sqlCmd2;
42.
43. sqlCmd = null;
44. sqlCmd2 = null;
45.
46. switch (choice) { 47.
48. case 1:
49. sqlCmd = "create table product " +
50. "(id varchar(8), descrip varchar(40), " + 51. "price decimal(10,2), unit varchar(20), " + 52. "qty int)";
53.
54. sqlCmd2 = "alter table product add " + 55. "constraint productpk primary key (id)";
56.
57. break;
58.
59. case 2:
60. sqlCmd = "drop table product";
61. break;
62. } 63.
64. template.execute(sqlCmd);
65.
66. if (sqlCmd2 != null) {
67. template.execute(sqlCmd2);
68. } 69. } 70.
71. private int getMenuChoice() { 72.
73. System.out.println("(1) Create Product table");
74. System.out.println("(2) Drop Product table");
75. System.out.println("(3) Quit");
76.
77. // rest of method not shown 78. }
79. }
Evaluation
Copy
Evaluation
Copy
The JdbcTemplate Class
jdbc.xml
1.
2. <bean id="tableUtility"
3. class="examples.jdbc.TableUtility">
4.
5. <constructor-arg ref="myDataSource"/>
6. </bean>
7.
8. <bean id="myDataSource" class=
9. "org.springframework.jdbc.datasource.DriverManagerDataSource">
10.
11. <property name="driverClassName"
12. value="com.mysql.jdbc.Driver"/>
13.
14. <property name="url"
15. value="jdbc:mysql://localhost/test"/>
16.
17. <property name="username" value="root"/>
18. </bean>
19.
• Note the use of the DriverManagerDataSource class.
This Spring class provides a simple implementation of the
standard JDBC DataSource interface, using bean
properties to configure the JDBC driver and URL.
Evaluation
Copy
Evaluation
Copy
Exception Translation
• Note that in the previous example, we did not have to
catch any SQLExceptions, which makes the code easier
to write and to read.
• Spring catches any SQLException and translates it to a
Spring-specific exception class that extends
org.springframework.dao.DataAccessException.
This is an unchecked exception so you don't have to catch
it if you don't want to.
Since many database exceptions are unrecoverable, it is often
reasonable not to catch them. However, you can always do so
if you wish. The Spring exceptions wrap the original
SQLException, so you can always get to the root cause of the
problem.
• Some of the classes in the Spring database exception
hierarchy are listed below.
BadSqlGrammarException - SQL syntax error, invalid table
name, etc.
DataIntegrityViolationException - duplicate key or
missing data value
DataRetrievalFailureExeption - error retrieving data
CannotAcquireLockException - row is locked and
database is configured not to wait for blocking locks
DataAccessResourceFailureException - problem
connecting to the database
Evaluation
Copy
Evaluation
Copy
Exception Translation
• Which exception is thrown for a specific SQL error code is
controlled by a configuration file called sql-error-
codes.xml. This file can be found in:
spring-framework-3.xxx\dist\org.springframework.jdbc-3.xxx.jar
• Here are the entries for the MySQL database.
sql-error-codes.xml (excerpt)
1. <bean id="MySQL" class=
2. "org.springframework.jdbc.support.SQLErrorCodes">
3.
4. <property name="badSqlGrammarCodes">
5. <value>1054,1064,1146</value>
6. </property>
7.
8. <property name="duplicateKeyCodes">
9. <value>1062</value>
10. </property>
11.
12. <property name="dataIntegrityViolationCodes">
13. <value>630,839,840,893,1169,1215,
14. 1216,1217,1451,1452,1557</value>
15. </property>
16.
17. <property name="dataAccessResourceFailureCodes">
18. <value>1</value>
19. </property>
20.
21. <property name="cannotAcquireLockCodes">
22. <value>1205</value>
23. </property>
24.
25. <property name="deadlockLoserCodes">
26. <value>1213</value>
27. </property>
28. </bean>
Evaluation
Copy
Evaluation
Copy
Updating with the JdbcTemplate
• The JdbcTemplate class includes several update()
methods for insert, delete, and update operations. The
update() methods return a count of the number of rows
affected (the update count).
• The simplest form of the update() method takes an SQL
statement as a parameter.
int update (String sql)
• To use a prepared statement, the values for the
placeholders in the prepared statement are passed to the
update() method in an Object array.
int update (String sql, Object[] args)
• A third form of the update() method takes an array of
integers containing the SQL types of the placeholder
values. This would be used to make sure that the
prepared statement is populated with the correct data
types rather than relying on the default mappings.
int update (String sql, Object[] args, int[] argTypes)
See JavaDocs for java.sql.Types
Evaluation
Copy
Evaluation
Copy
Updating with the JdbcTemplate
• This example reads data from a text file and inserts rows
into the product table. The name of the text file is
configured in the Spring configuration file.
LoadProductTable.java
1. package examples.jdbc;
2.
3. import java.io.IOException;
4.
5. import org.springframework.beans.factory.BeanFactory;
6. import org.springframework.beans.factory.xml.XmlBeanFactory;
7. import org.springframework.core.io.ClassPathResource;
8.
9. public class LoadProductTable { 10.
11. public static void main(String[] args) 12. throws IOException {
13.
14. BeanFactory factory = new XmlBeanFactory 15. (new ClassPathResource ("jdbc.xml"));
16.
17. ProductTableLoader loader = (ProductTableLoader) 18. factory.getBean("productLoader");
19.
20. loader.run();
21. } 22. }
jdbc.xml
1.
2. <bean id="productLoader"
3. class="examples.jdbc.ProductTableLoader">
4.
5. <constructor-arg ref="myDataSource"/>
6. <constructor-arg
7. value="c:/spring/setup/products.txt"/>
8.
Evaluation
Copy
Evaluation
Copy
Updating with the JdbcTemplate
ProductTableLoader.java
1. package examples.jdbc;
2.
3. // import's not shown 4.
5. public class ProductTableLoader { 6.
7. private JdbcTemplate template;
8. private String dataFile;
9.
10. public ProductTableLoader(DataSource ds, 11. String fileName) {
12.
13. template = new JdbcTemplate(ds);
14. dataFile = fileName;
15. } 16.
17. public void run() throws IOException { 18.
19. FileReader fr = new FileReader (dataFile);
20. BufferedReader br = new BufferedReader (fr);
21. String line;
22.
23. Object args[] = new Object[5];
24.
25. int updateCount;
26.
27. while ((line = br.readLine()) != null) { 28. StringTokenizer st =
29. new StringTokenizer (line, ",");
30.
31. if (st.countTokens() != 6) {
32. System.out.println ("Invalid record");
33. continue;
34. } 35.
36. args[0] = st.nextToken(); // productId 37. args[1] = st.nextToken(); // description 38. args[3] = st.nextToken(); // unit
39.
Evaluation
Copy
Evaluation
Copy
Updating with the JdbcTemplate
ProductTableLoader.java (continued)
40. args[2] =
41. new Double (st.nextToken()); // price 42. args[4] =
43. new Integer (st.nextToken()); // qtyOnHand 44.
45. // Insert new row into product table 46.
47. updateCount = template.update
48. ("INSERT INTO product VALUES (?,?,?,?,?)", 49. args);
50.
51. if (updateCount == 1) { 52. System.out.println
53. ("Created: " + args[0]);
54. } else {
55. System.out.println
56. ("Error creating: " + args[0]);
57. } 58. }
59. br.close();
60. } 61. }
Evaluation
Copy
Evaluation
Copy
Queries Using the JdbcTemplate
• There are five primary query methods in the
JdbcTemplate, each with a different return type. Each
method comes in two flavors: one that takes a static SQL
statement (shown below), and one that takes an SQL
statement with placeholders, along with an Object array
containing the values for the placeholders.
int queryForInt (String sql)
long queryForLong (String sql)
Object queryForObject (String sql, Class type)
Map queryForMap (String sql)
List queryForList (String sql)
• The queryForObject() method assumes a single
column from a single row will be returned by the query.
If the query does not return a single row/single column, an
IncorrectResultSizeDataException is thrown.
• The queryForMap() method assumes a single row will
be returned by the query. Each entry in the Map has a
column name as the key and the column data as the
value.
If the query does not return exactly one row, an
IncorrectResultSizeDataException is thrown.
• The queryForList() method is used for queries that
return multiple rows. The List returned contains a Map
for each row returned by the query.
Evaluation
Copy
Evaluation
Copy
Queries using the JdbcTemplate
SimpleQueries.java
1. package examples.jdbc;
2.
3. import java.util.List;
4. import java.util.Map;
5.
6. import javax.sql.DataSource;
7.
8. import org.springframework.jdbc.core.JdbcTemplate;
9.
10. public class SimpleQueries { 11.
12. private JdbcTemplate template;
13.
14. public SimpleQueries(DataSource ds) { 15. template = new JdbcTemplate(ds);
16. } 17.
18. public void run() { 19.
20. int numRecords = template.queryForInt 21. ("select count(*) from product");
22.
23. System.out.println(numRecords + 24. " records in product table");
25.
26. Object obj = template.queryForObject
27. ("select descrip from product where " + 28. "id='010-0100'", String.class);
29.
30. System.out.println("Description for Product " + 31. "010-0100 = " + obj);
32.
33. obj = template.queryForObject
34. ("select descrip from product where id=?", 35. new Object[] {"010-0200"}, String.class);
36.
37. System.out.println("Description for Product " + 38. "010-0200 = " + obj);
39.
Evaluation
Copy
Evaluation
Copy
Queries using the JdbcTemplate
SimpleQueries.java (continued)
40. List list = template.queryForList
41. ("select * from product where price > 10.0");
42.
43. System.out.println("Products with price " + 44. "greater than 10.00:");
45.
46. for (Object row: list) { 47. Map m = (Map) row;
48. for (Object colName : m.keySet()) { 49. System.out.println(colName + ": " + 50. m.get(colName));
51. }
52. System.out.println("---");
53. } 54. } 55. }
Evaluation
Copy
Evaluation
Copy
Mapping Results to Java Objects
• Another set of query methods allows you to map rows to
Java objects using a RowMapper.
Object queryForObject (String sql, RowMapper
mapper)
List query (String sql, RowMapper mapper)
• The queryForObject() method assumes a single row
result set and returns a single object. The query()
method returns a List of mapped objects.
• The RowMapper interface contains the following method.
Object mapRow (ResultSet rs, int rowNum)
• The mapRow() method maps a single row in the result set
to a Java object.
• The next example demonstrates a query that returns
Product objects. The Product class is shown on the
next page.
Evaluation
Copy
Evaluation
Copy
Mapping Results to Java Objects
Product.java
1. package examples.jdbc;
2.
3. public class Product { 4.
5. private String productId;
6. private String description;
7. private String unit;
8. private double price;
9. private int qtyOnHand;
10.
11. // getters and setters not shown 12.
13. public String toString() {
14. StringBuffer temp = new StringBuffer(128);
15. temp.append("Prod Id: ");
16. temp.append(productId);
17. temp.append(" Desc: ");
18. temp.append(description);
19. temp.append(" Unit: ");
20. temp.append(unit);
21. temp.append(" Price: ");
22. temp.append(price);
23. temp.append(" Qty: ");
24. temp.append(qtyOnHand);
25. return temp.toString();
26. } 27. }
Evaluation
Copy
Evaluation
Copy
Mapping Results to Java Objects
MappedQuery.java
1. package examples.jdbc;
2.
3. // import's not shown 4.
5. public class MappedQuery { 6.
7. private JdbcTemplate template;
8.
9. public MappedQuery(DataSource ds) { 10. template = new JdbcTemplate(ds);
11. } 12.
13. public void run() { 14.
15. List list = template.query("select * from " + 16. "product where descrip like 'PENCILS%'", 17. new ProductRowMapper());
18.
19. for (Object obj: list) {
20. System.out.println(obj);
21. } 22. } 23. } 24.
25. class ProductRowMapper implements RowMapper { 26.
27. public Object mapRow(ResultSet rs, int rowNum) 28. throws SQLException {
29.
30. Product prod = new Product();
31.
32. prod.setProductId(rs.getString("id"));
33. prod.setDescription(rs.getString("descrip"));
34. prod.setPrice(rs.getDouble("price"));
35. prod.setUnit(rs.getString("unit"));
36. prod.setQtyOnHand(rs.getInt("qty"));
37.
38. return prod;
39. } 40. }