Spring Framework support for JDBC part1

JdbcTemplate class simplifies the use of JDBC since it handles the creation and release of resources. it avoid one common mistake is failure to close connection.it also avoid repetitive code of creating and releasing resources (Connection,Statement,ResultSet).

your application code to provide sql and extract results logic and rest core JDBC logic like statement creation and execution handle by JdbcTemplate .

Spring Framework make database-access code clean, less error-prone, and more flexible. JdbcTemplate class executes SQL queries, update statements or stored procedure calls, and iteration over ResultSets and extraction of returned parameter values.It also catches JDBC exceptions and translates them to the generic, more informative.catch exceptions and translates them to generic and more informative

Spring JDBC Framework distributed in 4 different packages core,datasource,object and support.Spring provides following approach to access Database using JDBC.JDBC Template comes in 3 flavors

JdbcTemplate - this approach most widely used. All other approaches wrap JdbcTemplate and extend its functionality.

NamedParameterJdbcTemplate - This class handles parametrized queries in a much cleaner way.it replace "?" placeholder with named parameter

SimpleJdbcTemplate - this class combines the most frequently used features of both JdbcTemplate and NamedParameterJdbcTemplate .it also supports new feature of java5 like varargs, generics,autoboxing etc.

Steps to Use JDBC template
1.Create Simple table &Configure Jdbc.properties

CREATE TABLE EMPLOYEES
(
EMPLOYEE_ID NUMBER(6),
FIRST_NAME VARCHAR2(20 BYTE),
LAST_NAME VARCHAR2(25 BYTE) ,
EMAIL VARCHAR2(25 BYTE),
SALARY NUMBER(8,2)
)

jdbc.driverClassName=oracle.jdbc.OracleDriver
jdbc.url=jdbc:oracle:thin:@locahost:1521:DEMO
jdbc.username=test
jdbc.password=demo

2.Configure DataSource entry in applicationContext.xml file
<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<value>classpath:/jdbc.properties</value>
</property>
</bean>
<bean id="dataSource" destroy-method="close"
class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="${jdbc.driverClassName}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</bean>
3.Create JDBCTemplateDAO class which contains database operation using JDBCTemplate and EmpRowMapper Class to map row with database table

package com.kp.template.dao; import java.util.Collection; import javax.sql.DataSource; import org.springframework.jdbc.core.JdbcTemplate; import com.kp.row.mapper.EmpRowMapper; import com.kp.vo.Employee; public class JDBCTemplateDAO { private JdbcTemplate jdbcTemplate; public void setDataSource(DataSource dataSource) { this.jdbcTemplate = new JdbcTemplate(dataSource); } public int getTotalEmployeeCount() { String sql = "select count(*) from Employees"; int empCount = this.jdbcTemplate.queryForInt(sql); return empCount; } public Employee loadEmployee(Integer employeeId) { String sql = "select * from Employees where employee_id=?"; Employee emp = (Employee) this.jdbcTemplate.queryForObject(sql, new Object[] { employeeId }, new EmpRowMapper()); System.out.println(emp.getEmail()); return emp; } public Collection getAllEmployeeDetails() { String sql = "select * from Employees"; Collection col = this.jdbcTemplate.query(sql, new EmpRowMapper()); return col; } public void updateEmployeeDetail(int empId, String email) { String sql = "update Employees set email=? where employee_id=?"; this.jdbcTemplate.update(sql, new Object[] { "xyz@test.com", 101 }); } }

package com.kp.row.mapper; import java.sql.ResultSet; import java.sql.SQLException; import org.springframework.jdbc.core.RowMapper; import com.kp.vo.Employee; public class EmpRowMapper implements RowMapper { public Object mapRow(ResultSet rs, int rowNum) throws SQLException { Employee emp=new Employee(); emp.setEmpId(new Integer(rs.getInt("employee_id"))); emp.setFirstname(rs.getString("first_name")); emp.setLastName(rs.getString("last_name")); emp.setSalary(new Double(rs.getDouble("salary"))); emp.setEmail(rs.getString("email")); return emp; } }

package com.kp.vo; public class Employee { private Integer empId; private String firstname; private String lastName; private String email; private Double salary; public Integer getEmpId() { return empId; } public void setEmpId(Integer empId) { this.empId = empId; } public String getFirstname() { return firstname; } public void setFirstname(String firstname) { this.firstname = firstname; } public String getLastName() { return lastName; } public void setLastName(String lastName) { this.lastName = lastName; } public String getEmail() { return email; } public void setEmail(String email) { this.email = email; } public Double getSalary() { return salary; } public void setSalary(Double salary) { this.salary = salary; } }

4. JDBCTemplateExecuteService class used to call JDBCTemplateDAO method

package com.kp.template.service; import java.util.Collection; import com.kp.template.dao.JDBCTemplateDAO; import com.kp.vo.Employee; public class JDBCTemplateExecuteService { private JDBCTemplateDAO jdbcTemplateDAO; public int getTotalEmployeeCount(){ return jdbcTemplateDAO.getTotalEmployeeCount(); } public Employee loadEmployee(int employeeId){ return jdbcTemplateDAO.loadEmployee(employeeId); } public Collection getAllEmployeeDetails(){ return jdbcTemplateDAO.getAllEmployeeDetails(); } public void updateEmployeeDetail(int empId, String email){ jdbcTemplateDAO.updateEmployeeDetail(empId, email); } public JDBCTemplateDAO getJdbcTemplateDAO() { return jdbcTemplateDAO; } public void setJdbcTemplateDAO(JDBCTemplateDAO jdbcTemplateDAO) { this.jdbcTemplateDAO = jdbcTemplateDAO; } }

5.Create Class to call Service Method

package com.kp.action; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.kp.template.service.JDBCTemplateExecuteService; public class TemplateCallAction { public static void main(String[] args) { ApplicationContext ctx = new ClassPathXmlApplicationContext( "applicationContext.xml"); JDBCTemplateExecuteService templateService = (JDBCTemplateExecuteService)ctx .getBean("templateExecuteService"); System.out.println(templateService.getTotalEmployeeCount()); System.out.println(templateService.loadEmployee(201)); System.out.println(templateService.getAllEmployeeDetails().size()); templateService.updateEmployeeDetail(201, "abc@test.com"); } }

5.Complete applicationContext.xml file
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE beans PUBLIC "-//SPRING//DTD BEAN//EN" "http://www.springframework.org/dtd/spring-beans.dtd">

<beans>
<bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="locations">
<value>classpath:/jdbc.properties</value>
</property>
</bean>
<bean id="dataSource" destroy-method="close"
class="org.apache.commons.dbcp.BasicDataSource">
<property name="driverClassName" value="${jdbc.driverClassName}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</bean>

<bean id="templateExecuteService" class="com.kp.template.service.JDBCTemplateExecuteService">
<property name="jdbcTemplateDAO" ref="jdbcTemplateDAO"/>
</bean>

<bean id="jdbcTemplateDAO" class="com.kp.template.dao.JDBCTemplateDAO">
<property name="dataSource" ref="dataSource"/>
</bean>
</beans>

rest two approach i will discuss in next post