How To Execute The SQL Complexed Native Query In Grails

Posted By : Avnish Pandey | 28-Dec-2017

grails

Nowadays we use frameworks to simplify and decrease to write the code. We got so many frameworks for a different task to be performed for eg. Spring, Spring Boot, Hibernate, Groovy, and Girls etc. we can reduce so many repeated codes by using these frameworks.

Grails framework provides so many method to perform the operation on Database for eg. find(), findBy(), findAllBy(), findWhereBy(), findAllWhere(), findWhere(),executeQuery(), executeUpdate() etc. These methods runs the suitable query in database to get the results.
But sometimes we need to execute the native SQL queries on the database to get the suitable results. For that purpose, Grails framework provides SQL class and which is contained in the groovy.sql package.

I suppose we have a domain class Employee as below :

class Employee {
	String firstName
	String lastName
	String email
	Date joiningDate
	
	static constraints = {
		firstName nullable:true
		lastName nullable:true
		email nullable:true
		dob nullable:true
	}
}

In the database, the table name will be an employee and there will be four column first_name, last_name, email, and joining_date. now we want the fetch all employee data from the table then we have to use some predefined functions of grails will be like.
def employeeList = Employee.findAll();

But if need to execute the exact query then the query will be as follows :
String query = "select * from employee";

Then we have to create the object of DataSource class and import SQL class like as below :

import groovy.sql.Sql
Public class EmployeeService {
      def dataSource;
      def fetchEmployeeData{
	    String query = "select * from employee";
            def sql = new Sql(dataSource);
            def employeeList = sql.rows(query);
      }
}