package hillfly.wifichat.common.sql; import hillfly.wifichat.model.ChattingInfo; import hillfly.wifichat.model.Message; import hillfly.wifichat.model.Message.CONTENT_TYPE; import hillfly.wifichat.model.UserInfo; import hillfly.wifichat.model.Users; import java.util.ArrayList; import java.util.Collections; import java.util.List; import org.json.JSONException; import org.json.JSONStringer; import android.content.ContentValues; import android.content.Context; import android.database.Cursor; import android.database.sqlite.SQLiteDatabase; public class SqlDBOperate { private SQLHelper chatInfoSQLHelper; // 数据库类(t_chatting) private SQLiteDatabase chatInfoDataBase; // 数据库(t_chatting)的操作类 private DBHelper userSQLHelper; // 数据库类(t_user) private SQLiteDatabase userDataBase;// 数据库(t_user)的操作类 /* * 构造函数参数:context对象通过db的方法来操作数据库的增删改查 */ public SqlDBOperate(Context context) { chatInfoSQLHelper = new SQLHelper(context); chatInfoDataBase = chatInfoSQLHelper.getWritableDatabase(); userSQLHelper = new DBHelper(context); userDataBase = userSQLHelper.getWritableDatabase(); } /* 关闭数据库 */ public void close() { userSQLHelper.close(); userDataBase.close(); chatInfoSQLHelper.close(); chatInfoDataBase.close(); } /* * 参数:userInfo类 作用:用来添加用户信息 */ public void addUserInfo(UserInfo user) { ContentValues values = new ContentValues(); values.put("name", user.getName()); values.put("sex", user.getSex()); values.put("age", user.getAge()); values.put("IMEI", user.getIMEI()); values.put("ip", user.getIPAddr()); values.put("status", user.getIsOnline()); values.put("avater", user.getAvater()); values.put("lastdate", user.getLastDate()); values.put("device", user.getDevice()); values.put("constellation", user.getConstellation()); int id = getIDByIMEI(user.getIMEI()); if (id != 0) { user.setId(id); updateUserInfo(user); } else userDataBase.insert(userSQLHelper.getTableName(), "id", values); } /* 获取在线信息尚未完善,默认在线状态(0) */ public void addUserInfo(Users people) { ContentValues values = new ContentValues(); values.put("name", people.getNickname()); values.put("sex", people.getGender()); values.put("age", people.getAge()); values.put("IMEI", people.getIMEI()); values.put("ip", people.getIpaddress()); values.put("status", people.getOnlineStateInt()); values.put("avater", people.getAvatar()); values.put("lastdate", people.getLogintime()); values.put("device", people.getDevice()); values.put("constellation", people.getConstellation()); int id = getIDByIMEI(people.getIMEI()); if (id != 0) { userDataBase.update(userSQLHelper.getTableName(), values, "id = ?", new String[] { String.valueOf(id) }); } else userDataBase.insert(userSQLHelper.getTableName(), "id", values); } /* * 参数:userInfo类 作用:用来更新用户信息 */ public void updateUserInfo(UserInfo user) { // db=helper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put("name", user.getName()); values.put("sex", user.getSex()); values.put("age", user.getAge()); values.put("IMEI", user.getIMEI()); values.put("ip", user.getIPAddr()); values.put("status", user.getIsOnline()); values.put("avater", user.getAvater()); values.put("lastdate", user.getLastDate()); values.put("device", user.getDevice()); values.put("constellation", user.getConstellation()); userDataBase.update(userSQLHelper.getTableName(), values, "id = ?", new String[] { String.valueOf(user.getId()) }); } /* * 参数:用户的IMEI序列码结果返回IMEI码对应用户的ID */ public int getIDByIMEI(String imei) { Cursor cursor = userDataBase.query(userSQLHelper.getTableName(), new String[] { "id" }, "IMEI=?", new String[] { imei }, null, null, null); if (cursor.moveToNext()) { int id = cursor.getInt(cursor.getColumnIndex("id")); cursor.close(); return id; } cursor.close(); return 0; } /* * 参数:用户对应序号ID 作用:用来查找对应的用户 返回IMEI */ public String getIMEIByUserID(int id) { Cursor cursor = userDataBase.query(userSQLHelper.getTableName(), new String[] { "IMEI" }, "id=?", new String[] { String.valueOf(id) }, null, null, null); if (cursor.moveToNext()) { String IMEI = cursor.getString(cursor.getColumnIndex("IMEI")); cursor.close(); return IMEI; } cursor.close(); return null; } /* * 参数:用户对应序号ID 作用:用来查找对应的用户 返回userInfo类 */ public UserInfo getUserInfoByID(int id) { // db = helper.getWritableDatabase(); // db.query(table, columns, selection, selectionArgs, groupBy, having, // orderBy) Cursor cursor = userDataBase.query(userSQLHelper.getTableName(), new String[] { "id", "name", "age", "IMEI", "sex", "ip", "status", "avater", "lastdate", "device", "constellation" }, "id=?", new String[] { String.valueOf(id) }, null, null, null); if (cursor.moveToNext()) { UserInfo userInfo = new UserInfo(cursor.getInt(cursor.getColumnIndex("id")), cursor.getString(cursor.getColumnIndex("name")), cursor.getInt(cursor .getColumnIndex("age")), cursor.getString(cursor.getColumnIndex("sex")), cursor.getString(cursor .getColumnIndex("IMEI")), cursor.getString(cursor.getColumnIndex("ip")), cursor.getInt(cursor .getColumnIndex("status")), cursor.getInt(cursor .getColumnIndex("avater")), cursor.getString(cursor .getColumnIndex("lastdate")), cursor.getString(cursor .getColumnIndex("device")), cursor.getString(cursor .getColumnIndex("constellation"))); cursor.close(); return userInfo; } cursor.close(); return null; } /* * 参数:用户对应的IMEI码 作用:用来查找对应的用户 返回userInfo类 */ public UserInfo getUserInfoByIMEI(String imei) { // db = helper.getWritableDatabase(); // db.query(table, columns, selection, selectionArgs, groupBy, having, // orderBy) Cursor cursor = userDataBase.query(userSQLHelper.getTableName(), new String[] { "id", "name", "age", "IMEI", "sex", "ip", "status", "avater", "lastdate", "device", "constellation" }, "IMEI=?", new String[] { imei }, null, null, null); if (cursor.moveToNext()) { UserInfo userInfo = new UserInfo(cursor.getInt(cursor.getColumnIndex("id")), cursor.getString(cursor.getColumnIndex("name")), cursor.getInt(cursor .getColumnIndex("age")), cursor.getString(cursor.getColumnIndex("sex")), cursor.getString(cursor .getColumnIndex("IMEI")), cursor.getString(cursor.getColumnIndex("ip")), cursor.getInt(cursor .getColumnIndex("status")), cursor.getInt(cursor .getColumnIndex("avater")), cursor.getString(cursor .getColumnIndex("lastdate")), cursor.getString(cursor .getColumnIndex("device")), cursor.getString(cursor .getColumnIndex("constellation"))); cursor.close(); return userInfo; } cursor.close(); return null; } /* * 参数:用户信息的一系列序号如(1,2,3) 作用:用来删除用户信息 */ public void deteleUserInfo(Integer... ids) { if (ids.length > 0) { StringBuffer sb = new StringBuffer(); String[] strPid = new String[ids.length]; for (int i = 0; i < ids.length; i++) { sb.append('?').append(','); strPid[i] = String.valueOf(ids[i]); } sb.deleteCharAt(sb.length() - 1); // db = helper.getWritableDatabase(); userDataBase.delete(userSQLHelper.getTableName(), "id in (" + sb + ")", strPid); } } /* * 用来获取近期的一系列用户信息 参数:start为步数,count为最大记录数,(倒序排列) 放回List<userInfo> */ public List<UserInfo> getScrollDataOfUserInfo(int start, int count) { List<UserInfo> users = new ArrayList<UserInfo>(); // db = helper.getWritableDatabase(); Cursor cursor = userDataBase.query(userSQLHelper.getTableName(), new String[] { "id", "name", "age", "sex", "IMEI", "ip", "status", "avater", "lastdate", "device", "constellation" }, null, null, null, null, "id desc", start + "," + count); while (cursor.moveToNext()) { users.add(new UserInfo(cursor.getInt(cursor.getColumnIndex("id")), cursor .getString(cursor.getColumnIndex("name")), cursor.getInt(cursor .getColumnIndex("age")), cursor.getString(cursor.getColumnIndex("sex")), cursor .getString(cursor.getColumnIndex("IMEI")), cursor.getString(cursor .getColumnIndex("ip")), cursor.getInt(cursor.getColumnIndex("status")), cursor .getInt(cursor.getColumnIndex("avater")), cursor.getString(cursor .getColumnIndex("lastdate")), cursor.getString(cursor.getColumnIndex("device")), cursor.getString(cursor .getColumnIndex("constellation")))); } cursor.close(); return users; } /* * 作用: 用来获取表中用户总数量 */ public long getCountOfUserInfo() { // db = helper.getWritableDatabase(); Cursor cursor = userDataBase.query(userSQLHelper.getTableName(), new String[] { "count(*)" }, null, null, null, null, null); if (cursor.moveToNext()) { long count = cursor.getLong(0); cursor.close(); return count; } cursor.close(); return 0; } /* * 该函数将所有数据库中用户表的信息用JSON形式的String来表示 */ public String sendUserInfoToJSON() { List<UserInfo> users; int count = (int) getCountOfUserInfo(); users = getScrollDataOfUserInfo(0, count); JSONStringer jsonText = new JSONStringer(); try { // 首先是{,对象开始。object和endObject必须配对使用 jsonText.object(); jsonText.key("user"); // 键user的值是数组。array和endArray必须配对使用 jsonText.array(); for (UserInfo user : users) { jsonText.object(); jsonText.key("id"); jsonText.value(user.getId()); jsonText.key("name"); jsonText.value(user.getName()); jsonText.key("sex"); jsonText.value(user.getSex()); jsonText.key("age"); jsonText.value(user.getAge()); jsonText.key("IMEI"); jsonText.value(user.getIMEI()); jsonText.key("ip"); jsonText.value(user.getIPAddr()); jsonText.key("status"); jsonText.value(user.getIsOnline()); jsonText.key("avater"); jsonText.value(user.getAvater()); jsonText.key("lastdate"); jsonText.value(user.getLastDate()); jsonText.key("device"); jsonText.value(user.getDevice()); jsonText.key("constellation"); jsonText.value(user.getConstellation()); jsonText.endObject(); } jsonText.endArray(); // },对象结束 jsonText.endObject(); } catch (JSONException ex) { throw new RuntimeException(ex); } return jsonText.toString(); } /* * 参数:发送方ID sendID,接收方ID receiverID结果返回一系列的聊天记录的ID */ public List<Integer> getIDOfChattingInfo(int senderID, int receiverID) { List<Integer> ids = new ArrayList<Integer>(); Cursor cursor = chatInfoDataBase.query(chatInfoSQLHelper.getTableName(), new String[] { "id" }, "sendID=? and receiverID=?", new String[] { String.valueOf(senderID), String.valueOf(receiverID) }, null, null, null); while (cursor.moveToNext()) { ids.add(Integer.valueOf(cursor.getInt(cursor.getColumnIndex("id")))); } cursor.close(); return ids; } /* * 参数:发送方ID sendID,接收方ID receiverID结果返回一系列的聊天记录 */ public List<ChattingInfo> getAllMessageFromChattingInfo(int sendID, int receiverID) { List<ChattingInfo> infos = new ArrayList<ChattingInfo>(); Cursor cursor = chatInfoDataBase.query(chatInfoSQLHelper.getTableName(), new String[] { "id", "sendID", "receiverID", "chatting", "date", "style" }, "sendID=? and receiverID=?", new String[] { String.valueOf(sendID), String.valueOf(receiverID) }, null, null, null); while (cursor.moveToNext()) { infos.add(new ChattingInfo(cursor.getInt(cursor.getColumnIndex("id")), cursor .getInt(cursor.getColumnIndex("sendID")), cursor.getInt(cursor .getColumnIndex("receiverID")), cursor.getString(cursor.getColumnIndex("date")), cursor.getString(cursor .getColumnIndex("chatting")), cursor.getInt(cursor .getColumnIndex("style")))); } cursor.close(); return infos; } /* * 参数:chattinginfo类 作用:用来添加聊天记录 */ public void addChattingInfo(ChattingInfo info) { ContentValues values = new ContentValues(); values.put("sendID", info.getSendID()); values.put("receiverID", info.getReceiverID()); values.put("chatting", info.getInfo()); values.put("date", info.getDate()); values.put("style", info.getStyle()); chatInfoDataBase.insert(chatInfoSQLHelper.getTableName(), "id", values); } /* * 参数:chattinginfo类 作用:用来添加聊天记录 */ public void addChattingInfo(int senderID, int recieverID, String time, String content, CONTENT_TYPE type) { ContentValues values = new ContentValues(); values.put("sendID", senderID); values.put("receiverID", recieverID); values.put("chatting", content); values.put("date", time); values.put("style", getStyteByContentType(type)); chatInfoDataBase.insert(chatInfoSQLHelper.getTableName(), "id", values); } private int getStyteByContentType(CONTENT_TYPE type) { if (type == CONTENT_TYPE.TEXT) { return 0; } else if (type == CONTENT_TYPE.IMAGE) { return 1; } else if (type == CONTENT_TYPE.FILE) { return 2; } else if (type == CONTENT_TYPE.VOICE) { return 3; } return -1; } /* * 参数:chattinginfo类 作用:用来添加聊天记录 */ public void addChattingInfo(String senderIMEI, String recieverIMEI, String time, String content, CONTENT_TYPE type) { ContentValues values = new ContentValues(); values.put("sendID", getIDByIMEI(senderIMEI)); values.put("receiverID", getIDByIMEI(recieverIMEI)); values.put("chatting", content); values.put("date", time); values.put("style", getStyteByContentType(type)); chatInfoDataBase.insert(chatInfoSQLHelper.getTableName(), "id", values); } /* * 参数:chattinginfo类 作用:用来更新聊天记录 */ public void updateChattingInfo(ChattingInfo info) { // db=helper.getWritableDatabase(); ContentValues values = new ContentValues(); values.put("sendID", info.getSendID()); values.put("receiverID", info.getReceiverID()); values.put("chatting", info.getInfo()); values.put("date", info.getDate()); values.put("style", info.getStyle()); chatInfoDataBase.update(chatInfoSQLHelper.getTableName(), values, "id = ?", new String[] { String.valueOf(info.getId()) }); } /* * 参数:聊天记录序号ID 作用:用来查找对应的一条聊天记录 返回chattinginfo类 */ public ChattingInfo getChattingInfoByID(int id) { // db = helper.getWritableDatabase(); // db.query(table, columns, selection, selectionArgs, groupBy, having, // orderBy) Cursor cursor = chatInfoDataBase.query(chatInfoSQLHelper.getTableName(), new String[] { "id", "sendID", "receiverID", "chatting", "date", "style" }, "id=?", new String[] { String.valueOf(id) }, null, null, null); if (cursor.moveToNext()) { ChattingInfo chattingInfo = new ChattingInfo( cursor.getInt(cursor.getColumnIndex("id")), cursor.getInt(cursor .getColumnIndex("sendID")), cursor.getInt(cursor .getColumnIndex("receiverID")), cursor.getString(cursor .getColumnIndex("date")), cursor.getString(cursor .getColumnIndex("chatting")), cursor.getInt(cursor .getColumnIndex("style"))); cursor.close(); return chattingInfo; } return null; } /* * 删除所有聊天记录 */ public void deteleAllChattingInfo() { chatInfoDataBase.delete(chatInfoSQLHelper.getTableName(), null, null); } /* * 参数:聊天记录的一系列序号如(1,2,3) 作用:用来删除聊天记录 */ public void deteleChattingInfo(Integer... ids) { if (ids.length > 0) { StringBuffer sb = new StringBuffer(); String[] strPid = new String[ids.length]; for (int i = 0; i < ids.length; i++) { sb.append('?').append(','); strPid[i] = String.valueOf(ids[i]); } sb.deleteCharAt(sb.length() - 1); // db = helper.getWritableDatabase(); chatInfoDataBase.delete(chatInfoSQLHelper.getTableName(), "id in (" + sb + ")", strPid); } } /* * 用来获取近期的一系列聊天记录 参数:start为开始位置,count为最大记录数,(倒序排列) 放回List<chattingInfo> */ public List<ChattingInfo> getScrollDataOfChattingInfo(int start, int count) { List<ChattingInfo> info = new ArrayList<ChattingInfo>(); // db = helper.getWritableDatabase(); Cursor cursor = chatInfoDataBase.query(chatInfoSQLHelper.getTableName(), new String[] { "id", "sendID", "receiverID", "chatting", "date", "style" }, null, null, null, null, "id desc", start + "," + count); while (cursor.moveToNext()) { info.add(new ChattingInfo(cursor.getInt(cursor.getColumnIndex("id")), cursor .getInt(cursor.getColumnIndex("sendID")), cursor.getInt(cursor .getColumnIndex("receiverID")), cursor.getString(cursor.getColumnIndex("date")), cursor.getString(cursor .getColumnIndex("chatting")), cursor.getInt(cursor .getColumnIndex("style")))); } cursor.close(); return info; } /* * 用来获取近期的一系列聊天记录 参数:start为开始位置,count为最大记录数,(倒序排列) 放回List<chattingInfo> */ public List<Message> getScrollMessageOfChattingInfo(int start, int count, int senderID, int recieverID) { List<Message> messages = new ArrayList<Message>(); Cursor cursor = chatInfoDataBase.query( chatInfoSQLHelper.getTableName(), new String[] { "id", "sendID", "receiverID", "chatting", "date", "style" }, "(sendID=? and receiverID=?) or (receiverID=? and sendID=?)", new String[] { String.valueOf(senderID), String.valueOf(recieverID), String.valueOf(senderID), String.valueOf(recieverID) }, null, null, "id desc", start + "," + count); while (cursor.moveToNext()) { Message message = chattingInfoToMessage(new ChattingInfo(cursor.getInt(cursor .getColumnIndex("id")), cursor.getInt(cursor.getColumnIndex("sendID")), cursor.getInt(cursor.getColumnIndex("receiverID")), cursor.getString(cursor .getColumnIndex("date")), cursor.getString(cursor .getColumnIndex("chatting")), cursor.getInt(cursor .getColumnIndex("style")))); messages.add(message); } cursor.close(); Collections.reverse(messages); return messages; } private Message chattingInfoToMessage(ChattingInfo chattingInfo) { Message message = new Message(); message.setMsgContent(chattingInfo.getInfo()); // 设置聊天信息内容 message.setSendTime(chattingInfo.getDate());// 设置聊天信息发送时间 switch (chattingInfo.getStyle())// 设置聊天信息类型 { case 0: message.setContentType(CONTENT_TYPE.TEXT); break; case 1: message.setContentType(CONTENT_TYPE.IMAGE); break; case 2: message.setContentType(CONTENT_TYPE.FILE); break; case 3: message.setContentType(CONTENT_TYPE.VOICE); break; } message.setSenderIMEI(getIMEIByUserID(chattingInfo.getSendID()));// 设置发送方ID return message; } /* * 作用: 用来获取表中聊天记录总数量 */ public long getCountOfChattingInfo() { // db = helper.getWritableDatabase(); Cursor cursor = chatInfoDataBase.query(chatInfoSQLHelper.getTableName(), new String[] { "count(*)" }, null, null, null, null, null); if (cursor.moveToNext()) { long count = cursor.getLong(0); cursor.close(); return count; } return 0; } /* * 该函数将所有数据库中聊天信息表的信息用JSON形式的String来表示 */ public String sendChattingInfoToJSON(int sendID, int receiverID) { List<ChattingInfo> infos; infos = getAllMessageFromChattingInfo(sendID, receiverID); JSONStringer jsonText = new JSONStringer(); try { // 首先是{,对象开始。object和endObject必须配对使用 jsonText.object(); jsonText.key("chatting"); // 键user的值是数组。array和endArray必须配对使用 jsonText.array(); for (ChattingInfo info : infos) { jsonText.object(); jsonText.key("id"); jsonText.value(info.getId()); jsonText.key("sendID"); jsonText.value(info.getSendID()); jsonText.key("receiverID"); jsonText.value(info.getReceiverID()); jsonText.key("chatting"); jsonText.value(info.getInfo()); jsonText.key("date"); jsonText.value(info.getDate()); jsonText.key("style"); jsonText.value(info.getStyle()); jsonText.endObject(); } jsonText.endArray(); // },对象结束 jsonText.endObject(); } catch (JSONException ex) { throw new RuntimeException(ex); } return jsonText.toString(); } }