I was using Spring Data JPA with Oracle as part of an export process from CouchDB to Oracle. The CouchDB export application first exports my existing Places data from CouchDB, then maps it to the entity model I am using for JPA/Oracle, then persists it into Oracle, and finally performs some rudimentary listing of the Oracle data by logging it.
Some issues/points came to light during this excercise which I am noting here:-
1/ My connect string to Oracle was using a Pluggable database in Oracle XE. This needs to use a service name (xepdb1) rather than a SID (xe) for the connection. To do this I needed to use a slash before the service name in the application properties, to indicate a service name, rather than the colon I would normally use for a SID:
# Oracle settings
spring.datasource.url = jdbc:oracle:thin:@localhost:1521/xepdb1
spring.datasource.username = places
spring.datasource.password = xxxxxxxx
2/ I changed the Hibernate naming strategy in the application properties to prevent unwanted snake casing of Oracle column names:
# Hibernate settings
spring.jpa.hibernate.naming.physical-strategy = org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
3/ For persisting to Oracle, I had the choice of using Spring Data JPA, calling save() or saveAll() on the Individual Repository interfaces that extended CrudRepository (such as PlacesRepository), or I could use the Entity Manager. The Spring Data methods would persist or update fine, and could also save an iterable in one call. However, I had some small additional logic as I was passing and returning a Map. As I chose not to put this logic in the service layer, I therefore used an ordinary JPA Dao (repository) class, and autowired the entity manager via the @PersistenceContext annotation. I could therefore incorporate my logic in a single call and use entityManager.merge() to persist. I felt that situations vary and that it was not always appropriate to use Spring Data Interfaces for some use cases. However the Spring Data find queries were useful and clever for querying the database as they allowed for example a find by key with a like, and order by, just by declaring the appropriate method calls in the interface. I also used the @Query annotation for a custom query, which again was just annotated on a method in the interface.
4/ I had issues whereby the foreign keys on dependant entities were not being auto populated and threw up as null, even when I set nullable=false on the @JoinColumn annotations. In the end it was a simple case of not populating the objects in my model on both sides of the join before persisting – i.e. I was populating a list in a parent object for a one-to-many, but not populating the parent in the dependant object. Doing this resolved the problem and everything persisted automatically with a single parent persist/merge, with hibernate allocating primary keys via Oracle sequences, and then auto populating them in the foreign keys. This stack overflow post here pointed me in the right direction on this. I kept the nullable=false annotations where appropriate to correctly match the relationship I was using, and what was in Oracle.
5/ Once I was at the point of listing everything I had persisted, using Spring Data queries and some simple logging, I was initially hit with Hibernate multiple bag fetch exceptions when listing the data. At this stage I had annotated my repository methods as @Transactional rather than the service layer, and was therefore using explicit eager loading annotations as I needed everything to load before the methods returned. This post here details the issue, and it appears that Hibernate can only eagerly load one collection at a time. My solution to this was to take the more normal route of annotating my service methods as @Transactional rather than the repository, which then allowed a number of repository calls to be made from a single service method, all in the same transaction. Whilst this did expose the service layer to the transaction architecture and javax.transaction package, it is the more normal approach and gives flexibility in the service layer. In my case I could then just revert to the default lazy loading and perform the logging in the same service layer method (which I was happy to do as this was a very basic example just to demonstrate fetching the imported data from Oracle). Everything then worked fine.
