09.1. Android - Local Database (Sqlite)

09.1. Android - Local Database (Sqlite)

• 1.
  09.1. Database Oum Saokosal Masterof Engineering in Information Systems, South Korea 855-12-252-752 oum_saokosal@yahoo.com
• 2.
  SQLite • SQLite ispopular light-weight database • For Android, SQLite is "baked into" the Android runtime, so every Android application can create SQLite databases.
• 3.
  Fundamental of SQL •Create a table: CREATE TABLE IF NOT EXISTS tbl_student ( stu_id INTEGER PRIMARY KEY AUTOINCREMENT, stu_name VARCHAR(50) NULL, stu_add VARCHAR(100) NULL, stu_gpa FLOAT NULL );
• 4.
  • Insert arecord: INSERT INTO tbl_student VALUES ( 80123, 'Sok Visal', 'Phnom Penh', 4.0 );
• 5.
  • Select arecord: SELECT * FROM tbl_student WHERE stu_id=80123 ORDER BY stu_name ASC • View many records: SELECT * FROM tbl_student WHERE stu_name LIKE '%sok%' ORDER BY stu_name DESC
• 6.
  • Update arecord: UPDATE tbl_student SET stu_name='Sok Visal', stu_add='Takeo', stu_gpa=4.0 WHERE stu_id = 80123;
• 7.
  • Delete arecord: DELETE FROM tbl_student WHERE stu_id=80123;
• 8.
  SQLite Statements • ToCreate a database: SQLiteDatabase db = openOrCreateDatabase( "database_name", MODE_PRIVATE, null); • To execute a raw SQL statement: db.execSQL("CREATE TABLE tbl_product (pro_id integer, pro_name nvarchar(50));"); • To close a database: db.close();
• 9.
  • To createa table by executing a raw SQL statement: db.execSQL("CREATE TABLE IF NOT EXISTS tbl_student (stu_id INT PRIMARY KEY AUTOINCREMENT, stu_name VARCHAR(50) NULL, stu_add VARCHAR(100) NULL, stu_gpa FLOAT);");
• 10.
  • Insert arecord: db.execSQL("INSERT INTO tbl_student VALUES (80123, 'Sok Visal', 'Phnom Penh', 4.0);");
• 11.
  • Update arecord: db.execSQL("UPDATE tbl_student SET stu_name='Sok Visal', stu_add='Takeo', stu_gpa=4.0 WHERE stu_id = 80123;");
• 12.
  • Delete arecord: db.execSQL("DELETE FROM tbl_student WHERE stu_id= 80123;");
• 13.
  Records Retrieval • Afteryou select records using SELECT, you need to display it. In Android, you will use Cursor class to store the records. Cursor result = db.rawQuery("SELECT * FROM tbl_student WHERE stu_id=80123 ORDER BY stu_name ASC", null);
• 14.
  • Complete codeof records retrieval: Cursor result = db.rawQuery("SELECT * FROM tbl_student WHERE stu_id=80123 ORDER BY stu_name ASC", null); result.moveToFirst(); //Must include this while(result.isAfterLast() != true){ int id = result.getInt(0); String name = result.getString(1); String address = result.getString(2); int gpa = result.getFloat(3); //Do something here result.moveToNext(); }
• 15.
  Where is MyDatabase? • Please note that to see the database folder, you need to run the emulator first. • After created a database, you can find it by going to DDMS -> File Explorer -> Then open the folder: data/data/your.app.package/databases/yourdbname
• 17.
  How to ViewMy Database? • There is a plugin that allows you to view SQLite file in Eclipse. Below is the url: http://www.tylerfrankenstein.com/user/4/browse-android- emulator-sqlite-database-eclipse • After you download it onto your machine, place it here:
• 18.
  How to ViewMy Database? Cont.
• 19.
  Project Assignment • Thinkof your own favorite project related to a database and intent. And then make it a nice App for Android.
• 20.
  Go on tothe next slide