[Android]Sqliteデータベースプログラミング

[English]

 

Androidでもデータベースにデータを保存したい場合はSqliteを使用します。

 

環境:Android Studio 2.2.1, API 19

 

1.準備

SqLiteOpenHelperを継承したクラスを作成して、データベースを定義します。

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");

    }

  }

}

テーブルのレイアウトはonCreateメソッドの中でCreate Table文で定義します。

アプリのバージョンアップ時にテーブルレイアウトを変更したい場合は、onUpgradeメソッドの中でAlter Table文で定義します。

 

画像(Bitmap)のデータを保存したい場合は、データ型にBLOBを指定します。

 

2.データの新規追加(INSERT)

まず1.準備で作成したクラスのgetWritableDatabaseメソッドを実行して、書き込み可能なSQLiteDatabaseクラスを取得します。このSQLiteDatabaseクラスのInsertOrThrowメソッドを実行するとINSERT文が発行され、データの新規追加ができます。尚、もうひとつInsertメソッドがありますが、こちらはエラー発生時にアプリにエラーが返らない為、使用しない方が良いでしょう。

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());

  }

}

INSERTの対象のフィールド名と設定値の組み合わせはContentValuesで定義します。

Bitmapを保存する場合はbyte型に変換して指定します。

 

3.データの上書き(UPDATE)

UPDATEを実行する場合は、SQLiteDatabaseクラスのupdateメソッドを実行します。

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());

  }

}

設定する値はINSERTと同様にContentValuesで定義します。

検索条件はupdateメソッドの第三引数と第四引数で定義します。

 

4.データの削除(DELETE)

データの削除を実行する場合はSQLiteDatabaseクラスのdeleteメソッドを実行します。

検索条件の指定方法はupdateメソッドと同じです。

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.データの検索(SELECT)

データを検索する時は、まず1.準備で作成したクラスのgetReadableDatabaseメソッドを実行して、読み取り専用のSQLiteDatabaseクラスを作成します。その後、rawQueryメソッドでSELECT文を実行します。

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());

   }

}

 

・サンプルコード全文

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");

      }

    }

  }

}