StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POMulit level ListView pulling from database
text
Body
copied!<p>I'm working on my first android application and running into an issue I can't seem to find a solution for. I'm trying to setup my UI with a series of listviews that will at the end connect to a page displaying edit text that will require input. IE Categories -> Sources -> Titles -> Edittexts our in other words, they would first be displayed a list of categories (Music, Movies etc). Then the user clicks on lets say Music, it would then list artists in my database. Followed by clicking on an artist it would display their songs in my database. I have it setup atm where it will display my cursors I've setup but I'm having trouble setting up a my program to see what the user clicked and give results/display cursor that has the data I want to show. </p> <pre><code>public class dbadapter extends SQLiteOpenHelper { //The Android's default system path of your application database. public static final String DB_PATH = "/data/data/wanted.pro.madlibs/databases/"; public static final String DB_NAME = "madlib"; public static final int DB_VERSION = 1; private static final String TAG = "dbadapter"; //database variables public static final String KEY_ID = "_id"; public static final String KEY_CATEGORYDESC = "categorydesc"; public static final String KEY_TITLE = "titlekey"; public static final String KEY_TITLEDESC = "titledesc"; public static final String KEY_TITLESTORY = "titlestory"; public static final String KEY_SOURCEDESC = "sourcedesc"; public static final String KEY_SOURCE = "sourcekey"; public static final String KEY_CATEGORY = "categorykey"; //table variables public static final String CATEGORY_TABLE = "category"; public static final String SOURCE_TABLE = "source"; public static final String TITLE_TABLE = "title"; private dbadapter mydbhelper; private static SQLiteDatabase myDataBase; @Override public void onOpen(SQLiteDatabase myDatabase) { super.onOpen(myDatabase); if (!myDatabase.isReadOnly()) { // Enable foreign key constraints myDatabase.execSQL("PRAGMA foreign_keys=ON;"); } } /** * Constructor * Takes and keeps a reference of the passed context in order to access to the application assets and resources. * @param context */ private final Context mCtx; public dbadapter(Context context) { super(context, DB_NAME, null, DB_VERSION); this.mCtx = 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{ //By calling this method and empty database will be created into the default system path //of your application so we are gonna be able to overwrite that database with our database. this.getReadableDatabase(); try { copyDataBase(); } catch (IOException e) { throw new Error("Error copying database"); } } } /** * 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){ //database does't exist yet. } 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 transferring bytestream. * */ private void copyDataBase() throws IOException{ //Open your local db as the input stream InputStream myInput = mCtx.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) { Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data"); db.execSQL("DROP TABLE IF EXISTS category"); onCreate(db); } public dbadapter open() throws SQLException { mydbhelper = new dbadapter (mCtx); myDataBase = mydbhelper.getWritableDatabase(); return this; } // Add your public helper methods to access and get content from the database. // You could return cursors by doing "return myDataBase.query(....)" so it'd be easy // to you to create adapters for your views. // retrieves all the categories public static Cursor getAllCategories() { return myDataBase.query(CATEGORY_TABLE, new String[] { KEY_ID, KEY_CATEGORY, KEY_CATEGORYDESC, }, null, null, null, null, KEY_CATEGORYDESC); } // retrieves all the titles public Cursor getAllTitles() { return myDataBase.query(TITLE_TABLE, new String[] { KEY_ID, KEY_TITLE, KEY_TITLEDESC, KEY_TITLESTORY, }, null, null, null, null, KEY_TITLEDESC); } // retrieves all the sources public Cursor getAllSources() { return myDataBase.query(SOURCE_TABLE, new String[] { KEY_ID, KEY_SOURCE, KEY_SOURCEDESC, }, null, null, null, null, KEY_SOURCEDESC); } </code></pre> <p>Here is the code from my adapter</p> <pre><code>public class categories extends ListActivity { /** Called when the activity is first created. */ private dbadapter mydbhelper; @Override public void onCreate(Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.category_list); mydbhelper = new dbadapter(this); mydbhelper.open(); fillData(); } private void fillData() { Cursor c = mydbhelper.getAllCategories(); startManagingCursor(c); // Create an array to specify the fields we want to display in the list (TITLE,DATE,NUMBER) String[] from = new String[] {dbadapter.KEY_CATEGORYDESC}; // an array of the views that we want to bind those fields to (in this case text1,text2,text3) int[] to = new int[] {R.id.text1}; // Now create a simple cursor adapter and set it to display SimpleCursorAdapter adapter = new SimpleCursorAdapter(this, R.layout.cate_row, c, from, to); setListAdapter(adapter); } @Override protected void onListItemClick(ListView list, View v, int position, long id) { super.onListItemClick(list, v, position, id); final Intent intent = new Intent(this, source.class); startActivityForResult(intent, position); } } </code></pre> <p>this would be my sql log from where I made my database tables</p> <pre><code>CREATE TABLE category( _id INT, categorykey INT, categorydesc TEXT, PRIMARY KEY(_id) ); CREATE TABLE source( _id INT, sourcekey INT, sourcedesc TEXT, PRIMARY KEY(_id) ); CREATE TABLE title( _id INT, titlekey INT, titledesc TEXT, titlestory TEXT, PRIMARY KEY(_id) ); CREATE TABLE userword( _id INT, titlekey INT, userword TEXT, PRIMARY KEY(_id), FOREIGN KEY(titlekey) REFERENCES title(titlekey) ); CREATE TABLE category_title( titlekey INT, categorykey INT, FOREIGN KEY(titlekey) REFERENCES title(titlekey), FOREIGN KEY (categorykey) REFERENCES category(categorykey) ); CREATE TABLE source_title( titlekey INT, sourcekey INT, FOREIGN KEY(titlekey) REFERENCES title(titlekey), FOREIGN KEY (sourcekey) REFERENCES source(sourcekey) ); CREATE TABLE source_category( sourcekey INT, categorykey INT, FOREIGN KEY(categorykey) REFERENCES category(categorykey), FOREIGN KEY (sourcekey) REFERENCES source(sourcekey) ); </code></pre> <p>The only cursor I know I have right is the Category, since it's the only data in database table that will not be filtered by previous selected.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload