Springboot Spring данные JPA с использованием соединения и нумерации страниц ⇐ JAVA

[Гость]

Java: 20.0
Springboot: 3.0.1
Структура таблицы

Код: Выделить всё

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 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 findAllByReportedByNullOrReportedByNot(
@Param("reportedBy") String reportedBy, Pageable pageable);

*This gives unknown field

Код: Выделить всё

p.createdAt.*

error. Потому что в таблице имя

Код: Выделить всё

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.


Источник: https://stackoverflow.com/questions/781 ... pagination