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.
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.
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.
Here's our Service layer method. “SELECT * FROM v_flashcards.” Cool! “FlashcardsWithCategory” is the name of our @SqlResultSetMapping if you look above.
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.
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().