SPRING JDBC TEMPLATE SAMPLE

JdbcTemplate Object

The JdbcTemplate object executes queries, update statements and stored procedure calls, performs iteration over "ResultSets" and extraction of returned parameter values. It also catches "JDBC" exceptions and translates them to the generic, more informative, exception hierarchy defined in the org.springframework.dao package.

Data Source Structure

CREATE TABLE Student(
   ID   INT NOT NULL AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE  INT NOT NULL,
   PRIMARY KEY (ID)
);

Java Objects 

• Student.java
• StudentDAO.java
• StudentJDBCTemplate.java
• StudentMapper.java

MySql Connection jar file :  http://dev.mysql.com/downloads/connector/j/3.1.html

Student.java

/**
 *
 */
package com.gnsmind.it.dao;

/**
 * @author M. Zafer
 *
 */
public class Student {

       private Integer age;
       private String name;
       private Integer id;

       public void setAge(Integer age) {
          this.age = age;
       }
       public Integer getAge() {
          return age;
       }

       public void setName(String name) {
          this.name = name;
       }
       public String getName() {
          return name;
       }

       public void setId(Integer id) {
          this.id = id;
       }
       public Integer getId() {
          return id;
       }
}

StudentDAO.java

 /**
 *
 */
package com.gnsmind.it.dao;

import java.util.List;
import javax.sql.DataSource;
/**
 * @author M.Zafer
 *
 */
public interface StudentDAO {

     /**
        * This is the method to be used to initialize
        * database resources ie. connection.
        * Data Base Services
        */
       public void setDataSource(DataSource ds);
       /**
        * This is the method to be used to create
        * a record in the Student table.
        */
       public void create(String name, Integer age);
       /**
        * This is the method to be used to list down
        * a record from the Student table corresponding
        * to a passed student id.
        */
       public Student getStudent(Integer id);
       /**
        * This is the method to be used to list down
        * all the records from the Student table.
        */
       public List<Student> listStudents();
       /**
        * This is the method to be used to delete
        * a record from the Student table corresponding
        * to a passed student id.
        */
       public void delete(Integer id);
       /**
        * This is the method to be used to update
        * a record into the Student table.
        */
       public void update(Integer id, Integer age);
} 

StudentJDBCTemplate.java

 /**
 *
 */
package com.gnsmind.it.dao;

import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
/**
 * @author M.Zafer
 *
 */
public class StudentJDBCTemplate implements StudentDAO {

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

       public void create(String name, Integer age) {
        
           String SQL = "insert into student (name, age) values (?, ?)";
        
           jdbcTemplateObject.update( SQL, name, age);
           System.out.println("Created Record Name = " + name + " Age = " + age);
       }

       public Student getStudent(Integer id) {
        
           String SQL = "select * from student where id = ?";
           Student student = jdbcTemplateObject.queryForObject(SQL, new Object[]{id}, new StudentMapper());
           return student;
       }

       public List<Student> listStudents() {
        
           String SQL = "select * from student";
           List <Student> students = jdbcTemplateObject.query(SQL, new StudentMapper());
           return students;
       }

       public void delete(Integer id){
        
           String SQL = "delete from student where id = ?";
           jdbcTemplateObject.update(SQL, id);
           System.out.println("Deleted Record with ID = " + id );
           return;
       }

       public void update(Integer id, Integer age){
        
           String SQL = "update student set age = ? where id = ?";
           jdbcTemplateObject.update(SQL, age, id);
           System.out.println("Updated Record with ID = " + id );
           return;
       }
}

StudentMapper.java

 /**
 *
 */
package com.gnsmind.it.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;

/**
 * @author M.Zafer
 *
 */
public class StudentMapper implements RowMapper<Student> {

    public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
        
        Student student = new Student();
        student.setId(rs.getInt("id"));
        student.setName(rs.getString("name"));
        student.setAge(rs.getInt("age"));
        return student;
    }
}

Beans.xml

 <!-- Definition for studentJDBCTemplate bean -->
   <bean id="studentJDBCTemplate" class="com.gnsmind.it.dao.StudentJDBCTemplate">
      <property name="dataSource"  ref="dataSource" />   
   </bean>
  
   <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
       <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
       <property name="url" value="jdbc:mysql://localhost:3306/konak"/>
       <property name="username" value="root"/>
       <property name="password" value=""/>
   </bean>

Main.java

/**
 *
 */
package com.gnsmind.it.main;



import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.annotation.AnnotationConfigApplicationContext;
import org.springframework.context.support.AbstractApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.gnsmind.it.dao.Student;
import com.gnsmind.it.dao.StudentJDBCTemplate;
import com.gnsmind.it.object.HelloIndia;
import com.gnsmind.it.object.HelloWorld;
import com.gnsmind.it.object.HelloWorldConfig;
import com.gnsmind.it.object.JavaCollection;
import com.gnsmind.it.object.SpringObject;
import com.gnsmind.it.object.TextEditor;

/**
 * @author M.Zafer
 *
 */
public class Main {

    /**
     * @param args
     */
    public static void main(String[] args) {

        String beansSource = "beans.xml";
        ApplicationContext context = new ClassPathXmlApplicationContext(beansSource);

        //JDBC TEMPLATE SAMPLE*******************************
        try {
           
            StudentJDBCTemplate studentJDBCTemplate = (StudentJDBCTemplate)context.getBean("studentJDBCTemplate");
            
             System.out.println("------Records Creation--------" );
             studentJDBCTemplate.create("Alex", 11);
             studentJDBCTemplate.create("Matthias", 2);
            
             System.out.println("------Listing Multiple Records--------" );
             List<Student> students = studentJDBCTemplate.listStudents();
            
             for (Student record : students) {
            
                 System.out.print("ID : " + record.getId() );
                 System.out.print(", Name : " + record.getName() );
                 System.out.println(", Age : " + record.getAge());
             }

             System.out.println("----Updating Record with ID = 2 -----" );
             studentJDBCTemplate.update(2, 20);

             System.out.println("----Listing Record with ID = 2 -----" );
             Student student = studentJDBCTemplate.getStudent(2);
             System.out.print("ID : " + student.getId() );
             System.out.print(", Name : " + student.getName() );
             System.out.println(", Age : " + student.getAge());
            
        } catch (Exception e) {
           
            System.out.println("JDBC Template Error : " + e.getMessage());
        }
        //******************************************************************************************************
        System.exit(0);

    }

}