Groovy CRUD # 1

การเขียน​ groovy ​ให้​ติดต่อ​กับ​ฐานข้อมูล​ ​นั้น​แท้จริง​แล้ว​ groovySQL ​ไป​ใช้​ JDBC API ​ใน​การเชื่อมต่อ​ database ​อีกทีดังนี้
Groovy / Java Application <--> Groovy SQL <--> JDBC API <--> JDBC DriverManager / DataSource <--> Database Server

ข้อมูลที่จำ​เป็น​ต้อง​รู้ก่อนการ​ใช้​งาน​ database

• database URL
  • jdbc:hsqldb:hsql://server/dbname ​ใช้​เชื่อมต่อ​กับ​ HSQLDB server ​รองรับการ​ client ​หรือ​ process ​ที่มากกว่า​ 1
  • jdbc:hsqldb:file:/dir/dbname ​ใช้​เชื่อมต่อ​กับ​ single-client ​โดย​ HSQLDB ​จะ​ใช​ file ​เป็น​ตัวเก็บข้อมูล
  • jdbc:hsqldb:mem:dbname ​ใช้​เชื่อมต่อ​กับ​ database ​ที่ถูกสร้าง​และ​เก็บ​อยู่​ใน​ memory ​ซึ่ง​จะ​ nonpersistent ​เหมาะ​กับ​การเขียน​ application ​เล็กๆ​ ​เอาทดลองทำ​งาน
• username / password ​ใช้​ username: sa (sysadmin) ​และ​ password empty ​(​ไม่​มี)
• driver class name : org.hsqldb.jdbcDriver

ตัวอย่างการเชื่อมต่อฐานข้อมูล​ด้วย​ DriverManager

import groovy.sql.Sql
db = Sql.newInstance('jdbc:hsqldb:mem:GinA', 'sa', '', 'org.hsqldb.jdbcDriver');

DriverManager VS DataSource
ถ้า​ดูตอนการทำ​งานเชื่อมต่อ​กับ​ database ​จะ​เห็นว่ามี​ 2 concept ​คือ​ DriverManager ​และ​ Data Source ​ถ้า​เรา​ใช้​ Sql.newInstance method ​จะ​เป็น​การ​ใช้​ DriverManager ​แต่​ถึง​แม้​เรา​จะ​มี​ DriverManager ​แล้ว​ ​แต่​เพื่อการจัดการประสิทธิภาพที่ดีกว่า​เราควร​ใช้​ DataSource ​เพราะ​มันมีการจัดการการเชื่อมต่อ​กับ​ database ​ที่ดีกว่า​โดย​ใช้​ connect pool (แหล่งเก็บ​ connection ​ที่​ใช้​เชื่อมต่อ​กับ​ database) ​และ​รองรับ​ distributed transaction ​และ​เรา​ยัง​สามารถ​นำ​ connect ​นี้กลับมา​ใช้​ใหม่​ได้​อีก​ (reuse) ​เพราะ​เมื่อแต่ละ​ transaction ​ทำ​งานเสร็จก็​จะ​มีการคืน​ connect ​กลับมาที่​ pool ​แต่​ถ้า​เราลืมคืน​ connection ​ตัว​ Groovy ​จะ​เป็น​คืน​ transaction ​ให้​เอง

DataSource ​จึง​กลายมา​เป็น​ส่วน​สำ​คัญเมื่อ​ต้อง​การจัดการ​กับ​ทรัพยากรที่มี​อยู่​อย่างจำ​กัด​ให้​เกิดประ​โยชน์สูงสุด​ ​เช่น​ใน​ application server ​ก็มีการจัดการทรัพยากร​ใน​ application ​ของตัวเองที่​เป็น​ connection pool

ใน​ Groovy ​เรา​สามารถ​ใช้​ datasource ​ได้​ ​โดย​ขึ้น​อยู่​กับ​ database vendor ​แต่ละ​เจ้า​ซึ่ง​จะ​ implement ​ไว้​ที่​ javax.sql.DataSource ​แต่​ HSQL ​มี​เตรียม​ไว้​ที่​ org.hsqldb.jdbc.jdbcDataSource ​ตัวอย่างการเชื่อมต่อฐานข้อมูล​ด้วย​ DataSource

import groovy.sql.Sql
source = new org.hsqldb.jdbc.jdbcDataSource()
source.database = 'jdbc:hsqldb:mem:GinA'
source.user = 'sa'
source.password = ''
db2 = new Sql(source)

Note: ​ถ้า​เรา​ใช้​ application server ​เรา​สามารถ​เอา​ DataSource ​มา​ได้​โดย​ใช้​ JNDI ​ซึ่ง​ข้อดีของการ​ใช้​วิธีนี้​ ​คือการจัดการเชื่อมต่อ​กับ​ฐานข้อมูล​นั้น​ไม่​ขึ้น​อยู่​กับ​ application ​ของเรา​ ​ใน​ application ​ของเรา​ไม่​ต้อง​ระบุ​ database driver ​หรือ​ DataSource classs ​ซึ่ง​มัน​ช่วย​ให้​เรา​สามารถ​ migrate ​จาก​ฐานข้อมูลหนึ่งไปอีกฐานข้อมูลหนึ่ง​โดย​ไม่​ส่งผลกระทบ​ถึง​ application

Note: ​การ​ให้​ได้​มา​ซึ่ง​ connection ​ถ้า​ใช้​ Datasource ​จะ​ใช้​วิธีส่ง​ DataSource ​เข้า​ไป​ใน​ Sql constructor ​แต่​ถ้า​ใช้​ DriverManager ​จะ​ส่งค่าต่างๆ​ ​เข้า​ไปทาง​ Sql.newInstance

ไม่​ว่า​เรา​จะ​ใช้​ DataSource ​หรือ​ DriverManger ​สุดท้ายก็​จะ​ได้​มา​ซึ่ง​ reference ​ของ​ Sql instance

สุดท้าย​ถ้า​เรา​ต้อง​การ​ clone Sql instance ​ขึ้นมาอีกอัน​สามารถ​ใช้​ new Sql(db) ​ได้​ ​ตอนนี้​เรามี​ sql instance ​ที่พร้อม​จะ​ connect ​กับ​ฐานข้อมูล​แล้ว​ ​และ​เรา​ต้อง​การ​ execute ​บาง​ sql statement

Executing SQL
ใช้​คำ​สั่งง่ายๆ​ db.execute(statement) ​ใน​การติดต่อฐานข้อมูล​โดย​ส่ง​ statement sql ​เข้า​ไป

สร้างฐานข้อมูลนักกีฬา​จะ​ประกอบ​ด้วย​ ​ชื่อ​, ​นามสกุล​, ​วันเกิด​ ​และ​ primary key ​จาก​นั้น​ใส่​ index ​ให้​ตารางที่สร้างขึ้น​ (ฐานข้อมูล​ส่วน​ใหญ่​รวม​ทั้ง​ HSQLDB ​จะ​มีการสร้าง​ index ​ให้​อยู่​แล้ว​โดย​อัตโนมัติ​โดย​จะ​เป็น​ primary ​แต่​เรา​สามารถ​ระบุ​ index ​ที่​ต้อง​การ​จะ​สร้างเพื่อ​ให้​ชัดเจนยิ่งขึ้น​ได้​เช่น​กัน)​

จุดประสงค์ของ​ application ​นี้คือทดสอบการส่งคำ​สั่ง​ sql ​จึง​ไม่​ต้อง​มีการ​ใช้​ข้อมูลของเก่าก็​ได้​ (พยายามยึดหลัก​ agile ​ไว้)​ ​ดัง​นั้น​จึง​ลบ​ table ​และ​ index ​ที่​เราสร้าง​ไว้​ก่อน​แล้ว​ค่อยสร้าง​ใหม่​ (Reconstruct) ​ทุกครั้งที่มีการเรียก​ application ​นี้​ (agile database programming)

db.execute '''
DROP INDEX athleteIdx IF EXISTS;
DROP TABLE Athlete IF EXISTS;
CREATE TABLE Athlete(
athleteId INTEGER GENERATED BY DEFAULT AS IDENTITY,
firstname VARCHAR(64),
lastname VARCHAR(64),
dateOfBirth DATE
);
CREATE INDEX athleteIdx ON Athlete (athleteId);
'''

เพิ่มข้อมูลลง​ใน​ตาราง​ Athelete ​ด้วย​ข้อมูลนักวิ่งมาราธอน

db.execute '''
INSERT INTO Athlete (firstname, lastname, dateOfBirth)
VALUES ('Paul', 'Tergat', '1969-06-17');
INSERT INTO Athlete (firstname, lastname, dateOfBirth)
VALUES ('Khalid', 'Khannouchi', '1971-12-22');
INSERT INTO Athlete (firstname, lastname, dateOfBirth)
VALUES ('Ronaldo', 'da Costa', '1970-06-07');
'''

ถ้า​ใช้​วิธีนี้​ใน​การ​ insert ​ข้อมูล​ทั้ง​โปรแกรมมันยากที่​จะ​อ่าน​และ​จัดการข้อมูล​ทั้ง​หมด​ ​เพราะ​คำ​สั่ง​ sql ​มัน​ซ้ำ​กัน​อยู่​เยอะ​ ​ดัง​นั้น​เรา​จะ​เปลี่ยน​ใหม่​โดย​การ​ใช้​ prepare statement ​เข้า​มา​ช่วย​ prepare statement ​คือมีรุปแบบการเขียนคล้าย​ sql statement ​แต่​จะ​แทนค่าที่รับ​เข้า​มาทางโปรแกรม​ด้วย​เครื่องหมาย​ question mark ​แต่สุดท้ายเครื่องหมาย​ question mark ​จะ​ถูกแทนที่​ด้วย​ค่าที่รับ​เข้า​มาทางโปรแกรม​ ​การ​ใช้​วิธี​เพิ่ม​ reuse ​ได้​ ​ใน​ Java ​มี​ prepare statement ​โดย​ใช้​ java.sql.PreparedStatement interface

String athleteInsert = '''
INSERT INTO Athlete (firstname, lastname, dateOfBirth)
VALUES (?, ?, ?);
'''
db.execute athleteInsert, ['Paul', 'Tergat', '1969-06-17']
db.execute athleteInsert, ['Khalid', 'Khannouchi', '1971-12-22']
db.execute athleteInsert, ['Ronaldo', 'da Costa', '1970-06-07']

เขียน​ใหม่​ให้​อยู่​ใน​รูปแบบ​ closure ​โดย​ส่ง​ map ​เข้า​ไป​เป็น​ argument

def athletes = [
[first: 'Paul', last: 'Tergat', birth: '1969-06-17'],
[first: 'Khalid', last: 'Khannouchi', birth: '1971-12-22'],
[first: 'Ronaldo', last: 'da Costa', birth: '1970-06-07'],
]
athletes.each { athlete ->
db.execute "​""
INSERT INTO Athlete (firstname, lastname, dateOfBirth)
VALUES (${athlete.first}, ${athlete.last}, ${athlete.birth});
"​""
}

Reference : Groovy in Action

つづく