Spring JDBC : Named parameters , Calling Procedures

In the previous post we covered JdbcTemplate basics and executing few queries. One of the limitation of it was that you can only have ‘?’ placeholders for any parameterized query, and while substituting its values you have to pass an Object[] in the exact same order. Named parameters are a way in which you can identify these placeholders using names instead of ‘?’. To utilize this we need to use NamedParameterJdbcTemplate instead. Lets do an insert this time. MySpringDao.java:


package mynotes.springjdbc.morebasics;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Component;

@Component
public class MySpringDao {

private DataSource datasource;
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
public DataSource getDatasource() {
return datasource;
}
@Autowired
public void setDatasource(DataSource datasource) {
this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(datasource);
}

public void insertEmployee(Employee employee){
String sql="INSERT INTO EMPLOYEE (ID,NAME,PHONE) values (:id,:name,:phone)";
SqlParameterSource sqlParameterSource=new MapSqlParameterSource("id", employee.getId())
.addValue("name", employee.getName())
.addValue("phone", employee.getPhoneNumber());

namedParameterJdbcTemplate.update(sql, sqlParameterSource);
}

}

Notice the 3 named parameters we have in the sql query. In order to subtitue those with actual values, we need to use MapSqlParameterSource class and then pass in both sql statement and the mapped object to the update method.

Your Employee and spring.xml remains the same as previous post:


package mynotes.springjdbc.basics;

public class Employee{

private int id;
private String name;
private String phoneNumber;

@Override
public String toString() {
return ("Id=>" + this.id + " Name=>" + this.name+ " PhoneNumber=>"
+ this.phoneNumber);
}

public int getId() {
return id;
}

public void setId(int id) {
this.id = id;
}

public String getName() {
return name;
}

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

public void setPhoneNumber(String phoneNumber) {
this.phoneNumber = phoneNumber;
}

}


<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-3.2.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context-3.2.xsd">

<context:annotation-config />
<context:component-scan base-package="mynotes.springjdbc.morebasics" />

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

</beans>

App.java:


package mynotes.springjdbc.morebasics;

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

public class App {

public static void main(String[] args) {
ApplicationContext context=new ClassPathXmlApplicationContext("/mynotes/springjdbc/morebasics/spring.xml");
MySpringDao mySpringDao= context.getBean("mySpringDao",MySpringDao.class);
Employee employee=new Employee();
employee.setId(6);
employee.setName("Jason");
employee.setPhoneNumber("987654");
mySpringDao.insertEmployee(employee);
}
}

Running this will insert the data in DB.

In order to use both ‘?’ and ‘:namedParams’ you can use SimpleJdbcTemplate. This is a new addition and use java 5 implementation like var args etc. Since Spring 3.1 in favor of JdbcTemplate and NamedParameterJdbcTemplate this has been deprecated. The JdbcTemplate and NamedParameterJdbcTemplate now provide all the functionality of the SimpleJdbcTemplate.

Suppose you have many DaoImpl classes. The ‘dataSource’ and ‘jdbcTemplate’ or other template member variable needs to be seeted again and again. You might want to have  a common place to set this. Spring provides JdbcDaoSupport  classes  for the same. Lets see by an example.


package mynotes.springjdbc.morebasics;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class MySpringDaoNew extends JdbcDaoSupport {

//nothing here

public int getEmployeeCount(){
String sql="Select count(*) from Employee";
return this.getJdbcTemplate().queryForInt(sql);
}

}

See, no datasource or template initialization. You however still need to tell about datasource. That can be done in spring.xml like following. Just add this :


<bean id="mySpringDaoNew" class="mynotes.springjdbc.morebasics.MySpringDaoNew">
<property name="dataSource" ref="dataSource"></property>
</bean>

Calling Stored Procedures

Lets create a procedure first. I am using MySql DB and created following stored procedure which takes employeeId and returns corresponding employee’s name and phone using OUT parameters.


DELIMITER $$

CREATE  PROCEDURE `GetEmployeeDetailsById`(
IN in_id INTEGER,
OUT out_name VARCHAR(25),
OUT out_phone VARCHAR(15)

)
BEGIN

SELECT NAME, PHONE
INTO out_name, out_phone
FROM Employee where id = in_id;

END

Test the above procedure by running following query in sql window. (javatest is my schema name)


CALL `javatest`.`GetEmployeeDetailsById`(1,@name,@phone);
select @name,@phone;

Lets create an Interface


package mynotes.springjdbc.storedprocedure;

public interface EmployeeDaoInterface {

public Employee getDetailsById(int id);

}

Using SimpleJdbcCall


package mynotes.springjdbc.storedprocedure;

import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.stereotype.Component;

@Component
public class EmployeeDaoSimpleJdbcCall implements EmployeeDaoInterface {

private DataSource dataSource;
private SimpleJdbcCall simpleJdbcCall;

@Autowired
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
this.simpleJdbcCall = new SimpleJdbcCall(dataSource)
.withProcedureName("GetEmployeeDetailsById");
}

public Employee getDetailsById(int id) {
SqlParameterSource in = new MapSqlParameterSource().addValue("in_id",id);
Map<String, Object> out = simpleJdbcCall.execute(in);
Employee employee=new Employee();
employee.setId(id);
employee.setName((String) out.get("out_name"));
employee.setPhoneNumber((String) out.get("out_phone"));
return employee;
}

}

Notice the execution of the call involves creating an SqlParameterSource  containing the IN parameter. It’s important to match the name provided for the input value with that of the parameter name declared in the stored procedure. The execute method takes the IN parameters and returns a Map containing any out parameters keyed by the name as specified in the stored procedure.

Using StoredProcedure


package mynotes.springjdbc.storedprocedure;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;
@Component
public class EmployeeDaoStoredProcedure implements EmployeeDaoInterface {

private JdbcTemplate jdbcTemplate;
private EmployeeSP employeeSP;
@Autowired
public void setDataSource(DataSource source) {
this.jdbcTemplate = new JdbcTemplate(source);
this.employeeSP = new EmployeeSP(jdbcTemplate.getDataSource());
}

public Employee getDetailsById(int id) {
return this.employeeSP.getDetailsById(id);
}

}


package mynotes.springjdbc.storedprocedure;

import java.sql.Types;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.core.SqlOutParameter;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.object.StoredProcedure;

public class EmployeeSP extends StoredProcedure {

private static final String proc_name = "GetEmployeeDetailsById";

public EmployeeSP(DataSource datasource) {
super(datasource, proc_name);
declareParameter(new SqlParameter("in_id", Types.INTEGER));
declareParameter(new SqlOutParameter("out_name", Types.VARCHAR));
declareParameter(new SqlOutParameter("out_phone", Types.VARCHAR));
compile();
}

public Employee getDetailsById(int id){
Map<String,Object> results = super.execute(id);
Employee employee=new Employee();
employee.setId(id);
employee.setName((String) results.get("out_name"));
employee.setPhoneNumber((String) results.get("out_phone"));
return employee;
}

}

In this example, we have extended abstract class StoredProcedure in our class called, EmployeeSP. Notice the  constructor of EmployeeSP, it calls super class constructor and passes datasource and name of database stored procedure. We have also declared 3 stored procedure parameters, one is IN parameter id, and other 2 are  OUT parameter. Input to stored procedure is passed using IN parameter, and output from stored procedure is read using OUT parameter. The execute() methods, which can be invoked to call stored procedure and get result. It return result as Map, where key is OUT parameter, and value is result of stored procedure.
EmployeeDaoStoredProcedure just pass on the datasource to EmployeeSP class and calls for result. Change the component-scan to base-package=”mynotes.springjdbc.storedprocedure” in spring.xml so that we can get beans

Testing the above 2 appraoches. App.java


package mynotes.springjdbc.storedprocedure;

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

public class App {

public static void main(String[] args) {
ApplicationContext context=new ClassPathXmlApplicationContext("/mynotes/springjdbc/storedprocedure/spring.xml");
EmployeeDaoInterface empDao=context.getBean("employeeDaoSimpleJdbcCall",EmployeeDaoSimpleJdbcCall.class);
Employee employee=empDao.getDetailsById(2);
System.out.println(employee);
empDao=context.getBean("employeeDaoStoredProcedure",EmployeeDaoStoredProcedure.class);
employee=empDao.getDetailsById(1);
System.out.println(employee);
}
}

Output:


Id=>2 Name=>Jane PhoneNumber=>234567
Id=>1 Name=>Jake PhoneNumber=>123456

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: