Reading Notes for Spring 3 Core Components Tutorial Part V (Chapter 20)

This post is transferred from my old Hexo blog site, created on 2014.

This is a reading note from Spring Framework 3.1 Tutorial pdf, created by tutorialspoint. This pdf contains information for Spring 3 Core Basics, which is very useful for understanding defination and practive in Spring programming.

This note includes Chapter 20 in tutorial.

Spring JDBC Framework

Spring JDBC Framework takes care of all the low-level details starting from opening the connection, prepare and execute the SQL statement, process exceptions, handing transactions and finally close the connection.

JDBCTemplate is the most popular approach of using Spring JDBC framework.

JDBC Template Class

The JdbcTemplate class executes SQL 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.

Since instance of JdbcTemplate classes are threadsafe once it is configured, you can just configure one instance of JdbcTemplate, then inject it to different DAOs.

A comment practice is to configure a DataSource in your spring configuration file, then inject that bean to your DAO classes. For example:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"> <!--MySQL here as an example -->
    <property name="url" value="jdbc:mysql://localhost:3306/TEST"/>
    <property name="username" value="root"/>
    <property name="password" value="password"/>
    <!--You need URL, username and password -->
</bean>

Executing SQL Statements

DAO means Data Access Object. DAO often implements an interface, which provides ablity to read/write data from database. Other parts of application access DAO.

The way to achieve CRUD(Create, Read, Update and Delete) operation is (Assume SQL ia a SQL statement):

Query:

• Query for Integer: jdbcTemplateObject.queryForInt(SQL);

• Query for Long: jdbcTemplateObject.queryForLong(SQL);

• Query for Int, with a bind variable: jdbcTemplateObject.queryForInt(SQL, new Object[]{10});

• Query for String, with a bind variable: jdbcTemplateObject.queryForInt(SQL, new Object[]{10}, String.class);

• Query with ObjectMapper. This ObejctMapper will be returned after query finishes:

//Assume we want a Studnet object being returned
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.setName(rs.getSring("name"));
        student.setId(rs.getInt("id"));
        //Just apply these to setters
        return Student;
    }
}

• Query with ObjectMapper, return multiple objects:

List<Student> students = jdbcTemplateObject.query(SQL,new StudentMapper());
//StudentMapper class is the same as example above

Insert: SQL Statement: insert into Student(name,age) values (?,?). JDBCTemplate: jdbcTemplateObject.update(SQL, new Object[]{"Tim", 11});. If you want to insert more, just add more field and and key/value pair to SQL statement and JDBCTemplate.

Update: SQL Statement: update Student set name=? where id=?. JDBCTemplate: jdbcTemplateObject.update(SLQ, new Object[]{"Time",1});

Delete: SQL Statement: delete Student where id=?. JDBCTemplate: jdbcTemplateObject.update(SQL, new Object[]{10});

Create: SQL Statement: CREATE TABLE STUDENT( + ID INT NOT NULL AUTO_INCREMENT, etc. JDBCTemplate: jdbcTemplateObject.execute(SQL);

Example:

XML:

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver"> <!--MySQL here as an example -->
    <property name="url" value="jdbc:mysql://localhost:3306/TEST"/>
    <property name="username" value="root"/>
    <property name="password" value="password"/>
    <!--You need URL, username and password -->
</bean>

<bean id="studentDAOImpl" class="com.sample.StudentDAOImpl">
    <property name="datSource" ref="dataSource"/>
</bean>

JAVA:

//Assume we have a Student class with getter and setter for id, name and age attribute.

public class StudentMapper implements RowMapper<Student>{
    public Student mapRow(ResultSet rs, int rowNum) throws SQLException{
        Student student = new Student();
        student.setName(rs.getSring("name"));
        student.setId(rs.getInt("id"));
        student.setAge(rs.getInt("age"));
        return Student;
    }
    }

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

public interface StudentDAO{
    //Initialize database resource. i.e. connection
    public void setDataSource(DataSource ds);

    //Create Record
    public void create(String name, Integer age);

    //Get One Record from Id
    public Student getStudent(Integer id);

    //Get All Records
    public List<Student> listStudents();

    //Delete A Record from Id
    public void delete(Integer id)();

    //Update a Record from Id
    public void update(Integer id, Integer age)();
}

import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

//This class is the JDBCTemplate class, which is used to implement DAO interface above

publc class StudentDAOImpl implements StudentDAO{
    private DataSource dataSource; //we define this in beans.xml
    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);
        return;
    }
    public Student getStudent(Integer id){
        //Same as update example above
    }
    public List<Student> listStudents(){
        //Same as query example above
    }
    public void delete(Integer id)(){
        //Same as delete example above;
    }
    public void update(Integer id, Integer age)(){
        //Same as udpat example above;
    }
}

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

        StudentDAOImpl studentDaoImpl = (StudentDAOImpl) context.getBeans("studentDAOImpl");
        //Note we don't need to get dataSource bean. Spring does that automatically

        studentDaoImpl.create("Tim", 1);
        //You can now use DAO operation
    }
}

There are other JDBC framework prepared by Spring, such as NamedParameterJdbcTemplate and SimpleJdbcTemplate classes.

Stored Procedure

SimpleJdbcCall class is used to call a stored procedure with IN and OUT parameters.

Example:

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;

//bean.xml, Student.java, StudentMapper.java and StudentDAO.java are the same as example above

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

    public void setDataSource(DataSource dataSource){
        this.dataSource = dataSource;
        this.jdbcCall = new SimpleJdbcCall(dataSource).withProcedureName("Procedure_Name");
        //This is the way to call the stored procedure
    }
    public void create(String name, Integer age){
        JdbcTemplate jdbcTemplateObject = new JdbcTemplate(dataSource);
        String SQL = "insert into Student (name,age) values (?,?)";
        //Since we don't have a JdbcTemplate attribute now, we need to create new one here
        jdbcTemplateObject.update(SQL, name, age);
    }
    public Student getStudent(Integer id){
        SqlParameterSource in = new MapSqlParameterSource().addValue("in_id", id);
        //Add value parameter for stored procedure
        Map<String, Object> out = jdbcCall.execute(in);

        Student student = new Student();
        student.setId(id);
        student.setName((String) out.get("out_name")); //Add stored procedure value to setter
        student.setAge((Integer) out.get("out_age"));
        return Student;
    }
    //listStudents() is the same as example above
    //Test class is the same as example above, and you should get same result
}