Android SQLite

      This section,describes how to use the SQLite database in Android applications. SQLite is an Open Source database. SQLite supports standard relational database features like SQL syntax, transactions and prepared statements. The database requires limited memory at runtime (approx. 250 KByte) which makes it a good candidate from being embedded into other runtimes.
       SQLite supports the data types TEXT (similar to String in Java), INTEGER (similar to long in Java) and REAL (similar to double in Java). All other types must be converted into one of these fields before getting saved in the database. SQLite itself does not validate if the types written to the columns are actually of the defined type, e.g. you can write an integer into a string column and vice versa.

More information about SQLite can be found on the SQLite website: http://www.sqlite.org.

SQLite in Android

       SQLite is embedded into every Android device. Using an SQLite database in Android does not require a setup procedure or administration of the database.

You only have to define the SQL statements for creating and updating the database. Afterwards the database is automatically managed for you by the Android platform.

Access to an SQLite database involves accessing the file system. This can be slow. Therefore it is recommended to perform database operations asynchronously.

If your application creates a database, this database is by default saved in the directoryDATA/data/APP_NAME/databases/FILENAME.

     To create and upgrade a database in your Android application you create a subclass of the SQLiteOpenHelper class. In the constructor of your subclass you call the super() method of SQLiteOpenHelper, specifying the database name and the current database version.

     In this class you need to override the following methods to create and update your database.

• onCreate() - is called by the framework, if the database is accessed but not yet created.
• onUpgrade() - called, if the database version is increased in your application code. This method allows you to update an existing database schema or to drop the existing database and recreate it via the onCreate()method.

Both methods receive an SQLiteDatabase object as parameter which is the Java representation of the database.

The SQLiteOpenHelper class provides the getReadableDatabase() and getWriteableDatabase() methods to get access to an SQLiteDatabase object; either in read or write mode.

       Here am demonstrating the SQLITE by creating a Android project, which the user can add some data to the database and a listview for displaying the data.

for that create a new project DatabaseLeacher on Eclipse and follow the steps

     The project is

step 1: Open MainActivity.java and insert this code

package com.raspberry.databaseleacher;

import android.app.Activity;

import android.content.Intent;

import android.os.Bundle;

import android.view.View;

public class MainActivity extends Activity {

@Override

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.activity_main);

}

public void addData(View v)

{

Intent i = new Intent(this , AddData.class);

startActivity(i);

}

public void viewTable(View v)

{

Intent i = new Intent(this , ViewTable.class);

startActivity(i);

}

}

step2: open activity_main

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"

    xmlns:tools="http://schemas.android.com/tools"

    android:id="@+id/RelativeLayout1"

    android:layout_width="match_parent"

    android:layout_height="match_parent"

    android:columnCount="3"

    android:orientation="horizontal"

    tools:context=".MainActivity" >

    <TextView

        android:id="@+id/textView1"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_alignLeft="@+id/button2"

        android:layout_alignParentTop="true"

        android:layout_marginTop="48dp"

        android:text="Database Leacher"

        android:textAppearance="?android:attr/textAppearanceLarge" />

    <Button

        android:id="@+id/button1"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_below="@+id/textView1"

        android:layout_marginLeft="14dp"

        android:layout_marginTop="71dp"

        android:layout_toRightOf="@+id/button2"

        android:text="Add Data" 

        android:onClick="addData"

        />

    <Button

        android:id="@+id/button2"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_alignBaseline="@+id/button1"

        android:layout_alignBottom="@+id/button1"

        android:layout_alignParentLeft="true"

        android:layout_marginLeft="33dp"

        android:text="View table"

        android:onClick="viewTable"

         />

</RelativeLayout>

step3: Open DatabaseHandler.java

package com.raspberry.databaseleacher;

import java.util.ArrayList;

import java.util.List;

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper {

// All Static variables

// Database Version

private static final int DATABASE_VERSION = 1;

// Database Name

private static final String DATABASE_NAME = "contactsManager";

public DatabaseHandler(Context context) {

super(context, DATABASE_NAME, null, DATABASE_VERSION);

}

// Creating Tables

@Override

public void onCreate(SQLiteDatabase db) {

/*String CREATE_CONTACTS_TABLE = "CREATE TABLE " + TABLE_CONTACTS + "("

+ KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"

+ KEY_PH_NO + " TEXT" + ")";*/

String CREATE_CONTACTS_TABLE = "CREATE TABLE tbl(name TEXT,roll TEXT,mark TEXT)";

db.execSQL(CREATE_CONTACTS_TABLE);

}

@Override

public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) {

// TODO Auto-generated method stub

db.execSQL("DROP TABLE IF EXISTS tbl");

}

// Adding new contact

void addContact(String name,String roll,String mark) {

SQLiteDatabase db = this.getWritableDatabase();

/*

ContentValues values = new ContentValues();

values.put("name", name); 

values.put("roll", roll);

values.put("mark", mark);

// Inserting Row

db.insert("tbl", null, values);

*/

db.execSQL("INSERT INTO tbl (name,roll,mark) VALUES ('"+name+"','"+roll+"','"+mark+"')");

db.close(); // Closing database connection

}

//Getting all data

public List<String> getAllContacts() {

List<String> contactList = new ArrayList<String>();

// Select All Query

String selectQuery = "SELECT  * FROM tbl";

SQLiteDatabase db = this.getWritableDatabase();

Cursor cursor = db.rawQuery(selectQuery, null);

String d = "";

// looping through all rows and adding to list

if (cursor.moveToFirst()) {

do {

d = "";

d = d + cursor.getString(0);

d = d + ";" + cursor.getString(1);

d = d + ";" + cursor.getString(2);

contactList.add(d);

} while (cursor.moveToNext());

}

// return contact list

db.close();

return contactList;

}

}

Step4: open AddData.java

package com.raspberry.databaseleacher;

import java.util.List;

import android.app.Activity;

import android.os.Bundle;

import android.util.Log;

import android.view.View;

import android.widget.EditText;

import android.widget.Toast;

public class AddData extends Activity {

@Override

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.activity_add_data);

}

public void addData(View v)

{

EditText nameR = (EditText)findViewById(R.id.name);

EditText rollR = (EditText)findViewById(R.id.roll);

EditText markR = (EditText)findViewById(R.id.editText1);

String name = nameR.getText().toString();

String roll = rollR.getText().toString();

String mark = markR.getText().toString();

DatabaseHandler db = new DatabaseHandler(this);

try{

Log.d("Insert: ", "Inserting ..");

        db.addContact(name,roll,mark);

        

        Toast.makeText(getApplicationContext(), "Inserted", Toast.LENGTH_LONG).show();

}

catch (Exception e)

{

Toast.makeText(getApplicationContext(), "Error", Toast.LENGTH_LONG).show();

}

        List<String> data = db.getAllContacts();       

     

        for (String str : data) {

            //String log = //"Name: "+cn.getID()+" ,Name: " + cn.getName() + " ,Phone: " + cn.getPhoneNumber();

                // Writing Contacts to log

        Log.d("Name: ", str);

        }

}

}

Step 5: Open activity_add_data.xml

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"

    xmlns:tools="http://schemas.android.com/tools"

    android:id="@+id/RelativeLayout1"

    android:layout_width="match_parent"

    android:layout_height="match_parent"

    android:columnCount="1"

    tools:context=".AddData" >

    <TextView

        android:id="@+id/textView1"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_alignParentTop="true"

        android:layout_marginLeft="23dp"

        android:layout_marginTop="15dp"

        android:layout_toRightOf="@+id/textView2"

        android:text="Add data"

        android:textAppearance="?android:attr/textAppearanceLarge" />

    <TextView

        android:id="@+id/textView2"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_alignParentLeft="true"

        android:layout_below="@+id/textView1"

        android:layout_marginLeft="18dp"

        android:layout_marginTop="24dp"

        android:text="Name" />

    <TextView

        android:id="@+id/textView3"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_alignLeft="@+id/textView2"

        android:layout_below="@+id/name"

        android:layout_marginTop="38dp"

        android:text="Roll" />

    <TextView

        android:id="@+id/textView4"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_alignLeft="@+id/textView3"

        android:layout_below="@+id/roll"

        android:layout_marginTop="30dp"

        android:text="Mark" />

    <EditText

        android:id="@+id/editText1"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_alignBottom="@+id/textView4"

        android:layout_alignLeft="@+id/roll"

        android:ems="10"

        android:inputType="number" />

    <EditText

        android:id="@+id/name"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_alignBaseline="@+id/textView2"

        android:layout_alignBottom="@+id/textView2"

        android:layout_alignLeft="@+id/textView1"

        android:ems="10" />

    <EditText

        android:id="@+id/roll"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_alignBottom="@+id/textView3"

        android:layout_alignLeft="@+id/name"

        android:ems="10"

        android:inputType="number" >

        <requestFocus />

    </EditText>

    <Button

        android:id="@+id/button1"

        style="?android:attr/buttonStyleSmall"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_alignRight="@+id/editText1"

        android:layout_below="@+id/editText1"

        android:layout_marginRight="14dp"

        android:layout_marginTop="38dp"

        android:text="Add Data"

        android:onClick="addData"

         />

</RelativeLayout>

Step6 : Open ViewTable.java

package com.raspberry.databaseleacher;

import java.util.List;

import android.app.Activity;

import android.os.Bundle;

import android.util.Log;

import android.view.View;

import android.widget.ArrayAdapter;

import android.widget.ListView;

public class ViewTable extends Activity {

    // Declare the UI components

    private ListView monthsListView;

    private ArrayAdapter arrayAdapter;

@Override

protected void onCreate(Bundle savedInstanceState) {

super.onCreate(savedInstanceState);

setContentView(R.layout.activity_view_table);

}

public void retrive(View v)

{

monthsListView = (ListView) findViewById(R.id.listView1);

DatabaseHandler db = new DatabaseHandler(this);

List<String> data = db.getAllContacts();       

   

        for (String str : data) {

        Log.d("Name: ", str);

        }

        arrayAdapter = new ArrayAdapter(this, android.R.layout.simple_list_item_1, data);

        monthsListView.setAdapter(arrayAdapter);

}

}

Step7 : Open activity_view_table

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"

    xmlns:tools="http://schemas.android.com/tools"

    android:layout_width="match_parent"

    android:layout_height="match_parent"

    tools:context=".ViewTable" >

    <Button

        android:id="@+id/button1"

        android:layout_width="wrap_content"

        android:layout_height="wrap_content"

        android:layout_alignParentTop="true"

        android:layout_centerHorizontal="true"

        android:text="View Table"

        android:onClick="retrive"

         />

    <ListView

        android:id="@+id/listView1"

        android:layout_width="match_parent"

        android:layout_height="wrap_content"

        android:layout_alignParentLeft="true"

        android:layout_below="@+id/button1" >

    </ListView>

</RelativeLayout>

DOWNLOAD FULL CODE DatabaseLeacher.zip