packagecom.mindorks.example.android_dagger2_example.data; |
importandroid.content.ContentValues; |
importandroid.content.Context; |
importandroid.content.res.Resources; |
importandroid.database.Cursor; |
importandroid.database.SQLException; |
importandroid.database.sqlite.SQLiteDatabase; |
importandroid.database.sqlite.SQLiteOpenHelper; |
importcom.mindorks.example.android_dagger2_example.data.model.User; |
importcom.mindorks.example.android_dagger2_example.di.ApplicationContext; |
importcom.mindorks.example.android_dagger2_example.di.DatabaseInfo; |
importjavax.inject.Inject; |
importjavax.inject.Singleton; |
/** |
* Created by janisharali on 25/12/16. |
*/ |
@Singleton |
publicclassDbHelperextendsSQLiteOpenHelper { |
//USER TABLE |
publicstaticfinalStringUSER_TABLE_NAME='users'; |
publicstaticfinalStringUSER_COLUMN_USER_ID='id'; |
publicstaticfinalStringUSER_COLUMN_USER_NAME='usr_name'; |
publicstaticfinalStringUSER_COLUMN_USER_ADDRESS='usr_add'; |
publicstaticfinalStringUSER_COLUMN_USER_CREATED_AT='created_at'; |
publicstaticfinalStringUSER_COLUMN_USER_UPDATED_AT='updated_at'; |
@Inject |
publicDbHelper(@ApplicationContextContextcontext, |
@DatabaseInfoStringdbName, |
@DatabaseInfoIntegerversion) { |
super(context, dbName, null, version); |
} |
@Override |
publicvoidonCreate(SQLiteDatabasedb) { |
tableCreateStatements(db); |
} |
@Override |
publicvoidonUpgrade(SQLiteDatabasedb, intoldVersion, intnewVersion) { |
db.execSQL('DROP TABLE IF EXISTS '+USER_TABLE_NAME); |
onCreate(db); |
} |
privatevoidtableCreateStatements(SQLiteDatabasedb) { |
try { |
db.execSQL( |
'CREATE TABLE IF NOT EXISTS ' |
+USER_TABLE_NAME+'(' |
+USER_COLUMN_USER_ID+' INTEGER PRIMARY KEY AUTOINCREMENT, ' |
+USER_COLUMN_USER_NAME+' VARCHAR(20), ' |
+USER_COLUMN_USER_ADDRESS+' VARCHAR(50), ' |
+USER_COLUMN_USER_CREATED_AT+' VARCHAR(10) DEFAULT '+ getCurrentTimeStamp() +', ' |
+USER_COLUMN_USER_UPDATED_AT+' VARCHAR(10) DEFAULT '+ getCurrentTimeStamp() +')' |
); |
} catch (SQLException e) { |
e.printStackTrace(); |
} |
} |
protectedUsergetUser(LonguserId) throwsResources.NotFoundException, NullPointerException { |
Cursor cursor =null; |
try { |
SQLiteDatabase db =this.getReadableDatabase(); |
cursor = db.rawQuery( |
'SELECT * FROM ' |
+USER_TABLE_NAME |
+' WHERE ' |
+USER_COLUMN_USER_ID |
+' = ? ', |
newString[]{userId +''}); |
if (cursor.getCount() >0) { |
cursor.moveToFirst(); |
User user =newUser(); |
user.setId(cursor.getLong(cursor.getColumnIndex(USER_COLUMN_USER_ID))); |
user.setName(cursor.getString(cursor.getColumnIndex(USER_COLUMN_USER_NAME))); |
user.setAddress(cursor.getString(cursor.getColumnIndex(USER_COLUMN_USER_ADDRESS))); |
user.setCreatedAt(cursor.getString(cursor.getColumnIndex(USER_COLUMN_USER_CREATED_AT))); |
user.setUpdatedAt(cursor.getString(cursor.getColumnIndex(USER_COLUMN_USER_UPDATED_AT))); |
return user; |
} else { |
thrownewResources.NotFoundException('User with id '+ userId +' does not exists'); |
} |
} catch (NullPointerException e) { |
e.printStackTrace(); |
throw e; |
} finally { |
if (cursor !=null) |
cursor.close(); |
} |
} |
protectedLonginsertUser(Useruser) throwsException { |
try { |
SQLiteDatabase db =this.getWritableDatabase(); |
ContentValues contentValues =newContentValues(); |
contentValues.put(USER_COLUMN_USER_NAME, user.getName()); |
contentValues.put(USER_COLUMN_USER_ADDRESS, user.getAddress()); |
return db.insert(USER_TABLE_NAME, null, contentValues); |
} catch (Exception e) { |
e.printStackTrace(); |
throw e; |
} |
} |
privateStringgetCurrentTimeStamp() { |
returnString.valueOf(System.currentTimeMillis() /1000); |
} |
} |
android.database.sqlite
package.BaseColumns
interface, your inner class can inherit a primarykey field called _ID
that some Android classes such as CursorAdapter
expect it to have. It's not required, but this can help your databasework harmoniously with the Android framework.SQLiteOpenHelper
class contains a usefulset of APIs for managing your database.When you use this class to obtain references to your database, the systemperforms the potentiallylong-running operations of creating and updating the database only whenneeded and not during app startup. All you need to do is callgetWritableDatabase()
orgetReadableDatabase()
.getWritableDatabase()
or getReadableDatabase()
in a background thread,such as with AsyncTask
or IntentService
.SQLiteOpenHelper
, create a subclass thatoverrides the onCreate()
andonUpgrade()
callback methods. You mayalso want to implement theonDowngrade()
oronOpen()
methods,but they are not required.SQLiteOpenHelper
thatuses some of the commands shown above:SQLiteOpenHelper
:ContentValues
object to the insert()
method:insert()
is simply the table name. ContentValues
is empty (i.e., you did notput
any values).If you specify the name of a column, the framework inserts a row and setsthe value of that column to null. If you specify null
, like in thiscode sample, the framework does not insert a row when there are no values.insert()
methods returns the ID for thenewly created row, or it will return -1 if there was an error inserting the data. This can happenif you have a conflict with pre-existing data in the database.query()
method, passing it your selection criteria and desired columns.The method combines elements of insert()
and update()
, except the column listdefines the data you want to fetch (the 'projection'), rather than the data to insert. The resultsof the query are returned to you in a Cursor
object.selection
and selectionArgs
) arecombined to create a WHERE clause. Because the arguments are provided separately from the selectionquery, they are escaped before being combined. This makes your selection statements immune to SQLinjection. For more detail about all arguments, see thequery()
reference.Cursor
movemethods, which you must always call before you begin reading values. Since the cursor starts atposition -1, calling moveToNext()
places the 'read position' on thefirst entry in the results and returns whether or not the cursor is already past the last entry inthe result set. For each row, you can read a column's value by calling one of theCursor
get methods, such as getString()
or getLong()
. For each of the get methods,you must pass the index position of the column you desire, which you can get by callinggetColumnIndex()
orgetColumnIndexOrThrow()
. When finishediterating through results, call close()
on the cursorto release its resources.For example, the following shows how to get all the item IDs stored in a cursorand add them to a list:delete()
method. Themechanism works the same as the selection arguments to thequery()
method. It divides theselection specification into a selection clause and selection arguments. Theclause defines the columns to look at, and also allows you to combine columntests. The arguments are values to test against that are bound into the clause.Because the result isn't handled the same as a regular SQL statement, it isimmune to SQL injection.delete()
methodindicates the number of rows that were deleted from the database.update()
method.ContentValues
syntax ofinsert()
with the WHERE
syntaxof delete()
.update()
method isthe number of rows affected in the database.getWritableDatabase()
and getReadableDatabase()
areexpensive to call when the database is closed, you should leave your database connectionopen for as long as you possibly need to access it. Typically, it is optimal to close the databasein the onDestroy()
of the calling Activity.sqlite3
shell tool that allows you to browsetable contents, run SQL commands, and perform other useful functions on SQLitedatabases. For more information, see how to how to issue shell commands.