SQLite database

Android stores data in SQLite database.

Why SQLite?

• Its lightweight
  SQLite database is just a file. When not used, it doesnst use up any processor time.
• Its optimized for a single user
  Your app is the only thing that will talk to the database, so you shouldn’t have to identify with a username and password
• It’s stable and fast
  SQLite databases are stabel.

Where’s the database stored?

Android automatically creates a folder for each app where the app’s database cab be stored. When we create a databse, it will be stored in the following folder on the device
/data/data/com.mydomain.myappname/databases

Each database consists of two files. The first file is the daase file and has the same name as your database – for aexample, “myapp”. This is the main SQLite database file.All of your data is stored in this file.
The second file is the journal file. It has the same name as your database, with a suffix of “-journal” – for example, “myapp-journal”.

The journal file contains all of the changes made to your database. If there’s a problem, Android will use the journal to tundo your latest changes.

Android uses a set fo classes that allwos you to manage a SQLite database. There are theree types of object that do the bulk of this work:

• The SQLite Helper
  A SQHLite helper enables you to create and manage datases. You creat one by extending the SQLiteOpenHelper class.
• THe SQLite Database
  The SQLiteDatabase class gives you access to the databse.
• Cursors
  A Cursor lets you read from and write to the datase.

Here are the main data types you can use in SQLite, and what they can store:

INTEGER	Any integer type
TEXT	Any character type
REAL NUMERIC	Any floating-point number, Booleans, dates, and date-times
BLOB	Binary Large Object

Unlike most database systems, you don’t need to specify the column size in SQLite.

You create tables using Structured Query Language

SQL

CREATE TABLE DRINK (_id INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, DESCRIPTION TEXT, IMAGE_RESOURCE_ID INTEGER)

The SQLite helper is in charge of creating the SQLite database. An empty database is created on the device the first time it needs to be used, and then the SQLite helper’s onCreate() method is called. The onCreate() method has one parameter, a SQLiteDatabase object that represents the database that’s been created.

The SQLiteDatabase class gives you access to the database.

You can use the SQLiteDatabase execSQL() method to execude SQL on the database. This method has one parameter, the SQL you want to execute.

executeSQL(String sql);

To insert data into a table in a SQLite database, you start by specifying what values you want to insert into the table. To do this, you first create a ContentValues object:
ContentValues objValues = new ContentValues();

A ContentValues object describes a set of data.Youe usually create a new ConentValues object for each row of data you want tot create.
You add data to the ContentValues object using its put() method. This method adds name/value pairs of data: NAME is the column you want to add data to, and value is the data:

objValues.put("NAME", "value");

Once you’ve added a row of data to the ContentValues object, you insert it into the table using the SQLiteDatabase insert() method. This method inserts data into a table, and returns the ID of the record once it’s been inserted:

db.insert("SOMETABLE", null, objValues);

The middle parameter is usually set to null, as in the code above. It’s there in case the ContentValues object is empty, and you want to insert an empty row into your table.

To insert multiple rows into a table, you need to make repeat calls to the insert() method. Each call to the method inserts a seperate row. To insert multiple rows, you usually create a new method that inserts a single row of data, and call it each time you want to add a new row.

private static void insertDrink(SQLiteDatabse db,String name, String description, int resourceId){
ContentValues objValues = new ContentValues();
objValues.put("NAME", name);
objValues.put("DESCRIPTION", description);
objValues.put("IMAGE_RESOURCE_ID", resourceId);
db.insert("SOMETABLE", null, objValues);
}

insertDrink(db, "Latte", "Espresso and steamed milk", R.drawable.latte);
insertDrink(db, "Cappuccino", "Espresso and hot milk", R.drawable.latte);

Update records with the update() method

The update() method lets you update records in the database, and returns the number of records it’s updated. To use the update() method, you specify the table you want to update records in, the ContentValues object.

ContentValues objValues = new ConentValues();
objValues.put("DESCRIPTION", "Tasty");
db.update("SOMETABLE",objValues, "NAME = ?", new String[] {"Latte"});

The first parameter of the update() method is the name of the table you want to update(in this case, the SOMETABLE table).
The second parameter is the ContentValues object that describes the values you want to update.
The last two parameters specify which records you want to update by describing conditionsfor the update. Togehter, they form the WHERE clause of a SQL statement. The third parameter specifies the name of the column in the condition. In the above example, we want to update recors where teh value of the NAME collumn is “Latte”, so we use “NAME =?”; it means that we want the value in the NAME column to be eqal to some value. The ? symbo is a placeholder for this value. The last parameter is an array of Strings that says what the value of the condition should be.

If you set teh last two parameters of the update() method to null, ALL records in the table will be updated.

As an example:

db.update("SOMETABLE", objValues,null, null);

will update all records in the SOMETABLE table.