Android Tutorial CRUD SQLite Database
SQLite merupakan sebuah sistem manajemen basisdata relasional yang bersifat ACID-compliant dan memiliki ukuran pustaka kode yang relatif kecil, ditulis dalam bahasa C. SQLite merupakan proyek yang bersifat public domain yang dikerjakan oleh D. Richard Hipp. Wikipedia
CRUD (Create Read Update Delete) adalah sebuah sebuatan untuk aplikasi dimana pengguna dapat menambah, mengedit dan menghapus data. Aplkasi ini menggunakan floating action button untuk memanggil activity form isian biodata. Semua data akan ditampilkan pada listview. Untuk mengedit atau menghapus data pengguna harus menekan lama salah satu data pada listview untuk menampilkan action edit atau delete.

Buat project baru di Android Studio File ⇒ New Project. Kemudian pilih Basic Activity dan melanjutkannya hingga selesai.
activity_main.xml
Tampilan untuk container floating action button.
<?xml version="1.0" encoding="utf-8"?> <android.support.design.widget.CoordinatorLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:app="http://schemas.android.com/apk/res-auto" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:fitsSystemWindows="true" tools:context="com.dedykuncoro.crudsqlite.MainActivity"> <android.support.design.widget.AppBarLayout android:layout_width="match_parent" android:layout_height="wrap_content" android:theme="@style/AppTheme.AppBarOverlay"> <android.support.v7.widget.Toolbar android:id="@+id/toolbar" android:layout_width="match_parent" android:layout_height="?attr/actionBarSize" android:background="?attr/colorPrimary" app:popupTheme="@style/AppTheme.PopupOverlay" /> </android.support.design.widget.AppBarLayout> <include layout="@layout/content_main" /> <android.support.design.widget.FloatingActionButton android:id="@+id/fab" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_gravity="bottom|end" android:layout_margin="@dimen/fab_margin" app:srcCompat="@android:drawable/ic_input_add" /> </android.support.design.widget.CoordinatorLayout>
content_main.xml
Sebagai tampilan dari data-data pada listview.
<?xml version="1.0" encoding="utf-8"?> <RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:app="http://schemas.android.com/apk/res-auto" xmlns:tools="http://schemas.android.com/tools" android:id="@+id/content_main" android:layout_width="match_parent" android:layout_height="match_parent" app:layout_behavior="@string/appbar_scrolling_view_behavior" tools:context="com.dedykuncoro.crudsqlite.MainActivity" tools:showIn="@layout/activity_main"> <ListView android:id="@+id/list_view" android:layout_width="match_parent" android:layout_height="match_parent" /> </RelativeLayout>
list_row.xml
Sebagai tampilan custom listview yang berisi nama dan alamat.
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:orientation="vertical" android:padding="16dp" android:layout_width="match_parent" android:layout_height="match_parent"> <TextView android:id="@+id/id" android:layout_width="wrap_content" android:layout_height="wrap_content" android:visibility="gone"/> <TextView android:id="@+id/name" android:layout_marginBottom="5dp" android:textStyle="bold" android:textSize="16dp" android:layout_width="wrap_content" android:layout_height="wrap_content" /> <TextView android:id="@+id/address" android:textSize="16dp" android:layout_width="wrap_content" android:layout_height="wrap_content" /> </LinearLayout>
activity_add_edit.xml
Tampilan untuk mengisi biodata.
<?xml version="1.0" encoding="utf-8"?> <LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:id="@+id/activity_add_edit" android:orientation="vertical" android:layout_width="match_parent" android:layout_height="match_parent" android:paddingBottom="@dimen/activity_vertical_margin" android:paddingLeft="@dimen/activity_horizontal_margin" android:paddingRight="@dimen/activity_horizontal_margin" android:paddingTop="@dimen/activity_vertical_margin" tools:context="com.dedykuncoro.crudsqlite.AddEdit"> <EditText android:id="@+id/txt_id" android:layout_width="match_parent" android:layout_height="wrap_content" android:inputType="number" android:visibility="gone"/> <EditText android:id="@+id/txt_name" android:layout_width="match_parent" android:layout_height="wrap_content" android:hint="Input Name"/> <EditText android:id="@+id/txt_address" android:layout_marginTop="10dp" android:layout_width="match_parent" android:layout_height="wrap_content" android:hint="Input Address"/> <LinearLayout android:orientation="horizontal" android:layout_marginTop="10dp" android:layout_width="match_parent" android:layout_height="wrap_content"> <Button android:id="@+id/btn_submit" android:layout_weight="0.5" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="Submit"/> <Button android:id="@+id/btn_cancel" android:layout_weight="0.5" android:layout_width="match_parent" android:layout_height="wrap_content" android:text="Cancel"/> </LinearLayout> </LinearLayout>
Agar project terstruktur dan terorganisir, buat 3 paket dengan nama adapter, helper, dan model. Untuk membuat paket baru , klik kanan pada src=>New=>Peckage dan memberikan nama paket . Contoh : com.dedykuncoro.crudsqlite.

Buat class Adapter.java didalam package adapter dan tambahkan coding seperti dibawah ini. Class ini berfungsi sebagai menampilkan data seperti id, nama, dan alamat kemudian ditampilkan ke dalam listview.
Adapter.java
package com.dedykuncoro.crudsqlite.adapter; import android.app.Activity; import android.content.Context; import android.view.LayoutInflater; import android.view.View; import android.view.ViewGroup; import android.widget.BaseAdapter; import android.widget.TextView; import com.dedykuncoro.crudsqlite.R; import com.dedykuncoro.crudsqlite.model.Data; import java.util.List; /** * Created by Kuncoro on 22/12/2016. */ public class Adapter extends BaseAdapter { private Activity activity; private LayoutInflater inflater; private List<Data> items; public Adapter(Activity activity, List<Data> items) { this.activity = activity; this.items = items; } @Override public int getCount() { return items.size(); } @Override public Object getItem(int location) { return items.get(location); } @Override public long getItemId(int position) { return position; } @Override public View getView(int position, View convertView, ViewGroup parent) { if (inflater == null) inflater = (LayoutInflater) activity .getSystemService(Context.LAYOUT_INFLATER_SERVICE); if (convertView == null) convertView = inflater.inflate(R.layout.list_row, null); TextView id = (TextView) convertView.findViewById(R.id.id); TextView name = (TextView) convertView.findViewById(R.id.name); TextView address = (TextView) convertView.findViewById(R.id.address); Data data = items.get(position); id.setText(data.getId()); name.setText(data.getName()); address.setText(data.getAddress()); return convertView; } }
Buat class Data.java didalam package model dan tambahkan coding seperti dibawah ini. Class ini berfungsi sebagai membuat objek. Objek ini berisi informasi seperti id, nama, dan alamat.
Data.java
package com.dedykuncoro.crudsqlite.model; /** * Created by Kuncoro on 22/12/2016. */ public class Data { private String id, name, address; public Data() { } public Data(String id, String name, String address) { this.id = id; this.name = name; this.address = address; } public String getId() { return id; } public void setId(String id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getAddress() { return address; } public void setAddress(String address) { this.address = address; } }
Buat class DbHelper.java didalam package helper dan tambahkan coding seperti dibawah ini. Class ini berfungsi sebagai pengeloala query SQL CRUD.
DbHelper.java
package com.dedykuncoro.crudsqlite.helper; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.util.Log; import java.util.ArrayList; import java.util.HashMap; /** * Created by Kuncoro on 22/12/2016. */ public class DbHelper extends SQLiteOpenHelper { private static final int DATABASE_VERSION = 2; static final String DATABASE_NAME = "kuncorosqlite.db"; public static final String TABLE_SQLite = "sqlite"; public static final String COLUMN_ID = "id"; public static final String COLUMN_NAME = "name"; public static final String COLUMN_ADDRESS = "address"; public DbHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { final String SQL_CREATE_MOVIE_TABLE = "CREATE TABLE " + TABLE_SQLite + " (" + COLUMN_ID + " INTEGER PRIMARY KEY autoincrement, " + COLUMN_NAME + " TEXT NOT NULL, " + COLUMN_ADDRESS + " TEXT NOT NULL" + " )"; db.execSQL(SQL_CREATE_MOVIE_TABLE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS " + TABLE_SQLite); onCreate(db); } public ArrayList<HashMap<String, String>> getAllData() { ArrayList<HashMap<String, String>> wordList; wordList = new ArrayList<HashMap<String, String>>(); String selectQuery = "SELECT * FROM " + TABLE_SQLite; SQLiteDatabase database = this.getWritableDatabase(); Cursor cursor = database.rawQuery(selectQuery, null); if (cursor.moveToFirst()) { do { HashMap<String, String> map = new HashMap<String, String>(); map.put(COLUMN_ID, cursor.getString(0)); map.put(COLUMN_NAME, cursor.getString(1)); map.put(COLUMN_ADDRESS, cursor.getString(2)); wordList.add(map); } while (cursor.moveToNext()); } Log.e("select sqlite ", "" + wordList); database.close(); return wordList; } public void insert(String name, String address) { SQLiteDatabase database = this.getWritableDatabase(); String queryValues = "INSERT INTO " + TABLE_SQLite + " (name, address) " + "VALUES ('" + name + "', '" + address + "')"; Log.e("insert sqlite ", "" + queryValues); database.execSQL(queryValues); database.close(); } public void update(int id, String name, String address) { SQLiteDatabase database = this.getWritableDatabase(); String updateQuery = "UPDATE " + TABLE_SQLite + " SET " + COLUMN_NAME + "='" + name + "', " + COLUMN_ADDRESS + "='" + address + "'" + " WHERE " + COLUMN_ID + "=" + "'" + id + "'"; Log.e("update sqlite ", updateQuery); database.execSQL(updateQuery); database.close(); } public void delete(int id) { SQLiteDatabase database = this.getWritableDatabase(); String updateQuery = "DELETE FROM " + TABLE_SQLite + " WHERE " + COLUMN_ID + "=" + "'" + id + "'"; Log.e("update sqlite ", updateQuery); database.execSQL(updateQuery); database.close(); } }
Buka class MainActivity.java dan tambahkan coding seperti dibawah ini. Class ini berfungsi menampilkan data biodata dalam listview. Floating Action Button di gunakan untuk memanggil Form diodata yang berisi nama dan alamat. Menekan lama listview akan menampilkan pilihan edit dan delete.
MainActivity.java
package com.dedykuncoro.crudsqlite; import android.app.AlertDialog; import android.content.DialogInterface; import android.content.Intent; import android.os.Bundle; import android.support.design.widget.FloatingActionButton; import android.support.v7.app.AppCompatActivity; import android.support.v7.widget.Toolbar; import android.view.View; import android.view.Menu; import android.view.MenuItem; import android.widget.AdapterView; import android.widget.ListView; import com.dedykuncoro.crudsqlite.adapter.Adapter; import com.dedykuncoro.crudsqlite.helper.DbHelper; import com.dedykuncoro.crudsqlite.model.Data; import java.util.ArrayList; import java.util.HashMap; import java.util.List; public class MainActivity extends AppCompatActivity { ListView listView; AlertDialog.Builder dialog; List<Data> itemList = new ArrayList<Data>(); Adapter adapter; DbHelper SQLite = new DbHelper(this); public static final String TAG_ID = "id"; public static final String TAG_NAME = "name"; public static final String TAG_ADDRESS = "address"; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_main); Toolbar toolbar = (Toolbar) findViewById(R.id.toolbar); setSupportActionBar(toolbar); SQLite = new DbHelper(getApplicationContext()); FloatingActionButton fab = (FloatingActionButton) findViewById(R.id.fab); listView = (ListView) findViewById(R.id.list_view); fab.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View view) { Intent intent = new Intent(MainActivity.this, AddEdit.class); startActivity(intent); } }); adapter = new Adapter(MainActivity.this, itemList); listView.setAdapter(adapter); // long press listview to show edit and delete listView.setOnItemLongClickListener(new AdapterView.OnItemLongClickListener() { @Override public boolean onItemLongClick(final AdapterView<?> parent, View view, final int position, long id) { // TODO Auto-generated method stub final String idx = itemList.get(position).getId(); final String name = itemList.get(position).getName(); final String address = itemList.get(position).getAddress(); final CharSequence[] dialogitem = {"Edit", "Delete"}; dialog = new AlertDialog.Builder(MainActivity.this); dialog.setCancelable(true); dialog.setItems(dialogitem, new DialogInterface.OnClickListener() { @Override public void onClick(DialogInterface dialog, int which) { // TODO Auto-generated method stub switch (which) { case 0: Intent intent = new Intent(MainActivity.this, AddEdit.class); intent.putExtra(TAG_ID, idx); intent.putExtra(TAG_NAME, name); intent.putExtra(TAG_ADDRESS, address); startActivity(intent); break; case 1: SQLite.delete(Integer.parseInt(idx)); itemList.clear(); getAllData(); break; } } }).show(); return false; } }); getAllData(); } private void getAllData() { ArrayList<HashMap<String, String>> row = SQLite.getAllData(); for (int i = 0; i < row.size(); i++) { String id = row.get(i).get(TAG_ID); String poster = row.get(i).get(TAG_NAME); String title = row.get(i).get(TAG_ADDRESS); Data data = new Data(); data.setId(id); data.setName(poster); data.setAddress(title); itemList.add(data); } adapter.notifyDataSetChanged(); } @Override protected void onResume() { super.onResume(); itemList.clear(); getAllData(); } @Override public boolean onCreateOptionsMenu(Menu menu) { // Inflate the menu; this adds items to the action bar if it is present. getMenuInflater().inflate(R.menu.menu_main, menu); return true; } @Override public boolean onOptionsItemSelected(MenuItem item) { // Handle action bar item clicks here. The action bar will // automatically handle clicks on the Home/Up button, so long // as you specify a parent activity in AndroidManifest.xml. int id = item.getItemId(); //noinspection SimplifiableIfStatement if (id == R.id.action_settings) { return true; } return super.onOptionsItemSelected(item); } }
Buat class baru AddEdit.java dan tambahkan coding seperti dibawah ini. Class ini berfungsi menampilkan EditText nama dan alamat. Didalam class ini juga terdapat fungsi untuk insert dan update data ke SQLite database.
AddEdit.java
package com.dedykuncoro.crudsqlite; import android.support.v7.app.AppCompatActivity; import android.os.Bundle; import android.util.Log; import android.view.MenuItem; import android.view.View; import android.widget.Button; import android.widget.EditText; import android.widget.Toast; import com.dedykuncoro.crudsqlite.helper.DbHelper; public class AddEdit extends AppCompatActivity { EditText txt_id, txt_name, txt_address; Button btn_submit, btn_cancel; DbHelper SQLite = new DbHelper(this); String id, name, address; @Override protected void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.activity_add_edit); getSupportActionBar().setDisplayHomeAsUpEnabled(true); txt_id = (EditText) findViewById(R.id.txt_id); txt_name = (EditText) findViewById(R.id.txt_name); txt_address = (EditText) findViewById(R.id.txt_address); btn_submit = (Button) findViewById(R.id.btn_submit); btn_cancel = (Button) findViewById(R.id.btn_cancel); id = getIntent().getStringExtra(MainActivity.TAG_ID); name = getIntent().getStringExtra(MainActivity.TAG_NAME); address = getIntent().getStringExtra(MainActivity.TAG_ADDRESS); if (id == null || id == "") { setTitle("Add Data"); } else { setTitle("Edit Data"); txt_id.setText(id); txt_name.setText(name); txt_address.setText(address); } btn_submit.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { try { if (txt_id.getText().toString().equals("")) { save(); } else { edit(); } } catch (Exception e){ Log.e("Submit", e.toString()); } } }); btn_cancel.setOnClickListener(new View.OnClickListener() { @Override public void onClick(View v) { blank(); finish(); } }); } @Override public void onBackPressed() { finish(); } @Override public boolean onOptionsItemSelected(MenuItem item) { switch (item.getItemId()) { case android.R.id.home: blank(); this.finish(); return true; default: return super.onOptionsItemSelected(item); } } // Make blank all Edit Text private void blank() { txt_name.requestFocus(); txt_id.setText(null); txt_name.setText(null); txt_address.setText(null); } // Save data to SQLite database private void save() { if (String.valueOf(txt_name.getText()).equals(null) || String.valueOf(txt_name.getText()).equals("") || String.valueOf(txt_address.getText()).equals(null) || String.valueOf(txt_address.getText()).equals("")) { Toast.makeText(getApplicationContext(), "Please input name or address ...", Toast.LENGTH_SHORT).show(); } else { SQLite.insert(txt_name.getText().toString().trim(), txt_address.getText().toString().trim()); blank(); finish(); } } // Update data in SQLite database private void edit() { if (String.valueOf(txt_name.getText()).equals(null) || String.valueOf(txt_name.getText()).equals("") || String.valueOf(txt_address.getText()).equals(null) || String.valueOf(txt_address.getText()).equals("")) { Toast.makeText(getApplicationContext(), "Please input name or address ...", Toast.LENGTH_SHORT).show(); } else { SQLite.update(Integer.parseInt(txt_id.getText().toString().trim()), txt_name.getText().toString().trim(), txt_address.getText().toString().trim()); blank(); finish(); } } }
Run Aplikasinya.