2

I am doing a SELECT query, using a Hibernate native query, like below:

public List<SomeDTO> findDTOs() {
    StringBuilder sql = new StringBuilder();
    sql.append(" SELECT id as id, localDateColumn as date, ");

    sql.append(" FROM t");
    Query query = entityManager.createNativeQuery(sql.toString());
    query.unwrap(SQLQuery.class)
        .addScalar("id", StandardBasicTypes.LONG)
        .addScalar("localDateColumn", DateType.INSTANCE)
        .setResultTransformer(Transformers.aliasToBean(SomeDTO.class));
    return query.getResultList();
  }

I tried using StandardBasicTypes.DATE or DateType.INSTANCE which does not work. I know that it works fine to map java.sql.Types#DATE and java.util.Date.

I would like to know how would I map java.sql.Types#DATE to java.time.LocalDate.

Improve this question

2 Answers 2

Reset to default
2

There are many things which are wrong with your approach.

Risking SQL Injection attacks

The way you are building the SQL using String concatenation is very risky and can expose your application to SQL injection attacks.

StringBuilder sql = new StringBuilder();
sql.append(" SELECT id as id, localDateColumn as date, ");

sql.append(" FROM t");
Query query = entityManager.createNativeQuery(sql.toString());

More, it's not even correct since you append a , prior to the FROM clause. You need to use Criteria API if you want to build queries dynamically.

Aliases are not used properly

You set the localDateColumn column to the date alias like this:

sql.append(" SELECT id as id, localDateColumn as date, ");

But then you use the localDateColumn when setting the Hibernate Type using addScalar:

.addScalar("localDateColumn", DateType.INSTANCE)

Now, according to your question title, if the localDateColumn is a MySQL DATE column type, and you want to map it to a LocalDate Java Object, you should do it like this:

.addScalar("date", LocalDateType.INSTANCE)
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I just used dummy SQLs to simply explain the approach, please ignore syntax or aliases, question is about mapping the date type. The suggested solution return java.util.Date but I wanted it to convert into java.time.LocalDate. Please see my answer.
If you use .addScalar("date", LocalDateType.INSTANCE), you should get that.
And you should definitely read my answer carefully because you have even bigger problems in your code than what you initially posted the question for.
1

Since I am not using Entity for return data type. AttributeConverter is not useful in this case. I resolved this by manually converting the java.util.Date into java.time.LocalDate in the setter of the field.

Setter will look like this:

public void setLocalDateColumn(java.util.Date value){
    // convert into LocalDate
    // set
}

If you get UnsupportedOperationException while converting. You might still getting java.sql.Date here wrapped in java.util.Date. Do it like: Instant.ofEpochMilli(value.getTime()).atZone(DefaultZoneId).toLocalDate();

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.