英文:
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") // truncating
public 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);
@Getter
public class QuestionCommentResponseDto {
private final Long id;
private final Long questionId;
private final LocalDateTime createdDate;
private final LocalDateTime modifiedDate;
private final String text;
@Setter
private AccountResponseDto owner;
// constructors, equals(), hashcode()
@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
public 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 wrapper
return ResponseEntity.ok(responseData);
}
@RequiredArgsConstructor
@Getter
public enum SortType {
ID_ASC(" id "),
ID_DESC(" id DESC "),
// some extra constants
private String query;
}
// a basic utility class
public 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
@Override
public 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 q
ORDER 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") // truncating
public 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);
@Getter
public class QuestionCommentResponseDto {
private final Long id;
private final Long questionId;
private final LocalDateTime createdDate;
private final LocalDateTime modifiedDate;
private final String text;
@Setter
private AccountResponseDto owner;
// constructors, equals(), hashcode()
@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
public 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 wrapper
return ResponseEntity.ok(responseData);
}
@RequiredArgsConstructor
@Getter
public enum SortType {
ID_ASC(" id "),
ID_DESC(" id DESC "),
// some extra constants
private String query;
}
// a basic utility class
public 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
@Override
public 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 q
ORDER 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?
@Override
public 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 q
ORDER 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
@Override
public 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
@Override
public 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 q
ORDER 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_ on
questionco0_.question_id=question1_.id
order 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_ on
questionco0_.question_id=question1_.id
order 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!
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论