Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Works as Designed
    • Affects Version/s: 1.11.8 (Ingalls SR8)
    • Fix Version/s: None
    • Component/s: None
    • Labels:

      Description

      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

        Attachments

        1. data-jpa01.png
          data-jpa01.png
          61 kB
        2. data-jpa02.png
          data-jpa02.png
          165 kB
        3. data-jpa03.png
          data-jpa03.png
          49 kB
        4. data-jpa05.png
          data-jpa05.png
          252 kB
        5. data-jpa06.png
          data-jpa06.png
          67 kB
        6. data-jpa07.png
          data-jpa07.png
          21 kB
        7. data-jpa08.png
          data-jpa08.png
          61 kB
        8. data-jpa09.png
          data-jpa09.png
          61 kB

          Activity

            People

            • Assignee:
              schauder Jens Schauder
              Reporter:
              thomas.lang@th-deg.de Thomas Lang
              Last updater:
              Jens Schauder
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: