java程序标准增删改查示例

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

}