1 / 20

09.1. Database

09.1. Database. Prof. Oum Saokosal Master of Engineering in Information Systems, South Korea 855-12-252-752 oum_saokosal@yahoo.com. SQLite. SQLite is popular light-weight database

jane
Download Presentation

09.1. Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 09.1. Database Prof. Oum Saokosal Master of Engineering in Information Systems, South Korea 855-12-252-752 oum_saokosal@yahoo.com

  2. SQLite SQLite is popular 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 VARCHAR PRIMARY KEY, stu_name VARCHAR NOT NULL, stu_add VARCHAR, stu_gpa INT );

  4. Insert a record: INSERT INTO tbl_student VALUES ( '80123', 'SokVisal', 'Phnom Penh', '4.0' );

  5. Select a record: 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 ASC;

  6. Update a record: UPDATE tbl_student SET stu_name='SokVisal', stu_add='Takeo', stu_gpa='4.0' WHERE stu_id = '80123';

  7. Delete a record: DELETE FROM tbl_student WHERE stu_id='80123';

  8. SQLite Statements To Create a database: SQLiteDatabase db = openOrCreateDatabase( "database_name", MODE_PRIVATE, null); To execute a raw SQL statement: db.execSQL("SELECT * FROM tbl_student;"); To close a database: db.close();

  9. To create a table by executing a raw SQL statement: db.execSQL("CREATE TABLE IF NOT EXISTS tbl_student (stu_id INT PRIMARY KEY, stu_name VARCHAR NOT NULL, stu_add VARCHAR, stu_gpa INT);");

  10. Insert a record: db.execSQL("INSERT INTO tbl_student VALUES ('80123', 'SokVisal', 'Phnom Penh','4.0');");

  11. Update a record: db.execSQL("UPDATE tbl_student SET stu_name='SokVisal', stu_add='Takeo', stu_gpa='4.0' WHERE stu_id = '80123';");

  12. Delete a record: db.execSQL("DELETE FROM tbl_student WHERE stu_id='80123';");

  13. Records Retrieval After you 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 code of 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); intgpa = result.getInt(3); //Do something here result.moveToNext(); }

  15. Where is My Database? 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

  16. How to View My 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:

  17. How to View My Database? Cont.

  18. Project Assignment Think of your own favorite project related to a database and intent. And then make it a nice App for Android.

  19. Go on to the next slide

More Related