Published on March 10, 2014
Lecture 5: Storage: Saving Data Database, Files & Preferences  Ahsanul Karim firstname.lastname@example.org http://droidtraining.wordpress.com Android Application Development 1. Storage Options 2. Saving Data into a Database a. Introduction to SQLite b. SQL Helper Database Creation c. CRUD [Create Read Update Delete] 3. Using a Pre-populated Database 4. Some optimizations
Saving Data Storage Options Options to save persistent application data:  Shared Preferences: Store private primitive data in key-value pairs.  Internal Storage: Store private data on the device memory.  External Storage: Store public data on the shared external storage.  SQLite Databases: Store structured data in a private database.  Network Connection: Store data on the web with your network server.
Saving Data Today We’ll Discuss... 1. Saving Data into Database a. Introduction to SQLite b. SQLiteOpenHelper: Database Creation c. CRUD [Create Read Update Delete] 2. Using a Pre-populated Database 3. Some optimizations
Saving Data into a Database Introduction to SQLite  SQLite is a software library that implements SQL database engine  Self-contained: Requires very minimal support from external libraries or from the operating system. (well suited for embedded devices)  Serverless: No separate server process like most SQL database implementations (MySql, Oracle etc.). The process that wants to access the database reads and writes directly from the database files on disk.  Zero-configuration: Does not need to be "installed". There is no "setup" procedure. There is no server process that needs to be started, stopped, or configured. No need for an administrator to create a new database instance or assign access permissions to users.  Transactional: All changes and queries appear to be Atomic, Consistent, Isolated, and Durable (ACID)
Saving Data into a Database Introduction to SQLite  Features: 1. Self-contained, Serverless, Zero-configuration and Transactional 2. A complete database is stored in a single cross-platform disk file. 3. Supports terabyte-sized databases and gigabyte-sized strings and blobs. 4. Small code footprint 5. Faster than popular client/server database engines for most operations. 6. Simple, easy to use API. 7. Written in ANSI-C. 8. Cross-platform: Unix (Linux, Mac OS-X, Android, iOS) and Windows (Win32, WinCE, WinRT) are supported out of the box. Easy to port to other systems. 9. Sources are in the public domain. Use for any purpose. 10. Comes with a standalone command-line interface (CLI) client that can be used to administer SQLite databases.
Saving Data into a Database Introduction to SQL  Basic database concepts:  What is SQL? SQL stands for Structured Query Language SQL lets you access and manipulate databases  What Can SQL do? SQL can execute queries against a database SQL can retrieve data from a database SQL can insert records in a database SQL can update records in a database SQL can delete records from a database SQL can create new databases SQL can create new tables in a database SQL can create stored procedures in a database SQL can create views in a database SQL can set permissions on tables, procedures, and views http://www.w3schools.com/sql/sql_intro.asp
Saving Data into a Database Introduction to SQL   Relational Database Management System (RDBMS) 1. RDBMS stands for Relational Database Management System. 2. RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, Microsoft Access and SQLite. 3. The data in RDBMS is stored in database objects called tables. 4. A table is a collection of related data entries and it consists of columns and rows. http://www.w3schools.com/sql/sql_intro.asp Let’s have a quick look in SQL first...
Saving Data into a Database Introduction to SQL   RDBMS Example
Saving Data into a Database Introduction to SQL   RDBMS Example
Saving Data into a Database Introduction to SQL   Database operations 1. Database creation 2. Table(s) creation 3. Insert Record(s) 4. Update Record(s) 5. Delete Record(s) 6. Query 7. Drop Table(s) 8. Alter Table(s) SELECT - extracts data from a database UPDATE - updates data in a database DELETE - deletes data from a database INSERT INTO - inserts new data into a database CREATE DATABASE - creates a new database ALTER DATABASE - modifies a database CREATE TABLE - creates a new table ALTER TABLE - modifies a table DROP TABLE - deletes a table CREATE INDEX - creates an index (search key) DROP INDEX - deletes an index  SQL Commands
Saving Data into a Database Introduction to SQL   Example SQL Commands CREATE TABLE IF NOT EXISTS `employee` ( `EmployeeID` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(50) NOT NULL, `Address` varchar(100) NOT NULL, `PhoneNumber` varchar(11) NOT NULL, `Designation` varchar(50) NOT NULL, PRIMARY KEY (`EmployeeID`) ); [a] Table Creation
Saving Data into a Database Introduction to SQL   Example SQL Commands INSERT INTO employee (EmployeeID, Name, Address, PhoneNumber, Designation) VALUES (NULL, 'Hasan', 'Mirpur, Dhaka', '01745534', 'Developer'); INSERT INTO employee (EmployeeID, Name, Address, PhoneNumber, Designation) VALUES (NULL, 'Rahim', 'Motijheel, Dhaka', '01956635', 'Designer'); [a] Insert Records
Saving Data into a Database Introduction to SQL   Example SQL Commands SELECT * FROM employee; SELECT Name, Address FROM employee; SELECT * FROM employee WHERE EmployeeID = 1; SELECT * FROM employee WHERE Designation = ‘Developer’; [b] Query Records
Saving Data into a Database SQLite in Android
Saving Data into a Database Questions... Man who waits for roast duck to fly into mouth must wait very, very long time. -Chinese Proverb
Store structured data in a private database. Network Connection Store data on the ... preferences files ... internal storage. Saving cache files.
Persistent Data Storage ... preference file that belongs to the activity, you ... 5 • preference controls used to set the preferences
... Web SQL Database, Indexed Database, and File ... "client-side storage" means data is passed to ... e.g. preferences and cache. Beyond saving data, ...
It also explains how to read and write files in Android. ... Database and other private data will ... 5.1. Create preference file ...
Persistent Data Storage ... preference file that belongs to the activity, you ... • Insert data into the database by passing ...
... How to save data during gameplay? ... such as save files, settings and preferences, ... Storing data in a sqllite database.
This policy defines the backup policy ... storage data back from the offline media and putting it on an online storage system such as a file server. 5 ...