java - Groovy SQL 框架

Question

嗨，我想使用 Groovy Sql 设计一个类我将能够在未来的任何与数据库相关的项目中使用它。目前我想出了下面的类，它具有针对各个查询的单独功能，我想了解如何使其通用!这样我只需要传递1个查询和一些绑定(bind)参数(如果不传递null)请帮我找到解决方案

我的代码

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
  }
}

我的预言机表

CREATE TABLE PRODUCTINFO
(     "ID" NUMBER NOT NULL ENABLE, 
      "NAME" NVARCHAR2(200), 
      "DESCRIPTION" NVARCHAR2(200), 
      "ACTIVE" NVARCHAR2(2), 
      "RELEASE_DATE" DATE, 
       PRIMARY KEY ("ID")
)

用于查找最大 ID(选择)

ProductInfo pinfo= new ProductInfo();
int max=pinfo.executeSelectMax("select max(id) as max from productinfo");

用于更新

Object[] paramValues={iid,name,desc,"A",date,active};
pinfo.executeUpdation("update productinfo set name=?, description=?, release_date=?, active=? where id=?",paramValues);

用于插入

Object[] paramValues={max+1,name,desc,"A",date};
pinfo.executeInsert("insert into productinfo(ID,NAME,DESCRIPTION,ACTIVE,RELEASE_DATE) values (?,?,?,?,?)",paramValues);

另一个选择

List result=pinfo.executeSelection("select ID,NAME,DESCRIPTION,ACTIVE,RELEASE_DATE from productinfo where ACTIVE='A'");

Answer 1

怎么样:

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 = []
    sql.eachRow(query) { 
      result << [ it.id, it.name, it.description, it.active, it.release_date ]
    }
    result
  }

  public void executeInsert( GString query ) {
    sql.execute( query )
  }

  public void executeUpdation( GString query ) {
    sql.executeUpdate( query )
  }

  public int executeSelectMax( String query ) {
    sql.firstRow(query)?.max ?: 0
  }
}

然后，您的更新和插入示例将变为:

更新:

pinfo.executeUpdation( "update productinfo set name=$name, description=$desc, release_date=$date, active=${'A'} where id=$iid" )

插入:

pinfo.executeInsert( "insert into productinfo(ID,NAME,DESCRIPTION,ACTIVE,RELEASE_DATE) values (${max+1}, $name, $desc, ${'A'}, $date )" )

正如你所看到的...你的很多代码只是简单地包装了东西 that already exist在 groovy.sql.Sql 类中