companydaoimpljdbc.java

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

        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public int getCompanyIDFromCompanyEmail(String companyEmail)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT CompanyID");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE CompanyEmail = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, companyEmail);
            resultSet = statement.executeQuery();
            if(!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the row in table Company where alternate key [CompanyEmail] = (" + companyEmail + ").");
            }

            return resultSet.getInt("CompanyID");
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getCompanyIDFromCompanyEmail(ak).");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public int getCompanyIDFromCompanySpaceName(String companySpaceName)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        StringBuffer sql = new StringBuffer(512);
        sql.append("SELECT CompanyID");
        sql.append(" FROM " + TABLE_NAME);
        sql.append(" WHERE CompanySpaceName = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, companySpaceName);
            resultSet = statement.executeQuery();
            if(!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the row in table Company where alternate key [CompanySpaceName] = (" + companySpaceName + ").");
            }

            return resultSet.getInt("CompanyID");
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getCompanyIDFromCompanySpaceName(ak).");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public int getCompanyIDFromCompanyCreationDate(java.util.Date companyCreationDate)
        throws ObjectNotFoundException, DatabaseException {

        int totalCompanies = 0;
        int ret = 0;

        try {
            totalCompanies = getNumberOfCompanies();
        } catch (Exception err) {}

        Collection collectionCompany = getCompanies_withSortSupport_limit(0, totalCompanies, "CompanyCreationDate", "ASC");
        Iterator iteratorCompany = collectionCompany.iterator();
        while (iteratorCompany.hasNext()) {
            CompanyBean companyBean = (CompanyBean) iteratorCompany.next();
            java.util.Date companyCreationDateDB = new java.util.Date(companyBean.getCompanyCreationDate().getTime());
            String dateFormat = DateUtil.getDateDDMMYYYY(companyCreationDate);
            String dateFormatDB = DateUtil.getDateDDMMYYYY(companyCreationDateDB);
            if (dateFormat.equals(dateFormatDB)) {
                ret = companyBean.getCompanyID();
                break;
            }
        }
        if (ret == 0) {
            throw new ObjectNotFoundException("Cannot get company ID from company date.");
        }
        return ret;
    }

    /*
     * 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 CompanyBean getCompany(int companyID)
        throws ObjectNotFoundException, DatabaseException {

        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        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 CompanyID = ?");
        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            statement.setInt(1, companyID);
            resultSet = statement.executeQuery();
            if(!resultSet.next()) {
                throw new ObjectNotFoundException("Cannot find the row in table Company where primary key = (" + companyID + ").");
            }

            CompanyBean bean = new CompanyBean();
            // @todo: uncomment the following line(s) as needed
            //bean.setCompanyID(companyID);
            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"));
            return bean;
        } catch(SQLException sqle) {
            log.error("Sql Execution Error!", sqle);
            throw new DatabaseException("Error executing SQL in CompanyDAOImplJDBC.getCompany(pk).");
        } finally {
            DBUtils.closeResultSet(resultSet);
            DBUtils.closeStatement(statement);
            DBUtils.closeConnection(connection);
        }
    }

    public Collection getCompanies_withSortSupport_limit(int offset, int rowsToReturn, String sort, String order)
        throws IllegalArgumentException, DatabaseException {
        if (DBUtils.getDatabaseType() == DBUtils.DATABASE_MYSQL) {
            return getBeans_withSortSupport_limit_mysql(offset, rowsToReturn, sort, order);
        } else if (DBUtils.getDatabaseType() == DBUtils.DATABASE_NOSCROLL) {
            return getBeans_withSortSupport_limit_noscroll(offset, rowsToReturn, sort, order);
        }
        return getBeans_withSortSupport_limit_general(offset, rowsToReturn, sort, order);
    }

    /*
     * 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_mysql(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
        if (rowsToReturn != 1) {
            sql.append(" LIMIT ?, ?");
        }

        try {
            connection = DBUtils.getConnection();
            statement = connection.prepareStatement(sql.toString());
            if (rowsToReturn != 1) {
                statement.setInt(1, offset);
                statement.setInt(2, rowsToReturn);
            }
            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.getBeans_withSortSupport_limit_mysql.");
        } finally {
            DBUtils.closeResultSet(resultSet);
            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 */