Category Archives: hibernate

Mapping Entity relationships with JPA annotations

We will look at some practical examples of how to wire in Entity relationships using standard JPA annotations.

Generating Primary Key Id from Sequence

Consider the below SQL:

I would like to auto-generate the ID of GENRE from the SEQ_GENRE_ID.

This is how my Entity would look like:

Here, the @SequenceGenerator contains the name of the sequence used for ID generation. This can be ignored for DB like MySQL, which has support for AUTO GENERATION at the table level itself. The @GeneratedValue has the strategy used to generate the value.

Simple One to One Relationship

Consider the below tables:

Chapter

Section

The simple One-to-One can be wired up by using the below 2 annotations:

@OneToOne(fetch = FetchType.EAGER)
@JoinColumn(name = “content_id”)
private Section contents;

Specify the name of the column in the CHAPTER table which is the FOREIGN KEY to the SECTION table.

One to Many Relationship using a Mapping table

Consider the below entity:

In terms of SQL, we can have a main table called BOOK and then a mapping table called BOOK_GENRE. This mapping table would contain the IDs of BOOK and GENRE tables.

This relationship can be represented by:

@OneToMany(fetch = FetchType.EAGER)

@JoinTable(name = “BOOK_GENRE”, joinColumns = @JoinColumn(name = “book_id”, referencedColumnName = “id”), inverseJoinColumns = @JoinColumn(name = “genre_id”, referencedColumnName = “id”))

The @JoinTable takes in the name of the Mapping table. It has the below 2 attributes:

  1. joinColumns: You need to provide the FOREIGN KEY from the owning part of the relationship, in this case,  BOOK
  2. inverseJoinColumns: You need to provide the FOREIGN KEY of the non-owning side of the entity, in this case, GENRE

Saving Entity relationships

By default, none of the Entity relations are inserted or updated. You need to explicitly specify the cascade attribute in the OneToOne or OneToMany annotation.

@OneToOne(fetch = FetchType.EAGER, cascade = { CascadeType.PERSIST, CascadeType.MERGE })
@JoinColumn(name = “main_chapter_id”)
private Chapter mainChapter;

If you do not want to save the Entity, do not specify the cascade attribute:

@OneToOne(fetch = FetchType.EAGER)
@JoinColumn(name = “author_id”)
private Author author;

Sources

A working example can be found here:

https://github.com/paawak/blog/tree/master/code/one-to-many-example-2

How Spring-JPA sucks big time

Ok, so all I am trying to do is save a new Entity into the DB and then retrieving it using its ID.

Saving my Entity

The entity Book contains an Author, some Chapters and some Genres as shown below:

While saving, the assumption is that the Author and the Genres are already present in the DB. While saving a new Book, I pass all the attributes of the Book and the Chapters. However, since the Author and the Genres are already present in the DB, I am passing their IDs only and not all of their attributes; pretty much like a Foreign Key. I am illustrating this with the below JSON:

Note that for author and genres, we are only passing their IDs and no other attributes.

Fetching the Entity I just now saved

Now, after saving the new Book, when I fetch, my expectation is that Spring-JPA fetches the entire object graph faithfully, without missing any attributes. In reality, it never even bothers to hit the DB with a query, but returns the Book from the Session Cache itself. So, when I fetch my newly saved Book, only the IDs are fetched for Author and Genre, and no other attribute.

Implementation details with Spring JPA

Repository Layer

The interface BookDao defines the contract.

The implementation looks like:

Service Layer

Note that the Transactions are started in the Repository layer. So, when I do a saveOrUpdate() in my service, there are 2 transactions that are happening. However, despite that, the Book is returned from the Session Cache and I get an incomplete object graph. Spring JPA does not give me much leverage to clear or evict or refresh the Session Cache after the insert happens.

Implementing with standard JPA

This can be handled better with the plain vanilla JPA. This is how the Repository looks like:

The below line is particularly important in the method getBook(Long bookId):

entityManager.refresh(book);

Without the above line, we will still get an incomplete object graph.

Sources

An working example of this can be found here:

https://github.com/paawak/blog/tree/master/code/one-to-many-example-2

JPA: Creating EntityManager without persistence.xml

For JPA to work, we need to define a persistence.xml inside META-INF. I always found it pretty cumbersome and difficult to maintain. Especially so for my integration tests. Wouldn’t it be cool if we could do everything from Java code?

Here you go:

Using only Hibernate

Using Spring-JPA

Note that you can add your entities through code. In order for this to compile, you would need to include the below dependencies in maven:

The complete sources can be found here:

https://github.com/paawak/blog/tree/master/code/one-to-many-example-2

This particular Java file is:

https://github.com/paawak/blog/blob/master/code/one-to-many-example-2/src/test/java/com/swayam/demo/spring/springbootdemo/JpaIntegrationTest.java

[Example] Hibernate One-To-Many bidirectional relation

Let us consider the relation between Semester and Subjects. A Semester has a collection of Subjects. Each Subject, in turn, has a reference to the Semester.

This is illustrated as below:

This is how I convert it to Hibernate entities:

Further, I should be able to insert it with the following code:

The SQL script is:

The sources can be found https://github.com/paawak/blog/tree/master/code/OneToManyExample.

[How To] Handle Oracle TimeStamp with TimeZone from Java

Oracle TimeStamp with TimeZone: A Critical Analysis

For storing the TimeZone information in TimeStamp, Oracle (10g) has two options:

  1. TIMESTAMP WITH TIME ZONE data type:  As the name indicates, this stores the TimeZone data along with the TimeStamp. The TimeZone can be either stored in the UTC format (eaxmple: +05:30) or the TimeZoneRegion format (example: Asia/Calcutta or Indian Standard Time).
  2. TIMESTAMP WITH LOCAL TIME ZONE data type: This does not store the TimeZone information per se. While inserting data, it adjusts for the offset of the client’s TimeZone and stores it according to the db server’s local TimeZone, without the actual TimeZone information. Similarly, while querying, the TimeStamp will be offseted against the client’s TimeZone, without returning any explicit TimeZone information. This data type is very confusing and is to be avoided at all costs.

As far as JDBC is concerned, things are plain and simple when we talk about Statement. The trouble starts the moment we think about PreparedStatement. Both these data types map to the java.sql.Timestamp in JDBC. But java.sql.Timestamp does not have any TimeZone information. In this scenario, we should be using the PreparedStatement.setTimestamp(int parameterIndex, Timestamp x, Calendar cal) to pass the TimeZone information to the db driver. There is another twist to this tale. The TIMESTAMP WITH LOCAL TIME ZONE data type needs to adjust the time against the client’s TimeZone. When we open a Oracle client session, the TimeZone is already set. But that is not the case with JDBC Connection. The TimeZone info is not set by the driver, for reasons best known to themselves. So a simple ResultSet.getTimestamp(int columnIndex) will not work. Instead, we have to use the ResultSet.getTimestamp(int columnIndex, Calendar cal) to pass the TimeZone to the db driver.

Simple JDBC example

Consider the following table having both types of TimeStamp with TimeZone:

This is the SQL statement for inserting data:

INSERT INTO TIMESTAMP_DEMO (ID, NAME, TIME_WITH_ZONE, TIME_WITH_ZONE_LOCAL) VALUES (0, ‘manualInsert’, TO_TIMESTAMP_TZ(‘2010-09-26 11:30:00 Australia/Adelaide’,’YYYY-MM-DD HH24:MI:SS TZR’), TO_TIMESTAMP_TZ(‘2010-09-26 11:30:00 Australia/Adelaide’,’YYYY-MM-DD HH24:MI:SS TZR’));

We will try to insert data from plain JDBC and then read it back to ensure that the information persisted is correct.

INSERT Approach 1

Consider the following code snippet:

Now run the following query from any Oracle SQL Client:

SELECT TO_CHAR(TIME_WITH_ZONE, ‘YYYY-MM-DD HH24:MI:SS:FF TZR’) AS TIME_WITH_ZONE FROM TIMESTAMP_DEMO;

You will find that the returned value is:

2010-11-02 11:30:00:000000000 +05:30

Note that the TimeZone info is wrongly stored. The only conclusion that we can have is that the JDBC driver is buggy. How do we get around this problem?

INSERT Approach 2

On running the select statement, the data got back is:

2010-11-02 11:30:00:000000000 AUSTRALIA/ADELAIDE

This is precisely the data which was inserted. The trcik here is to bypass the JDBC data and use Oracle function TO_TIMESTAMP_TZ(timeString, format).

SELECT Approach 1

On running the above, we get the following exception:

java.sql.SQLException: The database session time zone is not set
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:110)

This is got on the line

res.getTimestamp(“TIME_WITH_ZONE_LOCAL”);

as the TimeZone is not set in the JDBC Connection object.

SELECT Approach 2

And this is how getTimeWithZone() looks like:

We get the following sysouts:

insert_1: 1, TIME=11:30:00:000 India Standard Time, TIME_LOCAL=06:00:00:000 India Standard Time
insert_2: 2, TIME=06:30:00:000 India Standard Time, TIME_LOCAL=06:00:00:000 India Standard Time

Note that in both cases, the returned TimeZone is wrongly fetched. Again, the JDBC driver is buggy.

SELECT Approach 3

As with INSERT, we will try to bypass the JDBC driver as follows:

The following sysouts are got:

insert_1: 1, TIME=11:30:00:000000000 +05:30, TIME_LOCAL=11:30:00:000000000 +05:30
insert_2: 2, TIME=11:30:00:000000000 AUSTRALIA/ADELAIDE, TIME_LOCAL=11:30:00:000000000 +05:30

Note that the values returned are exactly as intended.

Hibernate Example

Let us try and translate the above code into Hibernate. The first inference that we can draw from the above discussion is that get() on TIMESTAMP WITH LOCAL TIME ZONE will not work with the usual approach, and we have to define our custom data type to customise get() on PreparedStatement.

Hibernate Approach 1

This is how my custom data type (to map to TIMESTAMP WITH LOCAL TIME ZONE) looks like:

And this is how my entity looks like:

As expected, this will not save/get correct TimeZone data as it depends on the buggy JDBC driver.

Hibernate Approach 2

Here, as before, we use the TO_TIMESTAMP_TZ() function to circumvent the buggy JDBC driver. We keep the TimestampType for mapping to TIMESTAMP WITH LOCAL TIME ZONE and add TimestampType2 to map to TIMESTAMP WITH TIME ZONE.

And this is how my modified entity looks like:

This set up gives us the desirable save/get results with the correct TimeZone value.

Conclusion

I take this opportunity to illustrate the advantage of Open Source Software as opposed to proprietary ones. If the ojdbc sources were in public domain, we could have patched the bug instead of having to circumvent them as in the present instance.

Resources

The sources can be found https://github.com/paawak/blog/tree/master/code/OracleTimeZoneTest.

There are three test cases which illustrates the above discussion. I have used the ojdbc driver 5 for this example. Since I am using Maven for building this, the libraries are not a part of this distribution. You can grab any Oracle JDBC driver from here.

Using custom data type in Hibernate

Why need custom data type?

I have a java.util.Calendar object which I want to persist in the db, along with the TimeZone. Suppose I am working with a db which does not have support for storing TimeZone. How do I proceed? One of the easiest solutions would be to store the entire timestamp as a VARCHAR. Granted, but how do I instruct Hibernate to use a java.util.Calendar object instead of a String? Its done by annotating with @Type(type=””), where type should be the fully qualified name of a class implementing the org.hibernate.usertype.UserType interface.

Custom UserType

Hibenate Entity

Resources

The sources can be found https://github.com/paawak/blog/tree/master/code/HibernateCustomDataType

Id generation in Hibernate with Sequence

I have the following SQL Script:

The Hibernate entity for this would be:

Error in named query: org.hibernate.hql.ast.QuerySyntaxException: table is not mapped

I was taking advantage of the NamedQueries feature in Hibernate annotation, when suddenly I was bombed with this queer error:

ERROR – SessionFactoryImpl.<init>(363) | Error in named query: findMaxId
org.hibernate.hql.ast.QuerySyntaxException: AccountGroup is not mapped [SELECT MAX(id) FROM AccountGroup]

My AccountGroup looks like this:

As is evident from the error message, the HQL compiler is not able to map any table to “AccountGroup”. But this is strange, since I have already mapped it in my hibernate.cfg.xml. After a few minutes of futile googling, the solution dawned upon me.

Solution 1:

Include an additional annotation @Table:

Solution 2:

Modify your query to have the fully qualified class name:

Configuring connection pooling with Teneo

Connection pooling is one thing that needs to be done on a production server. Spring is becoming an extremely popular choice with Hibernate for its seamless integartion. But with Teneo, its a different ball-game altogether. Fortunately for us, Hibernate provides one such hook via the property in the Environment.CONNECTION_PROVIDER configuration. It takes in the name of a class implementing the interface ConnectionProvider.

I prefer the Apache Commons DBCP. You will find the example here. When configuring Teneo, you need to set:

MyConnectionProvider.java looks like: