row_counter<\/em> table in the example :<\/p>\n\/**\r\n* This class represents a contract for a row_counter table containing row\r\n* counters for projects. The project must exist before creating row counters\r\n* since the counter have a foreign key to the project.\r\n*\/\r\npublic final class RowCounterContract {\r\n\r\n\/**\r\n* Contains the name of the table to create that contains the row counters.\r\n*\/\r\npublic static final String TABLE_NAME = \"row_counter\";\r\n\r\n\/**\r\n* Contains the SQL query to use to create the table containing the row counters.\r\n*\/\r\npublic static final String SQL_CREATE_TABLE = \"CREATE TABLE \"\r\n+ RowCounterContract.TABLE_NAME + \" (\"\r\n+ RowCounterContract.RowCounterEntry._ID + \" INTEGER PRIMARY KEY AUTOINCREMENT,\"\r\n+ RowCounterContract.RowCounterEntry.COLUMN_NAME_PROJECT_ID + \" INTEGER,\"\r\n+ RowCounterContract.RowCounterEntry.COLUMN_NAME_CURRENT_AMOUNT + \" INTEGER DEFAULT 0,\"\r\n+ RowCounterContract.RowCounterEntry.COLUMN_NAME_FINAL_AMOUNT + \" INTEGER,\"\r\n+ \"FOREIGN KEY (\" + RowCounterContract.RowCounterEntry.COLUMN_NAME_PROJECT_ID + \") \"\r\n+ \"REFERENCES projects(\" + ProjectContract.ProjectEntry._ID + \"));\";\r\n\r\n\/**\r\n* This class represents the rows for an entry in the row_counter table. The\r\n* primary key is the _id column from the BaseColumn class.\r\n*\/\r\npublic static abstract class RowCounterEntry implements BaseColumns {\r\n\r\n \/\/ Identifier of the project to which the row counter belongs\r\n public static final String COLUMN_NAME_PROJECT_ID = \"project_id\";\r\n\r\n \/\/ Final amount of rows to reach\r\n public static final String COLUMN_NAME_FINAL_AMOUNT = \"final_amount\";\r\n\r\n \/\/ Current amount of rows done\r\n public static final String COLUMN_NAME_CURRENT_AMOUNT = \"current_amount\";\r\n }\r\n}\r\n<\/pre>\nTo create the tables that stores the data described by the contracts, you must implement the android.database.sqllite.SQLLiteOpenHelper<\/em> class that manages the access to the database. The following methods should be implemented as needed:<\/p>\n\n- onCreate:<\/strong> this method is called the first time the database is opened by your application. You should setup the database for use in that method by creating the tables and initializing any data you need.<\/li>\n
- onUpdate:<\/strong> this method is called when your application is upgraded and the version number has changed. You don’t need to do anything for your first version, but in the following versions you must provide queries to modify the database from the old version to the new structure as needed so your user don’t loose their data during the upgrade.<\/li>\n
- onDowngrade (optional) :<\/strong> you may implement this method if you want to handle the case where your application is downgraded to a version requiring an older version. The default implementation will throw a SQLiteException<\/em> and will not modify the database.<\/li>\n
- onOpen (optional) : <\/strong>this method is called after the database has been created, upgraded to a newer version or downgraded to an older version.<\/li>\n<\/ul>\n
Here is a basic implementation of the android.database.sqllite.SQLLiteOpenHelper<\/em> for the example that executes an SQL CREATE TABLE query for each table of the database in the onCreate<\/em> method. There is no method available in the android.database.sqlite.SQLiteDatabase<\/em> class to create a table, so you must use the execSQL<\/em> method to execute the query.<\/p>\n\/**\r\n* This class helps open, create, and upgrade the database file containing the\r\n* projects and their row counters.\r\n*\/\r\npublic class ProjectsDatabaseHelper extends SQLiteOpenHelper {\r\n \/\/ If you change the database schema, you must increment the database version.\r\n public static final int DATABASE_VERSION = 1;\r\n \/\/ The name of the database file on the file system\r\n public static final String DATABASE_NAME = \"Projects.db\";\r\n\r\n public ProjectsDatabaseHelper(Context context) {\r\n super(context, DATABASE_NAME, null, DATABASE_VERSION);\r\n }\r\n\r\n \/**\r\n * Creates the underlying database with the SQL_CREATE_TABLE queries from\r\n * the contract classes to create the tables and initialize the data.\r\n * The onCreate is triggered the first time someone tries to access\r\n * the database with the getReadableDatabase or\r\n * getWritableDatabase methods.\r\n *\r\n * @param db the database being accessed and that should be created.\r\n *\/\r\n @Override\r\n public void onCreate(SQLiteDatabase db) {\r\n \/\/ Create the database to contain the data for the projects\r\n db.execSQL(ProjectContract.SQL_CREATE_TABLE);\r\n db.execSQL(RowCounterContract.SQL_CREATE_TABLE);\r\n initializeExampleData(db);\r\n }\r\n\r\n \/**\r\n * This method must be implemented if your application is upgraded and must\r\n * include the SQL query to upgrade the database from your old to your new\r\n * schema.\r\n *\r\n * @param db the database being upgraded.\r\n * @param oldVersion the current version of the database before the upgrade.\r\n * @param newVersion the version of the database after the upgrade.\r\n *\/\r\n @Override\r\n public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {\r\n \/\/ Logs that the database is being upgraded\r\n Log.i(ProjectsDatabaseHelper.class.getSimpleName(),\r\n \"Upgrading database from version \" + oldVersion + \" to \" + newVersion);\r\n }\r\n }\r\n<\/pre>\nOnce the android.database.sqllite.SQLLiteOpenHelper<\/em> is implemented, you can get an instance of the database object android.database.sqlite.SQLiteDatabas<\/em>e using the getReadableDatabase<\/em> method of the helper if
\nyou only need to read data or the getWritableDatabase <\/em>method if you need to read and write data. There are four kinds of basic operations that can be done with the data, and modifications can not be undone like in all databases.<\/p>\n\n- Inserting a new row:<\/strong>the insert<\/em> method of the android.database.sqlite.SQLiteDatabase<\/em> object inserts a new row of data in a table. Data can be inserted with a SQL INSERT query using the execSQL<\/em> method, but using insert<\/em> is recommended to avoid SQL injection: only one database row can be created by the insert<\/em> method and nothing else, regardless of the input. In the following example, a few test projects are initialized in the database of the application by the onCreate<\/em> method of the database helper after the creation of the table:\n
\/**\r\n * Initialize example data to show when the application is first installed. \r\n * \r\n * @param db the database being initialized.\r\n *\/\r\nprivate void initializeExampleData(SQLiteDatabase db) {\r\n \/\/ A lot of code is repeated here that could be factorized in methods, \r\n \/\/ but this is clearer for the example\r\n\t\t\r\n \/\/ Insert the database row for an example project in the project table in the\r\n \/\/ database\r\n long projectId;\r\n ContentValues firstProjectValues = new ContentValues();\r\n firstProjectValues.put(ProjectContract.ProjectEntry.COLUMN_NAME_TITLE, \r\n \"Flashy Scarf\");\r\n projectId = db.insert(ProjectContract.TABLE_NAME, null, firstProjectValues);\r\n \/\/ Insert the database rows for a row counter linked to the project row \r\n \/\/ just created in the database (the insert method returns the \r\n \/\/ identifier of the row)\r\n ContentValues firstProjectCounterValues = new ContentValues();\r\n firstProjectCounterValues.put(RowCounterContract\r\n .RowCounterEntry.COLUMN_NAME_PROJECT_ID, projectId);\r\n firstProjectCounterValues.put(RowCounterContract\r\n .RowCounterEntry.COLUMN_NAME_FINAL_AMOUNT, 120);\r\n db.insert(RowCounterContract.TABLE_NAME, null, firstProjectCounterValues);\r\n\t\t\r\n \/\/ Insert the database row for a second example project in the project \r\n \/\/ table in the database.\r\n ContentValues secondProjectValues = new ContentValues();\r\n secondProjectValues.put(ProjectContract.ProjectEntry.COLUMN_NAME_TITLE, \r\n \"Simple Socks\");\r\n projectId = db.insert(ProjectContract.TABLE_NAME, null, secondProjectValues);\r\n \/\/ Insert the database rows for two identical row counters for the \r\n \/\/ project in the database\r\n ContentValues secondProjectCounterValues = new ContentValues();\r\n secondProjectCounterValues.put(RowCounterContract\r\n .RowCounterEntry.COLUMN_NAME_PROJECT_ID, projectId);\r\n secondProjectCounterValues.put(RowCounterContract\r\n .RowCounterEntry.COLUMN_NAME_FINAL_AMOUNT, 80);\r\n db.insert(RowCounterContract.TABLE_NAME, null, secondProjectCounterValues);\r\n db.insert(RowCounterContract.TABLE_NAME, null, secondProjectCounterValues);\t\r\n }\t\r\n<\/pre>\n<\/li>\n
- Reading existing rows:<\/strong> the query<\/em> method from the android.database.sqlite.SQLiteDatabase<\/em> class retrieves the data that was previously inserted in the database. This method will return a cursor that points to the collection of rows returned by your request, if any. You can then convert the data fetched from the database table to an object can be used in your application: in the example, the rows from the project<\/em> table are converted to Project<\/em> objects.\n
\/**\r\n\r\n* Gets the list of projects from the database.\r\n*\r\n* @return the current projects from the database.\r\n*\/\r\npublic ArrayList getProjects() {\r\n ArrayList projects = new ArrayList();\r\n \/\/ Gets the database in the current database helper in read-only mode\r\n SQLiteDatabase db = getReadableDatabase();\r\n\r\n \/\/ After the query, the cursor points to the first database row\r\n \/\/ returned by the request.\r\n Cursor projCursor = db.query(ProjectContract.TABLE_NAME, null, null, \r\n null, null, null, null);\r\n while (projCursor.moveToNext()) {\r\n \/\/ Get the value for each column for the database row pointed by\r\n \/\/ the cursor using the getColumnIndex method of the cursor and\r\n \/\/ use it to initialize a Project object by database row\r\n Project project = new Project();\r\n \r\n int idColIndex = projCursor.getColumnIndex(ProjectContract.ProjectEntry._ID);\r\n long projectId = projCursor.getLong(idColIndex);\r\n project.setId(projCursor.getLong(projectId);\r\n\r\n int nameColIndex = projCursor.getColumnIndex(ProjectContract\r\n .ProjectEntry.COLUMN_NAME_TITLE);\r\n project.setName(projCursor.getString(nameColIndex));\r\n \/\/ Get all the row counters for the current project from the\r\n \/\/ database and add them all to the Project object\r\n project.setRowCounters(getRowCounters(projectId));\r\n\r\n projects.add(project);\r\n }\r\n\r\n projCursor.close();\r\n\r\n return (projects);\r\n }\r\n<\/pre>\n<\/li>\n
- Updating existing rows: <\/strong>the update<\/em> method of an instance of the android.database.sqlite.SQLiteDatabase<\/em> class updates the data in a row or in multiple rows of a database table. Like with the insert <\/em>method, you could use the execSQL<\/em> query to run a SQL UPDATE query, but using the update<\/em> method is safer. In the following example, the current row counter value for the row counter in the row_counter<\/em> table is updated with the new value. According to the condition specified only the row counter with the identifier passed as a parameter is updated but with another condition you could update many rows, so you should always make sure that the condition only selects the rows you need.\n
\/**\r\n * Updates the current amount of the row counter in the database to the value \r\n * in the object passed as a parameter.\r\n * \r\n * @param rowCounter the object containing the current amount to set.\r\n *\/\r\npublic void updateRowCounterCurrentAmount(RowCounter rowCounter) {\r\n SQLiteDatabase db = getWritableDatabase();\r\n\t\t\r\n ContentValues currentAmountValue = new ContentValues();\r\n currentAmountValue.put(RowCounterContract.RowCounterEntry.COLUMN_NAME_CURRENT_AMOUNT, \r\n rowCounter.getCurrentAmount());\r\n\t\t\r\n db.update(RowCounterContract.TABLE_NAME, \r\n\t currentAmountValue, \r\n\t RowCounterContract.RowCounterEntry._ID +\"=?\",\r\n\t new String[] { String.valueOf(rowCounter.getId()) });\r\n }\r\n<\/pre>\n<\/li>\n
- Deleting existing rows:<\/strong>the delete<\/em> method of an instance of the android.database.sqlite.SQLiteDatabase<\/em> class deletes a row or in multiple rows of a database table. Like with the insert <\/em>method, you could use the execSQL<\/em> query to run a SQL UPDATE query, but using the delete<\/em> method is safer. In the following example, a row counter in the row_counter<\/em> table is deleted. According to the condition specified only the row counter with the identifier passed as a parameter is deleted but with another condition you could delete many rows, so you should always make sure that the condition only selects the rows you need so you don’t delete too much data.\n
\/**\r\n * Deletes the specified row counter from the database.\r\n * \r\n * @param rowCounter the row counter to remove.\r\n *\/\r\npublic void deleteRowCounter(RowCounter rowCounter) {\r\n SQLiteDatabase db = getWritableDatabase();\r\n\t\t\r\n db.delete(RowCounterContract.TABLE_NAME, \t\t\t \r\n RowCounterContract.RowCounterEntry._ID +\"=?\",\r\n new String[] { String.valueOf(rowCounter.getId()) });\r\n }\r\n<\/pre>\n<\/li>\n<\/ul>\nFinally, if you want to encapsulate access to the data in your database to avoid calling the database helper directly in your activity, you can also implement the android.content.ContentProvider<\/em> class from the Android SDK. This is only required if your application must share data with other applications: you do not need one to get started, but you should consider using it as your data gets more complex.