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 ! :)

Answer 2

Cursor c = db.query(
                            TABLE_NAME,  // The table to query
                            projection,                               // The columns to return
                            null,                                // The columns for the WHERE clause
                            null,                            // The values for the WHERE clause
                            null,                                     // don't group the rows
                            null,                                     // don't filter by row groups
                            sortOrder                                 // The sort order
                            );

Specify the table name in TABLE_NAME field. Then access the elements using

c.moveToFirst();
for(int i=0;i<c.getCount();i++)
{
 //access elements of column here
 long itemId = c.getLong(c.getColumnIndexOrThrow(Column_Name)); //Example with long, corresponding function for string etc also exists.
 c.moveToNext();
}

c.close();

Specify the column name in Column_name.

NOTE: If this column is in multiple tables, just put both these code snippets in a loop and iterate through it. May be you can store the table names in an arraylist and access each table in each iteration of the outermost loop. Hope this helps you!!!

Answer 3

Try this:

Cursor cursor = db.rawQuery("SELECT DISTINCT AS_name FROM Articles",null);
// If you want in order then  add "ORDER BY AS_name AESC" in sql query.

cursor.moveToFirst();

 while(cursor.moveToNext()) {
// do Something
}