package
table;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import page.circle.LoadPoTuiPage;
import javabean.BbsAddMessgaDTO;
import javabean.BbsThread;
import javabean.Code;
import javabean.CtrlFriends;
import javabean.TuiInfo;
import javabean.UserInfo;
import util.DBTable;
import util.bbs.BbsConstants;
import util.bbs.RecordsData;
import util.bbs.ThreadAttribute;
public class Excep ... {
public final static String SQUENCES_NAME = "SEQ_THREAD_ID";// squences名
/** *//**
* 查询:返回对象数组
*
* @param con
* @param grpid
* @return
* @throws Exception
*/
public static BbsThread[] loadSimpleTopicList(Connection con, long grpid)
throws Exception ...{
String sql = "select * from (select thread_id ,forum_id ,row_number() over"+
"(order by ltouch_time desc) as r from tab_bbs_thread where forum_id"+
"in (select forum_id from tab_bbs_forum where group_id=?)) where"+
"r<=8";
PreparedStatement pstm = null;
ResultSet rs = null;
ArrayList<BbsThread> list = new ArrayList<BbsThread>();
try ...{
pstm = con.prepareStatement(sql);
pstm.setLong(1, grpid);
rs = pstm.executeQuery();
while (rs.next()) ...{
BbsThread thr = new BbsThread();
thr.setThreadId(rs.getLong(1));
thr.setForumId(rs.getLong(2));
thr.setForum(BbsForumTable.loadForum(con, thr.getForumId()));
thr.setTopic(BbsMessageTable.loadSimpleTopic(con, thr
.getThreadId()));
list.add(thr);
}
} catch (SQLException e) ...{
throw new Exception(
"BbsThreadTable===============getSimpleTopicList has error!");
} finally ...{
if (rs != null) ...{
rs.close();
rs = null;
}
if (pstm != null) ...{
pstm.close();
pstm = null;
}
}
return (BbsThread[]) list.toArray(new BbsThread[list.size()]);
}
/** *//**
* 查询:返回int
*
* @param con
* @param frmid
* @return
* @throws Exception
*/
public static int loadThreadCount(Connection con, long frmid)
throws Exception ...{
String sql = "";
PreparedStatement pstm = null;
ResultSet rs = null;
int count = -1;
try ...{
pstm = con.prepareStatement(sql);
pstm.setLong(1, frmid);
rs = pstm.executeQuery();
if (rs.next()) ...{
count = rs.getInt(1);
}
} catch (SQLException e) ...{
throw new Exception(
"BbsThreadTable===============getThreadCount has error!");
} finally ...{
if (rs != null) ...{
rs.close();
rs = null;
}
if (pstm != null) ...{
pstm.close();
pstm = null;
}
}
return count;
}
/** *//**
* 查询:返回对象
*
* @param con
* @param thrid
* @return
* @throws Exception
*/
public static BbsThread loadThread(Connection con, long thrid)
throws Exception ...{
String sql = "";
PreparedStatement pstm = null;
ResultSet rs = null;
BbsThread thr = new BbsThread();
try ...{
pstm = con.prepareStatement(sql);
pstm.setLong(1, thrid);
rs = pstm.executeQuery();
if (rs.next()) ...{
thr.setThreadId(rs.getLong(1));
thr.setForumId(rs.getLong(2));
thr.setTattribute(rs.getInt(3));
thr.setLattribute(rs.getInt(4));
thr.setForum(BbsForumTable.loadForum(con, thr.getForumId()));
thr.setTopic(BbsMessageTable.loadSimpleTopic(con, thr
.getThreadId()));
}
} catch (SQLException e) ...{
throw new Exception(
"BbsThreadTable===============getThreadAttr has error!");
} finally ...{
if (rs != null) ...{
rs.close();
rs = null;
}
if (pstm != null) ...{
pstm.close();
pstm = null;
}
}
return thr;
}
/** *//**
* 插入:并返回long
*
* @param con
* @param amdto
* @return
* @throws Exception
*/
// 加入新帖,并返回线索ID
public static long insertThread(Connection con, BbsAddMessgaDTO amdto)
throws Exception ...{
String sql = "insert into "
+ TABLENAME
+ " values(?,?,"
+ "(select sysdate from dual),(select sysdate from dual),?,0,0,0,0,?)";
PreparedStatement pstm = null;
long thrid = DBTable
.loadSqencesValue(con, BbsThreadTable.SQUENCES_NAME);
try ...{
pstm = con.prepareStatement(sql);
pstm.setLong(1, thrid);
pstm.setLong(2, amdto.getForumid());
pstm.setInt(3, amdto.getAttribute());
pstm.setInt(4, amdto.getIsinPro());
pstm.executeUpdate();
} catch (SQLException e) ...{
throw new Exception(
"BbsThreadTable ===============insertThread has error!");
} finally ...{
if (pstm != null) ...{
pstm.close();
pstm = null;
}
}
return thrid;
}
/** *//**
* 查询:返回boolean
*
* @param con
* @param thrid
* @return
* @throws Exception
*/
public static boolean addThreadClick(Connection con, long thrid)
throws Exception ...{
String sql1 = "select " + COL_CLICK + " from " + TABLENAME + " where "
+ COL_THREAD_ID + "=?";
String sql2 = "update " + TABLENAME + " set " + COL_CLICK + "=? where "
+ COL_THREAD_ID + "=?";
PreparedStatement pstm = null;
ResultSet rs = null;
long click = 0;
try ...{
pstm = con.prepareStatement(sql1);
pstm.setLong(1, thrid);
rs = pstm.executeQuery();
if (rs.next()) ...{
click = rs.getLong(1);
}
pstm = con.prepareStatement(sql2);
pstm.setLong(1, click + 1);
pstm.setLong(2, thrid);
pstm.executeUpdate();
} catch (SQLException e) ...{
throw new Exception(
"BbsThreadTable ===============addThreadClick has error!");
} finally ...{
if (rs != null) ...{
rs.close();
rs = null;
}
if (pstm != null) ...{
pstm.close();
pstm = null;
}
}
return true;
}
/** *//**
* 更新:update
*
* @param con
* @param thrid
* @return
* @throws Exception
*/
// 更新回复时间
public static boolean updateTouchTime(Connection con, long thrid)
throws Exception ...{
String sql = "update " + TABLENAME + " set " + COL_LTOUCH_TIME
+ "=(select sysdate from dual) where " + COL_THREAD_ID + "=?";
PreparedStatement pstm = null;
try ...{
pstm = con.prepareStatement(sql);
pstm.setLong(1, thrid);
pstm.executeUpdate();
} catch (SQLException e) ...{
throw new Exception(
"BbsThreadTable ===============updateTouchTime has error!");
} finally ...{
if (pstm != null) ...{
pstm.close();
pstm = null;
}
}
return true;
}
rs.close();
rs = null;
/** *//**
* 更新:返回true
*
* @param con
* @param thrid
* @param isLock
* @return
* @throws Exception
*/
public static boolean updateLock(Connection con, long thrid, int isLock)
throws Exception ...{
String sql = "update " + TABLENAME + " set " + COL_LOCKED + "=? "
+ " where " + COL_THREAD_ID + "=?";
PreparedStatement pstm = null;
try ...{
pstm = con.prepareStatement(sql);
pstm.setInt(1, isLock);
pstm.setLong(2, thrid);
pstm.executeUpdate();
} catch (SQLException e) ...{
throw new Exception(
"BbsThreadTable ===============updateLock has error!");
} finally ...{
if (pstm != null) ...{
pstm.close();
pstm = null;
}
}
return true;
}
/** *//**
* 删除:返回boolean
*
* @param con
* @param thrid
* @return
* @throws Exception
*/
public static boolean deleteThread(Connection con, long thrid)
throws Exception ...{
String sql = "delete from tab_bbs_thread where thread_id=?";
PreparedStatement pstm = null;
try ...{
pstm = con.prepareStatement(sql);
pstm.setLong(1, thrid);
pstm.executeUpdate();
} catch (SQLException e) ...{
throw new Exception(
"BbsThreadTable ===============deleteThread has error!");
} finally ...{
if (pstm != null) ...{
pstm.close();
pstm = null;
}
}
return true;
}
/** *//**
* 迈兴志 2007-3-22 根据用户ID查询好友信息 调用过程包pkgFriends
*/
public static void loadFriends(Connection con, RecordsData records,
long userId, int pageId, int pageSet, int type, long tagid)
throws Exception ...{
CallableStatement proc = null;
ResultSet rs = null;
try ...{
proc = con.prepareCall("begin :1 := pkgFriends.getFriends("
+ userId + "," + pageId + "," + pageSet + "," + type + ","
+ tagid + "); end;");
proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
proc.execute();
rs = (ResultSet) proc.getObject(1);
while (rs.next()) ...{
CtrlFriends fr = new CtrlFriends();
fr.setMyUserId(rs.getLong(1));
fr.setFriendId(rs.getLong(2));
fr.setIsPublic(rs.getInt(3));
// 好友的信息
UserInfo ui = new UserInfo();
ui.setUserID(fr.getFriendId());
ui.setUserName(rs.getString(4));
ui.setAlias(rs.getString(5));
ui.setHeadPic(rs.getString(6));
ui.setFamilyArea(rs.getString(7));
Code[] code = OtherTable.loadCityArea(con, 10, ui
.getFamilyArea());
for (int i = 0; code != null && code.length > 0
&& i < code.length; i++) ...{
ui.setFamilyArea(code[i].getDesc());
}
// 标签处理 在JSP页面作拆分处理
fr.setTagId(rs.getString(8));
fr.setUser(ui);
fr.setRelationId(rs.getLong(9));
fr.setRelationName(rs.getString(10));
fr.setRelationType(rs.getInt(11));
records.getRecords().add(fr);
}
records.setBlockSize(pageSet);
records.setCurrBlock((pageId - 1) * pageSet + 1);
// 记录总数处理
proc = con.prepareCall("begin :1 := pkgFriends.getFriendsCntByTag("
+ userId + "," + type + ","+tagid+"); end;");
proc.registerOutParameter(1, oracle.jdbc.OracleTypes.NUMBER);
proc.execute();
// int i = (Integer) proc.getObject(1);
int i = proc.getInt(1);
records.setRecordsCount(i);
} catch (SQLException e) ...{
e.printStackTrace();
throw new Exception(
"CtrlFriendsTable查询好友信息===============loadFriends has error!");
} finally ...{
if (rs != null) ...{
rs.close();
rs = null;
}
if (proc != null) ...{
proc.close();
proc = null;
}
}
}
/** *//**
* 查询:带分页
*
* @param con
* @param records
* @param userid
* @param type
* @param name
* @param alias
* @param pageId
* @param pageSet
* @throws Exception
*/
public static void loadMyFriends(Connection con, RecordsData records,
long userid, int type, String name, String alias, int pageId,
int pageSet) throws Exception ...{
String contion1 = "";
String contion2 = "";
String contion3 = "";
if (name != null && !name.equals("")) ...{
contion1 = " where name like '%" + name + "%' ";
}
if (alias != null && !alias.equals("")) ...{
contion2 = " and alias like '%" + name + "%' ";
}
if (type != -1) ...{
contion3 = " where relation_type=" + type + " ";
}
String sql2 = "select count(*) from"
+ "(select id,name,alias, f_area,head_pic from tab_user_info "
+ contion1
+ ") i,"
+ "(select relation_id,relation_type,relation_name from tab_cir_relation"
+ contion3
+ ") cr,"
+ "(select my_user_id,friend_id,relation_id,tag_id,is_public from tab_ctrl_friends where my_user_id="
+ userid + ") f," + "(select tag_id,tag_name from tab_tag) t"
+ " where cr.relation_id=f.relation_id"
+ " and t.tag_id(+)=f.tag_id" + " and i.id=f.friend_id"
+ contion2;
String sql = "select * from (select f.my_user_id,friend_id,name fname,alias falias,i.head_pic fheadpic,f_area,f.relation_id,"
+ "cr.relation_name,f.tag_id,t.tag_name,cr.relation_type,is_public,row_number() over(order by i.id) r from"
+ "(select id,name,alias, f_area,head_pic from tab_user_info "
+ contion1
+ ") i,"
+ "(select relation_id,relation_type,relation_name from tab_cir_relation"
+ contion3
+ ") cr,"
+ "(select my_user_id,friend_id,relation_id,tag_id,is_public from tab_ctrl_friends where my_user_id="
+ userid
+ ") f,"
+ "(select tag_id,tag_name from tab_tag) t"
+ " where cr.relation_id=f.relation_id"
+ " and t.tag_id(+)=f.tag_id"
+ " and i.id=f.friend_id"
+ contion2 + ") where r between ? and ?";
PreparedStatement pstm = null;
ResultSet rs = null;
try ...{
pstm = con.prepareStatement(sql);
pstm.setInt(1, (pageId - 1) * pageSet + 1);
pstm.setInt(2, pageId * pageSet);
rs = pstm.executeQuery();
while (rs.next()) ...{
CtrlFriends fr = new CtrlFriends();
fr.setMyUserId(rs.getLong(1));
fr.setFriendId(rs.getLong(2));
UserInfo ui = new UserInfo();
ui.setUserID(fr.getFriendId());
ui.setUserName(rs.getString(3));
ui.setAlias(rs.getString(4));
ui.setHeadPic(rs.getString(5));
ui.setFamilyArea(rs.getString(6));
Code[] code = OtherTable.loadCityArea(con, 10, ui
.getFamilyArea());
for (int i = 0; code != null && code.length > 0
&& i < code.length; i++) ...{
ui.setFamilyArea(code[i].getDesc());
}
fr.setUser(ui);
fr.setRelationId(rs.getLong(7));
fr.setRelationName(rs.getString(8));
fr.setTagId(rs.getString(9));
fr.setTagName(rs.getString(10));
fr.setRelationType(type);
fr.setIsPublic(rs.getInt(12));
records.getRecords().add(fr);
}
records.setBlockSize(pageSet);
records.setCurrBlock((pageId - 1) * pageSet + 1);
records.setRecordsCount(getRecordCount(con, sql2));
} catch (SQLException e) ...{
e.printStackTrace();
throw new Exception(
"CtrlFriendsTable===============loadMyFriends has error!");
} finally ...{
if (rs != null) ...{
rs.close();
rs = null;
}
if (pstm != null) ...{
pstm.close();
pstm = null;
}
}
}
}
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import page.circle.LoadPoTuiPage;
import javabean.BbsAddMessgaDTO;
import javabean.BbsThread;
import javabean.Code;
import javabean.CtrlFriends;
import javabean.TuiInfo;
import javabean.UserInfo;
import util.DBTable;
import util.bbs.BbsConstants;
import util.bbs.RecordsData;
import util.bbs.ThreadAttribute;
public class Excep ... {
public final static String SQUENCES_NAME = "SEQ_THREAD_ID";// squences名
/** *//**
* 查询:返回对象数组
*
* @param con
* @param grpid
* @return
* @throws Exception
*/
public static BbsThread[] loadSimpleTopicList(Connection con, long grpid)
throws Exception ...{
String sql = "select * from (select thread_id ,forum_id ,row_number() over"+
"(order by ltouch_time desc) as r from tab_bbs_thread where forum_id"+
"in (select forum_id from tab_bbs_forum where group_id=?)) where"+
"r<=8";
PreparedStatement pstm = null;
ResultSet rs = null;
ArrayList<BbsThread> list = new ArrayList<BbsThread>();
try ...{
pstm = con.prepareStatement(sql);
pstm.setLong(1, grpid);
rs = pstm.executeQuery();
while (rs.next()) ...{
BbsThread thr = new BbsThread();
thr.setThreadId(rs.getLong(1));
thr.setForumId(rs.getLong(2));
thr.setForum(BbsForumTable.loadForum(con, thr.getForumId()));
thr.setTopic(BbsMessageTable.loadSimpleTopic(con, thr
.getThreadId()));
list.add(thr);
}
} catch (SQLException e) ...{
throw new Exception(
"BbsThreadTable===============getSimpleTopicList has error!");
} finally ...{
if (rs != null) ...{
rs.close();
rs = null;
}
if (pstm != null) ...{
pstm.close();
pstm = null;
}
}
return (BbsThread[]) list.toArray(new BbsThread[list.size()]);
}
/** *//**
* 查询:返回int
*
* @param con
* @param frmid
* @return
* @throws Exception
*/
public static int loadThreadCount(Connection con, long frmid)
throws Exception ...{
String sql = "";
PreparedStatement pstm = null;
ResultSet rs = null;
int count = -1;
try ...{
pstm = con.prepareStatement(sql);
pstm.setLong(1, frmid);
rs = pstm.executeQuery();
if (rs.next()) ...{
count = rs.getInt(1);
}
} catch (SQLException e) ...{
throw new Exception(
"BbsThreadTable===============getThreadCount has error!");
} finally ...{
if (rs != null) ...{
rs.close();
rs = null;
}
if (pstm != null) ...{
pstm.close();
pstm = null;
}
}
return count;
}
/** *//**
* 查询:返回对象
*
* @param con
* @param thrid
* @return
* @throws Exception
*/
public static BbsThread loadThread(Connection con, long thrid)
throws Exception ...{
String sql = "";
PreparedStatement pstm = null;
ResultSet rs = null;
BbsThread thr = new BbsThread();
try ...{
pstm = con.prepareStatement(sql);
pstm.setLong(1, thrid);
rs = pstm.executeQuery();
if (rs.next()) ...{
thr.setThreadId(rs.getLong(1));
thr.setForumId(rs.getLong(2));
thr.setTattribute(rs.getInt(3));
thr.setLattribute(rs.getInt(4));
thr.setForum(BbsForumTable.loadForum(con, thr.getForumId()));
thr.setTopic(BbsMessageTable.loadSimpleTopic(con, thr
.getThreadId()));
}
} catch (SQLException e) ...{
throw new Exception(
"BbsThreadTable===============getThreadAttr has error!");
} finally ...{
if (rs != null) ...{
rs.close();
rs = null;
}
if (pstm != null) ...{
pstm.close();
pstm = null;
}
}
return thr;
}
/** *//**
* 插入:并返回long
*
* @param con
* @param amdto
* @return
* @throws Exception
*/
// 加入新帖,并返回线索ID
public static long insertThread(Connection con, BbsAddMessgaDTO amdto)
throws Exception ...{
String sql = "insert into "
+ TABLENAME
+ " values(?,?,"
+ "(select sysdate from dual),(select sysdate from dual),?,0,0,0,0,?)";
PreparedStatement pstm = null;
long thrid = DBTable
.loadSqencesValue(con, BbsThreadTable.SQUENCES_NAME);
try ...{
pstm = con.prepareStatement(sql);
pstm.setLong(1, thrid);
pstm.setLong(2, amdto.getForumid());
pstm.setInt(3, amdto.getAttribute());
pstm.setInt(4, amdto.getIsinPro());
pstm.executeUpdate();
} catch (SQLException e) ...{
throw new Exception(
"BbsThreadTable ===============insertThread has error!");
} finally ...{
if (pstm != null) ...{
pstm.close();
pstm = null;
}
}
return thrid;
}
/** *//**
* 查询:返回boolean
*
* @param con
* @param thrid
* @return
* @throws Exception
*/
public static boolean addThreadClick(Connection con, long thrid)
throws Exception ...{
String sql1 = "select " + COL_CLICK + " from " + TABLENAME + " where "
+ COL_THREAD_ID + "=?";
String sql2 = "update " + TABLENAME + " set " + COL_CLICK + "=? where "
+ COL_THREAD_ID + "=?";
PreparedStatement pstm = null;
ResultSet rs = null;
long click = 0;
try ...{
pstm = con.prepareStatement(sql1);
pstm.setLong(1, thrid);
rs = pstm.executeQuery();
if (rs.next()) ...{
click = rs.getLong(1);
}
pstm = con.prepareStatement(sql2);
pstm.setLong(1, click + 1);
pstm.setLong(2, thrid);
pstm.executeUpdate();
} catch (SQLException e) ...{
throw new Exception(
"BbsThreadTable ===============addThreadClick has error!");
} finally ...{
if (rs != null) ...{
rs.close();
rs = null;
}
if (pstm != null) ...{
pstm.close();
pstm = null;
}
}
return true;
}
/** *//**
* 更新:update
*
* @param con
* @param thrid
* @return
* @throws Exception
*/
// 更新回复时间
public static boolean updateTouchTime(Connection con, long thrid)
throws Exception ...{
String sql = "update " + TABLENAME + " set " + COL_LTOUCH_TIME
+ "=(select sysdate from dual) where " + COL_THREAD_ID + "=?";
PreparedStatement pstm = null;
try ...{
pstm = con.prepareStatement(sql);
pstm.setLong(1, thrid);
pstm.executeUpdate();
} catch (SQLException e) ...{
throw new Exception(
"BbsThreadTable ===============updateTouchTime has error!");
} finally ...{
if (pstm != null) ...{
pstm.close();
pstm = null;
}
}
return true;
}
rs.close();
rs = null;
/** *//**
* 更新:返回true
*
* @param con
* @param thrid
* @param isLock
* @return
* @throws Exception
*/
public static boolean updateLock(Connection con, long thrid, int isLock)
throws Exception ...{
String sql = "update " + TABLENAME + " set " + COL_LOCKED + "=? "
+ " where " + COL_THREAD_ID + "=?";
PreparedStatement pstm = null;
try ...{
pstm = con.prepareStatement(sql);
pstm.setInt(1, isLock);
pstm.setLong(2, thrid);
pstm.executeUpdate();
} catch (SQLException e) ...{
throw new Exception(
"BbsThreadTable ===============updateLock has error!");
} finally ...{
if (pstm != null) ...{
pstm.close();
pstm = null;
}
}
return true;
}
/** *//**
* 删除:返回boolean
*
* @param con
* @param thrid
* @return
* @throws Exception
*/
public static boolean deleteThread(Connection con, long thrid)
throws Exception ...{
String sql = "delete from tab_bbs_thread where thread_id=?";
PreparedStatement pstm = null;
try ...{
pstm = con.prepareStatement(sql);
pstm.setLong(1, thrid);
pstm.executeUpdate();
} catch (SQLException e) ...{
throw new Exception(
"BbsThreadTable ===============deleteThread has error!");
} finally ...{
if (pstm != null) ...{
pstm.close();
pstm = null;
}
}
return true;
}
/** *//**
* 迈兴志 2007-3-22 根据用户ID查询好友信息 调用过程包pkgFriends
*/
public static void loadFriends(Connection con, RecordsData records,
long userId, int pageId, int pageSet, int type, long tagid)
throws Exception ...{
CallableStatement proc = null;
ResultSet rs = null;
try ...{
proc = con.prepareCall("begin :1 := pkgFriends.getFriends("
+ userId + "," + pageId + "," + pageSet + "," + type + ","
+ tagid + "); end;");
proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
proc.execute();
rs = (ResultSet) proc.getObject(1);
while (rs.next()) ...{
CtrlFriends fr = new CtrlFriends();
fr.setMyUserId(rs.getLong(1));
fr.setFriendId(rs.getLong(2));
fr.setIsPublic(rs.getInt(3));
// 好友的信息
UserInfo ui = new UserInfo();
ui.setUserID(fr.getFriendId());
ui.setUserName(rs.getString(4));
ui.setAlias(rs.getString(5));
ui.setHeadPic(rs.getString(6));
ui.setFamilyArea(rs.getString(7));
Code[] code = OtherTable.loadCityArea(con, 10, ui
.getFamilyArea());
for (int i = 0; code != null && code.length > 0
&& i < code.length; i++) ...{
ui.setFamilyArea(code[i].getDesc());
}
// 标签处理 在JSP页面作拆分处理
fr.setTagId(rs.getString(8));
fr.setUser(ui);
fr.setRelationId(rs.getLong(9));
fr.setRelationName(rs.getString(10));
fr.setRelationType(rs.getInt(11));
records.getRecords().add(fr);
}
records.setBlockSize(pageSet);
records.setCurrBlock((pageId - 1) * pageSet + 1);
// 记录总数处理
proc = con.prepareCall("begin :1 := pkgFriends.getFriendsCntByTag("
+ userId + "," + type + ","+tagid+"); end;");
proc.registerOutParameter(1, oracle.jdbc.OracleTypes.NUMBER);
proc.execute();
// int i = (Integer) proc.getObject(1);
int i = proc.getInt(1);
records.setRecordsCount(i);
} catch (SQLException e) ...{
e.printStackTrace();
throw new Exception(
"CtrlFriendsTable查询好友信息===============loadFriends has error!");
} finally ...{
if (rs != null) ...{
rs.close();
rs = null;
}
if (proc != null) ...{
proc.close();
proc = null;
}
}
}
/** *//**
* 查询:带分页
*
* @param con
* @param records
* @param userid
* @param type
* @param name
* @param alias
* @param pageId
* @param pageSet
* @throws Exception
*/
public static void loadMyFriends(Connection con, RecordsData records,
long userid, int type, String name, String alias, int pageId,
int pageSet) throws Exception ...{
String contion1 = "";
String contion2 = "";
String contion3 = "";
if (name != null && !name.equals("")) ...{
contion1 = " where name like '%" + name + "%' ";
}
if (alias != null && !alias.equals("")) ...{
contion2 = " and alias like '%" + name + "%' ";
}
if (type != -1) ...{
contion3 = " where relation_type=" + type + " ";
}
String sql2 = "select count(*) from"
+ "(select id,name,alias, f_area,head_pic from tab_user_info "
+ contion1
+ ") i,"
+ "(select relation_id,relation_type,relation_name from tab_cir_relation"
+ contion3
+ ") cr,"
+ "(select my_user_id,friend_id,relation_id,tag_id,is_public from tab_ctrl_friends where my_user_id="
+ userid + ") f," + "(select tag_id,tag_name from tab_tag) t"
+ " where cr.relation_id=f.relation_id"
+ " and t.tag_id(+)=f.tag_id" + " and i.id=f.friend_id"
+ contion2;
String sql = "select * from (select f.my_user_id,friend_id,name fname,alias falias,i.head_pic fheadpic,f_area,f.relation_id,"
+ "cr.relation_name,f.tag_id,t.tag_name,cr.relation_type,is_public,row_number() over(order by i.id) r from"
+ "(select id,name,alias, f_area,head_pic from tab_user_info "
+ contion1
+ ") i,"
+ "(select relation_id,relation_type,relation_name from tab_cir_relation"
+ contion3
+ ") cr,"
+ "(select my_user_id,friend_id,relation_id,tag_id,is_public from tab_ctrl_friends where my_user_id="
+ userid
+ ") f,"
+ "(select tag_id,tag_name from tab_tag) t"
+ " where cr.relation_id=f.relation_id"
+ " and t.tag_id(+)=f.tag_id"
+ " and i.id=f.friend_id"
+ contion2 + ") where r between ? and ?";
PreparedStatement pstm = null;
ResultSet rs = null;
try ...{
pstm = con.prepareStatement(sql);
pstm.setInt(1, (pageId - 1) * pageSet + 1);
pstm.setInt(2, pageId * pageSet);
rs = pstm.executeQuery();
while (rs.next()) ...{
CtrlFriends fr = new CtrlFriends();
fr.setMyUserId(rs.getLong(1));
fr.setFriendId(rs.getLong(2));
UserInfo ui = new UserInfo();
ui.setUserID(fr.getFriendId());
ui.setUserName(rs.getString(3));
ui.setAlias(rs.getString(4));
ui.setHeadPic(rs.getString(5));
ui.setFamilyArea(rs.getString(6));
Code[] code = OtherTable.loadCityArea(con, 10, ui
.getFamilyArea());
for (int i = 0; code != null && code.length > 0
&& i < code.length; i++) ...{
ui.setFamilyArea(code[i].getDesc());
}
fr.setUser(ui);
fr.setRelationId(rs.getLong(7));
fr.setRelationName(rs.getString(8));
fr.setTagId(rs.getString(9));
fr.setTagName(rs.getString(10));
fr.setRelationType(type);
fr.setIsPublic(rs.getInt(12));
records.getRecords().add(fr);
}
records.setBlockSize(pageSet);
records.setCurrBlock((pageId - 1) * pageSet + 1);
records.setRecordsCount(getRecordCount(con, sql2));
} catch (SQLException e) ...{
e.printStackTrace();
throw new Exception(
"CtrlFriendsTable===============loadMyFriends has error!");
} finally ...{
if (rs != null) ...{
rs.close();
rs = null;
}
if (pstm != null) ...{
pstm.close();
pstm = null;
}
}
}
}