Sunday, 25 August 2013

Simple SQLiteHelper example in android



SQLiteHelper.jar

This jar has the following functions.

1. public SQLiteHelper(Context context, String dbname, CursorFactory factory, int version)
This constructor is used to initialize SQLiteOpenHelper class.

2. public void createTable(String table_name, String[] fields, String[] types)
This method is used to create table with fields. Total number of fields and types must be equal.

3. public ArrayList<HashMap<String, Object>> getFields(String table_name)
This method will return all fields with their types as ArrayList.

4. public String insertData(String table_name, String[] fields, String[] data)
This method used to insert record in our table. This will return a String as
Transaction successfully completed
if the transaction completed. Otherwise it will return exception.

5. public ArrayList<HashMap<String, Object>> getAllData(String table_name)
This method used to get all record from our table as arraylist.

6. public ArrayList<HashMap<String, Object>> getSelectedData(String table_name, String fields[], String[] WhereClolmn, String[] WhereClolmnType, String[] data)
This method will return selected data as ArrayList

7. public String UpdateData(String table_name, String[] fields, String[] data, String WHereClassField, String WhereClassFieldData)
This method used to update record in our table. This will return a String as
Transaction Successfully Updated
if the transaction completed. Otherwise it will return exception.

8. public String deleteRecords(String table_name, String field_name, String[] data)
This method used to delete record in our table. This will return a String as
Number of rows deleted


9. public void addFields(String table_name, String field_name, String type, String defaultValue)
This method used to add new field in our table

10. public String executeQuery(String query)
This method used to execute the dynamic query

Sample program using this jar

MainActivity.java

package com.example.myproject;

import com.example.dbhelper.SQLiteHelper;

import android.os.Bundle;
import android.app.Activity;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

public class MainActivity extends Activity {

 SQLiteHelper helper;
 Context context;
 String DBName;
 CursorFactory factory = null;
 int version = 1;

 @Override
 protected void onCreate(Bundle savedInstanceState) {
  super.onCreate(savedInstanceState);
  setContentView(R.layout.activity_main);
  context = this;
  DBName = "MyDBname";
  helper = new SQLiteHelper(context, DBName, factory, version);
  helper.createTable("MyFirstTable", new String[] { "id", "name" },
    new String[] { "INTEGER PRIMARY KEY AUTOINCREMENT", "TEXT" });
  System.out.println(helper.getFields("MyFirstTable"));
  System.out.println(helper.insertData("MyFirstTable",
    new String[] { "name" }, new String[] { "Gunaseelan" }));
  System.out.println(helper.getAllData("MyFirstTable"));
  System.out.println(helper.UpdateData("MyFirstTable",
    new String[] { "name" }, new String[] { "Guna" }, "name",
    "Gunaseelan"));
  System.out.println(helper.getAllData("MyFirstTable"));
  System.out.println(helper.deleteRecords("MyFirstTable", "name",
    new String[] { "Guna" }));
  System.out.println(helper.getAllData("MyFirstTable"));
  
  helper.addFields("MyFirstTable", "mark", "NUMBER", "");
  
  System.out.println(helper.insertData("MyFirstTable", new String[] {
    "name", "mark" }, new String[] { "Gunaseelan", "98" }));
  System.out.println(helper.getSelectedData("MyFirstTable", new String[] {
    "name", "mark" }, new String[] { "name", "mark" },
    new String[] { "Text", "Number" }, new String[] { "Gunaseelan",
      "98" }));
  System.out.println(helper.executeQuery("Select * from MyFirstTable"));
 }
}
Logcat output

[{Type=INTEGER, Filed=id}, {Type=TEXT, Filed=name}, {Type=NUMBER, Filed=mark}]
Transaction successfully completed
[{id=4, mark=, name=Gunaseelan}]
Transaction Successfully Updated
[{id=4, mark=, name=Guna}]
Number of rows deleted 1
Transaction successfully completed
[{mark=98, name=Gunaseelan}]
I hope this post will help you. kindly share your feedback and suggestion as comment.



Thank You