Android/db/基本

db接続サンプル

• DatabaseHelper.java

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
public class DatabaseHelper extends SQLiteOpenHelper {
   static final String DATABASE_NAME = "mytest.db";
   static final int DATABASE_VERSION = 1;
   public DatabaseHelper(Context context) {
       super(context, DATABASE_NAME, null, DATABASE_VERSION);
   }
   @Override
   public void onCreate(SQLiteDatabase db) {
       ImageDao.createTable(db);
   }
   @Override
   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
       ImageDao.dropCreateTable(db);
   }
   public SQLiteDatabase open() {
       return super.getWritableDatabase();
   }
   public void close(){
       super.close();
   }
}

• ImageDao.java

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
public class ImageDao {
    private Context mContext;
   private SQLiteDatabase mDb;
   public static final String TABLE_NAME = "images";
   public static final String COL_ID = "_id";
   public static final String COL_IMAGE_ID = "image_id";
   public static final String COL_URL = "url";
   public static final String COL_LASTUPDATE = "lastupdate";
   public ImageDao(Context context) {
       mContext = context;
   }
   public void connection() {
       DatabaseHelper helper = new DatabaseHelper(mContext);
       mDb = helper.open();
   }
   public void close() {
       mDb.close();
   }
   public static void createTable(SQLiteDatabase db) {
       db.execSQL(
       "CREATE TABLE " + TABLE_NAME + " ("
       + COL_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
       + COL_IMAGE_ID + " TEXT,"
       + COL_URL + " TEXT NOT NULL,"
       + COL_LASTUPDATE + " TEXT NOT NULL);");
   }
   public static void dropCreateTable(SQLiteDatabase db) {
       db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
       createTable(db);
   }
   public boolean deleteAll(){
       return mDb.delete(TABLE_NAME, null, null) > 0;
   }
   public boolean deleteById(int id){
       String[] selectArgs = new String[]{ String.valueOf(id) };
       return mDb.delete(TABLE_NAME, COL_ID + "=?", selectArgs) > 0;
   }
   public ArrayList<HashMap<String, String>> findAll() {
       ArrayList<HashMap<String, String>> images = new ArrayList<HashMap<String, String>>();
       Cursor c =  mDb.query(TABLE_NAME, null, null, null, null, null, COL_ID);
       if (c.moveToFirst()) {
           do {
               HashMap<String, String> image = new HashMap<String, String>();
               image.put(ImageDao.COL_ID,  String.valueOf(c.getInt(c.getColumnIndex(ImageDao.COL_ID))));
               image.put(ImageDao.COL_IMAGE_ID,  c.getString(c.getColumnIndex(ImageDao.COL_IMAGE_ID)));
               image.put(ImageDao.COL_URL,  c.getString(c.getColumnIndex(ImageDao.COL_URL)));
               image.put(ImageDao.COL_LASTUPDATE,  c.getString(c.getColumnIndex(ImageDao.COL_LASTUPDATE)));
               images.add(image);
           } while(c.moveToNext());
       }
       c.close();
       return images;
   }
   public HashMap<String, String> findRowById(int id) {
       String[] selectArgs = new String[]{ String.valueOf(id) };
       Cursor c =  mDb.query(TABLE_NAME, null, COL_ID + "=?", selectArgs, null, null, null);
       HashMap<String, String> image = new HashMap<String, String>();
       if (c.moveToFirst()) {
           if (c.getCount() > 0 ) {
               image.put(ImageDao.COL_ID,  String.valueOf(c.getInt(c.getColumnIndex(ImageDao.COL_ID))));
               image.put(ImageDao.COL_IMAGE_ID,  c.getString(c.getColumnIndex(ImageDao.COL_IMAGE_ID)));
               image.put(ImageDao.COL_URL,  c.getString(c.getColumnIndex(ImageDao.COL_URL)));
               image.put(ImageDao.COL_LASTUPDATE,  c.getString(c.getColumnIndex(ImageDao.COL_LASTUPDATE)));
           }
       }
       c.close();
       return image;
   }
   public HashMap<String, String> findRowByImageId(String imageId) {
       String[] selectArgs = new String[]{ imageId };
       Cursor c =  mDb.query(TABLE_NAME, null, COL_IMAGE_ID + "=?", selectArgs, null, null, null);
       HashMap<String, String> image = new HashMap<String, String>();
       if (c.moveToFirst()) {
           if (c.getCount() > 0 ) {
               image.put(ImageDao.COL_ID,  String.valueOf(c.getInt(c.getColumnIndex(ImageDao.COL_ID))));
               image.put(ImageDao.COL_IMAGE_ID,  c.getString(c.getColumnIndex(ImageDao.COL_IMAGE_ID)));
               image.put(ImageDao.COL_URL,  c.getString(c.getColumnIndex(ImageDao.COL_URL)));
               image.put(ImageDao.COL_LASTUPDATE,  c.getString(c.getColumnIndex(ImageDao.COL_LASTUPDATE)));
           }
       }
       c.close();
       return image;
   }
   public void insertImageIdUrl(String imageId, String url){
       HashMap<String, String> image = new HashMap<String, String>();
       image.put(COL_IMAGE_ID, imageId);
       image.put(COL_URL, url);
       insert(image);
   }
   public void insert(HashMap<String, String> image){
       Date dateNow = new Date();
       ContentValues values = new ContentValues();
       values.put(COL_IMAGE_ID, image.get(COL_IMAGE_ID));
       values.put(COL_URL, image.get(COL_URL));
       values.put(COL_LASTUPDATE, dateNow.toLocaleString());
       mDb.insertOrThrow(TABLE_NAME, null, values);
   }
   public void updateByImageId(HashMap<String, String> image, String imageId) {
       Date dateNow = new Date();
       ContentValues values = new ContentValues();
       values.put(COL_IMAGE_ID, image.get(COL_IMAGE_ID));
       values.put(COL_URL, image.get(COL_URL_THUMB));
       values.put(COL_LASTUPDATE, dateNow.toLocaleString());
       mDb.update(TABLE_NAME, values, COL_IMAGE_ID + "=?", new String[] {imageId});
   }
}

• DbTestActivity.java

import java.util.ArrayList;
import java.util.HashMap;
import android.app.Activity;
import android.os.Bundle;
import android.util.Log;
public class DbTestActivity extends Activity {
   private static String TAG = "DbTestActivity";
   @Override
   public void onCreate(Bundle savedInstanceState) {
       super.onCreate(savedInstanceState);
       setContentView(R.layout.db_layout);
   }
   @Override
   public void onStart() {
       super.onStart();
       ImageDao dao = new ImageDao(getApplicationContext());
       dao.connection();
       dao.insertImageIdUrl("test1", "hoge1");
       ArrayList<HashMap<String, String>> users = dao.findAll();
       //HashMap<String, String> image = dao.findRowById(1);
       HashMap<String, String> image = dao.findRowByImageId("test1");
       dao.close();
       for (HashMap<String, String> user : users) {
           Log.d(TAG, "id " + user.get("_id"));
           Log.d(TAG, "image_id " + user.get("image_id"));
           Log.d(TAG, "url " + user.get("url"));
       }
       Log.d(TAG, "id " + image.get("_id"));
       Log.d(TAG, "image_id " + image.get("image_id"));
       Log.d(TAG, "url " + image.get("url"));
   }
}

以下エラー。データを取得したら、cursor.moveToFirst();をする

CursorIndexOutOfBoundsException

以下エラー。dbをcloseしてるか確認。

close() was never explicitly called on database '/data/data/com.project1/databases/test.db'

以下エラー。カーソルを閉じてるか確認

Finalizing a Cursor that has not been deactivated or closed. database