Spring Jdbc Template

By admin | December 11, 2011 | Updated : May 01, 2016 dao, jdbctemplate, spring, 2 Comments

Spring Jdbc Template Example explains step by step details of configuring Spring Jdbc Template

The Spring Framework is a free and open source framework using for the development of java enterprise applications

Spring framework consists of several different modules including Aspect Oriented Programming, Inversion Control, Model View Controller, Data Access, Transaction Management, Batch Processing, Remote Access & Spring JDBC Template will really ease the development effort of coding team.

SpringJdbcTemplate is a module available in spring core framework, which will help you to persist entities by using very little code, it simplifies the creation of database connection & closing connection etc.

In this example, we are injecting the DataSource by setter injection, if you need you can configure using spring configuration.

Required Libraries

You need to download

Following jar must be in classpath

org.springframework.asm-3.0.6.RELEASE.jar
org.springframework.beans-3.0.6.RELEASE.jar
org.springframework.context-3.0.6.RELEASE.jar
org.springframework.context.support-3.0.6.RELEASE.jar
org.springframework.core-3.0.6.RELEASE.jar
org.springframework.expression-3.0.6.RELEASE.jar
mysql-connector-java-5.1.18-bin.jar
org.springframework.jdbc-3.0.6.RELEASE.jar
org.springframework.transaction-3.0.6.RELEASE.jar
antlr-2.7.7.jar
commons-logging-1.1.1.jar

Create Database

CREATE TABLE Student(
		ID int unsigned default null auto_increment,
		FIRSTNAME VARCHAR(20) NOT NULL,
		LASTNAME VARCHAR(20) NOT NULL,
		PRIMARY KEY (ID)
	);

Create domain model

Create domain model named student. Here we are going to persist student object using JdbcTemplate

package com.javatips.domain;

public class Student {

private String firstName;
private String lastName;

public Student(String firstName, String lastName) {
    this.firstName = firstName;
    this.lastName = lastName;
}

public String getFirstName() {
    return firstName;
}

public String getLastName() {
    return lastName;
}

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

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

Create the Data Access Object (DAO)Interface

Create IDao for different database operations such as create,select,delete etc

package com.javatips;

import java.util.List;

import javax.sql.DataSource;

import com.javatips.domain.Student;

public interface IDao {

void setDataSource(DataSource ds);

void create(String firstName, String lastName);

List<Student> select(String firstname, String lastname);

List<Student> selectAll();

void deleteAll();

void delete(String firstName, String lastName);
}

Implement Data Access Object (DAO)Interface

Implement IDao for different database operations such as create,select,delete etc

package com.javatips;

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

import javax.sql.DataSource;

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

import com.javatips.domain.Student;

public class MySqlDao implements IDao {
private DataSource dataSource;

public void setDataSource(DataSource ds) {
    dataSource = ds;
}

public void create(String firstName, String lastName) {
    JdbcTemplate insert = new JdbcTemplate(dataSource);
    insert.update("INSERT INTO student (FIRSTNAME, LASTNAME) VALUES(?,?)",
        new Object[] { firstName, lastName });
}

public List<Student> select(String firstname, String lastname) {
    JdbcTemplate select = new JdbcTemplate(dataSource);
    return select.query("select FIRSTNAME, LASTNAME from student where FIRSTNAME = ? AND LASTNAME= ?",
            new Object[] { firstname, lastname },
            new RowMapper<Student>() {
              public Student mapRow(ResultSet resultSet,
                  int rowNum) throws SQLException {
                return new Student(resultSet
                    .getString("FIRSTNAME"), resultSet
                    .getString("LASTNAME"));
              }
            });
}

public List<Student> selectAll() {
    JdbcTemplate select = new JdbcTemplate(dataSource);
    return select.query("select FIRSTNAME, LASTNAME from student",
        new RowMapper<Student>() {
          public Student mapRow(ResultSet resultSet, int rowNum)
              throws SQLException {
            return new Student(resultSet.getString("FIRSTNAME"),
                resultSet.getString("LASTNAME"));
          }
        });
}

public void deleteAll() {
    JdbcTemplate delete = new JdbcTemplate(dataSource);
    delete.update("DELETE from student");
}

public void delete(String firstName, String lastName) {
    JdbcTemplate delete = new JdbcTemplate(dataSource);
    delete.update("DELETE from student where FIRSTNAME= ? AND LASTNAME = ?",
        new Object[] { firstName, lastName });
}

}

Test Program

package com.javatips;

import java.util.List;

import org.springframework.jdbc.datasource.DriverManagerDataSource;

import com.javatips.domain.Student;

public final class Main {

public static void main(String[] args) {
    MySqlDao dao = new MySqlDao();
    // Initialize the datasource, could /should be done of Spring configuration
    DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    dataSource.setUrl("jdbc:mysql://localhost/test");
    dataSource.setUsername("root");
    dataSource.setPassword("root");
    // Inject the datasource into the dao
    dao.setDataSource(dataSource);

    dao.create("Rockey", "CA");
    dao.create("Sony", "Decuth");
    dao.create("Lincy", "Maria");
    System.out.println("Select and list all student");
    List<Student> list = dao.selectAll();
    for (Student student : list) {
      System.out.print(student.getFirstName() + " ");
      System.out.println(student.getLastName());
    }
    //list students have name Sony, Decuth
    System.out.println("List students have name Sony, Decuth");
    list = dao.select("Sony", "Decuth");
    for (Student student : list) {
      System.out.print(student.getFirstName() + " ");
      System.out.println(student.getLastName());
    }

    // Clean-up
    dao.deleteAll();
}
}

You might also like following tutorials :

2 Responses to "Spring Jdbc Template"

soumya 2011-12-12 08:22:13.0

how can we do CRUD using jsp?? can anybody help me out?

admin 2011-12-13 08:22:13.0

It is always better to use any MVC framework rather than pure jsp page