Menggunakan SQL untuk membaca dan menulis ke database

1. Sebelum memulai

Banyak aplikasi yang Anda gunakan menyimpan data langsung di perangkat. Aplikasi Jam menyimpan alarm berulang, aplikasi Google Maps menyimpan daftar penelusuran terbaru, dan aplikasi Kontak memungkinkan Anda menambahkan, mengedit, dan menghapus informasi kontak.

Persistensi data—menyimpan atau mempertahankan data di perangkat—adalah bagian penting dari pengembangan Android. Data persisten memastikan konten buatan pengguna tidak hilang saat aplikasi ditutup, atau data yang didownload dari internet disimpan sehingga tidak perlu didownload ulang nanti.

SQLite adalah cara umum yang disediakan Android SDK untuk aplikasi Android guna mempertahankan data. SQLite menyediakan database relasional yang memungkinkan Anda merepresentasikan data dengan cara yang sama seperti cara Anda menyusun data dengan class Kotlin. Codelab ini mengajarkan dasar-dasar SQL—Structured Query Language— (Bahasa Kueri Terstruktur) yang, meskipun bukan bahasa pemrograman yang sebenarnya, memberikan cara yang sederhana dan fleksibel untuk membaca dan mengubah database SQLite hanya dengan beberapa baris kode.

Setelah mendapatkan pengetahuan dasar tentang SQL, Anda akan siap menggunakan library Room untuk menambahkan persistensi ke aplikasi Anda nanti di unit ini.

2. Konsep utama database relasional

Apa yang dimaksud database?

Jika sudah terbiasa dengan program spreadsheet seperti Google Spreadsheet, Anda sudah terbiasa dengan analogi dasar untuk database.

Spreadsheet terdiri dari tabel data terpisah, atau spreadsheet individual di workbook yang sama.

Setiap tabel terdiri dari kolom yang menentukan item yang diwakili oleh data dan baris yang mewakili setiap item dengan nilai untuk setiap kolom. Misalnya, Anda dapat menentukan kolom untuk ID, nama, jurusan, dan nilai siswa.

Setiap baris berisi data untuk satu siswa, dengan nilai untuk setiap kolom.

Database relasional berfungsi dengan cara yang sama.

Tabel menentukan pengelompokan data tingkat tinggi yang ingin Anda tampilkan, seperti siswa dan profesor.
Kolom menentukan data yang dimuat oleh setiap baris dalam tabel.
Baris berisi data aktual yang terdiri dari nilai untuk setiap kolom dalam tabel.

Struktur database relasional juga mencerminkan apa yang sudah Anda ketahui tentang class dan objek di Kotlin.

data class Student(
    id: Int,
    name: String,
    major: String,
    gpa: Double
)

Class, seperti tabel, membuat model data yang ingin Anda tampilkan di aplikasi.
Properti, seperti kolom, menentukan bagian data spesifik yang harus dimuat oleh setiap instance class.
Objek, seperti baris, adalah data sebenarnya. Objek berisi nilai untuk setiap properti yang ditentukan di class, seperti baris yang berisi nilai untuk setiap kolom yang ditentukan dalam tabel data.

Sama seperti spreadsheet yang dapat berisi beberapa sheet dan aplikasi yang dapat berisi beberapa class, database dapat berisi beberapa tabel. Database disebut database relasional jika dapat memodelkan hubungan antar-tabel. Misalnya, seorang mahasiswa pascasarjana mungkin memiliki satu profesor sebagai pembimbing doktoral, sedangkan profesor itu menjadi pembimbing doktoral untuk beberapa mahasiswa.

Setiap tabel dalam database relasional berisi ID unik untuk baris, seperti kolom dengan nilai di setiap baris berupa bilangan bulat yang bertambah secara otomatis. ID ini dikenal sebagai kunci utama.

Jika tabel mereferensikan kunci utama tabel lain, kunci tersebut dikenal sebagai kunci asing. Keberadaan kunci asing menandakan adanya hubungan antara beberapa tabel.

Apa itu SQLite?

SQLite adalah database relasional yang biasa digunakan. Secara khusus, SQLite merujuk pada library C ringan untuk pengelolaan database relasional dengan Bahasa Kueri Terstruktur, yang dikenal sebagai SQL dan terkadang diucapkan sebagai "sekuel".

Anda tidak perlu mempelajari C atau bahasa pemrograman baru untuk menggunakan database relasional. SQL hanyalah cara untuk menambahkan dan mengambil data dari database relasional dengan beberapa baris kode.

Merepresentasikan data dengan SQLite

Di Kotlin, Anda sudah terbiasa dengan jenis data seperti Int dan Boolean. Database SQLite juga menggunakan jenis data. Kolom tabel data harus memiliki jenis data tertentu. Tabel berikut memetakan jenis data Kotlin umum ke padanan SQLite.

Jenis data Kotlin	Jenis data SQLite
`Int`	INTEGER
`String`	VARCHAR atau TEXT
`Boolean`	BOOLEAN
`Float`, `Double`	REAL

Tabel dalam database dan kolom di setiap tabel secara kolektif dikenal sebagai skema. Di bagian berikutnya, Anda akan mendownload set data awal dan mempelajari skemanya lebih lanjut.

3. Mendownload set data awal

Database untuk codelab ini ditujukan untuk aplikasi email hipotetis. Codelab ini menggunakan contoh yang sudah dikenal, seperti mengurutkan dan memfilter email, atau menelusuri berdasarkan teks subjek atau pengirim, untuk mendemonstrasikan semua hal canggih yang dapat Anda lakukan dengan SQL. Contoh ini juga memastikan Anda memiliki pengalaman dengan jenis skenario yang mungkin Anda temukan di aplikasi sebelum menggunakan Room di pembelajaran berikutnya.

Download project awal dari cabang compose repositori GitHub SQL Basics di sini.

Menggunakan Database Inspector

Untuk menggunakan Database Inspector, lakukan langkah-langkah berikut:

Jalankan aplikasi SQL Basics di Android Studio. Saat aplikasi diluncurkan, Anda akan melihat layar berikut.

Di Android Studio, klik View > Tool Windows > App Inspection.

Sekarang Anda akan melihat tab baru di bagian bawah berlabel App Inspection dengan tab Database Inspector yang dipilih. Ada dua tab tambahan, tetapi Anda tidak perlu menggunakannya. Mungkin pemuatan akan memerlukan waktu beberapa detik, tetapi Anda akan melihat daftar di sebelah kiri dengan tabel data yang dapat Anda pilih untuk menjalankan kueri.

Klik tombol Open New Query Tab untuk membuka panel guna menjalankan kueri terhadap database.

Tabel email memiliki 7 kolom:

id: Kunci utama.
subject: Baris subjek email.
sender: Alamat email tempat email berasal.
folder: Folder tempat pesan dapat ditemukan, seperti Kotak Masuk atau Spam.
starred: Apakah pengguna memberi bintang pada email atau tidak.
read: Apakah pengguna membaca email atau tidak.
received: Stempel waktu saat email diterima.

4. Membaca data dengan pernyataan SELECT

Pernyataan `SELECT` SQL

Pernyataan SQL—terkadang disebut kueri—digunakan untuk membaca atau memodifikasi database.

Anda membaca data dari database SQLite dengan pernyataan SELECT. Pernyataan SELECT sederhana terdiri dari kata kunci SELECT, kemudian diikuti nama kolom, kata kunci FROM, dan nama tabel. Setiap pernyataan SQL diakhiri dengan titik koma (;).

Pernyataan SELECT juga dapat menampilkan data dari beberapa kolom. Anda harus memisahkan nama kolom dengan koma.

Jika ingin memilih setiap kolom dari tabel, gunakan karakter pengganti (*) sebagai pengganti nama kolom.

Dalam kedua kasus tersebut, pernyataan SELECT sederhana seperti ini akan menampilkan setiap baris dalam tabel. Anda hanya perlu menentukan nama kolom yang ingin ditampilkan.

Membaca data email menggunakan pernyataan `SELECT`

Salah satu hal utama yang perlu dilakukan aplikasi email adalah menampilkan daftar pesan. Dengan database SQL, Anda bisa mendapatkan informasi ini dengan pernyataan SELECT.

Pastikan tabel email dipilih di Database Inspector.

Pertama, coba pilih setiap kolom dari setiap baris dalam tabel email.

SELECT * FROM email;

Klik tombol Run di sudut kanan bawah kotak teks. Perhatikan bahwa seluruh tabel email ditampilkan.

Sekarang, coba pilih subjek untuk setiap baris.

SELECT subject FROM email;

Perhatikan bahwa, sekali lagi, kueri menampilkan setiap baris, tetapi hanya untuk satu kolom tersebut.

Anda juga dapat memilih beberapa kolom. Coba pilih subjek dan pengirim.

SELECT subject, sender FROM email;

Perhatikan bahwa kueri menampilkan setiap baris dalam tabel email, tetapi hanya nilai kolom subjek dan pengirim.

Selamat! Anda baru saja mengeksekusi kueri pertama Anda. Cukup bagus, tetapi anggaplah ini sebagai permulaan; semacam "halo dunia" dari SQL.

Anda dapat membuat pernyataan SELECT jauh lebih spesifik dengan menambahkan klausa untuk menentukan subset data dan bahkan mengubah cara output diformat. Di bagian berikut, Anda akan mempelajari klausa yang umum digunakan dalam pernyataan SELECT dan cara memformat data.

5. Menggunakan pernyataan SELECT dengan fungsi agregat dan nilai yang berbeda

Mengurangi kolom dengan fungsi agregat

Pernyataan SQL tidak terbatas pada baris yang ditampilkan. SQL menawarkan berbagai fungsi yang dapat menjalankan operasi atau penghitungan pada kolom tertentu, seperti menemukan nilai maksimum, atau menghitung jumlah nilai unik yang mungkin untuk kolom tertentu. Fungsi ini disebut fungsi agregat. Alih-alih menampilkan semua data di kolom tertentu, Anda dapat menampilkan satu nilai dari kolom tertentu.

Contoh fungsi agregat SQL meliputi hal berikut:

COUNT(): Menampilkan jumlah total baris yang cocok dengan kueri.
SUM(): Menampilkan jumlah nilai untuk semua baris di kolom yang dipilih.
AVG(): Menampilkan nilai mean—rata-rata—dari semua nilai di kolom yang dipilih.
MIN(): Menampilkan nilai terkecil di kolom yang dipilih.
MAX(): Menampilkan nilai terbesar di kolom yang dipilih.

Sebagai ganti nama kolom, Anda dapat memanggil fungsi agregat dan meneruskan nama kolom sebagai argumen di antara tanda kurung.

Alih-alih menampilkan nilai kolom tersebut untuk setiap baris dalam tabel, satu nilai akan ditampilkan dari pemanggilan fungsi agregat.

Fungsi agregat dapat menjadi cara yang efisien untuk melakukan penghitungan nilai jika Anda tidak perlu membaca semua data dalam database. Misalnya, Anda mungkin ingin menemukan nilai rata-rata di kolom tanpa memuat seluruh database ke dalam Daftar dan melakukannya secara manual.

Mari kita lihat cara kerja beberapa fungsi agregat dengan tabel email:

Sebuah aplikasi mungkin ingin mendapatkan jumlah total email yang diterima. Anda dapat melakukannya menggunakan fungsi COUNT() dan karakter pengganti (*).

SELECT COUNT(*) FROM email;

Kueri menampilkan satu nilai. Anda dapat melakukannya sepenuhnya dengan kueri SQL, tanpa kode Kotlin untuk menghitung baris secara manual.

Untuk mendapatkan waktu pesan terbaru, Anda dapat menggunakan fungsi MAX() di kolom yang diterima karena stempel waktu Unix terbaru adalah angka tertinggi.

SELECT MAX(received) FROM email;

Kueri tersebut menampilkan hasil tunggal, stempel waktu tertinggi—terbaru—di kolom yang diterima.

Filter hasil duplikat dengan `DISTINCT`

Saat memilih kolom, Anda dapat menambahkan kata kunci DISTINCT di awal. Pendekatan ini berguna jika Anda ingin menghapus duplikat dari hasil kueri.

Contohnya, banyak aplikasi email memiliki fitur pelengkapan otomatis untuk alamat. Anda mungkin ingin menyertakan semua alamat asal email dan menampilkannya dalam daftar.

Jalankan kueri berikut untuk menampilkan kolom sender bagi setiap baris.

SELECT sender FROM email;

Perhatikan bahwa hasilnya berisi banyak duplikat. Ini jelas bukan pengalaman yang ideal.

Tambahkan kata kunci DISTINCT sebelum kolom pengirim dan jalankan kembali kueri.

SELECT DISTINCT sender FROM email;

Perhatikan bahwa hasilnya kini jauh lebih kecil dan setiap nilainya unik.

Anda juga dapat mengawali nama kolom dalam fungsi agregat dengan kata kunci DISTINCT.

Misalnya Anda ingin mengetahui jumlah pengirim unik dalam database. Anda dapat menghitung jumlah pengirim unik dengan fungsi agregat COUNT() dan dengan kata kunci DISTINCT di kolom sender.

Buat pernyataan SELECT, dengan meneruskan DISTINCT sender ke fungsi COUNT().

SELECT COUNT(DISTINCT sender) FROM email;

Perhatikan bahwa kueri memberi tahu kita bahwa ada 14 pengirim unik.

6. Memfilter kueri dengan klausa WHERE

Banyak aplikasi email menawarkan fitur untuk memfilter pesan yang ditampilkan berdasarkan kriteria tertentu, seperti data, istilah penelusuran, folder, pengirim, dll. Untuk jenis kasus penggunaan ini, Anda dapat menambahkan klausa WHERE ke kueri SELECT.

Setelah nama tabel, di baris baru, Anda dapat menambahkan kata kunci WHERE, diikuti dengan ekspresi. Saat menulis kueri SQL yang lebih kompleks, biasanya setiap klausa ditempatkan di baris baru agar mudah dibaca.

Kueri ini melakukan pemeriksaan boolean untuk setiap baris yang dipilih. Jika pemeriksaan menampilkan nilai benar, baris akan disertakan dalam hasil kueri. Baris yang kuerinya menampilkan nilai salah tidak akan disertakan dalam hasil.

Misalnya, aplikasi email mungkin memiliki filter untuk spam, sampah, draf, atau filter buatan pengguna. Petunjuk berikut melakukannya dengan klausa WHERE:

Jalankan pernyataan SELECT untuk menampilkan semua kolom (*) dari tabel email, termasuk klausa WHERE untuk memeriksa kondisi folder = 'inbox'. Tidak, itu bukan kesalahan ketik: Anda menggunakan satu tanda sama dengan untuk memeriksa kesetaraan di SQL, dan tanda petik satu, bukan dua, untuk menampilkan nilai string.

SELECT * FROM email
WHERE folder = 'inbox';

Hasilnya hanya menampilkan baris untuk pesan di kotak masuk pengguna.

Operator logika dengan klausa `WHERE`

Klausa WHERE SQL tidak dibatasi pada satu ekspresi. Anda dapat menggunakan kata kunci AND, yang setara dengan operator and Kotlin (&&), untuk hanya menyertakan hasil yang memenuhi kedua kondisi tersebut.

Atau, Anda dapat menggunakan kata kunci OR, setara dengan operator or Kotlin (||), untuk menyertakan baris dalam hasil yang memenuhi salah satu kondisi tersebut.

Agar mudah dibaca, Anda juga dapat menegasikan ekspresi menggunakan kata kunci NOT.

Banyak aplikasi email memungkinkan beberapa filter, misalnya hanya menampilkan email yang belum dibaca.

Cobalah klausa WHERE yang lebih rumit berikut di tabel email:

Selain hanya menampilkan pesan di kotak masuk pengguna, coba batasi hasil pada pesan-pesan yang belum dibaca—dengan nilai salah pada kolom yang dibaca.

SELECT * FROM email
WHERE folder = 'inbox' AND read = false;

Perhatikan bahwa setelah menjalankan kueri, hasilnya hanya berisi email yang belum dibaca di kotak masuk pengguna.

Tampilkan semua email yang ada di folder penting OR yang berbintang (starred = true). Artinya, hasilnya mencakup email di folder berbeda asalkan email diberi bintang.

SELECT * FROM email
WHERE folder = 'important' OR starred = true;

Amati hasilnya.

Telusuri teks menggunakan `LIKE`

Satu hal yang sangat berguna dan dapat Anda lakukan dengan klausa WHERE adalah menelusuri teks di kolom tertentu. Anda mencapai hasil ini saat menentukan nama kolom, diikuti dengan kata kunci LIKE, lalu string penelusuran.

String penelusuran dimulai dengan simbol persen (%), diikuti dengan teks yang akan ditelusuri (istilah Penelusuran), lalu simbol persen (%) lagi.

Jika Anda menelusuri awalan—hasil yang dimulai dengan teks yang ditentukan—hapus simbol persen pertama (%).

Atau, jika Anda menelusuri akhiran, abaikan simbol persen terakhir (%).

Ada banyak kasus penggunaan ketika aplikasi dapat menggunakan penelusuran teks, seperti menelusuri email yang berisi teks tertentu dalam baris subjek atau memperbarui saran pelengkapan otomatis saat pengguna mengetik.

Petunjuk berikut memungkinkan Anda menggunakan penelusuran teks saat membuat kueri tabel email.

Karakter Shakespeare, seperti yang ada dalam database kita, suka membahas orang-orang bodoh. Jalankan kueri berikut untuk mendapatkan jumlah total email dengan kata "fool" di baris subjek.

SELECT COUNT(*) FROM email
WHERE subject LIKE '%fool%';

Amati hasilnya.

Jalankan kueri berikut untuk menampilkan semua kolom dari semua baris dengan subjek yang diakhiri kata "fool".

SELECT * FROM email
WHERE subject LIKE '%fool';

Amati bahwa dua baris ditampilkan.

Jalankan kueri berikut untuk menampilkan nilai berbeda dari kolom sender yang dimulai dengan huruf h.

SELECT DISTINCT sender FROM email
WHERE sender LIKE 'h%';

Perhatikan bahwa kueri menampilkan tiga nilai: helena@example.com , hyppolytus@example.com, dan hermia@example.com.

7. Mengelompokkan, mengurutkan, dan membatasi hasil

Mengelompokkan hasil dengan `GROUP BY`

Anda baru saja melihat cara menggunakan fungsi agregat dan klausa WHERE untuk memfilter dan mengurangi hasil. SQL menawarkan beberapa klausa lain yang dapat membantu Anda memformat hasil kueri. Di antara beberapa klausa ini adalah pengelompokan, pengurutan, dan pembatasan hasil.

Anda dapat menggunakan klausa GROUP BY untuk mengelompokkan hasil sehingga semua baris yang memiliki nilai sama untuk kolom tertentu dikelompokkan bersebelahan dalam hasil. Klausa ini tidak mengubah hasil, tetapi hanya urutan tampilannya.

Untuk menambahkan klausa GROUP BY ke pernyataan SELECT, tambahkan kata kunci GROUP BY, diikuti dengan nama kolom yang ingin Anda kelompokkan hasilnya.

Kasus penggunaan yang umum adalah memasangkan klausa GROUP BY dengan fungsi agregat untuk mempartisi hasil fungsi agregat di berbagai bucket, seperti nilai kolom. Berikut contohnya. Misalnya Anda ingin mendapatkan jumlah email di setiap folder: 'inbox', 'spam', dll. Anda dapat memilih kolom folder dan fungsi agregat COUNT(), serta menentukan kolom folder di klausa GROUP BY.

Jalankan kueri berikut untuk memilih kolom folder, dan hasil dari fungsi agregat COUNT(). Gunakan klausa GROUP BY untuk mengelompokkan hasil menurut nilai di kolom folder.

SELECT folder, COUNT(*) FROM email
GROUP BY folder;

Amati hasilnya. Kueri tersebut menampilkan jumlah total email untuk setiap folder.

Urutkan hasil dengan `ORDER BY`

Anda juga dapat mengubah urutan hasil kueri saat mengurutkannya dengan klausa ORDER BY. Tambahkan kata kunci ORDER BY, diikuti dengan nama kolom, lalu arah pengurutan.

Secara default, arah pengurutan adalah a urutan naik, yang dapat Anda hapus dari klausa ORDER BY. Jika Anda ingin hasil diurutkan dalam urutan menurun, tambahkan DESC setelah nama kolom.

Mungkin Anda mengharapkan aplikasi email menampilkan email terbaru terlebih dahulu. Petunjuk berikut memungkinkan Anda melakukannya dengan klausa ORDER BY.

Tambahkan klausa ORDER BY untuk mengurutkan email yang belum dibaca, berdasarkan kolom received. Karena urutan naik—yang terendah atau terlama dulu—adalah default, Anda harus menggunakan kata kunci DESC.

SELECT * FROM email
ORDER BY received DESC;

Amati hasilnya.

Anda dapat menggunakan klausa ORDER BY dengan klausa WHERE. Misalnya, pengguna ingin menelusuri email lama yang berisi teks fool. Mereka dapat mengurutkan hasil untuk menampilkan email terlama terlebih dahulu, dalam urutan menaik.

Pilih semua email dengan subjek berisi teks "fool" dan urutkan hasilnya d