Naming issue with native queries [DATAJPA-1225]

[spring-projects-issues]

Thomas Lang opened DATAJPA-1225 and commented

Hello there, my problem is generally described by this issue which has already been closed.

Please see the comments!
https://jira.spring.io/browse/DATAJPA-980

Issue:
The naming gets confused when doing a nativequery:

Please note:

as invoiceId

from the following query should map to the corresponding

long getInvoiceId();

property from the projection interface.
But postgres ignores the naming and names it invoiceid, which does not have a matching property.

    /**
     * Does a native query to get the invoices assigned for the given users and groups coded in the similarToPattern
     *
     * @param similarToPattern the similar to pattern (postgres specific as "%tlang%|%Seminare%"
     * @param state            a given invoice state
     * @return Iterable of InvoiceProjection
     */
    @Query(value = "select i.invoice_id as invoiceId, i.booking_tag as bookingTag, i.invoice_number as invoiceNumber, " +
            "i.created_by as createdBy, i.invoice_api_groups as groups, i.invoice_date as invoiceDate, r.recipient as recipient " +
            "from invoice i inner join recipient r on (i.recipient_recipient_id = r.recipient_id) where i.invoice_state = ?2 " +
            "and i.invoice_api_groups similar to ?1 order by i.invoice_date desc", nativeQuery = true)
    Iterable<InvoiceProjection> findAllProjectedByInvoiceStateAndSimilarUserOrApiGroup(String similarToPattern, int state);

To use the right naming scheme (because i use a projection interface which is already existing - see above) i have to use double quotes in postgres on the column aliasing as suggested by Jens Schauder in the comments (the closed issue) and here: https://stackoverflow.com/questions/20878932/are-postgresql-column-names-case-sensitive

So how do i use/escape double quotes within the data jpq query?
The query should be the following, but in that way the compiler does not accept this quoting

/**
 * Does a native query to get the invoices assigned for the given users and groups coded in the similarToPattern
 *
 * @param similarToPattern the similar to pattern (postgres specific as "%tlang%|%Seminare%"
 * @param state            a given invoice state
 * @return Iterable of InvoiceProjection
 */
@Query(value = "select i.invoice_id as "invoiceId", i.booking_tag as "bookingTag", i.invoice_number as "invoiceNumber", " +
        "i.created_by as "createdBy", i.invoice_api_groups as "groups", i.invoice_date as "invoiceDate", r.recipient as "recipient" " +
        "from invoice i inner join recipient r on (i.recipient_recipient_id = r.recipient_id) where i.invoice_state = ?2 " +
        "and i.invoice_api_groups similar to ?1 order by i.invoice_date desc", nativeQuery = true)
Iterable<InvoiceProjection> findAllProjectedByInvoiceStateAndSimilarUserOrApiGroup(String similarToPattern, int state);

Thank you all for your outstanding work on data jpa and your help!
Kind regards from a fan :)
Thomas

---

Affects: 1.11.8 (Ingalls SR8)

Reference URL: https://jira.spring.io/browse/DATAJPA-980

Attachments:

• data-jpa01.png (60.59 kB)
• data-jpa02.png (164.80 kB)
• data-jpa03.png (49.34 kB)
• data-jpa05.png (251.73 kB)
• data-jpa06.png (67.45 kB)
• data-jpa07.png (21.48 kB)
• data-jpa08.png (60.97 kB)
• data-jpa09.png (60.73 kB)

[spring-projects-issues]

Robert Hunt commented

Surely you just need to use the standard backslash escaping that you would use in a regular String? There is nothing special going on here, it's just a String value that you're passing to the @Query annotation. EG:

@Query(value = "select i.invoice_id as \"invoiceId\", i.booking_tag a...

[spring-projects-issues]

Jens Schauder commented

Rubert Hunt should be correct. Thomas, can you please confirm?

[spring-projects-issues]

Thomas Lang commented

Sorry, i cannot confirm. Maybe the error images attached can give you guys some hints.
Do you guys need some further information?

[spring-projects-issues]

Thomas Lang commented

By the way, here is my projection interface:

/**
 * Projection interface for spring data jpa.
 * takes the resultset of a spring data jpa query.
 *
 * @author tlang
 */
public interface InvoiceProjection {

    /**
     * The invoice id.
     *
     * @return long
     */
    long getInvoiceId();

    /**
     * The booking tag.
     *
     * @return String
     */
    String getBookingTag();

    /**
     * The invoice number.
     *
     * @return String
     */
    String getInvoiceNumber();

    /**
     * The invoice date.
     *
     * @return LocalDate
     */
    LocalDate getInvoiceDate();

    /**
     * The invoice recipient.
     *
     * @return String
     */
    String getRecipient();

    /**
     * The invoice state
     *
     * @return InvoiceState
     */
    InvoiceState getInvoiceState();

    /**
     * The created by property.
     *
     * @return String
     */
    String getCreatedBy();

    /**
     * The modified by property.
     *
     * @return String
     */
    String getModifiedBy();
}

[spring-projects-issues]

Jens Schauder commented

So from the screen shots I deduce:

1. the correct capitalized SQL statement gets send to Hibernate.
2. the proxy representing the projection contains the wrong capitalization (all lower case)

I see 3 potential points of failure here:

a) the JDBC driver doesn't return the correct capitalization
b) Hibernate does something funny
c) Spring Data does something

I just verified that the JDBC driver of Postgres does return alias names correctly capitalized. (Version org.postgres:postgres:42.0.0)

[spring-projects-issues]

Thomas Lang commented

Dear Jens,

thank you for your help. Please note that Oliver Gierke adviced me to do some debugging in the closed #980 here:
https://jira.spring.io/browse/DATAJPA-980?focusedCommentId=155213&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-155213
Please see the image attached (data-jpa05.png) for the details.

Maybe this helps finding the issue.

Thank you!
Kind regards!
Thomas

[spring-projects-issues]

Jens Schauder commented

If I interpret your screenshot correctly we are getting the wrong capitalization from Hibernate.

Then this is a Hibernate or a Postgres issue. Could you please check if your driver returns correct capitalized column names?
For this just get a JdbcTemplate injected and execute a simple query containing a double quoted alias with upper case letters using queryForMap, and inspect the keys in the resulting Map.

If they contain upper case letters file an issue against Hibernate.
If not file an issue against Postgres and/or upgrade to the version I mentioned above.

Please leave a link to the issue so we can track what is going on on the other side

[spring-projects-issues]

Thomas Lang commented

Dear Jens,

i have just uploaded a screenshot of what you suggested.
The List of Maps (i had to use the "queryForList" function) contains the columns with the right naming.

So i will file an issue against Hibernate.
I will provide the link here as soon as i have it done.

Thank you for your help.
Kind regards
Thomas

[spring-projects-issues]

Thomas Lang commented

Hello there,

i have just filed a hibernate issue:
https://hibernate.atlassian.net/browse/HHH-12119

By the way:
The strange thing is that non native queries gets mapped the right way.
But this just as a side note!

Again - Well done guys and girls!
It makes fun to work with spring data jpa.

Kind regards from a fan!
Thomas

[spring-projects-issues]

Thomas Lang commented

Hey there,

it´s me again.
The folks at hibernate have stated that the issue is " ... because Spring Boot overrides the PhysicalNamingStrategy and ImplicitNamingStrategy with their own implementations."

See it here:
https://hibernate.atlassian.net/browse/HHH-12119?focusedCommentId=100601&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-100601

Can you guys confirm that?
Is there anything else that i can give you on information?

Kind regards!
Thomas

[spring-projects-issues]

Jens Schauder commented

Not a Boot person, but it does look like the mentioned strategies are used.

You should be able to override them (see https://stackoverflow.com/questions/41918622/change-hibernate-5-naming-strategy-spring-boot-anootation) and provide a physical naming strategy derived from SpringPhysicalNamingStrategy but returning true for isCaseInsensitive.

Or you can just register an appropriate bean. https://docs.spring.io/spring-boot/docs/current-SNAPSHOT/reference/html/howto-data-access.html#howto-configure-hibernate-naming-strategy

Let us know if that works.

In the meantime, I'll try to find out why that naming strategy exists in this form

[spring-projects-issues]

Thomas Lang commented

Hey there Jens,

thank you for your help.
I am about to try this suggestion out.

I will let you know if that works.

By the way:
Without configuring a naming strategy, Spring Data JPA (with Spring Boot) maps a Property called

@Id
@GeneratedValue
@NotNull
private long invoiceId;

to a database (postgres) column named:

invoice_id

Generally i don´t care how the or mapper or any other tool will name my columns in the datasource.
But intuitively i would expect a naming like invoiceid in the database.

I have other Spring Data JPA Projects which are not Spring Boot based (but "the old" Spring way).
These namings gets not confused, meaning that the above property gets mapped to invoiceid.
That just as a side note.

I will let you know, if the suggestion from you respectively the hibernate guy will work.
Thank you for your help.

Just another question, maybe a little off topic here, but anyway :)
Have you guys considered bringing/port the cool Spring Data JPA to the .NET world?
If so, a lot of .NET developers will surely love it/be curious about it.
There is entity framework core/entity framework legacy already but this is by far not so cool as Spring Data JPA.

Kind regards from a fan.
Thomas

[spring-projects-issues]

Jens Schauder commented

About the underscore: I guess what one would expect/like depends on your upbringing. Without underscores, the names get really confusing, especially if you have long property names.

regarding "the old way": For one Spring Boot does register these naming strategies so yes it changes the behavior from vanilla Hibernate.
Also with Hibernate 5 they split their naming strategy in two (implicit and physical) and I guess in the progress the the physical even gets applied to properties which have a column name configured via @Column and as it seems even to native SQL queries. So now the NamingStrategies seem to have a broader effect.

Regarding .NET: There is a Spring .Net which seems to be dead (and I don't know who maintained it). Spring Data for .NET seems not realistic at the moment. Since one would need a Spring Data Commons for .Net which would need a Spring Framework for .NET

[spring-projects-issues]

Thomas Lang commented

Hey there Jens,

thank you for your reply.
I´ve tried your suggestion and it did not work.
I have the following:

Settings:

spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyJpaCompliantImpl
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl

Repository:

Query(value = "select i.invoiceid as invoiceId, i.bookingtag as bookingTag, i.invoicenumber as invoiceNumber, i.createdby as createdBy, i.invoiceapigroups as apiGroups, i.invoicedate as invoiceDate, r.recipient as recipient from invoice i inner join recipient r on (i.recipient_recipientid = r.recipientid) where i.invoicestate = ?2 and i.invoiceapigroups similar to ?1 order by i.invoicedate desc", nativeQuery = true)
Iterable<InvoiceProjection> findAllProjectedByInvoiceStateAndSimilarUserOrApiGroup(String similarToPattern, int state);

Hibernate logs:

2017-12-07 13:55:55.478 DEBUG 6784 --- [lTaskExecutor-2] org.hibernate.SQL                        : select i.invoiceid as invoiceId, i.bookingtag as bookingTag, i.invoicenumber as invoiceNumber, i.createdby as createdBy, i.invoiceapigroups as apiGroups, i.invoicedate as invoiceDate, r.recipient as recipient from invoice i inner join recipient r on (i.recipient_recipientid = r.recipientid) where i.invoicestate = ? and i.invoiceapigroups similar to ? order by i.invoicedate desc

But the materialization is still wrong.
!data-jpa07.png|thumbnail!

Please note:
When i do the same query not native and use the same projection interface, then the naming gets not confused.

Did i miss something?
Thank you.
Kind regards.
Thomas

[spring-projects-issues]

Thomas Lang commented

Dear Jens,

i´ve quickly tailored a test repository.
https://mygit.th-deg.de/tlang/spring-data-jpa-1225.git

You may use this to locate the error.
There is a readme where you can find instructions.
I´ve included a minimal database dump to be used.

Please let me know if there are cloning issues.
Thank you for your help.
Kind regards.
Thomas

[spring-projects-issues]

Thomas Lang commented

... it´s me again ...

I did some debugging into spring data jpa´s internals as Oliver Gierke advised me to do some weeks ago:
https://jira.spring.io/browse/DATAJPA-980?focusedCommentId=155213&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-155213

This is the result from the non native query projection - the naming is right here:
!data-jpa08.png|thumbnail!

This is the result from the native query projection - the naming is wrong here:
!data-jpa09.png|thumbnail!

Maybe this helps.
Thank you.
Kind regards.
Thomas

[spring-projects-issues]

Jens Schauder commented

Hi Thomas,
the example repository you linked to, doesn't contain any Spring dependencies.

Also since it just performs a select it doesn't tell us anything about the resulting column names.

I tried to reproduce the problem with a separate project but failed to reproduce the issue.

Please provide a complete example, which includes the setup (i.e. creates the necessary tables and inserts data as necessary)

[spring-projects-issues]

Thomas Lang commented

Hello Jens,

thanks again for your help.

I have updated the repository.

I use a maven project which itself uses the spring starter platform. All dependencies will be pulled from that. If you look at the effective pom (Eclipse or Intellij) you can see all Dependencies used. I don´t know how to provide other projects, since i´m just familiar with maven and spring starter.

I have switched the
spring.jpa.hibernate.ddl-auto=create-drop
so you get every test run a new database schema and data.

Further all you need for the test project to run is a postgres (9.4 or higher) database engine to connect to (preferably on localhost to set a simple user/password combination).
It has to be postgres (no in-memory) because i use the postgres specific similar to construct.
Currently it is located at
spring.datasource.url=jdbc:postgresql://localhost:5432/rechnung-neu

When you run the test the command runner creates one invoice and then selects it. The select fails, because of the naming issue.
I hope this helps you. I am a little puzzled at the moment, because this is very strange to me ...
Please let me know if i can do anything to help you.

Thank you!
Thomas

By the way.
If you want to see the problem via teamviewer and talk to each other via skype, we could do a session as well. Just let me know :) Thank you

[spring-projects-issues]

Jens Schauder commented

Based on you repostiories I created two new ones which in my opinion demonstrate:

• mixed case names for native queries don't seem to work as expected in hibernate
• this leads to the effect described above for Spring Data JPA projections
• this is not related to anything Boot does, since the Hibernate test case has no Spring dependencies.

Repository for reproducing this issue: https://github.com/schauder/issue-DATAJPA-1225

Repository for reproducing the Hibernate issue: https://github.com/schauder/issue-HHH-12119

I'll update the hibernate issue.

[spring-projects-issues]

Thomas Lang commented

Hey there Jens,

thank you for your help.
The hibernate guys will provide a fix for that in 5.2.13.
I will try this out when the version is available once again.

Good job.
Have a nice day.
Kind regards
Thomas

[spring-projects-issues]

Jens Schauder commented

Closing, since everybody seems to agree now that this is a Hibernate issue