Chapter1 SoLiteopenHelper will trigger its onupgrade (method if our database version number is not at default 1.Important methods of the soLiteopenHelper class are as follows: synchronized void close() synchronized SQLiteDatabase getReadableDatabase() synchronized SOLiteDatabase getWritableDatabase() abstract void onCreate(SQLiteDatabase db) void onopen(SQLiteDatabase db) abstract onUpgrade(SQLiteDatabase db,int oldversion,int e()method close synchron eyword prevents threa emory con ncy error The getRe ethods, me the fact that getReadableDatabase will return a readable database in case it cannot return a writable database,whereas getwriteableDatabase()returns a writable database object.The getWriteableDatabase()method will throw an SQLiteException if a database canno t be open ncase of getReadableDatabase(),if a on the database Calling either methods will invoke the()method if the database doesn't exist yet.Othe wise,it will invoke the onopen】oroi ng on t n numbe The onopen y (meth upd the ath ormanc The on onopen 】 rade()methods are des ed for the subclass nlement the i the database is created for the first time.This is the place where we create our tables by using SQLite statements,which we saw earlier in the example.The onopen() method is triggered when the database has been configured and after the database or downgraded as necessary.Read/write status the help of the isReadonly()methoc 【171
Chapter 1 [ 17 ] SQLiteOpenHelper will trigger its onUpgrade() method if our database version number is not at default 1. Important methods of the SQLiteOpenHelper class are as follows: • synchronized void close() • synchronized SQLiteDatabase getReadableDatabase() • synchronized SQLiteDatabase getWritableDatabase() • abstract void onCreate(SQLiteDatabase db) • void onOpen(SQLiteDatabase db) • abstract void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) The synchronized close() method closes any open database object. The synchronized keyword prevents thread and memory consistency errors. The next two methods, getReadableDatabase() and getWriteableDatabase(), are the methods in which the database is actually created or opened. Both return the same SQLiteDatabase object; the difference lies in the fact that getReadableDatabase() will return a readable database in case it cannot return a writable database, whereas getWriteableDatabase() returns a writable database object. The getWriteableDatabase() method will throw an SQLiteException if a database cannot be opened for writing. In case of getReadableDatabase(), if a database cannot be opened, it will throw the same exception. We can use the isReadOnly() method of the SQLiteDatabase class on the database object to know the state of the database. It returns true for read-only databases. Calling either methods will invoke the onCreate() method if the database doesn't exist yet. Otherwise, it will invoke the onOpen() or onUpgrade() methods, depending on the version number. The onOpen() method should check the isReadOnly() method before updating the database. Once opened, the database is cached to improve performance. Finally, we need to call the close() method to close the database object. The onCreate(), onOpen(), and onUpgrade() methods are designed for the subclass to implement the intended behavior. The onCreate() method is called when the database is created for the first time. This is the place where we create our tables by using SQLite statements, which we saw earlier in the example. The onOpen() method is triggered when the database has been configured and after the database schema has been created, upgraded, or downgraded as necessary. Read/write status should be checked here with the help of the isReadOnly() method
Enter SQLite The onUpgrade()method is called when the database needs to be upgraded depending on the version number supplied to it.By default,the database version is 1,and a As ample illustrating the use of the SQLiteOpenHelper class is present in the code undefor this chapter:we would ber public static final int VERSTON_NUMBER -1; sqlHelper @Override public void onUpgrade (SOLiteDatabase db, int oldversion,int newversion) //drop table on upgrade ABLE IF EXISTS· rs) /Create tables again onCreate(db); /creating table during oncreate TABLE_CONTACTS+( KEY NUMBER+INTEGER"+)"; catch(sQLException e){ e.printstackTrace(); 【18]
Enter SQLite [ 18 ] The onUpgrade() method is called when the database needs to be upgraded depending on the version number supplied to it. By default, the database version is 1, and as we increment the database version numbers and release new versions, the upgrade will be performed. A simple example illustrating the use of the SQLiteOpenHelper class is present in the code bundle for this chapter; we would be using it for explanation: class SQLiteHelperClass { . . public static final int VERSION_NUMBER = 1; sqlHelper = new SQLiteOpenHelper(context, "ContactDatabase", null, VERSION_NUMBER) { @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { //drop table on upgrade db.execSQL("DROP TABLE IF EXISTS " + TABLE_CONTACTS); // Create tables again onCreate(db); } @Override public void onCreate(SQLiteDatabase db) { // creating table during onCreate String createContactsTable = "CREATE TABLE " + TABLE_CONTACTS + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT," + KEY_NUMBER + " INTEGER" + ")"; try { db.execSQL(createContactsTable); } catch(SQLException e) { e.printStackTrace();
Chapter 1 } bc ynchronized void ( aoverride super.close(): Log d("TAG"."Database closed"): void npetb db) aoverride super.onopen(db): Log.d("TAG","Database opened"); per.getWritableDatabase(); Downloading the example code ou can download the example code files for all packtpub.com If urchased this book elsewhere,you can v a89 The SQLiteDatabase class i,it's time to】 at the pro o open,query,upc nd clos 【19]
Chapter 1 [ 19 ] } } @Override public synchronized void close() { super.close(); Log.d("TAG", "Database closed"); } @Override public void onOpen(SQLiteDatabase db) { super.onOpen(db); Log.d("TAG", "Database opened"); } }; . . //open the database in read-only mode SQLiteDatabase db = SQLiteOpenHelper.getWritableDatabase(); . . //open the database in read/write mode SQLiteDatabase db = SQLiteOpenHelper.getWritableDatabase(); Downloading the example code You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www. packtpub.com/support and register to have the files e-mailed directly to you. The SQLiteDatabase class Now that you are familiar with the helper class that kick-starts the use of SQLite databases within Android, it's time to look at the core SQLiteDatabase class. SQLiteDatabase is the base class required to work with an SQLite database in Android and provides methods to open, query, update, and close the database
Enter SQLite More than 50 methods are available for the sQLiteDatabase class,each with its own ncsather than an exaustive list,welcover the most mp of me thods and allow you to explore s ed method s a ure.At any time,you can refer to cument 7atabae7eaite7eoaitakdae. Some methods of the soLiteDatabase class are shown in the following list g groupBy having,String orderBy) public Cursor rawQuery(string sql,string [selectionArgs) public int delete (string table,string whereclause,string[ wherearas】 public in updare arring pabie Contentvalues values,String string[]whereArgs) Let us see these soLiteDatab action with an ole We will inse a name and number in our table Then we ry to fetch data back from the table.After this,we will go through the delete()and update()methods, both of which will take id as a parameter to identify which row of data in our database table we intend to delete or update: public void insertTosimpleDataBase() SQLiteDatabase db sqlHelper.getWritableDatabase(); cv.Put (KEY_NUMBER,"0000000000): values in different columns of the table using db.insert (TABLE CONTACTS,null,ev): cy.put（KEY NUMBER, "5555555")i //Inserting values in different columns of the table using db.insert (TABLE_CONTACTS,null,cv); 【201
Enter SQLite [ 20 ] More than 50 methods are available for the SQLiteDatabase class, each with its own nuances and use cases. Rather than an exhaustive list, we'll cover the most important subsets of methods and allow you to explore some of the overloaded methods at your leisure. At any time, you can refer to the full online Android documentation for the SQLiteDatabase class at http://developer.android.com/reference/ android/database/sqlite/SQLiteDatabase.html. Some methods of the SQLiteDatabase class are shown in the following list: • public long insert (String table, String nullColumnHack, ContentValues values) • public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy) • public Cursor rawQuery(String sql, String[] selectionArgs) • public int delete (String table, String whereClause, String[] whereArgs) • public int update (String table, ContentValues values, String whereClause, String[] whereArgs) Let us see these SQLiteDatabase classes in action with an example. We will insert a name and number in our table. Then we will use the raw query to fetch data back from the table. After this, we will go through the delete() and update() methods, both of which will take id as a parameter to identify which row of data in our database table we intend to delete or update: public void insertToSimpleDataBase() { SQLiteDatabase db = sqlHelper.getWritableDatabase(); ContentValues cv = new ContentValues(); cv.put(KEY_NAME, "John"); cv.put(KEY_NUMBER, "0000000000"); // Inserting values in different columns of the table using // Content Values db.insert(TABLE_CONTACTS, null, cv); cv = new ContentValues(); cv.put(KEY_NAME, "Tom"); cv.put(KEY_NUMBER, "5555555"); // Inserting values in different columns of the table using // Content Values db.insert(TABLE_CONTACTS, null, cv); }
Chapter 1 int count; Cursor cr-db.query(TABLE_CONTACTS, if(er I=null)( count ASE","count is :"count); if(cr I null) TABLE_CONTACTS,null) ount(); ""count is :"count); Pubiic void deiete(ing) String whereclause KEY_NAME+"?; String[] 9 new String[] ame whereArgs)i bc void update() ContentVa ContentValues(); BY NUMBER 9000") int rowsUpdated-db.update (TABLE CONTACTS,cv,whereclause, whereArgs); 【211 www.allitebooks.com
Chapter 1 [ 21 ] . public void getDataFromDatabase() { int count; db = sqlHelper.getReadableDatabase(); // Use of normal query to fetch data Cursor cr = db. query(TABLE_CONTACTS, null, null, null, null, null, null); if(cr != null) { count = cr.getCount(); Log.d("DATABASE", "count is : " + count); } // Use of raw query to fetch data cr = db.rawQuery("select * from " + TABLE_CONTACTS, null); if(cr != null) { count = cr.getCount(); Log.d("DATABASE", "count is : " + count); } } . . public void delete(String name) { String whereClause = KEY_NAME + "=?"; String[] whereArgs = new String[]{name}; db = sqlHelper.getWritableDatabase(); int rowsDeleted = db.delete(TABLE_CONTACTS, whereClause, whereArgs); } . . public void update(String name) { String whereClause = KEY_NAME + "=?"; String[] whereArgs = new String[]{name}; ContentValues cv = new ContentValues(); cv.put(KEY_NAME, "Betty"); cv.put(KEY_NUMBER, "999000"); db = sqlHelper.getWritableDatabase(); int rowsUpdated = db.update(TABLE_CONTACTS, cv, whereClause, whereArgs); } www.allitebooks.com