Android Tutorial - Database : SQLiteOpenHelper

 extends SQLiteOpenHelper to Manage your database

package app.test;

import java.util.HashMap;

import android.app.AlertDialog;
import android.app.ListActivity;
import android.content.ContentProvider;
import android.content.ContentUris;
import android.content.ContentValues;
import android.content.Context;
import android.content.DialogInterface;
import android.content.UriMatcher;
import android.content.res.Resources;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteQueryBuilder;
import android.hardware.SensorManager;
import android.net.Uri;
import android.os.Bundle;
import android.provider.BaseColumns;
import android.text.TextUtils;
import android.view.ContextMenu;
import android.view.LayoutInflater;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.EditText;
import android.widget.ListAdapter;
import android.widget.SimpleCursorAdapter;

class Provider extends ContentProvider {
  private static final String DATABASE_NAME = "constants.db";
  private static final int CONSTANTS = 1;
  private static final int CONSTANT_ID = 2;
  private static final UriMatcher MATCHER;
  private static HashMap<String, String> CONSTANTS_LIST_PROJECTION;

  public static final class Constants implements BaseColumns {
    public static final Uri CONTENT_URI = Uri
        .parse("content://com.commonsware.android.constants.Provider/constants");
    public static final String DEFAULT_SORT_ORDER = "title";
    public static final String TITLE = "title";
    public static final String VALUE = "value";
  }

  static {
    MATCHER = new UriMatcher(UriMatcher.NO_MATCH);
    MATCHER.addURI("com.commonsware.android.constants.Provider",
        "constants", CONSTANTS);
    MATCHER.addURI("com.commonsware.android.constants.Provider",
        "constants/#", CONSTANT_ID);

    CONSTANTS_LIST_PROJECTION = new HashMap<String, String>();
    CONSTANTS_LIST_PROJECTION.put(Provider.Constants._ID,
        Provider.Constants._ID);
    CONSTANTS_LIST_PROJECTION.put(Provider.Constants.TITLE,
        Provider.Constants.TITLE);
    CONSTANTS_LIST_PROJECTION.put(Provider.Constants.VALUE,
        Provider.Constants.VALUE);
  }

  public String getDbName() {
    return (DATABASE_NAME);
  }

  public int getDbVersion() {
    return (1);
  }

  private class DatabaseHelper extends SQLiteOpenHelper {
    public DatabaseHelper(Context context) {
      super(context, DATABASE_NAME, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
      Cursor c = db
          .rawQuery(
              "SELECT name FROM sqlite_master WHERE type='table' AND name='constants'",
              null);

      try {
        if (c.getCount() == 0) {
          db.execSQL("CREATE TABLE constants (_id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, value REAL);");

          ContentValues cv = new ContentValues();

          cv.put(Constants.TITLE, "Gravity, Death Star I");
          cv.put(Constants.VALUE, SensorManager.GRAVITY_DEATH_STAR_I);
          db.insert("constants", getNullColumnHack(), cv);

          cv.put(Constants.TITLE, "Gravity, Earth");
          cv.put(Constants.VALUE, SensorManager.GRAVITY_EARTH);
          db.insert("constants", getNullColumnHack(), cv);

          cv.put(Constants.TITLE, "Gravity, Jupiter");
          cv.put(Constants.VALUE, SensorManager.GRAVITY_JUPITER);
          db.insert("constants", getNullColumnHack(), cv);

          cv.put(Constants.TITLE, "Gravity, Mars");
          cv.put(Constants.VALUE, SensorManager.GRAVITY_MARS);
          db.insert("constants", getNullColumnHack(), cv);

          cv.put(Constants.TITLE, "Gravity, Mercury");
          cv.put(Constants.VALUE, SensorManager.GRAVITY_MERCURY);
          db.insert("constants", getNullColumnHack(), cv);

          cv.put(Constants.TITLE, "Gravity, Moon");
          cv.put(Constants.VALUE, SensorManager.GRAVITY_MOON);
          db.insert("constants", getNullColumnHack(), cv);

          cv.put(Constants.TITLE, "Gravity, Neptune");
          cv.put(Constants.VALUE, SensorManager.GRAVITY_NEPTUNE);
          db.insert("constants", getNullColumnHack(), cv);

          cv.put(Constants.TITLE, "Gravity, Pluto");
          cv.put(Constants.VALUE, SensorManager.GRAVITY_PLUTO);
          db.insert("constants", getNullColumnHack(), cv);

          cv.put(Constants.TITLE, "Gravity, Saturn");
          cv.put(Constants.VALUE, SensorManager.GRAVITY_SATURN);
          db.insert("constants", getNullColumnHack(), cv);

          cv.put(Constants.TITLE, "Gravity, Sun");
          cv.put(Constants.VALUE, SensorManager.GRAVITY_SUN);
          db.insert("constants", getNullColumnHack(), cv);

          cv.put(Constants.TITLE, "Gravity, The Island");
          cv.put(Constants.VALUE, SensorManager.GRAVITY_THE_ISLAND);
          db.insert("constants", getNullColumnHack(), cv);

          cv.put(Constants.TITLE, "Gravity, Uranus");
          cv.put(Constants.VALUE, SensorManager.GRAVITY_URANUS);
          db.insert("constants", getNullColumnHack(), cv);

          cv.put(Constants.TITLE, "Gravity, Venus");
          cv.put(Constants.VALUE, SensorManager.GRAVITY_VENUS);
          db.insert("constants", getNullColumnHack(), cv);
        }
      } finally {
        c.close();
      }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      android.util.Log.w("Constants",
          "Upgrading database, which will destroy all old data");
      db.execSQL("DROP TABLE IF EXISTS constants");
      onCreate(db);
    }
  }

  private SQLiteDatabase db;

  @Override
  public boolean onCreate() {
    db = (new DatabaseHelper(getContext())).getWritableDatabase();

    return (db == null) ? false : true;
  }

  @Override
  public Cursor query(Uri url, String[] projection, String selection,
      String[] selectionArgs, String sort) {
    SQLiteQueryBuilder qb = new SQLiteQueryBuilder();

    qb.setTables(getTableName());

    if (isCollectionUri(url)) {
      qb.setProjectionMap(getDefaultProjection());
    } else {
      qb.appendWhere(getIdColumnName() + "="
          + url.getPathSegments().get(1));
    }

    String orderBy;

    if (TextUtils.isEmpty(sort)) {
      orderBy = getDefaultSortOrder();
    } else {
      orderBy = sort;
    }

    Cursor c = qb.query(db, projection, selection, selectionArgs, null,
        null, orderBy);
    c.setNotificationUri(getContext().getContentResolver(), url);
    return c;
  }

  @Override
  public String getType(Uri url) {
    if (isCollectionUri(url)) {
      return (getCollectionType());
    }

    return (getSingleType());
  }

  @Override
  public Uri insert(Uri url, ContentValues initialValues) {
    long rowID;
    ContentValues values;

    if (initialValues != null) {
      values = new ContentValues(initialValues);
    } else {
      values = new ContentValues();
    }

    if (!isCollectionUri(url)) {
      throw new IllegalArgumentException("Unknown URL " + url);
    }

    for (String colName : getRequiredColumns()) {
      if (values.containsKey(colName) == false) {
        throw new IllegalArgumentException("Missing column: " + colName);
      }
    }

    populateDefaultValues(values);

    rowID = db.insert(getTableName(), getNullColumnHack(), values);
    if (rowID > 0) {
      Uri uri = ContentUris.withAppendedId(getContentUri(), rowID);
      getContext().getContentResolver().notifyChange(uri, null);
      return uri;
    }

    throw new SQLException("Failed to insert row into " + url);
  }

  @Override
  public int delete(Uri url, String where, String[] whereArgs) {
    int count;
    long rowId = 0;

    if (isCollectionUri(url)) {
      count = db.delete(getTableName(), where, whereArgs);
    } else {
      String segment = url.getPathSegments().get(1);
      rowId = Long.parseLong(segment);
      count = db.delete(
          getTableName(),
          getIdColumnName()
              + "="
              + segment
              + (!TextUtils.isEmpty(where) ? " AND (" + where
                  + ')' : ""), whereArgs);
    }

    getContext().getContentResolver().notifyChange(url, null);
    return count;
  }

  @Override
  public int update(Uri url, ContentValues values, String where,
      String[] whereArgs) {
    int count;

    if (isCollectionUri(url)) {
      count = db.update(getTableName(), values, where, whereArgs);
    } else {
      String segment = url.getPathSegments().get(1);
      count = db.update(
          getTableName(),
          values,
          getIdColumnName()
              + "="
              + segment
              + (!TextUtils.isEmpty(where) ? " AND (" + where
                  + ')' : ""), whereArgs);
    }

    getContext().getContentResolver().notifyChange(url, null);
    return count;
  }

  private boolean isCollectionUri(Uri url) {
    return (MATCHER.match(url) == CONSTANTS);
  }

  private HashMap<String, String> getDefaultProjection() {
    return (CONSTANTS_LIST_PROJECTION);
  }

  private String getTableName() {
    return ("constants");
  }

  private String getIdColumnName() {
    return ("_id");
  }

  private String getDefaultSortOrder() {
    return ("title");
  }

  private String getCollectionType() {
    return ("vnd.android.cursor.dir/vnd.commonsware.constant");
  }

  private String getSingleType() {
    return ("vnd.android.cursor.item/vnd.commonsware.constant");
  }

  private String[] getRequiredColumns() {
    return (new String[] { "title" });
  }

  private void populateDefaultValues(ContentValues values) {
    Long now = Long.valueOf(System.currentTimeMillis());
    Resources r = Resources.getSystem();

    if (values.containsKey(Provider.Constants.VALUE) == false) {
      values.put(Provider.Constants.VALUE, 0.0f);
    }
  }

  private String getNullColumnHack() {
    return ("title");
  }

  private Uri getContentUri() {
    return (Provider.Constants.CONTENT_URI);
  }
}

public class Test extends ListActivity {
  private static final int ADD_ID = Menu.FIRST + 1;
  private static final int EDIT_ID = Menu.FIRST + 2;
  private static final int DELETE_ID = Menu.FIRST + 3;
  private static final int CLOSE_ID = Menu.FIRST + 4;
  private static final String[] PROJECTION = new String[] {
      Provider.Constants._ID, Provider.Constants.TITLE,
      Provider.Constants.VALUE };
  private Cursor constantsCursor;

  @Override
  public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);

    constantsCursor = managedQuery(Provider.Constants.CONTENT_URI,
        PROJECTION, null, null, null);

    ListAdapter adapter = new SimpleCursorAdapter(this, R.layout.row,
        constantsCursor, new String[] { Provider.Constants.TITLE,
            Provider.Constants.VALUE }, new int[] { R.id.title,
            R.id.value });

    setListAdapter(adapter);
    registerForContextMenu(getListView());
  }

  @Override
  public void onDestroy() {
    super.onDestroy();

    constantsCursor.close();
  }

  @Override
  public boolean onCreateOptionsMenu(Menu menu) {
    menu.add(Menu.NONE, ADD_ID, Menu.NONE, "Add").setIcon(R.drawable.icon)
        .setAlphabeticShortcut('a');
    menu.add(Menu.NONE, CLOSE_ID, Menu.NONE, "Close")
        .setIcon(R.drawable.icon).setAlphabeticShortcut('c');

    return (super.onCreateOptionsMenu(menu));
  }

  @Override
  public boolean onOptionsItemSelected(MenuItem item) {
    switch (item.getItemId()) {
    case ADD_ID:
      add();
      return (true);

    case CLOSE_ID:
      finish();
      return (true);
    }

    return (super.onOptionsItemSelected(item));
  }

  @Override
  public void onCreateContextMenu(ContextMenu menu, View v,
      ContextMenu.ContextMenuInfo menuInfo) {
    menu.add(Menu.NONE, DELETE_ID, Menu.NONE, "Delete")
        .setIcon(R.drawable.icon).setAlphabeticShortcut('d');
  }

  @Override
  public boolean onContextItemSelected(MenuItem item) {
    switch (item.getItemId()) {
    case DELETE_ID:
      AdapterView.AdapterContextMenuInfo info = (AdapterView.AdapterContextMenuInfo) item
          .getMenuInfo();

      delete(info.id);
      return (true);
    }

    return (super.onOptionsItemSelected(item));
  }

  private void add() {
    LayoutInflater inflater = LayoutInflater.from(this);
    View addView = inflater.inflate(R.layout.add_edit, null);
    final DialogWrapper wrapper = new DialogWrapper(addView);

    new AlertDialog.Builder(this)
        .setTitle(R.string.add_title)
        .setView(addView)
        .setPositiveButton(R.string.ok,
            new DialogInterface.OnClickListener() {
              public void onClick(DialogInterface dialog,
                  int whichButton) {
                processAdd(wrapper);
              }
            })
        .setNegativeButton(R.string.cancel,
            new DialogInterface.OnClickListener() {
              public void onClick(DialogInterface dialog,
                  int whichButton) {
                // ignore, just dismiss
              }
            }).show();
  }

  private void delete(final long rowId) {
    if (rowId > 0) {
      new AlertDialog.Builder(this)
          .setTitle(R.string.delete_title)
          .setPositiveButton(R.string.ok,
              new DialogInterface.OnClickListener() {
                public void onClick(DialogInterface dialog,
                    int whichButton) {
                  processDelete(rowId);
                }
              })
          .setNegativeButton(R.string.cancel,
              new DialogInterface.OnClickListener() {
                public void onClick(DialogInterface dialog,
                    int whichButton) {
                  // ignore, just dismiss
                }
              }).show();
    }
  }

  private void processAdd(DialogWrapper wrapper) {
    ContentValues values = new ContentValues(2);

    values.put(Provider.Constants.TITLE, wrapper.getTitle());
    values.put(Provider.Constants.VALUE, wrapper.getValue());

    getContentResolver().insert(Provider.Constants.CONTENT_URI, values);
    constantsCursor.requery();
  }

  private void processDelete(long rowId) {
    Uri uri = ContentUris.withAppendedId(Provider.Constants.CONTENT_URI,
        rowId);
    getContentResolver().delete(uri, null, null);
    constantsCursor.requery();
  }

  class DialogWrapper {
    EditText titleField = null;
    EditText valueField = null;
    View base = null;

    DialogWrapper(View base) {
      this.base = base;
      valueField = (EditText) base.findViewById(R.id.value);
    }

    String getTitle() {
      return (getTitleField().getText().toString());
    }

    float getValue() {
      return (new Float(getValueField().getText().toString())
          .floatValue());
    }

    private EditText getTitleField() {
      if (titleField == null) {
        titleField = (EditText) base.findViewById(R.id.title);
      }

      return (titleField);
    }

    private EditText getValueField() {
      if (valueField == null) {
        valueField = (EditText) base.findViewById(R.id.value);
      }

      return (valueField);
    }
  }
}

//main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    >
<TextView  
    android:layout_width="fill_parent" 
    android:layout_height="wrap_content" 
    android:text="Hello World, ConstantsBrowser"
    />
</LinearLayout>

//add_edit.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:layout_width="fill_parent"
    android:layout_height="wrap_content"
    >
  <LinearLayout
      android:orientation="horizontal"
      android:layout_width="fill_parent"
      android:layout_height="wrap_content"
      >
    <TextView
        android:text="Display Name:"
        android:layout_width="wrap_content" 
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        />
    <EditText
        android:id="@+id/title"
        android:layout_width="fill_parent" 
        android:layout_height="wrap_content" 
        android:layout_alignParentRight="true"
        />
  </LinearLayout>
  <LinearLayout
      android:orientation="horizontal"
      android:layout_width="fill_parent"
      android:layout_height="wrap_content"
      >
    <TextView
        android:text="Value:"
        android:layout_width="wrap_content" 
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        />
    <EditText
        android:id="@+id/value"
        android:layout_width="fill_parent" 
        android:layout_height="wrap_content" 
        android:layout_alignParentRight="true"
        />
  </LinearLayout>
</LinearLayout>

//row.xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="horizontal"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    >
  <TextView
      android:id="@+id/title"
      android:layout_width="wrap_content" 
      android:layout_height="wrap_content"
      android:layout_alignParentLeft="true"
      />
  <TextView
      android:id="@+id/value"
      android:layout_width="wrap_content" 
      android:layout_height="wrap_content" 
      android:layout_alignParentRight="true"
      />
</RelativeLayout>

//strings.xml

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <string name="app_name">ConstantsBrowser</string>
    <string name="ok">OK</string>
    <string name="cancel">Cancel</string>
    <string name="add_title">Add Constant</string>
    <string name="delete_title">Delete Constant: Are You Sure?</string>
</resources>

extends SQLiteOpenHelper to create your own data helper

  


import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
 
public class DataHelper extends SQLiteOpenHelper {
 
  private static final String TABLE_UPDATE = "updatedb";
  private static final String CREATE_BDD = "CREATE TABLE " + TABLE_UPDATE + 
        " (script_name TEXT PRIMARY KEY, " +
         "script_description TEXT, website TEXT, script_ver TEXT, " +
         "script_author TEXT, username TEXT, " +
         "date_first_upload TEXT, date_last_update TEXT, " +
         "phone_brand TEXT, phone_model TEXT, " +
         "phone_firm_ver TEXT, phone_android_sdk TEXT," +
         "category TEXT, script_downloads TEXT, " +
         "zip_attached TEXT, execution TEXT, " +
         "md5sum_int TEXT, md5sum_ext TEXT, link_ext TEXT, link_txt TEXT, md5sum_txt TEXT);"; 
      
      
  public DataHelper(Context context, String name, CursorFactory factory, int version) {
    super(context, name, factory, version);
  }
 
  @Override
  public void onCreate(SQLiteDatabase db) {
    //on cr la table  partir de la requte crite dans la variable CREATE_BDD
    db.execSQL(CREATE_BDD);
    
  }
 
  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    //On peut fait ce qu'on veut ici moi j'ai dcid de supprimer la table et de la recrer
    //comme a lorsque je change la version les id repartent de 0
    db.execSQL("DROP TABLE " + TABLE_UPDATE + ";");
    onCreate(db);
  }
 
}

extends SQLiteOpenHelper

  
//package nicolasb1.additifs;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;

public class DataBaseHelper extends SQLiteOpenHelper {
  
  //The Android's default system path of your application database.
    private static String DB_PATH = "/data/data/nicolasb1.additifs/databases/";
 
    private static String DB_NAME = "additifs.db";
 
    public static final String KEY_ID = "_id";
    public static final String KEY_EID = "eid";
    public static final String KEY_COLOR = "color";
    public static final String KEY_NAME = "name";
    public static final String KEY_TYPE = "type";
    public static final String KEY_NAME2 = "name2";
    public static final String KEY_TYPE2 = "type2";
    
    private static final int DATABASE_VERSION = 10;
    
    private SQLiteDatabase myDataBase; 
 
    private final Context myContext;
 
    /**
     * Constructor
     * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
     * @param context
     */
    public DataBaseHelper(Context context) {
 
      super(context, DB_NAME, null, DATABASE_VERSION);
        this.myContext = context;
    }  
    public SQLiteDatabase getdatabase(){
        return this.myDataBase;
     } 
  /**
     * Creates a empty database on the system and rewrites it with your own database.
     * */
    public void createDataBase() throws IOException{
 
      boolean dbExist = checkDataBase();
 
      if(dbExist){
        //Do nothing
        this.getWritableDatabase();
      }
      
      dbExist = checkDataBase();
      
      if(!dbExist){
 
        //By calling this method and empty database will be created into the default system path
               //of your application so we are gonna be able to overwrite that database with our database.
          this.getReadableDatabase();
 
          try {
            
          copyDataBase();
 
        } catch (IOException e) {
 
            throw new Error("Error copying database");
 
          }
      }
      this.close();
    }
 
    /**
     * 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.NO_LOCALIZED_COLLATORS);
        
            int i = checkDB.getVersion();
            int a = i;
 
      }catch(SQLiteException e){
 
        //database does't exist yet.
 
      }
 
      if(checkDB != null){
 
        checkDB.close();
 
      }
 
      return checkDB != null ? true : false;
    }
 
 
    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();
 
    }
 
    public void openDataBase() throws SQLException{
 
      //Open the database
        String myPath = DB_PATH + DB_NAME;
      myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.NO_LOCALIZED_COLLATORS);
 
    }
 
    @Override
  public synchronized void close() {
 
          if(myDataBase != null)
            myDataBase.close();
 
          super.close();
 
  }
 
  @Override
  public void onCreate(SQLiteDatabase db) {
 
  }
 
  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    myContext.deleteDatabase(DB_NAME); 
  }

  public Cursor fetchByEID() {
    // TODO Auto-generated method stub
    return myDataBase.query("additifs", new String[] {KEY_ID, KEY_EID, KEY_COLOR, KEY_NAME, KEY_TYPE, KEY_NAME2, KEY_TYPE2}, null, null, null, null, "_id");
  }
  public Cursor fetchByName() {
    // TODO Auto-generated method stub
    return myDataBase.query("additifs", new String[] {KEY_ID, KEY_EID, KEY_COLOR, KEY_NAME, KEY_TYPE, KEY_NAME2, KEY_TYPE2}, null, null, null, null, "name");
  } 
  public Cursor fetchByEID2() {
    // TODO Auto-generated method stub
    return myDataBase.query("additifs", new String[] {KEY_ID, KEY_EID, KEY_COLOR, KEY_NAME, KEY_TYPE, KEY_NAME2, KEY_TYPE2}, null, null, null, null, "_id");
  }
  public Cursor fetchByName2() {
    // TODO Auto-generated method stub
    return myDataBase.query("additifs", new String[] {KEY_ID, KEY_EID, KEY_COLOR, KEY_NAME, KEY_TYPE, KEY_NAME2, KEY_TYPE2}, null, null, null, null, "name2");
  } 
  public Cursor fetchORANGE() {
    // TODO Auto-generated method stub
    return myDataBase.query("additifs", new String[] {KEY_ID, KEY_EID, KEY_COLOR, KEY_NAME, KEY_TYPE}, "color = 'ORANGE'", null, null, null, "_id");
  } 
  public Cursor fetchRED() {
    // TODO Auto-generated method stub
    return myDataBase.query("additifs", new String[] {KEY_ID, KEY_EID, KEY_COLOR, KEY_NAME, KEY_TYPE}, "color = 'RED'", null, null, null, "_id");
  } 
}

Create, delete, update

     

package app.test;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.util.Log;
import android.widget.Toast;

class DBAdapter {
  public static final String KEY_ROWID = "_id";
  public static final String KEY_NAME = "name";
  public static final String KEY_EMAIL = "email";
  private static final String TAG = "DBAdapter";

  private static final String DATABASE_NAME = "MyDB";
  private static final String DATABASE_TABLE = "contacts";
  private static final int DATABASE_VERSION = 2;

  private static final String DATABASE_CREATE = "create table contacts (_id integer primary key autoincrement, "
      + "name text not null, email text not null);";

  private final Context context;

  private DatabaseHelper DBHelper;
  private SQLiteDatabase db;

  public DBAdapter(Context ctx) {
    this.context = ctx;
    DBHelper = new DatabaseHelper(context);
  }

  private static class DatabaseHelper extends SQLiteOpenHelper {
    DatabaseHelper(Context context) {
      super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
      try {
        db.execSQL(DATABASE_CREATE);
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
      Log.w(TAG, oldVersion + " to " + newVersion
          + ", which will destroy all old data");
      db.execSQL("DROP TABLE IF EXISTS contacts");
      onCreate(db);
    }
  }

  public DBAdapter open() throws SQLException {
    db = DBHelper.getWritableDatabase();
    return this;
  }

  public void close() {
    DBHelper.close();
  }

  public long insertContact(String name, String email) {
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_NAME, name);
    initialValues.put(KEY_EMAIL, email);
    return db.insert(DATABASE_TABLE, null, initialValues);
  }

  public boolean deleteContact(long rowId) {
    return db.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
  }

  public Cursor getAllContacts() {
    return db.query(DATABASE_TABLE, new String[] { KEY_ROWID, KEY_NAME,
        KEY_EMAIL }, null, null, null, null, null);
  }

  public Cursor getContact(long rowId) throws SQLException {
    Cursor mCursor = db.query(true, DATABASE_TABLE, new String[] {
        KEY_ROWID, KEY_NAME, KEY_EMAIL }, KEY_ROWID + "=" + rowId,
        null, null, null, null, null);
    if (mCursor != null) {
      mCursor.moveToFirst();
    }
    return mCursor;
  }

  public boolean updateContact(long rowId, String name, String email) {
    ContentValues args = new ContentValues();
    args.put(KEY_NAME, name);
    args.put(KEY_EMAIL, email);
    return db.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
  }
}

public class Test extends Activity {
  @Override
  public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    try {
      String destPath = "/data/data/" + getPackageName()
          + "/databases/MyDB";
      File f = new File(destPath);
      if (!f.exists()) {
        CopyDB(getBaseContext().getAssets().open("mydb"),
            new FileOutputStream(destPath));
      }
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    }

    DBAdapter db = new DBAdapter(this);
    // ---add a contact---
    db.open();
    long id = db.insertContact("Wei-Meng Lee",
        "weimenglee@learn2develop.net");
    id = db.insertContact("Mary Jackson", "mary@jackson.com");
    db.close();
    // ---get all contacts---
    db.open();
    Cursor c = db.getAllContacts();
    if (c.moveToFirst()) {
      do {
        DisplayContact(c);
      } while (c.moveToNext());
    }
    db.close();

    // ---get a contact---
    db.open();
    c = db.getContact(2);
    if (c.moveToFirst())
      DisplayContact(c);
    else
      Toast.makeText(this, "No contact found", Toast.LENGTH_LONG).show();
    db.close();

    // ---update contact---
    db.open();
    if (db.updateContact(1, "Wei-Meng Lee", "weimenglee@gmail.com"))
      Toast.makeText(this, "Update successful.", Toast.LENGTH_LONG)
          .show();
    else
      Toast.makeText(this, "Update failed.", Toast.LENGTH_LONG).show();
    db.close();

    // ---delete a contact---
    db.open();
    if (db.deleteContact(1))
      Toast.makeText(this, "Delete successful.", Toast.LENGTH_LONG)
          .show();
    else
      Toast.makeText(this, "Delete failed.", Toast.LENGTH_LONG).show();
    db.close();

  }

  public void CopyDB(InputStream inputStream, OutputStream outputStream)
      throws IOException {
    byte[] buffer = new byte[1024];
    int length;
    while ((length = inputStream.read(buffer)) > 0) {
      outputStream.write(buffer, 0, length);
    }
    inputStream.close();
    outputStream.close();
  }

  public void DisplayContact(Cursor c) {
    Toast.makeText(
        this,
        "id: " + c.getString(0) + "\n" + "Name: " + c.getString(1)
            + "\n" + "Email:  " + c.getString(2), Toast.LENGTH_LONG)
        .show();
  }
}
//main.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    >
<TextView  
    android:layout_width="fill_parent" 
    android:layout_height="wrap_content" 
    android:text="@string/hello"
    />
</LinearLayout>