PostgreSQL configuration on Grails

How to : configure PostgreSQL on Grails
http://stackoverflow.com/questions/11535537/how-to-config-postgresql-to-grails-application

• Download the driver from http://jdbc.postgresql.org/download.html and copy it to the lib diretory of your project.
• Add to DataSource.groovy

dataSource {
pooled = true
driverClassName = "org.postgresql.Driver"
dialect = "org.hibernate.dialect.PostgreSQLDialect"
username = "username"
password = "password"
}
development {
dataSource {
dbCreate = "update" // one of 'create', 'create-drop', 'update', 'validate', ''
url = "jdbc:postgresql://localhost:5432/postgres"
}
}

Grails: Customizing Pagination and Sorting on Search Results

I wanted to add a search bar somewhere in my list.gsp, and have the search results reuse that page to display the list of matches.
This is my page

This is the expected output. A search bar on top of the list, where a user can search by root, or rootMeaning. The search results must be displayed on list.gsp.

Setup
1. This is the domain class:
 package sdb1

class Word {
    String root, rootMeaning and a bunch of other attributes.
    }

2. Go to the terminal and execute a generate-all command for the Word domain. This command will generate the controllers, and views which we can use later.
 
3. Open list.gsp and add this code to embed a search form on the page.

<g:form action="list" method="GET">
        <div class="fieldcontain">
            <label for="query">Search for </label>
            <g:textField name="query" value="${params.query}"/>
          <g:radioGroup name="category"
              labels="['root','rootMeaning']"
              values="['root', 'rootMeaning']"
              value="${params.category}"
              >
<p>${it.label} ${it.radio}</p>
</g:radioGroup>
        </div>

    </g:form>

 



4. Open WordController and add this in list function
        def searchCategory = params.category
        // this is needed for the first time to pass to the view.
        if(searchCategory==null) { searchCategory="root"
            params.category="root"
        }
def wordList = Word.createCriteria().list (params) {
            if ( params.query ) {
                        ilike("${searchCategory}", "%${params.query}%")
            }
        }
        [wordInstanceList: wordList,wordInstanceTotal: wordList.totalCount]
     5. The search results are now displayed. For the sorting to work , I added this to the list.gsp    for each of the columns.
                      

      

<g:sortableColumn property="root" title="${message(code: 'word.root.label', default: 'Root')}" params="[query:params.query, category:params.category]" />

Just like any other element, g:sortableColumn actually accepts params and this is the key to our sorting problem. If the user is on the search results, I simply pass around the query that he entered, it goes back to the controller and does another query on the database, this time, with a different sorting order.

7. Finally! For the pagination, here is a snippet from list.gsp

<div class="pagination">
<g:if test="${!params.query}">
<g:paginate total="${wordInstanceTotal}"/>
</g:if>
<g:else>
<g:paginate total="${wordInstanceTotal}" 
        params="[query:params.query, category:params.category]" />
</g:else>

</div>

Just like the g:sortableColumn above, I also passed around the query and the Category so that the paginate would work flawlessly! 

I took followed the ideas very closely on this site
http://www.icodeya.com/2012/05/grails-pagination-and-sorting-on-search.html