Android SQLite Insert Example

0

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:

  1. AddItemDialogFragment class extends DialogFragment as we want the AlertDialog to fall in line with Fragment‘s life-cycle.
  2. In onCreateDialog(), we inflate the custom dialog view layout.activity_add_item.xml. The view contains the language field and the ‘Add’ button.
  3. 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.
  4. 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);
  5. As you can see, it relies on Android’s ContentResolver which internally figures out the registered ContentProvider based on the URL and calls insert() method on it.
  6. 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));
    				}
    
  7. Call ListActivity‘s getLoaderManager() and then call on restartLoader() 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

Language List Page with Add Button

Language List Page with Add Button

Add Language Dialog

Add Language Dialog

Enter Language and click on Add

Enter Language and click on Add

Add Language Dialog

Language Inserted

Cannot insert same language again

Cannot insert same language again

Language added to the list

Language added to the list

Download the source code

This was an example about Android SQLite Database Insert Example.

You can download the source code here: sqliteInsertExample.zip
Share.

Comments are closed.