[Android]Sqlite Database Programming

[日本語]

 

You can use Sqlite for saving data on Android.

 

Environment: Android Studio 2.2.1, API 19

 

1.Preparation

Make class extended SqLiteOpenHelper and declare database layout.

private final static String DB_NAME = "sample1.db";

private final static String DB_TABLE = "SampleTable";

private final static int DB_VERSION = 1;

 

private class SampleDBHelper extends SQLiteOpenHelper {

  public SampleDBHelper(Context context) {

    super(context, DB_NAME, null, DB_VERSION);

  }

 

  @Override

  public void onCreate(SQLiteDatabase db) {

    db.execSQL("create table if not exists " + DB_TABLE + "(id text primary key,name text,type integer,image BLOB)");

  }

 

  @Override

  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    if (newVersion == 2) {

      db.execSQL("alter table " + DB_TABLE + " add column newField integer default 0");

    }

  }

}

You can define table layout to use "Create Table" in onCreate method.

You can change table layout to use "Alter Table" in onUpgrade method, when application version is up.

 

When you want to save Bitmap data, define BLOB data type.

 

2.How to Insert data

Use getWritableDatabase method of class which is made by SQLiteOpenHelper, and you can get writable SQLiteDatabase class.
When you execute InsertOrThrow method of SQLiteDatabase class, and new data is inserted in Sqlite.

And you'd better not use Insert method of SQLiteDatabase class, because this method don't return Exception class when error occur.

private ImageView imageView1;

private Button buttonInsert;

RelativeLayout relativeLayout;

Context context;

 

@Override

public void onCreate(Bundle bundle) {

  super.onCreate(bundle);

  context = getApplicationContext();

  relativeLayout = new RelativeLayout(context);

  setContentView(relativeLayout);

 

  imageView1 = new ImageView(context);

  Bitmap bmp = BitmapFactory.decodeResource(getResources(),R.drawable.edit);

  imageView1.setImageBitmap(bmp);

  imageView1.setAdjustViewBounds(true);

  RelativeLayout.LayoutParams param1 = new RelativeLayout.LayoutParams(300, 300);

  param1.leftMargin = 10;

  param1.topMargin = 10;

  param1.addRule(RelativeLayout.ALIGN_TOP);

  relativeLayout.addView(imageView1,param1);

 

  buttonInsert = new Button(context);

  buttonInsert.setText("Insert");

  buttonInsert.setOnClickListener(new View.OnClickListener() {

    @Override

    public void onClick(View v) {

      execInsert();

    }

  });

  RelativeLayout.LayoutParams param2 = new RelativeLayout.LayoutParams(300, 150);

  param2.leftMargin = 10;

  param2.topMargin = 320;

  param2.addRule(RelativeLayout.ALIGN_TOP);

  relativeLayout.addView(buttonInsert,param2);

}

 

private void execInsert(){

  try{

    SampleDBHelper dbHelper = new SampleDBHelper(context);

    SQLiteDatabase db = dbHelper.getWritableDatabase();

 

    ContentValues values = new ContentValues();

    values.put("id","0000000001");

    values.put("name","Name-1");

    values.put("type",1);

 

    Bitmap bmp = ((BitmapDrawable)imageView1.getDrawable()).getBitmap();

    if (bmp != null) {

      ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();

      bmp.compress(Bitmap.CompressFormat.PNG, 100, byteArrayOutputStream);

      byte[] bytes = byteArrayOutputStream.toByteArray();

      values.put("image", bytes);

    }

    db.insertOrThrow(DB_TABLE,null,values);

  }catch(Exception e){

    Log.e("touchInsertButton",e.toString());

  }

}

Use ContentValues to set field names and values.

If you want to save Bitmap Data, you must change Bitmap to byte data type.

 

3.How to update data.

Use update method of SQLiteDatabase class, you can update data in Sqlite.

private ImageView imageView1;

private Button buttonUpdate;

RelativeLayout relativeLayout;

Context context;

 

@Override

public void onCreate(Bundle bundle) {

  super.onCreate(bundle);

  context = getApplicationContext();

 

  relativeLayout = new RelativeLayout(context);

  setContentView(relativeLayout);

 

  imageView1 = new ImageView(context);

  Bitmap bmp = BitmapFactory.decodeResource(getResources(),R.drawable.edit);

  imageView1.setImageBitmap(bmp);

  imageView1.setAdjustViewBounds(true);

  RelativeLayout.LayoutParams param1 = new RelativeLayout.LayoutParams(300, 300);

  param1.leftMargin = 10;

  param1.topMargin = 10;

  param1.addRule(RelativeLayout.ALIGN_TOP);

  relativeLayout.addView(imageView1,param1);

 

  buttonUpdate = new Button(context);

  buttonUpdate.setText("Update");

  buttonUpdate.setOnClickListener(new View.OnClickListener() {

    @Override

    public void onClick(View v) {

     execUpdate();

    }

  });

  RelativeLayout.LayoutParams param4 = new RelativeLayout.LayoutParams(300, 150);

  param4.leftMargin = 10;

  param4.topMargin = 640;

  param4.addRule(RelativeLayout.ALIGN_TOP);

  relativeLayout.addView(buttonUpdate,param4);

 }

 

private void execUpdate(){

  try{

    SampleDBHelper dbHelper = new SampleDBHelper(context);

    SQLiteDatabase db = dbHelper.getWritableDatabase();

 

    ContentValues values = new ContentValues();

    values.put("name"," Name-2");

 

    Bitmap bmp = ((BitmapDrawable)imageView1.getDrawable()).getBitmap();

    if (bmp != null) {

      ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();

      bmp.compress(Bitmap.CompressFormat.PNG, 100, byteArrayOutputStream);

      byte[] bytes = byteArrayOutputStream.toByteArray();

      values.put("image", bytes);

    }

 

    String whereId = "id = ? and type = ?";

    String whereArgs[] = new String[2];

    whereArgs[0] = "0000000001";

    whereArgs[1] = "1";

 

    db.update(DB_TABLE,values,whereId,whereArgs);

  }catch(Exception e){

    Log.e("touchUpdateButton",e.toString());

  }

}

You can use ContentValues to set field names and values like inserting data.

You can set your search criteria to the third argument and the fourth argument of update method.

 

4.How to delete data

Use delete method of SQLiteDatabase class, you can delete data in Sqlite.

Method of setting your search criteria is the same as updating data.

private ImageView imageView1;

private Button buttonDelete;

RelativeLayout relativeLayout;

Context context;

 

@Override

public void onCreate(Bundle bundle) {

  super.onCreate(bundle);

  context = getApplicationContext();

 

  relativeLayout = new RelativeLayout(context);

  setContentView(relativeLayout);

 

  imageView1 = new ImageView(context);

  Bitmap bmp = BitmapFactory.decodeResource(getResources(),R.drawable.edit);

  imageView1.setImageBitmap(bmp);

  imageView1.setAdjustViewBounds(true);

  RelativeLayout.LayoutParams param1 = new RelativeLayout.LayoutParams(300, 300);

  param1.leftMargin = 10;

  param1.topMargin = 10;

  param1.addRule(RelativeLayout.ALIGN_TOP);

  relativeLayout.addView(imageView1,param1);

 

  buttonDelete = new Button(context);

  buttonDelete.setText("Delete");

  buttonDelete.setOnClickListener(new View.OnClickListener() {

    @Override

    public void onClick(View v) {

     execDelete();

    }

  });

  RelativeLayout.LayoutParams param5 = new RelativeLayout.LayoutParams(300, 150);

  param5.leftMargin = 10;

  param5.topMargin = 800;

  param5.addRule(RelativeLayout.ALIGN_TOP);

  relativeLayout.addView(buttonDelete,param5);

 }

 

private void execDelete(){

  try{

    SampleDBHelper dbHelper = new SampleDBHelper(context);

    SQLiteDatabase db = dbHelper.getWritableDatabase();

 

    String whereClause = "id = ?";

    String whereArgs[] = new String[1];

    whereArgs[0] = "0000000001";

 

    db.delete(DB_TABLE,whereClause,whereArgs);

  }catch(Exception e){

    Log.e("touchDeleteButton",e.toString());

  }

}

 

5.How to select data

When you select data in Sqlite, execute getReadableDatabase method of class which is made by SQLiteOpenHelper, and get SQLiteDatabase class(read only).

After, Use rawQuery method of SQLiteDatabase class, and you can select data in Sqlite.

private ArrayList _id = new ArrayList();

private ArrayList _name = new ArrayList();

private ArrayList _image = new ArrayList();

 

private void execSelect(){

  try{

    byte[] dataValue;

    Bitmap bmp = null;

    _id.clear();

    _name.clear();

    _image.clear();

 

    SampleDBHelper dbHelper = new SampleDBHelper(context);

    SQLiteDatabase db = dbHelper.getReadableDatabase();

 

    String sql = "select id,name,image from " + DB_TABLE + " where type=1 order by id";

 

    Cursor c = db.rawQuery(sql, null);

 

    int recordCount = c.getCount();

    if (recordCount > 0) {

      c.moveToFirst();

      for (int i = 0; i < recordCount; i++) {

        _id.add(c.getString(0)); //id

        _name.add(c.getString(1)); //name

        dataValue = c.getBlob(2); //image

        if (dataValue != null) {

          bmp = BitmapFactory.decodeByteArray(dataValue, 0, dataValue.length);

          _image.add(bmp); } c.moveToNext();

        }

      }

      c.close();

   }catch(Exception e){

     Log.e("touchSelectButton",e.toString());

   }

}

 

・Sample Code

package net.studioks.sample1;

 

import android.app.Activity;

import android.content.ContentValues;

import android.content.Context;

import android.database.Cursor;

import android.database.sqlite.SQLiteDatabase;

import android.database.sqlite.SQLiteOpenHelper;

import android.graphics.Bitmap;

import android.graphics.BitmapFactory;

import android.graphics.drawable.BitmapDrawable;

import android.os.Bundle;

import android.util.Log;

import android.view.View;

import android.widget.Button;

import android.widget.ImageView;

import android.widget.RelativeLayout;

import java.io.ByteArrayOutputStream;

import java.util.ArrayList;

 

public class sample1 extends Activity {

  private ImageView imageView1;

  private Button buttonInsert;

  private Button buttonSelect;

  private Button buttonUpdate;

  private Button buttonDelete;

  RelativeLayout relativeLayout;

  Context context;

 

  @Override

  public void onCreate(Bundle bundle) {

    super.onCreate(bundle);

    context = getApplicationContext();

 

    relativeLayout = new RelativeLayout(context);

    setContentView(relativeLayout);

 

    imageView1 = new ImageView(context);

    Bitmap bmp = BitmapFactory.decodeResource(getResources(),R.drawable.edit);

    imageView1.setImageBitmap(bmp);

    imageView1.setAdjustViewBounds(true);

    RelativeLayout.LayoutParams param1 = new RelativeLayout.LayoutParams(300, 300);

    param1.leftMargin = 10;

    param1.topMargin = 10;

    param1.addRule(RelativeLayout.ALIGN_TOP);

    relativeLayout.addView(imageView1,param1);

 

    buttonInsert = new Button(context);

    buttonInsert.setText("Insert");

    buttonInsert.setOnClickListener(new View.OnClickListener() {

      @Override

      public void onClick(View v) {

        execInsert();

      }

    });

    RelativeLayout.LayoutParams param2 = new RelativeLayout.LayoutParams(300, 150);

    param2.leftMargin = 10;

    param2.topMargin = 320;

    param2.addRule(RelativeLayout.ALIGN_TOP);

    relativeLayout.addView(buttonInsert,param2);

 

    buttonSelect = new Button(context);

    buttonSelect.setText("Select");

    buttonSelect.setOnClickListener(new View.OnClickListener() {

      @Override

      public void onClick(View v) {

        execSelect();

      }

    });

    RelativeLayout.LayoutParams param3 = new RelativeLayout.LayoutParams(300, 150);

    param3.leftMargin = 10;

    param3.topMargin = 480;

    param3.addRule(RelativeLayout.ALIGN_TOP);

    relativeLayout.addView(buttonSelect,param3);

 

    buttonUpdate = new Button(context);

    buttonUpdate.setText("Update");

    buttonUpdate.setOnClickListener(new View.OnClickListener() {

      @Override

      public void onClick(View v) {

        execUpdate();

      }

    });

    RelativeLayout.LayoutParams param4 = new RelativeLayout.LayoutParams(300, 150);

    param4.leftMargin = 10;

    param4.topMargin = 640;

    param4.addRule(RelativeLayout.ALIGN_TOP);

    relativeLayout.addView(buttonUpdate,param4);

 

    buttonDelete = new Button(context);

    buttonDelete.setText("Delete");

    buttonDelete.setOnClickListener(new View.OnClickListener() {

      @Override

      public void onClick(View v) {

        execDelete();

      }

    });

    RelativeLayout.LayoutParams param5 = new RelativeLayout.LayoutParams(300, 150);

    param5.leftMargin = 10;

    param5.topMargin = 800;

    param5.addRule(RelativeLayout.ALIGN_TOP);

    relativeLayout.addView(buttonDelete,param5);

  }

 

  private void execInsert(){

    try{

      SampleDBHelper dbHelper = new SampleDBHelper(context);

      SQLiteDatabase db = dbHelper.getWritableDatabase();

 

      ContentValues values = new ContentValues();

      values.put("id","0000000001"); values.put("name","Name-1");

      values.put("type",1);

 

      Bitmap bmp = ((BitmapDrawable)imageView1.getDrawable()).getBitmap();

      if (bmp != null) {

        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();

        bmp.compress(Bitmap.CompressFormat.PNG, 100, byteArrayOutputStream);

        byte[] bytes = byteArrayOutputStream.toByteArray();

        values.put("image", bytes);

      }

      db.insertOrThrow(DB_TABLE,null,values);

    }catch(Exception e){

      Log.e("touchInsertButton",e.toString());

    }

  }

 

  private void execUpdate(){

    try{

      SampleDBHelper dbHelper = new SampleDBHelper(context);

      SQLiteDatabase db = dbHelper.getWritableDatabase();

 

      ContentValues values = new ContentValues();

      values.put("name"," Name-2");

 

      Bitmap bmp = ((BitmapDrawable)imageView1.getDrawable()).getBitmap();

      if (bmp != null) {

        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();

        bmp.compress(Bitmap.CompressFormat.PNG, 100, byteArrayOutputStream);

        byte[] bytes = byteArrayOutputStream.toByteArray();

        values.put("image", bytes);

      }

 

      String whereId = "id = ? and type = ?";

      String whereArgs[] = new String[2];

      whereArgs[0] = "0000000001";

      whereArgs[1] = "1";

 

      db.update(DB_TABLE,values,whereId,whereArgs);

    }catch(Exception e){

      Log.e("touchUpdateButton",e.toString());

    }

  }

 

  private void execDelete(){

    try{

      SampleDBHelper dbHelper = new SampleDBHelper(context);

      SQLiteDatabase db = dbHelper.getWritableDatabase();

 

      String whereClause = "id = ?";

      String whereArgs[] = new String[1];

      whereArgs[0] = "0000000001";

 

      db.delete(DB_TABLE,whereClause,whereArgs);

    }catch(Exception e){

      Log.e("touchDeleteButton",e.toString());

    }

  }

 

  private ArrayList _id = new ArrayList();

  private ArrayList _name = new ArrayList();

  private ArrayList _image = new ArrayList();

 

  private void execSelect(){

    try{

      byte[] dataValue; Bitmap bmp = null; _id.clear();

      _name.clear();

      _image.clear();

 

      SampleDBHelper dbHelper = new SampleDBHelper(context);

      SQLiteDatabase db = dbHelper.getReadableDatabase();

 

      String sql = "select id,name,image from " + DB_TABLE + " where type=1 order by id";

 

      Cursor c = db.rawQuery(sql, null);

      int recordCount = c.getCount();

      if (recordCount > 0) {

        c.moveToFirst();

        for (int i = 0; i < recordCount; i++) {

          _id.add(c.getString(0)); //id

          _name.add(c.getString(1)); //name

          dataValue = c.getBlob(2); //image

          if (dataValue != null) {

            bmp = BitmapFactory.decodeByteArray(dataValue, 0, dataValue.length);

            _image.add(bmp);

          }

          c.moveToNext();

        }

      }

      c.close();

    }catch(Exception e){

      Log.e("touchSelectButton",e.toString());

    }

  }

 

  private final static String DB_NAME = "sample1.db";

  private final static String DB_TABLE = "SampleTable";

  private final static int DB_VERSION = 1;

 

  private class SampleDBHelper extends SQLiteOpenHelper {

    public SampleDBHelper(Context context) {

      super(context, DB_NAME, null, DB_VERSION);

    }

 

    @Override

    public void onCreate(SQLiteDatabase db) {

      db.execSQL("create table if not exists " + DB_TABLE + "(id text primary key,name text,type integer,image BLOB)");

    }

 

    @Override

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

      if (newVersion == 2) {

        db.execSQL("alter table " + DB_TABLE + " add column newField integer default 0");

      }

    }

  }

}