DataBase-CRUD Tutorial for Android

Here is the DataBase CRUD Tutorial for Android.

First of all create the SQLite DB and paste in the assets folder.

See Blog for creation of SQLite DB http://mfarhan133.wordpress.com/2010/10/24/working-with-sqlite-db-tutorial/

Use DBAdapter Class for DB connectivity and CRUD manipulation.

Its functions details are commented in class itself.

You can download its complete source code from here.

Select

First of all on the start of application(Splash) call createDataBase() function to create and copy database from assets folder(Select in my case). After first time creation of DB used DB will be now on “/data/data/mypackagename/database/” path.

  public ArrayList getUsers(){

		DBAdapter dbAdapter=DBAdapter.getDBAdapterInstance(this);
		try {
			dbAdapter.createDataBase();
		} catch (IOException e) {
			Log.i("*** select ",e.getMessage());
		}
    	dbAdapter.openDataBase();
		String query="SELECT * FROM user;";
		ArrayList> stringList = dbAdapter.selectRecordsFromDBList(query, null);
		dbAdapter.close();

		ArrayList usersList = new ArrayList();
		for (int i = 0; i < stringList.size(); i++) {
			ArrayList list = stringList.get(i);
			UserBO user = new UserBO();
			try {
				user.id = Integer.parseInt(list.get(0));
				user.name = list.get(1);
				user.age = Long.parseLong(list.get(2));
			} catch (Exception e) {
				Log.i("***" + Select.class.toString(), e.getMessage());
			}
			usersList.add(user);
		}
		return usersList;
	}

Insert

DBAdapter dbAdapter = DBAdapter.getDBAdapterInstance(Insert.this);
dbAdapter.openDataBase();

ContentValues initialValues = new ContentValues();
initialValues.put("name", etName.getText().toString());
initialValues.put("age", etAge.getText().toString());
long n = dbAdapter.insertRecordsInDB("user", null, initialValues);
Toast.makeText(Insert.this, "new row inserted with id = " + n, Toast.LENGTH_SHORT).show();

Update

DBAdapter dbAdapter = DBAdapter.getDBAdapterInstance(Update.this);
dbAdapter.openDataBase();

ContentValues initialValues = new ContentValues();
initialValues.put("name", etName.getText().toString());
initialValues.put("age", etAge.getText().toString());
String id = etId.getText().toString();
String [] strArray = {""+id};
long n = dbAdapter.updateRecordsInDB("user", initialValues, "id=?", strArray);

Toast.makeText(Update.this, n+" rows updated", Toast.LENGTH_SHORT).show();

Delete

DBAdapter dbAdapter = DBAdapter.getDBAdapterInstance(Delete.this);
dbAdapter.openDataBase();

String id = etId.getText().toString();
String [] strArray = {""+id};
long n = dbAdapter.deleteRecordInDB("user", "id = ?", strArray);
Toast.makeText(Delete.this, n+" rows effected", Toast.LENGTH_SHORT).show();

DBAdapter.java

public class DBAdapter extends SQLiteOpenHelper {

	private static String DB_PATH = "";
	private static final String DB_NAME = "user.sqlite";
	private SQLiteDatabase myDataBase;
	private final Context myContext;

	private static DBAdapter mDBConnection;

	/**
	 * Constructor
	 * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
	 * @param context
	 */
	private DBAdapter(Context context) {
		super(context, DB_NAME, null, 1);
		this.myContext = context;
		DB_PATH = "/data/data/"
				+ context.getApplicationContext().getPackageName()
				+ "/databases/";
		// The Android's default system path of your application database is
		// "/data/data/mypackagename/databases/"
	}

	/**
	 * getting Instance
	 * @param context
	 * @return DBAdapter
	 */
	public static synchronized DBAdapter getDBAdapterInstance(Context context) {
		if (mDBConnection == null) {
			mDBConnection = new DBAdapter(context);
		}
		return mDBConnection;
	}

	/**
	 * Creates an empty database on the system and rewrites it with your own database.
	 **/
	public void createDataBase() throws IOException {
		boolean dbExist = checkDataBase();
		if (dbExist) {
			// do nothing - database already exist
		} else {
			// By calling following method
			// 1) an empty database will be created into the default system path of your application
			// 2) than we overwrite that database with our database.
			this.getReadableDatabase();
			try {
				copyDataBase();
			} catch (IOException e) {
				throw new Error("Error copying database");
			}
		}
	}

	/**
	 * Check if the database already exist to avoid re-copying the file each time you open the application.
	 * @return true if it exists, false if it doesn't
	 */
	private boolean checkDataBase() {
		SQLiteDatabase checkDB = null;
		try {
			String myPath = DB_PATH + DB_NAME;
			checkDB = SQLiteDatabase.openDatabase(myPath, null,
					SQLiteDatabase.OPEN_READONLY);

		} catch (SQLiteException e) {
			// database does't exist yet.
		}
		if (checkDB != null) {
			checkDB.close();
		}
		return checkDB != null ? true : false;
	}

	/**
	 * Copies your database from your local assets-folder to the just created
	 * empty database in the system folder, from where it can be accessed and
	 * handled. This is done by transfering bytestream.
	 * */
	private void copyDataBase() throws IOException {
		    // Open your local db as the input stream
		InputStream myInput = myContext.getAssets().open(DB_NAME);
		    // Path to the just created empty db
		String outFileName = DB_PATH + DB_NAME;
		    // Open the empty db as the output stream
		OutputStream myOutput = new FileOutputStream(outFileName);
		    // transfer bytes from the inputfile to the outputfile
		byte[] buffer = new byte[1024];
		int length;
		while ((length = myInput.read(buffer)) > 0) {
			myOutput.write(buffer, 0, length);
		}
		    // Close the streams
		myOutput.flush();
		myOutput.close();
		myInput.close();
	}

	/**
	 * Open the database
	 * @throws SQLException
	 */
	public void openDataBase() throws SQLException {
		String myPath = DB_PATH + DB_NAME;
		myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
	}

	/**
	 * Close the database if exist
	 */
	@Override
	public synchronized void close() {
		if (myDataBase != null)
			myDataBase.close();
		super.close();
	}

	/**
	 * Call on creating data base for example for creating tables at run time
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
	}

	/**
	 * can used for drop tables then call onCreate(db) function to create tables again - upgrade
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	// ----------------------- CRUD Functions ------------------------------

	/**
	 * This function used to select the records from DB.
	 * @param tableName
	 * @param tableColumns
	 * @param whereClase
	 * @param whereArgs
	 * @param groupBy
	 * @param having
	 * @param orderBy
	 * @return A Cursor object, which is positioned before the first entry.
	 */
	public Cursor selectRecordsFromDB(String tableName, String[] tableColumns,
			String whereClase, String whereArgs[], String groupBy,
			String having, String orderBy) {
		return myDataBase.query(tableName, tableColumns, whereClase, whereArgs,
				groupBy, having, orderBy);
	}

	/**
	 * select records from db and return in list
	 * @param tableName
	 * @param tableColumns
	 * @param whereClase
	 * @param whereArgs
	 * @param groupBy
	 * @param having
	 * @param orderBy
	 * @return ArrayList>
	 */
	public ArrayList> selectRecordsFromDBList(String tableName, String[] tableColumns,
			String whereClase, String whereArgs[], String groupBy,
			String having, String orderBy) {

		ArrayList> retList = new ArrayList>();
	      ArrayList list = new ArrayList();
	      Cursor cursor = myDataBase.query(tableName, tableColumns, whereClase, whereArgs,
					groupBy, having, orderBy);
	      if (cursor.moveToFirst()) {
	         do {
	        	 list = new ArrayList();
	        	 for(int i=0; i 0;
	}

	/**
	 * This function used to update the Record in DB.
	 * @param tableName
	 * @param initialValues
	 * @param whereClause
	 * @param whereArgs
	 * @return 0 in case of failure otherwise return no of row(s) are updated
	 */
	public int updateRecordsInDB(String tableName,
			ContentValues initialValues, String whereClause, String whereArgs[]) {
		return myDataBase.update(tableName, initialValues, whereClause, whereArgs);
	}

	/**
	 * This function used to delete the Record in DB.
	 * @param tableName
	 * @param whereClause
	 * @param whereArgs
	 * @return 0 in case of failure otherwise return no of row(s) are deleted.
	 */
	public int deleteRecordInDB(String tableName, String whereClause,
			String[] whereArgs) {
		return myDataBase.delete(tableName, whereClause, whereArgs);
	}

	// --------------------- Select Raw Query Functions ---------------------

	/**
	 * apply raw Query
	 * @param query
	 * @param selectionArgs
	 * @return Cursor
	 */
	public Cursor selectRecordsFromDB(String query, String[] selectionArgs) {
		return myDataBase.rawQuery(query, selectionArgs);
	}

	/**
	 * apply raw query and return result in list
	 * @param query
	 * @param selectionArgs
	 * @return ArrayList>
	 */
	public ArrayList> selectRecordsFromDBList(String query, String[] selectionArgs) {
	      ArrayList> retList = new ArrayList>();
	      ArrayList list = new ArrayList();
	      Cursor cursor = myDataBase.rawQuery(query, selectionArgs);
	      if (cursor.moveToFirst()) {
	         do {
	        	 list = new ArrayList();
	        	 for(int i=0; i<cursor.getColumnCount(); i++){
	        		 list.add( cursor.getString(i) );
	        	 }
	        	 retList.add(list);
	         } while (cursor.moveToNext());
	      }
	      if (cursor != null && !cursor.isClosed()) {
	         cursor.close();
	      }
	      return retList;
	   }

}

Related Blog http://www.screaming-penguin.com/node/7742

About these ads
This entry was posted in Android, Tutorials and tagged , , . Bookmark the permalink.

21 Responses to DataBase-CRUD Tutorial for Android

  1. Super helpful mate, been struggling with getting my head around android SQLite and I have been struggling to grasp it for some reason (weird since I know SQL) it’s mainly just the android overhead I don’t get to be honest but looking over your adapter class it makes a few things a little more clear to me so thanks for the tutorial mate, cheers.

  2. rojy says:

    how to add new fields in the table ,updated UI and created new table using SQLite Database but its not working

  3. Farhan says:

    –Buddy its not a good programming approach to create your DB structure at run time.
    –”public void onCreate(SQLiteDatabase db)” function is used to create DB Structure at run time. Write your creation queries there.
    – Keep in mind that your DB present in assets folder will not used. It will be copied to “/data/data/mypackagename/databases/” path then it will be used. If you changed your DB structure then call copyDataBase() function externally by making it public. But for final app you should not call it externally.
    – Debug the code line by line and undertand the whole story by reading comments.

  4. rojy says:

    Thanks for reply ,have an issue created a database(newdata.sqlite) and copied it to assets and changed String DB_NAME = “newdata.sqlite”; in DBAdapter class but application is force closed

  5. Farhan says:

    –If you r using Eclipse See on Log on which line error comes. It will be written in red color something like this “com.your packagename.classname —”.
    –Otherwise debug line by line step into all functions and find the line on which app crash.

  6. rojy says:

    the problem was String query=”SELECT * FROM user;”; changed “user” to my database table name now working ,thanks

  7. Kumar says:

    hello sir,
    I’m new to android
    i created database and tables and inserted some values. I want to retrieve values whenever user enter some value in the editview textbox.

    It check the value in the database and if it correct i want to go to next page.

    could anyone please give any sample code or suggest ideas..?

    thanks in advance
    kumar.

  8. Farhan says:

    kumar i think you should download the sample code from link given in the page.
    I think further you want to implement the autoComplete TextBox

    http://mfarhan133.wordpress.com/2010/10/01/autocomplete-text-view/

    see this blog …

    • Kumar says:

      Thanks for your reply.
      But I created database and tables and inserted some values.
      For an example, User want to enter a username and password in the EditView in my apps. I want to get these two strings and compare in to database. If it is correct i want to go next page by using intent. I don’t need autoComplete TextBox.
      could you please help me to solve this problem?

      • Farhan says:

        public Cursor selectRecordsFromDB(String query, String[] selectionArgs) {
        return myDataBase.rawQuery(query, selectionArgs);
        }

        use this function of DBAdapter class

        Cursor cursor = myDataBase.rawQuery(query, selectionArgs);

        if cursor.moveToFirst() = true then means user have data which is pointed by cursor means athenticted.
        But if cursor.moveToFirst() = false means unathenticated

  9. zOro says:

    I want to use your code and i think i a still missing something,

    the db name is user.sqlite

    private static final String DB_NAME = "user.sqlite";

    and when selecting :

    String query="SELECT * FROM user;";

    do you use DB_NAME as table name,
    what if i want to use more then one table?

  10. usman says:

    how to create db in sd card?

  11. Farhan says:

    Usman,, the DB you put in asstets folder copied into SD Card by calling function
    private void copyDataBase() of DBAdapter class.
    It call only one time, once db copied , SD Card database used in future.

  12. rojy says:

    i have created an Employee database using CRUD example, how to get the row ID of an Employee(abc) so that all the details about that employee can be retrieved .

  13. Farhan says:

    Download the source code and see the update activity in which first data retrieve from the database against data’s id and the perform updation operation.

  14. Rajdeep says:

    Thanks yar

  15. M!ck says:

    Hey Farhan,

    great piece of helpful code! Thanks for that.

    M!ck

  16. hendra1 says:

    Thanks Farhan for your great tutorial CRUD.

    But I still have a problem, on method

    public ArrayList getUsers(){
    ….
    }

    when i want to query inner join 2 table
    table : User and Hobby
    How do I change it? could you give me some code snippet?

    Thanks before..

  17. engiguide says:

    Thanks a lot….
    it is work for me …
    and again thanks that it will help me to develop my future android app……

    keep posting this type article …

  18. Pramod.Waichal says:

    Thanks Farhan this helped me lot..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s