accountdaoimpl.java

来自「CRM源码This file describes some issues tha」· Java 代码 · 共 396 行

/*
 * Copyright 2006-2007 Queplix Corp.
 *
 * Licensed under the Queplix Public License, Version 1.1.1 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 * http://www.queplix.com/solutions/commercial-open-source/queplix-public-license/
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 */
package com.queplix.core.modules.inbox.utils.db;

import com.queplix.core.error.GenericSystemException;
import com.queplix.core.modules.inbox.utils.AccountDAO;
import com.queplix.core.modules.inbox.utils.AccountVO;
import com.queplix.core.utils.dao.AbstractDAO;
import com.queplix.core.utils.sql.SqlWrapper;
import com.queplix.core.utils.sql.SqlWrapperFactory;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.List;

/**
 * Description.
 * @author Konstantin Mironov
 * @since 8 Dec 2006
 */
public class AccountDAOImpl extends AbstractDAO implements AccountDAO {

    // ===================================================== Fields

    /** SQL wrapper implementation reference. */
    protected SqlWrapper sqlWrapper = SqlWrapperFactory.getSqlWrapper();

    // ===================================================== Overridden methods

    /*
     * (non-javadoc)
     *
     * @see AccountDAO#loadAccountVO
     */
    public AccountVO loadAccountVO(long accountID) {

        DEBUG("Try to get data for " + accountID + " mail account");

        AccountVO account = null;
        Connection con = null;
        PreparedStatement stmt = null;

        try {
            con = sqlWrapper.doConnection();
            String sql = DBRealmManager.getSql("get_account");
            stmt = sqlWrapper.doPreparedStatement(con, sql);
            stmt.setLong(1, accountID);

            ResultSet rs = sqlWrapper.executeQuery(stmt);
            if (!rs.next()) {
                throw new NullPointerException("Account #" + accountID + " cannot be found!");
            }

            account = buildAccountVO(rs);

        } catch(SQLException ex) {
            throw new GenericSystemException("SQL exception: " + ex.getMessage(), ex);

        } finally {
            sqlWrapper.closeConnection(con, stmt);
        }

        DEBUG("Got account VO for " + accountID + " : " + account);

        return account;
    } // loadAccountVO(long accountID) : AccountVO

    /*
     * (non-javadoc)
     *
     * @see AccountDAO#loadAllAccountVO
     */
    public Collection loadAllAccountVO() {

        List ret = new ArrayList();

        Connection con = null;
        PreparedStatement ps = null;

        try {
            con = sqlWrapper.doConnection();
            String sql = DBRealmManager.getSql("get_all_accounts");
            ps = sqlWrapper.doPreparedStatement( con, sql );

            ResultSet rs = sqlWrapper.executeQuery( ps );
            while( rs.next() ) {
                AccountVO account = buildAccountVO( rs );
                ret.add( account );
            }

        } catch( SQLException ex ) {
            throw new GenericSystemException( "SQL exception: " + ex.getMessage(), ex );

        } finally {
            sqlWrapper.closeConnection( con, ps );
        }

        return ret;
    }

    /*
     * (non-javadoc)
     *
     * @see AccountDAO#loadAllActiveAccountVO
     */
    public Collection loadAllActiveAccountVO(Date nextCheckDate) {

        List ret = new ArrayList();

        Connection con = null;
        PreparedStatement ps = null;

        try {
            con = sqlWrapper.doConnection();
            String sql = DBRealmManager.getSql("get_all_active_accounts");
            ps = sqlWrapper.doPreparedStatement(con, sql);
            sqlWrapper.getTimestampParser().setObject(ps, 1, nextCheckDate);

            ResultSet rs = sqlWrapper.executeQuery(ps);
            while(rs.next()) {
                AccountVO account = buildAccountVO(rs);
                ret.add(account);
            }
        } catch(SQLException ex) {
            throw new GenericSystemException("SQL exception: " + ex.getMessage(), ex);
        } finally {
            sqlWrapper.closeConnection(con, ps);
        }
        return ret;
    } // loadAllActiveAccountVO(Date) : Collection

    /*
     * (non-Javadoc)
     *
     * @see AccountDAO#loadValidFlag
     */
    public Boolean loadValidFlag(AccountVO account) {

        DEBUG("Try to get valid state for account: " + account);

        Connection con = null;
        PreparedStatement ps = null;

        Boolean validFlag;
        try {
            con = sqlWrapper.doConnection();
            String sql = DBRealmManager.getSql("get_valid_state");
            ps = sqlWrapper.doPreparedStatement(con, sql);
            ps.setLong(1, account.getAccountID());

            ResultSet rs = sqlWrapper.executeQuery(ps);
            if (!rs.next()) {
                throw new NullPointerException("Can't find account: " + account);
            }

            Integer num = sqlWrapper.getIntParser().getValue(rs, 1);
            if (num != null) {
                validFlag = new Boolean(num.intValue() > 0);
            } else {
                validFlag = null;
            }

        } catch(SQLException ex) {
            throw new GenericSystemException("SQL exception: " + ex.getMessage(), ex);

        } finally {
            sqlWrapper.closeConnection(con, ps);
        }

        DEBUG("Got valid state: " + validFlag);

        return validFlag;
    } // loadValidFlag(AccountVO) : Boolean

    /*
     * (non-Javadoc)
     *
     * @see AccountDAO#loadLastReceiveDate
     */
    public Date loadLastReceiveDate( AccountVO account ) {

        DEBUG( "Try to get last receive date for account: " + account );

        Connection con = null;
        PreparedStatement ps = null;

        Date lastReceiveDate;
        try {
            con = sqlWrapper.doConnection();
            String sql = DBRealmManager.getSql( "get_last_receive_date" );
            ps = sqlWrapper.doPreparedStatement( con, sql );
            ps.setLong( 1, account.getAccountID() );

            ResultSet rs = sqlWrapper.executeQuery( ps );
            if( !rs.next() ) {
                throw new NullPointerException( "Can't find account: " + account );
            }

            lastReceiveDate = sqlWrapper.getTimestampParser().getValue( rs, 1 );

        } catch( SQLException ex ) {
            throw new GenericSystemException( "SQL exception: " + ex.getMessage(), ex );

        } finally {
            sqlWrapper.closeConnection( con, ps );
        }

        DEBUG( "Got last receive date: " + lastReceiveDate );

        return lastReceiveDate;
    }

    /*
     * (non-Javadoc)
     *
     * @see AccountDAO#storeValidFlag
     */
    public void storeValidFlag( AccountVO account, Boolean b ) {

        Connection con = null;
        PreparedStatement stmt = null;

        try {
            con = sqlWrapper.doConnection();

            String sql = DBRealmManager.getSql( "update_valid_flag" );
            stmt = sqlWrapper.doPreparedStatement( con, sql );
            sqlWrapper.getIntParser().setObject( stmt, 1, ( b == null ) ? null :
                                                 ( b.booleanValue() ? new Integer( 1 ) : new Integer( 0 ) ) );
            stmt.setLong( 2, account.getAccountID() );
            sqlWrapper.executeUpdate( stmt );

        } catch( SQLException ex ) {
            throw new GenericSystemException( "SQL exception: " + ex.getMessage(), ex );

        } finally {
            sqlWrapper.closeConnection( con, stmt );
        }
    }

    /*
     * (non-Javadoc)
     *
     * @see AccountDAO#storeLastReceiveDate
     */
    public void storeLastReceiveDate(AccountVO account, Date d, String messageUID, String messageDigest) {

        Connection con = null;
        PreparedStatement stmt = null;

        try {
            con = sqlWrapper.doConnection();

            String sql = DBRealmManager.getSql("update_last_receive_date");
            stmt = sqlWrapper.doPreparedStatement(con, sql);
            sqlWrapper.getTimestampParser().setObject(stmt, 1, d);
            stmt.setString(2, messageUID);
            stmt.setString(3, messageDigest);
            stmt.setLong(4, account.getAccountID());
            sqlWrapper.executeUpdate(stmt);

        } catch(SQLException ex) {
            throw new GenericSystemException("SQL exception: " + ex.getMessage(), ex);
        } finally {
            sqlWrapper.closeConnection(con, stmt);
        } // try
    } // storeLastReceiveDate(AccountVO, Date, String, String)

    /*
     * (non-Javadoc)
     *
     * @see AccountDAO#storeLastMessageID
     */
    public void storeLastMessageID(AccountVO account, String messageUID, String messageDigest) {

        Connection con = null;
        PreparedStatement stmt = null;

        try {
            con = sqlWrapper.doConnection();

            String sql = DBRealmManager.getSql("update_last_message_id");
            stmt = sqlWrapper.doPreparedStatement(con, sql);
            stmt.setString(1, messageUID);
            stmt.setString(2, messageDigest);
            stmt.setLong(3, account.getAccountID());
            sqlWrapper.executeUpdate(stmt);

        } catch(SQLException ex) {
            throw new GenericSystemException("SQL exception: " + ex.getMessage(), ex);
        } finally {
            sqlWrapper.closeConnection(con, stmt);
        } // try
    } // storeLastMessageID(AccountVO, String, String)

    /*
     * (non-Javadoc)
     *
     * @see AccountDAO#storeNextCheckDate
     */
    public void storeNextCheckDate( AccountVO account, Date d ) {

        Connection con = null;
        PreparedStatement stmt = null;

        try {

            con = sqlWrapper.doConnection();

            String sql = DBRealmManager.getSql( "update_next_check_date" );
            stmt = sqlWrapper.doPreparedStatement( con, sql );
            sqlWrapper.getTimestampParser().setObject( stmt, 1, d );
            stmt.setLong( 2, account.getAccountID() );
            sqlWrapper.executeUpdate( stmt );

        } catch( SQLException ex ) {
            throw new GenericSystemException( "SQL exception: " + ex.getMessage(), ex );

        } finally {
            sqlWrapper.closeConnection( con, stmt );
        }
    } // storeNextCheckDate( AccountVO, Date)

    // ===================================================== Private methods

    // Builds AccountVO from ResultSet <code>rs</code>.
    private AccountVO buildAccountVO( ResultSet rs )
        throws SQLException {

        int i = 1;
        long accountID = rs.getLong( i++ );
        String providerName = rs.getString( i++ );
        String server = rs.getString( i++ );
        Integer port = sqlWrapper.getIntParser().getValue( rs, i++ );
        String folderName = rs.getString( i++ );
        String name = rs.getString( i++ );
        String password = rs.getString( i++ );
        Integer delayInMinutes = sqlWrapper.getIntParser().getValue( rs, i++ );
        Long maxMessageSize = sqlWrapper.getLongParser().getValue( rs, i++ );
        Long maxMailboxSize = sqlWrapper.getLongParser().getValue( rs, i++ );
        Boolean deleteMailFlag = null;
        Integer num = sqlWrapper.getIntParser().getValue( rs, i++ );
        if( num != null ) {
            deleteMailFlag = new Boolean( num.intValue() > 0 );
        }
        Boolean markEmailAsSeenFlag = null;
        num = sqlWrapper.getIntParser().getValue( rs, i++ );
        if( num != null ) {
            markEmailAsSeenFlag = new Boolean( num.intValue() > 0 );
        }
        Long defaultOwner = sqlWrapper.getLongParser().getValue( rs, i++ );
        Long defaultWorkgroup = sqlWrapper.getLongParser().getValue( rs, i++ );

        String messageUID = rs.getString( i++ );
        String messageDigest = rs.getString( i++ );

        AccountVO account = new AccountVO( accountID,
                                           providerName,
                                           server,
                                           port,
                                           folderName,
                                           name,
                                           password,
                                           delayInMinutes,
                                           maxMessageSize,
                                           maxMailboxSize,
                                           deleteMailFlag,
                                           markEmailAsSeenFlag,
                                           defaultOwner,
                                           defaultWorkgroup,
                                           messageUID,
                                           messageDigest
                );
        return account;
    }

} // class AccountDAOImpl extends AbstractDAO implements AccountDAO