In this article, we will see an example of SQLite Insert Example. We will work on database list application to demonstrate the SQLite insert.
In our previous article on Android, I showed you how to implement search. We will work on the same example to add an ‘Add’ button to the action bar.
Add ‘add’ button to menu as action
We want to add a button to the action bar, clicking which should open a dialog with a custom view. The custom view will contain just one edit field where we will enter the language name and then an ‘Add’ button to insert the language to the database table.
menu/main.xml:
<menu xmlns:android="http://schemas.android.com/apk/res/android" xmlns:app="http://schemas.android.com/apk/res-auto" xmlns:tools="http://schemas.android.com/tools" tools:context="com.javarticles.android.SearchableActivity" > <item android:id="@+id/search" android:actionViewClass="android.widget.SearchView" android:icon="@drawable/ic_search" android:showAsAction="ifRoom|collapseActionView" android:title="@string/search_label"/> <item android:id="@+id/action_add_item" android:icon="@drawable/add_item_icon" android:showAsAction="always" android:title="@string/button_add_lang"/> </menu>
Custom dialog view to add the language
When we click on the ‘Add’ button in the action bar, we will show the language field in a dialog box. The dialog view will have language and the ‘Add’ button. Here is the custom dialog view. This will be set to the AlertDialog
as we build it.
activity_add_item.xml.xml:
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:paddingBottom="@dimen/activity_vertical_margin" android:paddingLeft="@dimen/activity_horizontal_margin" android:paddingRight="@dimen/activity_horizontal_margin" android:paddingTop="@dimen/activity_vertical_margin" tools:context="com.whereisit.android.geton.ui.ItemAddActivity" > <EditText android:id="@+id/langName" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignParentLeft="true" android:layout_alignParentTop="true" android:layout_marginLeft="18dp" android:ems="10" android:hint="@string/lang_hint"> <requestFocus /> </EditText> <Button android:id="@+id/buttonAddItem" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_alignRight="@+id/langName" android:layout_below="@+id/langName" android:text="@string/button_add_lang" /> </RelativeLayout>
Override onOptionsItemSelected
We have added the ‘Add’ button to the action bar. When the button is clicked it should open up the AlertDialog
. We will override method onOptionsItemSelected()
to create the DialogFragment
and show it. We need to hook in the creation of AlertDialog
in the onCreateDialog()
method of DialogFragment
.
LanguageListActivity:
package com.javarticles.android; import android.app.DialogFragment; import android.app.ListActivity; import android.app.LoaderManager.LoaderCallbacks; import android.app.SearchManager; import android.content.Context; import android.content.CursorLoader; import android.content.Loader; import android.database.Cursor; import android.os.Bundle; import android.view.Menu; import android.view.MenuItem; import android.widget.CursorAdapter; import android.widget.SearchView; import android.widget.SimpleCursorAdapter; import com.javarticles.android.database.DatabaseConstants; public class LanguageListActivity extends ListActivity implements LoaderCallbacks { private static final int LOADER_ID = 42; private CursorAdapter _adapter; public void onCreate(final Bundle savedInstanceState) { super.onCreate(savedInstanceState); setContentView(R.layout.container_list); _adapter = new SimpleCursorAdapter(this, R.layout.container_list_item_view, null, new String[] { DatabaseConstants.COL_LANG_NAME }, new int[] { R.id.list_item }); setListAdapter(_adapter); getLoaderManager().initLoader(LOADER_ID, null, this); } @Override public Loader onCreateLoader(int id, Bundle args) { if (id != LOADER_ID) { return null; } return new CursorLoader(LanguageListActivity.this, LanguageContentProvider.CONTENT_URI, new String[] { DatabaseConstants.COL_LANG_ID, DatabaseConstants.COL_LANG_NAME }, null, null, null); } @Override public void onLoadFinished(Loader loader, Cursor data) { _adapter.swapCursor(data); } @Override public void onLoaderReset(Loader loader) { _adapter.swapCursor(null); } @Override public boolean onCreateOptionsMenu(Menu menu) { // Inflate the menu; this adds items to the action bar if it is present. getMenuInflater().inflate(R.menu.main, menu); SearchManager searchManager = (SearchManager) getSystemService(Context.SEARCH_SERVICE); SearchView searchView = (SearchView) menu.findItem(R.id.search) .getActionView(); searchView.setSearchableInfo(searchManager .getSearchableInfo(getComponentName())); return true; } @Override public boolean onOptionsItemSelected(MenuItem item) { int id = item.getItemId(); switch (id) { case R.id.action_add_item: DialogFragment dialogFragment = new AddItemDialogFragment(this); dialogFragment.show(getFragmentManager(), "AddItem"); return true; default: return false; } } }
Custom Add Dialog
See Android Custom Dialog Example to know more about how to create and show a custom dialog.
Few points to note about:
AddItemDialogFragment
class extendsDialogFragment
as we want theAlertDialog
to fall in line withFragment
‘s life-cycle.- In
onCreateDialog()
, we inflate the custom dialog viewlayout.activity_add_item.xml
. The view contains the language field and the ‘Add’ button. - Next,we set the ‘Add’ button’s listener. In this listener, we first verify whether language already exists. If yes, we will show ‘Language already exists message and return.
- If the language is not yet there in the table, we will insert the language.
ContentValues values = new ContentValues(); values.put(DatabaseConstants.COL_LANG_NAME, _langName.getText() .toString()); Uri uri = getActivity().getContentResolver().insert( LanguageContentProvider.CONTENT_URI, values);
- As you can see, it relies on Android’s
ContentResolver
which internally figures out the registeredContentProvider
based on the URL and callsinsert()
method on it. - If the insert i successful, we refresh the list view.
if (LanguageContentProvider.URI_MATCHER.match(uri) == LanguageContentProvider.REQ_ITEM_CODE) { Toast.makeText(getActivity(), "Language " + _langName.getText() + " inserted", Toast.LENGTH_SHORT).show(); _langName.setText(""); _listActivity.getLoaderManager().restartLoader(42, null, ((LoaderCallbacks) _listActivity)); }
- Call
ListActivity
‘sgetLoaderManager()
and then call onrestartLoader()
refresh the list view.
AddItemDialogFragment:
package com.javarticles.android; import android.app.AlertDialog; import android.app.Dialog; import android.app.DialogFragment; import android.app.ListActivity; import android.app.LoaderManager.LoaderCallbacks; import android.content.ContentValues; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.net.Uri; import android.os.Bundle; import android.view.View; import android.view.View.OnClickListener; import android.widget.Button; import android.widget.EditText; import android.widget.Toast; import com.javarticles.android.database.DatabaseConstants; public class AddItemDialogFragment extends DialogFragment implements DatabaseConstants { private EditText _langName; private Button _addButton; private ListActivity _listActivity; private static final String TAG = AddItemDialogFragment.class .getSimpleName(); public AddItemDialogFragment(ListActivity listActivity) { _listActivity = listActivity; } @Override public Dialog onCreateDialog(Bundle savedInstanceState) { View view = getActivity().getLayoutInflater().inflate( R.layout.activity_add_item, null); _langName = (EditText) view.findViewById(R.id.langName); _addButton = (Button) view.findViewById(R.id.buttonAddItem); _addButton.setOnClickListener(new OnClickListener() { @Override public void onClick(View v) { String langName = _langName.getText().toString(); if (alredayExists(langName)) { Toast.makeText( getActivity(), "Language " + _langName.getText() + " already exists!", Toast.LENGTH_SHORT) .show(); return; } ContentValues values = new ContentValues(); values.put(DatabaseConstants.COL_LANG_NAME, _langName.getText() .toString()); Uri uri = getActivity().getContentResolver().insert( LanguageContentProvider.CONTENT_URI, values); if (LanguageContentProvider.URI_MATCHER.match(uri) == LanguageContentProvider.REQ_ITEM_CODE) { Toast.makeText(getActivity(), "Language " + _langName.getText() + " inserted", Toast.LENGTH_SHORT).show(); _langName.setText(""); _listActivity.getLoaderManager().restartLoader(42, null, ((LoaderCallbacks) _listActivity)); } } }); AlertDialog.Builder alertDialogBuilder = new AlertDialog.Builder( getActivity()).setTitle(R.string.title_activity_item_add) .setIcon(R.drawable.add_item_icon) .setPositiveButton(android.R.string.ok, null).setView(view); return alertDialogBuilder.create(); } public boolean alredayExists(String langName) { SQLiteDatabase db = getDbHelper().getReadableDatabase(); Cursor cursor = db.query(TABLE_LANG, new String[] { COL_LANG_ID, COL_LANG_NAME }, COL_LANG_NAME + "=?", new String[] { langName }, null, null, null, null); if (cursor != null) { cursor.moveToFirst(); } if (cursor.getCount() == 0) { return false; } return true; } public SQLiteHelper getDbHelper() { return ((MyApplication) getActivity().getApplication()).getDbHelper(); } }
SQLite Database Helper
We initialize the Database as the application starts.
MyApplication:
package com.javarticles.android; import android.app.Application; import android.util.Log; public class MyApplication extends Application { private static final String TAG = MyApplication.class.getSimpleName(); private SQLiteHelper _dbHelper; @Override public void onCreate() { super.onCreate(); Log.d(TAG, "onCreate my app"); _dbHelper = new SQLiteHelper(this); } public SQLiteHelper getDbHelper() { return _dbHelper; } }
SQLiteOpenHelper
is Android’s SQLite helper class to create database and inserts some languages.
SQLiteHelper:
package com.javarticles.android; import android.content.Context; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import com.javarticles.android.database.DatabaseConstants; public class SQLiteHelper extends SQLiteOpenHelper implements DatabaseConstants { public static final int DB_VERSION = 4; public SQLiteHelper(Context context) { super(context, DB_NAME, null, DB_VERSION); getWritableDatabase(); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL("CREATE TABLE " + TABLE_LANG + "(" + COL_LANG_ID + " INTEGER PRIMARY KEY NOT NULL, " + " " + COL_LANG_NAME + " VARCHAR(50) NOT NULL);"); insertLanguage(db, "Java"); insertLanguage(db, "Perl"); insertLanguage(db, "Python"); insertLanguage(db, "Ruby"); } private static void insertLanguage(SQLiteDatabase db, String language) { db.execSQL("INSERT INTO " + TABLE_LANG + " (" + COL_LANG_NAME + ") VALUES ('" + language + "');"); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.delete(TABLE_LANG, null, null); insertLanguage(db, "Java"); insertLanguage(db, "Perl"); insertLanguage(db, "Python"); insertLanguage(db, "Ruby"); } }
DatabaseConstants:
package com.javarticles.android.database; public interface DatabaseConstants { public static String DB_NAME = "list_example.db"; public static final String TABLE_LANG = "languages"; public static final String COL_LANG_ID = "_id"; public static final String COL_LANG_NAME = "lang_name"; }
Implement insert in ContentProvider
Insert is handled in ContentProvider.insert()
. The values to be inserted come in the form of ContentValues
value object. Next, we open the database in writable mode and then call on SQLiteDatabase.insert()
to insert data.
LanguageContentProvider:
package com.javarticles.android; import android.content.ContentProvider; import android.content.ContentUris; import android.content.ContentValues; import android.content.UriMatcher; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteQueryBuilder; import android.net.Uri; import android.util.Log; import com.javarticles.android.database.DatabaseConstants; public class LanguageContentProvider extends ContentProvider implements DatabaseConstants { private static final String AUTHORITY = "com.javarticles.android.provider.LanguageContentProvider"; public static final Uri CONTENT_URI = Uri.parse("content://" + AUTHORITY + "/" + TABLE_LANG); public static final UriMatcher URI_MATCHER = new UriMatcher(UriMatcher.NO_MATCH); private static final String TAG = LanguageContentProvider.class.getSimpleName(); public static final int REQ_ITEM_CODE = 1; static { URI_MATCHER.addURI(AUTHORITY, TABLE_LANG + "/#", REQ_ITEM_CODE); } @Override public boolean onCreate() { return true; } @Override public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, String sortOrder) { SQLiteQueryBuilder qb = new SQLiteQueryBuilder(); qb.setTables(TABLE_LANG); String orderBy = COL_LANG_NAME + " asc"; Cursor cursor = qb.query(getDbHelper().getReadableDatabase(), new String[] {COL_LANG_ID, COL_LANG_NAME }, null, null, null, null, orderBy); return cursor; } @Override public String getType(Uri uri) { return "vnd.android.cursor.dir/vnd.com.javarticles.android.provider.LanguageContentProvider"; } @Override public Uri insert(Uri uri, ContentValues values) { Uri returnUri = null; SQLiteDatabase db = getDbHelper().getWritableDatabase(); long rowId = db.insert(TABLE_LANG, null, values); if (rowId == -1) { Log.d(TAG, "Insert failed"); returnUri = uri; } else { returnUri = ContentUris.withAppendedId(uri, rowId); Log.d(TAG, "Item inserted, returnURI " + returnUri); getContext().getContentResolver().notifyChange(uri, null); } return returnUri; } @Override public int delete(Uri uri, String selection, String[] selectionArgs) { return 0; } @Override public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) { return 0; } public SQLiteHelper getDbHelper() { return ((MyApplication)getContext()).getDbHelper(); } }
Register ContentProvider
We register ContentProvider
in <provider>
element.
androidManifest.xml:
<?xml version="1.0" encoding="utf-8"?> <manifest xmlns:android="http://schemas.android.com/apk/res/android" package="com.javarticles.android" android:versionCode="1" android:versionName="1.0" > <uses-sdk android:maxSdkVersion="22" android:minSdkVersion="14" android:targetSdkVersion="22" /> <application android:name="com.javarticles.android.MyApplication" android:allowBackup="true" android:icon="@drawable/ic_launcher_sandbox" android:label="@string/app_name" > <meta-data android:name="android.app.default_searchable" android:value="com.javarticles.android.SearchableActivity" /> <activity android:name="com.javarticles.android.LanguageListActivity" > <intent-filter> <action android:name="android.intent.action.MAIN" /> <category android:name="android.intent.category.LAUNCHER" /> </intent-filter> </activity> <activity android:name=".SearchableActivity" > <intent-filter> <action android:name="android.intent.action.SEARCH" /> </intent-filter> <meta-data android:name="android.app.searchable" android:resource="@xml/searchable" /> </activity> <provider android:name=".LanguageContentProvider" android:authorities="com.javarticles.android.provider.LanguageContentProvider" android:exported="false" /> </application> </manifest>
Run the application
Download the source code
This was an example about Android SQLite Database Insert Example.