hibernate inserting without left ticks

Question

I am trying to do insert using hibernate and am running into an issue. When I use the following code

@Override
    public Serializable addTestResult(Result test_result) {
        // TODO Auto-generated method stub
        return getCurrentSession().save(test_result);
    }

the end query looks like this:

mysql> insert into test_results (db, host) values ('db1', 'host1');

which gets rejected by mysql since there are left tick missing in the column name. How can I have hibernate add quotes around the column name so that the query looks like this:

mysql> insert into test_results (`db`, `host`) values ('db1', 'host1');

I don't think that is an invalid statement for mysql. Do you receive a syntax error or any other error? — porfiriopartida, Sep 26 '13 at 23:05
@porfiriopartida not until you use MySQL reserved keywords for column names. — adarshr, Sep 26 '13 at 23:07
Right.. http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html I thought it was only to prevent naming issues (like phpmyadmin does) for tables and fields with more than 1 word... interesting. — porfiriopartida, Sep 26 '13 at 23:09
Duplicate of http://stackoverflow.com/questions/3364835/automatic-reserved-word-escaping-for-hibernate-tables-and-columns — Manuel Darveau, Sep 27 '13 at 01:34

score 3 · Accepted Answer · answered Sep 27 '13 at 02:49

Quote the table/ column names you need quoted with ` back-ticks in your .hbm.xml or mapping annotations.

For example:

<property name='DB" column='`db`' />
@Column(name="`db`")

This tells Hibernate to quote these identifiers to the database. I use this, for example, with a `USER` table. As with any app, most table & column-names are not conflicting but user tends to be a SQL keyword.

You don't need to switch on quoting globally.

score 1 · Answer 2 · edited May 23 '17 at 10:34

1

Try to add the option hibernate.globally_quoted_identifiers=true.

Source: Automatic reserved word escaping for Hibernate tables and columns

edited May 23 '17 at 10:34

Community

1
1

answered Sep 27 '13 at 01:36

Manuel Darveau

4,585
5
26
36

hibernate inserting without left ticks

2 Answers2