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

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

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

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

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