iT邦幫忙

DAY 9
1

邊看邊學Groovy/Grails/Gradle系列 第 6

Groovy基本語法(4)-Groovy JDBC語法(1)以MySQL為例

Groovy存取Database也是透過JDBC機制,只是Groovy的語法比較簡單加上Groovy支援closure,像是select * from xxx,然後print出來,語法比起純java的JDBC少了很多行,今天時間關係只能示範最簡單的方法,未來幾天陸續介紹相關方法,另外平常日真的忙碌,每天只能分享些皮毛,假日再與大家分享Groovy如何輕鬆處理XML
要存取Database之前必須兩件事情要做好:

  1. import Groovy.sql.Sql
  2. 加入JDBC driver的jar到classpath中(我是以mysql 5.5為例)

簡單示意圖(我想大家都知道吧!?)擷取自Groovy In Action

再來廢話一下,連接資料庫不外乎:

  1. 資料庫的URI
  2. username
  3. password

以下就以簡單範例說明Groovy連結MySQL並以簡單SQL語法印出資料

import groovy.sql.Sql //使用Groovy JDBC必須import &注意大小寫

//Sql.newInstance意即建立一個連線
def conn=Sql.newInstance("jdbc:mysql://localhost/goo", // DB的URI
                                            "root",    // UserName
                                            "lga2011", // Password
                                            "com.mysql.jdbc.Driver") //Driver, 要記得加入jar檔
                                            
def records="select count(*) from sysdatepriority where priority = 1"

conn.eachRow(records){ count-> println "Total Records are ${count[0]}"} //當然支援SQL函數囉                                        

conn.eachRow("select * from sysdatepriority where priority =1"){ //eachRow方法類似於while(resultset.next())
    println "System: ${it.system}, Deadline: ${it.deadline}, Priority: ${it.priority}" //用GString印出每一筆資料
}

輸出結果:

Total Records are 31
System: C85, Deadline: 2013/05/30, Priority: 1
System: H23, Deadline: 2013/07/31, Priority: 1
System: K15, Deadline: 2013/05/30, Priority: 1
System: N11, Deadline: 2013/06/15, Priority: 1
System: N15, Deadline: 2013/05/15, Priority: 1
System: N23, Deadline: 2013/05/30, Priority: 1
System: N31, Deadline: 2013/07/30, Priority: 1
System: N32, Deadline: 2013/05/30, Priority: 1
System: N37, Deadline: 2013/06/15, Priority: 1
System: N38, Deadline: 2013/05/30, Priority: 1
System: N43, Deadline: 2013/04/30, Priority: 1
System: N61, Deadline: 2013/04/30, Priority: 1
System: P11, Deadline: 2013/05/15, Priority: 1
System: P27, Deadline: 2013/04/30, Priority: 1
System: P28, Deadline: 2013/05/15, Priority: 1
System: P30, Deadline: 2013/06/15, Priority: 1
System: P51, Deadline: 2013/05/30, Priority: 1
System: P54, Deadline: 2013/06/30, Priority: 1
System: P56, Deadline: 2013/06/30, Priority: 1
System: P61, Deadline: 2013/05/15, Priority: 1
System: R10, Deadline: 2013/05/15, Priority: 1
System: R14, Deadline: 2013/06/30, Priority: 1
System: T40, Deadline: 2013/04/15, Priority: 1
System: T55, Deadline: 2013/03/31, Priority: 1
System: T57, Deadline: 2013/06/30, Priority: 1
System: T64, Deadline: 2013/05/30, Priority: 1
System: W12, Deadline: 2013/06/30, Priority: 1
System: Y11, Deadline: 2013/06/30, Priority: 1
System: Y47, Deadline: 2013/06/15, Priority: 1
System: Y53, Deadline: 2013/05/15, Priority: 1
System: Y54, Deadline: 2013/04/30, Priority: 1

除此之外,Groovy版的JDBC當然也可以用語法create table schema,以及以下的方法

void  eachRow  String statement { row -> code }
void  eachRow  String prepStmt, List values { row -> code }
void  eachRow  GString prepStmt { row -> code }
void  query  String statement { resultSet -> code }
void  query  String prepStmt, List values { resultSet -> code }
void  query  GString prepStmt { resultSet -> code }
List  rows  String statement
List  rows  String prepStmt, List values
List  rows  GString prepStmt
Object  firstRow  String statement
Object  firstRow  String prepStmt, List values
Object  firstRow  GString prepStmt

看到prepStmt對於java人應該不陌生吧!?往後幾天再跟大家分享相關方法如何用


上一篇
Groovy基本語法(2)-Closure(3)
下一篇
Groovy基本語法(4)-Groovy JDBC語法(2)-Create Table及Insert語法
系列文
邊看邊學Groovy/Grails/Gradle27

尚未有邦友留言

立即登入留言