How to pass sql query in grails

Question

Currently I am working with grails and MySQL.i want to execute my own query example "SELECT fullName from User" while clicking an anchor tag.

its my example that i worked out

class InsertDataController {

def index() { }
def sample() {
    InsertData insertData=new InsertData();
    insertData.show();
}
}

Domain classes

import groovy.sql.Sql 
class InsertData {

def dataSource

def show(){
   System.out.println("demo show");
   def sql = new Sql(dataSource)
        def rows = sql.rows("SELECT fullName from User")

        rows.each { row ->
                System.out.println("demo data"+row.fullName);
            log.debug row.fullName
        }

        sql.close()
}


class User {
        String userName
        String password
        String fullName
        String toString(){
            "${fullName}"
        }
    static constraints = {
        fullName();
        userName(unique:true);
        password(password:true);
    }
}

can anybody suggest how to solve this i want to know how to write the controller and model for this

thank you,

Answer 1

Instead of using def sql = new Sql(dataSource) in domain class,

use def sql = Sql.newInstance(dataSource)

Your Domain class can be modified as

import groovy.sql.Sql 
class InsertData {
      
def dataSource
         
def show(){
    def sql = Sql.newInstance(dataSource)
    sql.eachRow("SELECT fullName from User") {
        println "the fullname is ${it.fullname}"
    }
}

And your Controller

class InsertDataController {
    def index() { }
    def sample() {
        InsertData insertData=new InsertData();
        insertData.show();
    }
}