how to select data from database using SQLite database in android?

android database sqlite

14,150

Well, just read what's in the log. You are doing a select on client_table1 but the table does not exist. You first have to create the table.

Please read into this tutorial on how to use sqlite on Android by using the SQLiteOpenHelper class:

http://www.vogella.de/articles/AndroidSQLite/article.html

14,150

Author by

Furqi

My attitude is based on how you treat me!!

Updated on June 04, 2022

Comments

Furqi almost 2 years

I am new to Android development and would like to select data and pre-populate a SQLite database using .sq3 files from the Assets folder.But getting exception on its path. So,what should i do? here is my code.

package com.wiztech.wahab;

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

import android.content.ContentValues;
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 DataBaseManager extends SQLiteOpenHelper{

//The Android's default system path of your application database.
  private static String DB_PATH = "/data/data/com.wiztech.wahab/databases/";


// private static String DB_NAME = "BlueConnect.sq3";
 private static String DB_NAME = "database.sq3";



private SQLiteDatabase myDataBase; 
private SQLiteDatabase myData; 

private final Context myContext;

public DataBaseManager(Context context) {
    super(context, DB_NAME, null, 1);
    this.myContext = context;
}

 /**
 * Creates a empty database on the system and rewrites it with your own database.
 * */
public void createDataBase() throws IOException{

    boolean dbExist = checkDataBase();
    if(dbExist){
        //do nothing - database already exist
    }else{  
        CopyFiles();
    }
}


private void CopyFiles()
{
    try
    { 
       InputStream is = myContext.getAssets().open(DB_NAME); 
       File outfile = new File(DB_PATH,DB_NAME);
       outfile.getParentFile().mkdirs();
       outfile.createNewFile();

      if (is == null)
      throw new RuntimeException("stream is null");
      else
      {
         FileOutputStream out = new FileOutputStream(outfile);      
      // BufferedOutputStream out = new BufferedOutputStream( new       FileOutputStream(outfile));
          byte buf[] = new byte[128];
            do {
          int numread = is.read(buf);
                if (numread <= 0)
                    break;
          out.write(buf, 0, numread);
           } while (true);

            is.close();
            out.close();
      }
       //AssetFileDescriptor af = am.openFd("world_treasure_hunter_deluxe.apk");
    }
    catch (IOException e)
    {
          throw new RuntimeException(e); 
    }

}    

/**
 * Check if the database already exist to avoid re-copying the file each time you open the application.
 * @return true if it exists, false if it doesn't
 */
private boolean checkDataBase(){

    SQLiteDatabase checkDB = null;

    try{
        String myPath = DB_PATH + DB_NAME;
        checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);

    }catch(SQLiteException e){

    }

    if(checkDB != null){
        checkDB.close();
    }

    return checkDB != null ? true : false;
}

/**
 * Copies your database from your local assets-folder to the just created empty database in the
 * system folder, from where it can be accessed and handled.
 * This is done by transfering bytestream.
 * */
@SuppressWarnings("unused")
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 bytes from the inputfile to the 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);

}

@Override
public synchronized void close() {

        if(myDataBase != null)
            myDataBase.close();

        super.close();

}

@Override
public void onCreate(SQLiteDatabase db) {

}

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

}


//---retrieve records---
public Cursor selectQuery(String query) throws SQLException 
{
    String myPath = DB_PATH + DB_NAME;
    //myData = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
    myData =  SQLiteDatabase.openDatabase(myPath,null, SQLiteDatabase.CREATE_IF_NECESSARY); 
    Cursor mCursor =myData.rawQuery(query, null);
    mCursor.moveToFirst();      
    myData.close();
    return mCursor;
}


////////// For Insert And Update Data ////////
public void insert_update(String query) throws SQLException 
{
    String myPath = DB_PATH + DB_NAME;
    myData = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
    myData.execSQL(query);
    myData.close();        
}

public boolean UpdateVote_Individual(String rowid,String value,String count) {
    String myPath = DB_PATH + DB_NAME;
    myData = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);

      ContentValues args = new ContentValues();
      args.put("total_votes", count);
      return myData.update("promotors", args, 
              "promoter_id='" + rowid+"'", null) > 0;           
  }


}






package com.wiztech.wahab;

import android.app.Activity;
import android.content.Intent;
import android.database.Cursor;
import android.graphics.Color;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.TextView;

public class Criteria_Activity extends Activity {

DataBaseManager db;
Cursor cur;

public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    System.out.println("reached inside Criteria_Activity");
    setContentView(R.layout.criteria);

    db = new DataBaseManager(getBaseContext());
    cur = db.selectQuery("select * from  client_table1;");

    if(cur.moveToFirst()) {

        do {

            String age = cur.getString(1);
            String gender = cur.getString(2);
            String prof = cur.getString(3);
            String hobby = cur.getString(4);
            String likes = cur.getString(5);
            String dislikes = cur.getString(6);

            TextView ag = (TextView) findViewById(R.id.age_value);
            if(age.length()==0||age==""){
                age="Not Saved";
                ag.setTextSize(16);
                ag.setTextColor(Color.GRAY);
            }
            ag.setText(age);

            TextView gend = (TextView) findViewById(R.id.gender_value);
            if(gender.length()==0||gender==""){
                gender="Not Saved";
                gend.setTextSize(16);
                gend.setTextColor(Color.GRAY);
            }
            gend.setText(gender);

            TextView pr = (TextView) findViewById(R.id.prof_value);
            if(prof.length()==0||prof==""){
                prof="Not Saved";
                pr.setTextSize(16);
                pr.setTextColor(Color.GRAY);
            }
            pr.setText(prof);

            TextView hobbie = (TextView) findViewById(R.id.hobby_value);
            if(hobby.length()==0||hobby==""){
                hobby="Not Saved";
                hobbie.setTextSize(16);
                hobbie.setTextColor(Color.GRAY);
            }
            hobbie.setText(hobby);

            TextView lykes = (TextView) findViewById(R.id.likes_value);
            if(likes.length()==0||likes==""){
                likes="Not Saved";
                lykes.setTextSize(16);
                lykes.setTextColor(Color.GRAY);
            }
            lykes.setText(likes);

            TextView dislykes = (TextView) findViewById(R.id.dislikes_value);
            if(dislikes.length()==0|| dislikes==""){
                dislikes="Not Saved";
                dislykes.setTextSize(16);
                dislykes.setTextColor(Color.GRAY);
            }
            dislykes.setText(dislikes);

        }while(cur.moveToNext());
        cur.close();
    }//end if(cur.moveToFirst..........

    Button criteria_edit_btn = (Button) findViewById(R.id.cr_edit_btn);

    criteria_edit_btn.setOnClickListener(new OnClickListener(){
        public void onClick(View v) {
            // TODO Auto-generated method stub
            Intent update_criteria_intent = new Intent(getParent(),    Edit_Criteria_Activity.class);
            TabGroupActivity parentActivity = (TabGroupActivity)getParent();
            parentActivity.startChildActivity("Edit_Criteria_Activity", update_criteria_intent);
        }           
    });

}// end onCreate

 }//end this.Activity