Android and Database

tenderlaSoftware and s/w Development

Dec 13, 2013 (3 years and 6 months ago)

77 views

Android and Database

Prepared from :
http://www.vogella.com/articles/AndroidSQLite/article.html

by Ken Nguyen

Clayton State University

2012

Nice tutorials
here


http://www.vogella.com/articles/AndroidSQLit
e/article.html


MVC


Model
-
View
-
Controller


It is easier to view the app in MVC model


M


the data model


the database


V


the interface of the application


C


the controller to monitor and



update the V and M


V
Layout

C

Activity /
Intent

M

Database

Monitor/update

Query/update

SQLite


Tiny SQL database engine developed by Dr.
Richard
Hipp

in 2000


SQLite footprint is about 250KB


Free


No server, no configuration, no admin


Included in mobile devices (android,
iphones
,
Symbian phones,
etc
), web
-
browser, OS, etc.

SQL commands


case insensitive


c
reate table

mytable

(


id
integer primary key

autoincrement
,


name
text
,


phone
text



);


i
nsert into

mytable

values

( null, ‘Steven’, ‘123
-
123
-
1234’);


i
nsert into

mytable

(name, phone)
values
(‘Ken’, ‘404
-
123
-
1234’);


s
elect * from

mytable

where

(id = 3);


select name, phone from
mytable

where (name like
“%
teven
%”);


d
rop table if exists
mytable
;

SQLite for Android Apps


Should have the following Java classes


Helper class that extends
SQLiteOpenHelper


An Activity act as a control to for the application


SQL Helper


Database Modeler (M)


Model your data


create your tables in SQL


Extends Android
SQLiteOpenHelper


Represents the database


Manage database creation and versions


Needs:



a constructor


overriding
oncreate
(
SQLiteDatabase

db
)


called on
the first database reference


o
verriding
onUpgrade
(
SQLiteDatabase

db
,
int

oldVersion
)


called when the database version is
different


SQL Helper Example

//InfoData.java


Events
table containing
|
id|time|title
|

public
class
InfoData

extends
SQLiteOpenHelper

{


private static final String DB_NAME = "
info.db
";


private static final
int

DB_VERSION = 1;




//database table name and columns


private static final String TABLE_NAME = "events";


private static final String ID = "id";


private static final String TIME = "time";


private static final String TITLE = "title";




public
InfoData
(Context
ctx
){



super(
ctx
, DB_NAME, null, DB_VERSION);


}




@Override


public void
onCreate
(
SQLiteDatabase

db
) {



//crate a new table



db.execSQL
("CREATE TABLE " + TABLE_NAME + "(" +



ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +



TIME + " INTEGER, " +



TITLE + " TEXT NOT NULL" + ");");


}




@Override


public void
onUpgrade
(
SQLiteDatabase

db
,
int

oldVersion
,





int

newVersion
) {



db.execSQL
("DROP TABLE IF EXISTS " + TABLE_NAME);



onCreate
(
db
);


}


}

Example Layout ( V)


activity_sqlex1.xml

<
RelativeLayout

xmlns:android
=
"http://schemas.android.com/
apk
/res/android"


xmlns:tools
=
"http://schemas.android.com/tools"


android:layout_width
=
"
match_parent
"


android:layout_height
=
"
match_parent
" >



<
TextView


android:id
=
"@+id/
textView
"


android:layout_width
=
"
wrap_content
"


android:layout_height
=
"
wrap_content
"


android:layout_alignParentLeft
=
"true"


android:layout_alignParentTop
=
"true"


android:layout_marginTop
=
"132dp"


android:text
=
"DB content" />



<
EditText


android:id
=
"@+id/input"


android:layout_width
=
"
wrap_content
"


android:layout_height
=
"
wrap_content
"


android:layout_alignParentLeft
=
"true"


android:layout_alignParentRight
=
"true"


android:layout_alignParentTop
=
"true"


android:ems
=
"10"


android:hint
=
"enter your event name"
/>


<Button


android:id
=
"@+id/button"


android:layout_width
=
"
wrap_content
"


android:layout_height
=
"
wrap_content
"


android:layout_below
=
"@+id/input"


android:layout_marginLeft
=
"40dp"


android:layout_toRightOf
=
"@+id/
textView
"


android:text
=
"submit"


android:onClick
=
"
sendData
" />

</
RelativeLayout
>

Example
-

SQLEx1.java (C)

//SQLEx1.java


activity java file


i.e. controller

public
class SQLEx1 extends Activity {

private
InfoData

DB
; //need a model for data


storage via SQLite

private static String[]
FROM = {"id", "time", "title
"}; //database column names

private static String
ORDER_BY = "time DESC
";


@Override


public void
onCreate
(Bundle
savedInstanceState
) {


super.onCreate
(
savedInstanceState
);


setContentView
(R.layout.
activity_sqlex1);


DB =
new
InfoData
(this
); //create a database


try{


addData
("Hello, Android!");


Cursor c =
getData
();


showData
(c);


}
finally{


DB.close
();


}


}

private void
showData
(Cursor c) {


String output = "Saved events:
\
n";


while(
c.moveToNext
()){


long id =
c.getLong
(0);


long time =
c.getLong
(1);


String title =
c.getString
(2);


output += id + ":" + ":" + time +

":" + title + "
\
n";


}


TextView

text = (
TextView
)
findViewById
(
R.id.
textView
);


text.setText
(output
);

}


//
pull a cursor pointing at the first record in the database

private Cursor
getData
() {

SQLiteDatabase

db

=
DB.getReadableDatabase
();

Cursor c =
db.query
("events",
FROM,
null, null, null, null, ORDER_BY);

startManagingCursor
(c);

return c;

}



//
add data into the database


private void
addData
(String s){


SQLiteDatabase

db

=
DB.getWritableDatabase
();




//insert with
sql

statement


db.execSQL
("insert into events (time, title) values(" +


"'" +
System.
currentTimeMillis
() + "', 'SQL:" + s + "');"
);



//insert with object creation


ContentValues

values =
new
ContentValues
();


values.put
("time",
System.
currentTimeMillis
());


values.put
("title", s);


db.insert
("events",
null, values
);


}





//Button handler
-

save
input into the database
//and
update the
view

public
void
sendData
(View v
){


EditText

ed

= (
EditText
)
findViewById
(
R.id.
input
);



String
input =
ed.getText
().
toString
();



ed.setText
(""); //clear out the input



addData
(input
);



Cursor
c =
getData
();


showData
(c
);


}

}//end of SQLEx1 class

Suggesting Apps


Event Registration


send email confirmation


Additional Activity to manage database


Edit


Send email remind of the event


Export database to different format



Address book


Find GPS coordinate via address


Notify users when being near an register address


Group/individual notify


privacy must be address