How to connect to database using JdbcTemplate in Spring / How to perform various database operations using Spring JdbcTemplate

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.

For detailed discussion on DriverManagerDataSource, please refer this post

Why JdbcTemplate

org.springframework.jdbc.core.JdbcTemplate 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 java.sql.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 org.springframework.jdbc.datasource.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 JdbTemplate.

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 using XML, then to get its object in the code, create a field of type JdbcTemplate and annotate it with @Autowired annotation or get it using org.springframework.context.support.ClassPathXmlApplicationContext. 

2. Programmatic Configuration

Create an object of org.springframework.jdbc.datasource.DriverManagerDataSource directly inside code and populate its fields with connection parameters as below. After configuring org.springframework.jdbc.datasource.DriverManagerDataSource, you can easily create an object of org.springframework.jdbc.core.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 :

CREATE TABLE `student` (
  `studentId` int(11) NOT NULL AUTO_INCREMENT,
  `studentName` varchar(255) NOT NULL,
  `yearLevel` varchar(45) NOT NULL,
  PRIMARY KEY (`studentId`)
);

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 All 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 java.util.List of type java.util.Map<String, Object>. Each list item represents a row of database table. 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 a column value for a record

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 the count of all records in a 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 a 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 org.springframework.jdbc.core.RowMapper. This RowMapper maps a database record to an object. It is an interface and has only one method mapRow which is supplied a java.sql.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");
	}
}

5. 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 java.sql.ResultSet object which is passed when a RowMapper is implemented in the queryForObject method of JdbcTemplate. java.sql.ResultSet has a getMetaData method which returns a java.sql.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 : first, the query to be executed and second, an implementation of org.springframework.jdbc.core.ResultSetExtractor. This interface has a single method extractData which receives the java.sql.ResultSet of query execution.
  4. DataSource can also be provided to JdbcTemplate using its setDataSource method.

Mark Your Impression

Close Menu