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);
}
@Override
public 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);
}
@Override
public 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()里调用。