从Jabberd2迁移到Openfire(三)


保存数据的Bean对象和DB连接类创建好之后,我们开始真正的获取数据,从Mysql数据中获得用户的基本信息及属于每个用户的列表信息。
首先是定义了两个私有的成员属性List<User> user和List<Group> group,然后提供两个publi的get方法用于取得这两个List。
然后再来看构造函数,首先初始化连接,然后调用本类的getUserInfo()和getGroupInfo()方法取得用户基本信息及每个用户的列表信息,然后关闭数据库连接。
下面分别来说getUserInfo和getGroupInfo

  1. getUserInfo中的查询SQL文使用左连接,因为有些用户没有设置个人信息,这样在vcard表中就不会存在该用户的基本信息,比如昵称等等。
  2. getGroupInfo中的查询使用了右连接,在实际导入数据的时候发现有些帐号的联系人列表仅存在roster-items表中,在roster-groups中不存在,所以使用了右连接

GetUserInfo.java

public class GetUserInfo {

private List<User> user;
private List<Group> group;

public GetUserInfo() {
DBConnection.initConnection();
getUserInfo();
getGroupInfo();
DBConnection.destoryConnction();
}

private void getUserInfo() {
Logger logger = Logger.getLogger(“GetUserInfo”);
user = new ArrayList<User>();
PreparedStatement ps = null;
ResultSet rs = null;
String sql = “SELECT a.username,a.`password`,a.realm,v.nickname,v.`n-family`, v.`n-given`, v.`n-middle`”
+ ” FROM authreg a left join vcard v”
+ ” ON v.`collection-owner` = concat(concat(a.username, ‘@’), a.realm);”;
try {
ps = DBConnection.getConnection().prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
int i = 1;
user.add(new User(rs.getString(i++), rs.getString(i++),rs.getString(i++), rs
.getString(i++), rs.getString(i++), rs.getString(i++),
rs.getString(i++)));
}
rs.close();
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}

logger.info(“User: ” + user.toString());
}

private void getGroupInfo() {
Logger logger = Logger.getLogger(“GetUserInfo”);
group = new LinkedList<Group>();
PreparedStatement ps = null;
ResultSet rs = null;
String sql = “SELECT rg.`collection-owner`, rg.jid, ri.name, rg.`group`”
+ ” FROM `roster-groups` rg right join `roster-items` ri”
+ ” on rg.`collection-owner` = ri.`collection-owner` and rg.jid = ri.jid”
+ ” GROUP BY `collection-owner`, jid, `group`”
+ ” ORDER BY `collection-owner`, jid asc;”;
try {
ps = DBConnection.getConnection().prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()) {
int i = 1;
group.add(new Group(rs.getString(i++), rs.getString(i++), rs
.getString(i++), rs.getString(i++)));
}
rs.close();
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
logger.info(“Group: ” + group.toString().substring(0, 2000) + ” …”);
}

/**
* 取得用户列表
*
* @return the user
*/
public List<User> getUser() {
return user;
}

/**
* 取得组列表
*
* @return the group
*/
public List<Group> getGroup() {
return group;
}
}

Advertisements

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s