[PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query]

Question

i need help. i am trying to select from database thru sql statement in play framework, but it gives me error, i cannot figure out where the clue is. here is the code:

@Transactional
public static Users findByUsernameAndPassword(String username, String password){
String hash = DigestUtils.md5Hex(password);
Query q = JPA.em().createNativeQuery("select * from USERS where" + "USERNAME=? and PASSWORD=?").setParameter(1, username).setParameter(2, password);
List<Users> users = q.getResultList();
if(users.isEmpty()){
    return null;
} else{
    return users.get(0);

here is the eror message:

[PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query]

can someone help me please! any help i would appreciate!

thanks

Alex · Accepted Answer · 2012-06-21 14:09:16Z

3

You are using pure sql in a ORM (object-relational mapping) query. JPA lets you build queries with your objects and it's properties, not your database tables.

TypedQuery<Users> q = JPA.em().createQuery("select * from Users where username= :username and password= :password", Users.class);
q.setparameter("username", username);
q.setparameter("password", hash);
List<Users> users = q.getResultList();

That is if your User entity is named Users, it probably is User though.

answered Jun 21, 2012 at 14:09

Alex

14011 bronze badges

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

5 Comments

doniyor Over a year ago

thanks, now i get this error: PersistenceException: [PersistenceUnit: defaultPersistenceUnit] Unable to configure EntityManagerFactory

Alex Over a year ago

Is Users your @Entity ? What do you have in it ? (Plural is usualy for admin controllers, that's why it is odd to find a model called Users). Did you try using Users.find("etc etc") ?

doniyor Over a year ago

yeah, my User is @Entity. I have renamed it back to 'User'. but i get the same error again. the problem is that it cannot find my Entity class, right?

Alex Over a year ago

I'm not sure if that's the real problem, now that I'm sure User is your entity, you can try : User.find("") This code is like Select * From User Where... so in your case it could be like this... User user = User.find("username = ? AND password = ?", username, hash).first()

Alex Over a year ago

Also, just a sidenote, you are saving in database using sql, when you could just User user = new User(blablabla) and then user.save()

aaberg · Accepted Answer · 2015-09-21 10:11:31Z

3

You are missing some spaces in your query. Try to fix that:

"select * from USERS where USERNAME=? and PASSWORD=?"

edited Sep 21, 2015 at 10:11

answered Jun 21, 2012 at 15:11

aaberg

2,27315 silver badges14 bronze badges

3 Comments

doniyor Over a year ago

dont i have to do like this: "select * from USERS where USERNAME='?' and PASSWORD='?'" so that the username will stay in '' ?

aaberg Over a year ago

No. Parameters will automatically be inserted, so they are interpreted as the right datatype. A varchar or char parameter, may not be surrounded with ''

doniyor Over a year ago

ok thanks it is working now, i had different problem, and now i have different problem :D. anyway, thanks a lot

Avinav Mishra · Accepted Answer · 2015-01-25 14:57:20Z

0

I also faced the same problem and replaced the @GeneratedValue from the id and manually put the value of the id it is now working.

answered Jan 25, 2015 at 14:57

Avinav Mishra

7589 silver badges12 bronze badges

Collectives™ on Stack Overflow

[PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query]

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related