Pertemuan 8 Dasar query basis data dengan SQLite ARFIAN HIDAYAT, S.KOM HT TP://ARFIANHIDAYAT.COM
Pokok Bahasan Basis Data Mobile dengan SQLite ◦ ◦ ◦ ◦
Membuat Basis Data Mobile Menginput data pada SQLite Membaca data Mengubah dan Menghapus data
Menampilkan query dan hasil query basis data ke dalam log.
Membuat Basis Data C:\sqlite>sqlite3.exe mobile.db SQLite version 3.8.4.3 2014-04-03 16:53:12 Enter ".help" for usage hints.
sqlite> .databases seq name
file
--- --------------- ---------------------0 main
C:\sqlite\mobile.db
Contoh Struktur Tabel
Membuat Tabel create table contacts( id integer primary key autoincrement not null, name text, phone_number text ); Menampilkan list table dalam database sqlite> .tables Menampilkan skema table contacts
sqlite> .schema contacts
Input Data SQLite sqlite> insert into contacts (name, phone_number) values ('Arfian Hidayat','0856414412xx'); sqlite> insert into contacts (name, phone_number) values (‘Bejo Selamet','0856414413xx');
Note
Kolom id tidak diisi, karena kolom otomatis melakukan autoincrement id
Menampilkan Isi Data sqlite> .header on sqlite> .mode column sqlite> select * from contacts; id
name
phone_number
----------
--------------
------------
1
Arfian Hidayat
0856414412xx
2
Bejo Selamet
0856414413xx
Note : Kolom id terisi secara otomatis terurut
Mengubah Data Mengubah phone_number pada data yang memiliki id=2 sqlite> update contacts set phone_number='08564123456x' where id='2'; sqlite> .header on sqlite> .mode column sqlite> select * from contacts; id
name
phone_number
---------- --------------
------------
1
Arfian Hidayat
0856414412xx
2
Bejo Selamet
08564123456x
Menghapus Data Menghapus data yang memiliki id=2 sqlite> delete from contacts where id='2'; sqlite> .header on sqlite> .mode column sqlite> select * from contacts; id
name
phone_number
--------- --------------
------------
1
0856414412xx
Arfian Hidayat
Connect Database SQLite
public long insert (String table, String nullColumnHack, ContentValues values) Convenience method for inserting a row into the database. Table
the table to insert the row into
NullColumnHack optional; may be null. SQL doesn't allow inserting a completely empty row without naming at least one column name. If your provided values is empty, no column names are known and an empty row can't be inserted. If not set to null, the nullColumnHack parameter provides the name of nullable column name to explicitly insert a NULL into in the case where your values is empty. values
this map contains the initial column values for the row. The keys should be the column names and the values the column values
Returns
the row ID of the newly inserted row, or -1 if an error occurred
public void put (String key, byte[] value) Adds a value to the set. Parameters key
the name of the value to put
value the data for the value to put
public int update (String table, ContentValues values, String whereClause, String[] whereArgs) Convenience method for updating rows in the database. Table
the table to update in
Values
a map from column names to new column values. null is a valid value that will be translated to NULL.
whereClause
the optional WHERE clause to apply when updating. Passing null will update all rows.
whereArgs
You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.
Returns
the number of rows affected
public int delete (String table, String whereClause, String[] whereArgs) Convenience method for deleting rows in the database. Table
the table to delete from
WhereClause
the optional WHERE clause to apply when deleting. Passing null will delete all rows.
WhereArgs
You may include ?s in the where clause, which will be replaced by the values from whereArgs. The values will be bound as Strings.
Returns
the number of rows affected if a whereClause is passed in, 0 otherwise. To remove all rows and get a count pass "1" as the whereClause.
Menulis Log Log berguna ketika kita akan menginisialisasi ,melihat kinerja code kita dsb, di jme/j2me kita dapat menggunakansystem.out.println untuk menampilkan log ke standart output di android kita dapat menggunakan ◦ Log.d(“tag/judul”,”isi log anda”);
jangan lupa import kebutuhan fungsi ◦ import android.util.Log;
untuk tipe – tipe log ◦ ◦ ◦ ◦ ◦
Log.v(); // Verbose Log.d(); // Debug Log.i(); // Info Log.w(); // Warning Log.e(); // Error
Code Untuk Mampilkan Log // Reading all contacts Log.d("Reading: ", "Reading all contacts.."); List
contacts = db.getAllContacts(); for (Contact cn : contacts) { String log = "Id: "+cn.getID()+" ,Name: " + cn.getName() + " ,Phone: " + cn.getPhoneNumber(); // Writing Contacts to log Log.d("Name: ", log); }
Android Log Cat Report I am writing output to Log report. You can see your log report by going to Windows ⇒ Show View ⇒
Other.. ⇒ Android ⇒ Log Cat.
SQLite Output Log
Sumber https://www.sqlite.org/cli.html http://www.androidhive.info/2011/11/android-sqlite-database-tutorial/ http://www.tutorialspoint.com/sqlite
