/**
* 删除会话
*
* @param conv
* @return
*/
public Boolean deleteConv(Conversation conv) {
Boolean boolRtn = false;
if (null == conv) {
return boolRtn;
}
SQLiteDatabase dbMaster = null;
try {
dbMaster = helper.getWritableDatabase();
dbMaster.beginTransaction();
int cid = conv.getCid();
dbMaster.delete("t_conversation", "" + "cid" + " == ?", new String[] {String.valueOf(cid)});
dbMaster.setTransactionSuccessful();
boolRtn = true;
} catch (SQLException e) {
LogUtil.info(e.toString());
} finally {
if (null != dbMaster) {
dbMaster.endTransaction();
}
// dbMaster.close();
}
return boolRtn;
}
/** 根据聊天双方的ID查看时候有会话缓存 */
public Conversation queryConversationInfo(String fromId, String toId) {
Conversation conv = null;
Cursor c = null;
SQLiteDatabase dbSlaver = null;
try {
dbSlaver = helper.getReadableDatabase();
String selection = "(fromuserId=? and touserId=?) or (touserId=? and fromuserId=?)";
String[] selectionArgs = {fromId, toId, fromId, toId};
c = dbSlaver.query("t_conversation", null, selection, selectionArgs, null, null, null);
if (c != null && c.moveToNext()) {
conv = new Conversation();
conv.setCid(c.getInt(c.getColumnIndex("cid")));
conv.setChatterId(c.getString(c.getColumnIndex("chatterid")));
conv.setConversationType(c.getInt(c.getColumnIndex("conversationtype")));
conv.setExt(c.getString(c.getColumnIndex("ext")));
conv.setPortraitImg(c.getString(c.getColumnIndex("portraitimg")));
conv.setConversationName(c.getString(c.getColumnIndex("conversationname")));
conv.setFromuserid(c.getString(c.getColumnIndex("fromuserid")));
conv.setTouserid(c.getString(c.getColumnIndex("touserid")));
conv.setLastMeaage(c.getString(c.getColumnIndex("lastmessage")));
conv.setLastTime(c.getString(c.getColumnIndex("lasttime")));
conv.setContentType(c.getInt(c.getColumnIndex("contenttype")));
}
} catch (SQLException e) {
LogUtil.info(e.toString());
} finally {
if (null != c) {
c.close();
}
}
return conv;
}
/**
* 新增会话
*
* @param conv 会话实体
*/
public void saveConv(Conversation conv) {
SQLiteDatabase dbMaster = null;
try {
dbMaster = helper.getWritableDatabase();
dbMaster.beginTransaction();
dbMaster.execSQL(
IMDbHelper.INSERT_CONVERSATION_SQL,
new Object[] {
conv.getChatterId(),
conv.getConversationType(),
conv.getExt(),
conv.getPortraitImg(),
conv.getFromuserid(),
conv.getTouserid(),
conv.getConversationName(),
conv.getLastMeaage(),
conv.getLastTime(),
conv.getContentType()
});
dbMaster.setTransactionSuccessful();
} catch (SQLException e) {
LogUtil.info(e.toString());
} finally {
if (null != dbMaster) {
dbMaster.endTransaction();
}
// dbMaster.close();
}
}
/**
* 查询会话表
*
* @param num 查询数量
* @param startIndex 查询开始索引
* @return
*/
public List<Conversation> queryConverInfo(int num, int startIndex) {
Conversation conv = null;
List<Conversation> convList = new ArrayList<Conversation>();
Cursor c = null;
SQLiteDatabase dbSlaver = null;
try {
dbSlaver = helper.getReadableDatabase();
String querySql = "select * from t_conversation";
c = dbSlaver.rawQuery(querySql, null);
while (c.moveToNext()) {
conv = new Conversation();
conv.setCid(c.getInt(c.getColumnIndex("cid")));
conv.setChatterId(c.getString(c.getColumnIndex("chatterid")));
conv.setConversationType(c.getInt(c.getColumnIndex("conversationtype")));
conv.setExt(c.getString(c.getColumnIndex("ext")));
conv.setPortraitImg(c.getString(c.getColumnIndex("portraitimg")));
conv.setConversationName(c.getString(c.getColumnIndex("conversationname")));
convList.add(conv);
}
} catch (SQLException e) {
LogUtil.info(e.toString());
} finally {
if (null != c) {
c.close();
}
// dbSlaver.close();
}
Collections.reverse(convList);
return convList;
}
/**
* 删除一条消息
*
* @param msg
* @return Boolean
*/
public Boolean delete(MessageInfo msg) {
Boolean boolRtn = false;
if (null == msg) {
return boolRtn;
}
SQLiteDatabase dbMaster = null;
try {
dbMaster = helper.getWritableDatabase();
dbMaster.beginTransaction();
String msgId = msg.getMessageId();
dbMaster.delete("t_message", "" + "msgId" + " == ?", new String[] {msgId});
dbMaster.setTransactionSuccessful();
boolRtn = true;
} catch (SQLException e) {
LogUtil.info(e.toString());
} finally {
if (null != dbMaster) {
dbMaster.endTransaction();
}
// dbMaster.close();
}
return boolRtn;
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
try {
sqLiteDatabase.execSQL(create);
} catch (SQLException e) {
Log.d("hel", e.toString());
}
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
try {
sqLiteDatabase.execSQL(drop);
onCreate(sqLiteDatabase);
} catch (SQLException e) {
Log.d("hel", e.toString());
}
}
public void excludeEpisodesBd(long serieId) {
String where = "id_serie = " + serieId;
try {
super.open();
db.delete(TABLE_NAME, where, null);
} catch (SQLException e) {
Log.e("SeriesManager", "Error trying to exclude Episode: " + e.toString());
} finally {
super.close();
}
}
@Override
public void onCreate(SQLiteDatabase db) {
String[] createSqls = mContext.getString(R.string.create_db_sql).split("\n");
db.beginTransaction();
try {
executeManySqlStatements(db, createSqls);
db.setTransactionSuccessful();
} catch (SQLException e) {
Log.e("Error creating tables and debug data", e.toString());
} finally {
db.endTransaction();
}
}
/**
* 根据conversationId查询消息记录
*
* @param conversationId
* @param num
* @param startIndex
* @return
*/
public List<MessageInfo> queryInfoByConversationId(
String conversationId, int num, int startIndex) {
MessageInfo msg = null;
List<MessageInfo> msgList = new ArrayList<MessageInfo>();
Cursor c = null;
SQLiteDatabase dbSlaver = null;
try {
dbSlaver = helper.getReadableDatabase();
String querySql =
"select * from t_message"
+ " where conversationid=? order by mid desc limit "
+ num
+ " offset "
+ startIndex;
c = dbSlaver.rawQuery(querySql, new String[] {conversationId + ""});
while (c.moveToNext()) {
msg = new MessageInfo();
msg.setMid(c.getInt(c.getColumnIndex("mid")));
msg.setMessageId(c.getString(c.getColumnIndex(IMDbHelper.MESSAGE_ID)));
msg.setFromuserId(c.getString(c.getColumnIndex(IMDbHelper.FROM_USER_ID)));
msg.setTouserId(c.getString(c.getColumnIndex(IMDbHelper.TO_USER_ID)));
msg.setIsRequireEncryption(c.getInt(c.getColumnIndex(IMDbHelper.IS_REQUIRE_ENCRYPTION)));
msg.setIsEncryptedOnServer(c.getInt(c.getColumnIndex(IMDbHelper.IS_ENCRYPTED_ON_SERVER)));
msg.setIsRead(c.getInt(c.getColumnIndex(IMDbHelper.IS_READ)));
msg.setTimesTamp(c.getString(c.getColumnIndex(IMDbHelper.TIMES_TAMP)));
msg.setIsReadacked(c.getInt(c.getColumnIndex(IMDbHelper.IS_READACKED)));
msg.setIsDeliveredacked(c.getInt(c.getColumnIndex(IMDbHelper.IS_DELIVERED_HACKED)));
msg.setContentType(c.getInt(c.getColumnIndex(IMDbHelper.CONTENT_TYPE)));
msg.setContent(c.getString(c.getColumnIndex(IMDbHelper.CONTENT)));
msg.setConversationId(c.getString(c.getColumnIndex(IMDbHelper.CONVERSATION_ID)));
msg.setSenderName(c.getString(c.getColumnIndex(IMDbHelper.SENDER_NAME)));
msg.setExt(c.getString(c.getColumnIndex(IMDbHelper.EXT)));
msg.setDeliveryState(c.getInt(c.getColumnIndex(IMDbHelper.DELIVERY_STATE)));
msg.setIsAnonymous(c.getInt(c.getColumnIndex(IMDbHelper.IS_ANONYMOUS)));
msg.setMessageType(c.getInt(c.getColumnIndex(IMDbHelper.MESSAGE_TYPE)));
msg.setPortraitImg(c.getString(c.getColumnIndex(IMDbHelper.PORTRAIT_IMG)));
msg.setDuration(c.getInt(c.getColumnIndex(IMDbHelper.DURATION)));
msg.setAudioFilePath(c.getString(c.getColumnIndex(IMDbHelper.AUDIO_FILE_PATH)));
msgList.add(msg);
}
} catch (SQLException e) {
LogUtil.info(e.toString());
} finally {
if (null != c) {
c.close();
}
// dbSlaver.close();
}
Collections.reverse(msgList);
return msgList;
}
public void addEpisode(Episode episode, Serie serie) {
try {
super.open();
ContentValues values = new ContentValues();
values.put("id_serie", serie.getId());
values.put("name", episode.getName());
values.put("season", episode.getSeason());
values.put("number", episode.getNumber());
db.insert(TABLE_NAME, null, values);
} catch (SQLException e) {
Log.e("SeriesManager", "Error trying to include Episode: " + e.toString());
} finally {
super.close();
}
}
public void updateEpisode(Episode episode, Serie serie) {
try {
String where = "pk_id = " + episode.getId();
super.open();
ContentValues values = new ContentValues();
values.put("id_serie", serie.getId());
values.put("name", episode.getName());
values.put("season", episode.getSeason());
values.put("number", episode.getNumber());
db.update(TABLE_NAME, values, where, null);
} catch (SQLException e) {
Log.e("SeriesManager", "Error trying to update Episode: " + e.toString());
} finally {
super.close();
}
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
Log.d(TAG, "create db");
try {
sqLiteDatabase.execSQL(
"create table if not exists "
+ FRUIT_TABLE
+ "("
+ ID
+ " integer primary key,"
+ NAME
+ " text,"
+ DESC
+ " text"
+ ")");
} catch (SQLException e) {
Log.e(TAG, "Exception while creating DB " + e.toString());
}
}
/** 查询最后一条聊天记录 */
public MessageInfo queryMessageInfoByFromIdAndToId(String fromId, String toId) {
MessageInfo msg = null;
Cursor c = null;
SQLiteDatabase dbSlaver = null;
try {
dbSlaver = helper.getReadableDatabase();
String querySql =
"select * from t_message where (fromuserid like ? and touserid like ?) or (touserid like ? and fromuserid like ?) order by mid desc limit 1";
c =
dbSlaver.rawQuery(
querySql, new String[] {fromId + "%", toId + "%", fromId + "%", toId + "%"});
if (c != null && c.moveToFirst()) {
msg = new MessageInfo();
msg.setMid(c.getInt(c.getColumnIndex("mid")));
msg.setMessageId(c.getString(c.getColumnIndex("messageid")));
msg.setFromuserId(c.getString(c.getColumnIndex("fromuserid")));
msg.setTouserId(c.getString(c.getColumnIndex("touserid")));
msg.setIsRequireEncryption(c.getInt(c.getColumnIndex("isrequireencryption")));
msg.setIsEncryptedOnServer(c.getInt(c.getColumnIndex("isencryptedonserver")));
msg.setIsRead(c.getInt(c.getColumnIndex("isRead")));
msg.setTimesTamp(c.getString(c.getColumnIndex("timestamp")));
msg.setIsReadacked(c.getInt(c.getColumnIndex("isreadacked")));
msg.setIsDeliveredacked(c.getInt(c.getColumnIndex("isdeliveredacked")));
msg.setContentType(c.getInt(c.getColumnIndex("contenttype")));
msg.setContent(c.getString(c.getColumnIndex("content")));
msg.setConversationId(c.getString(c.getColumnIndex("conversationid")));
msg.setSenderName(c.getString(c.getColumnIndex("sendername")));
msg.setExt(c.getString(c.getColumnIndex("ext")));
msg.setDeliveryState(c.getInt(c.getColumnIndex("deliverystate")));
msg.setIsAnonymous(c.getInt(c.getColumnIndex("isanonymous")));
msg.setMessageType(c.getInt(c.getColumnIndex("messagetype")));
msg.setPortraitImg(c.getString(c.getColumnIndex("portraitimg")));
}
} catch (SQLException e) {
LogUtil.info(e.toString());
} finally {
if (null != c) {
c.close();
}
}
return msg;
}
/**
* 新增一条消息
*
* @param msg 消息体
* @return msgId 消息存储的唯一ID
*/
public void saveMsg(MessageInfo msg) {
SQLiteDatabase dbMaster = null;
try {
dbMaster = helper.getWritableDatabase();
dbMaster.beginTransaction();
dbMaster.execSQL(
IMDbHelper.INSERT_MESSAGE_SQL,
new Object[] {
msg.getMessageId(),
msg.getFromuserId(),
msg.getTouserId(),
msg.getIsRequireEncryption(),
msg.getIsEncryptedOnServer(),
msg.getIsRead(),
msg.getTimesTamp(),
msg.getIsReadacked(),
msg.getIsDeliveredacked(),
msg.getContentType(),
msg.getContent(),
msg.getConversationId(),
msg.getSenderName(),
msg.getExt(),
msg.getDeliveryState(),
msg.getIsAnonymous(),
msg.getMessageType(),
msg.getDuration(),
msg.getPortraitImg(),
msg.getAudioFilePath()
});
dbMaster.setTransactionSuccessful();
} catch (SQLException e) {
LogUtil.info(e.toString());
} finally {
if (null != dbMaster) {
dbMaster.endTransaction();
}
// dbMaster.close();
}
}
public int updateConversationInfoById(Conversation conversation) {
SQLiteDatabase dbMaster = null;
int row = -1;
try {
dbMaster = helper.getReadableDatabase();
ContentValues values = new ContentValues();
values.put("chatterid", conversation.getChatterId());
values.put("conversationtype", conversation.getConversationType());
values.put("ext", conversation.getExt());
values.put("portraitimg", conversation.getPortraitImg());
values.put("lastmessage", conversation.getLastMeaage());
values.put("lasttime", conversation.getLastTime());
values.put("contenttype", conversation.getContentType());
String where = "cid=" + conversation.getCid();
row = dbMaster.update("t_conversation", values, where, null);
} catch (SQLException e) {
LogUtil.info(e.toString());
}
return row;
}
/**
* 查询DB中某表的最后一条ID
*
* @return msgId
*/
public int queryLastInsertId(String tableName) {
int lastMsgId = 0;
Cursor c = null;
SQLiteDatabase dbSlaver = null;
try {
dbSlaver = getReadableDatabase();
c = dbSlaver.rawQuery("select last_insert_rowid() from " + tableName, null);
if (c.moveToFirst()) {
lastMsgId = c.getInt(0);
}
} catch (SQLException e) {
logger.e(e.toString());
} finally {
if (null != c) {
c.close();
}
// dbSlaver.close(); //这里不能关闭哦，因为是在其它DB语句中间操作的，由其它DB操作部分关闭即可
}
return lastMsgId;
}
/** 获取会话列表 */
public List<Conversation> queryConverList() {
Conversation conv = null;
List<Conversation> convList = new ArrayList<Conversation>();
Cursor c = null;
SQLiteDatabase dbSlaver = null;
try {
dbSlaver = helper.getReadableDatabase();
// String querySql =
// "select * from t_conversation inner join t_message on t_conversation.chatterid =
// t_message.conversationid";
String querySql = "select * from t_conversation";
c = dbSlaver.rawQuery(querySql, null);
while (c.moveToNext()) {
conv = new Conversation();
conv.setCid(c.getInt(c.getColumnIndex("cid")));
conv.setChatterId(c.getString(c.getColumnIndex("chatterid")));
conv.setConversationType(c.getInt(c.getColumnIndex("conversationtype")));
conv.setExt(c.getString(c.getColumnIndex("ext")));
conv.setPortraitImg(c.getString(c.getColumnIndex("portraitimg")));
conv.setConversationName(c.getString(c.getColumnIndex("conversationname")));
conv.setFromuserid(c.getString(c.getColumnIndex("fromuserid")));
conv.setTouserid(c.getString(c.getColumnIndex("touserid")));
conv.setLastMeaage(c.getString(c.getColumnIndex("lastmessage")));
conv.setLastTime(c.getString(c.getColumnIndex("lasttime")));
conv.setContentType(c.getInt(c.getColumnIndex("contenttype")));
convList.add(conv);
}
} catch (SQLException e) {
LogUtil.info(e.toString());
} finally {
if (null != c) {
c.close();
}
// dbSlaver.close();
}
Collections.reverse(convList);
return convList;
}
public List<String> getAll() {
Cursor c = null;
List<String> l = null;
String[] cols = {"item"};
try {
c = _db.query(true, ITEMS_TABLE, cols, null, null, null, null, null, null);
int DATA_FLD = c.getColumnIndex("item");
int numRows = c.getCount();
c.moveToFirst();
for (int i = 0; i < numRows; i++) {
if (l == null) l = new ArrayList<String>();
String n = c.getString(DATA_FLD);
l.add(n);
c.moveToNext();
}
} catch (SQLException ex) {
OeLog.e(ex.toString(), ex);
} finally {
if (c != null && !c.isClosed()) c.close();
}
return l;
}
public void insertArtistBulk(ArrayList<ArrayList<String>> artists) {
// db.execSQL("PRAGMA synchronous=OFF");
/*InsertHelper ih = new InsertHelper(db,"artistTbl");
ih.prepareForInsert();*/
Logging.Log(LOG_TAG, "Bulk inserting " + artists.size() + " artists");
db.execSQL("DELETE FROM artistTbl");
try {
db.beginTransaction();
for (int i = 0; i < artists.size(); i++) {
ContentValues artist = new ContentValues();
artist.put("artistName", artists.get(i).get(0));
artist.put("numShows", artists.get(i).get(1));
db.insert("artistTbl", null, artist);
}
db.setTransactionSuccessful();
} catch (SQLException e) {
Logging.Log(LOG_TAG, e.toString());
} finally {
db.endTransaction();
}
// ih.execute();
}
/**
* 查询本地存储的消息条数
*
* @return msgCount
*/
public int queryConvCount() {
String SQL_QUERY_CONV_COUNT = "SELECT COUNT(*) AS " + MSG_COUNT + " FROM t_conversation";
int msgCount = 0;
Cursor c = null;
SQLiteDatabase dbSlaver = null;
try {
dbSlaver = helper.getReadableDatabase();
c = dbSlaver.rawQuery(SQL_QUERY_CONV_COUNT, null);
while (c.moveToNext()) {
msgCount = c.getInt(c.getColumnIndex(MSG_COUNT));
}
} catch (SQLException e) {
LogUtil.info(e.toString());
} finally {
if (null != c) {
c.close();
}
// dbSlaver.close();
}
return msgCount;
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
Log.w(
"EarthQuakeDBUpgrade",
"Upgrading database from version "
+ oldVersion
+ " to "
+ newVersion
+ ", which will destroy all old data");
String[] createSqls = mContext.getString(R.string.create_db_sql).split("\n");
db.beginTransaction();
try {
executeManySqlStatements(db, createSqls);
db.setTransactionSuccessful();
} catch (SQLException e) {
Log.e("Error creating tables and debug data", e.toString());
} finally {
db.endTransaction();
}
// recreate database from scratch once again.
onCreate(db);
}
Cursor getBooksList(String keyword, int flag) {
String sql;
try {
if (flag == EntryActivity.ISBN) {
System.out.println("DBHELPER isbn" + flag);
sql = "select * from " + TABLE_NAME1 + " Where ISBN = " + keyword;
} else {
System.out.println("DBHELPER tag" + flag);
String word = "'" + keyword + "'";
sql = "select * from " + TABLE_NAME1 + " Where Tag = " + word;
}
Cursor mCur = myDatabase.rawQuery(sql, null);
// if (mCur != null) {
// mCur.moveToNext();
// }
return mCur;
} catch (SQLException mSQLException) {
Log.e("Data", "getTestData >>" + mSQLException.toString());
throw mSQLException;
}
}