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

<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/TEST"/>
  <property name="username" value="root"/>
  <property name="password" value="password"/>
</bean>

String SQL = "select count(*) from Student";
int rowCount = jdbcTemplateObject.queryForInt( SQL );

String SQL = "select count(*) from Student";
long rowCount = jdbcTemplateObject.queryForLong( SQL );

String SQL = "select age from Student where id = ?";
int age = jdbcTemplateObject.queryForInt(SQL, new Object[]{10});

String SQL = "select name from Student where id = ?";
String name = jdbcTemplateObject.queryForObject(SQL, new Object[]{10}, String.class);

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

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;
  }
}

String SQL = "select * from Student";
List<Student> students = jdbcTemplateObject.query(SQL,
             new StudentMapper());

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;
  }
}

String SQL = "insert into Student (name, age) values (?, ?)";
jdbcTemplateObject.update( SQL, new Object[]{"Zara", 11} );

String SQL = "update Student set name = ? where id = ?";
jdbcTemplateObject.update( SQL, new Object[]{"Zara", 10} );

String SQL = "delete Student where id = ?";
jdbcTemplateObject.update( SQL, new Object[]{20} );

String SQL = "CREATE TABLE Student( " +
  "ID  INT NOT NULL AUTO_INCREMENT, " +
  "NAME VARCHAR(20) NOT NULL, " +
  "AGE INT NOT NULL, " +
  "PRIMARY KEY (ID));"

jdbcTemplateObject.execute( SQL );

DELIMITER $$

DROP PROCEDURE IF EXISTS `TEST`.`getRecord` $$
CREATE PROCEDURE `TEST`.`getRecord` (
IN in_id INTEGER,
OUT out_name VARCHAR(20),
OUT out_age INTEGER)
BEGIN
  SELECT name, age
  INTO out_name, out_age
  FROM Student where id = in_id;
END $$

DELIMITER ;

package com.yiibai;

import java.util.List;
import javax.sql.DataSource;

public interface StudentDAO {
  /** 
  * This is the method to be used to initialize
  * database resources ie. connection.
  */
  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();

}

package com.yiibai;

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;
  }
}

package com.yiibai;

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

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;
  }
}

package com.yiibai;

import java.util.Map;

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

public class StudentJDBCTemplate implements StudentDAO {
  private DataSource dataSource;
  private SimpleJdbcCall jdbcCall;

  public void setDataSource(DataSource dataSource) {
   this.dataSource = dataSource;
   this.jdbcCall = new SimpleJdbcCall(dataSource).
            withProcedureName("getRecord");
  }

  public void create(String name, Integer age) {
   JdbcTemplate jdbcTemplateObject = new JdbcTemplate(dataSource);
   String SQL = "insert into Student (name, age) values (?, ?)";

   jdbcTemplateObject.update( SQL, name, age);
   System.out.println("Created Record Name = " + name + " Age = " + age);
   return;
  }

  public Student getStudent(Integer id) {
   SqlParameterSource in = new MapSqlParameterSource().
               addValue("in_id", id);
   Map<String, Object> out = jdbcCall.execute(in);

   Student student = new Student();
   student.setId(id);
   student.setName((String) out.get("out_name"));
   student.setAge((Integer) out.get("out_age"));

   return student;
  }

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

}

package com.yiibai;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.yiibai.StudentJDBCTemplate;

public class MainApp {
  public static void main(String[] args) {
   ApplicationContext context = 
       new ClassPathXmlApplicationContext("Beans.xml");

   StudentJDBCTemplate studentJDBCTemplate = 
   (StudentJDBCTemplate)context.getBean("studentJDBCTemplate");
   
   System.out.println("------Records Creation--------" );
   studentJDBCTemplate.create("Zara", 11);
   studentJDBCTemplate.create("Nuha", 2);
   studentJDBCTemplate.create("Ayan", 15);

   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("----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());
  
  }
}

<?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-3.0.xsd ">

  <!-- Initialization for data source -->
  <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/TEST"/>
   <property name="username" value="root"/>
   <property name="password" value="password"/>
  </bean>

  <!-- Definition for studentJDBCTemplate bean -->
  <bean id="studentJDBCTemplate" 
   class="com.yiibai.StudentJDBCTemplate">
   <property name="dataSource" ref="dataSource" />  
  </bean>
   
</beans>

------Records Creation--------
Created Record Name = Zara Age = 11
Created Record Name = Nuha Age = 2
Created Record Name = Ayan Age = 15
------Listing Multiple Records--------
ID : 1, Name : Zara, Age : 11
ID : 2, Name : Nuha, Age : 2
ID : 3, Name : Ayan, Age : 15
----Listing Record with ID = 2 -----
ID : 2, Name : Nuha, Age : 2