10 Examples of JdbcTemplate in Spring Framework

Hello Java programmers, If you have been using the Spring framework then you may know that the JdbcTempalte is one of the most useful classes of the Spring framework. The JdbcTempalte class provides lots of convenient methods for performing database-related tasks that take the pain away from using the JDBC API. If you have worked in any real-life Java project which connects to a Database using JDBC then you know that JDBC API is not very convenient to use as it requires a lot of boilerplate code. For example, to execute a simple SELECT query, you first need to create a Connection and Statement object to execute the query and iterate through a ResultSet Object to get the result of the query. Finally, you need to close all of these resources to prevent resource leaks in Java.

By using classes like the JdbcTemplate Spring framework reduces a lot of this boilerplate coding. JdbcTemplate provides multiple overloaded methods to execute different kinds of SQL queries and perform CRUD operations.

The JdbcTemplate utility class also makes extensive usage of variable arguments method which makes using PreparedStatment and bind variable in SQL query very easy.

In this article, I'll show you how to use JdbcTempalte class to query a single value, run a query using a bind variable, how to populate a Java object from data retrieved from the database, how to insert rows, how to update records, how to delete records, and how to run any kind of SQL query using JdbcTempalte.

JdbcTemplate Examples in Spring Framework

As I told you, we'll see multiple examples of JdbcTempalte to perform day-to-day queries, but, before that, let's understand our schema and data inside our table so that we can verify the output and ascertain that code is working as expected.

Here is how our database and tables look like:

mysql> select  from employee;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|    101 | James    |       1 |   1000 |
|    102 | kate     |       1 |   1200 |
|    103 | Jack     |       2 |   1400 |
|    104 | John     |       2 |   1450 |
|    105 | Johnny   |       3 |   1050 |
|    108 | Alan     |       3 |   1150 |
|    106 | Virat    |       4 |    850 |
|    107 | Vina     |       4 |    700 |
|    109 | joya     |       4 |    700 |
|    110 | James    |       1 |   1000 |
+--------+----------+---------+--------+
10 rows in set (0.00 sec)

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| books          |
| department     |
| employee       |
+----------------+
3 rows in set (0.09 sec)

And, here are some common example of interacting with database and running SQL query to read and write data from tables using JdbcTemplate class of the Spring framework

1. How to use JdbcTemplate to query for single value like count, id, etc

If you want to run an SQL query that runs aggregate functions like count(), avg(), max(), and min() or just return an integer value then you can use the queryForInt() method of JdbcTempalte to execute the SQL query as shown in the following example:

 int total = jdbcTemplate.queryForInt("SELECT count() FROM employee"); 
                logger.info("Total Employees : " + total); 

2. JdbcTemplate example to query and populate Java object from the database

If you want to run an SQL query that returns a value object like String then you can use the queryForObject() method of JdbcTempalte class. 

This method takes an argument about what type of class query will return and then convert the result into that object and returns it to the caller.

 String name = jdbcTemplate.queryForObject("SELECT emp_name 
                                             FROM employee where emp_id=?",
                                             new Object[]{103}, String.class);

3. JdbcTemplate example to retrieve a Custom Object from the database

If your SQL query is going to return a user object like Employee, Order, or anything domain-specific then you need to provide a RowMapper implementation to queryForObject() method. This mapper will tell the JdbcTempalte how to convert the ResultSet into a custom object. 

Here is an example of retrieving a custom object.

Employee emp = jdbcTemplate.queryForObject("SELECT  FROM employee
                                              where emp_id=?", 
                                               new Object[]{103},
                                               new EmployeeMapper());

4.  JdbcTemplate Example to retrieve a list of Object from table

If your SQL query is going to return a List of objects instead of just one object then you need to use the query() method of JdbcTempalte. This is one of the most generic methods and it can run any kind of query. 

Again to convert the result to a custom object, you need to provide a RowMapper implementation as shown in the following example:

  List<Employee> empList = jdbcTemplate.query("SELECT  FROM employee 
                                             where salary > 500",
                                             new EmployeeMapper()); 

5.  How to INSERT records in SQL using Spring JdbcTemplate example

So far, we have seen the example of reading data from tables using JdbcTemplate's query methods like queryForInt(), queryForObject(), and query() itself. Now, you will see how to write data into a table like running insert, update, and delete query using JdbcTempalte. 

For inserting data into a database you can use the update() method of JdbcTempalte class as shown below:

   int insertCount = jdbcTemplate.update("INSERT INTO employee 
                                          values (?,?,?,?)",
                                          "111", "Peter", "1", "2000" );

6.  How to UPDATE records in SQL using Spring JdbcTemplate example

The same update method which we have used for inserting data in the previous example can also be used to run the update query in the Spring JDBC application. 

Here is an example of how to update a particular record using Spring's JdbcTempalte class:

   int updateCount = jdbcTemplate.update("UPDATE employee 
                                          SET dept_id=? 

                                          WHERE emp_id=?", "2", "112");

7.  How to DELETE rows in a table using Spring JdbcTemplate

The same update method which is used to run the insert and update query can also be used to run the delete query as shown below.  

This time it returns the number of rows deleted by a given SQL query, unlike the number of inserted and updated records in previous examples.

 int deleteCount = jdbcTemplate.update("DELETE FROM employee

                                       WHERE dept_id=?", "1" );

8.  JdbcTemplate example to execute any SQL query

So far we have seen examples of how to run DML or Data manipulation queries using Spring Framework and JdbcTemplate like CRUD (Create, Read, Update and Delete) but JdbcTemplate can also run DDL queries like Create table or Create Index.   

The JdbcTempalte class also has a generic execute() method to run DDL queries as shown below where we have created a new table called Book:

   jdbcTemplate.execute("create table Books (id integer,
                                         name varchar(50), ISBN  integer)");

Now that you have seen the individual examples of using JdbcTempalte's different methods like to execute(), update(), query(), queryForObject(), and queryForInt(), let's see the complete program which you can copy and execute in your favorite IDE.

Spring Framework JdbcTemplate Example in Java

Here is the sample program which will teach you how to use JdbcTemplate in a spring-based Java application.

import java.sql.ResultSet; 
import java.sql.SQLException;
import java.util.List; 
import javax.sql.DataSource; 
import org.apache.log4j.Logger; 
import org.springframework.context.ApplicationContext; 
import org.springframework.context.support.ClassPathXmlApplicationContext; 
import org.springframework.jdbc.core.JdbcTemplate; 
import org.springframework.jdbc.core.RowMapper; 
import com.test.domain.Employee; 
 
 
public class JdbcUtil { 
  
        private static Logger logger = Logger.getLogger(JdbcUtil.class); 
 
        private JdbcTemplate jdbcTemplate; 
 
        public void setDataSource(DataSource source){ 
                this.jdbcTemplate = new JdbcTemplate(source);
         } 
  
 
        /** 
          * This method demonstrates 10 JdbcTemplate examples in Spring 
          */
 
        public void jdbcTemplateExamples(){
   
                // how to use JdbcTemplate to query for single value e.g. count, id etc 
                int total = jdbcTemplate.queryForInt("SELECT count() FROM employee"); 
                logger.info("Total Employees : " + total); 
 
 
                //another example to query for single value using bind variable in Java 
                String name = jdbcTemplate.queryForObject("SELECT emp_name 
                                                FROM employee where emp_id=?",
                                                 new Object[]{103}, String.class);
 
 
                logger.info("Name of Employee : " + name); 
 
 
                //JdbcTemplate example to query and populate Java object from database 
                Employee emp = jdbcTemplate.queryForObject("SELECT  FROM employee
                                                       where emp_id=?", 
                                                       new Object[]{103},
                                                       new EmployeeMapper());
                 logger.info(emp);
 
 
 
               //JdbcTemplate example to retrieve a list of object from database
                 List empList = jdbcTemplate.query("SELECT  FROM employee 
                                                   where salary > 500",
                                                    new EmployeeMapper()); 
 
                logger.info("size : " + empList.size() + ", List of Employees : " 
                                                         + empList); 
 
 
                // JdbcTemplate Example to INSERT records into database
                 int insertCount = jdbcTemplate.update("INSERT INTO employee 
                                                       values (?,?,?,?)",
                                                        "111", "Peter", "1", "2000" );
 
 
                logger.info("number of rows inserted using JdbcTemplate : " 
                                               + insertCount);
 
 
                // How to update records in SQL using Spring JdbcTemplate example 
                int updateCount = jdbcTemplate.update("UPDATE employee 
                                            SET dept_id=? 
                                            where emp_id=?", "2", "112");
 
                logger.info("number of rows updated with JdbcTemplated : " 
                                             + updateCount);  
 
 
                // How to delete rows in a table using Spring JdbcTemplate 
                int deleteCount = jdbcTemplate.update("DELETE FROM employee
                                                   where dept_id=?", "1" );
 
                logger.info("number of rows deleted using JdbcTemplate : "
                                                  + deleteCount);
   
                // JdbcTemplate example to execute any SQL query 
                jdbcTemplate.execute("create table Books (id integer,
                                         name varchar(50), ISBN  integer)");
 
 
        }  

 
        public static void main(String args[]){ 
                ApplicationContext context 
                        = new ClassPathXmlApplicationContext("spring-config.xml"); 
                JdbcUtil jdbcUtil = (JdbcUtil) context.getBean("jdbcUtil");

 
                //calling jdbcTemplateExmaples() to 
                // demonstrate various ways to use JdbcTemplate in Spring 
                jdbcUtil.jdbcTemplateExamples();
 
        }
 
  
        /** 
          * nested static class to act as RowMapper for Employee object
           */
 
        private static class EmployeeMapper implements RowMapper {
 
 
            public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
 
                  Employee emp = new Employee();
                  emp.setId(rs.getInt("emp_id")); 
                  emp.setName(rs.getString("emp_name")); 
                  emp.setDepartmentId(rs.getInt("dept_id")); 
                  emp.setSalary(rs.getInt("salary")); 
                  return emp;
 
            }
  
        }
 
 
 
Output:
 
2013-01-03 02:47:34,282 0    [main] INFO  JdbcUtil  - Total Employees : 7 
2013-01-03 02:47:34,313 31   [main] INFO  JdbcUtil  - Name of Employee : Jack 
2013-01-03 02:47:34,313 31   [main] INFO  JdbcUtil  - 

  Employee [name=Jack, id=103,
 departmentId=2, salary=1400]
 
2013-01-03 02:47:34,313 31   [main] INFO  JdbcUtil  - size : 7, 
List of Employees : [Employee [name=Jack, id=103, departmentId=2, salary=1400],
 Employee [name=John, id=104, departmentId=2, salary=1450],
 Employee [name=Johnny, id=105, departmentId=3, salary=1050],
 Employee [name=Alan, id=108, departmentId=3, salary=1150],
 Employee [name=Virat, id=106, departmentId=4, salary=850], 
Employee [name=Vina, id=107, departmentId=4, salary=700],
 Employee [name=joya, id=109, departmentId=4, salary=700]]
 
  
2013-01-03 02:47:34,407 125  [main] INFO  JdbcUtil  
- number of rows inserted using JdbcTemplate : 1
 
2013-01-03 02:47:34,423 141  [main] INFO  JdbcUtil  
- number of rows updated with JdbcTemplated : 0
 
2013-01-03 02:47:34,516 234  [main] INFO  JdbcUtil  
- number of rows deleted using JdbcTemplate : 1

     

Important points about JdbcTemplate of Spring framework

Few points to note while using JdbcTemplate of Spring framework in Java application :

1) The JdbcTempalte is a thread-safe once configured, which means after configuration you can share the same JdbcTempalte among multiple DAO classes.

2) JdbcTemplate class maintains a reference to DataSource for communicating with the database.


That's all on How to use the JdbcTemplate class for JDBC related tasks in the Spring framework. JdbcTemplate significantly reduces coding required to perform various JDBC tasks like executing a query, you don't need to create PreparedStatement, Connection, and worry about closing them in finally block.

Spring framework takes care of all the boilerplate work like closing connections, statements, and resultset. With various overloaded methods available in the JdbcTemplate class you can do whatever you need. The use of Generics and variable arguments make it even more convenient in Spring 3.0.


Other Java and Spring Articles you may like

• 15 Spring Boot Interview Questions for Java Developers (questions)
• Top 5 Courses to Learn and Master Spring Cloud (courses)
• 5 Free Courses to Learn Spring Framework (free courses)
• Difference between @Controller and @RestController in Spring (answer)
• 5 Courses to Learn Spring Security for Java programmers (courses)
• Top 5 Spring Boot Annotations Java Developers should know (read)
• Difference between @RequestParam and @PathVariable in Spring (answer)
• Top 7  Courses to learn Microservices in Java (courses)
• 10 Spring MVC annotations Java developer should learn (annotations)
• @SpringBootApplication vs @EnableAutoConfiguration? (answer)
• 5 Spring Books Experienced Java Developer Should Read (books)
• Top 5 Frameworks Java Developer Should Know (frameworks)
• 10 Advanced Spring Boot Courses for Java developers (courses)
• Top 5 Spring Cloud annotations Java programmer should learn (cloud)
• Difference between @Component, @Service, and @Controller in Spring (answer)
• 20+ Spring MVC Interview Questions for Programmers (answer)
• Difference between @Autowired and @Inject in Spring? (answer)
• How Spring MVC works internally? (answer)
• 5 Courses to learn Spring Cloud for Microservices (courses)

Thanks for reading this article so far. If you find this Spring JDBC tutorial then please share it with your friends and colleagues. If you have any questions or feedback then please drop a note.