SQLite merupakan sebuah database yang dapat dipasang pada Aplikasi Android Studio. Buat yang sering menggunakan database MYSQL tidak akan sulit beradaptasi dengan database SQLite sebab keduanya memiliki query yang sama.
Pada postingan ini kita akan membuat CRUD SQLite di Android Studio. Saya tidak akan memberikan kode berupa satu aplikasi full, hanya berupa code CRUD saja jadi sisanya tinggal teman-teman sesuaikan dengan aplikasi sendiri.
Sebagai contoh pembuatan database CODING_RAKITAN dengan tabel artikel. langkah yang harus dilakukan adalah :
1. Buat class baru dengan nama DBHistori kemudian isi dengan kode dibawah :
...
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.provider.BaseColumns;
import java.util.ArrayList;
import java.util.HashMap;
public class DBArtikel extends SQLiteOpenHelper {
public static final int DATABASE_VERSION = 1;
public static final String DATABASE_NAME = "CODING_RAKITAN.db";
public DBArtikel(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
public static class FeedEntry implements BaseColumns {
public static final String TABLE_NAME = "artikel_tersimpan";
public static final String TITLE = "title";
public static final String URL = "url";
public static final String CONTENT = "content";
public static final String PUBLISHED = "published";
public static final String AUTHOR = "author";
public static final String DISPLAY_NAME = "displayName";
public static final String TANGGAL_SIMPAN = "tanggal_simpan";
public static final String GAMBAR_UTAMA = "gambar_utama";
}
@Override
public void onCreate(SQLiteDatabase db) {
String TABEL =
"CREATE TABLE " + DBArtikel.FeedEntry.TABLE_NAME + " (" +
DBArtikel.FeedEntry._ID + " INTEGER PRIMARY KEY," +
DBArtikel.FeedEntry.TITLE + " TEXT," +
DBArtikel.FeedEntry.URL+ " TEXT,"+
DBArtikel.FeedEntry.CONTENT+ " TEXT,"+
DBArtikel.FeedEntry.PUBLISHED+ " TEXT,"+
DBArtikel.FeedEntry.AUTHOR+ " TEXT,"+
DBArtikel.FeedEntry.DISPLAY_NAME+ " TEXT,"+
DBArtikel.FeedEntry.GAMBAR_UTAMA+ " TEXT,"+
DBArtikel.FeedEntry.TANGGAL_SIMPAN+" DATETIME DEFAULT CURRENT_TIMESTAMP)";
db.execSQL(TABEL);
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
String SQL_DELETE_ENTRIES =
"DROP TABLE IF EXISTS " + DBHistori.FeedEntry.TABLE_NAME;
String SQL_DELETE_ENTRIES_2 =
"DROP TABLE IF EXISTS " + DBArtikel.FeedEntry.TABLE_NAME;
db.execSQL(SQL_DELETE_ENTRIES);
db.execSQL(SQL_DELETE_ENTRIES_2);
onCreate(db);
}
public void insert_satu(String title, String url, String content, String publish, String author, String display_name, String gambar){
SQLiteDatabase db = this.getWritableDatabase();
String query = "INSERT INTO "+ FeedEntry.TABLE_NAME+ "("+
FeedEntry.TITLE +","+
FeedEntry.URL+","+
FeedEntry.CONTENT+","+
FeedEntry.PUBLISHED+","+
FeedEntry.AUTHOR+","+
FeedEntry.DISPLAY_NAME+","+
FeedEntry.GAMBAR_UTAMA+")"+
"VALUES ('"+title+"', '"+
url+"', '"+
content+"','"+
publish+"', '"+
author+"', '"+
display_name+"', '"+
gambar+"')";
db.execSQL(query);
db.close();
}
public void insert_dua(ContentValues values){
SQLiteDatabase db = this.getWritableDatabase();
db.insert(FeedEntry.TABLE_NAME, null, values);
}
public ArrayList<HashMap<String, String>> select_all(){
ArrayList<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
String query = "SELECT * FROM "+ FeedEntry.TABLE_NAME+" ORDER BY "+ FeedEntry.TANGGAL_SIMPAN+" desc";
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(query, null);
if (cursor.moveToFirst()){
do {
HashMap<String, String> map = new HashMap<String, String>();
map.put(FeedEntry._ID, cursor.getString(0));
map.put(FeedEntry.TITLE, cursor.getString(1));
map.put(FeedEntry.URL, cursor.getString(2));
map.put(FeedEntry.CONTENT, cursor.getString(3));
map.put(FeedEntry.PUBLISHED, cursor.getString(4));
map.put(FeedEntry.AUTHOR, cursor.getString(5));
map.put(FeedEntry.DISPLAY_NAME, cursor.getString(6));
map.put(FeedEntry.GAMBAR_UTAMA, cursor.getString(7));
map.put(FeedEntry.TANGGAL_SIMPAN, cursor.getString(8));
list.add(map);
}while (cursor.moveToNext());
}
db.close();
return list;
}
public ArrayList<HashMap<String, String>> select_where(String where){
ArrayList<HashMap<String, String>> list = new ArrayList<HashMap<String, String>>();
String query = "SELECT * FROM "+ FeedEntry.TABLE_NAME+" WHERE "+where+" ORDER BY "+ FeedEntry.TANGGAL_SIMPAN+" desc";
SQLiteDatabase db = this.getWritableDatabase();
Cursor cursor = db.rawQuery(query, null);
if (cursor.moveToFirst()){
do {
HashMap<String, String> map = new HashMap<String, String>();
map.put(FeedEntry._ID, cursor.getString(0));
map.put(FeedEntry.TITLE, cursor.getString(1));
map.put(FeedEntry.URL, cursor.getString(2));
map.put(FeedEntry.CONTENT, cursor.getString(3));
map.put(FeedEntry.PUBLISHED, cursor.getString(4));
map.put(FeedEntry.AUTHOR, cursor.getString(5));
map.put(FeedEntry.DISPLAY_NAME, cursor.getString(6));
map.put(FeedEntry.GAMBAR_UTAMA, cursor.getString(7));
map.put(FeedEntry.TANGGAL_SIMPAN, cursor.getString(8));
list.add(map);
}while (cursor.moveToNext());
}
db.close();
return list;
}
public void update(String title, String url, String content, String publish,
String author, String display_name, String gambar, String id) {
SQLiteDatabase database = this.getWritableDatabase();
String query = "UPDATE " + FeedEntry.TABLE_NAME + " SET "
+ FeedEntry.TITLE + "='" + title + "', "
+ FeedEntry.URL + "='" + url + "', "
+ FeedEntry.CONTENT + "='" + content + "', "
+ FeedEntry.PUBLISHED + "='" + publish + "', "
+ FeedEntry.AUTHOR + "='" + author + "', "
+ FeedEntry.DISPLAY_NAME + "='" + author + "', "
+ FeedEntry.GAMBAR_UTAMA + "='" + display_name + "'"
+ " WHERE " + FeedEntry._ID + "=" + "'" + id + "'";
database.execSQL(query);
database.close();
}
public void delete_where(int id) {
SQLiteDatabase database = this.getWritableDatabase();
String updateQuery = "DELETE FROM " + FeedEntry.TABLE_NAME + " WHERE " + FeedEntry._ID + "=" + "'" + id + "'";
database.execSQL(updateQuery);
database.close();
}
public void delete() {
SQLiteDatabase database = this.getWritableDatabase();
String updateQuery = "DELETE FROM " + FeedEntry.TABLE_NAME;
database.execSQL(updateQuery);
database.close();
}
}
2. Definisikan class pada Activity ataupun fragment dengan code :
...
private DBArtikel dbArtikel;
...
dbArtikel = new DBArtikel(context);
...
3. Insert data ke SQLite menggunakan metode 1
dbArtikel.insert_satu("isi_title", "isi content", "isi publish", "isi author", "isi display_name", "isi" gambar");
4. Insert data ke SQLite menggunakan metode 2
ContentValues values = new ContentValues();
values.put("title", "");
values.put("url", "");
values.put("content", "");
values.put("published", "");
values.put("author", "");
values.put("displayName", "");
values.put("gambar_utama", "");
dbArtikel.Insert(values);
Catatan isi tanda "" sesuai dengan data yang ingin disimpan contohnya
values.put("gambar_utama", "data gambarku");.
5. Select data
ArrayList> a = dbArtikel.select_all();
Selain kode di atas bisa juga melakukan select berdasarkan field :
ArrayList> a = dbArtikel.select_where("query");
6. Update data
...
dbArtikel.update("", "", "", "", "", "", "", "");
...
Silahkan isi "" sesuai dengan field pada code
public void update(String title, String url, String content, String publish,
String author, String display_name, String gambar, String id) {
SQLiteDatabase database = this.getWritableDatabase();
String query = "UPDATE " + FeedEntry.TABLE_NAME + " SET "
+ FeedEntry.TITLE + "='" + title + "', "
+ FeedEntry.URL + "='" + url + "', "
+ FeedEntry.CONTENT + "='" + content + "', "
+ FeedEntry.PUBLISHED + "='" + publish + "', "
+ FeedEntry.AUTHOR + "='" + author + "', "
+ FeedEntry.DISPLAY_NAME + "='" + author + "', "
+ FeedEntry.GAMBAR_UTAMA + "='" + display_name + "'"
+ " WHERE " + FeedEntry._ID + "=" + "'" + id + "'";
database.execSQL(query);
database.close();
}
7. Delete data
...
dbArtikel.delete();
...
Untuk menghapus data tertentu gunakan perintah
...
dbArtikel.delete_where(id);
...
Ganti id berdasarkan id data yang ingin dihapus dimana id merupakan tipe data int.
