Hibernate generates error in your SQL syntax with MySQL

0
votes

I am getting an error while try to fetch the record from table using HQL

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.userinfo userinfo0_ where userinfo0_.login_id='muskan' and userinfo0_.pwd='musk' at line 1

Full Error trace:

from Userinfo userinfo where userinfo.loginId='muskan' and userinfo.pwd='muskan@admin' and userinfo.role='Administrator'Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.loader.Loader.doList(Loader.java:2452) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2192) at org.hibernate.loader.Loader.list(Loader.java:2187) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:452) at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1258) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) at com.ovs.domain.UserDAO.validateUser(UserDAO.java:16) at com.ovs.action.LoginTest.main(LoginTest.java:17) Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.userinfo userinfo0_ where userinfo0_.login_id='muskan' and userinfo0_.pwd='musk' at line 1 at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:406) at com.mysql.jdbc.Util.getInstance(Util.java:381) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3558) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3490) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1959) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2109) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2643) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2077) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2228) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) at org.hibernate.loader.Loader.getResultSet(Loader.java:1869) at org.hibernate.loader.Loader.doQuery(Loader.java:718) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270) at org.hibernate.loader.Loader.doList(Loader.java:2449) ... 9 more

Table structure:

CREATE TABLE `userinfo` (
  `login_id` varchar(10) NOT NULL,
  `pwd` varchar(15) NOT NULL,
  `role` varchar(15) NOT NULL,
  `lastlogon` bigint(20) NOT NULL,
  PRIMARY KEY  (`login_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Hibernate Settings:

<name="hibernate.dialect">org.hibernate.dialect.MySQL5InnoDBDialect</property>

Java code:

String query="from Userinfo userinfo where userinfo.loginId='"+userinfo.getLoginId()+"' and userinfo.pwd='"+ userinfo.getPwd()+
                      "' and userinfo.role='"+userinfo.getRole()+"'";   
        Query validateUserQuery= getsession().createQuery(query);

        System.out.print(validateUserQuery.getQueryString());
        List<Userinfo> result = validateUserQuery.list();
2
sqlhibernategrammar

2 Answers

0
votes

You should try to change in your query userinfo.loginId by userinfo.login_id

0
votes

It would be great if you can show "Userinfo" entity class or XML. In query you can use parameters so your string will look better and to protect yourself from SQL injections.

String query="from Userinfo u where u.loginId = :loginId"
Query validateUserQuery= getsession().createQuery(query);
validateUserQuery.setParameter("loginId",userinfo.getLoginId());