companydaoimpljdbc.java

解觖java技术中后台无法上传数给的情况

    private Collection getBeans_withSortSupport_limit_noscroll(int offset, int rowsToReturn, String sort, String order)
        throws IllegalArgumentException, DatabaseException {
        if (offset < 0) throw new IllegalArgumentException("The offset < 0 is not allowed.");
        if (rowsToReturn <= 0) throw new IllegalArgumentException("The rowsToReturn <= 0 is not allowed.");
        /*
         * Sort by
         * CompanyID or CompanyName or CompanyCreationDate
         */
        if ((!sort.equals("CompanyID")) &&
           (!sort.equals("CompanyName")) &&
           (!sort.equals("CompanyAddress")) &&
           (!sort.equals("CompanyCreationDate"))) {
           throw new IllegalArgumentException("Cannot sort, reason: don't understand the criteria '" + sort + "'.");
        }

        if ((!order.equals("ASC")) &&
           (!order.equals("DESC")) ) {
            throw new IllegalArgumentException("Cannot sort, reason: don't understand the order '" + order + "'.");
        }

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT *");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC

        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            if (rowsToReturn != 1) {
                statement.setMaxRows(offset + rowsToReturn);
            }
            resultSet = statement.executeQuery();
            int rowIndex = -1;
            while (resultSet.next()) {
                rowIndex++;
                if(rowsToReturn !=1) {
                    if (rowIndex < offset) continue;
                }
                CompanyBean bean = new CompanyBean();
                bean.setCompanyID(resultSet.getInt("CompanyID"));
                bean.setGroupID(resultSet.getInt("GroupID"));
                bean.setCompanyName(resultSet.getString("CompanyName"));
                bean.setCompanyAddress(resultSet.getString("CompanyAddress"));
                bean.setCompanyCity(resultSet.getString("CompanyCity"));
                bean.setCompanyCAP(resultSet.getString("CompanyCAP"));
                bean.setCompanyProvince(resultSet.getString("CompanyProvince"));
                bean.setCompanyRegion(resultSet.getString("CompanyRegion"));
                bean.setCompanyPhone(resultSet.getString("CompanyPhone"));
                bean.setCompanyFax(resultSet.getString("CompanyFax"));
                bean.setCompanyWebsite(resultSet.getString("CompanyWebsite"));
                bean.setCompanyEmail(resultSet.getString("CompanyEmail"));
                bean.setCompanySpaceName(resultSet.getString("CompanySpaceName"));
                bean.setCompanySpaceHeader(resultSet.getString("CompanySpaceHeader"));
                bean.setCompanySpaceFooter(resultSet.getString("CompanySpaceFooter"));
                bean.setCompanyVATNumber(resultSet.getString("CompanyVATNumber"));
                bean.setCompanyLogo(resultSet.getString("CompanyLogo"));
                bean.setCompanyCss(resultSet.getString("CompanyCss"));
                bean.setCompanyCreationDate(resultSet.getTimestamp("CompanyCreationDate"));
                bean.setCompanyModifiedDate(resultSet.getTimestamp("CompanyModifiedDate"));
                retValue.add(bean);
                if (retValue.size() == rowsToReturn) break;// Fix the Sybase bug
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getBeans_withSortSupport_limit_noscroll.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.resetStatement(statement);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

   /*
    * Included columns: CompanyID, CompanyName, CompanyAddress, CompanyCity, CompanyCAP,
    *                   CompanyProvince, CompanyRegion, CompanyPhone, CompanyFax, CompanyWebsite,
    *                   CompanyEmail, CompanySpaceName, CompanySpaceHeader, CompanySpaceFooter, CompanyVATNumber, CompanyLogo, CompanyCss, CompanyCreationDate, CompanyModifiedDate
    * Excluded columns:
    */
   /**
    * This method support sorting and for PUBLIC view
    */
   /* @todo fix bug that cannot prepare sort and order */

    private Collection getBeans_withSortSupport_limit_general(int offset, int rowsToReturn, String sort, String order)
        throws IllegalArgumentException, DatabaseException {
        if (offset < 0) throw new IllegalArgumentException("The offset < 0 is not allowed.");
        if (rowsToReturn <= 0) throw new IllegalArgumentException("The rowsToReturn <= 0 is not allowed.");
        /*
         * Sort by
         * CompanyID or CompanyName or CompanyCreationDate
         */
        if ((!sort.equals("CompanyID")) &&
            (!sort.equals("CompanyName")) &&
            (!sort.equals("CompanyAddress")) &&
            (!sort.equals("CompanyCreationDate"))) {
            throw new IllegalArgumentException("Cannot sort, reason: don't understand the criteria '" + sort + "'.");
        }

        if ((!order.equals("ASC")) &&
            (!order.equals("DESC")) ) {
            throw new IllegalArgumentException("Cannot sort, reason: don't understand the order '" + order + "'.");
        }

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT *");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" ORDER BY " + sort + " " + order);// ColumnName, ASC|DESC

        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString(),ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            if (rowsToReturn != 1) {
                statement.setMaxRows(offset + rowsToReturn);
            }
            try {
                statement.setFetchSize(rowsToReturn);
            } catch (SQLException sqle) {
                //do nothing, postgreSQL doesnt support this method
            }
            resultSet = statement.executeQuery();
            boolean loop = resultSet.absolute(offset + 1);// the absolute method begin with 1 instead of 0 as in the LIMIT clause
            while (loop) {
                CompanyBean bean = new CompanyBean();
                bean.setCompanyID(resultSet.getInt("CompanyID"));
                bean.setGroupID(resultSet.getInt("GroupID"));
                bean.setCompanyName(resultSet.getString("CompanyName"));
                bean.setCompanyAddress(resultSet.getString("CompanyAddress"));
                bean.setCompanyCity(resultSet.getString("CompanyCity"));
                bean.setCompanyCAP(resultSet.getString("CompanyCAP"));
                bean.setCompanyProvince(resultSet.getString("CompanyProvince"));
                bean.setCompanyRegion(resultSet.getString("CompanyRegion"));
                bean.setCompanyPhone(resultSet.getString("CompanyPhone"));
                bean.setCompanyFax(resultSet.getString("CompanyFax"));
                bean.setCompanyWebsite(resultSet.getString("CompanyWebsite"));
                bean.setCompanyEmail(resultSet.getString("CompanyEmail"));
                bean.setCompanySpaceName(resultSet.getString("CompanySpaceName"));
                bean.setCompanySpaceHeader(resultSet.getString("CompanySpaceHeader"));
                bean.setCompanySpaceFooter(resultSet.getString("CompanySpaceFooter"));
                bean.setCompanyVATNumber(resultSet.getString("CompanyVATNumber"));
                bean.setCompanyLogo(resultSet.getString("CompanyLogo"));
                bean.setCompanyCss(resultSet.getString("CompanyCss"));
                bean.setCompanyCreationDate(resultSet.getTimestamp("CompanyCreationDate"));
                bean.setCompanyModifiedDate(resultSet.getTimestamp("CompanyModifiedDate"));
                retValue.add(bean);
                if(rowsToReturn !=1) {
                    if (retValue.size() == rowsToReturn) break; // Fix the Sybase bug
                }
                loop = resultSet.next();
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getBeans_withSortSupport_limit_general.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.resetStatement(statement);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public int getNumberOfCompanies()
        throws AssertionException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT Count(*)");
        sql.append(" FROM " + TABLE_NAME);
        //sql.append(" WHERE "); // @todo: uncomment as needed
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            resultSet = statement.executeQuery();
            if (!resultSet.next()) {
                throw new AssertionException("Assertion in CompanyDAOImplJDBC.getNumberOfCompanies.");
            }
            return resultSet.getInt(1);
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getNumberOfCompanies.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    /*
     * Included columns: CompanyID, GroupID, CompanyName, CompanyAddress, CompanyCity,
     *                   CompanyCAP, CompanyProvince, CompanyRegion, CompanyPhone, CompanyFax,
     *                   CompanyWebsite, CompanyEmail, CompanySpaceName, CompanySpaceHeader, CompanySpaceFooter,
     *                   CompanyVATNumber, CompanyLogo, CompanyCss, CompanyCreationDate, CompanyModifiedDate
     * Excluded columns:
     */
    public Collection getCompanies()
        throws DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Collection retValue = new ArrayList();
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT CompanyID, GroupID, CompanyName, CompanyAddress, CompanyCity, CompanyCAP, CompanyProvince, CompanyRegion, CompanyPhone, CompanyFax, CompanyWebsite, CompanyEmail, CompanySpaceName, CompanySpaceHeader, CompanySpaceFooter, CompanyVATNumber, CompanyLogo, CompanyCss, CompanyCreationDate, CompanyModifiedDate");
        sql.append(" FROM " + TABLE_NAME);
        //sql.append(" WHERE "); // @todo: uncomment as needed
        //sql.append(" ORDER BY ColumnName ASC|DESC "); // @todo: uncomment as needed
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                CompanyBean bean = new CompanyBean();
                bean.setCompanyID(resultSet.getInt("CompanyID"));
                bean.setGroupID(resultSet.getInt("GroupID"));
                bean.setCompanyName(resultSet.getString("CompanyName"));
                bean.setCompanyAddress(resultSet.getString("CompanyAddress"));
                bean.setCompanyCity(resultSet.getString("CompanyCity"));
                bean.setCompanyCAP(resultSet.getString("CompanyCAP"));
                bean.setCompanyProvince(resultSet.getString("CompanyProvince"));
                bean.setCompanyRegion(resultSet.getString("CompanyRegion"));
                bean.setCompanyPhone(resultSet.getString("CompanyPhone"));
                bean.setCompanyFax(resultSet.getString("CompanyFax"));
                bean.setCompanyWebsite(resultSet.getString("CompanyWebsite"));
                bean.setCompanyEmail(resultSet.getString("CompanyEmail"));
                bean.setCompanySpaceName(resultSet.getString("CompanySpaceName"));
                bean.setCompanySpaceHeader(resultSet.getString("CompanySpaceHeader"));
                bean.setCompanySpaceFooter(resultSet.getString("CompanySpaceFooter"));
                bean.setCompanyVATNumber(resultSet.getString("CompanyVATNumber"));
                bean.setCompanyLogo(resultSet.getString("CompanyLogo"));
                bean.setCompanyCss(resultSet.getString("CompanyCss"));
                bean.setCompanyCreationDate(resultSet.getTimestamp("CompanyCreationDate"));
                bean.setCompanyModifiedDate(resultSet.getTimestamp("CompanyModifiedDate"));
                retValue.add(bean);
            }
            return retValue;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getCompanies.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

} // end of class CompanyDAOImplJDBC