While working with Spring Framework, we are taking advantage of using CRUD repositories. However, some people would prefer having full control of SQL over the data. Sometimes I feel more comfortable processing the data using SQL, which might be related to old habits or to the flexiblity of SQL. Here I would like to give you an another approach than CRUD repositories to manipulate your data. Spring has good support for SQL with help of JDBC, called JDBC templates.
If you are following the posts one by one and do not intend to use JDBC templates for your project, you can simply skip this article.
Normally Spring implements our Repository interfaces on our behalf when we follow the convensions. For this post, I will implement Spring’s CRUD repository interface to feel more powerful on database operations. Keep in mind that feeling powerful is not the easiest thing always as you need to pay what it takes.
Required methods
Spring Data has an interface , CrudRepository, we have used in our previous posts. In this interface Spring gives us all required methods for a CRUD operations. As we know CRUD is an acronym for CREATE, READ, UPDATE, DELETE. When we have a look at the interface, we see
- save and saveAll for CREATE and UPDATE
- findById, existsById, findAll, findAllById, count for READ
- delete, deleteAll, deleteById for DELETE
package org.springframework.data.repository;
import java.util.Optional;
@NoRepositoryBean
public interface CrudRepository<T, ID> extends Repository<T, ID> {
<S extends T> S save(S entity);
<S extends T> Iterable<S> saveAll(Iterable<S> entities);
Optional<T> findById(ID id);
boolean existsById(ID id);
Iterable<T> findAll();
Iterable<T> findAllById(Iterable<ID> ids);
long count();
void deleteById(ID id);
void delete(T entity);
void deleteAll(Iterable<? extends T> entities);
void deleteAll();
}
In my previous posts, I had an interface named PersonRepository. I will implement this interface using JDBC template.
First here our PersonRepository interface
package com.enginaar.spring.data.repository;
import java.util.List;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import com.enginaar.spring.data.domain.Person;
@Repository
public interface PersonRepository extends CrudRepository<Person, Long> {
List<Person> findByName(String name);
List<Person> findByLastName(String lastname);
int countByName(String name);
}
Create a class and implement this interface. Auto generated code by IDE is here
package com.enginaar.spring.data.repository.impl;
import java.util.List;
import java.util.Optional;
import com.enginaar.spring.data.domain.Person;
import com.enginaar.spring.data.repository.PersonRepository;
public class PersonRepositoryImpl implements PersonRepository {
@Override
public <S extends Person> S save(S entity) {
return null;
}
@Override
public <S extends Person> Iterable<S> saveAll(Iterable<S> entities) {
return null;
}
@Override
public Optional<Person> findById(Long id) {
return null;
}
@Override
public boolean existsById(Long id) {
return false;
}
@Override
public Iterable<Person> findAll() {
return null;
}
@Override
public Iterable<Person> findAllById(Iterable<Long> ids) {
return null;
}
@Override
public long count() {
return 0;
}
@Override
public void deleteById(Long id) {
}
@Override
public void delete(Person entity) {
}
@Override
public void deleteAll(Iterable<? extends Person> entities) {
}
@Override
public void deleteAll() {
}
@Override
public List<Person> findByName(String name) {
return null;
}
@Override
public List<Person> findByLastName(String lastname) {
return null;
}
@Override
public int countByName(String name) {
return 0;
}
}
Let’s start implementing these method one by one.
Implementing methods one by one
Instead of adding method implementations one by one, I put all the implementations of interface here once. Then I will start explaining methods one by one.
package com.enginaar.spring.data.repository.impl;
import java.sql.PreparedStatement;
import java.util.LinkedList;
import java.util.List;
import java.util.Optional;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Component;
import com.enginaar.spring.data.domain.Person;
import com.enginaar.spring.data.repository.PersonRepository;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
@Component
public class PersonRepositoryImpl implements PersonRepository {
@Autowired
private JdbcTemplate db;
private RowMapper<Person> extractor = (ResultSet rs, int rowNum) -> {
Person person = new Person();
try {
person.setId(rs.getLong("id"));
person.setName(rs.getString("name"));
person.setLastName(rs.getString("last_name"));
return person;
} catch (SQLException ex) {
return null;
}
};
@Override
public <S extends Person> S save(S entity) {
if (entity.getId() == 0) {
KeyHolder key = new GeneratedKeyHolder();
db.update((PreparedStatementCreator) con -> {
PreparedStatement stmt = con.prepareStatement("insert into person (name, last_name) values (?, ?)", new String[]{"id"});
stmt.setString(1, entity.getName());
stmt.setString(2, entity.getLastName());
return stmt;
}, key);
return (S) findById(key.getKey().longValue()).get();
} else {
db.update("update person set name = ?, last_name = ? where id = ?", entity.getName(), entity.getLastName(), entity.getId());
return (S) findById(entity.getId()).get();
}
}
@Override
public <S extends Person> Iterable<S> saveAll(Iterable<S> entities) {
List<S> list = new LinkedList<>();
for (S entity : entities) {
list.add(save(entity));
}
return list;
}
@Override
public Optional<Person> findById(Long id) {
try {
Person p = db.queryForObject("select * from person p where p.id = ?", extractor, id);
return Optional.of(p);
} catch (EmptyResultDataAccessException e) {
return Optional.empty();
}
}
@Override
public boolean existsById(Long id) {
Optional<Person> findById = findById(id);
return findById.isPresent();
}
@Override
public Iterable<Person> findAll() {
return db.query("select * from person", extractor);
}
@SuppressWarnings("unchecked")
@Override
public Iterable<Person> findAllById(Iterable<Long> ids) {
NamedParameterJdbcTemplate named = new NamedParameterJdbcTemplate(db.getDataSource());
SqlParameterSource parameters = new MapSqlParameterSource("ids", ids);
return (Iterable<Person>) named.query("select * from person p where p.id in (:ids)", parameters, extractor);
}
@Override
public long count() {
return db.queryForObject("select count(*) from person", Integer.class);
}
@Override
public void deleteById(Long id) {
db.update("delete from person where id = ?", id);
}
@Override
public void delete(Person entity) {
deleteById(entity.getId());
}
@Override
public void deleteAll(Iterable<? extends Person> entities) {
for (var entity : entities) {
delete(entity);
}
}
@Override
public void deleteAll() {
db.update("delete from person");
}
@Override
public List<Person> findByName(String name) {
return db.query("select * from person p where p.name = ?", extractor, name);
}
@Override
public List<Person> findByLastName(String lastname) {
return db.query("select * from person p where p.last_name = ?", extractor, lastname);
}
@Override
public int countByName(String name) {
return db.queryForObject("select count(*) from person p where p.name = ?", Integer.class, name);
}
}
Class fields
JDBC Template
The first field of our class is JdbcTemplate which has been autowired by Spring context. Since we are handling all the db related operations using this JdbcTemplate, I named it db.
You can check what kind of methods jdbc template provides simple help of auto complete functionality of your ide or you can directly jump into the class itself.
Although JDBC template class has lots of method to perform JDBC operations, most of methods are derivatives of query and update methods which are meaningful. We see some examples in our implementation code. The only thing to keep in mind is that we will use update methods when we want to change a data from database.
Which SQL keywords are being used to change the database status?
Obviously update is the first one which has the same name with jdbc methods, but that is not the only one. Delete and insert operations also change the database status. What should we understand from this sentence? We will use update methods for inserting, updating and deleting the records. There is only one remaining operation, we will use query methods when we send “select” command to the database.
RowMapper
Row mapper is an iterface which we implement to show how to fill class using database resulset. Since we will use this interface several times, I defined it as a field and used it while developing method. We pass this parameter where we need to fill an instance of queried class.
Implementations
The first method group, Save
The Save method is being used to both inserting and updating the data commonly. Since we are developing our application using SQL commands, it is up to our decision to use save for both operation or seperate them. I prefer using the same method for both so that we will have better understanding of the idea which has been implemented in JPA/ORM.
Save method is designed to return a value which will be the instance of the record after saving it to database.
At the first line of save method, we identify the operation we perform either insert or update by simply looking at the id field. If we have an id, that means we are performing an update. Otherwise this is an insert operation.
We start with implementing of the insert operation. We create a KeyHolder class. This is an important class that helps us to get the id value which will be created by database with the insert operation. When we create a prepared statement we define a list of fields to be filled by database. The second parameter of prepareStatement method is the place where we put the list.
JdbcTemplate.update method has an overridden version which requires two parameters, PreparedStatementCreator and KeyHolder. We will continue with this one to get the id.
db.update(PreparedStatementCreator, KeyHolder)
Please be aware, I prefered this update method to get the id. If you think you do not need to have generated id, you may use simpler update method like following one.
db.update("insert into Person (name, last_name) values (?, ?)", entity.getName(), entity.getLastName())
This second one is also preferable for simpler insert.
After update operation we have id value and we call findById method to get current record from database. Why we retrieve this record using another database interaction is database side may have some triggers, application can have different observers etc. We make sure that we retrieve the latest database representation.
In the else part of our if condition we have relatively simpler lines of code which handles update operation and retrieves the latest record again and then returns it.
This was our hardest method to implement, we can carry on for the next method which is saveAll. It is the same operation for a bunch of records, we can make it simply using a for loop.
@Override
public <S extends Person> Iterable<S> saveAll(Iterable<S> entities) {
List<S> list = new LinkedList<>();
for(S entity : entities)
list.add(save(entity));
return list;
}
The second method group, findBy…
We have several query method supported by JDBC template but I like using queryForList with the method signature of
JdbcTemplate.queryForList(String sql, Object... args) : List<Map<String, Object>>
I like it because we do not need to map anything, just query the data and the data will be served in a list with each row as map. As simple as high level languages like PHP, JS etc.
For findById method I used JdbcTemplate.queryForObject method since I am expecting only one record. When I get the record I return it using the help of Optional class which is easier to handle. If we do not get any record from db, we retrieve Optional.empty result.
The difference of findAll method than findById is that we do not pass any parameter to JdbcTemplate.query method. The extractor field handles mapping of resultset with our Person class.
I prefered simple existById method using findById. If you want to make it a bit more performant, you can write another sql which does not bring all the records from db. Instead, only one field or a meaningful data is enough like “select 1 from person p where p.id = xxx”. Here we do not fetch all fields from the table. It is up to you.
FindAllById method has an important implementation detail. I used NamedParameterJdbcTemplate instead of ordinary JdbcTemplate. It is because of using list of ids as a parameter to query method. You can see I define a SqlParameterSource, gave it a name and passed the list directly to let Spring handle the sql generation. We can do this generating a spring adding all ids one by one, but this one helps us prevent sql injection. If you want to use more advanced features you can directly use NamedParameterJdbcTemplate instead.
FindByName, FindByLastname, countByName are similar to the previous methods.
The third group, delete
I think this group was the easiest group after implementing the previous ones. We are using JdbcTemplate.update method. First I developed deleteById and called it from the other methods. It is that easy.
Testing
You can find a test file here to see the results. It helped me when developing this case study. Of course I pushed these development to github you can find it here.
package com.example.demo;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit.jupiter.SpringExtension;
import com.enginaar.spring.data.DataApplication;
import com.enginaar.spring.data.domain.Person;
import com.enginaar.spring.data.repository.impl.PersonRepositoryImpl;
import com.github.javafaker.Faker;
import java.util.LinkedList;
import java.util.List;
import java.util.Optional;
import static org.assertj.core.api.Assertions.assertThat;
@ExtendWith(SpringExtension.class)
@SpringBootTest(classes = DataApplication.class)
class DataApplicationTests {
private @Autowired
@Qualifier("personRepositoryImpl")
PersonRepositoryImpl persons;
@Test
void saveNew() {
var saved = saveOne();
assertThat(saved).isNotNull();
}
@Test
void saveList() {
var l = new LinkedList<Person>();
for (int i = 0; i < 10; i++) {
l.add(generatePerson());
}
Iterable<Person> all = persons.saveAll(l);
assertThat(all).size().isEqualTo(l.size());
}
@Test
void findAll() {
var l = new LinkedList<Person>();
for (int i = 0; i < 10; i++) {
l.add(generatePerson());
}
Iterable<Person> saved = persons.saveAll(l);
var ids = new LinkedList<Long>();
for(var p : saved)
ids.add(p.getId());
Iterable<Person> found = persons.findAllById(ids);
assertThat(found).size().isEqualTo(ids.size());
}
@Test
void listAll() {
System.out.println("Test startedß");
Iterable<Person> all = persons.findAll();
assertThat(all).isNotEmpty();
}
@Test
void findById() {
var saved = saveOne();
Optional<Person> found = persons.findById(saved.getId());
assertThat(found).isPresent();
}
@Test
void delete() {
var person = saveOne();
persons.delete(person);
Optional<Person> found = persons.findById(person.getId());
assertThat(found).isEmpty();
}
@Test
void deleteById() {
var person = saveOne();
persons.deleteById(person.getId());
Optional<Person> found = persons.findById(person.getId());
assertThat(found).isEmpty();
}
@Test
void update() {
Person saved = saveOne();
saved.setName("Updated " + saved.getName());
Person updated = persons.save(saved);
assertThat(updated.getName()).startsWith("Updated");
}
@Test
void findByName() {
var person = saveOne();
List<Person> list = persons.findByName(person.getName());
assertThat(list).size().isGreaterThanOrEqualTo(1);
}
@Test
void count() {
long count = persons.count();
assertThat(count).isGreaterThan(0);
}
private Person saveOne() {
Person newOne = generatePerson();
return persons.save(newOne);
}
private Person generatePerson() {
var newOne = new Person();
newOne.setName(Faker.instance().name().firstName());
newOne.setLastName(Faker.instance().name().lastName());
return newOne;
}
}
Conclusion
I tried to give you fundamentals of JdbcTemplate to implement your methods using SQL. It was a good case study for me. When I take a look how far Spring come to, it is suprising. We spent weeks even months to develop these functionalities in the past. Now it is so reliable to use Spring Data JPA and let it handle these codes.
PS: I made a small non-performant implementation for a method intentionally. You can comment if you want to emphesize it.
