accountdaoimpl.java~

J2EE & Tomcat books published by hope

        try {
            getDBConnection();
            //stmt = dbConnection.createStatement();
            //result = stmt.executeQuery(queryStr);
            stmt = createPreparedStatement(dbConnection, queryStr);
            result = stmt.executeQuery();

            if ( !result.next() )
                throw new AccountDAOFinderException(
                                  "No record for primary key " + userId);

            int i = 1;
            userId = result.getString(i++);
            String status = result.getString(i++);
            String email = result.getString(i++);
            String firstName = result.getString(i++);
            String lastName = result.getString(i++);
            String street1 = result.getString(i++);
            String street2 = result.getString(i++);
            String city = result.getString(i++);
            String state = result.getString(i++);
            String zip = result.getString(i++);
            String country = result.getString(i++);
            String phone = result.getString(i++);

            Address addr = new Address(street1, street2, city, state, zip,
                               country);
            ContactInformation info =
                new ContactInformation(lastName, firstName, phone,
                                          email, addr);
            return(new AccountModel(userId, status, info));
        } catch(SQLException ae) {
            throw new AccountDAOSysException("SQLException while getting " +
                      "account; id = " + userId + " :\n" + ae);
        } finally {
            closeResultSet(result);
            closeStatement(stmt);
            closeConnection();
        }
    }

    private void deleteAccount (String userId) throws
                                         AccountDAODBUpdateException,
                                         AccountDAOSysException {
        String queryStr = "DELETE FROM " + DatabaseNames.ACCOUNT_TABLE
        + " WHERE userid = " + "'" + userId.trim() + "'";
        PreparedStatement stmt = null;
        Debug.println("queryString is: "+ queryStr);

        try {
            getDBConnection();
            //stmt = dbConnection.createStatement();
            //int resultCount = stmt.executeUpdate(queryStr);
            stmt = createPreparedStatement(dbConnection, queryStr);
            int resultCount = stmt.executeUpdate();

            if (resultCount != 1)
                throw new AccountDAODBUpdateException
                ("ERROR deleteing account from ACCOUNT_TABLE!! resultCount = "+
                 resultCount);
        } catch(SQLException se) {
            throw new AccountDAOSysException("SQLException while removing " +
                            "account; id = " + userId + " :\n" + se);
        } finally {
            closeStatement(stmt);
            closeConnection();
        }
    }

    private void updateAccount(AccountModel details) throws
                                         AccountDAODBUpdateException,
                                         AccountDAOAppException,
                                         AccountDAOSysException {
        if (!isValidData(details.getUserId(), details.getContactInformation()))
            throw new AccountDAOAppException("Illegal data values for update");

        ContactInformation info = details.getContactInformation();
        String queryStr = "UPDATE " + DatabaseNames.ACCOUNT_TABLE + " SET "
            + "status = " + "'" + details.getStatus().trim() + "',"
            + "email = " + "'" + info.getEMail().trim() + "',"
            + "firstname = " + "'" + info.getGivenName().trim() + "',"
            + "lastname = " + "'" + info.getFamilyName().trim() + "',"
            + "addr1 = " + "'"
            + info.getAddress().getStreetName1().trim() + "',";
        if (info.getAddress().getStreetName2() != null)
            queryStr += " addr2 = " + "'"
                +info.getAddress().getStreetName2().trim() +"',";
        else
            queryStr += " addr2 = " + "' ',";
            queryStr +=  "city = " + "'" + info.getAddress().getCity().trim()
            + "'," + "state = " + "'" + info.getAddress().getState().trim()
            + "'," + "zip = " + "'" + info.getAddress().getZipCode().trim()
            + "'," + "country = " + "'" + info.getAddress().getCountry().trim()
            + "'," + "phone = " + "'" + info.getTelephone().trim() + "'"
            + " WHERE userid = " + "'" + details.getUserId().trim() + "'";
        Debug.println("queryString is: "+ queryStr);

        PreparedStatement stmt = null;
        try {
            getDBConnection();
            stmt = createPreparedStatement(dbConnection, queryStr);
            int resultCount = stmt.executeUpdate();
            if (resultCount != 1)
                throw new AccountDAODBUpdateException
                ("ERROR updating account in ACCOUNT_TABLE!! resultCount = " +
                 resultCount);
        } catch(SQLException se) {
            throw new AccountDAOSysException("SQLException while updating " +
                     "account; id = " + details.getUserId() + " :\n" + se);
        } finally {
            closeStatement(stmt);
            closeConnection();
        }
    }

    private void getDBConnection() throws AccountDAOSysException {
        try {
            dbConnection = datasource.getConnection();
        } catch (SQLException se) {
            throw new AccountDAOSysException("SQL Exception while getting " +
                                "DB connection : \n" + se);
        }
        return;
    }

    private void closeConnection() throws AccountDAOSysException {
        try {
            if (dbConnection != null && !dbConnection.isClosed()) {
                dbConnection.close();
        }
        } catch (SQLException se) {
            throw new AccountDAOSysException("SQL Exception while closing " +
                                        "DB connection : \n" + se);
        }
    }

    private void closeResultSet(ResultSet result) throws AccountDAOSysException {
        try {
            if (result != null) {
                result.close();
            }
        } catch (SQLException se) {
            throw new AccountDAOSysException("SQL Exception while closing " +
                                        "Result Set : \n" + se);
        }
    }

    private void closeStatement(PreparedStatement stmt) throws AccountDAOSysException {
        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException se) {
            throw new AccountDAOSysException("SQL Exception while closing " +
                                        "Statement : \n" + se);
        }
    }

    /**
     * This method allows us to create a prepared search statement that will be friendly
     * To Japanese in cloudscape and other databases.
     * Basically we use a prepared statement that contants '?' where Japanese characters
     * may occur and then we use the stmt.setString(index, "search string")
     *
     * This technique should not affect the English searchs.
     *
    */

    private PreparedStatement createPreparedStatement(Connection con, String querry)
            throws SQLException {
        ArrayList targetStrings = new ArrayList();
        String processedQuerry = "";
        int startIndex = 0;
        if (startIndex != -1) {
            int index = startIndex;
            int literalStart = -1;
            while (index < querry.length()) {
                if (querry.charAt(index) == '\'') {
                    if (literalStart == -1 && index + 1 < querry.length()) {
                        literalStart = index +1;
                    } else {
                        String targetString = querry.substring(literalStart, index);
                        targetStrings.add(targetString);
                        literalStart = -1;
                        processedQuerry += "?";
                        index++;
                    }
                }
               if (index < querry.length() && literalStart == -1) {
                    processedQuerry += querry.charAt(index);
                }
                index++;
            }
            PreparedStatement stmt = con.prepareStatement(processedQuerry + " ");
            Iterator it = targetStrings.iterator();
            int counter =1;
            while (it.hasNext()) {
                String arg = (String)it.next();
                stmt.setString(counter++, arg);
            }
            return stmt;
        } else {
            PreparedStatement stmt = con.prepareStatement(querry);
            return stmt;
        }
    }

}