tumblr counter

Spring Jdbc Template

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

  1. JDK 6
  2. Eclipse 3.7
  3. Spring

Following jar must be in classpath

  1. org.springframework.asm-3.0.6.RELEASE.jar
  2. org.springframework.beans-3.0.6.RELEASE.jar
  3. org.springframework.context-3.0.6.RELEASE.jar
  4. org.springframework.context.support-3.0.6.RELEASE.jar
  5. org.springframework.core-3.0.6.RELEASE.jar
  6. org.springframework.expression-3.0.6.RELEASE.jar
  7. mysql-connector-java-5.1.18-bin.jar
  8. org.springframework.jdbc-3.0.6.RELEASE.jar
  9. org.springframework.transaction-3.0.6.RELEASE.jar
  10. antlr-2.7.7.jar
  11. 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();
 
}
}



Spring Jdbc Template Example Spring Jdbc Template For Database Access Simple Spring JDBC Template Example Configuring Spring Jdbc Template

You might also like following tutorials :

Java Tutorial Apache CXF Tutorial Hibernate Tutorial Spring Tutorial Struts Tutorial
2 Comments on "Spring Jdbc Template"
30.07.2012 18:57:37 soumya
how can we do CRUD using jsp?? can anybody help me out?
31.07.2012 17:15:58 admin
It is always better to use any MVC framework rather than pure jsp page