Android and Database

1. Android and Database Prepared from : http://www.vogella.com/articles/AndroidSQLite/article.html by Ken Nguyen Clayton State University 2012

2. Nice tutorials here • http://www.vogella.com/articles/AndroidSQLite/article.html

3. 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 C Activity / Intent Monitor/update V Layout Query/update M Database

4. 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.

5. SQL commands – case insensitive • create tablemytable ( id integer primary keyautoincrement, name text, phone text ); • insert intomytablevalues ( null, ‘Steven’, ‘123-123-1234’); • insert intomytable(name, phone) values (‘Ken’, ‘404-123-1234’); • select * frommytablewhere (id = 3); • select name, phone from mytable where (name like “%teven%”); • drop table if exists mytable;

6. 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

7. 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(SQLiteDatabasedb) – called on the first database reference • overriding onUpgrade(SQLiteDatabasedb, intoldVersion) – called when the database version is different

8. 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); }

9. @Override public void onCreate(SQLiteDatabasedb) { //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(SQLiteDatabasedb, intoldVersion, intnewVersion) { db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME); onCreate(db); } }

10. Example Layout ( V) – activity_sqlex1.xml <RelativeLayoutxmlns: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" />

11. <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>

12. 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(); } }

13. 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() { SQLiteDatabasedb = DB.getReadableDatabase(); Cursor c = db.query("events", FROM, null, null, null, null, ORDER_BY); startManagingCursor(c); return c; }

14. //add data into the database private void addData(String s){ SQLiteDatabasedb = 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); }

15. //Button handler - save input into the database //and update the view public void sendData(View v){ EditTexted = (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

16. 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