Spring JdbcTemplate to execute query in detail with example
Maven Dependency
We'll need the spring-boot-starter-jdbc dependency as the primary one and the dependency for the database that we'll be using. Here we are using SQLite which is an embedded database. we have already seen how we can use SQLite with Spring JPA Integration.
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- SQLite Database library -->
<dependency>
<groupId>com.zsoltfabok</groupId>
<artifactId>sqlite-dialect</artifactId>
<version>1.0</version>
</dependency>
<dependency>
<groupId>org.xerial</groupId>
<artifactId>sqlite-jdbc</artifactId>
</dependency>
If you have SQL installed and using it, then add the following dependency in pom.xml.
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
Configuration
Let's configure our data source first. We can configure it by defining the spring boot data source properties in the application.properties or by creating a configuration class.
The properties below are used to define the SQL data sources. Since we are using SQLite database, the spring.datasource.url property is enough for us.
spring.datasource.url=jdbc:mysql://localhost:3306/students
spring.datasource.username=username
spring.datasource.password=password
spring.datasource.driver=com.mysql.jdbc.DriverYou can also configure the data source explicitly by creating a spring bean as below inside a configuration class.
@Configuration
public class JdbcDataSourceConfig {
@Bean
public DataSource mysqlDataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/students");
dataSource.setUsername("username");
dataSource.setPassword("password");
return dataSource;
}
}
Spring Boot JdbcTemplate
query()
JdbcTemplate query() is used to run the SQL query and get multiple rows of results from the database. The query() method takes three parameters (SQL Query, Query parameters, Result mapper).
- SQL query - can be String OR PreparedStatementCreator.
- Query Parameters - if we need to pass parameters to the query then those parameter values can be passed using Object[] OR using PreparedStatementSetter.
- Result mapper - the mapper maps the query result to our java model class. Using Spring JDBC, 3 callback handler interfaces are available; RowMapper, ResultSetExtractor, and RollbackHandler. These interfaces provide methods for extracting results and converting the results into Java classes.
RowMapper - To create a custom RowMapper class, we have to implement the RowMapper interface and have to provide an implementation for the mapRow() method.
Below we have a custom Student RowMapper that maps ResultSet values into a Student object. By using the column names, we can get the column value from the ResultSet.
class StudentRowMapper implements RowMapper<Student> {
@Override
public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
Student student = new Student();
student.setId(rs.getInt("id"));
student.setName(rs.getString("name"));
return student;
}
}
BeanPropertyRowMapper - If the class variable names and table column names are the same, then using this class we can convert the ResultSet directly into a class without creating any mapper classes since the column names are the same, and the column value is automatically mapped to the corresponding class property.
public List<Student> getAll() {
String selectQuery = "SELECT * FROM Student;";
return jdbcTemplate.query(selectQuery, new BeanPropertyRowMapper<Student> ());
}queryForObject() method
The queryForObject() method is used to execute an SQL query and returns a single result object. The result type is specified in the arguments.
public String getById(int id) {
String selectQueryById = "SELECT name FROM Student WHERE id = 1;";
return jdbcTemplate.queryForObject(selectQueryById, String.class);
}
When you run queryForObject() with no parameters, JdbcTemplate creates a Statement, executes the query, and returns a single row or single column value as a result.
queryForObject with parameters
Here is an example with parameters and RowMapper. When you call queryForObject() with parameters, JdbcTemplates creates a PreparedStatement and executes the query, returning a single row or single value as a result.
public Student getById(int id) {
String selectQueryById = "SELECT * FROM Student WHERE id = ?;";
return jdbcTemplate.queryForObject(selectQueryById, new StudentRowMapper(), id);
}update() method - This is used for SQL operations such as insert, update, and delete. It executes a SQL query and returns the number of rows affected by executing our query. If there is an issue when running a query, it throws a DataAccessException.
Insertion - Here we are inserting a new student record into the student table database. The values will be inserted into the name and parentId columns.
public void insert(String studentName, String StudentParentId) {
String insertQuery = "INSERT INTO Student (name,parentId) VALUES(?,?);";
try {
jdbcTemplate.update(insertQuery, studentName, StudentParentId);
} catch (DataAccessException e) {
e.printStackTrace();
}
}
Updation - Here we are updating an existing student record in the database where the parentid of the student is being updated here.
public void update(String parentId, String studentId) {
String updateQuery = "UPDATE Student SET parentId = ? WHERE id = ?;";
try {
jdbcTemplate.update(updateQuery, parentId, studentId);
} catch (DataAccessException e) {
e.printStackTrace();
}
}
Deletion - Here we delete a student record based on the student's ID from the database.
public void delete(String studentId) {
String deleteQuery = "DELETE FROM Student WHERE id = ?;";
try {
jdbcTemplate.update(deleteQuery, studentId);
} catch (DataAccessException e) {
e.printStackTrace();
}
}NamedParameterJdbcTemplate
We have seen the usage of JdbcTemplate. If we want to update or retrieve any record by using any column name then in the query we use “?” and pass parameters in the method which will be injected in the place of “?” and JdbcTemplate executes the prepared query.
Spring JDBC provides another way to pass parameters by using names instead of “?”.
NamedParameterJdbcTemplate is an alternative to JdbcTemplate which has the same methods as JdbcTemplate except it has methods that take
We are using the MapSqlParameterSource to provide the values for the named parameters and passing this to NamedParameterJdbcTemplate.
public void insert(String studentName, String StudentParentId) {
String insertQuery = "INSERT INTO Student (name,parentId) VALUES(:name,:parentId);";
SqlParameterSource parameters = new MapSqlParameterSource()
.addValue("name", studentName)
.addValue("parentId", StudentParentId);
try {
namedParameterJdbcTemplate.update(insertQuery, parameters);
} catch (DataAccessException e) {
e.printStackTrace();
}
}