title: groovy sql framework
body: <p>Hi I want to design a class using <a href="http://docs.codehaus.org/display/GROOVY/Tutorial+6+-+Groovy+SQL" rel="nofollow">Groovy Sql</a> which I will be able to use in any of my future db related projects. At present I came up with the below class, which has separate functions for individual queries, I want to learn how can I make it generic! So that I just need to pass <code>1 query</code> and some <code>bind parameters (if not pass null)</code> Please help me get a solution</p>
  
  <p>My code</p>
  
  <pre><code>import java.sql.*;
  import java.util.List;
  import groovy.sql.Sql
  
  public class ProductInfo {
  
    ReadProperty prop
  
    def sql
    public ProductInfo()
    {
      prop=ReadProperty.getInstance("db")
      sql = Sql.newInstance("jdbc:oracle:thin:@"+prop.getProperty("hostname")+":"+prop.getProperty("port")+":"+prop.getProperty("service"), "asimonc", "asimon","oracle.jdbc.OracleDriver")
    }
  
    public List executeSelection(String query)
    {
      List result=new ArrayList()
      sql.eachRow(query)
      { 
        String[] rows=new String[5]
        rows[0]=(String)it.id
        rows[1]=(String)it.name
        rows[2]=(String)it.description
        rows[3]=(String)it.active
        rows[4]=(String)it.release_date
  
        result.add(rows)
      }
      return result
    }
  
    public executeInsert(String query,Object[] paramValues)
    {
      sql.execute(query,[paramValues[0], paramValues[1],paramValues[2], paramValues[3], paramValues[4]])
    }
  
    public executeUpdation(String query,Object[] paramValues)
    {
      sql.executeUpdate(query,[paramValues[1], paramValues[2],paramValues[4],paramValues[5], paramValues[0]])
    }
  
    public int executeSelectMax(String query)
    {
      int max     
      sql.eachRow(query) 
      {
        max=it.max
      }
      if(max==null)
        return 0
      else
        return max
    }
  }
  </code></pre>
  
  <p>My oracle table</p>
  
  <pre><code>CREATE TABLE PRODUCTINFO
  (     "ID" NUMBER NOT NULL ENABLE, 
        "NAME" NVARCHAR2(200), 
        "DESCRIPTION" NVARCHAR2(200), 
        "ACTIVE" NVARCHAR2(2), 
        "RELEASE_DATE" DATE, 
         PRIMARY KEY ("ID")
  )
  </code></pre>
  
  <p>for finding out max id (<strong>selection</strong>)</p>
  
  <pre><code>ProductInfo pinfo= new ProductInfo();
  int max=pinfo.executeSelectMax("select max(id) as max from productinfo");
  </code></pre>
  
  <p>for <strong>updation</strong></p>
  
  <pre><code>Object[] paramValues={iid,name,desc,"A",date,active};
  pinfo.executeUpdation("update productinfo set name=?, description=?, release_date=?, active=? where id=?",paramValues);
  </code></pre>
  
  <p>for <strong>insertion</strong></p>
  
  <pre><code>Object[] paramValues={max+1,name,desc,"A",date};
  pinfo.executeInsert("insert into productinfo(ID,NAME,DESCRIPTION,ACTIVE,RELEASE_DATE) values (?,?,?,?,?)",paramValues);
  </code></pre>
  
  <p>for yet another <strong>selection</strong></p>
  
  <pre><code>List result=pinfo.executeSelection("select ID,NAME,DESCRIPTION,ACTIVE,RELEASE_DATE from productinfo where ACTIVE='A'");
  </code></pre>