Spring-Querying-Using-Jdbc-Template

In this post we gonna see Querying database in Spring using jdbcTemplate.

Before starting every thing just add these dependencies to your maven project.

pom.xml


<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
 <modelVersion>4.0.0</modelVersion>
 <groupId>com.sourceforopensource.pojos</groupId>
 <artifactId>Spring-Querying-Using-Jdbc-Template</artifactId>
 <version>0.0.1-SNAPSHOT</version>
 <dependencies>
 <dependency>
 <groupId>org.springframework</groupId>
 <artifactId>spring-core</artifactId>
 <version>3.2.3.RELEASE</version>
 </dependency>
 <dependency>
 <groupId>org.springframework</groupId>
 <artifactId>spring-beans</artifactId>
 <version>3.2.3.RELEASE</version>
 </dependency>
 <dependency>
 <groupId>org.springframework</groupId>
 <artifactId>spring-context</artifactId>
 <version>3.2.3.RELEASE</version>
 </dependency>
 <dependency>
 <groupId>javax.inject</groupId>
 <artifactId>javax.inject</artifactId>
 <version>1</version>
 </dependency>
 <dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <version>5.1.25</version>
 </dependency>
 <dependency>
 <groupId>commons-dbcp</groupId>
 <artifactId>commons-dbcp</artifactId>
 <version>1.4</version>
 </dependency>
 <dependency>
 <groupId>org.springframework</groupId>
 <artifactId>spring-jdbc</artifactId>
 <version>3.2.3.RELEASE</version>
 </dependency>
 </dependencies>
</project>

Now we have a create Offers table in backend which is stored in mysql database and we inserted some sample data.

Offers.sql


CREATE TABLE `offers` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) NOT NULL,
 `email` varchar(60) NOT NULL,
 `text` text NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `offers` VALUES (1,'Bob','bob@nowhereatall.com','I will write Java for you'),(2,'Mike','mike@nowhereatall.com','Web design, very cheap'),(3,'Sue','sue@nowhereatall.com','PHP coding');

Below we have an Offers.java which takes values and we will return toString() when anyone called object.

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

}


So, Now we are going to Configure Connection pooling with Apache DBCP. The concept of connection pooling is simple, what we do is we just configure the connections only once for application level, because Creating a new connection for each user can be time consuming (often requiring multiple seconds of clock time), in order to perform a database transaction that might take milliseconds. Opening a connection per user can be unfeasible in a publicly-hosted Internet application where the number of simultaneous users can be very large. Accordingly, developers often wish to share a “pool” of open connections between all of the application’s current users. The number of users actually performing a request at any given time is usually a very small percentage of the total number of active users, and during request processing is the only time that a database connection is required. The application itself logs into the DBMS, and handles any user account issues internally.

There are several Database Connection Pools already available, we use one here called Apache DBCP.

here below we just defines database connections in jdbc.properties file and then we define a datasource by using this properties file

jdbc.properties


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

web.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.stereotype.Component;

@Component("offersDao")
public class OffersDAO {
 
 private JdbcTemplate jdbc;
 
 @Autowired //here we are setting the complete datasource to jdbctemplate.
 public void setDataSource(DataSource jdbc) {
 this.jdbc = new JdbcTemplate(jdbc);
 }

 public List&lt;Offer&gt; getOffers() {

//here now we are using query method from jdbcTemplate, which it will return the
//list of Offer object.
 return jdbc.query("select * from offers", new RowMapper&lt;Offer&gt;() {

//here we are mapping the ResultSet to Offer object. 
//here it will return the single Offer
 public Offer mapRow(ResultSet rs, int rowNum) throws SQLException {
 Offer offer = new Offer(); // here we are mapping rows to offer object.
 
 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;

public class App {

 public static void main(String[] args) {
 
 ApplicationContext context = new ClassPathXmlApplicationContext("com/sourceforopensource/beans/beans.xml");
 
 OffersDAO offersDao = (OffersDAO)context.getBean("offersDao");
 
 List&lt;Offer&gt; offers = offersDao.getOffers();
 
//here we are iterating over the offers which it will return the toString of object
 for(Offer offer: offers) {
 System.out.println(offer);
 }
 
 ((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]

 

 

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 *