Incorrect syntax near the keyword 'table' and could not extract ResultSet

Question

I have created a project with SQL Server which the files:

UserDAO.java*

public class UserDAO 
{
    private static SessionFactory sessionFactory;
    static 
    {
        sessionFactory = HibernateUtility.getSessionFactory();
    }

    @SuppressWarnings("unchecked")
    public static List<User> findAll()
    {
        Session session = sessionFactory.openSession();
        Criteria crit = session.createCriteria(User.class);
        List<User> userList = crit.list();
        return userList;
    }
}

UserService.java

public class UserService 
{
    public static void main(String[] args) 
    {
        List<User> listUsers = UserDAO.findAll();
        for(User u : listUsers)
        {
            System.out.println("User is = " + u.getUserName());
        }
    }
}

hibernate.cfg.xml

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
    <session-factory>
       <property name="hibernate.dialect">org.hibernate.dialect.SQLServer2008Dialect</property>
       <property name="hibernate.connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
       <property name="hibernate.connection.url">jdbc:sqlserver://localhost:1433;database=happy</property>
       <property name="hibernate.connection.username">lm</property>
       <property name="hibernate.connection.password">pp</property>
       <property name="hibernate.hbm2ddl.auto">create</property>
       <property name="show_sql">true</property>
       <property name="hibernate.current_session_context_class">thread</property>
       <property name="hibernate.hbm2ddl.auto">validate</property>
       <mapping class="com.annotation.day1.entity.User"/>
    </session-factory>
</hibernate-configuration>

After running the project, the exception bellow displayed:

Hibernate: 
    select
        this_.id as id1_0_0_,
        this_.password as password2_0_0_,
        this_.userName as userName3_0_0_ 
    from
        User this_
Aug 07, 2016 9:29:00 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 156, SQLState: S0001
Aug 07, 2016 9:29:00 PM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: Incorrect syntax near the keyword 'User'.
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:89)
    at org.hibernate.loader.Loader.getResultSet(Loader.java:2065)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1862)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838)
    at org.hibernate.loader.Loader.doQuery(Loader.java:909)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
    at org.hibernate.loader.Loader.doList(Loader.java:2553)
    at org.hibernate.loader.Loader.doList(Loader.java:2539)
    at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2369)
    at org.hibernate.loader.Loader.list(Loader.java:2364)
    at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:126)
    at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1682)
    at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:380)
    at com.annotation.day1.dao.UserDAO.findAll(UserDAO.java:74)
    at com.annotation.day1.service.UserService.main(UserService.java:24)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'User'.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:197)

Any help is appreciated. Thanks in advance.

Answer 1

USER is a Reserve Word and needs to be escaped in query using square bracket [] while querying.

If you are using annotations, escape through single quotes. '', like below

@Table(name="`user`")

Also refer here for similar issue.

Answer 2

Faced pretty similar issue, when Column name value has space in between.

Note : Hibernate 5.3.7 with SQLServer 2014

@Entity

@Table(name = "TableName")

public class Customer {

@Id
@Column(name = "cid")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int customerId;

@Column(name = "Customer Name")
private String customerName;

---
---

ERROR:

Solution 1 : Template Literals

Solution 2:

-We could remove the space or add '_' in between

@Column(name = "CustomerName") or @Column(name = "Customer_Name")

Note: Name is also a reserved word in SQLServer.