Spring JdbcTemplate Example to Insert, Update

The following example illustrates using JdbcTemplate in Spring JDBC. This is an introductory example to the Spring JDBC module.

Theme of the application

The theme of the application is to illustrate the JdbcTemplate class. This class is the heart of the Spring JDBC module because it contains methods with which you will perform insert, update, delete, select operations.

In this example, we will insert a student record, update record details. We will have a bean class called Student that will have three columns sno, sname and age. The table student will also contain sno, sname and age columns.

In this example we will be using Oracle 11g Database Release 2 and 4th driver.

Brief view of JdbcTemplate

The JdbcTemplate class' constructor takes a datasource object. A datasource points to a database. It is used to get connection to the database. DriverManagerDataSource is an implementation of javax.sql.DataSource that takes 4 properties namely

1. driverClassName - Name of the driver class used to connect to database. Here, Type 4 driver - oracle.jdbc.driver.OracleDriver
2. url - URL to connect to the database.
3. username
4. password

Now, the JdbcTemplate knows the database it has to connect to. Using the methods in this class, we will perform database operations.

Create a project in eclipse

1. File -> New -> Project -> Java Project
2. Give the project name spring49 and click Finish 
3. Now, the project is created.
4. Under the Project Explorer (in the sidebar) you will see spring49. If you aren't able to see Project Explorer, go to Window menu -> Show view -> Project Explorer.
5. Right click on the spring49 and click Properties
6. On the left side, click on Java Build Path.
7. Select the Libraries tab and then click on Add External Jars
8. Now, add these jar files starting with: spring-core, spring-beans, spring-context, spring-aop, spring-jdbc, spring-tx, spring-expression, ojdbc6.jar or ojbdc14.jar, commons-logging
9. Click on OK and then you are read

To create a class

In the Project Explorer, navigate to spring49 and right click on spring49. Then go to New -> Class and give the name of the class as defined below.

 

To create xml file

In the Project Explorer, navigate to spring49 and right click on src under spring49. Then go to New -> Other. Type xml file in the box and hit enter and give the name jdbcContext.xml

 

jdbcContext.xml

This file contains two bean definitions. One pointing to the datasource object that contains details about database connection and the other is the dao class.

<beans     xmlns="http://www.springframework.org/schema/beans"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:jdbc="http://www.springframework.org/schema/jdbc"
        xsi:schemaLocation="http://www.springframework.org/schema/beans
                            http://www.springframework.org/schema/beans/spring-beans-3.2.xsd
                            http://www.springframework.org/schema/jdbc
                            http://www.springframework.org/schema/jdbc/spring-jdbc-3.2.xsd">

        <!-- Create datasource and give connection properties -->
        <bean id="datasource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
            <property name="url" value="jdbc:oracle:thin:@localhost:1521:xe"/>
            <property name="username" value="scott"/>
            <property name="password" value="tiger"/>
        </bean>

        <!-- Create the dao object and pass the datasource to it -->
        <bean id="studentDao" class="spring49.StudentDao">
            <property name="datasource" ref="datasource"/>
        </bean>

</beans>

Student.java - Bean

This bean class presents the table student in the database.

package spring49;

public class Student
{
private int sno;
private String sname;
private int age;

    public Student()
    {

    }

    public Student(int sno,String sname,int age)
    {
        this.sno=sno;
        this.sname=sname;
        this.age=age;
    }

    public void setSno(int sno)
    {
        this.sno=sno;
    }

    public void setSname(String sname)
    {
        this.sname=sname;
    }

    public void setAge(int age)
    {
        this.age=age;
    }

    public int getSno()
    {
        return sno;
    }

    public String getSname()
    {
        return sname;
    }

    public int getAge()
    {
        return age;
    }

    public String toString()
    {
        return sno+" "+sname+" "+age;
    }
}

StudentDao.java - Operations done here

This is the heart class because the operations are done here.

package spring49;

import javax.sql.*;
import org.springframework.jdbc.core.*;

public class StudentDao
{
private DataSource datasource;
private JdbcTemplate jdbcTemplate;
private String insertSql,updateSql;

    {
        insertSql="insert into student values(?,?,?)";
        updateSql="update student set sname=?,age=?, where sno=?";
    }

    public void setDatasource(DataSource datasource)
    {
        this.datasource=datasource;
        jdbcTemplate=new JdbcTemplate(datasource);
    }

    public void insert(Student st)
    {
        jdbcTemplate.update(insertSql,new Object[]{st.getSno(),st.getSname(),st.getAge()});
    }

    public void update(Student st)
    {
        jdbcTemplate.update(updateSql,new Object[]{st.getSname(),st.getAge(),st.getSno()});
    }

} 

This class takes in datasource with the help of which it will create a JdbcTemplate object. This JdbcTemplate object is used to perform database operations.

insert(Student st) This method inserts the given student object into the database. For this, we will use the update() method which takes two parameters. The first being the SQL (the command) and the next is the parameters with which the question marks should be replaced with (in order).

Note that, the statement is similar to that as you have seen in PreparedStatement where the question marks are replaced with the corresponding arguments. The same applies here.

update(Student st) This is also the same as above, but the command is changed to updateSql and the order of the question marks is different, so is the order of the elements in the Object[].

Instead of using setInt(), setString() etc, this way is more precise. So this is an advantage with JdbcTemplate over PreparedStatement.

SpringPrg.java - Main class

 package spring49;

import org.springframework.context.support.GenericXmlApplicationContext;

public class SpringPrg
{
    public static void main(String args[])
    {
        GenericXmlApplicationContext gc=new GenericXmlApplicationContext("classpath:jdbcContext.xml");

        StudentDao st=(StudentDao)gc.getBean("studentDao");

        // To insert data
        st.insert(new Student(101,"Rama",20));
        st.insert(new Student(102,"Siva",22));
    }
}