为了复制这个用例,我在我的
high-performance-java-persistence
GitHub 仓库中创建了 SQLServerEscapeQuestionCharacterTest
。假设您有以下 JPA 实体:
@Entity(name = "Post")
@Table(name = "[post]")
public static class Post {
@Id
private Long id;
private String title;
@Column(name = "[active?]")
private boolean active;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public boolean isActive() {
return active;
}
public void setActive(boolean active) {
this.active = active;
}
}
这与以下数据库表相关:
CREATE TABLE [post] (
id bigint not null,
[active?] bit,
title varchar(255),
PRIMARY KEY (id)
)
如果您创建以下的
Post
实体:
Post post = new Post();
post.setId(1L);
post.setTitle("High-Performance Java Persistence");
post.setActive(true);
entityManager.persist(post);
Hibernate将执行正确的INSERT语句:
INSERT INTO [post] (
[active?],
title,
id
)
VALUES (
true,
'High-Performance Java Persistence',
1
)
执行JPQL查询时:
List<Post> posts = entityManager
.createQuery(
"select p " +
"from Post p " +
"where p.active = :active", Post.class)
.setParameter("active", true)
.getResultList();
assertEquals(1, posts.size());
Hibernate使用JPA的@Column注解中的name属性,该属性已经对列名进行了转义:
SELECT p.id AS id1_0_,
p.[active?] AS active2_0_,
p.title AS title3_0_
FROM [post] p
WHERE p.[active?] = true
对于原生 SQL 查询,您需要转义 ?
字符,如下所示:
List<String> posts = entityManager
.createNativeQuery(
"select p.title " +
"from [post] p " +
"where p.[active\\?] = :active")
.setParameter("active", true)
.getResultList();
assertEquals(1, posts.size());
而且,它的表现非常出色。
createSQLQuery
部分。我会猜测是[Active\\?]
(即应该传递一个反斜杠给Hibernate,但要加倍,否则Java会将其删除),但我目前没有测试的设置。 - Joachim Isaksson