In this post, we will see various operations performed on the database using the JDBC template in spring MVC. We have already seen, how to perform an insert operation on the database in this post. Please follow the link to learn connecting the MySQL database with spring MVC as it will help you understand the one discussed here.
Retrieving an object –
In the DAO class, add an inline class implementing RowMapper. This class defines how to generate a Title object from a database row:
private class TitleMapper implements RowMapper<Title> {
public Title mapRow(ResultSet row, int rowNum) throws SQLException {
Title title = new Title();
title.setId(row.getInt("id"));
title.setTitleName(row.getString("title"));
title.setDescription(row.getString("description"));
return title;
}
}
Now add a method to the class which will use this TitleMapper to fetch data from database, that method will look like below:
public Title findById(int id) {
String sql = "select * from title where id=?";
Title title = jdbcTemplate.queryForObject(sql, new Object[] { id }, new TitleMapper());
return title;
}
Whole class will look like below:
package com.codekru.dao;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import com.codekru.model.Title;
@Repository
public class TitleDAO {
@Autowired
JdbcTemplate jdbcTemplate;
public void add(Title title) {
String sql = "insert into title (title_name, description) values (?, ?)";
jdbcTemplate.update(sql, title.getTitleName(), title.getDescription());
}
public Title findById(int id) {
String sql = "select * from title where id=?";
Title title = jdbcTemplate.queryForObject(sql, new Object[] { id }, new TitleMapper());
return title;
}
private class TitleMapper implements RowMapper<Title> {
public Title mapRow(ResultSet row, int rowNum) throws SQLException {
Title title = new Title();
title.setId(row.getInt("id"));
title.setTitleName(row.getString("title_name"));
title.setDescription(row.getString("description"));
return title;
}
}
}
Now, you must be wondering that what is TitleMapper class doing here? What is its use? Do, we really need that class? Well, the answer is you don’t really need that class if your table field names match with your class field names, then we will use ParameterizedBeanPropertyRowMapper instead of our own custom RowMapper interface but as our class field names are different from our table field names, so we have to implement the RowMapper interface. Below is the method where ParameterizedBeanPropertyRowMapper is used:
public Title findById(Long id) {
String sql = "select * from title where id=?";
Title title = jdbcTemplate.queryForObject(sql, new Object[] { id }, ParameterizedBeanPropertyRowMapper.newInstance(Title.class));
return title;
}
/*but ParameterizedBeanPropertyRowMapper is depreceated, so we don't recommend using it */
The queryForObject() method uses the TitleMapper object to generate a Title object from the resulting database row.
In this example, we retrieve a title from its ID, which is the second argument of queryForObject(), as an element of an array. So, that is for retrieving an object. It’s easy right ;). Now let’s move to update an object.
Updating an object –
We will be using an sql update query and excecute it using updateTitle method:
public void updateTitle(Title title) {
String sql = "update title set title_name=?, description=? where id=?";
jdbcTemplate.update(sql, title.getTitleName(), title.getDescription(),title.getId());
}
Well that’s it for updating an object. Well it is going easy ;). Now let’s move forward to deleting an object.
Deleting an object –
We will be using a sql delete query and execute it using deleteTitle method:
public void deleteTitle(Title title) {
String sql = "delete from title where id=?";
jdbcTemplate.update(sql, title.getId());
}
And that is it for various CRUD operations, now we can move to some miscellaneous operations on the database using Spring MVC.
How to find the number of results for an SQL query –
Well, please see below code to find the answer of above question –
public long countOverallTitles() {
String sql = "select count(*) from title";
return jdbcTemplate.queryForObject(sql, Long.class);
}
And here in the response of the method, you will get number of rows that are in the title table.
How to save a list of objects all at once –
We will use the batchUpdate() method that takes an SQL insert query and a list of values as parameters. Please see the below code for an understanding of batchUpdate method:
public void add(List<Title> titleList) {
String sql = "insert into title (title_name, description) values (?, ?)";
List<Object[]> userRows = new ArrayList<Object[]>();
for (Title title : titleList) {
userRows.add(new Object[] { title.getTitleName(), title.getDescription() });
}
jdbcTemplate.batchUpdate(sql, userRows);
}
Well, how it works.. ? A list of SQL insert queries will be generated from the SQL insert query string and the list of values. They will be sent to the database and committed all at once.