First, my Book object:
public class Book {
public static final String TABLE_NAME = "BOOK";
public static final String COLUMN_ID = "ID";
public static final String COLUMN_TITLE = "TITLE";
private long id;
private String title;
public Book(String title) {
this.id = -1;
this.title = title;
}
public Book(long id, String title) {
this.id = id;
this.title = title;
}
public long getId() {
return id;
}
public void setId(long id) {
this.id = id;
}
public String getTitle() {
return this.title;
}
public void setTitle(String title) {
this.title = title;
}
}
My Database helper:
public class DatabaseHelper extends SQLiteOpenHelper {
private volatile SQLiteDatabase mDatabase;
private Context mContext;
private static final String DB_NAME = "data.db";
private static final int DB_VERSION = 1;
public DatabaseHelper(Context context) {
super(context.getApplicationContext(), DB_NAME, null, DB_VERSION);
mContext = context.getApplicationContext();
}
private synchronized void open() throws SQLException {
mDatabase = getWritableDatabase();
}
@Override
public void onCreate(SQLiteDatabase db) {
DatabaseSetup.createTables(db);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
//********CRUD OPERATIONS************
public synchronized long createBook(Book book) {
open(); //opens DB
ContentValues values = new ContentValues();
values.put(Book.COLUMN_TITLE, book.getTitle());
long id = mDatabase.insertWithOnConflict(Book.TABLE_NAME, null, values, SQLiteDatabase.CONFLICT_IGNORE);
close(); //closes DB
return id;
}
public synchronized Book getBookById(long id) {
open(); //opens DB
Cursor cursor = mDatabase.query(
Book.TABLE_NAME, // table
new String[]{Book.COLUMN_ID, Book.COLUMN_TITLE}, // column names
Book.COLUMN_ID + " = ?", // where clause
new String[]{id + ""}, // where params
null, // groupby
null, // having
null); // orderby
Book book = null;
if (cursor != null && cursor.getCount() > 0) {
cursor.moveToFirst();
String title = getStringFromColumnName(cursor, Book.COLUMN_TITLE);
book = new Book(id, title);
cursor.close();
}
close(); //closes DB
return book;
}
public synchronized long updateBook(Book book) {
open(); //opens DB
long bookId = book.getId();
ContentValues values = new ContentValues();
values.put(Book.COLUMN_TITLE, book.getTitle());
long numUpdated = mDatabase.updateWithOnConflict(
Book.TABLE_NAME, // table
values, // values
Book.COLUMN_ID + " = ?", // where clause
new String[]{bookId + ""}, // where params
SQLiteDatabase.CONFLICT_IGNORE);
close(); //closes DB
return numUpdated;
}
public synchronized void deleteBook(Book book) {
open(); //opens DB
long bookId = book.getId();
mDatabase.delete(
Book.TABLE_NAME, // table
Book.COLUMN_ID + " = ?", // where clause
new String[]{bookId + ""}); // where params
close(); //closes DB
}
//********HELPER FUNCTIONS************
public static String getStringFromColumnName(Cursor cursor, String columnName) {
int columnIndex = cursor.getColumnIndex(columnName);
return cursor.getString(columnIndex);
}
}
And my Database setup class which handles create-table queries
public class DatabaseSetup {
//create-table queries
public static final String CREATE_TABLE_BOOK_STRING =
"CREATE TABLE " + Book.TABLE_NAME + " ("
+ Book.COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
+ Book.COLUMN_TITLE + " TEXT NOT NULL)";
public static void createTables(SQLiteDatabase db) {
db.execSQL(CREATE_TABLE_BOOK_STRING);
}
}
Is it considered a good idea to open and close the database in every CRUD function like I am doing? I do it this way because even if I were to just open the database in, for example, an
onResume()
call in an Activity, there is no guarantee the connection will still be open by the time I invoke a CRUD operation elsewhere in the Activity.Do I need to be doing anything else to verify that the database is open / safe to perform operations on?
Is it even necessary to call
close()
?Is it bad practice for me to be putting the table name / field names in the
Book
class?Should I be putting the CRUD operations in their own separate class, e.g.
public class BookCrudHandler
?Should I be putting the
id
in the Book class (so that I have some relationship between the object and the row in the database), or should I have some other kind of data structure that maps POJOs to their corresponding IDs in the database that I reference as a lookup?Anything else / any comments / any practices I am missing that I should be using?