Android SqLitOpenHelper操作数据库

zhouxiaoli521

浏览: 559433 次
性别:
来自: 北京

最近访客更多访客>>

zjlovety

ll201lw

liro

scoryy

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

博客分类：

Android_数据存储

Android SQL Cache

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

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

http://www.cnblogs.com/GnagWang/archive/2011/02/27/1966082.html

分享到：