sqlite DB

package sqliteDB;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

public class DbHelper extends SQLiteOpenHelper {

	private static final String DATABASE_NAME = "ChatSysDemo.db";
	private static final int DATABASE_VERSION = 1;

	public DbHelper(Context context) {
		super(context,DATABASE_NAME,null,DATABASE_VERSION);
		// TODO Auto-generated constructor stub
	}

	// 数据库第一次被创建时onCreate会被调用
	@Override
	public void onCreate(SQLiteDatabase db) {
		// TODO Auto-generated method stub
		//历史消息记录表
		db.execSQL("CREATE TABLE IF NOT EXISTS message_record"
				+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT,sender TEXT,receiver TEXT,msg TEXT,time TEXT)");
		//会话表
		db.execSQL("CREATE TABLE IF NOT EXISTS session_record"   //type标示是否为好友
				+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT,userId INTEGER UNIQUE,userName TEXT,lastMsg TEXT,type INTEGER)");
		//好友列表
		db.execSQL("CREATE TABLE IF NOT EXISTS friends_list"
				+ "(_id INTEGER PRIMARY KEY AUTOINCREMENT,headImageUrl)"); 
	}

	// 如果DATABASE_VERSION值被改为2,系统发现现有数据库版本不同,即会调用onUpgrade
	@Override
	public void onUpgrade(SQLiteDatabase db,int oldVersion,int newVersion) {
		db.execSQL("ALTER TABLE person ADD COLUMN other STRING");
	}
}

package sqliteDB;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;

import commontool.friendslistPro;

import messageClasses.ChatContentMessage;
import messageClasses.Friends;
import messageClasses.Session;

import android.R.integer;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public class DBManager {
	private DbHelper helper;
	private SQLiteDatabase db;

	public DBManager(Context context) {
		helper = new DbHelper(context);
		// 因为getWritableDatabase内部调用了mContext.openOrCreateDatabase(mName,// mFactory);
		// 所以要确保context已初始化,我们可以把实例化DBManager的步骤放在Activity的onCreate里
		//最好不要在主线程调用
		db = helper.getWritableDatabase(); // 调用helper类的oncreate方法
	}
////////////////////////Add///////////////////
	/**
	 * 添加消息记录
	 * @param List<MessageClass> messages
	 */
	public void addMessageRecord(List<ChatContentMessage> messages) {
		db.beginTransaction(); // 开始事务
		try {
			for (ChatContentMessage message : messages) {
				db.execSQL(
						"INSERT INTO message_record VALUES(null,?,?)",new Object[] { message.sender,message.receiver,message.msg,message.time });
			}
			db.setTransactionSuccessful(); // 设置事务成功完成
		} finally {
			db.endTransaction(); // 结束事务
		}
	}
	
	public void updateFriendsList(List<Friends> friends) {
		db.beginTransaction(); // 开始事务
		try {
			db.delete("friends_list",null);
			for (Friends friend : friends) {
				db.execSQL(
						"INSERT INTO friends_list VALUES(null,new Object[] { String.valueOf(friend.userId),friend.userName,friend.headImageUrl });
			}
			db.setTransactionSuccessful(); // 设置事务成功完成
		} finally {
			db.endTransaction(); // 结束事务
		}
	}
	
	public void addFriends(Friends friend) {
		db.beginTransaction(); // 开始事务
		try {
				db.execSQL(
						"INSERT INTO friends_list VALUES(null,friend.headImageUrl });
				db.setTransactionSuccessful(); // 设置事务成功完成
		} finally {
			db.endTransaction(); // 结束事务
		}
	}
	
	public void addSessionRecord(List<Session> sessions) {
		db.beginTransaction(); // 开始事务
		try {
			db.delete("session_record",null);//更新太麻烦，直接先删掉所有的会话记录
			for (Session session : sessions) {
				db.execSQL(
						"INSERT INTO session_record VALUES(null,new Object[] { session.userId,session.userName,session.lastMessage,session.type });
			}
			db.setTransactionSuccessful(); // 设置事务成功完成
		} finally {
			db.endTransaction(); // 结束事务
		}
	}

	
	/**
	 * 获取hostUserName与guestuserName的聊天消息
	 * @return List<Person>
	 */
	public ArrayList<ChatContentMessage> queryMessagesRecord(String hostUserName,String guestuserName) {
		ArrayList<ChatContentMessage> messages = new ArrayList<ChatContentMessage>();
		Cursor c = queryTheMessageCursor(hostUserName,guestuserName);
		while (c.moveToNext()) {
			ChatContentMessage message = new ChatContentMessage();
			message.sender = c.getString(c.getColumnIndex("sender"));
			message.receiver = c.getString(c.getColumnIndex("receiver"));
			message.msg = c.getString(c.getColumnIndex("msg"));
			message.time = c.getString(c.getColumnIndex("time"));
			messages.add(message);
		}
		c.close();
		return messages;
	}
	/**
	 * 
	 * @param hostUserName
	 * @param guestuserName
	 * @return
	 */
	public HashMap<String,ArrayList<ChatContentMessage>> queryAllHistoryMessagesRecord(String hostUserName,ArrayList<Friends> friendList) {
		HashMap<String,ArrayList<ChatContentMessage>> historyMessaHashMap = new HashMap<String,ArrayList<ChatContentMessage>>();
		Iterator<Friends> iterator=friendList.iterator();
		String guestuserName;
		while (iterator.hasNext()) {
			Friends friend = iterator.next();
			guestuserName=friend.userName;
			ArrayList<ChatContentMessage> messageList=queryMessagesRecord(hostUserName,guestuserName);
			historyMessaHashMap.put(guestuserName,messageList);
		}
		return historyMessaHashMap;
	}
	//根据type查不同的会话记录(好友，非好友)
	public ArrayList<Friends> queryAllFriends() {
		ArrayList<Friends> friends = new ArrayList<Friends>();
		Cursor c = queryFriendsCursor();
		while (c.moveToNext()) {
			Friends friend = new Friends();
			friend.userId = c.getInt(c.getColumnIndex("userId"));
			friend.userName = c.getString(c.getColumnIndex("userName"));
			friend.headImageUrl = c.getString(c.getColumnIndex("headImageUrl"));
			friends.add(friend);
		}
		c.close();
		return friends;
	}

	public ArrayList<Session> queryAllSession(String userName,int type) {
		ArrayList<Session> sessions = new ArrayList<Session>();
		Cursor c = queryTheSessionCursor(userName,type);
		while (c.moveToNext()) {
			Session session = new Session();
			session.userId = c.getInt(c.getColumnIndex("userId"));
			session.userName = c.getString(c.getColumnIndex("userName"));
			session.lastMessage = c.getString(c.getColumnIndex("lastMessage"));
			session.type = c.getInt(c.getColumnIndex("type"));
			sessions.add(session);
		}
		c.close();
		return sessions;
	}
	
	/**
	 * 获得Cursor数据集
	 * @return Cursor
	 */
	public Cursor queryTheMessageCursor(String sender,String receiver) {
		// 查询某两人的聊天记录
		Cursor c = db
				.rawQuery(
						"SELECT * FROM message_record where (sender = ? and receiver = ?) or (sender = ? and receiver = ?)",new String[] { sender,receiver,sender });
		return c;
	}
	
	//获取与某人的会话
	public Cursor queryTheSessionCursor(String sender,int type) {
		// 查询会话记录
		Cursor c = db
				.rawQuery(
						"SELECT * FROM session_record where type = ?",new String[] { String.valueOf(type)});
		return c;
	}
	
	//获取与某人的会话
	public Cursor queryFriendsCursor() {
		// 查询会话记录
		Cursor c = db.rawQuery(
	         "SELECT * FROM friends_list",null);
		return c;
	}
		
	
	
  public int deleteMessageRecordWithSb(String userName)
   {
	   return db.delete("message_record","sender = ? || receiver = ?",new String[]{userName,userName});
   }
 
   public int deleteAllMessageRecord()
   {
	   return db.delete("message_record",null);
   }
   public int deleteAllSessionRecord()
   {
	   return db.delete("session_record",null);
   }
   public int deleteAllFriends()
   {
	   return db.delete("friends_list",null);
   }
   
	/**
	 *关闭数据库
	 */
	public void closeDB() {
		db.close();
	}
	
	/**
	 * update person's age
	 * 
	 * @param person
	 */
	// public void updateAge(Person person) {
	// ContentValues cv = new ContentValues();
	// cv.put("age",person.age);
	// db.update("person",cv,"name = ?",new String[]{person.name});
	// }

	/**
	 * delete old person
	 * 
	 * @param person
	 */
	// public void deleteOldMessage(Person person) {
	// db.delete("person","age >= ?",new
	// String[]{String.valueOf(person.age)});
	// }

}

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!