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
Cookies are important to the proper functioning of a site. To improve your experience, we use cookies to remember log-in details and provide secure log-in, collect statistics to optimize site functionality, and deliver content tailored to your interests. Click Agree and Proceed to accept cookies and go directly to the site or click on View Cookie Settings to see detailed descriptions of the types of cookies and choose whether to accept certain cookies while on the site.
About Author
Ritik Jain
Ritik is an accomplished Backend Developer with extensive experience in Mean. He is proficient in Core Java, Spring-Boot, Hibernate, Node.js, Angular 2+, and various relational databases like MySQL and MongoDB. With his expertise in API implementations, webservices, development testing, and deployments, he has contributed to the successful completion of various client projects. Apart from his professional pursuits, Ritik is an enthusiastic gamer and keeps himself updated with the latest advancements in technology.