Updated April 17, 2023
Introduction to JDBC template
JDBC template is the class which is provided in the spring framework for making the operations of the database from java application following the same JDBC concept but saves a lot of repetition of code and makes it easier and efficient as it handles the exception handling and the transaction management on its own. The full proof name of the JDBC template class is org.springframework.jdbc.core.JdbcTemplate class. In this article, we will learn about the general usage and requirement of the JDBC template, a brief introduction about it, its syntax and how it works along with the help of certain examples.
Need of JDBC class
While using the traditional approach of Java Database connectivity all the code related to connection creation, closing and handling the flow was on the developer. Following were the disadvantages of JDBC –
- The code like importing all the files, creating and establishing the connection with the database, preparing the queries and closing of the connection object needs to be done manually before and after the code of execution, the query needs to write.
- Code related to the handling of the exceptions based on the logic applied by the database needs to be done.
- In the case of transactions, transaction management needs to be done manually by coding the stuff on our own.
- All the code that is written for the above work needs repetition and consumes a lot of time when it needs to be transferred from one to another database logic.
All the above disadvantages of the traditional JDBC acts as the advantages for the JDBC template as it eliminates all of them. JDBC templates are time-saving
because we can directly go for executing the queries that you want to perform and mentioning all the other connection-related details in a single configuration file.
How it works and introduction to JDBC template?
The JDBC core package consists of the central class whose name is org,springframework.jdbc.core.JdbcTemplate which is also referred to as JDBC template which is generally used for avoiding all sorts of common errors that we do while using JDBC. This template is responsible for performing all the core workflow related to JDBC so that the application program is just left with the provision of the SQL queries that need to be executed and extracting the result of the same which can be further used by the application. This makes very simple for a developer to just focus on the business logic and code accordingly, leaving all the other core related function to be handled by JDBC template.
The JDBC template is responsible for executing all the passed queries, retrieving the data, updating and performing all the iterations over the result set. It further handles all the exceptions that might arise while doing database related operations and converts them to generic ones. You can get the information related to the hierarchy of exceptions that are handled by the JDBC template in this package org.springframework.dao.
Syntax –
Now, let us see the general declaration of the JDBC template class to understand which class it extends and which interfaces are implemented by it. Below is its declaration –
public class JdbcTemplate extends JdbcAccessor implements JdbcOperations
The steps that need to follow while using it involves –
- With the use of the data source configurations, we should create an object of JdbcTemplate class.
- Further, inside your application program and classes related to it, you should make the use of methods provided in this class to perform database operations.
The syntax of using the JDBC template’s query) method to execute the query statements is as shown below –
String sampleQueryVar = query statement;
List <Object/type> sampleListVar = jdbcTemplateObject.query(sampleQueryVar, new row mapper object)
The terminologies used in the above syntax are described below –
- sampleQueryVar – This is the variable which is declared for holding the query statement that we need to execute.
- sampleListVar – This is the variable that will store all the data retrieved after executing the query statement. The data type of this variable varies with respect to the query statement that we will execute.
- query statement – This is the query that we need to perform in the database and whose result is required in the application for usage. It can be any operational statement such as SELECT, INSERT, DELETE or UPDATE.
- row mapper object – This is the object of RowMapper class which will convert each of the retrieved row results to the class object which is the target format of the result.
- jdbcTemplateObject – In order to read the target object data from the database, we will need an object of the template class created by us.
Example
Now, let us understand the implementation of the jdbc template along with the help of an example. For this, we will need eclipse IDE and we will create a spring application in it. It will contain the below specified files –
ArticlesDAO.java file will be the Data Access Object interface for us which will contain the following code –
package com.educbaOrganization;
import java.util.List;
import javax.sql.DataSource;
public interface ArticlesDAO {
public void setDataSource(DataSource ds);
public List<Article> listArticles();
}
Article.java file
package com.educbaOrganization;
public class Article {
private Integer number_of_words;
private String article_name;
private Integer article_id;
public void setNumber_of_words(Integer number_of_words) {
this.number_of_words = number_of_words;
}
public Integer getNumber_of_words() {
return number_of_words;
}
public void setArticleName(String article_name) {
this.article_name = article_name;
}
public String getArticleName() {
return article_name;
}
public void setArticleId(Integer article_id) {
this.article_id = article_id;
}
public Integer getArticleId() {
return article_id;
}
}
ArticleMapper.java file
package com.educbaOrganization;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class ArticleMapper implements RowMapper<Article> {
public Article mapRow(ResultSet rs, int rowNum) throws SQLException {
Article article = new Article();
article.setArticleId(rs.getInt("article_id"));
article.setArticleName(rs.getString("article_name"));
article.setNumber_of_words(rs.getInt("number_of_words"));
return article;
}
}
Following is the implementation class file ArticleJDBCTemplate.java for the defined DAO interface ArticlesDAO.
package com.educbaOrganization;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
public class ArticleJDBCTemplate implements ArticlesDAO {
private DataSource dataSource;
private JdbcTemplate jdbcTemplateObject;
public void setDataSource(DataSource dataSource) {
this.dataSource = dataSource;
this.jdbcTemplateObject = new JdbcTemplate(dataSource);
}
public List<Article> listArticles() {
String SQL = "select * from educba_articles";
List <Article> articles = jdbcTemplateObject.query(SQL, new ArticleMapper());
return articles;
}
}
MainApp.java file
package com.educbaOrganization;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.educbaOrganization.ArticleJDBCTemplate;
public class MainApp {
public static void main(String[] args) {
ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");
ArticleJDBCTemplate articleJDBCTemplate =
(ArticleJDBCTemplate)context.getBean("articleJDBCTemplate");
System.out.println("The following is the result containing the records" );
List<Article> articles = articleJDBCTemplate.listArticles();
for (Article record : articles) {
System.out.print("ID : " + record.getArticleId() );
System.out.print(", ArticleName : " + record.getArticleName() );
System.out.println(", Number_of_words : " + record.getNumber_of_words());
}
}
}
Beans.xml file
<?xml version = "1.0" encoding = "UTF-8"?>
<beans xmlns = "http://www.springframework.org/schema/beans"
xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation = "http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">
<bean article_id="dataSource"
class = "org.springframework.jdbc.datasource.DriverMannumber_of_wordsrDataSource">
<property name = "driverClassArticleName" value = "com.mysql.jdbc.Driver"/>
<property name = "url" value = "jdbc:mysql://localhost:3306/TEST"/>
<property name = "userarticle_name" value = "root"/>
<property name = "password" value = "admin"/>
</bean>
<bean article_id="articleJDBCTemplate"
class = "com.educbaOrganization.ArticleJDBCTemplate">
<property name = "dataSource" ref = "dataSource" />
</bean>
</beans>
Output:
We can cross check the database content by firing the following query on our database –
SELECT * FROM [educba_articles];
The output of the above query is –
Conclusion
The use of the JDBC template makes it very easy to perform the database operations using JDBC as we only need to focus on the business logic and the rest all of the core responsibilities are performed by the template itself.
Recommended Articles
This is a guide to the JDBC template. Here we discuss the general declaration of the JDBC template class to understand which class it extends. You may also have a look at the following articles to learn more –