SQLite的使用

DBHelper继承了SQLiteOpenHelper，作为维护和管理数据库的基类

/** * 创建数据库 * @author daihuie  */public class DBHelper extends SQLiteOpenHelper {private static final String DATABASE_NAME = "honglin.db";private static final int DATABASE_VERSION = 1;public DBHelper(Context context) {super(context, DATABASE_NAME, null, DATABASE_VERSION);}@Overridepublic void onCreate(SQLiteDatabase db) {String sql1 = "CREATE TABLE IF NOT EXISTS t_latelymessage"+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT,"+ "friendName TEXT,"+ "myName TEXT,"+ "content TEXT,"+ "time TEXT,"+ "number INTEGER)";db.execSQL(sql1);String sql2 = "CREATE TABLE IF NOT EXISTS t_chatrecord"+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT,"+ "friendName TEXT,"+ "myName TEXT,"+ "time TEXT,"+ "content TEXT,"+ "url TEXT,"+ "type INTEGER,"+ "state INTEGER)";db.execSQL(sql2);}@Overridepublic void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { db.execSQL("DROP TABLE IF EXISTS t_latelymessage");//把原先的删除掉 db.execSQL("DROP TABLE IF EXISTS t_chatrecord");     onCreate(db);}}

DBManager是建立在DBHelper之上，封装了常用的业务方法：增删改查

package hl.view.mydb;i/** * 数据库操作 * @author daihuie * */public class DBManager {private DBHelper mDbHelper;private SQLiteDatabase db;private String TABLE_NAME_1 = "t_latelymessage";private String TABLE_NAME_2 = "t_chatrecord";public DBManager(Context context) {this.mDbHelper = new DBHelper(context);this.db = this.mDbHelper.getWritableDatabase();}/** * 添加最新消息 * @param mLatelyMessage */public void addLatelyMessage(LatelyMessage mLatelyMessage){db.beginTransaction();ContentValues values = new ContentValues();values.put("friendName", mLatelyMessage.getFriendName());values.put("myName", mLatelyMessage.getMyName());values.put("content", mLatelyMessage.getContent());values.put("time", mLatelyMessage.getTime());values.put("number", mLatelyMessage.getNumber());db.insert(TABLE_NAME_1, null, values);db.setTransactionSuccessful();db.endTransaction();}/** * 根据时间删除最新消息  * @param mLatelyMessage */public void deleteLatelyMessage(LatelyMessage mLatelyMessage){String[] time = {mLatelyMessage.getTime()};db.delete(TABLE_NAME_1, "time=?",time);}/** * 根据id修改最新消息  * @param mLatelyMessage */public void updateLatelyMessageById(LatelyMessage mLatelyMessage){ContentValues values = new ContentValues();values.put("_id", mLatelyMessage.getMessageId());values.put("friendName", mLatelyMessage.getFriendName());values.put("myName", mLatelyMessage.getMyName());values.put("content", mLatelyMessage.getContent());values.put("time", mLatelyMessage.getTime());values.put("number", mLatelyMessage.getNumber());String[] whereArgs = {mLatelyMessage.getMessageId()+""};db.update(TABLE_NAME_1, values, "_id=?", whereArgs);}/** * 根据买家名字查找出所有的最新消息 * @param name * @return */public List<LatelyMessage> queryLatelyByMyName(String name){String[] selectArgs = {name};List<LatelyMessage> list = new ArrayList<LatelyMessage>();Cursor c = db.query(TABLE_NAME_1, null, "myName=?", selectArgs, null, null, null);while(c.moveToNext()){int messageId = c.getInt(c.getColumnIndex("_id"));String friendName = c.getString(c.getColumnIndex("friendName"));String myName = c.getString(c.getColumnIndex("myName"));String content = c.getString(c.getColumnIndex("content"));String time = c.getString(c.getColumnIndex("time"));int number = c.getInt(c.getColumnIndex("number"));LatelyMessage mLatelyMessage = new LatelyMessage(messageId, friendName, myName, content, time, number);list.add(mLatelyMessage);}return list;}/** * 根据买、卖家的名字查找出所有的最新消息 * @param name1：买家名字 * @param name2：卖家名字 * @return */public List<LatelyMessage> queryLatelyByMyNameAndFriendName(String name1,String name2){String[] selectArgs = {name1,name2};List<LatelyMessage> list = new ArrayList<LatelyMessage>();Cursor c = db.query(TABLE_NAME_1, null, "myName=? and friendName=?", selectArgs, null, null, null);while(c.moveToNext()){int messageId = c.getInt(c.getColumnIndex("_id"));String friendName = c.getString(c.getColumnIndex("friendName"));String myName = c.getString(c.getColumnIndex("myName"));String content = c.getString(c.getColumnIndex("content"));String time = c.getString(c.getColumnIndex("time"));int number = c.getInt(c.getColumnIndex("number"));LatelyMessage mLatelyMessage = new LatelyMessage(messageId, friendName, myName, content, time, number);list.add(mLatelyMessage);}return list;}/** * 查找出所有的最新消息 * @return */public List<LatelyMessage> queryAllLatelyMessage(){List<LatelyMessage> list = new ArrayList<LatelyMessage>();Cursor c = db.query(TABLE_NAME_1, null, null, null, null, null, null);while(c.moveToNext()){int messageId = c.getInt(c.getColumnIndex("_id"));String friendName = c.getString(c.getColumnIndex("friendName"));String myName = c.getString(c.getColumnIndex("myName"));String content = c.getString(c.getColumnIndex("content"));String time = c.getString(c.getColumnIndex("time"));int number = c.getInt(c.getColumnIndex("number"));LatelyMessage mLatelyMessage = new LatelyMessage(messageId, friendName, myName, content, time, number);list.add(mLatelyMessage);}return list;}/** * 添加消息记录 * @param mChatRecord */public void addChatRecord(ChatRecord mChatRecord){ContentValues values = new ContentValues();values.put("state", mChatRecord.getState());values.put("friendName", mChatRecord.getFriendName());values.put("content", mChatRecord.getContent());values.put("time", mChatRecord.getTime());values.put("type", mChatRecord.getType());values.put("myName", mChatRecord.getMyName());values.put("url", mChatRecord.getUrl());db.insert(TABLE_NAME_2,null,values);}/** * 根据时间删除消息记录 * @param mChatRecord */public void deleteChatRecord(ChatRecord mChatRecord){String[] time = {mChatRecord.getTime()};db.delete(TABLE_NAME_2, "time=?",time);}/** * 根据买、卖家名字查出消息记录 * @param name1：卖家名字 * @param name2：买家名字 * @param page:当前页数 * @return */public List<ChatRecord> queryChatRecord(String name1,String name2,int page){String[] selectArgs = {name1,name2};List<ChatRecord> list = new ArrayList<ChatRecord>();List<ChatRecord> list2 = new ArrayList<ChatRecord>();Cursor c = db.query(TABLE_NAME_2, null, "friendName=? and myName=?", selectArgs, null, null, "time DESC limit "+page*5);while(c.moveToNext()){int recordId = c.getInt(c.getColumnIndex("_id"));String friendName = c.getString(c.getColumnIndex("friendName"));String myName = c.getString(c.getColumnIndex("myName"));String time = c.getString(c.getColumnIndex("time"));String content = c.getString(c.getColumnIndex("content"));String url = c.getString(c.getColumnIndex("url"));int type = c.getInt(c.getColumnIndex("type"));int state = c.getInt(c.getColumnIndex("state"));ChatRecord mChatRecord = new ChatRecord(recordId, friendName, myName, time, content, url, type, state);list.add(mChatRecord);}for (int i = list.size()-1; i>=0; i--) {list2.add(list.get(i));}return list2;}/** * 根据买、卖家名字查询消息总条数 * @param name1 * @param name2 * @param page * @return 总条数 */public int getChatRecord_Count(String name1,String name2){String[] selectArgs = {name1,name2};List<ChatRecord> list = new ArrayList<ChatRecord>();Cursor c = db.query(TABLE_NAME_2, null, "friendName=? and myName=?", selectArgs, null, null, null);return c.getCount();}/** * 关闭数据库 */public void closeDataBase(){db.close();}}

开始调用：

private DBManager mDbManager;

mDbManager  = new DBManager(this);

chatRecordList = mDbManager.queryChatRecord(sendName,MainActivity.username,page);//查询

其他增删改同样调用<span style="font-family: 'microsoft yahei';">DBManager 里的相应方法即可。

注：最后一定要调用closeDataBase方法 关闭数据库，一般在Activity的onDestory（）里调用。