Monday, June 6, 2011

Add data to SQLite database, with SimpleCursorAdapter updated dynamically

Work on the article "A simple example using Android's SQLite database, exposes data from Cursor to a ListView", it was modified to add ui for user to add data to SQLite database. Once SQLite database updated, simple call cursor.requery() to update ListView dynamically.

Add data to SQLite database, with SimpleCursorAdapter updated dynamically

Modify main.xml and row.xml to update our ui
<?xml version="1.0" encoding="utf-8"?>

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

   android:orientation="vertical"

   android:layout_width="fill_parent"

   android:layout_height="fill_parent"

   >

<TextView 

   android:layout_width="fill_parent"

   android:layout_height="wrap_content"

   android:text="@string/hello"

   />

<TextView 

   android:layout_width="fill_parent"

   android:layout_height="wrap_content"

   android:text="Enter content of column 1"

   />

<EditText

   android:id="@+id/content1"

   android:layout_width="fill_parent"

   android:layout_height="wrap_content"

   />

<TextView 

   android:layout_width="fill_parent"

   android:layout_height="wrap_content"

   android:text="Enter content of column 2"

   />

<EditText

   android:id="@+id/content2"

   android:layout_width="fill_parent"

   android:layout_height="wrap_content"

   />

<Button

   android:id="@+id/add"

   android:layout_width="fill_parent"

   android:layout_height="wrap_content"

   android:text="Add"

   />

<Button

   android:id="@+id/deleteall"

   android:layout_width="fill_parent"

   android:layout_height="wrap_content"

   android:text="Delete All"

   />

<ListView

 android:id="@+id/contentlist"

 android:layout_width="fill_parent"

 android:layout_height="fill_parent"/>

</LinearLayout>


<?xml version="1.0" encoding="utf-8"?>

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

   android:orientation="vertical"

 android:layout_width="fill_parent"

 android:layout_height="wrap_content">

<LinearLayout

   android:orientation="horizontal"

 android:layout_width="fill_parent"

 android:layout_height="wrap_content">

<TextView

 android:layout_width="wrap_content"

 android:layout_height="fill_parent"

 android:padding="2dip"

 android:text="#"/>

<TextView

 android:id="@+id/id"

 android:layout_width="wrap_content"

 android:layout_height="fill_parent"

 android:padding="2dip"

 android:paddingRight="10dip"/>

<TextView

 android:layout_width="wrap_content"

 android:layout_height="fill_parent"

 android:padding="2dip"

 android:paddingRight="10dip"

 android:text="-" />

<TextView

 android:id="@+id/text1"

 android:layout_width="fill_parent"

 android:layout_height="fill_parent"

 android:padding="2dip"/>

</LinearLayout>

<TextView

 android:id="@+id/text2"

 android:layout_width="fill_parent"

 android:layout_height="wrap_content"

 android:padding="2dip"/>

</LinearLayout>


SQLiteAdapter.java
package com.exercise.AndroidSQLite;



import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

import android.database.sqlite.SQLiteDatabase.CursorFactory;



public class SQLiteAdapter {



 public static final String MYDATABASE_NAME = "MY_DATABASE";

 public static final String MYDATABASE_TABLE = "MY_TABLE";

 public static final int MYDATABASE_VERSION = 1;

 public static final String KEY_ID = "_id";

 public static final String KEY_CONTENT1 = "Content1";

 public static final String KEY_CONTENT2 = "Content2";



 //create table MY_DATABASE (ID integer primary key, Content text not null);

 private static final String SCRIPT_CREATE_DATABASE =

  "create table " + MYDATABASE_TABLE + " ("

  + KEY_ID + " integer primary key autoincrement, "

  + KEY_CONTENT1 + " text not null, "

  + KEY_CONTENT2 + " text not null);";

 

 private SQLiteHelper sqLiteHelper;

 private SQLiteDatabase sqLiteDatabase;



 private Context context;

 

 public SQLiteAdapter(Context c){

  context = c;

 }

 

 public SQLiteAdapter openToRead() throws android.database.SQLException {

  sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION);

  sqLiteDatabase = sqLiteHelper.getReadableDatabase();

  return this; 

 }

 

 public SQLiteAdapter openToWrite() throws android.database.SQLException {

  sqLiteHelper = new SQLiteHelper(context, MYDATABASE_NAME, null, MYDATABASE_VERSION);

  sqLiteDatabase = sqLiteHelper.getWritableDatabase();

  return this; 

 }

 

 public void close(){

  sqLiteHelper.close();

 }

 

 public long insert(String content1, String content2){

  

  ContentValues contentValues = new ContentValues();

  contentValues.put(KEY_CONTENT1, content1);

  contentValues.put(KEY_CONTENT2, content2);

  return sqLiteDatabase.insert(MYDATABASE_TABLE, null, contentValues);

 }

 

 public int deleteAll(){

  return sqLiteDatabase.delete(MYDATABASE_TABLE, null, null);

 }

 

 public Cursor queueAll(){

  String[] columns = new String[]{KEY_ID, KEY_CONTENT1, KEY_CONTENT2};

  Cursor cursor = sqLiteDatabase.query(MYDATABASE_TABLE, columns,

    null, null, null, null, null);

  

  return cursor;

 }

 

 public class SQLiteHelper extends SQLiteOpenHelper {



  public SQLiteHelper(Context context, String name,

    CursorFactory factory, int version) {

   super(context, name, factory, version);

  }



  @Override

  public void onCreate(SQLiteDatabase db) {

   // TODO Auto-generated method stub

   db.execSQL(SCRIPT_CREATE_DATABASE);

  }



  @Override

  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

   // TODO Auto-generated method stub

  }

 } 

}


AndroidSQLite.java
package com.exercise.AndroidSQLite;



import android.app.Activity;

import android.database.Cursor;

import android.os.Bundle;

import android.view.View;

import android.widget.Button;

import android.widget.EditText;

import android.widget.ListView;

import android.widget.SimpleCursorAdapter;



public class AndroidSQLite extends Activity {

 

 EditText inputContent1, inputContent2;

 Button buttonAdd, buttonDeleteAll;

 

 private SQLiteAdapter mySQLiteAdapter;

 ListView listContent;

 

 SimpleCursorAdapter cursorAdapter;

 Cursor cursor;

 

   /** Called when the activity is first created. */

   @Override

   public void onCreate(Bundle savedInstanceState) {

       super.onCreate(savedInstanceState);

       setContentView(R.layout.main);

      

       inputContent1 = (EditText)findViewById(R.id.content1);

       inputContent2 = (EditText)findViewById(R.id.content2);

       buttonAdd = (Button)findViewById(R.id.add);

       buttonDeleteAll = (Button)findViewById(R.id.deleteall);

      

       listContent = (ListView)findViewById(R.id.contentlist);



       mySQLiteAdapter = new SQLiteAdapter(this);

       mySQLiteAdapter.openToWrite();



       cursor = mySQLiteAdapter.queueAll();

       String[] from = new String[]{SQLiteAdapter.KEY_ID, SQLiteAdapter.KEY_CONTENT1, SQLiteAdapter.KEY_CONTENT2};

       int[] to = new int[]{R.id.id, R.id.text1, R.id.text2};

       cursorAdapter =

        new SimpleCursorAdapter(this, R.layout.row, cursor, from, to);

       listContent.setAdapter(cursorAdapter);

      

       buttonAdd.setOnClickListener(buttonAddOnClickListener);

       buttonDeleteAll.setOnClickListener(buttonDeleteAllOnClickListener);

      

   }

  

   Button.OnClickListener buttonAddOnClickListener

   = new Button.OnClickListener(){



  @Override

  public void onClick(View arg0) {

   // TODO Auto-generated method stub

   String data1 = inputContent1.getText().toString();

   String data2 = inputContent2.getText().toString();

   mySQLiteAdapter.insert(data1, data2);

   updateList();

  }

    

   };

  

   Button.OnClickListener buttonDeleteAllOnClickListener

   = new Button.OnClickListener(){



  @Override

  public void onClick(View arg0) {

   // TODO Auto-generated method stub

   mySQLiteAdapter.deleteAll();

   updateList();

  }

    

   };



 @Override

 protected void onDestroy() {

  // TODO Auto-generated method stub

  super.onDestroy();

  mySQLiteAdapter.close();

 }







 private void updateList(){

  cursor.requery();

   }



}


download filesDownload the files.

Related article:
- Retrieve individual items in cursor

15 comments:

SunLikeStar said...

Hi,
I've trying this example for two days but not able t get it working.I am think I'm putting this code in the project incorrectly. Can you please send me the complete project code.

Erik said...

Download the project

Jonas said...

thanks a lot for the tutorial, it really helps.

I've modify the code by entering 3 data, but still there is error when I click the ADD button or when insert to database, Please help me to know the error guys :)

This is the following source of my application --> http://www.mediafire.com/#s3zj859d9b0p8

Krishna Bavandlapally said...

Thanks its working and most useful..

nothing0819 said...

How to delete each row by each button

Erik said...

hello nothing0819,

Please read Delete row in SQLite database.

sayeedarehman said...

thx u soooooo much .....keep helping us(beginner)

Anonymous said...

how can i add another table? then put the same data in the first table? into the second table?

Unknown said...

Thanks for good one ! But how can i show the list views in different activity(Xml)

Unknown said...

Please show me the manifest.xm;

Anonymous said...

Thanks for thr tutorial but how is row and main xml connected? I see you calling R.id.row in your main, i don;t see how they are connected

Erik said...

cursorAdapter = new SimpleCursorAdapter(this, R.layout.row, cursor, from, to);

karthi said...

i cant add more values in list view how to add

Unknown said...

please i must say that you just provided a nice program here, thanks for that. But i've been trying to implement the SELECT QUERY, to select a particular cell row, but it seems not to work, can you assist me with the right code statement for it, thanks.

Chithrarengasamy said...

Sir
i copy and paste ur code .& ( modify AndroidSQLite.java).& mainActivity.java....
.how it works class modify AndriodSqlite.java to mainActivity????