Java Database Connectivity (JDBC) 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.
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)
The advantages of JDBC API is as follows:
- 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.
The disadvantages of JDBC API is as follows:
- 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.
Data Access using JDBC Template
There are a number of options for selecting an approach to form the basis for your JDBC database access. Spring framework provides the following approaches for JDBC database access:
- JdbcTemplate
- NamedParameterJdbcTemplate
- SimpleJdbcTemplate
- SimpleJdbcInsert and SimpleJdbcCall
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
/*package whatever //do not write package name here */@Configuration@ComponentScan("com.exploit.jdbc")public class SpringJdbcConfig { @Bean public DataSource mysqlDataSource() { DriverManagerDataSource dataSource = new DriverManagerDataSource(); dataSource.setDriverClassName( "com.mysql.jdbc.Driver"); dataSource.setUrl( dataSource.setUsername("user"); dataSource.setPassword("password"); return dataSource; }} |
A. File: Student.java
Java
// Java Program to Illustrate Student Classpackage com.exploit.org;// Classpublic 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
Below is the implementation of the Data Access Object interface file StudentDAO.java.
Example:
Java
// Java Program to Illustrate StudentDAO Classpackage com.exploit.org;// importing required classesimport java.util.List;import javax.sql.DataSource;// Classpublic 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();} |
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> |
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 Classpackage com.exploit.org;// Importing required classesimport java.util.List;import javax.sql.DataSource;import org.springframework.jdbc.core.JdbcTemplate;// Class// Implementing StudentDAO Classpublic 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; }} |
