3

This is the Adapter Class I am using to Insert or Get Data from the SQLite Database. There is a method for inserting the data and a method for getting the data.

In the getData method, I return a cursor and handle it in some other class but the problem I face is difficult to understand from my perspective.

When I get the data using the query function of SqliteDatabase it returns me a Cursor so, in other words, the cursor has a subset of the some table inside the database. The work of Database Object is over so I close it and then return the Cursor. The function works without any problem but the cursor returned on the other side is empty.

Log displayed:

java.lang.IllegalStateException: Cannot perform this operation because the connection pool has been closed.

If I remove the line:

db.close();

then everything works fine, but some point in time it says leak in the database if I call getData() function more number of times.

public class DatabaseAdapter { DatabaseHelper helper; public DatabaseAdapter (Context context) { helper = new DatabaseHelper(context); } public long insertData(String username, String domain, String crypt) { SQLiteDatabase db = helper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put(DatabaseHelper.USERNAME, username); values.put(DatabaseHelper.DOMAIN, domain); values.put(DatabaseHelper.CRYPT, crypt); long id = db.insert(DatabaseHelper.TABLE_NAME, null, values); db.close(); return id; } public Cursor getData() { String[] columns = {DatabaseHelper.USERNAME, DatabaseHelper.DOMAIN, DatabaseHelper.CRYPT}; SQLiteDatabase db = helper.getReadableDatabase(); Cursor c = db.query(DatabaseHelper.TABLE_NAME, columns, null, null, null, null, null); db.close(); // This line is the problem. return c; } public boolean deleteRow(SavedInformation information) { String[] array = {information.getUsername(), information.getDomainName(), information.getCryptPassword()}; SQLiteDatabase db = helper.getWritableDatabase(); db.delete(DatabaseHelper.TABLE_NAME, "" + DatabaseHelper.USERNAME + " = ? AND " + DatabaseHelper.DOMAIN + " = ? AND " + DatabaseHelper.CRYPT + " = ?", array); return true; } static class DatabaseHelper extends SQLiteOpenHelper { private static final String DATABASE_NAME = "Data_HASH"; private static final int DATABASE_VERSION = 1; private static final String TABLE_NAME = "HASH"; /*DATABASE COLUMNS */ private static final String UID = "_id"; private static final String USERNAME = "username"; private static final String DOMAIN = "domain"; private static final String CRYPT = "crypt"; /*DATABASE CREATE QUERY */ private static final String CREATE_TABLE = "CREATE TABLE " + TABLE_NAME + " (" + UID + " INTEGER PRIMARY KEY AUTOINCREMENT," + USERNAME + " VARCHAR," + DOMAIN + " VARCHAR," + CRYPT + " VARCHAR);"; public DatabaseHelper (Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { try { db.execSQL(CREATE_TABLE); } catch (SQLException e) { e.printStackTrace(); } } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { try { db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME); onCreate(db); } catch (SQLException e) { e.printStackTrace(); } } }}
Improve this question
5
  • you should open close the db outside of the DatabaseAdapter , from the calling activity. So on the activity you use getData() you should open the db, call getData(), do whatever you want with the data, and then close the db. Commented Apr 15, 2015 at 12:31
  • The db is initialized with the help of a inner class which is DatabaseHelper. Commented Apr 15, 2015 at 13:15
  • 1
    yes, im sayingit would be better to have a SQLiteDatabase mDb member variable on DatabaseAdapter and have dbOpen() {mDb = helper.getWritableDatabase();} and dbClose(){mdb.Close()} functions that you call from whatever activity wants to access the db, so it can close the db after it is done with it. Commented Apr 15, 2015 at 13:20
  • 1
    also, you can use a singleton approach, see my answer here: stackoverflow.com/questions/28747201/… Commented Apr 15, 2015 at 13:21
  • I wanted to know the singleton approach for this. Thank you :) Commented Apr 15, 2015 at 13:40

2 Answers 2

Reset to default
2

If I'm not mistaken, you shouldn't use the cursor in any other class if you're just getting data from the database. Create a data model and return the data from the method, not a cursor reference.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

If I use a Data Model class say RowData that has the data members for all the columns which are returned then I think I should use ArrayList to store the single row object. Am I Right or is there any other good way to do this.
I'm usually using an ArrayList, and it works for me..:)
Alright. I can take your solution and work with it. The thing is still not clear to me why it returns empty cursor if I close the database. The work of database object was to return the cursor and it did then what else cursor wants from it.
Well simplified, the cursor is a reference to a temporary table in current database instance. If you close the database, you close any temp tables holding your query results, and the cursor itself isn't pointing to anything after that.
Hmm. I thought it works like an Array which stores our data, but it is a reference to a temporary table then is is obvious it will require the database object. Thanks khumche :)
0

You should avoid to call open/close cursor many times.

Improve this answer

3 Comments

This isn't answering the question, make it more relevant to the question and add a justification.
This is an Android application so I cannot control the user behavior. If there is a button which gets the data using getdata() function then it is the user's choice how many times he clicks it.
If you are using a list view then you can use CursorAdapter. Or try to call close method onPause or onDestroy in activity.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.