Evgeniy Krivosheev Vyacheslav Yakovenko Last update: Feb, 2012 Spring Framework Module 4 – DAO, JDBC

2 Contents DAO Design Pattern JDBC Support in Spring Framework javax.sql.DataSource Configuring javax.sql.DataSource JdbcTemplate RowMapper JdbcDaoSupport Parameterized SQL queries JdbcTemplate :: Insert / Update / Delete JdbcTemplate :: Other SQL queries

3 Spring :: DAO Design Pattern

4

5 BOOK ID: integer TITLE : varchar COMMENT : varchar DATE_RELEASE : timestamp AUTHOR_ID: integer

6 Spring :: DAO Design Pattern Spring DAO is a module aimed at data handling; Makes it easy to work with such technologies as JDBC, Hibernate, JDO, etc.; Allows to switch between technologies fairly easy; Facilitates handling specific exceptions;

7 Spring :: DAO Design Pattern Data structure design is abstracted from specific database; The code is simplified and business objects are explicit; Shipping from one DB (ORM, etc.) to another one is made easier; Data access mechanism is accumulated at separate level;

8 Spring :: JDBC support

9 Why use JDBC, if there is ORM? Flexibility: using all RDBMS possibilities JDBC transparence – everything is under the control Perfomance No magic Why (plain) JDBC is not enough? Manual exception handling Manual transaction management No mapping of data to the objects Big amount of the service code

10 Spring :: Plain JDBC example

11 Spring :: Spring+JDBC example

12 Spring :: JDBC Support Without Spring: Define connection parameters; Open the connection; Specify the statement; Prepare and execute the statement; Iteration through the results; Do the work for each iteration; Process any exception; Handle transactions; Close the connection; With Spring support: Specify the statement; Do the work for each iteration;

13 Spring :: JDBC Support Core classes for work with JDBC in Spring: javax.sql.DataSource: controls database connections; JdbcTemplate is a central class that control queries execution; RowMapper: controls mapping of each query row; JdbcDaoSupport: facilitates configuring and transferring parameters;

14 Spring :: javax.sql.DataSource DataSource Interface is a part of the JDBC specification that can be seen as connection factory; Spring connects the database via DataSource; DataSource allows to hide connection pooling and transaction management;

15 Spring :: Retrieving javax.sql.DataSource How to retrieve the DataSource? Configure its own: Makes unit testing easier; Web container is not required; Via JNDI; Using DataSource implementations: Apache DBCP; c3p0 is the most useful implementation;

16 Database Connection Pool (dbcp) When new user access DB, it gets the connection from the pool Opening the connection takes the time If all opened connections are busy then new connection is created As soon as user free up the connection it becomes available for other users If the connection is not used its closing Spring :: Retrieving javax.sql.DataSource

17 Spring :: Configuring javax.sql.DataSource OR <jee:jndi-lookup id="dataSource" jndi-name="java:comp/env/jdbc/datasource"/>

18 Spring :: Configuring javax.sql.DataSource In prototyping and testing, Spring allows moving embedded database up in context (HSQLDB / H2 / Derby). HSQLDB is used by default. или

19 Spring :: JdbcTemplate JdbcTemplate is the central class in the package org.springframework.jdbc.core: Executes SQL queries; Iterates over results; Catches JDBC exceptions; Parameters necessary when executing SQL query: DataSource; RowMapper; SQL query row;

20 Spring :: JdbcTemplate Instance of JdbcTemplate class is threadsafe; Can be configured only once and then be used in various DAO; DataSource is needed to create JdbcTemplate; Generally, DataSource is transferred to DAO and then to JdbcTemplate;

21 Spring :: RowMapper BOOK ID: integer TITLE : varchar COMMENT : varchar DATE_RELEASE : timestamp Mapping data from DB to the object model RowMapper is doing mapping of ResultSet to the certain objects ResultSetRowMapper Book mapRow(ResultSet rs, int rowNum)

22 Spring :: RowMapper

23 Spring :: PreparedStatementSetter BOOK ID: integer TITLE : varchar COMMENT : varchar DATE_RELEASE : timestamp Mapping data from object model to SQL PreparedStatementSetter is doing mapping of object to SQL request SQLPreparedStatement getPreparedStatementSetter (final Book book) +

24 Spring :: PreparedStatementSetter

25 Spring :: RowMapper Interface from org.springframework.jdbc.core; It is implemented through ResultSet mapping in specific objects; Describes operations for each ResultSet row; Used in query() method from JdbcTemplate or for results of stored procedure;

26 Spring :: JdbcTemplate Example Create tables and business objects; Configure DataSource; Create DAO class; Transfer DataSource to DAO; Implement RowMapper; Create the JdbcTemplate instance; Transfer DataSource there; Invoke query() method; Parameters: SQL query and RowMapper;

27 Spring :: JdbcTemplate Example When calling countryDao.getCountryList() we get the list of objects of Country type.

28 Spring :: JdbcTemplate Example

29 Spring :: JdbcDaoSupport DAO classes can inherit from JdbcDaoSupport; In this case setDataSource(..) method will be already implemented; JdbcDaoSupport facilitates working with DataSource and hides how JdbcTemplate is created;

30 public class JdbcCustomerDAO extends JdbcDaoSupport implements CustomerDAO { //no need to set datasource here public void insert(Customer customer) { String sql = "INSERT INTO CUSTOMER (CUST_ID, NAME, AGE) + VALUES (?, ?, ?) »; getJdbcTemplate().update(sql, new Object[] { customer.getCustId(), customer.getName(),customer.getAge() }); } <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> Spring :: JdbcDaoSupport

31 Spring :: NamedParameterJdbcTemplate Created using NamedParameterJdbcTemplate; Configured exactly as JdbcTemplate;

32 Spring :: ParameterizedRowMapper

33 Spring :: JdbcTemplate :: Insert Insert, Update and Delete are executed in the same way; The only difference is SQL query;

34 Spring :: JdbcTemplate :: Other SQL queries Execute method from JdbcTemplate can be used when executing any SQL query:

35 Spring :: Exceptions translation Spring translates all technology-specific exceptions such as SQLException to its own exception class hierarchy with the DataAccessException as the root exception; Spring can also wrap checked exceptions specific to Hibernate, JDO, and JPA, and convert them to runtime exceptions;

36 Spring :: DAO exceptions hierarchy

37 public class CustomSQLErrorCodesTranslator extends SQLErrorCodeSQLExceptionTranslator { protected DataAccessException customTranslate(String task, String sql, SQLException sqlex) { if (sqlex.getErrorCode() == ) { return new DeadlockLoserDataAccessException(task, sqlex); } return null; } private JdbcTemplate jdbcTemoplate; public void setDataSource(DataSource dataSource) { this.jdbcTemplate = new JdbcTemplate(); this.jdbcTemplate.setDataSource(dataSource); CustomSQLErrorCodesTranslator tr = new CustomSQLErrorCodesTranslator(); tr.setDataSource(dataSource); this.jdbcTemplate.setExceptionTranslator(tr); } Spring :: Custom DAO exceptions translator

38 Exercises :6 : Using JDBC in Spring when handling data – 45 min for practice; – 15 min for discussion;

39 Any questions!?