dbusermanager.java

Ftp服务1.0

package ranab.server.ftp.usermanager;

import java.io.File;
import java.util.Collection;
import java.util.ArrayList;
import java.util.Collections;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import ranab.io.LogFile;
import ranab.server.ftp.FtpConfig;


/**
 * This is another database based user manager class. I have
 * tested it using MySQL and Oracle database. 
 * The table structure is:
 *
 * <pre>
 * CREATE TABLE FTP_USER (      
 *    LOGIN_ID VARCHAR(64) NOT NULL PRIMARY KEY,       
 *    PASSWORD VARCHAR(64),      
 *    HOME_DIR VARCHAR(128) NOT NULL,             
 *    ENABLED VARCHAR(8) NOT NULL,    
 *    WRITE_PERM VARCHAR(8) NOT NULL,       
 *    IDLE_TIME INT NOT NULL,             
 *    UPLOAD_RATE INT NOT NULL,             
 *    DOWNLOAD_RATE INT NOT NULL
 * )
 * </pre>
 *
 * @author <a href="mailto:[e-mail]">Rana Bhattacharyya</a>
 */
public
class DbUserManager extends UserManager {

    private Connection mDbConnection       = null;

    private PreparedStatement mNewUserStmt = null;
    private PreparedStatement mDelUserStmt = null;
    private PreparedStatement mGetUserStmt = null;
    private PreparedStatement mGetAllStmt  = null;
    private PreparedStatement mUpdUserStmt = null;
    
    /**
     * Constructor. Create database connection and
     * create statements.
     */
    public DbUserManager(FtpConfig cfg) throws Exception {
        super(cfg);

        // open database connection
        String className = cfg.getProperty(FtpConfig.PREFIX + "db.driver");
        String url = cfg.getProperty(FtpConfig.PREFIX + "db.url");
        String user = cfg.getProperty(FtpConfig.PREFIX + "db.user");
        String password = cfg.getProperty(FtpConfig.PREFIX + "db.password");
        int poolsize = cfg.getInteger(FtpConfig.PREFIX + "db.poolsize", 5);
        
        Class.forName(className);
        mDbConnection = DriverManager.getConnection(url, user, password);
        mDbConnection.setAutoCommit(true);
                
        // create table if necessary        
        createTable();        
                
        // prepare statements
        mDelUserStmt = mDbConnection.prepareStatement("DELETE FROM FTP_USER WHERE LOGIN_ID = ?");
        mGetAllStmt  = mDbConnection.prepareStatement("SELECT LOGIN_ID FROM FTP_USER");
        mNewUserStmt = mDbConnection.prepareStatement("INSERT INTO FTP_USER VALUES (?, ?, ?, ?, ?, ?, ?, ?)");
        mUpdUserStmt = mDbConnection.prepareStatement("UPDATE FTP_USER SET PASSWORD = ?, HOME_DIR = ?, ENABLED = ?, WRITE_PERM = ?, IDLE_TIME = ?, UPLOAD_RATE = ?, DOWNLOAD_RATE = ? WHERE LOGIN_ID = ?");
        mGetUserStmt = mDbConnection.prepareStatement("SELECT * FROM FTP_USER WHERE LOGIN_ID = ?");
        getConfig().getLogger().info("Database user manager opened.");
    }
    
    /**
     * Create table if necesary
     */
    private void createTable() throws SQLException {
        
        // check existance
        boolean bExist = false;
        try {
            String usrCnt = "SELECT COUNT(*) FROM FTP_USER";
            Statement stmt = mDbConnection.createStatement();
            ResultSet rs = stmt.executeQuery(usrCnt);
            rs.close();
            stmt.close();
            bExist = true;
        }
        catch(SQLException ex) {
            bExist = false;
        }
        if (bExist) {
            return;
        }
        
        // table does not exist - trying to create
        getConfig().getLogger().info("Table FTP_USER does non exist - creating table.");
        StringBuffer sb = new StringBuffer();
        sb.append("CREATE TABLE FTP_USER (\n"); 
        sb.append("  LOGIN_ID VARCHAR(64) NOT NULL PRIMARY KEY,\n");
        sb.append("  PASSWORD VARCHAR(64),\n");
        sb.append("  HOME_DIR VARCHAR(128) NOT NULL,\n");
        sb.append("  ENABLED VARCHAR(8) NOT NULL,\n");
        sb.append("  WRITE_PERM VARCHAR(8) NOT NULL,\n");
        sb.append("  IDLE_TIME INT NOT NULL,\n");             
        sb.append("  UPLOAD_RATE INT NOT NULL,\n");
        sb.append("  DOWNLOAD_RATE INT NOT NULL\n");
        sb.append(")\n");
        
        String sql = sb.toString();
        Statement stmt = mDbConnection.createStatement();
        stmt.execute(sql);
        stmt.close();
    }
    
    /**
     * Delete user. Delete the row from the table.
     */
    public synchronized void delete(String name) throws SQLException {
       mDelUserStmt.setString(1, name);
       mDelUserStmt.executeUpdate();
    }

    /**
     * Save user. If new insert a new row, else update the existing row.
     */
    public synchronized void save(User user) throws SQLException {

        // null value check
        if(user.getName() == null) {
            throw new NullPointerException("User name is null.");
        } 
                  
        if( !doesExist(user.getName()) ) {
            mNewUserStmt.setString(1, user.getName());
            mNewUserStmt.setString(2, getPassword(user));
            mNewUserStmt.setString(3, user.getVirtualDirectory().getRootDirectory());
            mNewUserStmt.setString(4, String.valueOf(user.getEnabled()));
            mNewUserStmt.setString(5, String.valueOf(user.getVirtualDirectory().getWritePermission()));
            mNewUserStmt.setInt(6, user.getMaxIdleTime());
            mNewUserStmt.setInt(7, user.getMaxUploadRate());
            mNewUserStmt.setInt(8, user.getMaxDownloadRate());
            mNewUserStmt.executeUpdate();
        }
        else {
            mUpdUserStmt.setString(1, getPassword(user));
            mUpdUserStmt.setString(2, user.getVirtualDirectory().getRootDirectory());
            mUpdUserStmt.setString(3, String.valueOf(user.getEnabled()));
            mUpdUserStmt.setString(4, String.valueOf(user.getVirtualDirectory().getWritePermission()));
            mUpdUserStmt.setInt(5, user.getMaxIdleTime());
            mUpdUserStmt.setInt(6, user.getMaxUploadRate());
            mUpdUserStmt.setInt(7, user.getMaxDownloadRate());
            mUpdUserStmt.setString(8, user.getName());
            mUpdUserStmt.executeUpdate();
        }
    }


    /**
     * Get the user object. Fetch the row from the table.
     */
    public synchronized User getUserByName(String name) {
        try {
            User thisUser = null;
            mGetUserStmt.setString(1, name);
            ResultSet rs = mGetUserStmt.executeQuery();
            if(rs.next()) {
                thisUser = new User();
                thisUser.setName(rs.getString("LOGIN_ID"));
                thisUser.getVirtualDirectory().setRootDirectory(new File(rs.getString("HOME_DIR")));
                thisUser.setEnabled(rs.getString("ENABLED").equals(Boolean.TRUE.toString()));
                thisUser.getVirtualDirectory().setWritePermission(rs.getString("WRITE_PERM").equals(Boolean.TRUE.toString()));
                thisUser.setMaxIdleTime(rs.getInt("IDLE_TIME"));
                thisUser.setMaxUploadRate(rs.getInt("UPLOAD_RATE"));
                thisUser.setMaxDownloadRate(rs.getInt("DOWNLOAD_RATE"));
            }
            rs.close();
            return thisUser;
        }
        catch(SQLException ex) {
            getConfig().getLogger().error(ex);
        }
        return null;
    }
    
    
    /**
     * User existance check
     */
    public synchronized boolean doesExist(String name) {
        boolean bValid = false;
        try {
            mGetUserStmt.setString(1, name);
            ResultSet rs = mGetUserStmt.executeQuery();
            bValid = rs.next();
            rs.close();
        }
        catch(SQLException ex) {
            bValid = false;
            getConfig().getLogger().error(ex);
        }
        return bValid;
    }
    
    
    /**
     * Get all user names from the database.
     */
    public synchronized Collection getAllUserNames() {
        ArrayList names = new ArrayList();
        try {
            ResultSet rs = mGetAllStmt.executeQuery();
            while(rs.next()) {
                names.add(rs.getString("LOGIN_ID"));
            }
            rs.close();
        }
        catch(SQLException ex) {
            getConfig().getLogger().error(ex);
        }
        Collections.sort(names);
        return names;
    }

    
    /**
     * Get user password.
     * If the password value is not null
     *    password = new password 
     * else 
     *   if user does exist
     *     password = old password
     *   else 
     *     password = ""
     */
    private synchronized String getPassword(User user) throws SQLException {
        
        if (user.getPassword() != null) {
            return user.getPassword();
        }
        
        String password = "";
        mGetUserStmt.setString(1, user.getName());
        ResultSet rs = mGetUserStmt.executeQuery();
        if (rs.next()) {
            password = rs.getString("PASSWORD");
        }
        rs.close();
        if (password == null) {
            password = "";
        }
        return password;
    }
    
    /**
     * User authentication
     */
    public synchronized boolean authenticate(String user, String password) {
        
        String existPassword = null;
        
        try {
            mGetUserStmt.setString(1, user);
            ResultSet rs = mGetUserStmt.executeQuery();
            if (rs.next()) {
                existPassword = rs.getString("PASSWORD");
            }
            rs.close();
        }
        catch(SQLException ex) {
            getConfig().getLogger().error(ex);
            return false;
        }
        
        if (existPassword == null) {
            existPassword = "";
        }
        
        return existPassword.equals(password);
    }
    
    
    /**
     * Close this user manager. Close the database statements and connection.
     */
    public synchronized void dispose() {
        if (mNewUserStmt != null) {
            try {mNewUserStmt.close(); } catch(SQLException ex) {}
            mNewUserStmt = null;
        }
        
        if (mDelUserStmt != null) {
            try {mDelUserStmt.close(); } catch(SQLException ex) {}
            mDelUserStmt = null;
        }
        
        if (mGetUserStmt != null) {
            try {mGetUserStmt.close(); } catch(SQLException ex) {}
            mGetUserStmt = null;
        }
        
        if (mGetAllStmt != null) {        
            try {mGetAllStmt.close(); } catch(SQLException ex) {}
            mGetAllStmt = null;
        }
        
        if (mUpdUserStmt != null) {
            try {mUpdUserStmt.close(); } catch(SQLException ex) {}
            mUpdUserStmt = null;
        }
        
        if (mDbConnection != null) {        
            try {mDbConnection.close(); } catch(SQLException ex) {}
            mDbConnection = null;
        }
    }    
}