【Android】SqLitOpenHelper操作数据库

做android中一个最常用的就是数据库的操作了，我们在android提供的SqLitOpenHelper上稍稍加工，就可以很方便的对数据进行操作。

public class DatabaseHelper extends SQLiteOpenHelper {

    public static final String COLUMN_ID = "_id";


    private String tableName;
    private Map<String, String> tableContent;

    DatabaseHelper(Context context, String name, CursorFactory cursorFactory,
            int version) {
        super(context, name, cursorFactory, version);
        this.tableName = null;
        this.tableContent = new HashMap<String, String>();
    }

    public void setTableAttr(String tableName, Map<String, String> tableContent)
    {
        this.tableName = tableName;
        this.tableContent = tableContent;
    }


    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub
        if( this.tableName == null ||
                this.tableContent.size() == 0)
        {
            return;
        }

        String sql = "create table if not exists ";
        sql += this.tableName;
        sql +="(";
        sql += COLUMN_ID;
        sql += " integer primary key,";


        Set set = tableContent.entrySet();
        Iterator iterator = set.iterator();
        int index = 0;
        while (iterator.hasNext()) {
            index++;
            Map.Entry mapEntry = (Map.Entry) iterator.next();
            if (mapEntry.getValue() != null) {
                sql += (String) mapEntry.getKey();
                sql += " ";
                sql += (String) mapEntry.getValue();

                if( index < this.tableContent.size())
                {
                    sql += ",";
                }

            }
        }

        sql += ")";

        db.execSQL( sql );
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        this.onCreate(db);
    }

}

下来我们写一个数据库表的基类：

public abstract class Table {
//    public static int _DATA_CACHE_VERSION_ = 1;

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

    protected String tableName;

    private final Context mContext;

    protected CursorFactory mFactory;

    protected Map<String, String> tableContent;

    private SQLiteDatabase mDataBase;

    protected DatabaseHelper dbHelper;

    protected String mColumnIdInMedia;

    private boolean mIsInit;

    protected int _DATA_CACHE_VERSION_ = 0;

    public Table(Context ctx) {
        this.mContext = ctx;
        this.tableContent = new HashMap<String, String>();
        mIsInit = false;
        this.mFactory = null;
        mColumnIdInMedia = null;
        this.getClass().getSimpleName().toLowerCase();
    }

    protected abstract void _init();
    public void init() {
        if (mIsInit) {
            return;
        }

        _init();

        dbHelper = new DatabaseHelper(mContext, DB_NAME, mFactory,
                _DATA_CACHE_VERSION_);

        this.setTableAttr();

        dbHelper.setTableAttr(tableName, tableContent);
        mDataBase = dbHelper.getWritableDatabase();



        mIsInit = true;
    }

    protected abstract void setTableAttr();

    public boolean insert(ContentValues values) {

        if (mDataBase == null) {
            return false;
        }

        mDataBase.insert(this.tableName, null, values);

        return true;
    }

    public boolean delete(String whereClause) {

        if (mDataBase == null) {
            return false;
        }

        mDataBase.delete(this.tableName, whereClause, null);
        return true;
    }

    public boolean update(ContentValues values, String whereClause) {
        if (mDataBase == null) {
            return false;
        }

        mDataBase.update(this.tableName, values, whereClause, null);
        return true;
    }

    private Cursor query(String columnName, int id) {
        if (mDataBase == null) {
            return null;
        }

        if( mColumnIdInMedia == null ||
                mColumnIdInMedia.equalsIgnoreCase(""))
        {
            mColumnIdInMedia = DatabaseHelper.COLUMN_ID;
        }

        Cursor cursor = mDataBase.query(this.tableName, new String[] {columnName, mColumnIdInMedia}, null, null, null,
                null, "_id asc");

        int _idIndex = cursor.getColumnIndex(mColumnIdInMedia);

        if(_idIndex != -1)
        {
            for (cursor.moveToFirst(); !(cursor.isAfterLast()); cursor.moveToNext()) {
                if( id == cursor.getInt(_idIndex))
                {
                    return cursor;
                }
            }
        }

        cursor.close();
        return null;

    }

    public String queryString(String columnName, int id)
    {
        Cursor cursor = query( columnName, id);

        String value = null;

        if(cursor != null)
        {
            value = cursor.getString(0);
            cursor.close();
        }

        return value;
    }

    public int queryInteger(String columnName, int id)
    {
        Cursor cursor = query( columnName, id);

        int value = -1;

        if(cursor != null)
        {
            value = cursor.getInt(0);
            cursor.close();
        }

        return value;
    }

    public double queryDouble(String columnName, int id)
    {
        Cursor cursor = query( columnName, id);

        double value = -1;

        if(cursor != null)
        {
            value = cursor.getDouble(0);
            cursor.close();
        }

        return value;
    }

    public void Release() {

        if( this.mDataBase != null )
        {
            this.mDataBase.close();
            this.mDataBase = null;
        }

        this.mIsInit = false;
    }

}

下来写具体的表了：

public class ImageTable extends Table {
    public static final int DATA_CACHE_VERSION_ = 1;

    public static final String COLUMN_WIDTH = "width";

    public static final String COLUMN_HEIGHT = "height";

    public static final String COLUMN_LASTTIME = "lasttime";

    public static final String COLUMN_DATA = "_data";

    public static final String COLUMN_ID_IMAGE = "_id_image";

    public ImageCache(Context ctx) {
        super(ctx);
        // TODO Auto-generated constructor stub
    }

    @Override
    protected void setTableAttr() {
        // TODO Auto-generated method stub
        this.mColumnIdInMedia = COLUMN_ID_IMAGE;

        this.tableContent.clear();

        this.tableContent.put(COLUMN_ID_IMAGE, "integer");
        this.tableContent.put(COLUMN_WIDTH, "integer");
        this.tableContent.put(COLUMN_HEIGHT, "integer");
        this.tableContent.put(COLUMN_LASTTIME, "integer");
        this.tableContent.put(COLUMN_DATA, "varchar");
    }

    @Override
    protected void _init() {
        // TODO Auto-generated method stub
        _DATA_CACHE_VERSION_ = DATA_CACHE_VERSION_;
    }
}

到这里所有的工作都准备好了，下来看看怎么用这些个东西来操作数据库：

1、新增一条记录：

Table imagTable = new ImageTable(ctx);
imageTable.init();

ContentValues values = new ContentValues();
    values.put(ImageTable.COLUMN_DATA,
            "/mnt/sdcard/image/xxx.jpg");
    values.put(ImageTable.COLUMN_HEIGHT,
            300);
    values.put(ImageTable.COLUMN_WIDTH, 400);
    values.put(ImageTable.COLUMN_LASTTIME,
            123123123123);
imageTale.insert(values);

imageTale.Release();

2、删除记录：

Table imagTable = new ImageTable(ctx);
imageTable.init();

imageTale.delete(ImageTable.COLUMN_LASTTIME + "=45554837");
imageTale.delete(DatabaseHelper.COLUMN_ID + "=3");

imageTale.Release();

3、更新一条记录：

Table imagTable = new ImageTable(ctx);
imageTable.init();

ContentValues values = new ContentValues();
    values.put(ImageTable.COLUMN_DATA,
            "/mnt/sdcard/image/xxx.jpg");
    values.put(ImageTable.COLUMN_HEIGHT,
            300);
    values.put(ImageTable.COLUMN_WIDTH, 400);
    values.put(ImageTable.COLUMN_LASTTIME,
            123123123123);

imageCache.update(values, DatabaseHelper.COLUMN_ID + "=3");

imageTale.Release();

4、当然还有一个很重要的就是查询：

Table imagTable = new ImageTable(ctx);
imageTable.init();

int width = imagTable.queryInteger(ImageTable.COLUMN_WIDTH, 1);

String str = imagTable.queryString(ImageTable.COLUMN_DATA, 1);
imageTale.Release();

基本的功能都全了，日常再用都再接着完善了，使用还算方便吧， 多张表也是这样使用，我用每个类的名字转为小写做为表的名称。