JDBC Template
JdbcTemplate is a central class in the JDBC core package that simplifies the use of JDBC and helps to avoid common errors. It internally uses JDBC API and eliminates a lot of problems with JDBC API. It executes SQL queries or updates, initiating iteration over ResultSets and catching JDBC exceptions and translating them to the generic. It executes core JDBC workflow, leaving application code to provide SQL and extract results. It handles the exception and provides the informative exception messages with the help of exception classes defined in the org.springframework.dao package.
The common methods of spring JdbcTemplate class.
Methods | Description |
---|---|
public int update(String query) | Used to insert, update and delete records. |
public int update(String query, Object… args) | Used to insert, update and delete records using PreparedStatement using given arguments. |
public T execute(String sql, PreparedStatementCallback action) | Executes the query by using PreparedStatementCallback. |
public void execute(String query) | Used to execute DDL query. |
public T query(String sql, ResultSetExtractor result) | Used to fetch records using ResultSetExtractor. |
JDBC Template Queries
Basic query to count students stored in the database using JdbcTemplate.
int result = jdbcTemplate.queryForObject(
"SELECT COUNT(*) FROM STUDENT", Integer.class);
And here’s a simple INSERT:
public int addStudent(int id)
{
return jdbcTemplate.update("INSERT INTO STUDENT VALUES (?, ?, ?)", id, "megan", "India");
}
The standard syntax of providing parameters is using the “?” character.
Implementation: Spring JdbcTemplate
We start with some simple configurations of the data source. We’ll use a MySQL database
Example:
Java
//Java program for spring datasource configuration @Configuration @ComponentScan ( "com.exploit.jdbc" ) public class SpringJdbcConfig { @Bean public DataSource mysqlDataSource() { DriverManagerDataSource dataSource = new DriverManagerDataSource(); dataSource.setDriverClassName( "com.mysql.jdbc.Driver" ); dataSource.setUrl( "jdbc:mysql://localhost:8800/springjdbc" ); dataSource.setUsername( "user" ); dataSource.setPassword( "password" ); return dataSource; } } |
In the above code, we have used @Configuration annotation which means for Spring context, the class file is configured in the class path. The @ComponentScan(“com.exploit.jdbc”) annotation will scan the package (inside the parameter) for classes.
A. File: Student.java (Model Class)
Java
// Java Program to Illustrate Student Class package com.exploit.org; // Class public class Student { // Class data members private Integer age; private String name; private Integer id; // Constructor public Student() {} // Setters and Getters public void setAge(Integer age) { this .age = age; } public Integer getAge() { return age; } public void setName(String name) { this .name = name; } public String getName() { return name; } public void setId(Integer id) { this .id = id; } public Integer getId() { return id; } } |
B. File: StudentDAO.java (DAO Class)
Below is the implementation of the Data Access Object interface file StudentDAO.java.
Example:
Java
// Java Program to Illustrate StudentDAO Class package com.exploit.org; // importing required classes import java.util.List; import javax.sql.DataSource; // Class public interface StudentDAO { // Used to initialize database resources // ie. connection public void setDataSource(DataSource ds); // Used to list down all the records // from the Student table public List<Student> listStudents(); } |
In the above code, we created DAO Class for mapping the model class (Student.Java) with the database table using SQL queries.
C. File: Maven Dependency
Dependency is used in the pom.xml file.
Example:
XML
< dependency > < groupId >org.springframework.boot</ groupId > < artifactId >spring-boot-starter-jdbc</ artifactId > </ dependency > < dependency > < groupId >mysql</ groupId > < artifactId >mysql-connector-java</ artifactId > < scope >runtime</ scope > </ dependency > |
In the above pom.xml file we have used “spring-boot-starter-jdbc” dependency for implementing Java Database Connectivity in our application. Also we have used “mysql-connector-java” dependency to connect to the MySQL database and execute SQL queries.
D. File: StudentJDBCTemplate.java
Below is the implementation class file StudentJDBCTemplate.java for the defined DAO interface StudentDAO.
Example:
Java
// Java Program Illustrating Implementation // of StudentDAO Class package com.exploit.org; // Importing required classes import java.util.List; import javax.sql.DataSource; import org.springframework.jdbc.core.JdbcTemplate; // Class // Implementing StudentDAO Class public class StudentJDBCTemp implements StudentDAO { // Class data members private DataSource dataSource; private JdbcTemplate jdbcTemplateObject; // Method 1 public void setDataSource(DataSource dataSource) { // This keyword refers to current instance itself this .dataSource = dataSource; this .jdbcTemplateObject = new JdbcTemplate(dataSource); } // Method 2 public List<Student> listStudents() { // Custom SQL query String SQL = "select * from Student" ; List<Student> students = jdbcTemplateObject.query( SQL, new StudentMapper()); return students; } } |
Spring – JDBC Template
In this article, we will discuss about Spring JDBC Template and how to configure the JDBC Template to execute queries. Spring JDBC Template provides a fluent API that improves code simplicity and readability and the JDBC Template is used to connect to the database and execute SQL Queries.
JDBC (Java Database Connectivity) is an application programming interface (API) that defines how a client may access a database. It is a data access technology used for Java database connectivity. It provides methods to query and update data in a database and is oriented toward relational databases. JDBC offers a natural Java interface for working with SQL. JDBC is needed to provide a “Pure Java” solution for application development. JDBC API uses JDBC drivers to connect with the database.
Types of JDBC Drivers
There are 4 types of JDBC Drivers.
- JDBC-ODBC Bridge Driver
- Native API Driver (partially Java driver)
- Network Protocol Driver (fully Java driver)
- Thin Driver (fully java driver)
Advantages of JDBC API
- Automatically creates the XML format of data from the database.
- It supports query and stored procedures.
- Almost any database for which ODBC driver is installed can be accessed.
Disadvantages of JDBC API
- Writing a lot of codes before and after executing the query, such as creating connection, creating a statement, closing result-set, closing connection, etc.
- Writing exception handling code on the database logic.
- Repetition of these codes from one to another database logic is time-consuming.
These problems of JDBC API are eliminated by Spring JDBC-Template. It provides methods to write the queries directly that saves a lot of time and effort.
Contact Us