NOTE: Source Code available for download at the bottom of the page
We will learn how to use Spring JdbcDaoSupport for implementing our database operations.
What's needed?
Overall design
Let's get started.
employee table
CREATE TABLE `employeestore`.`employee` ( `employee_id` INT NOT NULL, `name` VARCHAR(45) NULL, `age` VARCHAR(45) NULL, PRIMARY KEY (`employee_id`));
Create a maven project of artifact "maven-archetype-quickstart" as shown here. Remember to select the correct artifact "maven-archetype-quickstart" as shown below.
Update the dependencies in pom file.
For Spring context jars
<dependency> <groupId>org.springframework</groupId> <artifactId>spring-context</artifactId> <version>4.0.0.RELEASE</version> </dependency>
For Spring JDBC jars
<dependency> <groupId>org.springframework</groupId> <artifactId>spring-jdbc</artifactId> <version>4.0.0.RELEASE</version> </dependency>
For mySQl connector/driver
<dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.28</version> </dependency>
Employee Model Class
package com.javavision.SpringJDBC.model; public class Employee { public String name; public long employeeId; public int age; public Employee(){ } public Employee(String name, long employeeId, int age) { super(); this.name = name; this.employeeId = employeeId; this.age = age; } public String getName() { return name; } public void setName(String name) { this.name = name; } public long getEmployeeId() { return employeeId; } public void setEmployeeId(long employeeId) { this.employeeId = employeeId; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } }
This will act as our model object for communication with DAO.
Create an interface EmployeeDAO to define the dao operations.
EmployeeDAO
package com.javavision.SpringJDBC.dao; import com.javavision.SpringJDBC.model.Employee; public interface EmployeeDAO { public int insertEmployee(Employee employee); public Employee getEmployee( long employeeId ); }
Create implementation class EmployeeDAOImpl
EmployeeDAOImpl
package com.javavision.SpringJDBC.dao; import java.sql.ResultSet; import java.sql.SQLException; import org.springframework.dao.EmptyResultDataAccessException; import org.springframework.jdbc.core.RowMapper; import org.springframework.jdbc.core.support.JdbcDaoSupport; import com.javavision.SpringJDBC.model.Employee; public class EmployeeDAOImpl extends JdbcDaoSupport implements EmployeeDAO { public int insertEmployee(Employee employee) { String insert_employee = "INSERT INTO employee(employee_id,name,age)VALUES(?,?,?);"; int count = getJdbcTemplate().update( insert_employee, new Object[] { employee.getEmployeeId(), employee.getName(),employee.getAge() }); return count; } public Employee getEmployee(long employeeId) { Employee emp; try { emp = getJdbcTemplate().queryForObject( "select * from employee where employee_id = " + employeeId, new RowMapper<Employee>() { public Employee mapRow(ResultSet rs, int rownumber) throws SQLException { Employee e = new Employee(); e.setEmployeeId(rs.getLong("employee_id")); e.setName(rs.getString("name")); e.setAge(rs.getInt("age")); return e; } }); } catch (EmptyResultDataAccessException e) { return null; } return emp; } }
NOTE: There is no need to define JdbcTemplate
Create the application context.
application-context.xml
<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="employeeDAO" class="com.javavision.SpringJDBC.dao.EmployeeDAOImpl"> <property name="jdbcTemplate" ref="myJdbcTemplate"/> </bean> <bean id="myJdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate"> <constructor-arg ref="dataSource"/> </bean> <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource"> <property name="driverClassName" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://localhost:3306/employeestore" /> <property name="username" value="root" /> <property name="password" value="admin" /> </bean> </beans>
Let's put everything together.
Main class to load the spring application context and call the DAO methods.
package com.javavision.SpringJDBC; import org.springframework.context.ApplicationContext; import org.springframework.context.support.ClassPathXmlApplicationContext; import com.javavision.SpringJDBC.dao.EmployeeDAO; import com.javavision.SpringJDBC.model.Employee; public class MainCaller { public static void main(String[] args) { ApplicationContext applicationContext = new ClassPathXmlApplicationContext( "application-context.xml"); EmployeeDAO employeeDAO = (EmployeeDAO)applicationContext.getBean("employeeDAO"); Employee e1 = new Employee("Dan", 123, 20); int count = employeeDAO.insertEmployee(e1); Employee retrievedEmployee = employeeDAO.getEmployee(123); System.out.println("Retrieved Employee :: " + retrievedEmployee.getEmployeeId() + " ," + retrievedEmployee.getName() + " ," + retrievedEmployee.getAge()); } }
Project Structure:
|