accountdaomysql.java

Chinaxp 论坛源代码

/*
* XP Forum
*
* Copyright (c) 2002-2003 RedSoft Group.  All rights reserved.
*
*/
package org.redsoft.forum.dao.mysql;

/**
 * AccountDAO's implmentation for Mysql
 *
 * @author Charles Huang
 * @version 1.0
 */

import org.redsoft.forum.dao.Account;
import org.redsoft.forum.dao.AccountDAO;
import org.redsoft.forum.dao.PersistentAccount;
import org.redsoft.forum.exception.AccountAlreadyExistException;
import org.redsoft.forum.exception.AccountNotFoundException;
import org.redsoft.forum.exception.DAOException;
import org.redsoft.forum.util.Validation;
import org.apache.commons.beanutils.BeanUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.ArrayList;
import java.lang.reflect.InvocationTargetException;


public class AccountDAOmySql implements AccountDAO {

    public AccountDAOmySql() {
    }


    /**
     *  Get a db connection
     *
     * @return Connection - DB connection
     */
    public Connection getConnection() throws SQLException {
        return MysqlDAOFactory.getConnection();
    }


    /**
     *  Add a user account
     *
     * @param account - A account object that contains the user info,like userName,
     *                  password,email
     */
    public void addAccount(final Account account)
            throws DAOException,
            AccountAlreadyExistException {
        Validation.validateNotNull(account);
        try {
            findByUserName(account.getUserName());
            // if found
            throw new AccountAlreadyExistException();
        } catch (final AccountNotFoundException accountNotFoundException) {
            // if not found, go on to add account
        }
        final Connection conn;
        try {
            conn = getConnection();
            final PreparedStatement stat
                    = conn.prepareStatement("insert into "
                    + PersistentAccount.PROPERTY_ACCOUNT_TABLE
                    + " ( "
                    + PersistentAccount.PROPERTY_USER_NAME + ","
                    + PersistentAccount.PROPERTY_PASSWORD + ","
                    + PersistentAccount.PROPERTY_EMAIL + ","
                    + PersistentAccount.PROPERTY_COLUMN_WRITER + ")"
                    + " values( '"
                    + account.getUserName() + "', '"
                    + account.getPassword() + "', '"
                    + account.getEmail() + "', '"
                    + (account.isColumnWriter() ? "Y" : "N") + "')");

            stat.execute();
            stat.close();
            conn.close();
            return;
        } catch (SQLException e) {
            throw new DAOException(e);
        }
    }

    /**
     *  update a user account
     *
     * @param account - A account object that contains the user info,like userName,
     *                  password,email
     */
    public void updateAccount(final Account account)
            throws DAOException,
            AccountNotFoundException {
        Validation.validateNotNull(account);

        try {
            findByUserName(account.getUserName());
            final Connection conn = getConnection();
            String statement = "update "
                    + PersistentAccount.PROPERTY_ACCOUNT_TABLE
                    + " set "
                    + PersistentAccount.PROPERTY_PASSWORD + "='"
                    + account.getPassword() + "', "
                    + PersistentAccount.PROPERTY_EMAIL + "='"
                    + account.getEmail() + "', "
                    + PersistentAccount.PROPERTY_COLUMN_WRITER + "='"
                    + (account.isColumnWriter() ? "Y" : "N") + "' where "
                    + PersistentAccount.PROPERTY_USER_NAME + "='"
                    + account.getUserName() + "'";
            final PreparedStatement stat = conn.prepareStatement(statement);
            stat.execute();
            stat.close();
            conn.close();
        } catch (SQLException e) {
            throw new DAOException(e);
        }

    }

    /**
     *  Find a user given a user name
     *
     * @param userName - The user name
     * @return Account - A account object that contains the user info
     */
    public Account findByUserName(final String userName)
            throws DAOException,
            AccountNotFoundException {
        PersistentAccount account = null;
        Validation.validateNotNull(userName);
        final Connection conn;
        try {
            conn = getConnection();
            final PreparedStatement stat
                    = conn.prepareStatement("select "
                    + PersistentAccount.PROPERTY_USER_NAME + ","
                    + PersistentAccount.PROPERTY_PASSWORD + ","
                    + PersistentAccount.PROPERTY_EMAIL + ","
                    + PersistentAccount.PROPERTY_COLUMN_WRITER + " from "
                    + PersistentAccount.PROPERTY_ACCOUNT_TABLE
                    + " where "
                    + PersistentAccount.PROPERTY_USER_NAME
                    + "='" + userName + "'");
            final ResultSet resultSet = stat.executeQuery();
            if (resultSet.next()) {
                account = new PersistentAccount(resultSet.getString(PersistentAccount.PROPERTY_USER_NAME),
                        resultSet.getString(PersistentAccount.PROPERTY_PASSWORD),
                        resultSet.getString(PersistentAccount.PROPERTY_EMAIL),
                        resultSet.getString(PersistentAccount.PROPERTY_COLUMN_WRITER).equals("Y") ? true : false);
            }
            resultSet.close();
            stat.close();
            conn.close();
        } catch (SQLException e) {
            throw new DAOException(e);
        }
        if (account == null) {
            throw new AccountNotFoundException();
        }
        Account valAccount = new Account();
        // copy properties from persistent account to a value object account
        try {
            BeanUtils.copyProperties(valAccount, account);
        } catch (IllegalAccessException e) {
            throw new DAOException(e);
        } catch (InvocationTargetException e) {
            throw new DAOException(e);
        }
        return valAccount;
    }

    /**
     *  Remove a account given a user name
     *
     *  @param userName - User name
     */
    public void removeAccount(final String userName) throws DAOException {
        Validation.validateNotNull(userName);
        final Connection conn;
        try {
            conn = getConnection();
            final PreparedStatement stat
                    = conn.prepareStatement("delete from " + PersistentAccount.PROPERTY_ACCOUNT_TABLE
                    + " where " + PersistentAccount.PROPERTY_USER_NAME
                    + "='" + userName + "'");
            stat.execute();
            stat.close();
            conn.close();
        } catch (SQLException e) {
            throw new DAOException(e);
        }
    }

    /**
     * Find all columnWriters
     * @return
     * @throws DAOException
     */
    public Collection findColumnWriters() throws DAOException {
        final ArrayList result = new ArrayList();
        final Connection conn;
        try {
            conn = getConnection();
            final PreparedStatement stat = conn.prepareStatement(
                    "select "
                    + PersistentAccount.PROPERTY_USER_NAME + ","
                    + PersistentAccount.PROPERTY_PASSWORD + ","
                    + PersistentAccount.PROPERTY_EMAIL + ","
                    + PersistentAccount.PROPERTY_COLUMN_WRITER
                    + " from " + PersistentAccount.PROPERTY_ACCOUNT_TABLE
                    + " where " + PersistentAccount.PROPERTY_COLUMN_WRITER + "='Y'"
            );
            final ResultSet resultSet = stat.executeQuery();
            while (resultSet.next()) {
                PersistentAccount account = new PersistentAccount(
                        resultSet.getString(PersistentAccount.PROPERTY_USER_NAME),
                        resultSet.getString(PersistentAccount.PROPERTY_PASSWORD),
                        resultSet.getString(PersistentAccount.PROPERTY_EMAIL),
                        true
                );

                Account valAccount = new Account();
                // copy properties from persistent account to a value object account
                try {
                    BeanUtils.copyProperties(valAccount, account);
                } catch (IllegalAccessException e) {
                    throw new DAOException(e);
                } catch (InvocationTargetException e) {
                    throw new DAOException(e);
                }
                result.add(account);
            }
            resultSet.close();
            stat.close();
            conn.close();
        } catch (SQLException e) {
            throw new DAOException(e);
        }
        //System.out.println( result.size() );
        return result;
    }
}