重要だよねSQLiteDatabase - The blog

さて今回はSQLiteDatabaseを使用してみたいと思います。下記の雑誌の記事を参考に、より簡単にしたものを作っています(ただ、DBにaccessするところはthread化してます)

日経ソフトウエア 2011年 09月号 [雑誌]

作者: 日経ソフトウエア
出版社/メーカー: 日経BP社
発売日: 2011/07/23
メディア: 雑誌
購入: 1人クリック: 46回
この商品を含むブログ (4件) を見る

やりたいこと

SQLiteでDBを作成する
DBにdata挿入
DBからdataを読み込み
SpinnerやListViewを使用してdataを表示

Spinnerで地域名を選ぶとそのAddressを持ったshop名をListViewに表示

実装の要点を下記にまとめます

1. SQLiteOpenHelper拡張classを作成

public class MySQLiteOpenHelper extends SQLiteOpenHelper {

	@Override
	public void onCreate(SQLiteDatabase db) {

	}

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

	}
}

2. SQLiteDatabase objectを使用し、SQLiteOpenHelper#onCreate()でTableを作成

private static final String CREATE_SHOP_TABLE = "create table " + MyDao.SHOP_TABLE_NAME + " (" + 
	MyDao.SHOP_ID + " integer primary key autoincrement, " + 
	MyDao.SHOP_NAME + " text not null, " + 
	MyDao.ADDRESS1 + " text not null, " +
	MyDao.ADDRESS2 + " text not null, " + 
	MyDao.ADDRESS3 + " text not null)";

@Override
public void onCreate(SQLiteDatabase db) {
	db.execSQL(CREATE_SHOP_TABLE);
}

3. DAO(Data Access Object) classにData挿入のmethodを定義

public class MyDao {
	
	public static void insert(SQLiteDatabase db, String shopName, String address1, String address2, String address3){
		ContentValues values = new ContentValues();
		values.put(SHOP_NAME, shopName);
		values.put(ADDRESS1, address1);
		values.put(ADDRESS2, address2);
		values.put(ADDRESS3, address3);
		db.insert(SHOP_TABLE_NAME, null, values);
	}
	
	public static Cursor getAll(SQLiteDatabase db){
		return db.rawQuery(GET_ALL, null);
	}
	
	public static Cursor getAllDistinctAddress2(SQLiteDatabase db){
		return db.rawQuery(GET_ALL_DISTINCT, null);
	}
	
	public static Cursor getAllByAddress2(SQLiteDatabase db, String address2){
		return db.rawQuery(GET_ALL_BY_ADDRESS2, new String[]{address2});
	}
}

4. DAO classにData読み込みのmethodを定義

public class MyDao {

	public static final String SHOP_TABLE_NAME = "shop";
	public static final String SHOP_ID = "_id";
	public static final String SHOP_NAME = "shop_name";
	public static final String ADDRESS1 = "address1";
	public static final String ADDRESS2 = "address2";
	public static final String ADDRESS3 = "address3";
	
	private static final String GET_ALL = "select * from " + SHOP_TABLE_NAME;
	private static final String GET_ALL_DISTINCT = "select distinct " + ADDRESS2 + " from " + SHOP_TABLE_NAME;
	private static final String GET_ALL_BY_ADDRESS2 = "select * from " + SHOP_TABLE_NAME + " where " + ADDRESS2 + " = ?";
	
	public static Cursor getAll(SQLiteDatabase db){
		return db.rawQuery(GET_ALL, null);
	}
	
	public static Cursor getAllDistinctAddress2(SQLiteDatabase db){
		return db.rawQuery(GET_ALL_DISTINCT, null);
	}
	
	public static Cursor getAllByAddress2(SQLiteDatabase db, String address2){
		return db.rawQuery(GET_ALL_BY_ADDRESS2, new String[]{address2});
	}
}

5. Adapterに取得したDataをbindしSpinnerやListViewにset

他、覚えておきたい点まとめます

SQLiteOpenHelper#onCreate()ははじめてDBを作成する時のみ呼ばれる
Activity#startManagingCursor()でCursorのlife cycleをActivityに任せられる
Transaction処理が多い場合SQLiteDatabase#beginTransaction()をcall、SQLiteDatabase#setTransactionSuccessful()でcommit、SQLiteDatabase#endTransaction()でTransaction処理を終了
DBにaccessする時はUI threadとは別のthreadで行うべき
Spinnerには定石がある(ここ参照)

んでは実装です

mainのlayout 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"
    >
    <Spinner
    	android:id="@+id/spinner"
    	android:layout_width="fill_parent"
    	android:layout_height="wrap_content" />
    <!-- 表示するitemがないときに表示される。ListActivityがよろしくやってくれる -->
    <TextView
	android:id="@android:id/empty"  
	android:layout_width="fill_parent" 
	android:layout_height="fill_parent"
	android:text="@string/empty" />
   <ListView
	android:id="@android:id/list"
	android:layout_width="fill_parent" 
	android:layout_height="fill_parent" />
</LinearLayout>

list itemのlayout xml

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
  android:layout_width="fill_parent" android:layout_height="wrap_content"
  android:paddingTop="4dip" android:paddingLeft="7dip"
  android:paddingRight="7dip" android:paddingBottom="10dip">
  <TextView android:layout_width="wrap_content"
    android:layout_height="wrap_content" android:id="@+id/shop"
    android:text="shop" android:layout_alignParentLeft="true"
    android:textSize="25dip" android:ellipsize="marquee"
    android:singleLine="true"></TextView>
</RelativeLayout>

ListActivity拡張class

package com.android.practice.sql;

import java.util.ArrayList;
import java.util.List;

import com.android.practice.sql.db.MyDao;
import com.android.practice.sql.db.MySQLiteOpenHelper;

import android.app.ListActivity;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.os.Handler;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.ArrayAdapter;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;
import android.widget.Spinner;

public class SQLPracticeActivity extends ListActivity {
	
	private static final String TAG = SQLPracticeActivity.class.getSimpleName();
	private static final boolean DEBUG = true;
	
	private MySQLiteOpenHelper mDbHelper;
	private Handler mHandler;
	
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        
        mDbHelper = new MySQLiteOpenHelper(this);
        mHandler = new Handler();
        
        initSpinner();
        initListView(null);
    }
    
    private void initSpinner(){
    	Spinner spinner = (Spinner)findViewById(R.id.spinner);
    	//Spinnerには定石がある
    	//android.R.layout.simple_spinner_itemはPlatformから提供されているSpinnerの標準layout
    	ArrayAdapter<String> adapter = new ArrayAdapter<String>(this, android.R.layout.simple_spinner_item);
    	//android.R.layout.simple_spinner_dropdown_itemもPlatformから提供されているSpinnerが開いた際に表示する標準layout
    	adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
	//SQLiteOpenHelper#getReadableDatabase()でSQLiteDatabase objectを取得
	//Disk fullで書き込み禁止といった問題等がなければSQLiteOpenHelper#getWritableDatabase()と同じobjectを返してくるらしい
    	bindDataToAdapter(mDbHelper.getWritableDatabase(), adapter);
    	spinner.setAdapter(adapter);
    	spinner.setOnItemSelectedListener(new MySpinnerOnItemSelectedListener());
    }
    
    private void bindDataToAdapter(final SQLiteDatabase db, final ArrayAdapter<String> adapter){
	//DBにaccessする時はUI threadとは別のthreadで行うべき
    	new Thread(new Runnable(){
    		public synchronized void run() {
				final List<String> address2List = getAddress2List(db);
				if(mHandler != null){
					mHandler.postAtFrontOfQueue(new Runnable(){
						public void run() {
							adapter.add(getResources().getString(R.string.all));
							for(String address2: address2List){
								if(adapter != null){
									adapter.add(address2);
								}
							}
						}
					});
				}
			}
    	}).start();
    }


    private List<String> getAddress2List(SQLiteDatabase db){
    	List<String> list = new ArrayList<String>();
    	Cursor c = null;
    	
    	try{
    		c = MyDao.getAllDistinctAddress2(db);
    		if(c != null && c.getCount() != 0){
    			int address2Index = c.getColumnIndex(MyDao.ADDRESS2);
    			while(c.moveToNext()){
    				String address2 = c.getString(address2Index);
    				list.add(address2);
    				if(DEBUG)Log.d(TAG, "address2: " + address2);
    			}
    		}
    	}catch(RuntimeException e){
    		e.printStackTrace();
    	}finally{
    		if(c != null){
    			c.close();
    		}
    	}
    	
    	return list;
    }
    
    private void initListView(final String address2){
    	
    	//DBにaccessする時はUI threadとは別のthreadで行うべき
    	new Thread(new Runnable(){
			public synchronized void run() {
				if(mHandler != null && mDbHelper != null){
					Cursor temp = null;
					
					if(address2 == null){
						temp = MyDao.getAll(mDbHelper.getReadableDatabase());
					}else{
						temp = MyDao.getAllByAddress2(mDbHelper.getReadableDatabase(), address2);
					}
					
				        final Cursor c = temp;
			    	        mHandler.postAtFrontOfQueue(new Runnable(){
			    		        public void run() {
			    			        //Activity#startManagingCursor()でCursorのlife cycleをActivityに任せられる
			    			        startManagingCursor(c);
			    			        SimpleCursorAdapter adapter = new SimpleCursorAdapter(
			    					SQLPracticeActivity.this, 
			    					R.layout.shop_list_row, 
			    					c, 
			    					new String[]{MyDao.SHOP_NAME}, 
			    					new int[]{R.id.shop}
			    			        );
			    			        //ListActivity#getListView()でListViewを取得
			    	    	                ListView listView = (ListView)getListView();
		    				       listView.setAdapter(adapter);
			    		       }
			    	        });
				}
			}
    	}).start();
    }
    
    private class MySpinnerOnItemSelectedListener implements OnItemSelectedListener {

		public void onItemSelected(AdapterView<?> parent, View view, int pos, long id) {
			String address2 = (String)parent.getItemAtPosition(pos);
			//下記の方法でもOKらしい
			//String address2 = (String)parent.getAdapter().getItem(pos);
			if(address2.equals(getResources().getString(R.string.all))){
				address2 = null;
			}
			initListView(address2);
		}

		public void onNothingSelected(AdapterView<?> parent) {
			//Do nothing
		}
    }
}

SQLiteOpenHelper拡張class

package com.android.practice.sql.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class MySQLiteOpenHelper extends SQLiteOpenHelper {
	
	private static final String TAG = MySQLiteOpenHelper.class.getSimpleName();
	
	private static final String DB_NAME = "shop.db";
	private static final int DB_VERSION = 1;
	
	private static final String CREATE_SHOP_TABLE = "create table " + MyDao.SHOP_TABLE_NAME + " (" + 
	                                                                  MyDao.SHOP_ID + " integer primary key autoincrement, " + 
	                                                                  MyDao.SHOP_NAME + " text not null, " + 
	                                                                  MyDao.ADDRESS1 + " text not null, " +
	                                                                  MyDao.ADDRESS2 + " text not null, " + 
	                                                                  MyDao.ADDRESS3 + " text not null)";
	
	public MySQLiteOpenHelper(Context context){
		//第三引数のCursorFactoryはNULLでいいらしい
		super(context, DB_NAME, null, DB_VERSION);
	}
	
	public MySQLiteOpenHelper(Context context, String name,
			CursorFactory factory, int version) {
		super(context, name, factory, version);
	}
	
	//SQLiteOpenHelper#onCreate()ははじめてDBを作成する時のみ呼ばれる
	@Override
	public void onCreate(SQLiteDatabase db) {
		//SQLiteOpenHelper#onCreate()でTableを作成
		db.execSQL(CREATE_SHOP_TABLE);
		insertShopData(db);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		//Do nothing for now
	}
	
	private void insertShopData(SQLiteDatabase db){
		//Transaction処理が多い場合SQLiteDatabase#beginTransaction()をcall
		db.beginTransaction();
		MyDao.insert(db, "Super Threeee A", "Iwate", "Mizusawa", "Nishi-Odori");
		MyDao.insert(db, "Super Kanboooon", "Iwate", "Morioka", "Ohara");
		MyDao.insert(db, "Super Fujiiiiya", "Iwate", "Morioka", "Daito");
		//SQLiteDatabase#setTransactionSuccessful()でcommit
		db.setTransactionSuccessful();
		//SQLiteDatabase#endTransaction()でTransaction処理を終了
		db.endTransaction();
	}
}

DAO class

package com.android.practice.sql.db;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

//SQLiteDatabase objectを使用し、DBの操作を行うmethodをDAO(Data Access Object) classに定義
public class MyDao {
	
	private static final String TAG = "MyDao";
	
	public static final String SHOP_TABLE_NAME = "shop";
	public static final String SHOP_ID = "_id";
	public static final String SHOP_NAME = "shop_name";
	public static final String ADDRESS1 = "address1";
	public static final String ADDRESS2 = "address2";
	public static final String ADDRESS3 = "address3";
	
	private static final String GET_ALL = "select * from " + SHOP_TABLE_NAME;
	//distinct制約を使用すると指定されたcolumnの重複行を削除してくれる
	private static final String GET_ALL_DISTINCT = "select distinct " + ADDRESS2 + " from " + SHOP_TABLE_NAME;
	private static final String GET_ALL_BY_ADDRESS2 = "select * from " + SHOP_TABLE_NAME + " where " + ADDRESS2 + " = ?";
	
	//DAO(Data Access Object) classにData挿入のmethodを定義
	public static void insert(SQLiteDatabase db, String shopName, String address1, String address2, String address3){
		ContentValues values = new ContentValues();
		values.put(SHOP_NAME, shopName);
		values.put(ADDRESS1, address1);
		values.put(ADDRESS2, address2);
		values.put(ADDRESS3, address3);
		//ContentValues objectを使用しDataを挿入
		db.insert(SHOP_TABLE_NAME, null, values);
	}
	
	//DAO classにData読み込みのmethodを定義
	public static Cursor getAll(SQLiteDatabase db){
		return db.rawQuery(GET_ALL, null);
	}
	
	//DAO classにData読み込みのmethodを定義	
	public static Cursor getAllDistinctAddress2(SQLiteDatabase db){
		return db.rawQuery(GET_ALL_DISTINCT, null);
	}
	
	//DAO classにData読み込みのmethodを定義	
	public static Cursor getAllByAddress2(SQLiteDatabase db, String address2){
		return db.rawQuery(GET_ALL_BY_ADDRESS2, new String[]{address2});
	}
}

と、こんな感じです。

SQL文、ここ参考にさせていただきました

HashTag #Java, #Android, #SQLiteOpenHelper, #SQLiteDatabase, #Spinner, #ListView