Hibernate with MS SQL

Question

I'm trying to integrate hibernate with MS SQL, below is the sql query I get from hibernate

12:27:44,172 DEBUG [AbstractSaveEventListener] Executing identity-insert immediately
Hibernate: 
    insert 
    into
        aide.dbo.rule
        (appId, ruleName) 
    values
        (?, ?)

causes error

12:27:44,229 DEBUG [SqlExceptionHelper] Incorrect syntax near the keyword 'rule'. [n/a]
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'rule'.

same error is thrown in MS SQL management studio too

while this command runs fine

 insert 
    into
        [aide].[dbo].[rule]
        (appId, ruleName) 
    values
        ('rf', 'wfw')

below is my hibernate cfg

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <!-- <property name="hibernate.bytecode.use_reflection_optimizer">false</property> -->
        <property name="hibernate.connection.url">jdbc:sqlserver://localhost:1433</property>
        <property name="hibernate.default_catalog">aide</property>
        <property name="hibernate.default_schema">dbo</property>
        <property name="hibernate.connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
        <property name="hibernate.connection.username">aide</property>
        <property name="hibernate.connection.password">aide</property>
        <property name="hibernate.dialect">org.hibernate.dialect.SQLServer2008Dialect</property>
        <property name="hibernate.show_sql">true</property><!-- JDBC connection pool (use the 
            built-in) -->
        <property name="hibernate.connection.pool_size">1</property>
        <!-- Drop and re-create the database schema on startup -->
        <property name="hibernate.hbm2ddl.auto">update</property>
        <property name="hibernate.format_sql">true</property>

        <mapping class="com.****.Rule" />

        <!-- <mapping resource="com/****/Rules.hbm.xml"></mapping> -->
    </session-factory>
</hibernate-configuration>

(sqljdbc4.jar downloaded from microsoft website) seems hibernate is generating a query not understandable by MS SQL

No, -you- are. By using the keyword 'rule' as a name. The error is already telling you that: "Incorrect syntax near the KEYWORD 'rule'". But you're not listening. — Gimby
– Gimby, Commented Jun 5, 2014 at 7:22
OP's phrasing is legitimate. There are situations where there is very good reason for using a reserved keyword as a table name, and pretty much every DBMS allows it with some form of escaping. The problem that led me, and probably OP, here was how to best do it in the context of hibernate. I have a hard requirement of supporting multiple DBMS's, must add SQL Server support to an existing entity structure, and need a form of escaping that is understood by all of the ones I must support. The table name is not a reserved keyword in any of the other DBMSs I must support. — Sofia Paixão
– Sofia Paixão, Commented Feb 15, 2019 at 19:53

Vlad Mihalcea · Accepted Answer · 2015-06-25 13:43:25Z

2

RULE is a SQL Server reserved keyword.

If you need to stick to that name you need to escape it with:

@Table(name="`rule`")

edited Jun 25, 2015 at 13:43

answered Jun 5, 2014 at 11:16

Vlad Mihalcea

156k85 gold badges599 silver badges984 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Hibernate with MS SQL

while this command runs fine

below is my hibernate cfg

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

while this command runs fine

below is my hibernate cfg

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related