Spring Using Named Parameters

 

In this post we are going to see how to query with dynamic input for specific rows, rather than query the entire database.

Offer.java


package com.sourceforopensource.pojos;

public class Offer {
private int id;
private String name;
private String email;
private String text;

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 getEmail() {
return email;
}

public void setEmail(String email) {
this.email = email;
}

public String getText() {
return text;
}

public void setText(String text) {
this.text = text;
}

@Override
public String toString() {
return "Offer [id=" + id + ", name=" + name + ", email=" + email
+ ", text=" + text + "]";
}

}

jdbc.properties


jdbc.username = root
jdbc.password = letmein
jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/Offers



beans.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:p="http://www.springframework.org/schema/p"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.2.xsd">

<context:component-scan base-package="com.sourceforopensource.pojos">
</context:component-scan>
<context:property-placeholder
location="com/sourceforopensource/props/jdbc.properties" />

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
destroy-method="close">

<property name="driverClassName" value="${jdbc.driver}"></property>
<property name="url" value="${jdbc.url}"></property>
<property name="password" value="${jdbc.password}"></property>
<property name="username" value="${jdbc.username}"></property>
</bean>
</beans>

OffersDao.java


package com.sourceforopensource.pojos;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import javax.sql.DataSource;

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

@Component("offersDao")
public class Offers DAO {

//here we have changed jdbcTemplate  class to NamedParameterJdbcTemplate class where //this special class have separate methods for querying with parameters

private NamedParameterJdbcTemplate jdbc;

@Autowired
public void setDataSource(DataSource jdbc) {
this.jdbc = new NamedParameterJdbcTemplate(jdbc);
}

public List<Offer> getOffers() {

return jdbc.query("select * from offers", new RowMapper<Offer>() {

public Offer mapRow(ResultSet rs, int rowNum) throws SQLException {
Offer offer = new Offer();

offer.setId(rs.getInt("id"));
offer.setName(rs.getString("name"));
offer.setText(rs.getString("text"));
offer.setEmail(rs.getString("email"));

return offer;
}

});
}

//here we are giving a dynamic input which is id. which is a named parameter.

public Offer getOffer(int id) {

//This MapSqlParameterSource will map the input to the queryForObject

MapSqlParameterSource params = new MapSqlParameterSource();
params.addValue("id", id);

//here we are using placeholder value, we can use multiple placeholder values using add //value method
//we are also passing the value to the queryForObject method here by specifying params here.
return jdbc.queryForObject("select * from offers where id=:id", params,
new RowMapper<Offer>() {

public Offer mapRow(ResultSet rs, int rowNum)
throws SQLException {
Offer offer = new Offer();

offer.setId(rs.getInt("id"));
offer.setName(rs.getString("name"));
offer.setText(rs.getString("text"));
offer.setEmail(rs.getString("email"));

return offer;
}

});
}

}

App.java


package com.sourceforopensource.pojos;

import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.CannotGetJdbcConnectionException;

public class App {

public static void main(String[] args) {

ApplicationContext context = new ClassPathXmlApplicationContext(
"com/sourceforopensource/beans/beans.xml");

OffersDAO offersDao = (OffersDAO) context.getBean("offersDao");

try {
List<Offer> offers = offersDao.getOffers();

for (Offer offer : offers) {
System.out.println(offer);
}

//querying to the OffersDao
Offer offer = offersDao.getOffer(2);

System.out.println("Should be Mike: " + offer);
}
catch(CannotGetJdbcConnectionException ex) {
System.out.println("Unable to connect to database.");
}
catch (DataAccessException ex) {
System.out.println(ex.getMessage());
System.out.println(ex.getClass());
}

((ClassPathXmlApplicationContext) context).close();
}

}

output:

Offer [id=1, name=Bob, email=bob@nowhereatall.com, text=I will write Java for you]
Offer [id=2, name=Mike, email=mike@nowhereatall.com, text=Web design, very cheap]
Offer [id=3, name=Sue, email=sue@nowhereatall.com, text=PHP coding]

//this is the output of named parameter
Should be Mike: Offer [id=2, name=Mike, email=mike@nowhereatall.com, text=Web design, very cheap]

Balakrishna Pendyala

Author: Balakrishna Pendyala

Balakrishna is a Software Engineer working in Media Streaming Domain, and also worked on Banking and Health Care domains. His areas of interest include Object Oriented Design, SOLID Design principles, Restful Web Services and Open Source softwares including Spring, Spring Boot, and Hibernate.

If you found an error, highlight it and press Shift + Enter or click here to inform us.

Leave a Reply

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