jdbc通用sql分页封装

代码



Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->    

    

    /**

     * 查询结果的总数，常用的地方就是查询分页时的总记录数

     * @param sql：原始sql语句！

     * @return 查询结果的总数

     * @throws SQLException

     */

    

    public int getsqlcount(String sql) throws SQLException{

        DataBase db = new DataBase();

        this.con = db.getCon();

        String nsql = "select count(*) from (" + sql + ")";

        System.out.println("查询总数的SQL：--->"+nsql);

        try {

            st = con.createStatement();

            rs = st.executeQuery(nsql);

            if (rs.next()) {

                return rs.getInt(1);

            } else {

                return 0;

            }

        } catch (SQLException e) {

            System.out.println("执行查询总数sql异常，不能执行！");

            throw new SQLException(e.toString());

        } finally {

            try {

                if(con!=null)

                    con.close();

                if(st!=null)

                    st.close();

                if(rs!=null)

                    rs.close();

                System.out.println("siyu--getsqlcount-->关闭连接成功！");

            } catch (Exception e) {

                e.printStackTrace();

                System.out.println("关闭数据连接有误!!");

            }

        }

    }



    /**

     * 查找分页的sql语句的数据

     * @param sql 原始sql语句！

     * @param currentpage 当前页

     * @param pagesize 每页大小

     * @return 返回满足该条件的结果集，以List<String[]> 形式返回

     */

    public List<String[]> getsqllistset(String sql, int currentpage, int pagesize) throws SQLException {

        if (currentpage <= 0) {

            currentpage = 1;

        }

        if (pagesize < 1 || pagesize > 100) {

            pagesize = 10;

        }

        int startrow = (currentpage - 1) * pagesize;

        String nsql = "select * from (select a.*, rownum r from (" + sql + ") a where rownum <= " + (startrow + pagesize) + ") where r > " + startrow;

        System.out.println("分页sql语句：" + nsql);

        try {

            DataBase db = new DataBase();

            this.con = db.getCon();

            st = con.createStatement();

            rs = st.executeQuery(nsql);

            ResultSetMetaData rsmd = rs.getMetaData();

            int numberOfColumns = rsmd.getColumnCount();

            System.out.println(numberOfColumns);

            List<String[]> list = new ArrayList<String[]>();

            while (rs.next()) {

                String[] str = new String[numberOfColumns - 1];

                for (int r = 1; r < numberOfColumns; r++) {

                    str[r - 1] = rs.getString(r);

                }

                list.add(str);

            }

            return list;

        } catch (SQLException e) {

            e.printStackTrace();

            System.out.println("执行查询总数sql异常，不能执行！");

            throw new SQLException(e);

        } catch (Exception e) {

            e.printStackTrace();

            System.out.println("查询数据结果异常，不能执行！");

            return null;

        }

        finally {

            try {

                if(con!=null)

                    con.close();

                if(st!=null)

                    st.close();

                if(rs!=null)

                    rs.close();

                System.out.println("siyu--getsqllistset-->关闭连接成功！");

            } catch (Exception e) {

                System.out.println("关闭数据连接有误!!");

            }

        }

    }



    

    /**

     * 查找分页的sql语句的数据

     * @param sql 原始sql语句！

     * @param currentpage 当前页

     * @param pagesize 每页大小

     * @return 返回满足该条件的结果集，以List<<Map<String,Object>> 形式返回

     */

    public List<Map<String,Object>> getsqlmapset(String sql, int currentpage,int pagesize) throws SQLException {

        if (currentpage <= 0) {

            currentpage = 1;

        }

        if (pagesize < 1 || pagesize > 100) {

            pagesize = 10;

        }

        int startrow = (currentpage - 1) * pagesize;

        String nsql = "select * from (select a.*, rownum r from (" + sql + ") a where rownum <= " + (startrow + pagesize) + ") where r > " + startrow;

        System.out.println("分页sql语句：" + nsql);

        try {

            DataBase db = new DataBase();

            this.con = db.getCon();

            st = con.createStatement();

            rs = st.executeQuery(nsql);

            List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();

            ResultSetMetaData rsmd = rs.getMetaData();

            int numberOfColumns = rsmd.getColumnCount();

            while (rs.next()) {

                Map<String,Object> rsTree = new HashMap<String, Object>(numberOfColumns);

                for (int r = 1; r < numberOfColumns; r++) {

                    rsTree.put(rsmd.getColumnName(r), rs.getObject(r));

                }

                list.add(rsTree);

            }

            return list;

        } catch (SQLException e) {

            e.printStackTrace();

            System.out.println("执行查询总数sql异常，不能执行！");

            throw new SQLException(e);

        } catch (Exception e) {

            e.printStackTrace();

            System.out.println("查询数据结果异常，不能执行！");

            return null;

        }

        finally {

            try {

                if(con!=null)

                    con.close();

                if(st!=null)

                    st.close();

                if(rs!=null)

                    rs.close();

                System.out.println("siyu--getsqlmapset-->关闭连接成功！");

            } catch (Exception e) {

                System.out.println("关闭数据连接有误!!");

            }

        }

    }