viewdao.java

eclipse java/jsp 航空管理系统

    /** 该方法是删除记录 */
    public void remove(String sql) {

        AppMode.registerUser();

        PreparedStatement ps = null;
        try {

            /** 判断数据库连接是不是正常，不正常的话抛出异常 */
            if (con.isClosed()) {
                throw new IllegalStateException("没有连接到数据库");
            }
            ps = con.prepareStatement(sql);

            if (ps.executeUpdate() != 1) {
                throw new SQLException("error.removed.View");
            }

        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("error.unexpected");
        } finally {

            AppMode.loginoutUser();

            try {
                /** 关闭数据库连接 */
                if (ps != null)
                    ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException("error.unexpected");
            }
        }
    }

    /** 处理指定关键字的flight表删除操作 */
    public void removeID(String keyID) {

        AppMode.registerUser();

        /** 要操作的sql语句 */
        String sql = "DELETE FROM flight WHERE ";
        sql += "fno";
        sql += "=?";
        PreparedStatement ps = null;
        System.out.println(sql);
        try {

            /** 判断数据库连接是不是正常，不正常的话抛出异常 */
            if (con.isClosed()) {
                throw new IllegalStateException("没有连接到数据库");
            }
            ps = con.prepareStatement(sql);
            /** 按照关键字删除数据 */
            ps.setString(1, keyID.trim());
            System.out.println(sql);
            if (ps.executeUpdate() != 1) {
                throw new SQLException("error.removed.View");
            }

        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("error.unexpected");
        } finally {

            AppMode.loginoutUser();

            try {
                /** 关闭数据库连接 */
                if (ps != null)
                    ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException("error.unexpected");
            }
        }
    }

    /** 处理指定关键字的city表删除操作 */
    public void removeID2(String keyID) {

        AppMode.registerUser();

        /** 建立Statement对象，以执行SQL命令 */
        Statement stmt = null;
        try {

            /** 判断数据库连接是不是正常，不正常的话抛出异常 */
            if (con.isClosed()) {
                throw new IllegalStateException("没有连接到数据库");
            }
            stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            /** 执行SQL命令，删除记录 */
            stmt.executeUpdate("DELETE FROM city WHERE fno='" + keyID + "'");
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("error unexpected");
        } finally {

            AppMode.loginoutUser();

            try {
                /** 关闭数据库连接 */
                if (stmt != null)
                    stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException("error unexpected");
            }
        }
    }

    /** 查询指定关键字的记录 */
    public View findByPrimaryKey(String keyID) throws SQLException {

        AppMode.registerUser();

        /** 建立PreparedStatement对象，以执行SQL命令 */
        PreparedStatement ps = null;
        ResultSet rs = null;
        View m_View = null;
        /** 要操作的sql语句 */
        String sql = "SELECT fno,fcountry,fcom,iout from flght where fno=?";
        try {
            /** 判断数据库连接是不是正常，不正常的话抛出异常 */
            if (con.isClosed()) {
                throw new IllegalStateException("没有连接到数据库");
            }
            ps = con.prepareStatement(sql);
            ps.setString(1, keyID.trim());
            rs = ps.executeQuery();
            if (rs.next()) {
                m_View = new View();

                /** 将关键字查询到的记录赋给各个字段 */
                m_View.setFno(rs.getString(1));
                m_View.setFcountry(rs.getString(2));
                m_View.setFcom(rs.getString(3));
                m_View.setIout(rs.getString(4)); //</findByPrimaryKey>
                return m_View;
            } else {
                throw new SQLException("error.removed.View");
            }

        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("error.unexpected");
        } finally {

            AppMode.loginoutUser();

            try {
                /** 关闭数据库连接 */
                if (ps != null)
                    ps.close();
                if (rs != null)
                    rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException("error.unexpected");
            }
        }
    }

    /** 处理不是一个结果集的查询，返回多条记录 */
    public Collection findSQL(String sql, int ipage) {

        AppMode.registerUser();

        /** 建立PreparedStatement对象，以执行SQL命令 */
        PreparedStatement ps = null;
        ResultSet rs = null;
        ArrayList list = new ArrayList();

        try {
            /** 判断数据库连接是不是正常，不正常的话抛出异常 */
            if (con.isClosed()) {
                throw new IllegalStateException("没有连接到数据库");
            }
            ps = con.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE,
                    ResultSet.CONCUR_UPDATABLE);
            rs = ps.executeQuery();
            rs.absolute(-1);
            /** 得到查询后的行数 */
            rowCount = rs.getRow();

            int offset = 1;
            int pagesize = getLength();
            if (getLength() < 1) {
                pagesize = rowCount;
                pageCount = 1;
            } else {
                pageCount = rowCount / getLength()
                        + ((rowCount % getLength()) > 0 ? 1 : 0);
                offset = (ipage - 1) * getLength() + 1;
                if (offset < 1)
                    offset = 1;
                if (offset > rowCount)
                    offset = rowCount;

            }
            rs.absolute(offset);
            for (int i = 0; i < pagesize && offset < rowCount + 1; i++, 
            offset++) {
                /** 将关键字查询到的记录赋给各个字段 */
                View m_View = new View();
                m_View.setFno(rs.getString(1));
                m_View.setFcountry(rs.getString(2));
                m_View.setFcom(rs.getString(3));
                m_View.setIout(rs.getString(4));
                rs.next();
                list.add(m_View);
            }
            return list;
        } catch (SQLException e) {
            return list;
        } finally {

            AppMode.loginoutUser();

            try {
                /** 关闭数据库连接 */
                if (ps != null)
                    ps.close();
                if (rs != null)
                    rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
                throw new RuntimeException("error.unexpected");
            }
        }
    }

}