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,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,driver)

??

????def toyQuery = new ToyQuery(sql)

??? def toys?= toyQuery.execute()

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