室内设计学校环境分析网站推广优化之八大方法

sqlite 数据库简单封装示例，使用记事本数据库表进行示例。

首先继承SQLiteOpenHelper 使用sql语句进行创建一张表。

public class noteDBHelper extends SQLiteOpenHelper {public noteDBHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {super(context, name, factory, version);}@Overridepublic void onCreate(SQLiteDatabase sqLiteDatabase) {String sql="create table if not exists note_data(" +"note_id integer primary key autoincrement," +"note_tittle varchar,"+"note_content varchar,"+"note_type varchar,"+"createTime varchar,"+"updateTime varchar,"+"note_owner varchar)";sqLiteDatabase.execSQL(sql);}@Overridepublic void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {}
}

使用单例封装这张表的增删改查，同时转换成字段对应的结构体。这样方便数据管理，插入，查询，更新等操作。 

public class NoteDaoManage {private static NoteDaoManage noteDaoManage;Context context;noteDBHelper dbHelper;public static NoteDaoManage GetInstance(Context base) {if (noteDaoManage == null) {noteDaoManage = new NoteDaoManage(base);}return noteDaoManage;}private NoteDaoManage(Context context) {this.context = context;dbHelper = new noteDBHelper(context, "note.db", null, 1);}public void insertNote(NoteBean bean){SQLiteDatabase sqLiteDatabase= dbHelper.getWritableDatabase();ContentValues cv = new ContentValues();cv.put("note_tittle",bean.getTitle());cv.put("note_content",bean.getContent());cv.put("note_type",bean.getType());cv.put("createTime",bean.getCreateTime());cv.put("updateTime",bean.getUpdateTime());cv.put("note_owner",bean.getOwner());sqLiteDatabase.insert("note_data",null,cv);}public int DeleteNote(int id){SQLiteDatabase sqLiteDatabase= dbHelper.getWritableDatabase();int ret=0;ret=sqLiteDatabase.delete("note_data","note_id=?",new String[]{id + ""});return ret;}@SuppressLint("Range")public  List<NoteBean>  getAllData(){List<NoteBean> noteList = new ArrayList<>();SQLiteDatabase db = dbHelper.getWritableDatabase();String sql="select * from note_data "; // 查询全部数据Cursor cursor = db.rawQuery(sql,null);while (cursor.moveToNext()) {NoteBean note = new NoteBean();note.setId(cursor.getInt(cursor.getColumnIndex("note_id")));note.setTitle(cursor.getString(cursor.getColumnIndex("note_tittle")));note.setContent(cursor.getString(cursor.getColumnIndex("note_content")));note.setType(cursor.getString(cursor.getColumnIndex("note_type")));note.setCreateTime(cursor.getString(cursor.getColumnIndex("createTime")));note.setUpdateTime(cursor.getString(cursor.getColumnIndex("updateTime")));noteList.add(note);}if (cursor != null) {cursor.close();}if (db != null) {db.close();}return noteList;}public void updateNote(NoteBean note) {SQLiteDatabase db = dbHelper.getWritableDatabase();ContentValues cv = new ContentValues();cv.put("note_tittle", note.getTitle());cv.put("note_content", note.getContent());cv.put("note_type", note.getType());cv.put("updateTime", note.getUpdateTime());db.update("note_data", cv, "note_id=?", new String[]{note.getId()+""});db.close();}@SuppressLint("Range")public List<NoteBean> queryNotesAll(int mark, String text) {SQLiteDatabase db = dbHelper.getWritableDatabase();List<NoteBean> noteList = new ArrayList<>();NoteBean note;String sql ;Cursor cursor = null;if (TextUtils.isEmpty(text)){sql="select * from note_data "; // 查询全部数据}else {if(mark==0){sql = "SELECT * FROM note_data WHERE note_tittle LIKE '%" + text + "%'" + " order by note_id desc"; // 构建SQL语句}else {sql = "SELECT * FROM note_data WHERE note_content LIKE '%" + text + "%'" + " order by note_id desc"; // 构建SQL语句}}cursor = db.rawQuery(sql,null);while (cursor.moveToNext()) {note = new NoteBean();note.setId(cursor.getInt(cursor.getColumnIndex("note_id")));note.setTitle(cursor.getString(cursor.getColumnIndex("note_tittle")));note.setContent(cursor.getString(cursor.getColumnIndex("note_content")));note.setType(cursor.getString(cursor.getColumnIndex("note_type")));note.setCreateTime(cursor.getString(cursor.getColumnIndex("createTime")));note.setUpdateTime(cursor.getString(cursor.getColumnIndex("updateTime")));noteList.add(note);}if (cursor != null) {cursor.close();}if (db != null) {db.close();}return noteList;}}