Getting a column from .sqlite containing multiple tables with multiple columns

Question

!Database consists of multiple tables and multiple columns as shown & i have pasted this law.sqlite in assets folder.

Database consists of multiple tables and multiple columns as shown & i have pasted this law.sqlite in assets folder.

Suppose i want to access all the elements of column AS_name as shown . So how should i code for it?

Answer 1

I tried and now the problem is solved :

For anyone facing similar type of problem can try my implementation :

Step 1:

Make a GetterSetter class (named GS here) & generate the Getter-Setters of variables used.

Like in my case:

public class GS {
     String AS_name;

public String getAS_name() {
return AS_name;
}

public void setAS_name(String aS_name) {
AS_name = aS_name;
}
}

Step 2:

Make a DBAdapter class which extends SQLiteOpenHelper & in that assign the your name of the file with extension .sqlite !

Rest you need only to copy my DBAdapter.java code & take care to implement the method getData() in which the data from database is fetched !

public class DBAdapter extends SQLiteOpenHelper
{
CustomAdapter adapter;
static String name = "law.sqlite"; //--Replace it with ur sqlite name 
static String path = "";
static ArrayList<GS> gs;
static SQLiteDatabase sdb;

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

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
    // TODO Auto-generated method stub
} 

private DBAdapter(Context v) 
{
    super(v, name, null, 1);
    path = "/data/data/" + v.getApplicationContext().getPackageName() + "/databases";
}

public boolean checkDatabase()
{
    SQLiteDatabase db = null;
    try 
    {
        db = SQLiteDatabase.openDatabase(path + "/" + name, null, SQLiteDatabase.OPEN_READONLY);
    } catch (Exception e) 
    {
        e.printStackTrace();
    }
    if (db == null) 
    {
        return false;
    } 
    else
    {
        db.close();
        return true;
    }
}

public static synchronized DBAdapter getDBAdapter(Context v)
{
    return (new DBAdapter(v));
}

public void createDatabase(Context v) 
{
    this.getReadableDatabase();
    try
    {
        InputStream myInput = v.getAssets().open(name);
        // Path to the just created empty db
    String outFileName = path +"/"+ 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();
    } catch (IOException e) 
    {
        System.out.println(e);
    }
}

public void openDatabase() 
{
    try 
    {
        sdb = SQLiteDatabase.openDatabase(path + "/" + name, null,
                SQLiteDatabase.OPEN_READWRITE);
    } catch (Exception e) 
    {
        System.out.println(e);
    }
}

public ArrayList<GS> getData() 
{
    try{
    Cursor c1 = sdb.rawQuery("SELECT DISTINCT * FROM Articles", null);
    gs = new ArrayList<GS>();
    while (c1.moveToNext())
    {
        GS q1 = new GS();

        q1.setAS_name(c1.getString(3)); //--- here 3 represents column no.
        Log.v("AS_name",q1.AS_name+""); 
        gs.add(q1);

    }
    }
    catch (Exception e) {
        e.printStackTrace();
    }
    return gs;
}
}

Step 3:

The class MainActivity.java :

public class MainActivity extends Activity {

ArrayList<GS> q = new ArrayList<GS>();
CustomAdapter adapter;
ListView lv;
@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);
       // Get ListView object from xml
     lv = (ListView) findViewById(R.id.listView1);

    DBAdapter db = DBAdapter.getDBAdapter(getApplicationContext());
    if (!db.checkDatabase()) 
    {
        db.createDatabase(getApplicationContext());
    }
    db.openDatabase();

    q = db.getData();
    for(int i=0;i<q.size();i++)
    {
        Log.i("outside",""+q.get(i).getAS_name());
    }
    lv = (ListView) findViewById(R.id.listView1);
    lv.setAdapter(new CustomAdapter(MainActivity.this,q));
    //lv.setAdapter(adapter);

}

class CustomAdapter extends ArrayAdapter<GS>
  {
       ArrayList<GS> list;
       LayoutInflater mInfalter;    
       public CustomAdapter(Context context, ArrayList<GS> list)
       {
           super(context,R.layout.customlayout,list);
          this.list= list;  
          mInfalter = LayoutInflater.from(context);
          for(int i=0;i<list.size();i++)
         {
             Log.i("................",""+list.get(i).getAS_name());
         }

       }
//         public int getCount(){
//              return list.size();
//          }
       @Override
        public View getView(int position, View convertView, ViewGroup parent) {
          ViewHolder holder;
          Log.i("..........","Hello in getView");
          if(convertView==null)
          {
               convertView = mInfalter.inflate(R.layout.customlayout,parent,false);//--customlayout.xml must have a textView 
               holder = new ViewHolder();
               holder.tv1 = (TextView)convertView.findViewById(R.id.textView1); 
               convertView.setTag(holder); 
          }else{
                holder = (ViewHolder)convertView.getTag();
          } 

                holder.tv1.setText(list.get(position).getAS_name());
          return convertView;
    }

  }
 static class ViewHolder
    {
        TextView tv1;
    }  
}

Run this code & finally the list in the listview will be displayed ! :)