Spring JDBC

1. Spring JDBC

2. Doel • Database acties • Flexibel • Gecontroleerd • Productief

3. Database acties • CRUD • stored procedures • Lob • ...

4. Flexibel • jdbc • ORM framework • connection pooling • datasource • testen • outside container • mock objecten • Database onafhankelijk (?!)

5. Gecontroleerd • Exception handling • Connection leaking

6. Productief • gericht op functie • geen plumbing

7. Architectuur Domain Object Domain Object Value Object JNDI Service DAO

8. SIDE STEP - Architectuur issue • Hoe implementeer je de emp – dept en emp – mgr relatie in de domein objecten? • Hoe ga je met die relatie om in DAO’s? • ORM taak • lazy loading vs ... Department Employee

9. Example private Connection getConnection() throws SQLException { try { DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver()); return DriverManager.getConnection ("jdbc:oracle:thin:@localhost:1521:orcl“ ,"scott", "tiger"); } catch (SQLException sqle) { // handle exception return null; } } import java.sql.*; import javax.sql.*; public class EmpDao { public List getAllEmployees() { Connection con = null; PreparedStatement pstmt = null; ResultSet rs = null; List emps = new ArrayList(); try { con = getConnection(); pstmt = con.prepareStatement ("select * from emp"); rs = pstmt.executeQuery(); while (rs.next()) { Employee e = new Employee(); e.setId (rs.getLong(1)); e.setName (rs.getString(2)); // ... emps.add(e); } } catch (SQLException e) { // handle exception } finally { try { rs.close(); pstmt.close(); con.close(); } catch (SQLException e1) { // no action needed } } return emps; } } private Connection getConnection() throws SQLException { try { Context ic = new InitialContext(); DataSource ds = (DataSource) ic.lookup ("java:comp/env/jdbc/myDatabase"); return ds.getConnection(); } catch (NamingException e) { // handle exception return null; } }

10. How can Spring help? Make life easier: • DAO Support • JDBC, Hibernate, Toplink, iBatis, JDO, ... • Dependency Injection • Jdbc helper classes • Exception Handling • MockObjects

11. Spring Architectuur Domain Object Domain Object JdbcDaoSupport HibernateDaoSupport TopLinkDaoSupport ... XXDAO Support DAO Interface Domain Object Service DAO ApplicationContext-jdbc

12. Example A public interface empDao { public List getAllEmployees (); } public class EmployeeJdbcDao extends JdbcDaoSupport implements EmpDao { public List getAllEmployees() { JdbcTemplate jt = getJdbcTemplate(); return jt.queryForList (“select * from emp”); } } <bean id="dataSourceDBDirect" class="org.springframework.jdbc.datasource.DriverManagerDataSource" destroy-method="close"> <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" /> <property name="url" value="jdbc:oracle:thin:@localhost:1521:BAARSJES1" /> <property name="username" value="scott" /> <property name="password" value="tiger" /> </bean> <bean id="employeeDAO" class="nl.amis.demo.dao.jdbc.EmployeeJdbcDao" > <property name="dataSource"> <ref local="dataSourceDBDirect" /> </property> </bean>

13. Exception Handling • RuntimeException ipv checked SQLException • DataAccessException • SQLException Translation • DataIntegrityViolationException • DataRetrievalFailureException • CannotGetJdbcConnectionException • ...

14. jdbc helper classes • JdbcTemplate • query, queryForList, queryForInt, queryFor.. • ArrayList (per row) of HashMaps (column name as key) • RowMapper • PreparedStatementCreator/Callback • MappingSQLQuery • ...

15. Testen en MockObjects public class TestEmployeeDao extends AbstractDependencyInjectionSpringContextTests { private EmployeeDao employeeDAO; public void setEmployeeDAO(EmployeeDao employeeDAO) { this.employeeDAO = employeeDAO; } protected String[] getConfigLocations() { return new String[] {"nl/amis/demo/dao/jdbc/applicationContext-jdbc.xml"}; } public void testFindEmployeeById () { Employee emp = employeeDAO.getEmployeeById(7839); assertEquals("KING", emp.getName()); assertEquals("PRESIDENT", emp.getJob()); // ... } } <bean id="employee7839" class="nl.amis.demo.domain.Employee"> <property name="name" value="KING" /> <property name="employeeNumber" value="7839" /> <property name="job" value="PRESIDENT" /> </bean> <bean id="employeeMockDAO" class="nl.amis.demo.dao.EmployeeMockDao"> <property name="emp"> <ref local="employee7839" /> </property> </bean>