@Override
  public DBFriendGroupMapping findByAccount(String account) {
    db = getTransactionDB();
    Cursor cursor =
        db.rawQuery(
            "SELECT * FROM "
                + DBColumns.FRIEND_STATE_TABLE_NAME
                + " WHERE "
                + DBColumns.FRIEND_STATE_ACCOUNT
                + "=?",
            new String[] {account});
    DBFriendGroupMapping mapping = null;
    if (cursor.moveToFirst()) {
      // 查找user
      String userAccount =
          cursor.getString(cursor.getColumnIndex(DBColumns.FRIEND_STATE_USER_ACCOUNT));
      DBUser user = userDao.findByAccount(userAccount);

      // 查找friendgroup
      String friendGroupAccount =
          cursor.getString(cursor.getColumnIndex(DBColumns.FRIEND_STATE_FRIEND_GROUP_ACCOUNT));
      DBFriendGroup friendGroup = fgDao.findByAccount(friendGroupAccount);

      mapping = DBObjectHelper.getDB3FriendGroupMapping(user, friendGroup, cursor);
    }
    cursor.close();

    return mapping;
  }
  @Override
  public List<DBUser> findByFriendGroup(DBFriendGroup friendGroup) {
    db = getTransactionDB();

    Cursor cursor =
        db.rawQuery(
            "SELECT "
                + DBColumns.FRIEND_STATE_USER_ACCOUNT
                + " FROM "
                + DBColumns.FRIEND_STATE_TABLE_NAME
                + " WHERE "
                + DBColumns.FRIEND_STATE_FRIEND_GROUP_ACCOUNT
                + "=?",
            new String[] {friendGroup.getAccount()});
    int i = 0;
    String[] us = new String[cursor.getCount()];
    while (cursor.moveToNext()) {
      us[i++] = cursor.getString(0);
    }
    cursor.close();

    List<DBUser> users = new ArrayList<DBUser>();
    for (int j = 0; j < us.length; j++) {
      users.add(userDao.findByAccount(us[j]));
    }

    return users;
  }
  @Override
  // public synchronized void update(String userAccount, XMPPUser contact) {
  public void update(String userAccount, XMPPUser contact) {
    db = getTransactionDB();

    synchronized (synchObj) {

      // 是否存在
      DBUser user = userDao.findByAccount(userAccount);
      if (user == null) {
        return;
      }

      DBUser ct = userDao.findByName(contact.getJid());
      if (ct == null) {
        add(userAccount, contact);

        return;
      }

      // 查找group,不存在会创建新的分组
      String group = contact.getGroup();
      DBFriendGroup fg = fgDao.find(userAccount, group);

      DBFriendGroupMapping mp = new DBFriendGroupMapping();
      mp.setUser(ct);
      mp.setFriendGroup(fg);
      mp.setLoginState(contact.getStatusMode());
      mp.setRemark(contact.getNickname());

      // 查找mapping中是否存在
      DBFriendGroupMapping mapping = find(userAccount, ct.getAccount());
      if (mapping != null) {
        // 更新
        mp.setAccount(mapping.getAccount());
        update(mp);
      } else {
        // 不存在,添加
        String acc = Integer.parseInt(getMaxAccount()) + 1 + "";
        mp.setAccount(acc);
        add(mp);
      }
    }
  }
  @Override
  // public synchronized void add(String account, XMPPUser contact) {
  public void add(String account, XMPPUser contact) {
    db = getTransactionDB();

    synchronized (synchObj) {

      // 查找group
      String group = contact.getGroup();
      DBFriendGroup fg = fgDao.find(account, group);

      // 查找contact
      String jid = contact.getJid();
      DBUser ct = userDao.findByName(jid);
      if (ct == null) {
        // 添加contact
        // 查找account
        String ua = userDao.findMaxAccount();
        ua = Integer.parseInt(ua) + 1 + "";
        ct = new DBUser();
        ct.setAccount(ua);
        ct.setNickname(jid);
        // ct.setState(contact.getStatusMode());
        ct.setDesp(contact.getStatusMessage());
        ct.setRegisterTime(new Date().getTime());
        userDao.add(ct);
      }

      // 是否已经存在,add(mapping)中会检查

      // 添加mapping
      DBFriendGroupMapping mapping = new DBFriendGroupMapping();
      mapping.setFriendGroup(fg);
      mapping.setUser(ct);
      mapping.setRemark(contact.getNickname());
      mapping.setLoginState(contact.getStatusMode());
      String ac = Integer.parseInt(getMaxAccount()) + 1 + "";
      mapping.setAccount(ac);
      add(mapping);
    }
  }
  @Override
  public int add(DBFriendGroupMapping mapping) {
    db = getTransactionDB();

    // 查找user
    DBUser user = userDao.findByAccount(mapping.getUser().getAccount());
    if (user == null) {
      return DBColumns.ERROR_USER_NOT_FOUND;
    }

    // 查找group
    DBFriendGroup fg = fgDao.findByAccount(mapping.getFriendGroup().getAccount());
    if (fg == null) {
      fgDao.add(mapping.getFriendGroup());
    }

    // 是否已经存在
    if (isExists(mapping.getUser().getAccount(), mapping.getFriendGroup().getAccount())) {
      return DBColumns.ERROR_FRIEND_STATE_EXISTS;
    }

    // 添加
    db.execSQL(
        "INSERT INTO "
            + DBColumns.FRIEND_STATE_TABLE_NAME
            + "("
            + DBColumns.FRIEND_STATE_ACCOUNT
            + ","
            + DBColumns.FRIEND_STATE_USER_ACCOUNT
            + ","
            + DBColumns.FRIEND_STATE_FRIEND_GROUP_ACCOUNT
            + ","
            + DBColumns.FRIEND_STATE_LOGIN_STATE
            + ","
            + DBColumns.FRIEND_STATE_LOGIN_CHANNEL
            + ","
            + DBColumns.FRIEND_STATE_POSITION
            + ","
            + DBColumns.FRIEND_STATE_REMARK
            + ") VALUES(?,?,?,?,?,?,?)",
        new String[] {
          mapping.getAccount(),
          mapping.getUser().getAccount(),
          mapping.getFriendGroup().getAccount(),
          mapping.getLoginState() + "",
          mapping.getLoginChannel() + "",
          mapping.getPosition() + "",
          mapping.getRemark()
        });

    return DBColumns.RESULT_OK;
  }
  @Override
  public void delete(String userAccount, String jid) {
    db = getTransactionDB();

    DBUser contact = userDao.findByName(jid);
    if (contact == null) {
      return;
    }

    // 查找对应的account
    // select fs.[account] from friend_state fs,friend_group fg where
    // fs.[user_account]='100003' AND fs.[friend_group_account]=fg.[account]
    // and fg.[user_account]='100000'
    DBFriendGroupMapping mapping = find(userAccount, contact.getAccount());
    delete(mapping);
    // db.execSQL("DELETE * FROM " + DBColumns., new Object[]{});
  }
  @Override
  public int delete(DBUser user) {
    db = getTransactionDB();

    DBUser u = userDao.findByAccount(user.getAccount());
    if (u == null) {
      return DBColumns.ERROR_USER_NOT_FOUND;
    }

    db.execSQL(
        "DELETE FROM "
            + DBColumns.FRIEND_STATE_TABLE_NAME
            + " WHERE "
            + DBColumns.FRIEND_STATE_USER_ACCOUNT
            + "=?",
        new Object[] {user.getAccount()});

    return DBColumns.RESULT_OK;
  }
  @Override
  public DBFriendGroupMapping find(String userAccount, String contactAccount) {
    db = getTransactionDB();

    // 要查询的用户
    DBUser user = userDao.findByAccount(contactAccount);

    // select fs.* from friend_state fs,friend_group fg where
    // fs.[user_account]='100002' and fs.[friend_group_account]=fg.[account]
    // and fg.[user_account]='100000'
    Cursor cursor =
        db.rawQuery(
            "SELECT fs.* FROM "
                + DBColumns.FRIEND_STATE_TABLE_NAME
                + " fs,"
                + DBColumns.FRIEND_GROUP_TABLE_NAME
                + " fg WHERE fs."
                + DBColumns.FRIEND_STATE_USER_ACCOUNT
                + "=? AND fs."
                + DBColumns.FRIEND_STATE_FRIEND_GROUP_ACCOUNT
                + "=fg."
                + DBColumns.FRIEND_GROUP_ACCOUNT
                + " AND fg."
                + DBColumns.FRIEND_GROUP_USER_ACCOUNT
                + "=?",
            new String[] {contactAccount, userAccount});

    DBFriendGroupMapping mapping = null;
    if (cursor.moveToFirst()) {
      String friendGroupAccount =
          cursor.getString(cursor.getColumnIndex(DBColumns.FRIEND_STATE_FRIEND_GROUP_ACCOUNT));
      DBFriendGroup friendGroup = fgDao.findByAccount(friendGroupAccount);
      mapping = DBObjectHelper.getDB3FriendGroupMapping(user, friendGroup, cursor);
    }
    cursor.close();

    return mapping;
  }
  @Override
  public List<DBFriendGroupMapping> findMappingByFriendGroup(DBFriendGroup friendGroup) {
    // friendgroup中分组中包含的user,及user状态
    db = getTransactionDB();
    Cursor cursor =
        db.rawQuery(
            "SELECT * FROM "
                + DBColumns.FRIEND_STATE_TABLE_NAME
                + " WHERE "
                + DBColumns.FRIEND_STATE_FRIEND_GROUP_ACCOUNT
                + "=?",
            new String[] {friendGroup.getAccount()});
    List<DBFriendGroupMapping> mappings = new ArrayList<DBFriendGroupMapping>();
    while (cursor.moveToNext()) {
      String userAccount =
          cursor.getString(cursor.getColumnIndex(DBColumns.FRIEND_STATE_USER_ACCOUNT));
      DBUser user = userDao.findByAccount(userAccount);
      mappings.add(DBObjectHelper.getDB3FriendGroupMapping(user, friendGroup, cursor));
    }
    cursor.close();

    return mappings;
  }
  @Override
  public String getRemark(String userAccount, String contactAccount) {
    db = getTransactionDB();

    // select fs.[remark] from friend_state fs,friend_group fg where
    // fs.user_account='100009' and fg.[user_account]='100000' and
    // fs.[friend_group_account] = fg.[account];
    Cursor cursor =
        db.rawQuery(
            "SELECT fs."
                + DBColumns.FRIEND_STATE_REMARK
                + " FROM "
                + DBColumns.FRIEND_STATE_TABLE_NAME
                + " fs,"
                + DBColumns.FRIEND_GROUP_TABLE_NAME
                + " fg WHERE fs."
                + DBColumns.FRIEND_STATE_USER_ACCOUNT
                + "=? AND fg."
                + DBColumns.FRIEND_GROUP_USER_ACCOUNT
                + "=? AND fs."
                + DBColumns.FRIEND_STATE_FRIEND_GROUP_ACCOUNT
                + "=fg."
                + DBColumns.FRIEND_GROUP_ACCOUNT,
            new String[] {contactAccount, userAccount});
    String remark = null;
    if (cursor.moveToFirst()) {
      remark = cursor.getString(0);
    }
    cursor.close();

    if (remark == null) {
      remark = userDao.findByAccount(contactAccount).getNickname();
    }

    return remark;
  }
  @Override
  public void updateXMPPFriendState(String userAccount, String jid, int status) {
    // public void updateXMPPFriendState(String jid, int status) {
    Logger.d("db", "user:"******",jid:" + jid + ",status:" + status);
    // 更新对应状态
    db = getTransactionDB();

    // 根据jid查询user_account
    // String userAccount = userDao.findByName(jid).getAccount();
    DBUser contact = userDao.findByName(jid);
    if (contact == null) {
      // 抛出异常,暂时不做
      return;
    }

    String contactAccount = contact.getAccount();
    // update friend_state set login_state=5 where user_account='100120' and
    // exists(select * from friend_group where account='33' and user_account='100118');
    // group_account不好获取
    //		db.execSQL("UPDATE " +DBColumns.FRIEND_STATE_TABLE_NAME
    //				+ " SET " + DBColumns.FRIEND_STATE_LOGIN_STATE + "=? WHERE "
    //				+ DBColumns.FRIEND_STATE_USER_ACCOUNT + "=? AND EXISTS(SELECT * FROM "
    //				+ DBColumns.FRIEND_GROUP_TABLE_NAME + " WHERE "
    //				+ DBColumns.FRIEND_GROUP_ACCOUNT + "=? AND "
    //				+ DBColumns.FRIEND_GROUP_USER_ACCOUNT + "=?)"
    //				,new Object[]{status,contactAccount});

    // 查询对应的friend_state_account,然后修改
    // select fs.account
    // from friend_state fs,friend_group fg
    // where fs.[user_account]='100120'
    // and fs.[friend_group_account]=fg.[account]
    // and fg.[user_account]='100112'
    Cursor cursor =
        db.rawQuery(
            "SELECT fs."
                + DBColumns.FRIEND_STATE_ACCOUNT
                + " FROM "
                + DBColumns.FRIEND_STATE_TABLE_NAME
                + " fs,"
                + DBColumns.FRIEND_GROUP_TABLE_NAME
                + " fg WHERE fs."
                + DBColumns.FRIEND_STATE_USER_ACCOUNT
                + "=? AND fs."
                + DBColumns.FRIEND_STATE_FRIEND_GROUP_ACCOUNT
                + "=fg."
                + DBColumns.FRIEND_GROUP_ACCOUNT
                + " AND fg."
                + DBColumns.FRIEND_GROUP_USER_ACCOUNT
                + "=?",
            new String[] {contactAccount, userAccount});
    String account = null;
    if (cursor.moveToFirst()) {
      account = cursor.getString(0);
    }
    cursor.close();

    if (account == null) {
      // 不存在,抛出异常,暂不处理
      return;
    }

    // 修改状态
    db.execSQL(
        "UPDATE "
            + DBColumns.FRIEND_STATE_TABLE_NAME
            + " SET "
            + DBColumns.FRIEND_STATE_LOGIN_STATE
            + "=? WHERE "
            + DBColumns.FRIEND_STATE_ACCOUNT
            + "=?",
        new Object[] {status, account});
  }
 @Override
 public void close() {
   super.close();
   userDao.close();
   fgDao.close();
 }