0

I am writing a java application in which I am using Spring Boot and JPA in order to map classes to my database tables.

However, due to a somewhat complex database structure I also have the need of creating custom queries that are not mapped to any specific POJOs / Entities.

Therefore I am using PreparedStatement together with a DataSource with @Autowired annotation.

It hit me that using both of these DB Access methods might not be suitable to use together?

So far everything has worked out in my dev environment, but are there any pitfalls that I should look out for when using both of these together or is there a preferred way of doing custom queries when using JPA?

It should be noted that my database calls are fairly short and happen in a stateless manner, so there should hopefully not be any problems with interfering sessions (?)

user2868900
  • 721
  • 3
  • 9
  • 29

2 Answers2

2

JPA EntityManager will not know anything about your changes made with PreparedStatement. This will cause issues with JPA built-in caching, maybe with versioning and also with transaction support.

Though you may need to check this question: Is it OK to use both JPA (for normal CRUDs) and JDBC (for batch update & call stored proc) in the same project

Ivan
  • 8,508
  • 2
  • 19
  • 30
  • I see, that doesn't sound promising unfortunately. Is there any equivalent to `PreparedStatement` be for `EntityManager` since I am in dire need of creating these custom queries that are not mapped to a specific entity... – user2868900 Feb 27 '19 at 19:29
2

Invan's answer makes a clear point.

On the other hand your fine when:

  • you need complex queries to SHOW data (read only).
  • you infrequently need to do some batch updates and do a clear cache entityManager.getEntityManagerFactory().getCache().evictAll()
Dirk Deyne
  • 6,048
  • 1
  • 15
  • 32
  • Thank you! The queries are indeed only `SELECT` statements (i.e. no modifying existing data) and serializing the response to json. All writing to the DB is happening by using JPA / Hibernate. Do you think I will be fine using `PreparedStatements` for the select? – user2868900 Feb 27 '19 at 20:16
  • alternatively you could use spring's jdbctemplate – Dirk Deyne Feb 27 '19 at 20:21
  • Why do you want to use SQL when you have JPA? If you use JPA correctly it represents the _object_ model of your domain. If incorrectly, it represents the _data_ model instead. SQL always represents the data model. So which model drives your application? I recommend using the object model. Then you code what makes sense in the object model without worrying about data structures in the database at all. – Lew Bloch Feb 27 '19 at 22:12