Groovy笔记(9)_GroovySql

GroovySql概述

1、GSql结合利用闭包和迭代器，把资源管理的负担转移到Groovy框架本身，从而简化JDBC编程

2、不用关闭Conne，也不用关闭ResultSet

3、Groovy把迭代器编程隐含的，使用起来更简单。

     def coll = ["groovy","grails","java"]

     coll.each{tiem -> println item}

建库建表

1、创建MySQL数据库groovytoysstore

      show databases;

      create database groovysstore

      character set utf8

2、建表toys,包含toyName，unitPrice 两列

     use groovytoysstore

     create table toys(toyName varchar(50) not null,

                                 unitPrice varchar(20))

建立数据库连接

1、import groovy.sql.*

     def db = 'jdbc:mysql:  //主机/数据库

                    ?useUnicode=true&characterEncoding=UTF-8'

     def user = 'root'

     def password = 'clat'

     def driver = 'com.mysql.jdbc.Driver'

     def sql = Sql.newInstahnce(db,user,password,driver)

      // 需要把mysql的驱动jar包拷贝到项目lib目录下

数据库CRUD

1、sql.execute("delete from toys")

     sql.execute("insert into toys values('toy1','100')")

     sql.eachRow("select * from toys"){toy ->

     println '/t/t'+toy.toyName + '/t' +toy.unitPrice}

2、def searchToyName = "toy1"

     sql.eachRow("select * from toys where toyName =?",[searchToyName]){toy ->

             println '/t/t' + toy.toyName +'/t' +toy.unitPrice}

3、def updateToyName = 'toy1'

     def updateUnitPrice = '1000'

      sql.execute("update toys set unitPrice= ? where toyName =? ",[updateUnitPrice,updateToyName])

操作DataSet(简化操作)

1、DataSet类是Sql类的子类（可以用Jad查看）

2、def toys = sql.dataSet('toys')

      //dataSet()参数是表名

      public DataSet dataSet(String tableName){

          return new DataSet(this, talbe);

      }

3、toys.add(toyName:'toy8',unitPrice:'800')

     //public void add(Map values) throws SQLException

4、toys.each{toy ->

            println toy.toyName + ':' + toy.unitPrice}

5、例子

     def sql = Sql.newInstance(db,user,password,driver)

     def toys = sql.dataSet('toys')

     def list = toys.rows()  //返回列表

     println list.class

     list.each{      //遍历列表

         println it

     }

      def t = toys.firstRow()    //获取第一行 ,lastRow()最后一行

      println t

简单OR Mapping

1、SqlQuery抽象类（使用模板方法模式）

     abstract class SqlQuery{

         def sql ;   def query

         def SqlQuery(sql,query){

               this.sql = sql;  this.query = query

          }

         def abstract mapRow(row)    //映射方法先定义，由子类实现

         def execute(){

              def rowsList = sql.rows(query)

              def results = []

              def size = rowsList.size()

              0.upto(size - 1){ index ->

                  results << this.mapRow(rowsList[index])

              }

              return results

          }

     }

2、class ToyQuery extends SqlQuery{        //子类实现mapRow()方法

            def ToyQuery(sql){

                    super(sql,'select * from toys')

             }

            def mapRow(row){

                    def acc = new Toy(toyName: row.getProperty("toyName"),

                                                  unitPrice: row.getProperty("unitPrice"))

                return acc

             }

     }

3、使用上面的类 完成简单ORM  , 数据库 -》 对象

    def db = 'jdbc:mysql://localhost/groovytoysstore?useUnicode=true&characterEncoding=UTF-8'

    def  user= 'root'

    def password = 'clat'

    def driver = 'com.myql.jdbc.Driver'

    def sql = Sql.newInstance(db,user,password,driver)

    def toyQuery = new ToyQuery(sql)

    def toys = toyQuery.execute()

    toys.each{ toy -> println toy.toyName}