How to execute a SQL query in grails

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.