Connecting to Database

1. Connecting to Database

2. Agenda • Problems with plain JDBC interaction • Spring way of handling database access

3. Database Connectivity • Any non trivial enterprise application usually have a persistence mechanism and usually it’s a SQL compliant database. • Spring does not provide any native support for database access. • Spring provides integration to various data access framework which includes • JDBC • Hibernate • iBatis • Apache Object Relationship Bridge • JPA • Toplink

4. Database Connectivity • In spite of SQL standards there are variation in the dialect support by different vendors. • Database resources need to be managed • Executing and managing connections to database is a non trivial process. • Developer has to manage transaction boundaries.

5. Database Connectivity Curse of SQLException • Unable to connect to database. • Query being performed has syntax error. • Tables/Columns do not exist • Violates database constraint Can’t do much about it….

6. Plain JDBC Way Employee employee= new Employee(); employee.setName(“Manmohan"); try{ Class.forName("org.hsqldb.jdbcDriver"); }catch(ClassNotFoundExceptioncfe){ log.error("Driver not found"); } Connection conn = null; PreparedStatement stmt = null;

7. Plain JDBC Way try { conn = DriverManager.getConnection ("jdbc:hsqldb:hsql://localhost","sa",""); stmt = conn.prepareStatement ("insert into EMPLOYEE (name) values (?) "); stmt.setString(1, student.getName()); stmt.execute(); }catch(SQLException se){ log.error("Problem with data insert"); }

8. Plain JDBC Way finally{ try{ if(stmt != null) { stmt.close(); } if(conn != null) { conn.close(); } }catch(SQLException se) { log.error(“Could not close resource properly”); }

9. Plain JDBC Way Define connection parameters Open the connection Specify the statement Prepare and execute the statement Process any exception Handle transactions Close the connection We have not even talked about Transaction Details yet

10. Plain JDBC Way Define connection parameters Open the connection Specify the statement Prepare and execute the statement Set up the loop to iterate through the results (if any) Do the work for each iteration Process any exception Handle transactions Close the connection

11. DAO DAO interface Service Object DAO Implementation • Spring promotes DAO pattern for data access. This promotes exposing functionality through interfaces

12. DAO • Programming against DAO interface will shield us from implementation details of underlying data access code public interface employeeDao{ public void saveEmployee(Employee employee); }

13. DAO • Move the implementation to save a student to EmployeeJdbcDao public class EmployeeJdbcDao implements EmployeeDao{ public void saveEmployee(Employee employee) { //Move all the JDBC ineraction code here } }

14. Spring way to Connect Database • No matter which data access technology is used it has a fixed part and a programmer defined part. • Spring handles the data access using templates and callbacks. • The fixed part like opening closing connection is handled by template and the variable part like how to handle the result is handled by callbacks.

15. Spring way to Connect Database • Spring comes with several data access template for different persistence mechanism. Some of which are: • JdbcTemplate • SimpleJdbcTemplate • HibernateTemplate • JpaTemplate

16. JdbcTemplate • JdbcTemplate class simplifies working with JDBC • It will automatically handle resource management, exception handling and transaction management • It is a thread safe class so you can use a single instance that many classes can use. • It also gives access to Connection with getConnectionmethod

17. JdbcTemplate • Change the DAO implementation using JdbcTemplate public class EmployeeJdbcDao implements EmployeeDao{ private JdbcTemplatejdbcTemplate; public void setJdbcTemplate(JdbcTemplate template){ jdbcTemplate = tempalte; }

18. JdbcTemplate • Change the DAO implementation using JdbcTemplate public void saveEmployee(Employee employee) { jdbcTemplate.update ("insert into Employee (name) values (?)“, employee.getName()); } Where all code gone and who sets JdbcTemplate?

19. JdbcTemplate • Configure JdbcTemplate in configuration <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <property name="dataSource" ref="dataSource" /> </bean>

20. JdbcTemplate • Configure dataSource <bean id="dataSource" class="org.springframework.jdbc.datasource. DriverManagerDataSource”> <property name="driverClassName" value="org.hsqldb.jdbcDriver" /> <property name="url” value="jdbc:hsqldb:hsql://localhost" /> <property name="username" value="sa" /> <property name="password" value="" /> </bean>

21. JdbcTemplate – Fetching results • Collection can be fetched and list of proper objects will get created @Override public List<Employee> getEmployees() { return jdbcTemplate.query ("Select name as name, salary as salary from Employee", new ParameterizedRowMapper<Employee>(){ public Employee mapRow(ResultSetrs,introwNum) throws SQLException { Employee employee = new Employee(); employee.setName(rs.getString("name")); employee.setSalary(rs.getInt("salary")); return employee;

22. Accessing Datasource • Accessing datasource using jndi lookup <bean id =“datasource” class=“org.springframework.jndi.JndiObjectFactoryBean”> <property name=“jndiName” value=/jdbc/ds”/> <property name=“resourceRef” value=“true”/> </bean> • Spring 2.0+ you can use jee namespace <jee:jndi-lookup id=“datasource” jndi-name=“jdbc/ds” resource-ref=“true” />

23. Exception Handling • Handling of Exceptions is a pain as we have seen • Spring Exceptions are unchecked so no need to catch them. • Spring throws finer exception which gives better insight into problem • The root of Spring exception hierarchy is DataAccessException

24. Conclusion • Problems with plain JDBC interaction • Spring way of handling database access