Points To Remember
- You need to inject the SessionFactory object in the service or the controller, where you want to use it.
- Get the current session from the session factory and execute the query in this session using sessionFactory.getCurrentSession().
- You can also execute the query in a new session by using sessionFactory.openSession().
Executing SQL query in grails
Person.groovy
package com.ekiras.grails;
class Person{
String username
String email
String password
static mapping = {
}
static constraints = {
username nullable: true
password nullable: false, blank: false
email nullable: false, blank: false
}
}
PersonService.groovy
package com.ekiras.grails;
import org.hibernate.SessionFactory;
import grails.transaction.Transactional
import com.ekiras.grails.Person;
@Transactional
class PersonService{
SessionFactory sessionFactory;
def listPersons(){
String query = "select distinct username from person";
def personList = sessionFactory.getCurrentSession().createSQLQuery(query).list();
return personList;
}
boolean deletePerson(Person person){
String query = "delete from person where username=${person.username}";
Integer rowsEffected = sessionFactory.getCurrentSession().createSQLQuery(query).executeUpdate();
if(rowsEffected == 1);
return true;
else
return false;
}
Person updatePerson(Person person){
String query = "update person set username=${person.username} where id={person.id}";
Integer rowsEffected = sessionFactory.getCurrentSession().createSQLQuery(query).executeUpdate();
if(rowsEffected == 1);
return person;
else
// throw new Exception();
}
}
This is how you can execute the simple SQL queries in Grails using hibernate sessionFactory.
