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; } } }}