August 3 2016

Spring NamedParameterJdbcTemplate example

Spring NamedParameterJdbcTemplate example

Spring NamedParameterJdbcTemplate is template like JdbcTemplate with set of methods supporting JDBC operations. NamedParameterJdbcTemplate supports named parameters. A positional parameter (?) is set by its index in the clause. A named parameter (:paramName) is set by its name.

NamedParameterJdbcTemplate allows us to represent and pass the sql parameters with parameter names rather than the positional parameter (‘?’).

In the below example, EMPLOYEE_ID parameter is set by name ‘ :empId’(colon followed by a parameter name).


String deleteQuery = "DELETE FROM Employee WHERE EMPLOYEE_ID = :empId";

Map parameters = new HashMap();
parameters.put("empId", eid);
namedParameterJdbcTemplate.update(deleteQuery, parameters);

Example

Employee.java


package com.answersz.springJdbcTemplate;

public class Employee {

private Integer age;
private Integer employeeId;
private String fatherName;
private String name;

public Integer getAge() {
return age;
}

public void setAge(Integer age) {
this.age = age;
}

public Integer getEmployeeId() {
return employeeId;
}

public void setEmployeeId(Integer employeeId) {
this.employeeId = employeeId;
}

public String getFatherName() {
return fatherName;
}

public void setFatherName(String fatherName) {
this.fatherName = fatherName;
}

public String getName() {
return name;
}

public void setName(String name) {
this.name = name;
}

}

EmployeeDAO.java


package com.answersz.springJdbcTemplate;

import java.util.HashMap;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;

public class EmployeeDAO {

@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

public void insertEmployee(final Employee employee) {

String insertQuery = "INSERT INTO EMPLOYEE " + "(EMPLOYEE_ID, NAME, FATHER_NAME,AGE) "
+ "VALUES (:empId, :name, :fname,:age)";

Map parameters = new HashMap();
parameters.put("empId", employee.getEmployeeId());
parameters.put("name", employee.getName());
parameters.put("fname", employee.getFatherName());
parameters.put("age", employee.getAge());
namedParameterJdbcTemplate.update(insertQuery, parameters);
}

public void deleteEmployee(final Integer eid) {

String deleteQuery = "DELETE FROM Employee WHERE EMPLOYEE_ID = :empId";

Map parameters = new HashMap();
parameters.put("empId", eid);
namedParameterJdbcTemplate.update(deleteQuery, parameters);
}

public NamedParameterJdbcTemplate getNamedParameterJdbcTemplate() {
return namedParameterJdbcTemplate;
}

public void setNamedParameterJdbcTemplate(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
}

}

namedParameterJdbcTemplateConfig.xml


<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
<bean id="employeeDAO" class="com.answersz.springJdbcTemplate.EmployeeDAO">

<property name="namedParameterJdbcTemplate" ref="namedParameterJdbcTemplate" />
</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/answersz" />
<property name="username" value="root" />
<property name="password" value="admin" />
</bean>

<bean id="namedParameterJdbcTemplate"
class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">
<constructor-arg ref="dataSource"></constructor-arg>
</bean>

</beans>

NamedParameterJdbcTemplateTest.java


package com.answersz.springJdbcTemplate;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class NamedParameterJdbcTemplateTest {

public static void main(String[] args) {

// Spring NamedParameterJdbcTemplate example

ApplicationContext context = new ClassPathXmlApplicationContext("namedParameterJdbcTemplateConfig.xml");

EmployeeDAO employeeDAO = (EmployeeDAO) context.getBean("employeeDAO");

Employee employee = new Employee();
employee.setEmployeeId(1002);
employee.setName("Robert");
employee.setFatherName("William");
employee.setAge(29);

employeeDAO.insertEmployee(employee);

employeeDAO.deleteEmployee(1001);
;

}
}


Copyright © 2014. All rights reserved.

Posted August 3, 2016 by answersz in category Spring

Leave a Comment

Your email address will not be published. Required fields are marked *

*