英文:
DESC is passed to a Hibernate query, but the order is still ascending. Why?
问题
I have a RESTful app. Here's a test method from it. It is intended to retrieve a page of QuestionCommentResponseDto objects in descending order.
@Test@Sql(executionPhase = BEFORE_TEST_METHOD, value = BASE_SCRIPT_PATH + "GetPageTest/before.sql")@Sql(executionPhase = AFTER_TEST_METHOD, value = BASE_SCRIPT_PATH + "GetPageTest/after.sql") // truncatingpublic void getPageOneSizeFiveSortByIdDescTest() throws Exception {token = testUtil.getToken(testUsername, testPassword);MockHttpServletResponse response = mockMvc.perform(get(BASE_URI + "page/" + 1).header(HttpHeaders.AUTHORIZATION, token).param("pageSize", "5").param("sortType", "ID_DESC")).andExpect(status().isOk()).andReturn().getResponse();// asserts omitted
<!-- the before script -->TRUNCATE TABLE permissions CASCADE;<!-- the same for accounts, questions, tags, question_comments -->INSERT INTO permissions(id, name, created_date, modified_date)VALUES (1, 'ADMIN', current_timestamp, current_timestamp),(2, 'MODERATOR', current_timestamp, current_timestamp),(3, 'USER', current_timestamp, current_timestamp);INSERT INTO accounts(id, username, password, enabled, created_date, modified_date)VALUES (1, 'mickey_m', '$2y$10$ZdqfOo1vwdHJJGnkmGrw/OUelZcU9ZfRFaX/RMN3XniXH96eTkB1e', true, current_timestamp,current_timestamp),(2, 'minerva_m', '$2y$10$PiZxGGi904rCLdTSGY1ycuQhcEtQrP1u74KvQ2IEuk5Jh18Ml.6xO', true, current_timestamp,current_timestamp);INSERT INTO accounts_permissions(account_id, permission_id)VALUES (1, 3),(2, 3);INSERT INTO questions(id, created_date, modified_date, title, description, account_id)VALUES (1, current_timestamp, current_timestamp, 'title', 'description', 2);INSERT INTO tags(id, created_date, modified_date, name)VALUES (1, current_timestamp, current_timestamp, 'tag1'),(2, current_timestamp, current_timestamp, 'tag2'),(3, current_timestamp, current_timestamp, 'tag3');INSERT INTO questions_tags(question_id, tag_id)VALUES (1, 1), (1, 2), (1, 3);INSERT INTO question_comments(id, created_date, modified_date, text, account_id, question_id)VALUES (1, current_timestamp, current_timestamp, 'text', 1, 1),<!-- the middle rows are omitted -->(10, current_timestamp, current_timestamp, 'text', 1, 1);
@Getterpublic class QuestionCommentResponseDto {private final Long id;private final Long questionId;private final LocalDateTime createdDate;private final LocalDateTime modifiedDate;private final String text;@Setterprivate AccountResponseDto owner;// constructors, equals(), hashcode()
@AllArgsConstructor@NoArgsConstructor@Getter@Setterpublic class AccountResponseDto {private Long id;private String username;// equals(), hashcode()
// the relevant controller method@GetMapping("/page/{pageNumber}")public ResponseEntity<Data<Page<QuestionCommentResponseDto>>> getPage(@PathVariable @Positive @NotNull Integer pageNumber,@RequestParam(defaultValue = "20") @NotNull Integer pageSize,@RequestParam(defaultValue = "ID_ASC") @NotNull SortType sortType) {PaginationParameters params = PaginationParameters.ofPageNumberSizeAndSortType(pageNumber, pageSize, sortType);// ↑ PaginationParameters is a simple record class defined as PaginationParameters(Integer pageNumber, Integer size, SortType sortType)Page<QuestionCommentResponseDto> page = dtoService.getPage(params);Data<Page<QuestionCommentResponseDto>> responseData = Data.build(page); // a two-bit wrapperreturn ResponseEntity.ok(responseData);}
@RequiredArgsConstructor@Getterpublic enum SortType {ID_ASC(" id "),ID_DESC(" id DESC "),// some extra constantsprivate String query;}
// a basic utility classpublic class PaginationParametersProcessor {public static String extractSortingModifier(PaginationParameters params) {return params.sortType().getQuery();}public static int extractFirstResultIndex(PaginationParameters params) {return (params.pageNumber() - 1) * params.size();}public static int extractMaxResults(PaginationParameters params) {return params.size();}}
dtoService.getPage(params) eventually comes down to this
@Overridepublic List<QuestionCommentResponseDto> getDtosWithoutSetOwner(PaginationParameters params) {String sortingModifier = PaginationParametersProcessor.extractSortingModifier(params); // assigns " id DESC "int offset = PaginationParametersProcessor.extractFirstResultIndex(params);int limit = PaginationParametersProcessor.extractMaxResults(params);return entityManager.createQuery("""SELECT new stack.overflow.model.dto.response.QuestionCommentResponseDto (qc.id, q.id, qc.createdDate, qc.modifiedDate, qc.text) FROM QuestionComment qc JOIN qc.question qORDER BY :sort""", QuestionCommentResponseDto.class).setParameter("sort", sortingModifier).setFirstResult(offset).setMaxResults(limit).getResultList();}
The :sort parameter is successfully passed into the query executed by Hibernate. Here's a snippet from my console output
Hibernate: select questionco0_.id as col_0_0_, question1_.id as col_1_0_, questionco0_.created_date as col_2_0_, questionco0_.modified_date as col_3_0_, questionco0_.text as col_4_0_ from question_comments questionco0_ inner join questions question1_ on questionco0_.question_id=question1_.id order by ? limit ?2023-06-04 16:46:53.164 TRACE 3172 --- [ main] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] - [ id DESC ]
But the ids in the resulting list are in an ascending, not descending order
Body = {"data":{"dtos":[{"id":1,"questionId":1,"createdDate":"2023-06-04T16:46:52.942835","modifiedDate":"2023-06-04T16:46:52.942835","text":"text","owner":{"id":1,"username":"mickey_m"}},{"id":2,"questionId":1,"createdDate":"2023-06-04T16:46:52.942835","modifiedDate":"2023-06-04T16:46:52.942835","text":"text","owner":{"id":1,"username":"mickey_m"}},{"id":3,"questionId":1,"createdDate<details><summary>英文:</summary>I have a RESTful app. Here's a test method from it. It is intended to retrieve a page of `QuestionCommentResponseDto` objects in *descending* order```java@Test@Sql(executionPhase = BEFORE_TEST_METHOD, value = BASE_SCRIPT_PATH + "GetPageTest/before.sql")@Sql(executionPhase = AFTER_TEST_METHOD, value = BASE_SCRIPT_PATH + "GetPageTest/after.sql") // truncatingpublic void getPageOneSizeFiveSortByIdDescTest() throws Exception {token = testUtil.getToken(testUsername, testPassword);MockHttpServletResponse response = mockMvc.perform(get(BASE_URI + "page/" + 1).header(HttpHeaders.AUTHORIZATION, token).param("pageSize", "5").param("sortType", "ID_DESC")).andExpect(status().isOk()).andReturn().getResponse();// asserts omitted
<!-- the before script -->TRUNCATE TABLE permissions CASCADE;<!-- the same for accounts, questions, tags, question_comments -->INSERT INTO permissions(id, name, created_date, modified_date)VALUES (1, 'ADMIN', current_timestamp, current_timestamp),(2, 'MODERATOR', current_timestamp, current_timestamp),(3, 'USER', current_timestamp, current_timestamp);INSERT INTO accounts(id, username, password, enabled, created_date, modified_date)VALUES (1, 'mickey_m', '$2y$10$ZdqfOo1vwdHJJGnkmGrw/OUelZcU9ZfRFaX/RMN3XniXH96eTkB1e', true, current_timestamp,current_timestamp),(2, 'minerva_m', '$2y$10$PiZxGGi904rCLdTSGY1ycuQhcEtQrP1u74KvQ2IEuk5Jh18Ml.6xO', true, current_timestamp,current_timestamp);INSERT INTO accounts_permissions(account_id, permission_id)VALUES (1, 3),(2, 3);INSERT INTO questions(id, created_date, modified_date, title, description, account_id)VALUES (1, current_timestamp, current_timestamp, 'title', 'description', 2);INSERT INTO tags(id, created_date, modified_date, name)VALUES (1, current_timestamp, current_timestamp, 'tag1'),(2, current_timestamp, current_timestamp, 'tag2'),(3, current_timestamp, current_timestamp, 'tag3');INSERT INTO questions_tags(question_id, tag_id)VALUES (1, 1), (1, 2), (1, 3);INSERT INTO question_comments(id, created_date, modified_date, text, account_id, question_id)VALUES (1, current_timestamp, current_timestamp, 'text', 1, 1),<!-- the middle rows are omitted -->(10, current_timestamp, current_timestamp, 'text', 1, 1);
@Getterpublic class QuestionCommentResponseDto {private final Long id;private final Long questionId;private final LocalDateTime createdDate;private final LocalDateTime modifiedDate;private final String text;@Setterprivate AccountResponseDto owner;// constructors, equals(), hashcode()
@AllArgsConstructor@NoArgsConstructor@Getter@Setterpublic class AccountResponseDto {private Long id;private String username;// equals(), hashcode()
// the relevant controller method@GetMapping("/page/{pageNumber}")public ResponseEntity<Data<Page<QuestionCommentResponseDto>>> getPage(@PathVariable @Positive @NotNull Integer pageNumber,@RequestParam(defaultValue = "20") @NotNull Integer pageSize,@RequestParam(defaultValue = "ID_ASC") @NotNull SortType sortType) {PaginationParameters params = PaginationParameters.ofPageNumberSizeAndSortType(pageNumber, pageSize, sortType);// ↑ PaginationParameters is a simple record class defined as PaginationParameters(Integer pageNumber, Integer size, SortType sortType)Page<QuestionCommentResponseDto> page = dtoService.getPage(params);Data<Page<QuestionCommentResponseDto>> responseData = Data.build(page); // a two-bit wrapperreturn ResponseEntity.ok(responseData);}
@RequiredArgsConstructor@Getterpublic enum SortType {ID_ASC(" id "),ID_DESC(" id DESC "),// some extra constantsprivate String query;}
// a basic utility classpublic class PaginationParametersProcessor {public static String extractSortingModifier(PaginationParameters params) {return params.sortType().getQuery();}public static int extractFirstResultIndex(PaginationParameters params) {return (params.pageNumber() - 1) * params.size();}public static int extractMaxResults(PaginationParameters params) {return params.size();}}
dtoService.getPage(params) eventually comes down to this
@Overridepublic List<QuestionCommentResponseDto> getDtosWithoutSetOwner(PaginationParameters params) {String sortingModifier = PaginationParametersProcessor.extractSortingModifier(params); // assigns " id DESC "int offset = PaginationParametersProcessor.extractFirstResultIndex(params);int limit = PaginationParametersProcessor.extractMaxResults(params);return entityManager.createQuery("""SELECT new stack.overflow.model.dto.response.QuestionCommentResponseDto (qc.id, q.id, qc.createdDate, qc.modifiedDate, qc.text) FROM QuestionComment qc JOIN qc.question qORDER BY :sort""", QuestionCommentResponseDto.class).setParameter("sort", sortingModifier).setFirstResult(offset).setMaxResults(limit).getResultList();}
The :sort parameter is successfully passed into the query executed by Hibernate. Here's a snippet from my console output
Hibernate: select questionco0_.id as col_0_0_, question1_.id as col_1_0_, questionco0_.created_date as col_2_0_, questionco0_.modified_date as col_3_0_, questionco0_.text as col_4_0_ from question_comments questionco0_ inner join questions question1_ on questionco0_.question_id=question1_.id order by ? limit ?2023-06-04 16:46:53.164 TRACE 3172 --- [ main] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] - [ id DESC ]
But the ids in the resulting list are in an ascending, not descending order
Body = {"data":{"dtos":[{"id":1,"questionId":1,"createdDate":"2023-06-04T16:46:52.942835","modifiedDate":"2023-06-04T16:46:52.942835","text":"text","owner":{"id":1,"username":"mickey_m"}},{"id":2,"questionId":1,"createdDate":"2023-06-04T16:46:52.942835","modifiedDate":"2023-06-04T16:46:52.942835","text":"text","owner":{"id":1,"username":"mickey_m"}},{"id":3,"questionId":1,"createdDate":"2023-06-04T16:46:52.942835","modifiedDate":"2023-06-04T16:46:52.942835","text":"text","owner":{"id":1,"username":"mickey_m"}},{"id":4,"questionId":1,"createdDate":"2023-06-04T16:46:52.942835","modifiedDate":"2023-06-04T16:46:52.942835","text":"text","owner":{"id":1,"username":"mickey_m"}},{"id":5,"questionId":1,"createdDate":"2023-06-04T16:46:52.942835","modifiedDate":"2023-06-04T16:46:52.942835","text":"text","owner":{"id":1,"username":"mickey_m"}}],"count":10}}
Why is that and how do I fix it? Is it because Postgres can't figure out which id I mean (QuestionComment's or Question's)? Suggestions?
In case you need to look at the entire project, here's the repo
UPD: Ok, so setParameter() encloses the passed value in parentheses thereby precluding any query modification (which makes sense considering the peril of SQL injections). What should I do then? Regular concatenation would make my code less pretty, don't you think?
@Overridepublic List<QuestionCommentResponseDto> getDtosWithoutSetOwner(PaginationParameters params) {String sortingModifier = PaginationParametersProcessor.extractSortingModifier(params);int offset = PaginationParametersProcessor.extractFirstResultIndex(params);int limit = PaginationParametersProcessor.extractMaxResults(params);return entityManager.createQuery("""SELECT new stack.overflow.model.dto.response.QuestionCommentResponseDto (qc.id, q.id, qc.createdDate, qc.modifiedDate, qc.text) FROM QuestionComment qc JOIN qc.question qORDER BY""" + sortingModifier, QuestionCommentResponseDto.class) // ← not nice.setFirstResult(offset).setMaxResults(limit).getResultList();}
What's worse, I now get error 500 in all "getPage" tests, including those that used to go smoothly
Body = {"error":"could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet"}
As I understand, it's because of the thing I mentioned above, id's ambiguity
ERROR 7816 --- [ main] o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: column reference "id" is ambiguous
Here's what I tried to resolve that ambiguity
@Overridepublic List<QuestionCommentResponseDto> getDtosWithoutSetOwner(PaginationParameters params) {String sortingModifier = PaginationParametersProcessor.extractSortingModifier(params, QuestionComment.class);
public class PaginationParametersProcessor {public static String extractSortingModifier(PaginationParameters params) {return params.sortType().getQuery();}public static String extractSortingModifier(PaginationParameters params,Class<?> entity) {String nonadaptedQuery = extractSortingModifier(params);String entityTableName = extractEntityTableName(entity);String adaptedQuery = nonadaptedQuery.replaceFirst("(?=\\w)", entityTableName + ".");return adaptedQuery;}private static String extractEntityTableName(Class<?> entity) {if (entity.isAnnotationPresent(Entity.class)) {return entity.isAnnotationPresent(Table.class) ?entity.getAnnotation(Table.class).name() :entity.getSimpleName();} else {throw new IllegalArgumentException(String.format("%s is not an @Entity", entity.getName()));}}
@AllArgsConstructor@NoArgsConstructor@Getter@Setter@Entity@EntityListeners(AuditingEntityListener.class)@Table(name = "question_comments") // ← see?public class QuestionComment {
Result:
ERROR 4440 --- [ main] o.h.hql.internal.ast.ErrorTracker : Invalid path: 'question_comments.id'org.hibernate.hql.internal.ast.InvalidPathException: Invalid path: 'question_comments.id'
I don't get it. Why is it invalid? I tried this too (after all, JPA operates with @Entities, not tables)
private static String extractEntityTableName(Class<?> entity) {if (entity.isAnnotationPresent(Entity.class)) {return entity.getSimpleName();} else {// you know the rest
Result:
Body = {"error":"Cannot read field \"value\" because \"s1\" is null"}
I'm at my wits' end!
UPD2: This kind of works but is not very pretty
@Overridepublic List<QuestionCommentResponseDto> getDtosWithoutSetOwner(PaginationParameters params) {String sortingModifier = PaginationParametersProcessor.extractSortingModifier(params).trim();int offset = PaginationParametersProcessor.extractFirstResultIndex(params);int limit = PaginationParametersProcessor.extractMaxResults(params);return entityManager.createQuery("""SELECT new stack.overflow.model.dto.response.QuestionCommentResponseDto (qc.id, q.id, qc.createdDate, qc.modifiedDate, qc.text) FROM QuestionComment qc JOIN qc.question qORDER BY qc.""" + sortingModifier, QuestionCommentResponseDto.class).setFirstResult(offset).setMaxResults(limit).getResultList();}
But I still don't understand why neither of the previous two options worked
答案1
得分: 1
The :sort parameter is successfully passed into the query executed by Hibernate.
that is actually not true. The resulting query effectively looks like:
Hibernate: select questionco0_.id as col_0_0_,question1_.id as col_1_0_,questionco0_.created_date as col_2_0_,questionco0_.modified_date as col_3_0_,questionco0_.text as col_4_0_from question_comments questionco0_inner join questions question1_ onquestionco0_.question_id=question1_.idorder by "id DESC" limit ?
That is clearly stated in HBN debug message:
binding parameter [1] as [VARCHAR] - [ id DESC ]
Basically, you are sorting by a constant, but not by a column. Anticipating a follow-up Q about how to parameterise column name in order by clause: you can't, you can parameterise constants, but not columns.
英文:
> The :sort parameter is successfully passed into the query executed by Hibernate.
that is actually not true. The resulting query effectively looks like:
Hibernate: select questionco0_.id as col_0_0_,question1_.id as col_1_0_,questionco0_.created_date as col_2_0_,questionco0_.modified_date as col_3_0_,questionco0_.text as col_4_0_from question_comments questionco0_inner join questions question1_ onquestionco0_.question_id=question1_.idorder by "id DESC" limit ?
That is clearly stated in HBN debug message:
binding parameter [1] as [VARCHAR] - [ id DESC ]
Basically, you are sorting by a constant, but not by a column. Anticipating a follow-up Q about how to parameterise column name in order by clause: you can't, you can parameterise constants, but not columns.
答案2
得分: 1
只看这里的我的答案。你不能通过PreparedStatement参数来改变执行计划，将asc改成desc或反之会改变执行计划。所以无论是SQL还是Hibernate都不会这样做（如你所期望的那样）。
所以当你将它作为PreparedStatement参数传递时，实际上传递的是一个常量，因此你的DBMS只会运行以下查询：
select ....order by 'id desc'
这是一个常量！
英文:
Just look at my answer here. You cannot change execution plan with PreparedStatement parameters, changing asc to desc or vice-versa changes the execution plan. So neither SQL nor Hibernate won't do that (as you except).
So when you are passing that as PreparedStatement parameter, you are passing a constant, so your DBMS just runs the query as:
select ....order by 'id desc'
which is a constant!
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论