; 06-spring-jdbc2
Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out
Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

06-spring-jdbc2

VIEWS: 6 PAGES: 47

Java,J2EE,Struts,Hibernate,JSF,Goolge web development toolkit(GWT),Spring,Dojo,Html,Xhtml

More Info
  • pg 1
									© 2008 coreservlets.com

Spring JDBC Part 2
Originals of Slides and Source Code for Examples: http://courses.coreservlets.com/Course-Materials/spring.html
Customized Java EE Training: http://courses.coreservlets.com/
Servlets, JSP, Struts, JSF/MyFaces/Facelets, Ajax, GWT, Spring, Hibernate/JPA, Java 5 & 6. Developed and taught by well-known author and developer. At public venues or onsite at your location.

© 2008 coreservlets.com

For live Spring & Hibernate training, see courses at http://courses.coreservlets.com/. t htt // l t /
Taught by the experts that brought you this tutorial. Available at public venues, or customized versions venues can be held on-site at your organization.
•C Courses d developed and t l d d taught b M t H ll ht by Marty Hall • Courses developed and taught by EE Training: http://courses.coreservlets.com/ Customized Java coreservlets.com experts (edited by Marty)
– Spring, Hibernate/JPA, EJB3, Ruby/Rails – Java 5, Java 6, intermediate/beginning servlets/JSP, advanced servlets/JSP, Struts, JSF, Ajax, GWT, custom mix of topics

Servlets, JSP, Struts, JSF/MyFaces/Facelets, Ajax, GWT, Spring, Hibernate/JPA, Java 5 & 6. Contact hall@coreservlets.com for details Developed and taught by well-known author and developer. At public venues or onsite at your location.

Topics in This Section p
• • • • Introduction to Spring JDBC APIs Result transformations Parameter mapping Updating tables

4

Java EE training: http://courses.coreservlets.com

© 2008 coreservlets.com

Handling H dli JDBC Query Results
Customized Java EE Training: http://courses.coreservlets.com/
Servlets, JSP, Struts, JSF/MyFaces/Facelets, Ajax, GWT, Spring, Hibernate/JPA, Java 5 & 6. Developed and taught by well-known author and developer. At public venues or onsite at your location.

Spring JDBC ResultSet Support p g pp
• Transforms information from JDBC ResultSet objects into domain types
– Hard-coded mapping information
• D t b Database changes results i code changes h lt in d h

– No reflection overhead – No configuration overhead

• Spring API offers numerous mapping options
– Row to object mapping – Typed Row to object mapping yp j pp g – Result to object collection mapping
6

Java EE training: http://courses.coreservlets.com

Row to Object Mapping j pp g
• Implemented as a Spring callback interface
– RowMapper

• Method signature is simple and intuitive
– Single method requirement
• mapRow(resultSet ResultSet rowNumber RowNum)#Object mapRow(resultSet:ResultSet,rowNumber:RowNum)#Object

– Only handles rows – Stateless callback
• Supports stateless implementations such as anonymous class implementations

• Method signature throws SQLException
– Limited capability for domain exceptions

• Design is simple but limited
– Limited utility without JDBC template – No generics support – Only supported by JdbcTemplate
7

Java EE training: http://courses.coreservlets.com

Row to Object Mapping Process j pp g
• Setup DAO
– Defer connectivity responsibilities • Design class for DataSource dependency injection i j ti – Use Spring JDBC APIs • Initialize Spring JDBC template(s) with the injected DataSource

• Implement callback
– Create static RowMapper reference – Implement callback and mapRow method p

• Integrate callback into business method
8

– Integrate with a JDBC template call http://courses.coreservlets.com Java EE training:

Row to Object Mapping Process Continued
• Create applicationContext.xml • Register beans
– DAO and DataSource beans

• Inject dependencies
– Specify the DataSource bean as a DAO bean dependency

• Initialize the container • Access and use the DAO bean

9

Java EE training: http://courses.coreservlets.com

DAO Interface
package coreservlets; import java.util.List; public interface CustomerListQuery { public List<Customer> getCustomers(); }

10

Java EE training: http://courses.coreservlets.com

Setup DAO p
import org.springframework.jdbc.core.*; public class RowMapperCustomerListQuery implements CustomerListQuery { private JdbcTemplate jdbc; public RowMapperCustomerListQuery(DataSource dataSource) { jdbc = new JdbcTemplate(dataSource); } ... }

11

Java EE training: http://courses.coreservlets.com

Implement Callback p
import org.springframework.jdbc.core.*; public class RowMapperCustomerListQuery implements CustomerListQuery { ... private static final RowMapper customerRowMapper = new RowMapper (){ public Object mapRow(ResultSet rslt, int rowNum) rslt throws SQLException { return new Customer(rslt.getString("id"), rslt.getString("name")); } }; } }
12

Java EE training: http://courses.coreservlets.com

Integrate Callback g
import org.springframework.jdbc.core.*; public class RowMapperCustomerListQuery implements CustomerListQuery { ... public List<Customer> getCustomers() { return jdbc.query( "select id, name from customer", customerRowMapper); } ... }

13

Java EE training: http://courses.coreservlets.com

DAO Bean
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns http://www.springframework.org/schema/beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"> <bean id="customerListQuery" class="coreservlets.RowMapperCustomerListQuery"> <constructor-arg ref= dataSource /> ref="dataSource" </bean> </beans>

14

Java EE training: http://courses.coreservlets.com

DAO Execution
import org.springframework.beans.factory.*; import org.springframework.context.support.*; org.springframework.context.support. ; public class Main { public static void main(String[] args) throws Exception { BeanFactory beanFactory = new ClassPathXmlApplicationContext(new String[]{ "/applicationContext.xml", "/dataSourceContext xml"}); /dataSourceContext.xml }); CustomerListQuery query = (CustomerListQuery) beanFactory.getBean("customerListQuery"); List<Customer>customers = query.getCustomers(); for(Customer customer : customers) { System.out.println(customer); } } Standard output } Customer id=jjoe, name=Java Joe Customer id=jjohn, name=Java John
15

Java EE training: http://courses.coreservlets.com

Typed Row to Object Mapping yp j pp g
• Implemented as a Spring callback interface with generics
– ParameterizedRowMapper <T>

• Method signature is simple and intuitive
– Si l method requirement Single th d i t
• mapRow(resultSet : ResultSet, rowNumber : RowNum)#T

– Only handles rows – Stateless callback
• Supports stateless implementations such as anonymous class implementations

• Method signature throws SQLException
– Limited capability for domain exceptions

16

Java EE training: http://courses.coreservlets.com

Typed Row to Object Mapping Process
• Setup DAO
– Defer connectivity responsibilities • Design class for DataSource dependency injection i j ti – Use Spring JDBC APIs • Initialize Spring JDBC template(s) with the injected DataSource

• Implement callback
– Create static ParameterizedRowMapper reference – Implement callback and mapRow method p

• Implement business method
17

– Integrate callback into JDBC templatehttp://courses.coreservlets.com Java EE training: call

Typed Row to Object Mapping Process Continued
• Create applicationContext.xml • Register beans
– DAO and DataSource beans

• Inject dependencies
– Specify the DataSource bean as a DAO bean dependency

• Initialize the container • Access and use the DAO bean

18

Java EE training: http://courses.coreservlets.com

DAO Interface
public interface CustomerListQuery { public List<Customer> getCustomers(); }

19

Java EE training: http://courses.coreservlets.com

Setup DAO p
import org.springframework.jdbc.core.simple.*; public class ParameterizedRowMapperCustomerListQuery implements CustomerListQuery { private SimpleJdbcTemplate simpleJdbc; public ParameterizedRowMapperCustomerListQuery( DataSource dataSource) { simpleJdbc = new SimpleJdbcTemplate(dataSource); } ... }

20

Java EE training: http://courses.coreservlets.com

Implement Callback p
import org.springframework.jdbc.core.simple.*; public class ParameterizedRowMapperCustomerListQuery implements CustomerListQuery { ... private ParameterizedRowMapper<Customer> customerRowMapper = new ParameterizedRowMapper<Customer>(){ public Customer mapRow(ResultSet rslt, int rowNum) rslt throws SQLException { return new Customer(rslt.getString("id"), rslt.getString("name")); } }; } }
21

Java EE training: http://courses.coreservlets.com

Integrate Callback g
import org.springframework.jdbc.core.simple.*; public class ParameterizedRowMapperCustomerListQuery implements CustomerListQuery { ... public List<Customer> getCustomers() { return this.simpleJdbc.<Customer>query( "select id, name from customer", select id customer customerRowMapper); } ... }

22

Java EE training: http://courses.coreservlets.com

DAO Bean
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns http://www.springframework.org/schema/beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"> <bean id="customerListQuery" class="coreservlets.ParameterizedRowMapperCustomerListQuery"> <constructor-arg ref= dataSource /> ref="dataSource" </bean> </beans>

23

Java EE training: http://courses.coreservlets.com

DAO Execution
import org.springframework.beans.factory.*; import org.springframework.context.support.*; org.springframework.context.support. ; public class Main { public static void main(String[] args) throws Exception { BeanFactory beanFactory = new ClassPathXmlApplicationContext(new String[]{ "/applicationContext.xml", "/dataSourceContext xml"}); /dataSourceContext.xml }); CustomerListQuery query = (CustomerListQuery) beanFactory.getBean("customerListQuery"); List<Customer>customers = query.getCustomers(); for(Customer customer : customers) { System.out.println(customer); } } Standard output } Customer id=jjoe, name=Java Joe Customer id=jjohn, name=Java John
24

Java EE training: http://courses.coreservlets.com

ResultSet to Object Collection Mapping
• Implemented as a Spring callback interface
– ResultSetExtractor

• Method signature is simple and intuitive
– Single method requirement
• e tractData(res ltSet : ResultSet, rowNumber : extractData(resultSet Res ltSet ro N mber RowNum)#T – Handles full ResultSet

– Stateless callback
• Supports stateless implementations such as anonymous class implementations

• Method signature throws SQLException g p
– Limited capability for domain exceptions

• Design is simple but limited
– Limited utility without JDBC template ted ut ty w t out J C te p ate – No generics support – Only supported by JdbcTemplate
25

Java EE training: http://courses.coreservlets.com

ResultSet to Object Collection Mapping Process
• Setup DAO
– Defer connectivity responsibilities • Design class for DataSource dependency injection i j ti – Use Spring JDBC APIs • Initialize Spring JDBC template(s) with the injected DataSource

• Implement callback
– Create static ResultSetExtractor reference – Implement callback and extractData method p

• Implement business method
26

– Integrate callback into JDBC templatehttp://courses.coreservlets.com Java EE training: call

ResultSet to Object Collection Mapping Process Continued
• Create applicationContext.xml • Register beans
– DAO and DataSource beans

• Inject dependencies
– Specify the DataSource bean as a DAO bean dependency

• Initialize the container • Access and use the DAO bean

27

Java EE training: http://courses.coreservlets.com

DAO Interface
public interface CustomerListQuery { public List<Customer> getCustomers(); }

28

Java EE training: http://courses.coreservlets.com

Setup DAO p
import org.springframework.jdbc.core.*; public class ResultSetExtractorCustomerListQuery implements CustomerListQuery { private JdbcTemplate jdbc; public ResultSetExtractorCustomerListQuery( DataSource dataSource) { jdbc = new JdbcTemplate(dataSource); } ... }

29

Java EE training: http://courses.coreservlets.com

Implement Callback p
... private static final ResultSetExtractor customerListExtractor = new ResultSetExtractor () {

public Object extractData(ResultSet resultSet) throws SQLException { List<Customer>list = new ArrayList<Customer>(); while(resultSet.next()){ i Customer customer = new Customer( resultSet.getString("id"), resultSet.getString("name")); resultSet getString("name")); list.add(customer); } return list; }
30

}; ...

Java EE training: http://courses.coreservlets.com

Integrate Callback g
import org.springframework.jdbc.core.simple.*; public class ResultSetExtractorCustomerListQuery implements CustomerListQuery { ... public List<Customer> getCustomers() { return (List) this.jdbc.query( "select id, name from customer" , customerListExtractor); } ... }

31

Java EE training: http://courses.coreservlets.com

DAO Bean
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns http://www.springframework.org/schema/beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"> <bean id="customerListQuery" class="coreservlets.ResultSetExtractorCustomerListQuery"> <constructor-arg ref= dataSource /> ref="dataSource" </bean> </beans>

32

Java EE training: http://courses.coreservlets.com

DAO Execution
import org.springframework.beans.factory.*; import org.springframework.context.support.*; org.springframework.context.support. ; public class Main { public static void main(String[] args) throws Exception { BeanFactory beanFactory = new ClassPathXmlApplicationContext(new String[]{ "/applicationContext.xml", "/dataSourceContext xml"}); /dataSourceContext.xml }); CustomerListQuery query = (CustomerListQuery) beanFactory.getBean("customerListQuery"); List<Customer>customers = query.getCustomers(); for(Customer customer : customers) { System.out.println(customer); } } Standard output } Customer id=jjoe, name=Java Joe Customer id=jjohn, name=Java John
33

Java EE training: http://courses.coreservlets.com

© 2008 coreservlets.com

Passing JDBC P i Parameters
Customized Java EE Training: http://courses.coreservlets.com/
Servlets, JSP, Struts, JSF/MyFaces/Facelets, Ajax, GWT, Spring, Hibernate/JPA, Java 5 & 6. Developed and taught by well-known author and developer. At public venues or onsite at your location.

Spring JDBC Parameter Support p g pp
• Passes Java values to JDBC statement variables
– C Converts from Java values to JDBC d t types t f J l t data t
• Type conversion implied by Java argument type • Optionally, explicitly specified via SQL parameter type arrays

– Assigns values to q y variable p g query placeholders
• Mapping implied by position or named parameters

– No configuration overhead
• No XML • No annotations

– Hard-coded settings
• Database changes results in code changes

• Spring API offers several mapping option
– Simple objects – Parameter map objects – Spring parameter objects

35

Java EE training: http://courses.coreservlets.com

Simple Object Parameters p j
• Passes parameter values as plain java.lang.Object( ) (s) • Type conversion is implied or explicit
– B d on Java argument type Based J – Additional arguments can be supplied specifying the type
• JdbcTemplate feature only p y

• Value assignment is implied
– Based on argument position

• JDBC template support
– JdbcTemplate#query – SimpleJdbcTemplate#query
36

Java EE training: http://courses.coreservlets.com

Simple Object Parameter Process
• Setup DAO
– Defer connectivity responsibilities • Design class for DataSource dependency injection – Use Spring JDBC APIs • Initialize Spring JDBC template(s) with the injected DataSource

• I l Implement callback t llb k • Implement business method
– Integrate callback into JDBC template call – Integrate object parameters into JDBC template call
• Create variable placeholders in SQL • Map object arguments according to placeholders

37

Java EE training: http://courses.coreservlets.com

Simle Object Parameter Process Continued
• Create applicationContext.xml • Register beans
– DAO and DataSource beans

• Inject dependencies
– Specify the DataSource bean as a DAO bean dependency

• Initialize the container • Access and use the DAO bean

38

Java EE training: http://courses.coreservlets.com

DAO Interface
public interface CustomerQuery { public Customer getCustomerByName(String name); }

39

Java EE training: http://courses.coreservlets.com

Setup DAO p
import org.springframework.jdbc.core.simple.*; public class ObjectParameterCustomerQuery implements CustomerQuery { private SimpleJdbcTemplate simpleJdbc; public ObjectParameterCustomerQuery(DataSource dataSource) { simpleJdbc = new SimpleJdbcTemplate(dataSource); } ... }

40

Java EE training: http://courses.coreservlets.com

Implement Callback p
private ParameterizedRowMapper<Customer> customerRowMapper = new ParameterizedRowMapper<Customer>(){ public Customer mapRow(ResultSet rslt, int rowNum) throws SQLException { return new Customer(rslt.getString("id"), rslt.getString("name")); } };

41

Java EE training: http://courses.coreservlets.com

Integrate Object Parameters g j
import org.springframework.jdbc.core.simple.*; public class ObjectParameterCustomerQuery implements CustomerQuery { ... public Customer getCustomerByName(String customerName) { try{ return simpleJdbc.queryForObject( "select id, name from customer where name = ?" select id ? , customerRowMapper , customerName); } catch(EmptyResultDataAccessException e){ return null; } } }
42

Java EE training: http://courses.coreservlets.com

DAO Bean
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns http://www.springframework.org/schema/beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"> <bean id="customerQuery" class="coreservlets.ObjectParameterCustomerQuery"> <constructor-arg ref= dataSource /> ref="dataSource" </bean> </beans>

43

Java EE training: http://courses.coreservlets.com

DAO Execution
import org.springframework.beans.factory.*; import org.springframework.context.support.*; org.springframework.context.support. ; public class Main { public static void main(String[] args) throws Exception { BeanFactory beanFactory = new ClassPathXmlApplicationContext(new String[]{ "/applicationContext.xml", "/dataSourceContext.xml"}); CustomerQuery query = (CustomerQuery) beanFactory.getBean("customerQuery"); Customer result = query.getCustomerByName("Java Joe"); System.out.println(result); } } Customer id=jjoe, name=Java Joe
44

Standard output Java EE training: http://courses.coreservlets.com

Parameter Map Objects p j
• Passes parameter names and values as maps
– Enabled by parameter naming support – Map keys are parameter names – Map values are parameter values

• Type conversion is implied
– Based on Java argument type

• Value assignment is explict g p
– Mapped to named parameters

• JDBC template support
– NamedParameterJdbcTemplate#query – SimpleJdbcTemplate#query
45

Java EE training: http://courses.coreservlets.com

Parameter Map Object Process p j
• Setup DAO
– Defer connectivity responsibilities • Design class for DataSource dependency injection – Use Spring JDBC APIs • Initialize Spring JDBC template(s) with the injected DataSource

• I l Implement callback t llb k • Implement business method
– Integrate callback into JDBC template call – Integrate object parameters into JDBC template call
• Create named variable placeholders in SQL command string
– Replace conventional variable placeholders (?) with a variable name prefixed with a colon (:namedParameter)
46

• Convert parameters into a map object • Integrate map into the JDBC template training: http://courses.coreservlets.com Java EE call

Parameter Map Object Process Continued
• Create applicationContext.xml • Register beans
– DAO and DataSource beans

• Inject dependencies
– Specify the DataSource bean as a DAO bean dependency

• Initialize the container • Access and use the DAO bean

47

Java EE training: http://courses.coreservlets.com

DAO Interface
public interface CustomerQuery { public Customer getCustomerByName(String name); }

48

Java EE training: http://courses.coreservlets.com

Setup DAO p
import org.springframework.jdbc.core.simple.*; public class MapParameterCustomerQuery implements CustomerQuery { private SimpleJdbcTemplate simpleJdbc; public MapParameterCustomerQuery(DataSource dataSource) { simpleJdbc = new SimpleJdbcTemplate(dataSource); } ... }

49

Java EE training: http://courses.coreservlets.com

Implement Callback p
private ParameterizedRowMapper<Customer> customerRowMapper = new ParameterizedRowMapper<Customer>(){ public Customer mapRow(ResultSet rslt, int rowNum) throws SQLException { return new Customer(rslt.getString("id"), rslt.getString("name")); } };

50

Java EE training: http://courses.coreservlets.com

Convert Parameters to Map Object
import org.springframework.jdbc.core.simple.*; public class MapParameterCustomerQuery implements CustomerQuery { ... private Map<String, Object> parameterize(String customerName) {

Map<String, Object>parameterMap = new HashMap<String, Object>() ne HashMap<String Object>(); parameterMap.put("customerName", customerName); return parameterMap;
} }
51

Java EE training: http://courses.coreservlets.com

Integrate Parameter Map Object g p j
import org.springframework.jdbc.core.simple.*; public class MapParameterCustomerQuery implements CustomerQuery { ... public Customer getCustomerByName(String customerName) { try{ Map<String, Object>parameterMap = parameterize(customerName); return simpleJdbc queryForObject( simpleJdbc.queryForObject( "select id, name from customer" + " where name = :customerName", customerRowMapper, parameterMap); } catch(EmptyResultDataAccessException e){ return null; t ll } } Java EE training: http://courses.coreservlets.com }

52

DAO Bean
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns http://www.springframework.org/schema/beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"> <bean id="customerQuery" class="coreservlets.MapParameterCustomerQuery"> <constructor-arg ref= dataSource /> ref="dataSource" </bean> </beans>

53

Java EE training: http://courses.coreservlets.com

DAO Execution
import org.springframework.beans.factory.*; import org.springframework.context.support.*; org.springframework.context.support. ; public class Main { public static void main(String[] args) throws Exception { BeanFactory beanFactory = new ClassPathXmlApplicationContext(new String[]{ "/applicationContext.xml", "/dataSourceContext.xml"}); CustomerQuery query = (CustomerQuery) beanFactory.getBean("customerQuery"); Customer result = query.getCustomerByName("Java Joe"); System.out.println(result); } } Customer id=jjoe, name=Java Joe
54

Standard output Java EE training: http://courses.coreservlets.com

Spring Parameter Object p g j
• Passes parameter names, values and/or types using a custom Spring type i t S i t
– Enabled by parameter naming support – Associates a parameter name with a value – Associates a parameter name with a type

• Type conversion is implied or explicit
– Based on Java argument value type – Based on explicit type setting

• Value assignment is explict
– Mapped to named parameters

• JDBC template support p pp
– NamedParameterJdbcTemplate#query – SimpleJdbcTemplate#query
55

Java EE training: http://courses.coreservlets.com

Spring Parameter Object Process
• Setup DAO
– Defer connectivity responsibilities • Design class for DataSource dependency injection – Use Spring JDBC APIs • Initialize Spring JDBC template(s) with the injected DataSource

• I l Implement callback t llb k • Implement business method
– Integrate callback into JDBC template call – Integrate object parameters into JDBC template call • Create named variable placeholders in SQL • C Convert parameters i into a SqlParameterSource object • Integrate Spring type into aJava EE training: http://courses.coreservlets.com JDBC template call

56

Spring Parameter Object Process
• Create applicationContext.xml • Register beans
– DAO and DataSource beans

• Inject dependencies
– Specify the DataSource bean as a DAO bean dependency

• Initialize the container • Access and use the DAO bean

57

Java EE training: http://courses.coreservlets.com

DAO Interface
public interface CustomerQuery { public Customer getCustomerByName(String name); }

58

Java EE training: http://courses.coreservlets.com

Setup DAO p
import org.springframework.jdbc.core.namedparam.*; import org.springframework.jdbc.core.simple.*; org.springframework.jdbc.core.simple. ; public class SqlParameterSourceCustomerQuery implements CustomerQuery { private SimpleJdbcTemplate simpleJdbc; public SqlParameterSourceCustomerQuery (DataSource dataSource) { simpleJdbc = new SimpleJdbcTemplate(dataSource); } ... }

59

Java EE training: http://courses.coreservlets.com

Implement Callback p
private ParameterizedRowMapper<Customer> customerRowMapper = new ParameterizedRowMapper<Customer>(){ public Customer mapRow(ResultSet rslt, int rowNum) throws SQLException { return new Customer(rslt.getString("id"), rslt.getString("name")); } };

60

Java EE training: http://courses.coreservlets.com

Convert Parameters to Spring Parameter Object
import org.springframework.jdbc.core.namedparam.*; import org.springframework.jdbc.core.simple.*; org.springframework.jdbc.core.simple. ; public class SqlParameterSourceCustomerQuery implements CustomerQuery { ... private SqlParameterSource parameterize(String customerName) { MapSqlParameterSource parameterMap = new MapSqlParameterSource(); parameterMap.addValue("customerName", customerName, Types.VARCHAR); return parameterMap; t t M } }
61

Java EE training: http://courses.coreservlets.com

Integrate Spring Parameter Object
import org.springframework.jdbc.core.simple.*; public class MapParameterCustomerQuery implements CustomerQuery { ... public Customer getCustomerByName(String customerName) { try{ SqlParameterSource parameterMap = parameterize(customerName); return this jdbc queryForObject( this.jdbc.queryForObject( "select id, name from customer" + " where name = :customerName", customerRowMapper, parameterMap); } catch(EmptyResultDataAccessException e){ return null; t ll } } Java EE training: http://courses.coreservlets.com }

62

DAO Bean
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns http://www.springframework.org/schema/beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"> <bean id="customerQuery" class="coreservlets.SqlParameterSourceCustomerQuery"> <constructor-arg ref= dataSource /> ref="dataSource" </bean> </beans>

63

Java EE training: http://courses.coreservlets.com

DAO Execution
import org.springframework.beans.factory.*; import org.springframework.context.support.*; org.springframework.context.support. ; public class Main { public static void main(String[] args) throws Exception { BeanFactory beanFactory = new ClassPathXmlApplicationContext(new String[]{ "/applicationContext.xml", "/dataSourceContext.xml"}); CustomerQuery query = (CustomerQuery) beanFactory.getBean("customerQuery"); Customer result = query.getCustomerByName("Java Joe"); System.out.println(result); } } Customer id=jjoe, name=Java Joe
64

Standard output Java EE training: http://courses.coreservlets.com

© 2008 coreservlets.com

Spring Query Objects
Customized Java EE Training: http://courses.coreservlets.com/
Servlets, JSP, Struts, JSF/MyFaces/Facelets, Ajax, GWT, Spring, Hibernate/JPA, Java 5 & 6. Developed and taught by well-known author and developer. At public venues or onsite at your location.

Spring Query Object p g y j
• JDBC query template
– Stores the SQL command – Implementor defines result set to domain type transformation t f ti – Reusable and thread-safe

• Parameter passing
– Supports objects and maps – Types are set explicitly

• Standalone implementation
– Depends on a DataSource and a SQL statement – Separate JDBC template is unnecessary
66

Java EE training: http://courses.coreservlets.com

Spring Query Object Process p g y j
• Setup DAO
– Defer connectivity responsibilities • Design class for DataSource dependency injection

• Do not initialize JdbcTemplate, SimpleJdbcTemplate, or implement a callback • Implement Spring query object g y j
– – – – Extend MappingSqlQuery Specify SQL and variable types Implement mapRow method Initialize and cache the query object from the DAO constructor body

67

Java EE training: http://courses.coreservlets.com

Spring Query Object Process Continued
• Implement business method
– Integrate Spring query object

• Create applicationContext.xml • Register beans
– DAO and DataSource beans

• Inject dependencies
– Specify the DataSource bean as a DAO bean dependency

• I iti li the container Initialize th t i • Access and use the DAO bean
68

Java EE training: http://courses.coreservlets.com

Setup DAO p
import org.springframework.jdbc.core.*; import org.springframework.jdbc.object.*; org.springframework.jdbc.object. ; public class QueryObjectCustomerQuery implements CustomerQuery { public QueryObjectCustomerQuery(DataSource dataSource) { } ... }

69

Java EE training: http://courses.coreservlets.com

Implement Spring Query Object p p g y j
class CustomerMappingSqlQuery extends MappingSqlQuery{ public CustomerMappingSqlQuery(DataSource dataSource) { super(dataSource, "select id, name from customer where name = ?"); super.setParameters( new SqlParameter[]{new SqlParameter(VARCHAR)}); } protected Object mapRow(ResultSet rslt, int rowNumber) throws SQLException { return new Customer(rslt.getString("id"), rslt.getString("name")); } }

70

Java EE training: http://courses.coreservlets.com

Initialize and Cache Spring Query Object
import org.springframework.jdbc.core.*; import org.springframework.jdbc.object.*; org.springframework.jdbc.object. ; public class QueryObjectCustomerQuery implements CustomerQuery { private MappingSqlQuery customerMappingSqlQuery; public QueryObjectCustomerQuery(DataSource dataSource) { customerMappingSqlQuery = new CustomerMappingSqlQuery(dataSource); } ... }

71

Java EE training: http://courses.coreservlets.com

Integrate Spring Query Object g p g y j
public class QueryObjectCustomerQuery implements CustomerQuery { private MappingSqlQuery query; public QueryObjectCustomerQuery(DataSource dataSource) { query = new CustomerMappingSqlQuery(dataSource); } public Customer getCustomerByName(String customerName) { try{ return (Customer) query.findObject(customerName); } catch(EmptyResultDataAccessException e){ return null; } } }
72

Java EE training: http://courses.coreservlets.com

DAO Bean
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns http://www.springframework.org/schema/beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"> <bean id="customerQuery" class="coreservlets.QueryObjectCustomerQuery"> <constructor-arg ref= dataSource /> ref="dataSource" </bean> </beans>

73

Java EE training: http://courses.coreservlets.com

DAO Execution
import org.springframework.beans.factory.*; import org.springframework.context.support.*; org.springframework.context.support. ; public class Main { public static void main(String[] args) throws Exception { BeanFactory beanFactory = new ClassPathXmlApplicationContext(new String[]{ "/applicationContext.xml", "/dataSourceContext.xml"}); CustomerQuery query = (CustomerQuery) beanFactory.getBean("customerQuery"); Customer result = query.getCustomerByName("Java Joe"); System.out.println(result); } } Customer id=jjoe, name=Java Joe
74

Standard output Java EE training: http://courses.coreservlets.com

© 2008 coreservlets.com

Modifying th M dif i the Database
Customized Java EE Training: http://courses.coreservlets.com/
Servlets, JSP, Struts, JSF/MyFaces/Facelets, Ajax, GWT, Spring, Hibernate/JPA, Java 5 & 6. Developed and taught by well-known author and developer. At public venues or onsite at your location.

Spring JDBC Table Updates p g p
• Database update interfaces exposed as JDBC template methods t l t th d
– JdbcTemplate#update – SimpleJdbcTemplate#update

• Multiple parameter mapping options
– Simple object arrays
• Optional SQL type settings

– Parameter map structure – Spring parameter object p gp j

• No ResultSet transformations • Added responsibility of checking the number of modified rows
76

Java EE training: http://courses.coreservlets.com

Spring JDBC Table Update Process
• Setup DAO
– Defer connectivity responsibilities • Design class for DataSource dependency injection – Use Spring JDBC APIs • Initialize Spring JDBC template(s) with the injected DataSource

• I l Implement parameter mapping mechanism t t i h i
– Simple object, map, or Spring parameter object

• Implement business method
– Implement JDBC template call • Create SQL command with variable placeholders • Handle parameters using parameter mapping mechanism • Verify result based on the affected rowcount Java EE training: http://courses.coreservlets.com

77

Spring JDBC Table Update Process
• Create applicationContext.xml • Register beans
– DAO and DataSource beans

• Inject dependencies
– Specify the DataSource bean as a DAO bean dependency

• Initialize the container • Access and use the DAO bean

78

Java EE training: http://courses.coreservlets.com

DAO Interface
public interface CustomerUpdate { public void save(Customer customer); public void deleteById (String customerId); }

79

Java EE training: http://courses.coreservlets.com

Setup DAO p
import org.springframework.jdbc.core.simple.*; public class SpringCustomerUpdate implements CustomerUpdate { private SimpleJdbcTemplate simpleJdbc; public SpringCustomerUpdate(DataSource dataSource) { simpleJdbc = new SimpleJdbcTemplate(dataSource); } ... }

80

Java EE training: http://courses.coreservlets.com

Implement Parameter Mapping p pp g
private Map<String, Object> parameterize(Customer cust) { Map<String, Object> parameterMap = new HashMap<String, Object>(); parameterMap.put("customerId", cust.getId()); parameterMap.put("customerName", cust.getName()); return parameterMap; }

81

Java EE training: http://courses.coreservlets.com

Implement Save p
public void save(Customer customer) { Map<String, Object>parameters = parameterize(customer); boolean updated = simpleJdbc.update( "update customer set name = :customerName" + " where id = :customerId", parameters) > 0; if(updated){ return; } simpleJdbc.update( "insert into customer (id, name)" + " values (:customerId, :customerName)", parameters); t ) }
82

Java EE training: http://courses.coreservlets.com

Implement Delete p
public void deleteById (String customerId) { simpleJdbc.update( "delete from customer where id = ?", customerId); }

83

Java EE training: http://courses.coreservlets.com

DAO Bean
<?xml version="1.0" encoding="UTF-8"?> <beans xmlns http://www.springframework.org/schema/beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"> <bean id="customerUpdate" class="coreservlets.SpringCustomerUpdate"> <constructor-arg ref= dataSource /> ref="dataSource" </bean> <bean id="customerListQuery" class="coreservlets.ParameterizedRowMapperCustomerListQuery"> <constructor-arg ref="dataSource" /> </bean> </beans>
84

Java EE training: http://courses.coreservlets.com

Instantiate Container
import org.springframework.beans.factory.*; import org.springframework.context.support.*; org.springframework.context.support. ; public class Main { public static void main(String[] args) throws Exception { BeanFactory beanFactory = new ClassPathXmlApplicationContext(new String[]{ "/applicationContext.xml", "/dataSourceContext.xml"}); } }

85

Java EE training: http://courses.coreservlets.com

Acquire Beans q
import org.springframework.beans.factory.*; import org.springframework.context.support.*; org.springframework.context.support. ; public class Main { public static void main(String[] args) throws Exception { BeanFactory beanFactory = new ClassPathXmlApplicationContext(new String[]{ "/applicationContext xml", /applicationContext.xml "/dataSourceContext.xml"}); CustomerUpdate customerUpdate = (CustomerUpdate) beanFactory.getBean("customerUpdate"); CustomerListQuery customerQuery = (CustomerListQuery) beanFactory.getBean("customerListQuery"); b F t tB (" t Li tQ ") } }
86

Java EE training: http://courses.coreservlets.com

Setup Customer Object p j
public class Main { public static void main(String[] args) throws Exception { BeanFactory beanFactory = ...; CustomerUpdate customerUpdate = ...; CustomerListQuery customerQuery = ...; Customer customer = new Customer(); customer.setId("jspring"); customer.setName( Joe Spring"); customer setName("Joe Spring ); } }

87

Java EE training: http://courses.coreservlets.com

Save Customer Object j
public class Main { public static void main(String[] args) throws Exception { ... Customer customer = new Customer(); customer.setId("jspring"); customer.setName("Joe Spring"); customerUpdate.save(customer); System.out.println( After System out println("After initial save : " + customerQuery.getCustomers()); } }
Standard output

88

After initial save : [Customer id=jspring, name=Joe Spring]
Java EE training: http://courses.coreservlets.com

Update Customer Object p j
public class Main { public static void main(String[] args) throws Exception { ... Customer customer = new Customer(); customer.setId("jspring"); customer.setName("Joe Spring"); customerUpdate.save(customer); System.out.println( After System out println("After initial save : " + customerQuery.getCustomers()); customer.setName("Joseph Spring"); customerUpdate.save(customer); System.out.println("After update customerQuery.getCustomers()); } }
89

: " +
Standard output p

After initial save : [Customer id=jspring, name=Joe Spring] After update : [Customer id=jspring, name=Joseph Spring]
Java EE training: http://courses.coreservlets.com

Delete Customer Object j
public class Main { public static void main(String[] args) throws Exception { ... customerUpdate.save(customer); System.out.println("After initial save : " + customerQuery.getCustomers()); customer.setName("Joseph Spring"); customerUpdate.save(customer); customerUpdate save(customer); System.out.println("After update customerQuery.getCustomers());

: " +

customerUpdate.deleteById(customer.getId()); System.out.println("After delete : " + customerQuery.getCustomers()); } }
90

Standard output p

After initial save : [Customer id=jspring, name=Joe Spring] After update : [Customer id=jspring, name=Joseph Spring] After update : []

Java EE training: http://courses.coreservlets.com

© 2008 coreservlets.com

Wrap-up
Customized Java EE Training: http://courses.coreservlets.com/
Servlets, JSP, Struts, JSF/MyFaces/Facelets, Ajax, GWT, Spring, Hibernate/JPA, Java 5 & 6. Developed and taught by well-known author and developer. At public venues or onsite at your location.

Summary y
• Steps for Spring JDBC
– Initiali e Jdb T Initialize JdbcTemplate and/or Si l Jdb T l t SimpleJdbcTemplate l t – Define SQL statement
• Traditional placeholder (?) or named parameters (:namedParameter)

– Implement parameter mapping
• Simple object (java.lang.Object), map (java.util.Map) or Spring parameter object (SqlParameterSource) • Overloaded JDBC template methods or Spring parameter object to explicitly set SQL types

• Process ResultSet objects for queries
– Row to collection - RowMapper with mapRow method – Row to typed collection - ParameterizedRowMapper<T> with mapRow#T method – ResultSet to collection - ResultSetExtractor with extractData method

• Inspect the modified row count for table updates
92

– Insert, update and delete statements via template update methods

Java EE training: http://courses.coreservlets.com

Summary Continued y
• Create applicationContext.xml • R i t beans Register b
– DAO and DataSource beans

• Inject dependencies
– Specify DataSource bean as a DAO bean dependency

• Initialize the container • Access and use the DAO beans

93

Java EE training: http://courses.coreservlets.com

© 2008 coreservlets.com

Questions? Q ti ?
Customized Java EE Training: http://courses.coreservlets.com/
Servlets, JSP, Struts, JSF/MyFaces/Facelets, Ajax, GWT, Spring, Hibernate/JPA, Java 5 & 6. Developed and taught by well-known author and developer. At public venues or onsite at your location.


								
To top