Use Native Sql Query In Grails

Posted By : Ritik Jain | 28-Sep-2021

In order to use a native query firstly we need to import sql package:-

import groovy.sql.Sql

 

Then we need to inject the dataSource that simply provided the database connection. The DataSource already available in a Grails application, We just need to define the name dataSource as a reference, after this, we can use it in our grails application. 

def dataSource

 

Now we need to create a method that accesses the data source and SQL. Here we can also access the mapped table that is not possible by findBy*

def methodName(User user){
final def query = '''\
select * from tableName as mn INNER JOIN anotherTableName as e ON mn.id = e.table_name_id where mn.subject In (SomeThing) And e.email_string = :email;'''

final Sql sql = new Sql(dataSource)
def mail = sql.rows(query,email: user.email)
return mail
}

 

If dataSource is not available then we simply use the below code to establish the connection with the database:-

 def db = [url:'jdbc:hsqldb:mem:testDB', user:'sa', password:'', driver:'org.hsqldb.jdbc.JDBCDriver']
 def sql = Sql.newInstance(db.url, db.user, db.password, db.driver)

 

Now you can invoke sql to create a table or to run native query:

sql.execute '''
     create table PROJECT (
         id integer not null,
         name varchar(50),
         url varchar(100),
     )
 '''

 

Thanks 

About Author

Author Image
Ritik Jain

Ritik Jain is Java Developer and has good knowledge of Core java,Spring Boot and Asp.net.

Request for Proposal

Name is required

Comment is required

Sending message..