Groovy 数据库应用开发实践-SQL对象托管和封装

author:wjw


Groovy SQL 重复创建时,将导致本地嵌入式数据库锁定,如Hsqldb,sqlite等,为保存系统中只存一份SQL对象,通过一个单例模式的工具类实现对SQL的托管,并统一归数据库访问层调用.

示例代码:


class GroovySqlHsqlDbUtil {

        def sql;
        def static instance
        
    //单例模式
        static {
            instance=new GroovySqlHsqlDbUtil()
        
        }
        
    static def getInstance(){
         if(instance==null){
               instance=new GroovySqlHsqlDbUtil()
         }
         return   instance
     }   
     
    private GroovySqlHsqlDbUtil(){
         getGroovySql()
    }
    
    //获取Groovy 数据库服务对象 Sql    
   public Sql  getGroovySql(){
        
        def dbc= new GroovySqlDataSourceConfig();
        //jdbc:hsqldb:mem:testDB
        //
         def db = [url:'jdbc:hsqldb:file:E:/WjWork/MIS/med_cmp_for_saler_prj/db/plugs_gv_web_db', user:'SA', password:'SA', driver:'org.hsqldb.jdbc.JDBCDriver']
        if(this.sql==null){
               // def sql=Sql.newInstance("jdbc:jtds:sqlserver://localhost:4687/shield","sa","sa","net.sourceforge.jtds.jdbc.Driver");
             
            //sqlserver
         //   def sql=Sql.newInstance("jdbc:jtds:sqlserver://${dbc.getHost()}:${dbc.getPort()}/${dbc.getDbName()}","${dbc.getUser()}","${dbc.getPassword()}","net.sourceforge.jtds.jdbc.Driver");
           def sql = Sql.newInstance(db.url, db.user, db.password, db.driver)
   
            //hsqldb
                        
            this.sql=sql;
        }else{
            return this.sql;
        }
                        
       
    }

}



Hsqldb实现:


/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package apps.plugs.groovyExt.dbUtil
import groovy.sql.Sql
import java.sql.Connection;

import net.sourceforge.jtds.jdbc.Driver

import config.GroovySqlDataSourceConfig
import org.hsqldb.jdbc.JDBCDriver

/*
 * 单例模式
 * 加载并托管groovy SQL对象
 *
 * apps.plugs.groovyExt.dbUtil.GroovySqlHsqlDbUtil
 * @author Administrator
 *
 */
class GroovySqlHsqlDbUtil {

        def sql;
        def static instance
        
    //单例模式
        static {
            instance=new GroovySqlHsqlDbUtil()
        
        }
        
    static def getInstance(){
         if(instance==null){
               instance=new GroovySqlHsqlDbUtil()
         }
         return   instance
     }   
     
    private GroovySqlHsqlDbUtil(){
         getGroovySql()
    }
    
    //获取Groovy 数据库服务对象 Sql    
   public Sql  getGroovySql(){
        
        def dbc= new GroovySqlDataSourceConfig();
        //jdbc:hsqldb:mem:testDB
        //
         def db = [url:'jdbc:hsqldb:file:E:/WjWork/MIS/med_cmp_for_saler_prj/db/plugs_gv_web_db', user:'SA', password:'SA', driver:'org.hsqldb.jdbc.JDBCDriver']
        if(this.sql==null){
               // def sql=Sql.newInstance("jdbc:jtds:sqlserver://localhost:4687/shield","sa","sa","net.sourceforge.jtds.jdbc.Driver");
             
            //sqlserver
         //   def sql=Sql.newInstance("jdbc:jtds:sqlserver://${dbc.getHost()}:${dbc.getPort()}/${dbc.getDbName()}","${dbc.getUser()}","${dbc.getPassword()}","net.sourceforge.jtds.jdbc.Driver");
           def sql = Sql.newInstance(db.url, db.user, db.password, db.driver)
   
            //hsqldb
                        
            this.sql=sql;
        }else{
            return this.sql;
        }
                        
       
    }
    
 //测试代码
    
    static void main(argvs){
        
        
        def dbutil= GroovySqlHsqlDbUtil.getInstance()
           
        
     def sql=   dbutil.getGroovySql();
        
     
           def sql_id_list="SELECT * FROM BASE_SYS_USER"
           
            def collection=new ArrayList<Long>();
   
            sql.eachRow(sql_id_list){
                row  ->
             println "${ row.Id}"
                
            collection.add(row.Id)
            }
        
    }
    
    
}

SQLServer 实现:


/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package apps.plugs.groovyExt.dbUtil
import groovy.sql.Sql
import java.sql.Connection;

import net.sourceforge.jtds.jdbc.Driver

import config.GroovySqlDataSourceConfig
/**
 * * 单例模式
 * 加载并托管groovy SQL对象
 * @author Administrator
 */
class DefaultGroovySqlDbUtil {

        def sql;
        
     //单例模式
        def static instance
       
        
        static {
            instance=new DefaultGroovySqlDbUtil()
        
        }
        
    static def getInstance(){
         if(instance==null){
               instance=new DefaultGroovySqlDbUtil()
         }
         return   instance
     }  
    
    //获取Groovy 数据库服务对象 Sql    
   public Sql  getGroovySql(){
        
        def dbc= new GroovySqlDataSourceConfig();
        
        if(this.sql==null){
               // def sql=Sql.newInstance("jdbc:jtds:sqlserver://localhost:4687/shield","sa","sa","net.sourceforge.jtds.jdbc.Driver");
             
            //sqlserver
            def sql=Sql.newInstance("jdbc:jtds:sqlserver://${dbc.getHost()}:${dbc.getPort()}/${dbc.getDbName()}","${dbc.getUser()}","${dbc.getPassword()}","net.sourceforge.jtds.jdbc.Driver");
             
            //hsqldb
                        
            this.sql=sql;
        }else{
            return this.sql;
        }
                        
       
    }
    
 //测试代码
    
    static void main(argvs){
        
        
        def dbutil= DefaultGroovySqlDbUtil.getInstance()
        
    
            def sql_str="select count( *)as row_size from Vmember_basic_list where  userid=1989 and  type=1 and auditing=1 and ( trueeship ='0' ) and (langid is null or langid=1)"
        
        
        Sql sql=dbutil.getGroovySql()
         
        def rs
        
       // def c=0
        
        
        sql.eachRow(sql_str){
            
            row_total ->
            
           //获取数据组值
           println row_total.row_size
           
          rs=row_total.row_size
            
           // c++
           // println c
            
        }
        
    }
}