Monday, May 21, 2012

SQLITE DATABSE in android


8. DATABASE

8.1 DATABASE

Database contains classes to explore data returned through a content provider or if you need to manage data in a private database, use the android.database.sqlite classes. These classes are used to manage the Cursor object returned from a content provider query. Databases are usually created and opened with openOrCreateDatabase(String, int, SQLiteDatabase.CursorFactory).
In our applications we will be mainly dealing with SQLiteDatabase.It contains the SQLite database management classes that an application would use to manage its own private database. Applications use these classes to maange private databases. Exposes methods to manage a SQLite database.

8.2 SQLiteOpenHelper

To create and upgrade a database in your Android application you usually subclass SQLiteOpenHelper . In this class you need to override the methods onCreate() to create the database and onUpgrade() to upgrade the database in case of changes in the database schema. Both methods receive an SQLiteDatabase object which represents the database.
SQLiteOpenHelper provides the methods getReadableDatabase() and getWriteableDatabase() to get access to an SQLiteDatabase object which allows database access either in read or write mode.
For the primary key of the database tables you should always use the identifier _id as some of Android functions rely on this standard.
A best practice is to create per table a separate class which define static onCreate() and onUpdate() methods. These methods are then called in the corresponding methods of SQLiteOpenHelper . This way your implementation of SQLiteOpenHelper will not get to large even if you have several tables.

8.3 SQLiteDatabase

SQLiteDatabase has methods to create, delete, execute SQL commands, and perform other common database management tasks.Database names must be unique within an application, not across all applications.
A simple program is given below here an edit text is provided followed by a button, on clicking the button the text in edittext is inserted into database
package a.b.c;

import android.app.Activity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;
import android.widget.Toast;

public class DatabaseActivity extends Activity {
      EditText e1;
      Button b1;
      SQLiteDatabase database = null;
      String s ;
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
       
        e1 = (EditText)findViewById(R.id.ed1);
        b1 = (Button)findViewById(R.id.bttn1);       
       
        database = DatabaseActivity.this.openOrCreateDatabase("Database1", MODE_PRIVATE, null);
        database.execSQL("CREATE TABLE IF NOT EXISTS Table1(name varchar);");
       
        b1.setOnClickListener(new OnClickListener() {
                 
                  @Override
                  public void onClick(View arg0) {
                        // TODO Auto-generated method stub
                        s = e1.getText().toString();                   
                        database.execSQL("INSERT INTO Table1 VALUES('"+s+"')");
                        Toast.makeText(getApplicationContext(), "Inserted successfully", Toast.LENGTH_SHORT).show();
                        e1.setText(" ");
                  }
            });      
       
    }
}
main.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >

    <TextView
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:text="@string/hello" />
    <EditText
        android:id="@+id/ed1"
        android:layout_width="140dip"
        android:layout_height="wrap_content"/>
    <Button
        android:id="@+id/bttn1"
        android:text="Done"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"/>
</LinearLayout>
Now we will combine list view and database. The aim of program is to create a database and insert values into it from edittext on button click on same time list the text from edittext. Also provided option to delete from database,
package a.b.c;

import java.util.ArrayList;

import android.app.ListActivity;
import android.content.Intent;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.Toast;

public class Datapass1Activity extends ListActivity {
      SQLiteDatabase dbpass = null;
      Button b1,b2;
      ArrayList<String> list = new ArrayList<String>();
      ArrayAdapter<String> arraydpter;
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
      
        arraydpter = new ArrayAdapter<String>(this, android.R.layout.simple_list_item_1,list);
        setListAdapter(arraydpter);
       
        dbpass = Datapass1Activity.this.openOrCreateDatabase("Datapass", MODE_PRIVATE, null);
        dbpass.execSQL("CREATE TABLE IF NOT EXISTS Tab1(name varchar(10));");
       
       
        //ADDING DATA TO LIST
        b1=(Button)findViewById(R.id.bttn1);
        b1.setOnClickListener(new OnClickListener() {
                 
                  public void onClick(View v) {
                        Intent intnt = new Intent(Datapass1Activity.this, second.class);
                        startActivityForResult(intnt, 0);
                       
                  }
            });
       
        //  REMOVING DATA FROM LIST
        b2 = (Button)findViewById(R.id.bttn2);
        b2.setOnClickListener(new OnClickListener() {
                 
                  public void onClick(View arg0) {
                       
                        //String s1 = list.get(0).toString();                      
                        String s = list.get(0).toString();
                        dbpass.execSQL("DELETE FROM Tab1 where name like '"+s+"' ");
                        list.remove(0);
                        arraydpter.notifyDataSetChanged();
                       
                  }
            });
       
    }
    @Override
    protected void onActivityResult(int requestCode, int resultCode, Intent data) {
      // TODO Auto-generated method stub
      super.onActivityResult(requestCode, resultCode, data);
      //HERE NO NEED FOR DCLARING INTENT COZ INTENT COMES ALONG WITH METHOD onActivityResult i.e, data
      Bundle newbun = data.getExtras();
      String s = newbun.getString("key");
      dbpass.execSQL("INSERT INTO Tab1 VALUES('"+s+"')");
      list.add(s);
      arraydpter.notifyDataSetChanged();
      Toast.makeText(this, s, Toast.LENGTH_SHORT).show();
    }
}
second.java
package a.b.c;

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;

public class second extends Activity {
      Button b3;
      EditText e1;
      String s1;
    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main1);
       
        e1 = (EditText)findViewById(R.id.ed1);
       
        b3 =(Button)findViewById(R.id.bttn3);
        b3.setOnClickListener(new OnClickListener() {
                 
                  public void onClick(View v) {
                 
                        s1 = e1.getText().toString();
                        Intent intent = new Intent();
                        Bundle bun = new Bundle();
                        bun.putString("key", s1);
                        intent.putExtras(bun);
                        setResult(RESULT_OK, intent);
                        finish();
                       
                  }
            });
    }
}
main.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical" >

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Hai"
        android:layout_marginTop="20dip"
        android:layout_gravity="center_horizontal" />
    <TextView
        android:id="@+id/tv1"
        android:text="What do you want to do...?"       
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="40dip"
        android:layout_gravity="center_horizontal"/>
    <Button
        android:id="@+id/bttn1"
        android:text=" Add to list "              
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="20dip"
        android:layout_gravity="center_horizontal"
        />
    <Button
        android:id="@+id/bttn2"
        android:text=" Remove from list "               
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="20dip"
        android:layout_gravity="center_horizontal"
        android:onClick="remove"/>
    <ListView
        android:id="@android:id/list"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content">
 </ListView>
</LinearLayout>

main1.xml
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="vertical" >   
    <EditText
        android:id="@+id/ed1"
        android:layout_width="150dip"
        android:layout_height="wrap_content"
        android:layout_gravity="center_horizontal"
        android:layout_marginTop="20dip"/>
    <Button
        android:id="@+id/bttn3"
        android:text="Click to enter to list"       
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="20dip"
        android:layout_gravity="center_horizontal"/>
</LinearLayout>
While using additional activities don’t forget to add them in manifest. Also provide an xml for each activity.


No comments:

Post a Comment