android database - Telkom University

1.


Playing with SQLite Database
SQLite : Database specific name for Android
Application
For windows there are several kind of database
name : Mysql, SQL server, Oracle, Microsoft
Access, etc. (Microsoft excel is not database!)
a. Create SQLite Database File
b. Database Query (Scripting)
c. Database Query (Interface)
2.
Steps to create Database application in
Android
1.
2.
3.
Open “SQLite Database Browser 2.0
b1.exe”
Ctrl+N (File-New Database)
Select directory and give name you want to
save database file (file name for database,
example : data)
Create Database (Create New Database)
unavailable in SQLite
2. Drop Database (Delete Existing Database) 
unavailable in SQLite
3. Create Table and its Field (Create New Table
and its Field)
4. Drop Table (Delete Existing Table)
5. Insert Data (Add New Data)
6. Update Data (Modify Existing Data)
7. Select Data (Read Existing Data)
8. Delete Data (Delete Existing Data)
1.
CREATE TABLE <namatabel> (namafield1
tipedatafield1, namafield2 tipedatafield2 ,
namafield3 tipedatafield3,…, namafieldN
tipedatafieldN)
Example :
Assumption :
Table name : kelasmobapp
Field names : NIM (integer primary key), Nama
(Text)
CREATE TABLE kelasmobapp (nim int,nama text)
DROP TABLE <namatabel>
Example :
Assumption :
Table name : kelasmobapp
DROP TABLE kelasmobapp
INSERT INTO <namatabel> VALUES
(datafield1,datafield2)
Example :
Assumption :
Table name : kelasmobapp
Field names : NIM (integer primary key), Nama (Text)
Data inserted : NIM= 111100001, Nama=’Heri Suprapto’
INSERT INTO kelasmobapp VALUES
(111100001,’Heri Suprapto’)
UPDATE <namatabel> SET <namafield>=<isidatafield>
WHERE <namafield>=<nilaitertentu>
Example :
Assumption :
Table name : kelasmobapp
Field names : NIM (integer primary key), Nama (Text)
Data updated : NIM= 111100001, Nama=’Heri Suprapto’ will
be updated to be ’Herawati Suprapti’
UPDATE kelasmobapp SET Nama=‘Herawati Suprapti’
WHERE NIM=111100001
SELECT
<namafield1>,<namafield2>,<namafield3>,….,<n
amafieldN> FROM <namatabel>
Example :
Assumption :
Table name : kelasmobapp
Field names : NIM (integer primary key), Nama (Text)
Data read : NIM
SELECT nim FROM kelasmobapp
All field :
SELECT * From kelasmobapp
DELETE FROM <namatabel> WHERE
<namafield>=<nilaitertentu>
Example :
Assumption :
Table name : kelasmobapp
Field names : NIM (integer primary key), Nama (Text)
Data deleted : NIM= 111100001, Nama=’Heri Suprapto’
will be deleted
DELETE FROM kelasmobapp WHERE
NIM=111100001
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Open “SQLite Database Browser 2.0 b1.exe”
Ctrl+N (File-New Database)
Select directory and give name you want to save
database file (file name for database example : data)
Make a name for the database table, and klik add
Add minimum a field name and type, click create
Add other field as you want
After finish, you can click create
Save the database (File-Save Database), you now
have a database file : data (without extension)
Make new data to all field, use GUI and query script.
Close “SQLite Database Browser 2.0 b1.exe”
Create Class DataBaseHelper.java to your project
Set DB_NAME in DataBaseHelper.java to database file name of your
project which you have created when preparing the SQLite Database
file (data)
Load your database file in DDMS , steps :
1.
2.
3.
a.
b.
c.
d.
e.
4.
5.
Click File Explorer tab
Find your package (if not existing, run your project in the first time then try
to find again)
Create folder databases
In databases folder, load your database file
You can view the table and the content by clicking on the database file and
click SQLite Browser
Create your xml interface file
Create your own script
1.
2.
3.
4.
Create Class DataBaseHelper.java to your
project
Set DB_NAME in DataBaseHelper.java to
database file name of your project which
you have created when preparing the
SQLite Database file (data)
Make new class for activity, example
testing.java:
Make your database file based on script (in
onCreate method):
try {
datavar
.execSQL("create table tabel(no int,status
teks, alamat teks,nim teks,nama
teks);");
} catch (Exception exception) {
Toast.makeText(Cobadb1Activity.this,
"Database sudah ada",
Toast.LENGTH_SHORT).show();
}
Note about datavar :
SQLiteDatabase datavar;
//before onCreate
DataBaseHelper db1 = new
DataBaseHelper(this); //
inside onCreate
datavar =
db1.getWritableDatabase();
// inside onCreate
5.
Create your own script
1.
2.
3.
4.
5.
CREATE
datavar
.execSQL("create table tabel(no int,status
teks, alamat teks,nim teks,nama teks);");
INSERT
SELECT
UPDATE
DELETE
datavar
.execSQL("insert into
tabel(Status,no,Alamat,NIM,Nama) values ('"
+ Status
+ "','"
+ no
+ "','"
+ Alamat
+ "','" + NIM + "','" + Nama + "'); ");
ContentValues CV = new ContentValues();
CV.put("Status", Status);
CV.put("No", no);
CV.put("Alamat", Alamat);
CV.put("NIM", NIM);
CV.put("Nama", Nama);
datavar.insert("tabel", null, CV);
final Cursor db;
db = datavar.rawQuery("select * from tabel;",
null);
db.moveToFirst();
nama.setText(db.getString(4));
nim.setText(db.getString(3));
alamat.setText(db.getString(2));
datavar.execSQL("update tabel set nama='" +
Nama + "',alamat='"
+ Alamat + "',status='" + Status + "' where
NIM='"
+ NIM + "';");
datavar.execSQL("delete from tabel where
nama='"
+ Nama + "';");