Connect to database using JdbcTemplate in Spring

Spring provides many ways to interact with a database and perform various operations on it.
One of the approach using DriverManagerDataSource was discussed in a previous post.

The above approach using DriverManagerDataSource is suitable for testing purposes and is not recommended for practical applications since it only takes care of establishing database connection.
Other steps such as retrieving Connection, creating Statement / PreparedStatement, closing Connection are to be done by the developer.

Why JdbcTemplate
dbcTemplate takes care of all management tasks such as creating Connection, retrieving it, creating Statement / PreparedStatement objects, executing queries, iterating over ResultSet and closing them behind the scenes.

Developer is only left with writing and executing database query and handling the results as required.
Thus, it removes a lot of redundant code required in the application and lets developer focus on the logic.
How to configure JdbcTemplate
JdbcTemplate requires an object of DataSource to communicate with the database.
Since the data source mentioned above is an interface, we must provide an instance of class which implements it.

Spring provides an implementation class which is DriverManagerDataSource.

This class is provided various parameters required to connect to a database such as its url, name, user name, password etc.
There are two ways to configure DriverManagerDataSource and hence JdbcTemplate.

1. As a bean in the spring configuration XML

<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:p="http://www.springframework.org/schema/p"
xmlns:util="http://www.springframework.org/schema/util" xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd  http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context-3.0.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util.xsd">

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

   <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
     <property name="dataSource" ref="dataSource"></property>
   </bean>
</beans>

If JdbcTemplate is configured as a bean using XML, then to get its object in the code,

A. Create a field of type JdbcTemplate and annotate it with @Autowired annotation, or
B. Get it by using ClassPathXmlApplicationContext.

2. Programmatic Configuration
Create an object of DriverManagerDataSource directly inside code and populate its fields with connection parameters as below.

After configuring DriverManagerDataSource, you can easily create an object of JdbcTemplate by passing the object of DriverManagerDataSource in its constructor as shown below.

  DriverManagerDataSource ds = new DriverManagerDataSource();
  ds.setDriverClassName("com.mysql.jdbc.Driver");
  ds.setUrl("jdbc:mysql://localhost:3306/codippa");
  ds.setUsername("root");
  ds.setPassword("root");
  JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);

Database Operations using JdbcTemplate
Now when we have configured JdbcTemplate in our application code, another question arises as to what all can I do with it, which database operations I can perform with it and how.

Well, everything that one needs to do using a database connection can be done with JdbcTemplate.

The database table we will be interacting with can be created using the following query :

Entity class corresponding to the above table is as below :

package com.codippa;

public class Student {
	
   private String studentId;
	
   private String studentName;
	
   private String yearLevel;


   public String getStudentId() {
      return studentId;
   }

   public void setStudentId(String studentId) {
      this.studentId = studentId;
   }

   public String getStudentName() {
      return studentName;
   }

   public void setStudentName(String studentName) {
      this.studentName = studentName;
   }

   public String getYearLevel() {
      return yearLevel;
   }

   public void setYearLevel(String yearLevel) {
      this.yearLevel = yearLevel;
   }
	
}

1. Fetching records from a table
JdbcTemplate’s queryForList method is used for fetching all records from a table.
This method takes a String as parameter which is the SQL query of the form Select * from tablename and returns a List of type java.util.Map<String, Object>.

Each list item represents a row of database table and is a map.
Each Map is a collection of key-value pair with key as column name and value as the value stored in that column.

public class Application {
   public static void main(String[] args) {
      DriverManagerDataSource ds = new DriverManagerDataSource();
      ds.setDriverClassName("com.mysql.jdbc.Driver");
      ds.setUrl("jdbc:mysql://localhost:3306/codippa");
      ds.setUsername("root");
      ds.setPassword("root");
      JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
      List<Map<String, Object>> allRecords = jdbcTemplate.
                            queryForList("select * from student");
   }
}

2. Fetching column value for row
Suppose we want to fetch the name of student whose studentId is 1.
Using JdbcTemplate, this can be done using its queryForObject method. This method takes two arguments :

an SQL query, and
the class type of the value returned.

public class Application {

   public static void main(String[] args) {
      DriverManagerDataSource ds = new DriverManagerDataSource();
      ds.setDriverClassName("com.mysql.jdbc.Driver");
      ds.setUrl("jdbc:mysql://localhost:3306/codippa");
      ds.setUsername("root");
      ds.setPassword("root");
      JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
      // provide appropriate query and class representing the type of value returned
      String studentName = jdbcTemplate.queryForObject("select studentName "+
                                   "from student where studentId=1", String.class);
   }
}

3. Fetching count of records in table
JdbcTemplate can be used to get the total number of records in a table using its queryForObject method.
SQL query to fetch the number of records in a table is Select count(*) from tablename;.

This query is executed by passing the query as a first argument to this method while the return type is the class of the type of result returned.
The result returned is an integer, hence the second argument is Integer.class.

public class Application {
   public static void main(String[] args) {
	DriverManagerDataSource ds = new DriverManagerDataSource();
	ds.setDriverClassName("com.mysql.jdbc.Driver");
	ds.setUrl("jdbc:mysql://localhost:3306/codippa");
	ds.setUsername("root");
	ds.setPassword("root");
	JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
	int totalRecords = jdbcTemplate.queryForObject("select count(*) from student",
                                        Integer.class);
   }
}

4. Fetching record in the form of its Entity class
Fetch a record from the database and get the result in the form of an object with its fields populated with the column values from the database.

In simpler terms, let’s say we fetch a student record with studentId 1 from the database and populate an object of Student class whose fields are populated with the column values of the student record.

The above task is accomplished using queryForObject method of JdbcTemplate. This method takes two arguments :
a String which is the SQL query used to fetch the records, and
an RowMapper.

This RowMapper maps a database record to an object.
It is an interface and has only one method mapRow which is supplied a ResultSet returned as a result of execution of SQL query.
It is inside this method that we need to convert the record from database to an actual object.

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class Application {

    public static void main(String[] args) {
       DriverManagerDataSource ds = new DriverManagerDataSource();
       ds.setDriverClassName("com.mysql.jdbc.Driver");
       ds.setUrl("jdbc:mysql://localhost:3306/codippa");
       ds.setUsername("root");
       ds.setPassword("root");
       JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
       Student student = jdbcTemplate.queryForObject(
                            "select * from student where studentId=1", 
              new RowMapper() {
		@Override
	        public Student mapRow(ResultSet rs, int rowNum)
                          throws SQLException {
		   Student student = new Student();
		   student.setStudentId(rs.getString(1));
		   student.setStudentName(rs.getString(2));
		   student.setYearLevel(rs.getString(2));
		   return student;
	        }
	});
   }
}

5. Inserting/Updating a record in a table
Inserting a record is performed using update method of JdbcTemplate class.
This method takes an insert or update SQL query as argument and the values which are to be inserted/updated into database columns.

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class Application {

   public static void main(String[] args) {
      DriverManagerDataSource ds = new DriverManagerDataSource();
      ds.setDriverClassName("com.mysql.jdbc.Driver");
      ds.setUrl("jdbc:mysql://localhost:3306/codippa");
      ds.setUsername("root");
      ds.setPassword("root");
      JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
      String insertQuery = "Insert into student values(?,?,?)";
      jdbcTemplate.update(insertQuery, 2,"new","11");
   }
}

6. Getting column names from a table
JdbcTemplate can also be used to get the column names present in a table.
This can be achieved using the ResultSet object which is passed when a RowMapper is implemented in the queryForObject method of JdbcTemplate.

ResultSet has a getMetaData method which returns a ResultSetMetaData object.
This meta data can be used to get the column names as shown in the code below.

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

public class Application {

   public static void main(String[] args) {
      DriverManagerDataSource ds = new DriverManagerDataSource();
      ds.setDriverClassName("com.mysql.jdbc.Driver");
      ds.setUrl("jdbc:mysql://localhost:3306/codippa");
      ds.setUsername("root");
      ds.setPassword("root");
      JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);
      jdbcTemplate.setDataSource(ds);
       // execute any query related to the table
      jdbcTemplate.queryForObject("select * from student where studentId=1", 
           new RowMapper() {
	      @Override
	      public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
                 // get meta data
                 ResultSetMetaData metaData = rs.getMetaData();
                 // get total number of columns in table
	         int columnCount = metaData.getColumnCount();
                 // iterate over columns
	         for (int i = 0; i < columnCount; i++) {
                    // get column name at current index
		    String columnName = metaData.getColumnName(i);
		 }
	         return null;
	      }
	});
    }
}

Let’s tweak in

1. update() method of JdbcTemplate can also be used to execute a Delete query also.
2. update() method returns an integer which is the number of rows affected as a result of query execution.
3. To convert a query result to an object, JdbcTemplate’s query() method can also be used.
   It takes two arguments:
   A. the query to be executed, and
   B. an implementation of ResultSetExtractor.
   This interface has a single method extractData() which receives the ResultSet of query execution.
4. DataSource can also be provided to JdbcTemplate using its setDataSource() method.