4

I am quite familiar with the JdbcTemplate object of Spring (and with spring-jdbc in general), and I really like writing the SQL and dealing with the object mapping myself.

However recently I was playing with the CrudRepository interface and I really liked it.

That said, I still favor writing my own SQLs queries when it comes to a complex query (joins and so on).

I would like to be able to use the CrudRepository methods in a class that also has a JdbcTemplate instance, that way I could have the out of the box methods of CrudRepository while being able to write my own complex SQLs and use them with JdbcTemplate.

The problem is, of course, that CrudRepository is just an interface while JdbcTemplate is a regular class. So the question is

  1. Does any of what I am mentioning actually make sense?
  2. Assuming that answer to (1) is yes, how would I be able to have a class with an instance of a JdbcTemplate that also provides an implementation of CrudRepository without having to hack my way around it too much?
Juan Antonio Gomez Moriano
  • 13,103
  • 10
  • 47
  • 65

2 Answers2

3

Mixing JPA and JDBC is very well possible in any application. The JdbcTemplate can be used to write complex queries or do complexer custom mapping. Another option is to use a RowCallbackHandler (instead of a RowMapper to directly stream something to a file or other resource, to preserve memory).

When using Spring Data you can use the @Query annotation to write custom queries in a repository. This can be a native query or JPQL.

However there is nothing in Spring preventing you from using both in a single class. You could directly inject your repository into a service as well as the JdbcTemplate (or you could put that behind another facade if you like). 

@Service
@Transactional
public class YourService {

    private final YourCrudRepository repo;
    private final JdbcTemplate jdbc;

    public YourService(YourCrudRepository repo, JdbcTemplate jdbc) {
        this.repo=repo;
        this.jdbc=jdbc;
    }

    // Your methods go down here
}

Now you can use both. The nice thing about Spring is that regardless of using the CrudRepository or the JdbcTemplate it will throw the same exceptions, the DataAccessException. If they operate on the same DataSource a single JpaTransactionManager is enough to manage the connection and transaction.

M. Deinum
  • 115,695
  • 22
  • 220
  • 224
  • Is the jdbcTemplate aware of the entity manager 1st level cache and flushing strategy? I would doubt that. So this opens the room for inconsistent and unexpected behavior. Do you have more experience regarding this? – Mahatma_Fatal_Error May 20 '21 at 19:46
  • Ofcourse it isn't, so it is wise to do a `flush` before using the jdbc template (or you could use some AOP to automate this). Unless you are sure that everything already has been flused. – M. Deinum May 21 '21 at 09:07
0

Why not just use them as members?

public class MyDao {
    private JdbcTemplate template;
    private CrudRepository repository;
}

Of course you would need to pass through all methods, which is kind on noisy. Dao would be the stereotype Data Access Object.

cmoetzing
  • 742
  • 3
  • 16