This post can have multiple titles which I’m going to list, but the main task at hand is to retrieve non-column Entity fields from a JPA Query.
Here are those alternative titles and issues we’ll be covering in this post.
- How to Retrieve Data from a SQL View
- Using @SqlResultSetMapping and @ConstructorResult in Spring JPA
- How to Serialize a ZonedDateTime Datatype in a JPA Query
- How to Serialize a TEXT (or CBLOB) H2 Datatype in a JPA Query
The SQL View
Let’s start with the SQL View. Pretty straightforward. We’re building a Flashcard feature with each flashcard being assigned a category and want to display the Category Name in a list.
Our CategoryName is a @Transient field in our Flashcard Entity.
@SqlResultSetMapping and @ConstructorResult
We’re going to populate our custom set of properties with a ResultSet Map and unique Constructor, or @SqlResultSetMapping and @ConstructorResult. We add those to the definition of our Flashcard Entity. Notice the type definitions where needed, or when the SQL column datatype is different than the Model property type. The “name” values are the names of the SQL columns.
The Constructor we created to accommodate the @ConstructorResult.
Using the SQL Result Mapping
Here’s our Service layer method. “SELECT * FROM v_flashcards.” Cool! “FlashcardsWithCategory” is the name of our @SqlResultSetMapping if you look above.
Serializing the ZonedDateTime field in a JPA Query
The datetimeCreated field is populated automatically with a Spring JPA AuditingEntityListener, which we’ve customized to enter a ZonedDateTime field. This could be problematic when converting from the SQL Timestamp datatype, so as you can see above, the solution is to assign the SQL datatype in the @ColumnResult as a PersistentZonedTimeDate class.
Serializing a CBLOB H2 Datatype
Everything was working great in our custom mapping and constructor setup until we ran some JUnit tests and hit against our H2Database data. The “content” field is a MySQL TEXT datatype and presented no problems, but in H2 a TEXT datatype is actually a CBLOB. Yeah, whatever.
The solution to this little snafu was to define the “content” Flashcard column as a big VARCHAR().