Working with SQLite Database in ANDROID.

By | February 14, 2011

Below is a straight forward example of how to deal with SQLite database in ANDROID.
Go ahead and copy and paste the following code to your java file.
The example has one database and performs functions like insert delete and listing of values in a textView.

package com.database;
import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.LinearLayout;
import android.widget.ScrollView;
import android.widget.TableLayout;
import android.widget.TableRow;
import android.widget.TextView;
import android.widget.Toast;

public class databaseactivity extends Activity {
    /** Called when the activity is first created. */

    private static String DBNAME = "mydb.db";
    private static String TABLE = "myTable";

    Button butAdd,butdel;
    EditText nam,mail;
    LinearLayout lay;
    TableLayout layout;
    TextView tev,tev1;
    int f=0;
    Integer ind;
   ScrollView sv;
    public void onCreate(Bundle savedInstanceState)
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        tev=(TextView)findViewById(R.id.tv);
        tev1=(TextView)findViewById(R.id.tv1);
        layout=(TableLayout)findViewById(R.id.layout);
        lay = (LinearLayout) findViewById(R.id.linear);
        butAdd = (Button) findViewById(R.id.add);
        nam=(EditText)findViewById(R.id.names);
        mail=(EditText)findViewById(R.id.emails);

       SQLiteDatabase mydb;
       mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
       mydb.execSQL("create table if not exists "+TABLE+" (_id integer primary key autoincrement,name text not null,email text not null);");
       mydb.close();
       sv = new ScrollView(this);
       setContentView(sv);
       sv.addView(lay);

       butAdd.setOnClickListener(new View.OnClickListener()
       {
      @Override
      public void onClick(View v)
      {
                  f=0;
                  addTask();
      }
      });
    }

    public void addTask()
    {
                SQLiteDatabase mydb;
                mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
                ContentValues newrow = new ContentValues();
                String a=nam.getText().toString();
                String b=mail.getText().toString();
                if(a.trim().length()!=0 && b.trim().length()!=0)
                {
                                newrow.put("name", nam.getText().toString());
                                newrow.put("email", mail.getText().toString());
                                mydb.insert(TABLE, null, newrow);
                }
                else
                {
                                if(f==0)
                                      Toast.makeText(getApplicationContext(), "Fields cannot be left blank",
                                                                                                               Toast.LENGTH_SHORT).show();
                }
                nam.setText(null);
                mail.setText(null);
                String[] result = new String[]{"_id","name","email"};
                Cursor allrows  = mydb.query(TABLE, result, null, null, null, null, null);
                Integer cindex = allrows.getColumnIndex("name");
                Integer cindex1 = allrows.getColumnIndex("email");
                Integer cindex2 = allrows.getColumnIndex("_id");
                if(allrows.moveToFirst())
                {
                layout.removeAllViews();
                do
                {
                                TableRow row= new TableRow(this);
                                final TextView tv = new TextView(this);
                                final TextView tv1 = new TextView(this);
                                final Button but=new Button(this);
                                but.setText("Delete");
                                final Button butt=new Button(this);
                                butt.setText("Edit");                                ind=Integer.parseInt(allrows.getString(cindex2)) ;
                                but.setId(ind);
                                butt.setId(ind);
                                nam.setId(ind);
                                but.setOnClickListener(new View.OnClickListener() {
                                                public void onClick(View v)
                                                {
                                                                f=1;
                                                                int i=but.getId();
                                                                SQLiteDatabase mydb;
                                                                mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
                                                                mydb.delete(TABLE,"_id="+i, null);
                                                                mydb.close();
                                                                Toast.makeText(getApplicationContext(), "Deleted", Toast.LENGTH_SHORT).show();
                                                                addTask();
                                                }
                                });


                                butt.setOnClickListener(new View.OnClickListener() {
                                                public void onClick(View v)
                                                {
                                                               butt.setText("Save");
                                                               nam.setText(tv.getText());
                                                               mail.setText(tv1.getText());
                                                               butt.setOnClickListener(new View.OnClickListener() {
                                                               public void onClick(View v)   {
                                                                             f=1;
                                                                             int j=but.getId();
                                                                             SQLiteDatabase mydb;
                                                                             mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE,null);
;
                                                                             ContentValues newrow1 = new ContentValues();
                                                                             String a=nam.getText().toString();
                                                                             String b=mail.getText().toString();
                                                                             if(a.trim().length()!=0&& b.trim().length()!=0)                                                                                {                                                                                                newrow1.put("name", nam.getText().toString());
                                                                                                  newrow1.put("email", mail.getText().toString());
                                                                                                  mydb.update(TABLE,newrow1, "_id="+j, null);
                                       
                                                                                                  Toast.makeText(getApplicationContext(), "Updated",
                                                                                                                            Toast.LENGTH_SHORT).show();
                                                                                                  nam.setText(null);
                                                                                                  mail.setText(null);
                                                                                                  butt.setText("Edit");
                                                                                                  addTask();
                                                                               }    else                                                                                {
                                                                                         //if(f==0)
                                                                                           Toast.makeText(getApplicationContext(), "Fields cannot be left blank",
                                                                                                                                 Toast.LENGTH_SHORT).show();
                                                                         }                                                                                mydb.close();
                                                                    }
                                                            });
                                                }
                                });
                                tv.setText(allrows.getString(cindex));
                                row.addView(tv);
                                tv1.setText(allrows.getString(cindex1));
                                row.addView(tv1);
                                row.addView(butt);
                                row.addView(but);
                                layout.addView(row);
                }while(allrows.moveToNext());
                }
                else
                {
                                layout.removeAllViews();
                                Toast.makeText(getApplicationContext(), "Table is empty", Toast.LENGTH_SHORT).show();
                }
                mydb.close();
    }
}

In the XML file set up a TableLayout with android:stretchColumns=”0,1″
and other buttons also and proceed.

Please leave your valuable comments.

7 thoughts on “Working with SQLite Database in ANDROID.

  1. Phyll

    Hey, I’m having a little trouble trying to figure out what should be in the xml for this example, the order and such, probably makes a difference. Right now it tells me the specified child already has a parent and I must remove it. It just doesn’t tell what the specified child is. Anybody get this working?

    Reply
    1. James

      Paste your error in here and we will try to solve it. This is a perfectly working code and no one has ever complained it. Please double check your code.

      Reply
  2. Phyll

    I got an xml file that works after some trial and error. Perhaps this will save everybody some work. It’s kind of a neat example. Here’s the file:

    Hope this helps

    Phyll

    Reply
  3. Phyll

    Looks like my last comment lost the code. Kind of steep learning curve on this site. How about a link. Get the file here:

    betterpaving.com/Relay/main.xml

    It really does work.

    Phyll

    Reply
  4. Pingback: How to save score in Android Cocos2D? | Coderz Heaven

  5. Pingback: Working with SQLite databases through command Line in android … | Programmer Solution

Leave a Reply

Your email address will not be published. Required fields are marked *