第十三章、Groovy数据库

1、Groovy SQL： 一个好的JDBC

• 连接数据库

• import com.mysql.cj.jdbc.MysqlDataSource
  import groovy.sql.Sql
  
  /**
   * @author liangchen* @date 2020/11/26
   */
  @Grab(group='mysql', module='mysql-connector-java', version='8.0.17')
  // 13.1 连接数据库
  def  url = 'jdbc:mysql://localhost:3306/test?characterEncoding=utf-8'
  def user ='root'
  def password='cl123456'
  def driver = 'com.mysql.cj.jdbc.Driver'
  def sql = Sql.newInstance(url, user, password, driver)
  // 连上数据库
  sql.query('select * from demo', {it -> println it});
  sql.close()
  
  
  // 使用DataSource创建连接
  
  def dataSource = new MysqlDataSource(
          url:"jdbc:mysql://localhost:3306/test?characterEncoding=utf-8",
          user:'root',
          password:'cl123456'
  )
  sql = new Sql(dataSource)
  sql.close()
  

• 数据库基本操作

  • package com.jack.groovy.ch13
    
    import com.mysql.cj.jdbc.MysqlDataSource
    import groovy.sql.Sql
    
    import java.text.SimpleDateFormat
    import java.util.logging.Level
    import java.util.logging.Logger
    Logger.getLogger('_13_1_2ExecutingSQL.groovy' ).level = Level.FINE
    
    /**
     * @author liangchen* @date 2020/11/26
     */
    // 这里需要注意mysql-connector的版本
    @Grab(group='mysql', module='mysql-connector-java', version='8.0.17')
    def dataSource = new MysqlDataSource(
            url:"jdbc:mysql://localhost:3306/test?characterEncoding=utf-8",
            user:'root',
            password:'cl123456'
    )
    sql = new Sql(dataSource)
    
    sql.execute '''
        DROP TABLE  IF EXISTS Athlete;
    
      
    '''
    sql.execute '''
        CREATE TABLE Athlete (
    
        firstname   VARCHAR(64),
    
        lastname    VARCHAR(64),
    
        dateOfBirth DATE
    
      );
    '''
    sql.close()
    
    // 定义数据库工具类
    class DbUtil{
        static  Sql create(){
            def url="jdbc:mysql://localhost:3306/test?characterEncoding=utf-8"
            def user='root'
            def password = 'cl123456'
            def driver = 'com.mysql.cj.jdbc.Driver'
            def sql = Sql.newInstance(url, user, password, driver)
    
            sql.execute """
                 DROP TABLE  IF EXISTS Record, Athlete,AAA;
     
    """
            sql.execute"""
        CREATE TABLE Athlete (
    
                  athleteId   INTEGER ,
    
                  firstname   VARCHAR(64),
    
                  lastname    VARCHAR(64),
    
                  dateOfBirth DATE,
    
                  UNIQUE(athleteId)
    
                );
    """
            // 返回SQL语句
            sql
        }
        static  void init(sql){
            def qry = '''
        INSERT INTO Athlete(firstname, lastname, dateOfBirth) VALUES(?,?,?)
    '''
            sql.withBatch (3, qry){ps->
                ps.addBatch('Paula',     'Radcliffe',   '1973-12-17')
    
                ps.addBatch('Catherine', 'Ndereba',     '1972-07-21')
    
                ps.addBatch('Naoko',     'Takahashi',   '1972-05-06')
    
                ps.addBatch('Tegla',     'Loroupe',     '1973-05-09')
    
                ps.addBatch('Ingrid',    'Kristiansen', '1956-03-21')
    
            }
        }
    
    }
    
    def sql = DbUtil.create()
    sql.execute '''
        INSERT INTO Athlete(firstname, lastname, dateOfBirth) 
            VALUES('Paul', 'Tergat', '1969-06-17');
            
    '''
    //预编译
    def athleteInsert='''
           INSERT INTO Athlete (firstname, lastname, dateOfBirth) 
           VALUES (?,?,?);
    '''
    //预编译插入数据
    sql.execute athleteInsert, ['Khalid', 'Khannouchi', '1971-12-22']
    
    // 使用变量插入数据
    
    def data = [first: 'Ronaldo', last: 'da Costa', birth: '1970-06-07']
    sql.execute """
        INSERT INTO Athlete (firstname, lastname, dateOfBirth)
        VALUES (${data.first}, ${data.last}, ${data.birth});
    """
    //sql.close()
    
    //  查询操作 居中，不足用-填充 （ 通过字段名获取）
    println 'Athlete Info '.center(25, '-')
    def fmt = new SimpleDateFormat('dd. MMM yyyy (E)', Locale.US)
    sql.eachRow('SELECT * FROM Athlete'){
        athlete ->
            println athlete.firstname + ' ' + athlete.lastname
            println 'born on ' + fmt.format(athlete.dateOfBirth)
            print '-'*25
            println ''
    }
    
    //分行打印 ，通过索引下标获取
    sql.eachRow('SELECT firstname, lastname FROM Athlete'){
        row->
            println row[0] + ' ' + row[1]
    }
    
    // 通过结果集（ResultSet)
    sql.query('SELECT firstname, lastname FROM Athlete') { resultSet ->
        if (resultSet.next()) {
            print resultSet.getString(1)
            print ' '
            println resultSet.getString('lastname')
        }
    }
    //查询条数等于3条
    assert  sql.firstRow('SELECT COUNT(*) AS num FROM Athlete').num == 3
    
    // 插入数据
    sql.execute '''
    INSERT INTO Athlete(lastname) VALUES('da Costa')
    '''
    //更新数据
    sql.execute '''
    UPDATE Athlete SET firstname='Ronaldo' where lastname='da Costa'
    '''
    // 返回影响行数
    def updateCount = sql.executeUpdate '''
        UPDATE Athlete SET dateOfBirth='1970-06-07' where lastname = 'da Costa'
    '''
    assert updateCount == 2
    
    // 删除数据
    sql.execute "DELETE FROM Athlete WHERE lastname='Tergat'"
    assert  sql.firstRow('SELECT COUNT(*) as num FROM Athlete').num ==3
    sql.close()
    
    
    
    
    

2、Groovy SQL 高级特性

• 批量处理

• // 批量
  
  
  sql = DbUtil.create();
  sql.execute '''
  CREATE TABLE Record (
  
    runId       INTEGER ,
  
    time        INTEGER,    -- in seconds
  
    venue       VARCHAR(64),
  
    whenRun     DATE,
  
    fkAthlete   INTEGER,
  
    CONSTRAINT fk FOREIGN KEY (fkAthlete)
  
      REFERENCES Athlete (athleteId) ON DELETE CASCADE
  
  );
  '''
  sql.withBatch {
      stmt ->
          stmt.addBatch '''
      INSERT INTO Athlete(firstname,lastname, dateOfBirth)
         VALUES('Paula', 'Radclife','1973-12-17')
  '''
  }
  
  
  def qry = '''
      INSERT INTO Athlete(firstname, lastname, dateOfBirth) VALUES(?,?,?)
  '''
  sql.withBatch (3, qry){ps->
      ps.addBatch('Paula',     'Radcliffe',   '1973-12-17')
  
      ps.addBatch('Catherine', 'Ndereba',     '1972-07-21')
  
      ps.addBatch('Naoko',     'Takahashi',   '1972-05-06')
  
      ps.addBatch('Tegla',     'Loroupe',     '1973-05-09')
  
      ps.addBatch('Ingrid',    'Kristiansen', '1956-03-21')
  
  }
  
  
  

• 分页

  • // 分页
    def sql = DbUtil.create()
    DbUtil.init(sql)
    def qry = 'SELECT * FROM Athlete'
    assert sql.rows(qry, 1, 2)*.lastname == ['Radcliffe', 'Ndereba']
    assert sql.rows(qry, 3,2)*.lastname ==  ['Takahashi', 'Loroupe']
    
    

• 使用元数据闭包

  • // 使用元数据闭包
    def sql = DbUtil.create()
    def dump2 (sql, tableName){
        def printColNames = { meta ->
            def width = meta.columnCount * 12
            println "CONTENT OF TABLE ${tableName}".center(width, '-')
            (1..meta.columnCount).each {
                print meta.getColumnLabel(it).padRight(12)
            }
            println()
            println '-'*width
    
        }
        def printRow = {row ->
            row.toRowResult().values().each{
                print it.toString().padRight(12)
            }
            println()
        }
    
        sql.eachRow('SELECT * FROM ' + tableName, printColNames, printRow)
    }
    
    def baos = new ByteArrayOutputStream()
    System.setOut(new PrintStream(baos))
    dump2(sql, 'Athlete')
    println baos.toString().readLines()*.trim().join('\n')
    
    baos.reset()
    dump2(sql, 'Record')
    
    

• 使用不同数据类型新增到数据库

  • import groovy.transform.Canonical
    
    /**
     * @author liangchen* @date 2020/12/1
     */
    
    sql = DbUtil.create()
    def  insertPrefix = '''
    INSERT INTO Athlete (firstname, lastname, dateOfBirth)
    VALUES
    '''
    def loroup = [first: 'Tegla', last: 'Loroupe', dob: '1973-05-09']
    
    sql.execute insertPrefix + '(:first, :last,:dob)', loroup
    
    // 或者
    sql.execute insertPrefix + '(:first,:last,:dbo)',
    first: 'Ingrid', last:'Kristiansm', dob:'1956-03-21'
    
    //或者声明一个域对象
    @Canonical
    class Athlete{
        String first,last,dob
    }
    def ndereba = new Athlete('Catherine', 'Ndereba', '1972-07-21')
    sql.execute insertPrefix + '(?.first,?.last,?.dob)', ndereba
    
    //或者如下
    def takahashi = new Athlete('Naoka', 'Takahashi')
    def takahashiExtra = [dob:'1972-05-06']
    sql.execute insertPrefix + '(?1.first,?1.last,?2.dob)', takahashi, takahashiExtra
    
    assert  sql.firstRow('SELECT COUNT(1) as num FROM Athlete').num ==4
    

3、Groovy DataSets

• package com.jack.groovy.ch13
  
  /**
   * @author liangchen* @date 2020/12/1
   */
  
  def sql = DbUtil.create()
  def athletes = sql.dataSet('Athlete')
  
  def result = []
  athletes.each {result << it.firstname}
  assert result == ['Paul', 'Khalid', 'Ronaldo']
  
  athletes.add(
          firstname:'Paula',
          lastname:'Radcliffe',
          dateOfBirth: '1973-12-17'
  )
  result = athletes.rows().collect { it.firstname }
  assert result == ['Paul', 'Khalid', 'Ronaldo', 'Paula']
  
  // 查询大于 '1970-1-1'
  youngsters = athletes.findAll { it.dateOfBirth > '1970-1-1' }
  youngsters.each {
      println it.firstname
  }
  
  // 13.19 使用过滤器
  athletes = sql.dataSet('Athlete')
  athletes.add(
          firstname: 'Paula',
          lastname:'Radcliffe',
          dateOfBirth: '1973-12-17'
  
  )
  def query = athletes.findAll { it.firstname >= 'P' }
  query = query.findAll {it.dateOfBirth > '1970-01-01'}
  query = query.sort {it.dateOfBirth}
  query = query.reverse()
  assert query.sql == 'select * from Athlete where firstname >= ? and '
  + 'dateOfBirth > ? order by dateOfBirth DESC'
  assert  query.parameters == ['P','1970-01-01']
  assert query.rows()*.firstname == ['Paula', 'Ronaldo']
  

4、总结

1. 这章学习很不是很清楚，学会基本操作数据库功能