KAL
Menu

groovy.sql

Kelas Sql, dataset, dan pengelolaan transaksi untuk akses basis data.

Daftar isi

Paket groovy.sql menyediakan kelas Sql, yaitu pembungkus (wrapper) tipis di atas JDBC yang menyingkirkan pengelolaan sumber daya manual. Setelah membaca topik ini, pembaca dapat membuka koneksi ke basis data, menjalankan query dengan parameter terikat, membaca baris hasilnya, menyisipkan dan memperbarui data, serta membungkus beberapa pernyataan ke dalam satu transaksi. Seluruh contoh memakai basis data H2 yang diunduh lewat anotasi @Grab, sehingga setiap skrip dapat disalin dan langsung dijalankan.

Kelas Sql sebagai pembungkus JDBC#

Dengan JDBC murni, satu query menuntut langkah berulang, yaitu membuka Connection, membuat Statement, menelusuri ResultSet, lalu menutup semuanya di blok finally. Kelas Sql meringkas langkah-langkah itu. Objek Sql memegang satu koneksi, membuka dan menutup Statement beserta ResultSet secara otomatis pada setiap operasi, dan melepas koneksi yang dipegangnya hanya ketika close() dipanggil.

Koneksi dibuka melalui Sql.newInstance(url, user, password, driver), bentuk yang dipakai skrip berikut. Alternatifnya adalah Sql.withInstance(url, user, password, driver) { sql -> ... } yang menutup koneksi secara otomatis begitu closure selesai, termasuk ketika exception keluar dari closure tersebut. Bentuk kedua cocok untuk skrip pendek karena menghilangkan pemanggilan close() sama sekali.

Driver H2 dipasang lewat @Grab dan @GrabConfig sebagaimana dijelaskan pada topik Grape dan @Grab. URL jdbc:h2:mem:rekap dengan parameter DB_CLOSE_DELAY=-1 membuat basis data hidup di memori selama JVM berjalan. Setiap eksekusi skrip memulai JVM baru, sehingga skema dan datanya dibangun ulang dari nol dan keluarannya selalu sama.

groovy rekap-nilai.groovy 
// rekap-nilai.groovy: koneksi H2 in-memory dan query pertama dengan kelas Sql
@Grab('com.h2database:h2:2.2.224')
@GrabConfig(systemClassLoader=true)
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:rekap;DB_CLOSE_DELAY=-1', 'sa', '', 'org.h2.Driver')

sql.execute 'CREATE TABLE nilai (nama VARCHAR(20), skor INT)'
sql.executeInsert "INSERT INTO nilai (nama, skor) VALUES ('Ali', 85), ('Budi', 78), ('Citra', 91)"

sql.eachRow('SELECT nama, skor FROM nilai ORDER BY skor DESC') { baris ->
    println "${baris.nama} : ${baris.skor}"
}
println "Jumlah peserta: ${sql.rows('SELECT nama FROM nilai').size()}"

sql.close()
text Keluaran 
Citra : 91
Ali : 85
Budi : 78
Jumlah peserta: 3

Skrip membuat tabel nilai lewat execute, mengisi tiga baris lewat executeInsert, lalu menelusuri hasilnya dengan eachRow. Blok closure menerima satu baris sebagai parameter bernama baris, dan properti baris.nama langsung mengambil nilai kolom nama tanpa konversi manual. Baris terakhir menghitung jumlah peserta melalui rows, metode yang dibahas lebih rinci pada bagian berikutnya.

Membaca hasil query#

Tiga metode menutupi kebutuhan pembacaan data. Metode rows menjalankan query dan mengembalikan seluruh hasil sebagai List berisi objek GroovyRowResult, sehingga daftar itu dapat disimpan, disaring, dan diolah dengan metode koleksi Groovy. eachRow menelusuri baris satu per satu dan langsung mengoper setiap baris ke closure, sehingga cocok untuk hasil yang besar karena tidak menampung semuanya di memori terlebih dahulu. firstRow mengembalikan satu baris pertama saja, atau null bila hasilnya kosong.

Skrip berikut memakai ketiganya pada tabel nilai yang sama dengan skrip pertama. Perhatikan baris firstRow : [NAMA:Citra, SKOR:91]. H2 menyimpan nama kolom yang tidak diberi tanda kutip dalam huruf besar, dan pencetakan objek mentah menampilkannya apa adanya. Akses properti seperti teratas.nama tidak membedakan huruf besar dan kecil, sehingga nilai kolomnya tetap terbaca.

groovy Contoh: rows dan firstRow 
// membaca baris dengan rows dan firstRow
@Grab('com.h2database:h2:2.2.224')
@GrabConfig(systemClassLoader=true)
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:rekap;DB_CLOSE_DELAY=-1', 'sa', '', 'org.h2.Driver')
sql.execute 'CREATE TABLE nilai (nama VARCHAR(20), skor INT)'
sql.executeInsert "INSERT INTO nilai (nama, skor) VALUES ('Ali', 85), ('Budi', 78), ('Citra', 91)"

def semua = sql.rows('SELECT nama, skor FROM nilai')
println "Jumlah baris: ${semua.size()}"
println "Nama pertama : ${semua[0].nama}"
println "Lulus        : ${semua.findAll { it.skor > 84 }.collect { it.nama }}"

def teratas = sql.firstRow('SELECT nama, skor FROM nilai ORDER BY skor DESC')
println "firstRow     : $teratas"
println "Juara        : ${teratas.nama} dengan skor ${teratas.skor}"

sql.close()
text Keluaran 
Jumlah baris: 3
Nama pertama : Ali
Lulus        : [Ali, Citra]
firstRow     : [NAMA:Citra, SKOR:91]
Juara        : Citra dengan skor 91

rows mengembalikan List, sehingga metode koleksi seperti findAll dan collect dapat langsung dipasang di atasnya. Keluaran memperlihatkan dua baris yang lolos penyaringan dan nama yang diambil dari kolomnya. Satu hal perlu dicatat, yaitu urutan baris tanpa ORDER BY tidak dijamin oleh SQL, sehingga perhitungan yang bergantung pada peringkat tetap memakai ORDER BY seperti pada query firstRow.

Parameter terikat#

Nilai yang berasal dari luar skrip tidak boleh direkatkan ke dalam teks SQL dengan penggabungan string. Cara yang benar adalah parameter terikat (bound parameter), yaitu menuliskan tanda tanya ? sebagai tempat nilai pada teks SQL, lalu mengirim daftar nilainya sebagai argumen kedua. Driver kemudian mengirim teks SQL dan nilai-nilainya secara terpisah, sehingga nilai dari luar tidak pernah dieksekusi sebagai bagian dari perintah. Mekanisme inilah yang melindungi aplikasi dari serangan SQL injection.

Skrip berikut mengikat dua jenis nilai, yaitu angka ambang pada penyaringan dan nama yang dicari pada pencarian satu baris. Bentuk GString juga didukung, misalnya sql.rows("SELECT ... WHERE skor > $ambang"), karena Sql mengubah setiap ekspresi $ menjadi tanda tanya saat eksekusi.

groovy Contoh: parameter terikat dengan tanda tanya 
// ambang dan nama dicari dikirim sebagai parameter terikat
@Grab('com.h2database:h2:2.2.224')
@GrabConfig(systemClassLoader=true)
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:rekap;DB_CLOSE_DELAY=-1', 'sa', '', 'org.h2.Driver')
sql.execute 'CREATE TABLE nilai (nama VARCHAR(20), skor INT)'
sql.executeInsert "INSERT INTO nilai (nama, skor) VALUES ('Ali', 85), ('Budi', 78), ('Citra', 91)"

def ambang = 80
def lulus = sql.rows('SELECT nama, skor FROM nilai WHERE skor > ? ORDER BY skor DESC', [ambang])
lulus.each { println "Lulus : ${it.nama} (${it.skor})" }

def dicari = 'Citra'
def baris = sql.firstRow('SELECT skor FROM nilai WHERE nama = ?', [dicari])
println "Skor ${dicari}: ${baris.skor}"

sql.close()
text Keluaran 
Lulus : Citra (91)
Lulus : Ali (85)
Skor Citra: 91

Keluaran menunjukkan penyaringan menghasilkan dua baris dan pencarian nama mengembalikan skor 91. Apabila query memuat lebih dari satu tanda tanya, urutan nilai pada daftar mengikuti urutan tanda tanya pada teks SQL.

Menyisipkan, memperbarui, dan memproses massal#

Tiga metode menangani penulisan data. Metode execute menjalankan perintah apa pun dan biasanya dipakai untuk DDL seperti CREATE TABLE. executeInsert menyisipkan baris dan mengembalikan kunci yang dibangkitkan otomatis (generated key), sedangkan executeUpdate mengembalikan jumlah baris yang terdampak. Agar kunci terisi, tabelnya perlu kolom yang nilainya dibangkitkan oleh basis data, seperti IDENTITY pada H2.

Hasil executeInsert berupa list dalam list, misalnya [[1]] untuk satu baris, karena kunci dikumpulkan per baris dan satu penyisipan boleh memuat beberapa baris sekaligus. Penyisipan ratusan atau ribuan baris satu per satu boros waktu karena setiap panggilan menimbulkan satu perjalanan bolak-balik ke basis data. Metode withBatch mengelompokkan penyisipan ke dalam batch yang dikirim bersamaan lewat satu PreparedStatement.

groovy Contoh: executeInsert, executeUpdate, dan withBatch 
// menulis data: kunci sisip, jumlah baris, dan pemrosesan batch
@Grab('com.h2database:h2:2.2.224')
@GrabConfig(systemClassLoader=true)
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:rekap;DB_CLOSE_DELAY=-1', 'sa', '', 'org.h2.Driver')
sql.execute 'CREATE TABLE sensor (id IDENTITY, suhu DECIMAL(4,1), jam VARCHAR(8))'

def kunci = sql.executeInsert 'INSERT INTO sensor (suhu, jam) VALUES (?, ?)', [27.5, '07:00']
println "Kunci baris baru : $kunci"

int ubah = sql.executeUpdate 'UPDATE sensor SET suhu = ? WHERE id = ?', [28.0, 1]
println "Baris terubah    : $ubah"

def hasil = sql.withBatch(2, 'INSERT INTO sensor (suhu, jam) VALUES (?, ?)') { ps ->
    ps.addBatch([28.4, '08:00'])
    ps.addBatch([28.1, '09:00'])
    ps.addBatch([29.0, '10:00'])
}
println "Hasil batch      : $hasil"
println "Total baris      : ${sql.rows('SELECT id FROM sensor').size()}"

sql.close()
text Keluaran 
Kunci baris baru : [[1]]
Baris terubah    : 1
Hasil batch      : [1, 1, 1]
Total baris      : 4

Keluaran memperlihatkan kunci [[1]] dari penyisipan pertama, satu baris berubah dari executeUpdate, dan daftar [1, 1, 1] dari withBatch sebagai jumlah baris yang terdampak per pernyataan. Argumen pertama withBatch, yaitu angka 2 pada contoh, menyatakan ukuran kelompok yang dikirim sekaligus. Total empat baris sensor mengonfirmasi bahwa seluruh penyisipan berhasil.

DataSet#

Kelas DataSet membungkus satu tabel menjadi objek yang dapat dioperasikan lewat nama kolomnya. Objeknya diperoleh dari sql.dataSet('nama_tabel'), penyisipan dilakukan lewat metode add yang menerima map kolom dan nilai, sementara pembacaan memakai each dan rows. DataSet menghimpun operasi tingkat tabel yang sederhana, sedangkan query yang melibatkan beberapa tabel tetap lebih tepat ditulis dengan metode Sql biasa. Properti sql pada objek DataSet memperlihatkan perintah SQL yang dibangkitkannya.

groovy Contoh: DataSet untuk tabel piket 
// DataSet: sisip lewat add dan SQL yang dibangkitkan otomatis
@Grab('com.h2database:h2:2.2.224')
@GrabConfig(systemClassLoader=true)
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:rekap;DB_CLOSE_DELAY=-1', 'sa', '', 'org.h2.Driver')
sql.execute 'CREATE TABLE piket (nama VARCHAR(20), hari VARCHAR(10))'

def piket = sql.dataSet('piket')
piket.add(nama: 'Ali', hari: 'Senin')
piket.add(nama: 'Budi', hari: 'Selasa')
println "Jumlah    : ${piket.rows().size()}"
println "SQL dasar : ${piket.sql}"
piket.each { println "Jaga      : ${it.nama} (${it.hari})" }

sql.close()
text Keluaran 
Jumlah    : 2
SQL dasar : select * from piket
Jaga      : Ali (Senin)
Jaga      : Budi (Selasa)

Dua baris data masuk lewat add tanpa satu pun teks INSERT yang ditulis manual, dan each menelusuri seluruh barisnya. Properti sql menampilkan select * from piket, yaitu bentuk query yang dibangkitkan DataSet dari nama tabelnya.

Metode findAll pada DataSet memiliki perilaku yang berbeda dari findAll pada koleksi. Closure yang dikirim tidak dijalankan di sisi Groovy, melainkan diterjemahkan menjadi klausa WHERE, sehingga penyaringan tetap berlangsung di basis data. Saat skrip dijalankan dari direktori tempat berkasnya berada, piket.findAll { it.hari == 'Senin' } menghasilkan select * from piket where hari = ? pada properti sql-nya, lengkap dengan tanda tanya pengikat parameter. Penerjemahan ini membaca ulang berkas sumber skrip dari direktori kerja, sehingga menjalankan salinan skrip dari folder lain gagal dengan GroovyRuntimeException. Karena keberhasilan eksekusinya bergantung pada letak berkas skrip, contoh findAll ditandai tidak-dijalankan.

groovy Contoh: findAll menjadi klausa WHERE 
// tidak-dijalankan
// findAll menerjemahkan closure menjadi klausa WHERE
@Grab('com.h2database:h2:2.2.224')
@GrabConfig(systemClassLoader=true)
import groovy.sql.Sql

def sql = Sql.newInstance('jdbc:h2:mem:rekap;DB_CLOSE_DELAY=-1', 'sa', '', 'org.h2.Driver')
sql.execute 'CREATE TABLE piket (nama VARCHAR(20), hari VARCHAR(10))'
sql.executeInsert "INSERT INTO piket (nama, hari) VALUES ('Ali', 'Senin'), ('Budi', 'Selasa')"

def piket = sql.dataSet('piket')
def senin = piket.findAll { it.hari == 'Senin' }
println "SQL: ${senin.sql}"
senin.each { println "Jaga: ${it.nama} (${it.hari})" }

sql.close()

Transaksi dengan withTransaction#

Transaksi (transaction) menjamin sekelompok pernyataan diterapkan seluruhnya atau tidak sama sekali. Metode withTransaction membuka transaksi, menjalankan closure di dalamnya, lalu mengakhiri dengan commit ketika closure selesai tanpa exception. Ketika exception keluar dari closure, seluruh perubahan di dalamnya dibatalkan lewat rollback.

Sebelum melihat demonya, ada satu perilaku yang perlu diantisipasi. Ketika rollback terjadi, Sql mencatat peristiwanya sebagai warning melalui java.util.logging ke aliran stderr. Skrip berikut menyetel level logger tersebut ke OFF supaya keluarannya bersih, dan dalam praktiknya baris itu boleh dilepas bila catatan warning justru diinginkan.

groovy Contoh: rollback saat transaksi gagal 
// sisip di dalam transaksi lalu gagal sengaja untuk memicu rollback
@Grab('com.h2database:h2:2.2.224')
@GrabConfig(systemClassLoader=true)
import groovy.sql.Sql
import java.util.logging.Level
import java.util.logging.Logger

// Sql mencatat rollback sebagai warning java.util.logging, sehingga levelnya dimatikan
Logger.getLogger('groovy.sql.Sql').level = Level.OFF

Sql.withInstance('jdbc:h2:mem:rekap;DB_CLOSE_DELAY=-1', 'sa', '', 'org.h2.Driver') { sql ->
    sql.execute 'CREATE TABLE piket (nama VARCHAR(20), hari VARCHAR(10))'
    sql.executeInsert "INSERT INTO piket (nama, hari) VALUES ('Ali', 'Senin'), ('Budi', 'Selasa')"

    println "Sebelum: ${sql.rows('SELECT nama FROM piket').size()} baris"
    try {
        sql.withTransaction {
            sql.executeInsert "INSERT INTO piket (nama, hari) VALUES ('Citra', 'Rabu')"
            throw new RuntimeException('validasi gagal')
        }
    } catch (RuntimeException e) {
        println "Tertangkap: ${e.message}"
    }
    println "Sesudah: ${sql.rows('SELECT nama FROM piket').size()} baris"
}
text Keluaran 
Sebelum: 2 baris
Tertangkap: validasi gagal
Sesudah: 2 baris

Skrip menyisipkan baris Citra di dalam transaksi, lalu melempar exception secara sengaja untuk mensimulasikan kegagalan di tengah proses. Angka sebelum dan sesudah sama-sama dua, yang berarti penyisipan Citra ikut dibatalkan oleh rollback. Pola ini penting ketika beberapa penyisipan harus berlangsung lengkap bersama, misalnya baris jadwal piket beserta rinciannya.

Kesalahan umum#

Dua error yang paling sering muncul pada pemakaian sehari-hari berupa SQLException, dan keduanya mudah dikenali dari pesannya. Skrip demo menangkap exception tersebut lalu mencetak bagian pertama pesannya dengan split(';') agar inti masalahnya terlihat tanpa jejak tumpukan (stack trace). Logger groovy.sql.Sql dimatikan dengan alasan yang sama seperti pada demo transaksi.

Error pertama adalah CREATE TABLE yang dijalankan dua kali pada basis data yang masih hidup, misalnya ketika skrip dieksekusi dua kali dalam sesi JVM yang sama atau ketika URL menunjuk basis data berbasis berkas.

groovy Contoh: tabel yang sudah ada 
// CREATE TABLE yang diulang pada basis data yang masih hidup
@Grab('com.h2database:h2:2.2.224')
@GrabConfig(systemClassLoader=true)
import groovy.sql.Sql
import java.util.logging.Level
import java.util.logging.Logger

Logger.getLogger('groovy.sql.Sql').level = Level.OFF

def sql = Sql.newInstance('jdbc:h2:mem:rekap;DB_CLOSE_DELAY=-1', 'sa', '', 'org.h2.Driver')
sql.execute 'CREATE TABLE nilai (nama VARCHAR(20), skor INT)'
try {
    sql.execute 'CREATE TABLE nilai (nama VARCHAR(20), skor INT)'
} catch (java.sql.SQLException e) {
    println "SQLException: ${e.message.split(';')[0]}"
}
sql.close()
text Keluaran 
SQLException: Table "NILAI" already exists

Perbaikannya adalah menuliskan CREATE TABLE IF NOT EXISTS sehingga pernyataan ulang diabaikan, atau memastikan DDL hanya dijalankan sekali per sesi.

Error kedua adalah kesalahan sintaks SQL. Kata DARI pada contoh berikut bukan kata kunci SQL yang sah, dan H2 menandai posisi kesalahannya dengan tanda [*] tepat sebelum token yang bermasalah.

groovy Contoh: sintaks SQL yang salah 
// perintah SQL dengan kata kunci yang salah
@Grab('com.h2database:h2:2.2.224')
@GrabConfig(systemClassLoader=true)
import groovy.sql.Sql
import java.util.logging.Level
import java.util.logging.Logger

Logger.getLogger('groovy.sql.Sql').level = Level.OFF

def sql = Sql.newInstance('jdbc:h2:mem:rekap;DB_CLOSE_DELAY=-1', 'sa', '', 'org.h2.Driver')
sql.execute 'CREATE TABLE nilai (nama VARCHAR(20), skor INT)'
sql.executeInsert "INSERT INTO nilai (nama, skor) VALUES ('Ali', 85)"
try {
    sql.rows('SELECT nama DARI nilai')
} catch (java.sql.SQLException e) {
    println "SQLException: ${e.message.split(';')[0]}"
}
sql.close()
text Keluaran 
SQLException: Syntax error in SQL statement "SELECT nama DARI [*]nilai"

Error semacam ini tidak dapat dicegah oleh kompilator Groovy karena isi string SQL baru diperiksa basis data saat dieksekusi. Kebiasaan yang membantu adalah menguji query di konsol basis data terlebih dahulu, atau menuliskan nilai sebagai parameter agar teks perintahnya tetap bersih.

Ringkasan#

  • Kelas Sql membungkus JDBC dan menangani Statement beserta ResultSet secara otomatis, dengan koneksi dibuka lewat Sql.newInstance atau Sql.withInstance yang menutup sendiri.
  • rows mengembalikan List baris, eachRow menelusuri baris satu per satu, dan firstRow mengambil satu baris, sementara tanda tanya dengan daftar parameter melindungi query dari SQL injection.
  • executeInsert mengembalikan kunci yang dibangkitkan, executeUpdate mengembalikan jumlah baris, execute menjalankan DDL, dan withBatch mengelompokkan penyisipan massal.
  • DataSet memetakan satu tabel menjadi objek dengan add, dan findAll-nya diterjemahkan menjadi klausa WHERE, sedangkan withTransaction melakukan rollback bila exception keluar dari closure-nya.

Referensi#