Search code examples
androidsqliteandroid-fragmentsassetssimplecursoradapter

How to display a SQLite database in a listview of a fragment class?

I am new in Android Studio. I have a SQLite database with three columns and I would to display all the data into a listview. The database is in the assets folder. I tried to do with a SimpleCursorAdapter in a fragment class but without success. The app crash when I launch it on my device. Can someone tell me why?

This is my fragment class:

public class rightFragment extends Fragment {

View view;
Cursor c;
SimpleCursorAdapter adapter;
ListView listView;
Button buttondisplay;

@Override
public View onCreateView(LayoutInflater inflater, ViewGroup container,
                         Bundle savedInstanceState) {

   view = inflater.inflate (R.layout.fragment_right, container, false);

     DatabaseHelper myDbHelper = new DatabaseHelper (getActivity ());
    try {
        myDbHelper.createDataBase ();
    } catch (IOException ioe) {

    }
    try {
        myDbHelper.openDataBase ();
    } catch (SQLException sqle) {
        throw sqle;
    }

    c = myDbHelper.query ("fradeu", null, null, null, null, null, null);


    Cursor c = myDbHelper.readData ();
    String[] from = new String []{DatabaseHelper.COL1,DatabaseHelper.COL2};
    int[] to = new int[] {R.id.fra, R.id.deu};

    adapter = new SimpleCursorAdapter (getActivity (), R.layout.list_view_adapter, c, from, to) {
    };

    buttondisplay.setOnClickListener (new View.OnClickListener () {
        @Override
        public void onClick(View v) {

            adapter.notifyDataSetChanged();
            listView.setAdapter(adapter);
        }
});
return view
}
}

DatabaseHelper:

public class DatabaseHelper extends SQLiteOpenHelper {

String DB_PATH = null;
private static String DB_NAME = "mydatabase3.db";
private SQLiteDatabase database;
private final Context myContext;
public static final String TABLE_NAME = "fradeu";
public static final String COL1 = "fra";
public static final String COL2 = "deu";

public DatabaseHelper(Context context) {
    super (context, DB_NAME, null, 10);
    this.myContext = context;
    this.DB_PATH = "/data/data/" + context.getPackageName () + "/" + "databases/";
    Log.e ("Path 1", DB_PATH);
}


public void createDataBase() throws IOException {
    boolean dbExist = checkDataBase ();
    if (dbExist) {
    } else {
        this.getReadableDatabase ();
        try {
            copyDataBase ();
        } catch (IOException e) {
            throw new Error ("Error copying database");
        }
    }
}

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;
}

private void copyDataBase() throws IOException {
    InputStream myInput = myContext.getAssets ().open (DB_NAME);
    String outFileName = DB_PATH + DB_NAME;
    OutputStream myOutput = new FileOutputStream (outFileName);
    byte[] buffer = new byte[10];
    int length;
    while ((length = myInput.read (buffer)) > 0) {
        myOutput.write (buffer, 0, length);
    }
    myOutput.flush ();
    myOutput.close ();
    myInput.close ();
}

public void openDataBase() throws SQLException {
    String myPath = DB_PATH + DB_NAME;
    database = SQLiteDatabase.openDatabase (myPath, null, SQLiteDatabase.OPEN_READONLY);
}

public Cursor readData(){
    String[] allColumns = new String[] {DatabaseHelper.COL1,DatabaseHelper.COL2};
    Cursor c =database.query (DatabaseHelper.TABLE_NAME,allColumns,null,null,null,null,null);
    if(c != null) {
        c.moveToFirst ();

    }
    return c;
}

@Override
public synchronized void close() {
    if (database != null)
        database.close ();
    super.close ();
}

@Override
public void onCreate(SQLiteDatabase db) {

    }

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (newVersion > oldVersion)
        try {
            copyDataBase ();
        } catch (IOException e) {
            e.printStackTrace ();
        }
}

public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {
    return database.query ("fradeu", null, null, null, null, null,null);

    }
}

The xml file used for the SimpleCursorAdapter:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    android:orientation="horizontal" >
    <TextView
        android:id="@+id/fra"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="fra"
        android:textAppearance="?android:attr/textAppearanceLarge" />
    <TextView
        android:id="@+id/deu"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginLeft="10dp"
        android:text="deu"
        android:textAppearance="?android:attr/textAppearanceMedium" />
</LinearLayout>
Solution

  • There are numerous potential issues, Here's a bit of a check list to start with :-

    1. Does file mydatabase2.db exist in the ????/App/Main/assets folder?
    2. Is it a valid SQLite Database (open it in a SQlite Tool and check).
    3. Table fradeu should have a column named _id which should be an alias of the rowid (e.g. it is defined as _id INTEGER PRIMARY KEY or _id INTEGER PRIMARY AUTOINCREMENT, the latter not recommended due to unnecessary overheads)
      • Note refer to code that sets allColumns ( and how you can get around not having _id column).

    The Fix

    To overcome issue the code was changed quite extensively, including code to catch potential issues, so it's a bit bloated and rather than propogating exceptions requiring try/catch clauses at the higher levels they are tested for and captured at the lower level (for my convenience).

    The following is from the working code (as below) this uses the following as mydatabase3.db :-

    enter image description here

    Which is was copied to the assets folder.

    The resultant app initially looking like :-

    enter image description here

    And then :-

    enter image description here

    The Code

    • Note see comments for fixes recommendations and reasons for changes

    DatabaseHelper.java :-

    public class DatabaseHelper extends SQLiteOpenHelper {

    private String DB_PATH = null;
    private static String DB_NAME = "mydatabase3.db";
    private SQLiteDatabase database;
    private final Context myContext;
    public static final String TABLE_NAME = "fradeu";
    public static final String COL1 = "fra";
    public static final String COL2 = "deu";

    public DatabaseHelper(Context context) {
        super (context, DB_NAME, null, 10);
        this.myContext = context;
        this.DB_PATH = "/data/data/" + context.getPackageName () + "/" + "databases/";
        String altdbpath = (context.getDatabasePath("anything")).getParent() + "/"; //<<<< gets path without hardcoding recommended
        //<<<< Added for info re paths
        Log.d("DBPATHINFO","Hardcoded DB path is >>>>" + DB_PATH + "<<<<");
        Log.d("DBPATHINFO","Derived   DB path is >>>>" + altdbpath + "<<<<");
        Log.e ("Path 1", DB_PATH);
    }

    public void createDataBase() {
        boolean dbExist = checkDataBase ();
        if (!dbExist) {
            this.getReadableDatabase ();
            copyDataBase();
        }
    }

    // Note can check file which won't issue stacktrace which may be confusing
    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;
    }


    private void copyDataBase() {

        InputStream myInput;
        OutputStream myOutput;

        final String TAG = "COPYDATABASE";
        Log.d(TAG,"Attempt to copy database initiated.");
        Log.d(TAG,"Attempting to open Asset " + DB_NAME);
        try {
            myInput = myContext.getAssets().open(DB_NAME);
        } catch (IOException e) {
            Log.d(TAG,"Error attempting to open Asset " +DB_NAME);
            throw new RuntimeException(e);
        }
        //InputStream myInput = myContext.getAssets ().open (DB_NAME);
        String outFileName = DB_PATH + DB_NAME;
        Log.d(TAG,"Attempting to open the Database file :- " + outFileName);
        try {
            myOutput = new FileOutputStream(outFileName);
        } catch (IOException e) {
            Log.d(TAG,"Error attempting to open the Database file :-" + outFileName);
            throw new RuntimeException(e);
        }
        byte[] buffer = new byte[4096];
        long bytescopied = 0;
        int length;
        Log.d(TAG,"Attempting to copy from the asset file to the Database file");
        try {
            while ((length = myInput.read(buffer)) > 0) {
                myOutput.write(buffer, 0, length);
                bytescopied = bytescopied + length;
            }
        } catch (IOException e) {
            Log.d(TAG,"Error while copying from the asset file to the Database file - " +
                    String.valueOf(bytescopied) +
                    " bytes copied, so far.");
        }
        Log.d(TAG,"File has been copied from the assets file to the Database file." +
                String.valueOf(bytescopied) +
                " bytes were copied."
        );
        Log.d(TAG, "Attempting to flush and close files.");
        try {
            myOutput.flush();
            myOutput.close();
            myInput.close();
        } catch (IOException e) {
            Log.d(TAG,"Error flusing or closing files.");
        }
    }

    public void openDataBase() {
        String myPath = DB_PATH + DB_NAME;
        database = SQLiteDatabase.openDatabase (myPath, null, SQLiteDatabase.OPEN_READONLY);
    }

    public Cursor readData(){


        // String[] allColumns = new String[] {DatabaseHelper.COL1,DatabaseHelper.COL2};
        //<<<<< for a CursorAdapater _id column MUST be included
        // Assuming you have an _id column then  allColumns replaced with null (all columns)

        // Alternaelty if the table doesn have _id column then you could use :-
        //String[] allColumns = new String[] {DatabaseHelper.COL1,DatabaseHelper.COL2,"rowid AS _id"};
        Cursor c =database.query (DatabaseHelper.TABLE_NAME,null,null,null,null,null,null);
        //<<<< useless code cursor WILL NOT be null
        //      (very rarely would a cursor be null and
        //      probably only if trapped by try/catch
        //          which will tend to obfuscate matters/issues
        //      )
        /*
        if(c != null) {
            c.moveToFirst ();
        }
        */
        return c;
    }

    @Override
    public synchronized void close() {
        if (database != null)
            database.close ();
        super.close ();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (newVersion > oldVersion)
            copyDataBase();
    }

    public Cursor query(String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {
        return database.query ("fradeu", null, null, null, null, null,null);

    }
}

    The onCreateView method of the fragment :-

        public View onCreateView(LayoutInflater inflater, ViewGroup container,
                             Bundle savedInstanceState) {
        //<<<< Obviously change to use your layout (this is stock layout)
        View rootView = inflater.inflate(R.layout.fragment_so50804849, container, false);
        listView = (ListView) rootView.findViewById(R.id.list); //<<<< ADDED NOTE use your id
        buttondisplay = (Button) rootView.findViewById(R.id.showlist); //<<<< ADDED NOTE use your id

        DatabaseHelper myDbHelper = new DatabaseHelper (getActivity ());
        myDbHelper.createDataBase();
        myDbHelper.openDataBase();

        /*
        c = myDbHelper.query(DatabaseHelper.TABLE_NAME,null,null,null,null,null,null);
        Cursor c = myDbHelper.readData(); //???? two cursors called c c will now refer to this one
        */
        c = myDbHelper.readData(); //<<<< Just the one cursor

        String[] from = new String []{DatabaseHelper.COL1,DatabaseHelper.COL2};
        int[] to = new int[] {R.id.fra, R.id.deu};
        adapter = new SimpleCursorAdapter(getActivity(),R.layout.list_view_adapter,c,from,to,0);
        //listView.setAdapter(adapter);

        //<<<< No need for a button see commented out line above where setAdapter is used this works
        buttondisplay.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {
                if (buttondisplay.getText().toString().contains("SHOW")) {
                    listView.setAdapter(adapter);
                    buttondisplay.setText("HIDE LIST");
                } else {
                    listView.setAdapter(null);
                    buttondisplay.setText("SHOW LIST");
                }
            }
        });
        return rootView;
    }