[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");
}
}
}
}
Write a comment
Alex Nordeen (Tuesday, 13 August 2019 15:58)
Hi,
I am Alex editor at Guru99. There is 69% chance you will not open this email considering its automated cold mail.
But I must highlight I enjoyed your content at https://www.studioks.net/jp-androiddb/
I could not help noticing you linke to https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase . I have created a more-in depth article at https://www.guru99.com/sqlite-database.html
Can you link to us? I did be happy to share your page with our 25k Facebook/Twitter/Linkedin Followers as a thank you.
Best,
[Alex]
Alex Nordeen (Friday, 23 August 2019 14:48)
Hi,
A quick follow up to check if you got my email below
I know I am intruding your mailbox and I do not want to be bother if you are not interested.
======================Original Message========================
Hi,
I am Alex editor at Guru99. There is 69% chance you will not open this email considering its automated cold mail.
But I must highlight I enjoyed your content at https://www.studioks.net/jp-androiddb/
I could not help noticing you linke to https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase . I have created a more-in depth article at https://www.guru99.com/sqlite-database.html
Can you link to us? I did be happy to share your page with our 25k Facebook/Twitter/Linkedin Followers as a thank you.
Best,
[Alex]
Alex Nordeen (Thursday, 05 September 2019 14:38)
I reached out previously and hadn’t heard back from you yet. This tells me a few things:
1) You're being chased by a T-rex and haven't had time to respond.
2) You aren't interested.
3) You're interested but haven't had a time to respond.
Whichever one it is, please let me know as I am getting worried! Please respond 1,2, or 3. I do not want to be a bother.
======================Original Message========================
Hi,
I am Alex editor at Guru99. There is 69% chance you will not open this email considering its automated cold mail.
But I must highlight I enjoyed your content at https://www.studioks.net/jp-androiddb/
I could not help noticing you linke to https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase . I have created a more-in depth article at https://www.guru99.com/sqlite-database.html
Can you link to us? I did be happy to share your page with our 25k Facebook/Twitter/Linkedin Followers as a thank you.
Best,
[Alex]
Alex Nordeen (Friday, 13 September 2019 15:13)
Hi,
I've reached out several times but haven't heard back, which tells me one of three things:
1. You’re interested in giving link back, but haven't had a chance to get back to me yet.
2. You're not interested and want me to stop emailing.
3. You've fallen and can't get up – in that case let me know and I'll call 911.
Can you please reply with 1, 2 or 3? I don't want to be a bother.
======================Original Message========================
Hi,
I am Alex editor at Guru99. There is 69% chance you will not open this email considering its automated cold mail.
But I must highlight I enjoyed your content at https://www.studioks.net/jp-androiddb/
I could not help noticing you linke to https://developer.android.com/reference/android/database/sqlite/SQLiteDatabase . I have created a more-in depth article at https://www.guru99.com/sqlite-database.html
Can you link to us? I did be happy to share your page with our 25k Facebook/Twitter/Linkedin Followers as a thank you.
Best,
[Alex]