在使用 PostgreSQL 执行 NamedNativeQuery 连接查询时，排除主键后遇到错误。

huangapple
go评论98阅读模式
英文:

While executing NamedNativeQuery Join query after excluding the primary key using postgresql facing error

问题

我不需要在结果输出中包含id（主键），但在查询和结果集中排除id后出现错误。org.postgresql.util.PSQLException: 在此结果集中未找到列名id。jpa，因此需要帮助格式化此实体类。

**注意：**可以假设已进行以下方法调用entityManager.createNamedQuery("getPublisherInfoList"，PublisherInfoResponseEntity.class).getResultList();

  1. @NamedNativeQueries(value = {
  2.     @NamedNativeQuery(
  3.         name = "getPublisherInfoList",
  4.         query = "SELECT publisher.publisher_name, publisher.contact_name, publisher.contact_email, publisher.contact_phone, publisher.managed_services, " +
  5.                 "annual.customers, annual.employees, annual.revenue " +
  6.                 "FROM publisher_portal.publisher_informations publisher JOIN publisher_portal.publisher_annual annual " +
  7.                 "ON publisher.publisher_name=annual.publisher_name",
  8.         resultSetMapping = "PublisherInfoResponseMappings"
  9.     ),
  10.     
  11.     @NamedNativeQuery(
  12.         name = "getPublisherByName",
  13.         query = "SELECT publisher.publisher_name, publisher.contact_name, publisher.contact_email, publisher.contact_phone, " +
  14.                 "publisher.managed_services, annual.customers, annual.employees, annual.revenue " +
  15.                 "FROM publisher_portal.publisher_informations publisher JOIN publisher_portal.publisher_annual annual " +
  16.                 "ON publisher.publisher_name=annual.publisher_name where publisher.publisher_name= :publisherName",
  17.         resultSetMapping = "PublisherInfoResponseMappings"
  18.     )
  19. })
  20.     
  21. @SqlResultSetMapping(name = "PublisherInfoResponseMappings", entities = {
  22.     @EntityResult(entityClass = PublisherInfoResponseEntity.class, fields = {
  23.         @FieldResult(name = "publisher_name", column = "publisher_name"),
  24.         @FieldResult(name = "contact_name", column = "contact_name"),
  25.         @FieldResult(name = "contact_phone", column = "contact_phone"),
  26.         @FieldResult(name = "contact_email", column = "contact_email"),
  27.         @FieldResult(name = "managed_services", column = "managed_services"),
  28.     })
  29. })
  31. @Entity
  32. @Table(name="publisher_informations",schema="publisher_portal")
  33. public class PublisherInfoResponseEntity {
  34.     
  35.     @Id
  36.     private Long id;
  37.     
  38.     private String publisher_name;
  39.     
  40.     private String contact_name;
  41.     
  42.     private String contact_phone;
  43.     
  44.     private String contact_email;
  45.     
  46.     private String managed_services;
  47.     
  48.     //setters and getters
  49.     
  50. }
英文:

I m not required id (primary key) in the resultant output but after excluding id in query and resultset getting error.org.postgresql.util.PSQLException: The column name id was not found in this ResultSet. jpa
SO need help in formatting this entity class.

Note: Assumption should be made like the below metthod call has been made entityManager.createNamedQuery("getPublisherInfoList",PublisherInfoResponseEntity.class).getResultList();

  1. @NamedNativeQueries(value = {
  2. @NamedNativeQuery(
  3. name = "getPublisherInfoList",
  4. query = "SELECT publisher.publisher_name,publisher.contact_name,publisher.contact_email,publisher.contact_phone,publisher.managed_services,\n" +
  5. "annual.customers,annual.employees,annual.revenue,"
  6. "FROM publisher_portal.publisher_informations publisher JOIN publisher_portal.publisher_annual annual\n" +
  7. "ON publisher.publisher_name=annual.publisher_name",
  8. resultSetMapping = "PublisherInfoResponseMappings"
  9. ),
  10. @NamedNativeQuery(
  11. name = "getPublisherByName",
  12. query = "SELECT publisher.publisher_name,publisher.contact_name,publisher.contact_email,publisher.contact_phone,\n" +
  13. "publisher.managed_services,annual.customers,annual.employees,annual.revenue,\n" +
  14. "FROM publisher_portal.publisher_informations publisher JOIN publisher_portal.publisher_annual annual\n" +
  15. "ON publisher.publisher_name=annual.publisher_name where publisher.publisher_name= :publisherName",
  16. resultSetMapping = "PublisherInfoResponseMappings"
  17. )
  18. })
  19. @SqlResultSetMapping(name = "PublisherInfoResponseMappings", entities = {
  20. @EntityResult(entityClass = PublisherInfoResponseEntity.class, fields = {
  21. @FieldResult(name = "publisher_name", column = "publisher_name"),
  22. @FieldResult(name = "contact_name", column = "contact_name"),
  23. @FieldResult(name = "contact_phone", column = "contact_phone"),
  24. @FieldResult(name = "contact_email", column = "contact_email"),
  25. @FieldResult(name = "managed_services", column = "managed_services"),
  26. })
  27. })
  28. @Entity
  29. @Table(name="publisher_informations",schema="publisher_portal")
  30. public class PublisherInfoResponseEntity {
  31. @Id
  32. private Long id;
  33. private String publisher_name;
  34. private String contact_name;
  35. private String contact_phone;
  36. private String contact_email;
  37. private String managed_services;
  38. //setters and getters
  39. }

答案1

得分: 0

看一下，如果你想在结果集中排除某些列，只需在实体类的特定列/字段上使用 @Jsongnore。

解决方案如下

在查询和结果集中包含主键，然后在主键上使用 @Jsongnore，以便准备好的结果集中不包含 id 字段。

注意 你可以在字段或者 getters 方法上应用 @Jsongnore

例如

  1. @NamedNativeQueries(value = {
  2.     @NamedNativeQuery(
  3.         name = "getPublisherInfoList",
  4.         query = "SELECT publisher.id,publisher.publisher_name,publisher.contact_name,publisher.contact_email,publisher.contact_phone,publisher.managed_services,\n" +
  5.                 "annual.customers,annual.employees,annual.revenue," +
  6.                 "FROM publisher_portal.publisher_informations publisher JOIN publisher_portal.publisher_annual annual\n" +
  7.                 "ON publisher.publisher_name=annual.publisher_name",
  8.         resultSetMapping = "PublisherInfoResponseMappings"
  9.     ),
  10.     @NamedNativeQuery(
  11.         name = "getPublisherByName",
  12.         query = "SELECT publisher.id,publisher.publisher_name,publisher.contact_name,publisher.contact_email,publisher.contact_phone,\n" +
  13.                 "publisher.managed_services,annual.customers,annual.employees,annual.revenue,\n" +
  14.                 "FROM publisher_portal.publisher_informations publisher JOIN publisher_portal.publisher_annual annual\n" +
  15.                 "ON publisher.publisher_name=annual.publisher_name where publisher.publisher_name= :publisherName",
  16.         resultSetMapping = "PublisherInfoResponseMappings"
  17.     )
  18. })
  19. @SqlResultSetMapping(name = "PublisherInfoResponseMappings", entities = {
  20.     @EntityResult(entityClass = PublisherInfoResponseEntity.class, fields = {
  21.         @FieldResult(name = "id", column = "id"),
  22.         @FieldResult(name = "publisher_name", column = "publisher_name"),
  23.         @FieldResult(name = "contact_name", column = "contact_name"),
  24.         @FieldResult(name = "contact_phone", column = "contact_phone"),
  25.         @FieldResult(name = "contact_email", column = "contact_email"),
  26.         @FieldResult(name = "managed_services", column = "managed_services"),
  27.     })
  28. })
  29. @Entity
  30. @Table(name="publisher_informations",schema="publisher_portal")
  31. public class PublisherInfoResponseEntity {
  32.     @Id
  33.     @Jsongnore
  34.     private Long id;
  35.     
  36.     private String publisher_name;
  37.     
  38.     private String contact_name;
  39.     
  40.     private String contact_phone;
  41.     
  42.     private String contact_email;
  43.     
  44.     private String managed_services;
  45.     
  46.     //setters and getters
  47. }

注意：假设已经使用 entityManager.createNamedQuery(..) 进行了操作。

英文:

See if you want to exclude some column in resulset simply use @Jsongnore on that particular
column/field in your entity class.

So the solution will be

Include Primary key in query and result set but then use @Jsongnore on primary key so that the prepared resultset won’t have the field id.

Note you can apply @Jsongnore either at field or at getters method

For Example

  1. @NamedNativeQueries(value = {
  2. @NamedNativeQuery(
  3. name = "getPublisherInfoList",
  4. query = "SELECT publisher.id,publisher.publisher_name,publisher.contact_name,publisher.contact_email,publisher.contact_phone,publisher.managed_services,\n" +
  5. "annual.customers,annual.employees,annual.revenue,"
  6. "FROM publisher_portal.publisher_informations publisher JOIN publisher_portal.publisher_annual annual\n" +
  7. "ON publisher.publisher_name=annual.publisher_name",
  8. resultSetMapping = "PublisherInfoResponseMappings"
  9. ),
  10. @NamedNativeQuery(
  11. name = "getPublisherByName",
  12. query = "SELECT publisher.id,publisher.publisher_name,publisher.contact_name,publisher.contact_email,publisher.contact_phone,\n" +
  13. "publisher.managed_services,annual.customers,annual.employees,annual.revenue,\n" +
  14. "FROM publisher_portal.publisher_informations publisher JOIN publisher_portal.publisher_annual annual\n" +
  15. "ON publisher.publisher_name=annual.publisher_name where publisher.publisher_name= :publisherName",
  16. resultSetMapping = "PublisherInfoResponseMappings"
  17. )
  18. })
  19. @SqlResultSetMapping(name = "PublisherInfoResponseMappings", entities = {
  20. @EntityResult(entityClass = PublisherInfoResponseEntity.class, fields = {
  21. @FieldResult(name = "id", column = "id")
  22. @FieldResult(name = "publisher_name", column = "publisher_name"),
  23. @FieldResult(name = "contact_name", column = "contact_name"),
  24. @FieldResult(name = "contact_phone", column = "contact_phone"),
  25. @FieldResult(name = "contact_email", column = "contact_email"),
  26. @FieldResult(name = "managed_services", column = "managed_services"),
  27. })
  28. })
  29. @Entity
  30. @Table(name="publisher_informations",schema="publisher_portal")
  31. public class PublisherInfoResponseEntity {
  32. @Id
  33. @Jsongnore
  34. private Long id;
  35. private String publisher_name;
  36. private String contact_name;
  37. private String contact_phone;
  38. private String contact_email;
  39. private String managed_services;
  40. //setters and getters
  41. }

note:assume that entityManager.createNamedQuery(..) has been made

huangapple
  • 本文由 发表于 2020年8月19日 21:16:32
  • 转载请务必保留本文链接：https://go.coder-hub.com/63487856.html
  • java
  • postgresql
  • spring-boot
  • spring-data-jpa
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定