Chapter 7: Using JDBC with Spring - Training Etc

materialisticrampantInternet και Εφαρμογές Web

10 Νοε 2013 (πριν από 4 χρόνια και 1 μήνα)

99 εμφανίσεις

I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-1
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

Copy
Evaluation


I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-2
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

Copy
Evaluation


I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-3
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

Copy
Evaluation


I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-4
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

Copy
Evaluation


I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-5
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

Copy
Evaluation


I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-6
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

Copy
Evaluation


I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-7
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

Copy
Evaluation


I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-8
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

I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-9
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

I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-10
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.
9. </bean>
Evaluation
Copy

Evaluation


Copy

I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-11
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

I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-12
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

I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-13
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

I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-14
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

I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-15
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

I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-16
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

I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-17
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

I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-18
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. }

Evaluation
Copy

Evaluation


Copy

I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-19
Exercises
1. Modify the TableUtility program, adding two more
menu items to allow you to create and drop a table called
"customer." The schema for the table is as follows.
ID VARCHAR (4) (primary key)
NAME VARCHAR (24)
CITY VARCHAR (24)
STATE CHAR (2)
ZIPCODE VARCHAR (10)
BALANCE DECIMAL (10,2)
CREDLIMIT DECIMAL (10,2)

 Solution: solutions.jdbc.TableUtility

2. Load the customer table with some sample data. Read
the data from the following text file.
c:/spring/setup/customers.txt

 Solution: solutions.jdbc.LoadCustomerTable
solutions.jdbc.CustomerTableLoader
Evaluation
Copy

Evaluation


Copy

I
NTRODUCTION TO
S
PRING
F
RAMEWORK
C
HAPTER
7:

U
SING
JDBC
WITH
S
PRING

©2013 /training/etc Inc. REPRODUCTION OF THESE MATERIALS IS PROHIBITED.
7-20
Exercises
3. Write a program to perform the following queries on the
customer table and display the results.
 Find the name of the customer with id = '0114'
• Hint: Use queryForObject()

 Find the name, city, and state of the customer with id = '0118'
• Hint: Use queryForMap()

 Find the id's and names of all customers in California (CA)
• Hint: Use queryForList()


 Solution: solutions.jdbc.CustomerQueries
solutions.jdbc.CustomerQueriesTest



4. Write a Customer class with data fields corresponding to
the columns in the customer table. Write (or let Eclipse
generate) getter and setter methods for the data fields,
and provide a toString() method. Then, execute the
queries from the previous exercise, this time returning
Customer objects.
 Solution: solutions.jdbc.Customer
solutions.jdbc.CustomerMappedQueries
solutions.jdbc.CustomerMappedQueriesTest
Evaluation
Copy

Evaluation


Copy