Spring JDBC query example using JdbcDaoSupport
In the previous example you have seen Spring JDBC example with JdbcDaoSupport.
This page you will see more examples on how to query single record and query multiple records using BeanPropertyRowMapper.
pom.xml file gives all required dependencies:
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>SpringHelloWorld</groupId>
<artifactId>SpringHelloWorld</artifactId>
<version>0.0.1-SNAPSHOT</version>
<properties>
<spring.version>3.2.0.RELEASE</spring.version>
</properties>
<dependencies>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-core</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>${spring.version}</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.31</version>
</dependency>
</dependencies>
</project>
|
Employee model class:
package com.java2novice.model;
public class Employee {
private int empId;
private String name;
private int salary;
private String dept;
public int getEmpId() {
return empId;
}
public void setEmpId(int empId) {
this.empId = empId;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getSalary() {
return salary;
}
public void setSalary(int salary) {
this.salary = salary;
}
public String getDept() {
return dept;
}
public void setDept(String dept) {
this.dept = dept;
}
}
|
Employee DAO interface:
package com.java2novice.dao;
import java.util.List;
import com.java2novice.model.Employee;
public interface EmployeeDao {
public Employee findEmployeeById(int empId);
public List<Employee> findAllEmployees();
}
|
Employee DAO implementation class: Note that below class extended JdbcDaoSupport, and didnot use JdbcTemplate directly.
When using BeanPropertyRowMapper make sure that model properties matches with table column names. Also note that the BeanPropertyRowMapper
also maps database column names like emp_id to empId attribute in the class.
package com.java2novice.dao;
import java.util.ArrayList;
import java.util.List;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import com.java2novice.model.Employee;
public class EmployeeDaoImpl extends JdbcDaoSupport implements EmployeeDao{
@Override
public Employee findEmployeeById(int empId) {
Employee emp = null;
String query = "select * from employee where emp_id=?";
Object[] inputs = new Object[] {empId};
emp = (Employee) getJdbcTemplate().queryForObject(query, inputs,
new BeanPropertyRowMapper(Employee.class));
return emp;
}
@Override
public List<Employee> findAllEmployees() {
List<Employee> empList = new ArrayList<Employee>();
String query = "select * from employee";
empList = getJdbcTemplate().query(query,
new BeanPropertyRowMapper(Employee.class));
return empList;
}
}
|
Here is the xml based configuration file, look at mysql DB configurations:
<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-3.0.xsd">
<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/java2novice" />
<property name="username" value="user_name" />
<property name="password" value="password" />
</bean>
<bean id="employeeDAO" class="com.java2novice.dao.EmployeeDaoImpl">
<property name="dataSource" ref="dataSource" />
</bean>
</beans>
|
Here is the final demo class:
package com.java2novice.test;
import java.util.List;
import org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.java2novice.dao.EmployeeDao;
import com.java2novice.model.Employee;
public class SpringDemo {
public static void main(String a[]){
String confFile = "applicationContext.xml";
ConfigurableApplicationContext context =
new ClassPathXmlApplicationContext(confFile);
EmployeeDao empDao = (EmployeeDao) context.getBean("employeeDAO");
Employee emp = empDao.findEmployeeById(3);
System.out.println(emp.getName()+" | "+emp.getSalary()+" | "+emp.getDept());
System.out.println("-----------------------------------------------");
List<Employee> empList = empDao.findAllEmployees();
for(Employee em:empList){
System.out.println(em.getName()+" | "+em.getSalary()+" | "+em.getDept());
}
}
}
|
|