Download
1 / 20
200 likes | 335 Views
This guide covers the essentials of SQLite, a lightweight database integrated into Android. Learn to create and manage databases with practical SQL commands such as CREATE, INSERT, SELECT, UPDATE, and DELETE. Explore how to retrieve records using Cursor class, and discover best practices for database management in Android applications. Gain hands-on experience with real-world SQL examples tailored for students. Enhance your project with this valuable resource and create your own custom Android app connected to a robust SQLite database.
E N D
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 For Android, SQLite is "baked into" the Android runtime, so every Android application can create SQLite databases.
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 );
Insert a record: INSERT INTO tbl_student VALUES ( '80123', 'SokVisal', 'Phnom Penh', '4.0' );
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;
Update a record: UPDATE tbl_student SET stu_name='SokVisal', stu_add='Takeo', stu_gpa='4.0' WHERE stu_id = '80123';
Delete a record: DELETE FROM tbl_student WHERE stu_id='80123';
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();
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);");
Insert a record: db.execSQL("INSERT INTO tbl_student VALUES ('80123', 'SokVisal', 'Phnom Penh','4.0');");
Update a record: db.execSQL("UPDATE tbl_student SET stu_name='SokVisal', stu_add='Takeo', stu_gpa='4.0' WHERE stu_id = '80123';");
Delete a record: db.execSQL("DELETE FROM tbl_student WHERE stu_id='80123';");
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);
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(); }
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
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:
Project Assignment Think of your own favorite project related to a database and intent. And then make it a nice App for Android.
