Today we will learn how to manage a database in Android using native SQLite libraries.
- SQLiteOpenHelper: (abstract class to derive) provides to manage database creation and versioning.
- SQLiteDatabase: provides methods to execute sql commands.
The first class launches our "create tables" and other stuffs..
The second one let you choose to execute raw sql commands or use defined methods for insert, update and delete (see examples).
We could create a wrapper to make work easier. It can contains SQLiteOpenHelper derived class and the methods to simplify the access to SQLiteDatabase object.
//Some constants private static final String DATABASE_NAME = "mydb"; private static final String DATABASE_TABLE = "settings"; public static final String KEY_SERVERIP = "server_ip"; public static final String KEY_SERVERPORT = "server_port"; private static final String DATABASE_CREATETABLE= "create table "+DATABASE_TABLE+" ( " + KEY_SERVERIP+ " text null, " + KEY_SERVERPORT+" text null ); "; private static final int DATABASE_VERSION = 2; //Helper class private static class DatabaseHelper extends SQLiteOpenHelper { DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(DATABASE_CREATETABLE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS "+DATABASE_TABLE); onCreate(db); } }
The second one let you choose to execute raw sql commands or use defined methods for insert, update and delete (see examples).
DatabaseHelper DBHelper = new DatabaseHelper(context); //This method returns database and opens it. //It also check if create (onCreate) or upgrade (onUpgrade). SQLiteDatabase db = DBHelper.getWritableDatabase(); //... //Method to update with method of SQLiteDatabase class public Boolean insertServerData(String Ip,String port) { DBHelper.getWritableDatabase(); //database is always to open try { ContentValues args = new ContentValues(); args.put(KEY_SERVERIP, Ip); args.put(KEY_SERVERPORT, port); return db.update(DATABASE_TABLE, args, null, null) > 0; } catch(Exception ex) { return false; } finally { DBHelper.close(); //and close it when we don't need it anymore } } //Method to select with method of SQLiteDatabaseClass public Cursor SelectAll() { Cursor mCursor =null; try { open(); mCursor= db.query(true, DATABASE_TABLE, new String[] { KEY_SERVERIP, KEY_SERVERPORT}, null, null, null, null, null, null); return mCursor; } catch(SQLException x) { //... } finally { close(); } return null; } //Method to Execute a raw query public boolean insertNullRecord() { try { open(); //returs void db.execSQL("insert into "+DATABASE_TABLE+" SELECT NULL,NULL,NULL,NULL "); //db.rawQuery(sSql,null); returns a Cursor return true; } catch(Exception ex) { return false; } finally { close(); } }
We could create a wrapper to make work easier. It can contains SQLiteOpenHelper derived class and the methods to simplify the access to SQLiteDatabase object.
public class DBAdapter { private final Context context; private DatabaseHelper DBHelper; private SQLiteDatabase db; public DBAdapter(Context ctx) { this.context = ctx; DBHelper = new DatabaseHelper(context); } //opens the database public DBAdapter open() throws SQLException { db = DBHelper.getWritableDatabase(); return this; } //closes the database public void close() { DBHelper.close(); } /* - HERE MY METHODS TO EXECUTE QUERIES - HERE DATABASEHELPER CLASS WITH CONSTANTS */ }
Here we are! Just fix (as we want) our class and it's quite ready! Let's remember together how retrieve data from Cursor and we are done!
Collection<String> list=null; Cursor resultSet=dbWr.GetData(); //retrieve cursor if(resultSet!=null && resultSet.getCount()>0) { resultSet.moveToFirst(); try { list = new ArrayList<String>(); do { //0 is the first column list.add(resultSet.getString(0)); } while(resultSet.moveToNext()); } catch(SQLException ex) { throw ex; } finally { resultSet.close(); } }
Happy coding guys! Thanks for reading :)
I hope you apreciate it.
No comments:
Post a Comment