Lanjutan CRUD Sqlite

By | Juli 15, 2020
Crud Sqlite
Crud Sqlite

Lanjutan untuk database SQliteopenhelper , sebelumnya sudah kita bahas basic dari sqliteopenhelper disini . Kali ini kita akan bahas bagaimana untuk membuat Create Read Update Delete (CRUD) untuk database (SQLite) lokal android

Mari kita buat dulu untuk DatabaseHelper , Untuk bagaimana cara buat file nya saya rasa tidak perlu saya jelaskan disini lagi

package com.example.crudapps;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "student.db";
    public static final String TABLE_NAME = "student_table";
    public static final String COL_1 = "id";
    public static final String COL_2 = "name";
    public static final String COL_3 = "address";
    public static final String COL_4 = "phone";
    public static final String COL_5 = "email";
    public DatabaseHelper(@Nullable Context context) {
        super(context, DATABASE_NAME, null, 1);

    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    db.execSQL("create table "+TABLE_NAME+"("+COL_1+" integer primary key autoincrement, "+COL_2+" text,"+COL_3+" text,"+COL_4+" text,"+COL_5+" text)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("drop table if exists "+TABLE_NAME);
        onCreate(db);
    }

    public boolean insertData(String name,String address,String phone,String email)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues content = new ContentValues();
        content.put(COL_2,name);
        content.put(COL_3,address);
        content.put(COL_4,phone);
        content.put(COL_5,email);
        long result  = db.insert(TABLE_NAME,null,content);
        if(result==-1)
            return false;
        else
            return true;
    }

    public Cursor getAllData()
    {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor res = db.rawQuery(" select * from "+TABLE_NAME,null);
        return res;
    }

    public boolean updateData(String id,String name,String address,String phone,String email)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues content = new ContentValues();
        content.put(COL_1,id);
        content.put(COL_2,name);
        content.put(COL_3,address);
        content.put(COL_3,phone);
        content.put(COL_4,email);
        db.update(TABLE_NAME,content,"id = ?",new String[] {id} );
        return true;
    }

    public int deleteData(String id){

        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete(TABLE_NAME,"id = ?",new String[]{id});
    }
}

Untuk database saya beri nama student.db , dan nama tabel nya student_table .


Insert Data

Untuk membuat insert data di code tersebut sudah dijelaskan

public boolean insertData(String name,String address,String phone,String email)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues content = new ContentValues();
        content.put(COL_2,name);
        content.put(COL_3,address);
        content.put(COL_4,phone);
        content.put(COL_5,email);
        long result  = db.insert(TABLE_NAME,null,content);
        if(result==-1)
            return false;
        else
            return true;
    }

Insert data sudah dijelaskan ada kata getWriteableDatabase.


Read Data

Selanjutnya untuk ambil semua data di database

public Cursor getAllData()
    {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor res = db.rawQuery(" select * from "+TABLE_NAME,null);
        return res;
    }

getAllData untuk ambil data didatabase untuk kategori data mana yang mau diambil bisa di utak atik trigger database contoh : (“select * from” +TABLE_NAME,null);


Update Data

Lalu untuk update data yang sudah di dalam database

public boolean updateData(String id,String name,String address,String phone,String email)
    {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues content = new ContentValues();
        content.put(COL_1,id);
        content.put(COL_2,name);
        content.put(COL_3,address);
        content.put(COL_3,phone);
        content.put(COL_4,email);
        db.update(TABLE_NAME,content,"id = ?",new String[] {id} );
        return true;
    }

updateData untuk memperbarui data yang sudah ada di dalam database


Delete Data

Untuk data yang ingi dihapus bisa gunakan deleteData berikut contohnya

public int deleteData(String id){

        SQLiteDatabase db = this.getWritableDatabase();
        return db.delete(TABLE_NAME,"id = ?",new String[]{id});
    }

delete data bisa gunakan id , jadi agar tahu data mana yang ingin di hapus

Lalu database sudah selesai di buat , waktunya eksekusi tiap halaman , mana yang digunakan untuk Create , Read , Update dan Delete data


Delete Activity

Buat DeleteActivity di android anda

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout 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"
    tools:context=".InsertActivity">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:orientation="vertical"
        android:paddingLeft="10dp"
        android:paddingTop="100dp"
        android:paddingRight="10dp">

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:paddingBottom="10dp">

            <TextView
                android:id="@+id/textView"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:text="ID : " />

            <EditText
                android:id="@+id/editText"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:ems="10"
                android:inputType="textPersonName" />

        </LinearLayout>

        <Button
            android:id="@+id/button5"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="Delete" />
    </LinearLayout>
</androidx.constraintlayout.widget.ConstraintLayout>

Dan Java nya

package com.example.crudapps;

import androidx.appcompat.app.AppCompatActivity;

import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class DeleteActivity extends AppCompatActivity {

    DatabaseHelper myDb;
    Button b6;
    EditText e5id;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_delete);

        myDb = new DatabaseHelper(this);

        b6 = findViewById(R.id.button5);
        e5id = findViewById(R.id.editText);

        b6.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                int deletedrows = myDb.deleteData(e5id.getText().toString());
                if(deletedrows>0)
                    Toast.makeText(getApplicationContext(),"Deleted",Toast.LENGTH_LONG).show();
                else
                    Toast.makeText(getApplicationContext(),"Record Not Found",Toast.LENGTH_LONG).show();
            }
        });
    }
}

Insert Data Activity

Selanjutanya Buat Activity Insert Activity

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout 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"
    tools:context=".InsertActivity">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:orientation="vertical"
        android:paddingLeft="10dp"
        android:paddingTop="100dp"
        android:paddingRight="10dp">

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:paddingBottom="10dp">

            <TextView
                android:id="@+id/textView"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:text="Name" />

            <EditText
                android:id="@+id/editText"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:ems="10"
                android:inputType="textPersonName" />

        </LinearLayout>

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:paddingBottom="10dp">

            <TextView
                android:id="@+id/textView2"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:text="Address" />

            <EditText
                android:id="@+id/editText2"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:ems="10"
                android:inputType="textPersonName" />

        </LinearLayout>

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:paddingBottom="10dp">

            <TextView
                android:id="@+id/textView3"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:text="Phone" />

            <EditText
                android:id="@+id/editText3"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:ems="10"
                android:inputType="textPersonName" />
        </LinearLayout>

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:paddingBottom="10dp">

            <TextView
                android:id="@+id/textView4"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:text="Email" />

            <EditText
                android:id="@+id/editText4"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:ems="10"
                android:inputType="textPersonName" />
        </LinearLayout>

        <Button
            android:id="@+id/button5"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="Insert" />
    </LinearLayout>
</androidx.constraintlayout.widget.ConstraintLayout>

Dan Javanya

package com.example.crudapps;

import androidx.appcompat.app.AppCompatActivity;

import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class InsertActivity extends AppCompatActivity {

    DatabaseHelper myDb;
    Button b5;
    EditText e1name,e2address,e3phone,e4email;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_insert);
        myDb = new DatabaseHelper(this);
        b5 = findViewById(R.id.button5);

        e1name = findViewById(R.id.editText);
        e2address = findViewById(R.id.editText2);
        e3phone = findViewById(R.id.editText3);
        e4email = findViewById(R.id.editText4);

        b5.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                boolean result = myDb.insertData(e1name.getText().toString(),e2address.getText().toString(),e3phone.getText().toString(),e4email.getText().toString());
                if(result==false)
                    Toast.makeText(getApplicationContext(),"Could not insert",Toast.LENGTH_LONG).show();
                else
                    Toast.makeText(getApplicationContext(),"Success",Toast.LENGTH_LONG).show();
            }
        });
    }
}

Update Activity

Selanjutnya kita akan membuat update activity yang di mana bisa di gunaka update data di database

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout 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"
    tools:context=".InsertActivity">

    <LinearLayout
        android:layout_width="match_parent"
        android:layout_height="match_parent"
        android:orientation="vertical"
        android:paddingLeft="10dp"
        android:paddingTop="100dp"
        android:paddingRight="10dp">



        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal">
            <TextView
                android:id="@+id/textView5"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:text="ID" />

            <EditText
                android:id="@+id/editText5"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:ems="10"
                android:inputType="textPersonName"
                 />
        </LinearLayout>

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:paddingBottom="10dp">

            <TextView
                android:id="@+id/textView"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:text="Name" />

            <EditText
                android:id="@+id/editText"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:ems="10"
                android:inputType="textPersonName" />

        </LinearLayout>

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:paddingBottom="10dp">

            <TextView
                android:id="@+id/textView2"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:text="Address" />

            <EditText
                android:id="@+id/editText2"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:ems="10"
                android:inputType="textPersonName" />

        </LinearLayout>

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:paddingBottom="10dp">

            <TextView
                android:id="@+id/textView3"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:text="Phone" />

            <EditText
                android:id="@+id/editText3"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:ems="10"
                android:inputType="textPersonName" />
        </LinearLayout>

        <LinearLayout
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:orientation="horizontal"
            android:paddingBottom="10dp">

            <TextView
                android:id="@+id/textView4"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:text="Email" />

            <EditText
                android:id="@+id/editText4"
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:layout_weight="1"
                android:ems="10"
                android:inputType="textPersonName" />
        </LinearLayout>

        <Button
            android:id="@+id/button5"
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:text="Update" />
    </LinearLayout>
</androidx.constraintlayout.widget.ConstraintLayout>

Dan Javanya

package com.example.crudapps;

import androidx.appcompat.app.AppCompatActivity;

import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class UpdateActivity extends AppCompatActivity {

    DatabaseHelper myDb;
    Button b6;
    EditText e1name,e2address,e3phone,e4email,e5id;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_update);
        myDb = new DatabaseHelper(this);

        b6 = findViewById(R.id.button5);

        e1name = findViewById(R.id.editText);
        e2address = findViewById(R.id.editText2);
        e3phone = findViewById(R.id.editText3);
        e4email = findViewById(R.id.editText4);
        e5id = findViewById(R.id.editText5);

        b6.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                if(e5id.getText().toString() == "")
                {
                    Toast.makeText(getApplicationContext(),"Enter ID",Toast.LENGTH_LONG).show();
                }
                boolean isupdated = myDb.updateData(e5id.getText().toString(),e1name.getText().toString(),e2address.getText().toString(),e3phone.getText().toString()
                ,e4email.getText().toString());

                if(isupdated==true)
                    Toast.makeText(getApplicationContext(),"Updated",Toast.LENGTH_LONG).show();
                else
                    Toast.makeText(getApplicationContext(),"Not Updated",Toast.LENGTH_LONG).show();

            }
        });

    }
}

Display Activity

Selanjutnya diplay activity atau bisa di sebut juga dengan Read

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout 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"
    tools:context=".DisplayActivity">

</androidx.constraintlayout.widget.ConstraintLayout>

Memang tdk terlalu bnyak di xmlnya

karena hanya untuk menampilkan saja

Java nya

package com.example.crudapps;

import androidx.appcompat.app.AppCompatActivity;

import android.app.AlertDialog;
import android.database.Cursor;
import android.os.Bundle;
import android.widget.Button;
import android.widget.Toast;

public class DisplayActivity extends AppCompatActivity {

    DatabaseHelper myDb;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_display);
        myDb = new DatabaseHelper(this);

        Cursor res = myDb.getAllData();

        if(res.getCount()==0)
        {
            Toast.makeText(getApplicationContext(),"No Data",Toast.LENGTH_LONG).show();
        }
        else
        {
            StringBuffer buffer = new StringBuffer();
            while(res.moveToNext())    //it is like rs.next()
            {
                buffer.append("ID : "+res.getString(0));
                buffer.append("\nName : "+res.getString(1));
                buffer.append("\nAddress : "+res.getString(2));
                buffer.append("\nPhone : "+res.getString(3));
                buffer.append("\nEmail : "+res.getString(4)+"\n\n");
                showMessage("Data",buffer.toString());
            }
        }
    }

    public void showMessage(String title,String message)
    {
        AlertDialog.Builder builder = new AlertDialog.Builder(this);
        builder.setCancelable(true);
        builder.setTitle(title);
        builder.setMessage(message);
        builder.show();
    }
}

Dan hasilnya seperti berikut

Itulah basic dari database offline di database , bisa dikembangkan lagi untuk tampilan dll , masih banyak yang dipelajari dalam dunia programming

Selamat Mencoba_

Tinggalkan Balasan

Alamat email Anda tidak akan dipublikasikan. Ruas yang wajib ditandai *

This site uses Akismet to reduce spam. Learn how your comment data is processed.