 |
VOOZH | about |
|
VOOZH | about |
Spring JDBC Template is a part of the Spring Framework that simplifies database operations by removing boilerplate JDBC code. It provides a clean and efficient way to interact with relational databases using SQL queries. It handles connection management, exception handling, and resource cleanup automatically.
There are 4 types of JDBC Drivers.
1. JDBC-ODBC Bridge Driver: Connects to databases using an ODBC driver and is platform-dependent. It is not recommended for production use.
2. Native API Driver : Uses the database’s native API for communication and is partially written in Java. It is also platform-dependent.
3. Network Protocol Driver: Uses middleware to communicate with the database and is fully Java-based. It provides platform independence.
4. Thin Driver: Directly communicates with the database using its network protocol and is fully Java-based. It is the most commonly used JDBC driver.
This diagram shows how a Java application interacts with a database using JDBC, where the JDBC Driver Manager acts as a bridge between the Java API and the vendor-specific database system.
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 is a core class in the Spring JDBC module that simplifies database access by removing boilerplate JDBC code. It provides an easy and efficient way to execute SQL queries and handle results.
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. |
1. Counting Records: Basic query to count students stored in the database using JdbcTemplate.
int count = jdbcTemplate.queryForObject(
"SELECT COUNT(*) FROM STUDENT", Integer.class);
2. Inserting a Record: Basic query to insert elements into the database.
public int addStudent(int id, String name, String country) {
return jdbcTemplate.update(
"INSERT INTO STUDENT (id, name, country) VALUES (?, ?, ?)",
id, name, country);
}
Note: The standard syntax of providing parameters is using the "?" character.
3. Fetching Records: Basic query to fetch records from the database.
public List<Student> getAllStudents() {
return jdbcTemplate.query(
"SELECT * FROM STUDENT",
new BeanPropertyRowMapper<>(Student.class));
}
Given below are the steps to configure and use JdbcTemplate in a Spring application.
Start by configuring the DataSource and JdbcTemplate in a Spring configuration class.
SpringJdbcConfig.java:
Define a model class to represent the database table.
Student.java:
Define a DAO interface for database operations.
StudentDAO.java:
Implement the DAO interface using JdbcTemplate.
Add the required dependencies in the pom.xml file.
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.
Below is the implementation class file StudentJDBCTemplate.java for the defined DAO interface StudentDAO.
StudentJDBCTemplate.java:
Note: StudentJDBCTemplate implements StudentDAO and provides database operations using JdbcTemplate. If you are using StudentJDBCTemplate, you do not need a separate StudentDAOImpl class.
