groovy学习7-groovy sql - 雪霁霜飞 - 博客园

groovy学习7-groovy sql - 雪霁霜飞 - 博客园

groovy学习7-groovy sql

发现groovy操作数据库很简单，一行import，2行操作代码。

 

 

      
      
      


       
       
       
       
       
       import groovy.sql.Sql 
       
       
       /*
       
       
       * * @author <a href="mailto:[e-mail]">张挺</a> * @since 2010-4-2 15:07:35 * 
       
       
       */
       
       
        piEstimate 
       
       
       =
       
       
        
       
       
       3
       
       
       ; println(
       
       
       "
       
       
       Pi is about ${piEstimate}
       
       
       "
       
       
       ); println(
       
       
       "
       
       
       Pi is closer to ${22 / 7}
       
       
       "
       
       
       ); 
       
       
       //
       
       
       使用Sql对象查询
       
       
        //
       
       
       第一个it是普通的对象应用，第二个it是groovy表达式
       
       
        
       
       
       sql 
       
       
       =
       
       
        Sql.newInstance(
       
       
       "
       
       
       jdbc:mysql://localhost:3306/test
       
       
       "
       
       
       , 
       
       
       "
       
       
       root
       
       
       "
       
       
       , 
       
       
       "
       
       
       xxxxxxx
       
       
       "
       
       
       , 
       
       
       "
       
       
       com.mysql.jdbc.Driver
       
       
       "
       
       
       ) sql.eachRow(
       
       
       "
       
       
       select * from user
       
       
       "
       
       
       , { println it.id 
       
       
       +
       
       
        
       
       
       "
       
       
        -- ${it.username} --
       
       
       "
       
       
       }); 
       
       
       //
       
       
       取第一行
       
       
        
       
       
       row 
       
       
       =
       
       
        sql.firstRow(
       
       
       "
       
       
       select username, password from user
       
       
       "
       
       
       ) println 
       
       
       "
       
       
       Row: columnA = ${row.username} and columnB = ${row.password}
       
       
       "
       
       
        
       
       
       //
       
       
       执行插入
       
       
        
       
       
       username 
       
       
       =
       
       
        
       
       
       "
       
       
       yue
       
       
       "
       
       
        password 
       
       
       =
       
       
        
       
       
       "
       
       
       O'shea
       
       
       "
       
       
        sql.execute(
       
       
       "
       
       
       insert into user (username, password) values (${username}, ${password})
       
       
       "
       
       
       ) 
       
       
       //
       
       
       another insert demo use prepareStatment
       
       
        
       
       
       username 
       
       
       =
       
       
        
       
       
       "
       
       
       yue
       
       
       "
       
       
        password 
       
       
       =
       
       
        
       
       
       "
       
       
       wu
       
       
       "
       
       
        sql.execute(
       
       
       "
       
       
       insert into user (username, password) values (?,?)
       
       
       "
       
       
       , [username, password]) 
       
       
       //
       
       
       更新操作，也可以用sql.execute("update user set password = ? where id=?", [comment,4])
       
       
        
       
       
       comment 
       
       
       =
       
       
        
       
       
       "
       
       
       test
       
       
       "
       
       
        sql.executeUpdate(
       
       
       "
       
       
       update user set password = ? where id=?
       
       
       "
       
       
       , [comment,
       
       
       4
       
       
       ]) 
       
       
       //
       
       
       delete
       
       
        
       
       
       sql.execute(
       
       
       "
       
       
       delete from word where word_id = ?
       
       
       "
       
       
        , [
       
       
       5
       
       
       ]) 
       
       
       //
       
       
       在业务逻辑层就可以这么用直接返回list
       
       
        
       
       
       def getPersons() { def persons 
       
       
       =
       
       
        [] sql.eachRow(
       
       
       "
       
       
       Select * from user
       
       
       "
       
       
       ) { persons 
       
       
       <<
       
       
        it.toRowResult() } 
       
       
       return
       
       
        persons } 
       
       
       //
       
       
       如果返回的类的属性和表明一致的话就可以这么做
       
       
        
       
       
       Person p 
       
       
       =
       
       
        
       
       
       new
       
       
        Person( it.toRowResult() )