In a Hibernate SQL query how do you escape ? character in the field name

Question

This is a SQL Server DB and the developer, many years ago, made a column name for boolean values contain a ? character. Many systems access this DB, so changing this column name isn't really an option for me.

This works in native MS tools:

SELECT * FROM MyDatabase.dbo.[My Table] WHERE [Active?]= true

That same query fails in a session.createSQLQuery with Hibernate throwing an error attempting to interpret the ? inside the bracketed field name as a parameter.

I've tried backticks [Active?\]

I've tried escaping with /! [Active/!?] and vice versa !/ [Active!/?] with no effect.

I've also looked around quite a few stack overflow and web forums for other suggestions but the other things I've tried have not worked.

Is this a bug in hibernate? It seems to me that nothing in brackets should be interpretable as parameters.

The specific error is a query exception

> Expected positional parameter count: 1, actual parameters:[]

with the query string attached.

joachim-isaksson Answered it: [Active\\?] is it

@JoachimIsaksson, not sure what you mean by mapping. This isn't an hql mapping. I do have a class that maps to the table that I will probably need to modify as well but the error is occurring in the code before I actually get to assign myObject = query.list(); — Marc Johnson, Jun 14 '14 at 14:58
Ah, missed the `createSQLQuery` part. I'd take a wild stab at `[Active\\?]` (ie one backslash should be passed to hibernate, just doubled since otherwise Java will remove it), but I don't currently have a setup to test on. — Joachim Isaksson, Jun 14 '14 at 15:17
@JoachimIsaksson, that was it. I could've sworn I tried that and got errors in the string. — Marc Johnson, Jun 14 '14 at 17:26

score 5 · Answer 1 · answered Jan 29 '19 at 20:13

To replicate this use case, I created the SQLServerEscapeQuestionCharacterTest in my high-performance-java-persistence GitHub repository.

Assuming you have the following JPA entity:

@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;
    }
}

Which is associated with the following database table:

CREATE TABLE [post] (
    id bigint not null, 
    [active?] bit, 
    title varchar(255), 
    PRIMARY KEY (id)
)

If you create the following Post entity:

Post post = new Post();
post.setId(1L);
post.setTitle("High-Performance Java Persistence");
post.setActive(true);

entityManager.persist(post);

Hibernate will execute the proper INSERT statement:

INSERT INTO [post] (
    [active?], 
    title, 
    id
) 
VALUES (
    true, 
    'High-Performance Java Persistence', 
    1
)

When executing a JPQL query:

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 uses the name attribute of the JPA @Column which already escapes the 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

For native SQL queries, you need to escape the ? character like this:

List<String> posts = entityManager
.createNativeQuery(
    "select p.title " +
    "from [post] p " +
    "where p.[active\\?] = :active")
.setParameter("active", true)
.getResultList();

assertEquals(1, posts.size());

And, it works like a charm.

Marc Johnson · Accepted Answer · 2017-06-21T05:53:04.587

-2

@joachim-isaksson Answered it [Active\\?]

edited Jun 21 '17 at 05:53

answered Jun 20 '17 at 20:34

Marc Johnson

75
1
12

In a Hibernate SQL query how do you escape ? character in the field name

2 Answers2