Spring Create and Getting Placeholders from Bean Properties

Now we are going to see How to give values as properties to sql in spring, using BeanPropertySqlParameterSource and update method. Lets see an example of that.

Offers.java


package com.sourceforopensource.pojos;

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

public Offer() {

}

public Offer(String name, String email, String text) {
this.name = name;
this.email = email;
this.text = text;
}

public Offer(int id, String name, String email, String text) {
this.id = id;
this.name = name;
this.email = email;
this.text = 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 = admin
jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/sfos

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.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Component;

@Component("offersDao")
public class OffersDAO {

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;
}
});
}

public boolean create(Offer offer) {

//this BeanPropertySqlParameterSource will take the offer object and take the object //variables to inject into the update method.
BeanPropertySqlParameterSource params = new BeanPropertySqlParameterSource(offer);

return jdbc.update("insert into offers (name, text, email) values (:name, :text, :email)", params) == 1;
}

public boolean delete(int id) {
MapSqlParameterSource params = new MapSqlParameterSource("id", id);

return jdbc.update("delete from offers where id=:id", params) == 1;
}

public Offer getOffer(int id) {

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

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);
}


}
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=3, name=Sue, email=sue@nowhereatall.com, text=PHP coding]
Offer [id=4, name=krishna, email=balakrishna.pendyala12@gmail.com, text=this is my new offer]

Note: if we run the program again the id will be automatically incremented.

 

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 *