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.
2
Answers
Above post needs two modification to make it work properly
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.The query will again include filtered data when the post gets reported by another user.
so, correct implementation will be
Definition of the query will look like:
Please share your entity class as well for better answer.
You need to set up either of the below
as a property shown in below example or by creating custom strategy.