Spring Executing Update Statements

This post is about executing update statements in sql in Spring, this is pretty straight forward the jdbcTemplate have a method called update we can use those to run sql level update of our database

we already have a sample data in Offers table in database

jdbc.properties

</p>
jdbc.username = root
jdbc.password = letmein
jdbc.driver = com.mysql.jdbc.Driver
jdbc.url = jdbc:mysql://localhost:3306/Offers
<p style="text-align: justify;">

Offers.java

</p>
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 + "]";
}

}
<p style="text-align: justify;">

beans.xml

</p>
<?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>
<p style="text-align: justify;">

OffersDao.java

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

//here we are using the delete method for updating the delete query in sql.

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

});
}
}
<p style="text-align: justify;">

App.java

</p>
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 {

offersDao.delete(2);

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

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

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=3, name=Sue, email=sue@nowhereatall.com, text=PHP coding]

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 *