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.
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 it for just $10 on Udemy sales.
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.
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 it for 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 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.
Btw, if you are new to Spring and looking for a good course to master all essential concepts then I highly recommend you to checkout Learn Spring: The Master Class by fellow Java developer and blogger Eugen Paraschiv. It is one of the best course to learn Spring 5 and Spring Boot 2 from scratch, in a guided and code-focused way
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.
Further Learning
Spring Framework 5: Beginner to Guru
Spring Framework Fundamentals by Bryan Hansen
Learn Spring: The Certification Class
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)
P. S. - If you want to learn the Spring framework, particularly Spring Boot from scratch, and looking for some free resources then you can also check out this Spring Boot and AWS S3 course by Nelson Djalo, one of my favorite instructors on Udemy.
No comments:
Post a Comment