Hello guys, If you have been using the Spring framework then you may know that the JdbcTempalte is one of the most useful classes of 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 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 leak in Java.
By using classes like the JdbcTemplate Spring framework reduces a lot of these 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
Hello guys, If you have been using the Spring framework then you may know that the JdbcTempalte is one of the most useful classes of 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 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 leak in Java.
By using classes like the JdbcTemplate Spring framework reduces a lot of these 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.
Btw, I expect that you are familiar with the Spring framework. If you are new to Spring Framework and not familiar with Spring API then I suggest you first go through a comprehensive Spring framework course like Spring Framework 5: Beginner to Guru by John Thomson on Udemy to learn basics. This will save you a lot of time. It’s also the most up-to-date course to learn Spring and covers Spring 5 and new concepts like Reactive Programming. It’s also very affordable and you can buy in just $10 on Udemy sales.
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 Spring framework
1. How to use JdbcTemplate to query for single value like count, id etc
If you want to run an SQL query which 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 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 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 fo 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 method 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 given SQL query, unlike 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 an 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 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;
}
}
pre class=”brush:bash”>
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. Use or Generics and variable arguments make it even more convenient in Spring 3.0.
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.
Published on Java Code Geeks with permission by Javin Paul, partner at our JCG program. See the original article here: 10 JdbcTemplate Examples in Spring Framework Opinions expressed by Java Code Geeks contributors are their own. |
