SQLException - Passing thru Query as SQLQuery?

bljohnson · **Joined:** Wed Jul 28, 2004 6:13 pm **Posts:** 12

OK - first off - I've just started using Hibernate. So I apologize for such a simple question :)

Im trying to do a really simple query using HQL. Here's what the code looks like:

Code:

Query query = session.createQuery("from users where users.username=:username and users.password=:password");
query.setString("username", "admin");
query.setString("password", "password");

But when I run this it seems as though the dialect isnt turning this into a real SQL query.. it just seems to be passing it straight through to the database. I'm using MySQL as the database and setting the dialect to "net.sf.hibernate.dialect.MySQLDialect".

I'm getting this exception:

Code:

java.sql.SQLException: Syntax error or access violation,  message from server: "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 'from where (users.username='admin' )and(users.password='password' )' at line 1"

My database name is "users" and my mapped object is User. Should they, or are they required to have the same name?

christian · **Posted:** Fri Jul 30, 2004 12:18 pm

Thats because your query is not HQL. Please see the examples in the documentation.

bljohnson · **Joined:** Wed Jul 28, 2004 6:13 pm **Posts:** 12

I suspected it was some incorrect HQL statement. I looked at some of the examples in the documentation. One included in the quickstart was:

Code:

Query query = session.createQuery("select c from Cat as c where c.sex = :sex");

If I do a similar query like (which is what I initially tried):

Code:

Query query = session.createQuery("select u from users as u where u.username=:username and u.password=:password");

I get this:

Code:

7406 [main] ERROR com.li.wedge.test.TestAxis  - error trying to load hibernate data for User
net.sf.hibernate.QueryException: unexpected token: as [select u from users as u where u.username=:username and u.password=:password]
   at net.sf.hibernate.hql.FromParser.token(FromParser.java:94)
   at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
   at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:123)
   at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
   at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
   at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
   at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:293)
   at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1561)
   at net.sf.hibernate.impl.SessionImpl.iterate(SessionImpl.java:1591)
   at net.sf.hibernate.impl.QueryImpl.iterate(QueryImpl.java:27)
   at com.li.wedge.test.TestAxis.main(TestAxis.java:73)

I've skimmed through chapter 11 also. I'll read that chapter a little closer and find some code examples to read.

Thanks.[/quote]

christian · **Posted:** Fri Jul 30, 2004 12:30 pm

Try "from User as u", it's a class name.

bljohnson · **Joined:** Wed Jul 28, 2004 6:13 pm **Posts:** 12

Good timing - I actually just tried that and it worked. I just need to force my poor brain to ignore all I know about direct SQL calls to the database and focus on hibernate itself rather than thinking about the tables, columns and database formats.

Thanks for the help.