Saturday, 8 October 2011

android application to connect to your own sqlite database created externlly

Hello there  today i am going to show you how can you connect your own sqlite database from android application.This application is a simple quote application which read the quotes(jokes,proverbs etc) in databases and display them in textview if you press next button the next quote will appear and if you press back button previous quote will appear.
You can make your database with sqlite browser.In your database make two fields
1."_id" (number count 1,2,3...)primary integer key
2.quote(text)

First open eclipse go to FILE ->New->Android Project
Fill the form and choose your target sdk version.And give the Activity name as  "MyActivity"
click finish. Now in eclipse your activity should be open.
Now put your database in the "assets" folder of the project.

In the right side in eclipse click on your projct->scr-> and right click on  your package  name and make a new class name "DbH".
Now write the code as below in your DbH class


package YOUR PACKAGE NAME;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DbH extends SQLiteOpenHelper {
    private Context mycontext;

    private String DB_PATH = "/data/data/YOUR PACKAGE NAME/databases/";
     private static String DB_NAME = "YOUR DATABASE NAME";
    private static String DB_TABLE="YOUR DATABASE TABLE NAME";
   
    public SQLiteDatabase myDataBase;
   
    /*private String DB_PATH = "/data/data/"
                                + mycontext.getApplicationContext().getPackageName()
                                + "/databases/";
*/
   
    public DbH(Context context) throws IOException  {
        super(context,DB_NAME,null,1);
        this.mycontext=context;
       }

    public void createdatabase() throws IOException{
        boolean dbexist = checkdatabase();
        if(dbexist)
        {
            System.out.println(" Database exists.");
        }
        else{
            this.getReadableDatabase();
        try{
                copydatabase();
            }
            catch(IOException e){
                throw new Error("Error copying database");
            }
        }
    }
    private boolean checkdatabase() {
        //SQLiteDatabase checkdb = null;
        boolean checkdb = false;
        try{
            String myPath = DB_PATH + DB_NAME;
            File dbfile = new File(myPath);
            checkdb = SQLiteDatabase.openDatabase(myPath,null,SQLiteDatabase.OPEN_READWRITE) != null;
            checkdb = dbfile.exists();
        }
        catch(SQLiteException e){
            System.out.println("Database doesn't exist");
        }

        return checkdb;
    }
    private void copydatabase() throws IOException {

        //Open your local db as the input stream
        InputStream myinput = mycontext.getAssets().open(DB_NAME);

        // Path to the just created empty db
       String outfilename = DB_PATH + DB_NAME;

        //Open the empty db as the output stream
        OutputStream myoutput = new FileOutputStream(outfilename);

        // transfer byte to inputfile to outputfile
        byte[] buffer = new byte[1024];
        int length;
        while ((length = myinput.read(buffer))>0)
        {
            myoutput.write(buffer,0,length);
        }

        //Close the streams
        myoutput.flush();
        myoutput.close();
        myinput.close();

    }

    public void opendatabase() throws SQLException
    {
        //Open the database
        String mypath = DB_PATH + DB_NAME;
        myDataBase = SQLiteDatabase.openDatabase(mypath, null, SQLiteDatabase.OPEN_READONLY);

    }

    public synchronized void close(){
        if(myDataBase != null){
            myDataBase.close();
        }
        super.close();
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
// This will return a cursor containing database records
 public Cursor  data(){
   
     Cursor c;     
     c=myDataBase.query(DB_TABLE, null, null,null,null,null,null);
      return c;
      }

@Override
public void onCreate(SQLiteDatabase arg0) {
    // TODO Auto-generated method stub
   
}

}

Now open your Main Activity class "MyActivity"
and make this class looks like:







package YOUR PACAGE NAME;

import java.io.IOException;

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;

public class MyActivity extends Activity implements View.OnClickListener {
    /** Called when the activity is first createdl. */
     Cursor cur;
    TextView tv;
    DbH db;
    Button next,back;
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        tv=(TextView)findViewById(R.id.text);
        next=(Button)findViewById(R.id.next);
        back=(Button)findViewById(R.id.back);
        next.setOnClickListener(this);
     back.setOnClickListener(this);
     
        try {
            db=new DbH(this);
        } catch (IOException e2) {
           
            e2.printStackTrace();
        }
       
      
            try {
                db.createdatabase();
            } catch (IOException e) {
               
                e.printStackTrace();
            }

         db.opendatabase();
          cur=db.data();
           cur.moveToFirst();
   
          tv.setText(cur.getString(1));
      
     }
@Override
public void onClick(View v) {
   
    switch(v.getId())
    {
    case R.id.next :
        if(cur.isLast())
        {
            cur.moveToFirst();
            tv.setText(""+cur.getString(1));
        }
        else
        {
            cur.moveToNext();
        tv.setText(""+cur.getString(1));
       
        }
    break;
    case R.id.back:
    {
        if(cur.isFirst())
        {
            cur.moveToLast();
            tv.setText(""+cur.getString(1));
        }
        else {cur.moveToPrevious();
        tv.setText(""+cur.getString(1));
        }
        break;
    }
   
    }
}
}

Now your layout file :
I am  not making it colorful or setting the buttons look awesome. It is a simple layout which will just work fine.You can change the layout as you want 


<?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:id="@+id/text"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    android:textColor="#00ff00"
    android:textSize="20px"
    />
 
    <Button
   
     android:id="@+id/next"
     android:text="NEXT"
     android:layout_width="wrap_content"
     android:layout_height="wrap_content"
     android:onClick="true"
     />
      <Button
   
     android:id="@+id/back"
     android:text="BACK"
     android:layout_width="wrap_content"
     android:layout_height="wrap_content"
     android:onClick="true"
     />
 
</LinearLayout>

Now save all the open files and right click on project and run it.("don't forget to change package name and database name and table name.")
If you find any difficulty let me know and  Enjoy!!!  
   
  
   





8 comments:

  1. hello this is very grate example to beginners. now just i update in this example.i need to use image from db. how?
    i have use this code after tv.setText(cur.getString(1));
    my image field is 3 (blob).my code for image is
    tv.setText(cur.getString(1));
    byte[] bb = cur.getBlob(3);
    ByteArrayInputStream is = new ByteArrayInputStream(bb);
    Bitmap theImage = BitmapFactory.decodeStream(is);
    ImageView img = (ImageView)findViewById(R.id.imageView1);
    img.setImageBitmap(theImage);
    but it give "Force Close"
    give me some basic idea for that.

    ReplyDelete
  2. Really nice tutorial it help me a lot thanks for this please carry on

    ReplyDelete
  3. this one is showing error database doesnt exist.
    No table found

    ReplyDelete
  4. This tutorial is very helpful. What to do when we want to fetch multiple columns from db?

    ReplyDelete
  5. How to fetch blob type image using this example?

    ReplyDelete
  6. Hi Manish And Nidhi, To fetch from multiple column

    Cursor c = db.query(YOUR_TABEL_NAME, new String[] {YOUR_COLUMN_1,YOUR_COLUMN_2...and so on}, null, null,null,null);
    while(c.moveToNext()) {

    fetch your columns
    }

    ReplyDelete
  7. I know how to iterate using cursor.isFirst and cursor.moveToNext. How to iterate using cursor.moveToPosition(x)? I would really appreciate your reply.Thanks

    //Mydatabase.java

    public Cursor getData(int _id) {
    SQLiteDatabase db = this.getReadableDatabase();



    Cursor cursor =db.rawQuery("Select * from '"+DB_TABLE+"' where _id = ?", new String[] { String.valueOf(_id)});

    if (cursor != null)
    {
    cursor.moveToFirst();
    }

    return cursor;
    }


    //MyActivity.java

    public void onClick(View v) {
    cur=db.getData(position);
    int firstpos=1;
    int lastpos=4;

    switch(v.getId())
    {

    case R.id.next :
    {

    if (cur != null && cur.getCount()> 0 && position < cur.getCount() && position != cur.getCount()){
    cur.moveToPosition(position);
    textView1.setText(""+cur.getString(1));// Display Columns
    position++;
    cur.moveToNext();
    }
    if(cur.moveToPosition(lastpos))
    {

    cur.moveToPosition(firstpos);
    textView1.setText(""+cur.getString(1));
    }
    /*else
    {
    cur.moveToPosition(position);
    textView1.setText(""+cur.getString(1));
    position++;
    }*/


    //display details code

    }
    break;
    case R.id.random:
    {
    Random r = new Random();
    int rnum = r.nextInt(max - min + 1) + min;
    cur=db.getData(rnum);
    setNewData(rnum);
    }
    }


    }

    private void setNewData (int xyz) {



    }

    ReplyDelete