Spring JDBC Template Example

Step by step guide to learn spring jdbctemplate example along with bean database configuration. Learn how to execute a select query using spring jdbc. Spring JDBC is an abstraction of core JDBC.There are lots of benefits to use Spring jdbc over core jdbc. Like Open the connection, Specify the statement, Prepare and execute the statement, Process any exception Handle transactions and Close the connection.

Step #1:- Create Database table Create "trn_person" table in mysql database using below sql table script.

CREATE TABLE  trn_person (
  'person_id' int(10) unsigned NOT NULL auto_increment,
  'first_name' varchar(45) NOT NULL,
  'last_name' varchar(45) NOT NULL, 
  'email' varchar(45) NOT NULL,
  PRIMARY KEY  (user_id)
);

“Spring Framework JDBC provides an abstraction over the core Jdbc”

Step #2:- Spring Database Configuration Create new "springtutorial-database.xml" file, which is used to configure database details.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
 
	<!--  This is Database Configuration -->
	<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName"><value>com.mysql.jdbc.Driver</value></property>
		<property name="url"><value>jdbc:mysql://localhost:3306/technicalkeeda</value></property>
		<property name="username"><value>root</value></property>
		<property name="password"><value></value></property>
	</bean>
		
</beans>

Create another file "springtutorial-beans.xml" which is used to define the bean definitions.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-2.5.xsd"> 
	<bean id="personDao" class="com.net.technicalkeeda.dao.PersonDaoImpl">	
		<property name="dataSource" ref="dataSource" />
	</bean>
		
</beans>

Create one more file "springtutorial-context.xml" which import above two files.

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans
       http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">
	<import resource="springtutorial-database.xml" />
	<import resource="springtutorial-beans.xml" />
	
</beans>

Step #3:- PersonDaoImpl.java findAll() method return collection of Person Objects. jdbcTemplate.query() method executes the select query and return person obejct which is mapped in PersonMapper class. PersonMapper implements the RowMapper interface and override the mapRow() method.

package com.net.technicalkeeda.dao;

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

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.net.technicalkeeda.bean.Person;
import com.net.technicalkeeda.interfaces.PersonDao;

public class PersonDaoImpl implements PersonDao {

	private DataSource dataSource;

	private JdbcTemplate jdbcTemplate;

	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
		this.jdbcTemplate = new JdbcTemplate(this.dataSource);
	}

	public Collection findAll() {
		return jdbcTemplate.query(
				"SELECT first_name, last_name, email FROM trn_employee",
				new PersonMapper());

	}
	
	// row mapper class
	private static final class PersonMapper implements RowMapper {

		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			Person person = new Person();
			person.setFirstName(rs.getString("first_name"));
			person.setLastName(rs.getString("last_name"));
			person.setEmail(rs.getString("email"));
			return person;
		}
	}

}

Step #3.1:- Person.java Pojo Class Simple pojo class contains firstName, lastName and email getter and setter methods. Also called as Value object class.

package com.net.technicalkeeda.bean;

public class Person {

	private String firstName;
	private String lastName;
	private String email;

	public Person() {

	}

	public String getFirstName() {
		return firstName;
	}

	public void setFirstName(String firstName) {
		this.firstName = firstName;
	}

	public String getLastName() {
		return lastName;
	}

	public void setLastName(String lastName) {
		this.lastName = lastName;
	}

	public String getEmail() {
		return email;
	}

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

}

Step #3.2:- PersonDao.java Interface PersonDao interface contains findAll() method. Which is implemented by PersonDaoImpl class.

package com.net.technicalkeeda.interfaces;

import java.util.Collection;
import java.util.List;

import com.net.technicalkeeda.bean.Person;
public interface PersonDao {
	public Collection findAll();
}

Step #4:- Finish Its time to test the example. Get the PersonDao object using application context factory. call the findAll() method to get the each employee record.

package com.net.technicalkeeda.controller;

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

import com.net.technicalkeeda.bean.Person;
import com.net.technicalkeeda.interfaces.PersonDao;

public class Test {

	public static void main(String[] args) {

		ApplicationContext context = new ClassPathXmlApplicationContext(
				"springtutorial-context.xml");
		PersonDao personDao = (PersonDao) context.getBean("personDao");

		for (Person person : personDao.findAll()) {
			System.out.println("First Name" + person.getFirstName());
			System.out.println("\nLast Name" + person.getLastName());
			System.out.println("\nEmail " + person.getEmail());
		}

	}

}