sqlgrailsgrails-2.0gsp

How to pass sql query in grails


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,


Solution

  • 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();
        }
    }