How to query single column using spring JdbcTemplate?
In the previous example you have seen Spring JDBC query example using JdbcDaoSupport
In this page you will see how to query single column using JdbcTemplate.
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;
public interface EmployeeDao {
public String findEmployeeName(int empId);
public int findMaxSalary();
}
|
Employee DAO implementation class: Note that below class extended JdbcDaoSupport, and didnot use JdbcTemplate directly.
Note carefully, below class comes with two methods, in which one method maps the column type, another method uses JdbcTemplate specific type
method:
package com.java2novice.dao;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
public class EmployeeDaoImpl extends JdbcDaoSupport implements EmployeeDao{
@Override
public String findEmployeeName(int empId) {
String query = "select name from employee where emp_id=?";
Object[] inputs = new Object[] {empId};
String empName = getJdbcTemplate().queryForObject(query, inputs, String.class);
return empName;
}
@Override
public int findMaxSalary() {
String query = "select max(salary) from employee";
int maxSalary = getJdbcTemplate().queryForInt(query);
return maxSalary;
}
}
|
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 org.springframework.context.ConfigurableApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.java2novice.dao.EmployeeDao;
public class SpringDemo {
public static void main(String a[]){
String confFile = "applicationContext.xml";
ConfigurableApplicationContext context =
new ClassPathXmlApplicationContext(confFile);
EmployeeDao empDao = (EmployeeDao) context.getBean("employeeDAO");
String empName = empDao.findEmployeeName(3);
System.out.println("Employee name: "+empName);
System.out.println("-----------------------------------------------");
int maxSal = empDao.findMaxSalary();
System.out.println("Max salary: "+maxSal);
}
}
|
|