Sqlite所作增删改查示例

packagecom.db.imgfornote;

 

importandroid.content.ContentValues;

importandroid.content.Context;

importandroid.database.Cursor;

importandroid.database.sqlite.SQLiteDatabase;

importandroid.database.sqlite.SQLiteDatabase.CursorFactory;

importandroid.database.sqlite.SQLiteOpenHelper;

importandroid.util.Log;

 

publicclassDBHelper extendsSQLiteOpenHelper {

    finalprivatestatic String mDbName="imgfornote";

    finalprivatestatic int mDbVersion=1;

    privatestaticDBHelper mInstance=null;

    privatefinalstatic String mTUserPhoto="UserPhoto";

    finalprivatestatic String mCreateSqlForNoteClass="create table if not exists NoteClass(classId integer primary key asc autoincrement,className NVARCHAR(100),rowTime timestamp default (datetime('now', 'localtime')))";

    finalprivatestatic String mCreateSqlForUserPhoto="create table if not exists UserPhoto(photoId integer primary key asc autoincrement,photoName VARCHAR(200),userPt VARCHAR(200),title VARCHAR(255),classId integer,content NVARCHAR(250),tag NVARCHAR(200),remark text,status integer default 0,rowTime timestamp default (datetime('now', 'localtime')))";

    finalprivatestatic String[] mInsertSqlForNoteClass={"insert into NoteClass(className) values('默认分类[私有]');","insert into NoteClass(className) values('读书笔记[私有]');","insert into NoteClass(className) values('电子资料[公开]');"};

    privateDBHelper(Context context, CursorFactory factory) {

        super(context, mDbName, factory, mDbVersion);  

    }

     

    publicstaticDBHelper GetInstance(Context context, SQLiteDatabase.CursorFactory factory)

    {

        if(mInstance==null){

            mInstance = newDBHelper(context,factory); 

        }

        returnmInstance;

    }

 

    @Override

    publicvoidonCreate(SQLiteDatabase db) {

        // 创建表

        db.execSQL(mCreateSqlForNoteClass);

        db.execSQL(mCreateSqlForUserPhoto);

        //初始化数据

        for(inti=0;i<mInsertSqlForNoteClass.length;i++)

            db.execSQL(mInsertSqlForNoteClass[i]);

    }

 

    @Override

    publicvoidonUpgrade(SQLiteDatabase db, intoldVersion, intnewVersion) {

        // TODO Auto-generated method stub

 

    }

     

    privateCursor ExecSQLForCursor(String sql, String[] selectionArgs){

        SQLiteDatabase db =getWritableDatabase();

        Log.i("ExecSQLForCursor",sql);

        returndb.rawQuery(sql, selectionArgs);

    }

    privatevoidExecSQL(String sql){

        try{

            SQLiteDatabase db =getWritableDatabase();

            ExecSQL(sql,db);

        }catch(Exception e){

            Log.e("ExecSQL Exception",e.getMessage());

                    e.printStackTrace();

        }

    }

    privatevoidExecSQL(String sql,SQLiteDatabase db ){

    try{

            db.execSQL(sql);   

            Log.i("ExecSQL",sql);

        }catch(Exception e){

            Log.e("ExecSQL Exception",e.getMessage());

                e.printStackTrace();

        }

    }

    //添加照片信息

    publiclongInsertUserPhoto(String photoName,String title){

        SQLiteDatabase db =getWritableDatabase();

        ContentValues cv = newContentValues();

        cv.put("photoName", photoName);

        cv.put("title", title);

        returndb.insert(mTUserPhoto, null, cv);

    }

    //查询照片信息

    publicCursor SearchPhoto(introw,String sort){

        Cursor cur = null;

        try{

            String ord = (sort==null|| sort.toLowerCase().startsWith("a"))?"asc":"desc";

            String sql = "select * from UserPhoto order by photoId "+ord;

            String[] args = {String.valueOf(row)};

            if(row>0){

                sql +=" limit ?";

            }else{

                args=null;

            }

            cur = ExecSQLForCursor(sql,args);          

        }catch(Exception e) {

            cur = null;

            Log.e("SearchPhoto Exception",e.getMessage());

            e.printStackTrace();

        }

        returncur;

    }  

    //修改照片信息

    publicintUpdateUserPhoto(intphotoId,intclassId,String title,String content, String tag){

        SQLiteDatabase db =getWritableDatabase();

        ContentValues cv = newContentValues();

        cv.put("classId", classId);

        cv.put("title", title);

        cv.put("content", content);

        cv.put("tag", tag);

        String[] args = {String.valueOf(photoId)};

        returndb.update(mTUserPhoto, cv, "photoId=?",args);               

    }

    //删除照片信息

    publicintDeleteUserPhoto(intphotoId){

        SQLiteDatabase db =getWritableDatabase();

        String[] args = {String.valueOf(photoId)};

        returndb.delete(mTUserPhoto, "photoId=?", args);

    }

}