Getting A Column From .sqlite Containing Multiple Tables With Multiple Columns

!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

Solution 1:

Try this:

Cursorcursor= 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
}

Solution 2:

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:

publicclassGS {
     String AS_name;

publicStringgetAS_name() {
return AS_name;
}

publicvoidsetAS_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 !

publicclassDBAdapterextendsSQLiteOpenHelper
{
CustomAdapter adapter;
staticStringname="law.sqlite"; //--Replace it with ur sqlite name staticStringpath="";
static ArrayList<GS> gs;
static SQLiteDatabase sdb;

@OverridepublicvoidonCreate(SQLiteDatabase db)
{
    // TODO Auto-generated method stub
}

@OverridepublicvoidonUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
{
    // TODO Auto-generated method stub
} 

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

publicbooleancheckDatabase()
{
    SQLiteDatabasedb=null;
    try 
    {
        db = SQLiteDatabase.openDatabase(path + "/" + name, null, SQLiteDatabase.OPEN_READONLY);
    } catch (Exception e) 
    {
        e.printStackTrace();
    }
    if (db == null) 
    {
        returnfalse;
    } 
    else
    {
        db.close();
        returntrue;
    }
}

publicstaticsynchronized DBAdapter getDBAdapter(Context v)
{
    return (newDBAdapter(v));
}

publicvoidcreateDatabase(Context v) 
{
    this.getReadableDatabase();
    try
    {
        InputStreammyInput= v.getAssets().open(name);
        // Path to the just created empty dbStringoutFileName= path +"/"+ name;
        // Open the empty db as the output streamOutputStreammyOutput=newFileOutputStream(outFileName);
        // transfer bytes from the inputfile to the outputfilebyte[] buffer = newbyte[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);
    }
}

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

public ArrayList<GS> getData() 
{
    try{
    Cursorc1= sdb.rawQuery("SELECT DISTINCT * FROM Articles", null);
    gs = newArrayList<GS>();
    while (c1.moveToNext())
    {
        GSq1=newGS();

        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 :

publicclassMainActivityextendsActivity {

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

    DBAdapterdb= 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(newCustomAdapter(MainActivity.this,q));
    //lv.setAdapter(adapter);

}

classCustomAdapterextendsArrayAdapter<GS>
  {
       ArrayList<GS> list;
       LayoutInflater mInfalter;    
       publicCustomAdapter(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();//          }@Overridepublic 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 = newViewHolder();
               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;
    }

  }
 staticclassViewHolder
    {
        TextView tv1;
    }  
}

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

Solution 3:

Cursorc= db.query(
                            TABLE_NAME,  // The table to query
                            projection,                               // The columns to returnnull,                                // The columns for the WHERE clausenull,                            // The values for the WHERE clausenull,                                     // don't group the rowsnull,                                     // 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 herelong 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!!!