Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Springboot spring data JPA using join and pagination

Java: 20.0

Springboot: 3.0.1

Table structure

posts | CREATE TABLE `posts` (
  `id` binary(16) NOT NULL,
  `posts` varchar(5000) DEFAULT NULL,
  `created_at` datetime(6) DEFAULT NULL,
  `updated_at` datetime(6) DEFAULT NULL,
  `user_id` binary(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK8omq0tc18jd43bu5tjh6jvra7taq` (`user_id`),
  CONSTRAINT `FK8omq0tc18jd43bu5tjh6jvra7taq` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

Post entity

@Entity
@Getter
@Setter
@Table(name = "posts")
@JsonIdentityInfo(generator = ObjectIdGenerators.PropertyGenerator.class, property = "id")
public class Post {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @UuidGenerator(style = UuidGenerator.Style.TIME)
  private UUID id;

  @Column(length = 5000)
  private String comment;

  @ManyToOne
  @JsonBackReference
  @JoinColumn(name = "user_id")
  @ToStringExclude
  private User user;

  @Column(name = "deleted_by")
  private String deletedBy;

  @Column(name = "created_at")
  private Timestamp createdAt;

  @Column(name = "updated_at")
  private Timestamp updatedAt;


  @JsonManagedReference
  @OneToMany(mappedBy = "post", cascade = CascadeType.REMOVE, orphanRemoval = true)
  @ToStringExclude
  private List<PostReport> postReports = new ArrayList<>();
}

post_reports

post_reports | CREATE TABLE `post_reports` (
  `id` binary(16) NOT NULL,
  `reported_at` datetime(6) DEFAULT NULL,
  `reported_by` varchar(255) DEFAULT NULL,
  `reported_reason` varchar(255) DEFAULT NULL,
  `updated_at` datetime(6) DEFAULT NULL,
  `updated_by` varchar(255) DEFAULT NULL,
  `post_id` binary(16) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FKj9so403o1bquehozscdqxpyijjma` (`post_id`),
  CONSTRAINT `FKj9so403o1bquehozscdqxpyijjma` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |

PostReport entity

@Entity
@Getter
@Setter
@Table(name = "post_reports")
@JsonIdentityInfo(generator = ObjectIdGenerators.PropertyGenerator.class, property = "id")
public class PostReport {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  @UuidGenerator(style = UuidGenerator.Style.TIME)
  private UUID id;

  @ManyToOne
  @JoinColumn(name = "post_id")
  @JsonBackReference
  @ToStringExclude
  private Post post;

  @Column(name = "reported_by")
  private String reportedBy;

  @Column(name = "reported_reason")
  private String reportedReason;

  @Column(name = "reported_at")
  private Timestamp reportedAt;

  @Column(name = "updated_by")
  private String updatedBy;

  @Column(name = "updated_at")
  private Timestamp updatedAt;
}

Application Properties

spring.datasource.driver-class-name=com.amazonaws.secretsmanager.sql.AWSSecretsManagerMySQLDriver
spring.datasource.url=jdbc-secretsmanager:mysql://${MYSQL_HOST:some.rds.amazonaws.com}:3306/dbname
spring.jpa.database-platform=org.hibernate.dialect.MySQLDialect
spring.jpa.hibernate.ddl-auto=update
spring.jpa.hibernate.naming_strategy=org.hibernate.cfg.EJB3NamingStrategy
spring.datasource.initialization-mode=always
spring.sql.init.mode=always 
hibernate.physical_naming_strategy=org.hibernate.boot.model.naming.CamelCaseToUnderscoresNamingStrategy

Query

@Query(
      value =
          "SELECT DISTINCT p.* FROM posts AS p LEFT JOIN post_reports AS pr ON p.id=pr.post_id WHERE (pr.reported_by IS NULL OR pr.reported_by!=:reportedBy) order by p.created_at",
      countQuery =
          "SELECT COUNT(DISTINCT p.*) FROM posts AS p LEFT JOIN FETCH post_reports AS pr ON p.id= pr.post_id WHERE (pr.reported_by IS NULL OR pr.reported_by!=:reportedBy)",
      nativeQuery = true)
  Page<Post> findAllByReportedByNullOrReportedByNot(
      @Param("reportedBy") String reportedBy, Pageable pageable);

*This gives unknown field p.createdAt.* error. Because in table the name is created_at it is not recognising the column.

Solution

I am passing String sortBy = "createdAt"; which is the reason for this issue. but my other JPA queries are taking this as attribute name is createdAt, so I created another pageable object by String sortBy2 = "created_at"; for this query and if I am using this, it is working. Would like to make it work with createdAt.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Above post needs two modification to make it work properly

    1. Create a new pageable object with created_at and pass that in the native query, because native query understands table column and not the attribute of the entity class.

    2. The query will again include filtered data when the post gets reported by another user.

    so, correct implementation will be

    String sortBy2 = "created_at";
Sort sort2 = Sort.by(sortBy2).descending();
Pageable pageable2 = PageRequest.of(pageNumber, limit, sort2);
postRepository. findAllByReportedByNullOrReportedByNot(@Param("reportedBy") String reportedBy, Pageable pageable)

    Definition of the query will look like:

    @Query(
      value =
          "SELECT DISTINCT p.* FROM posts p WHERE p.id NOT IN (SELECT DISTINCT pr.post_id FROM post_reports pr WHERE pr.reported_by = : reportedBy)",
      nativeQuery = true)
  Page<Post> findAllByReportedByNullOrReportedByNot(
      @Param("reportedBy") String reportedBy, Pageable pageable);

  2. 0

    Please share your entity class as well for better answer.

    You need to set up either of the below

    1. Setup a Naming Strategy
    2. Use lowercase column names in your annotations

    as a property shown in below example or by creating custom strategy.

     spring:
  jpa:
    hibernate:
      naming.physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search