How to cast a column to another data type

Hibernate ORM
1

I’ve been dealing with a problem in my application that has me forced to change the HibernateQuery in a way that casts the existing DateTime column I have within my database into a date for further processing.

I’ve looked around a little on here already and found a supposed solution using sqlGroupProjection which ended up looking like this for me.

Projection dp = Projections.sqlGroupProjection("CAST("+sp.getIname()+" AS DATE",
                                               "CAST("+sp.getIname()+" AS DATE",
                                               new String[] {sp.getIname()},
                                               new Type[] {StandardBasicTypes.DATE});

This does work to some the extent that it now generates this in the SQL statement

CAST(ierstelltAm AS DATE)

Which would be correct if it wasn’t for the fact that something is now missing. Usually when Hibernate generates these statements they’re given unique identifiers, one of them being y[i]_ and the other somehow relating to the table/entity used in the syntax of entity[i]_.

Since these are all inbuilt hibernate functions I had expected the above method to generate something along these lines

CAST(auftrag8_.IErstelltAm AS DATE) as y1_

based on the fact that the code previously generated using the criteria system did look like this.

auftrag8_.IErstelltAm as y1_

The question is now how do I correctly cast something to another data type using hibernate criterie, if it’s not the sqlGroupProjection method mentioned in other places – or is this a syntactical/logical error on my part?

2

I don’t quite understand the issue. Do you have a query error or are you receiving wrong results? Either way, I would recommend you switch to using the JPA Criteria API as the legacy Hibernate Criteria API is going away in 6.0

3

I’m not getting wrong results per say. I do get the correct results for what code is generated but what is generated is not what I need.
We have a database field that is saved as a datetime which is, for most purposes in the application the correct way to store it.
For in-application data analysis purposes however it is direly needed that this is casted to a date during the runtime so only the relevant part of the datetime is considered while Hibernate compares the queried data for aggregation and for that I end up needing a way to cast this column to the date datatype during runtime.

4

I still don’t understand what it is that you want. Please formulate this in the following form:

  • What do you do i.e. the code, model that you are using
  • What do you expect i.e. what is the outcome/result/query that you would expect
  • What is the actual/unexpected behavior

You can certainly do casts with Hibernate, but you have to formulate a proper question.

5
  • What do you do i.e. the code, model that you are using
    The code I’m using is the one shared in the post above.
Projection dp = Projections.sqlGroupProjection("CAST("+sp.getIname()+" AS DATE",
                                               "CAST("+sp.getIname()+" AS DATE",
                                               new String[] {sp.getIname()},
                                               new Type[] {StandardBasicTypes.DATE});

It is later added to a list of Projections via something like this

p = p.add(Projections.alias(dp, sp.getIbezeichnung()));

  • What do you expect i.e. what is the outcome/result/query that you would expect
    The outcome I wish for is that the generated result from “dp” would seamlessly fit into this ProjectionList and offer to bring the expected result that the result will have the datatype of the column this is exectued for casted to a Date.

  • What is the actual/unexpected behavior
    The unexpected behaviour is that while I do get a piece of code added to the ProjectionList that would work standalone it doesn’t really do it here. The numbered alias y[i]_ Hibernate usually gives to these kind of things is missing in this script. Also missing is the specific table from which the column is from which makes the script fail to execute.

6

First of all, the SQL fragment you are passing is not correct. You are missing a closing parenthesis. Next, the aliases that it should use is provided by you through the third parameter new String[] {sp.getIname()}. As far as I understand, the alias has to be included in the SQL fragment by you. If you want the table alias to be prefixed, you can use the placeholder {alias}. I think this should look like the following:

Projection dp = Projections.sqlGroupProjection("CAST({alias}."+sp.getIname()+" AS DATE) as "+sp.getIname(),
                                           "CAST({alias}."+sp.getIname()+" AS DATE)",
                                           new String[] {sp.getIname()},
                                           new Type[] {StandardBasicTypes.DATE});