userdaoimpl.java

J2EE & Tomcat books published by hope

/*
 */
package com.sun.j2ee.workflow.user.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.Collection;

import javax.sql.DataSource;
import javax.naming.InitialContext;
import javax.naming.Context;
import javax.naming.NamingException;

import com.sun.j2ee.workflow.util.JNDINames;
import com.sun.j2ee.workflow.user.dao.UserDAO;
import com.sun.j2ee.workflow.util.DatabaseNames;
import com.sun.j2ee.workflow.user.model.UserModel;

import com.sun.j2ee.workflow.user.exceptions.UserDAOSysException;
import com.sun.j2ee.workflow.user.exceptions.UserDAOAppException;
import com.sun.j2ee.workflow.user.exceptions.UserDAODBUpdateException;
import com.sun.j2ee.workflow.user.exceptions.UserDAOFinderException;
import com.sun.j2ee.workflow.user.exceptions.UserDAODupKeyException;

import com.sun.j2ee.workflow.util.Debug;

/** This class calls the simple Datasource from Tomcat to get connection
 * No connection pooling is used.
 * @author Jian (James) Cai
 */
public class UserDAOImpl implements UserDAO {

    private transient Connection dbConnection = null;
    private transient DataSource datasource   = null;

    public UserDAOImpl() throws UserDAOSysException {
        try {
            InitialContext ic = new InitialContext();
            datasource = (DataSource) ic.lookup(JNDINames.WORKFLOW_DATASOURCE);
            Debug.println("getting datasource");
            if (datasource==null)
            {
            Debug.println("datasource is null");
                
            }
        } catch (NamingException ne) {
            throw new UserDAOSysException("Naming Exception while looking "
                                             + " up DataSource Connection " +
                                                JNDINames.WORKFLOW_DATASOURCE +
                                                    ": \n" + ne.getMessage());
        }
    }
/*
    public void query{
        
        // load the PoolMan JDBC Driver
        try {
            Class.forName("com.codestudio.sql.PoolMan").newInstance();
        } catch (Exception ex) {
            System.out.println("Could Not Find the PoolMan Driver. " +
                               "Is poolman.jar in your CLASSPATH?");
            System.exit(0);
        }

        // establish a Connection to the last database listed in the 'poolman.props' file
        Connection con = DriverManager.getConnection("jdbc:poolman");

        try {

            Statement s = con.createStatement();
            ResultSet res = s.executeQuery(sql);
            ResultSetMetaData meta = res.getMetaData();

            int cols = meta.getColumnCount();
            while (res.next()) {
                for (int i = 1; i <= cols; i++) {
                    Object val = res.getObject(i);
                    System.out.print("\t" + meta.getColumnLabel(i) + ": ");
                    System.out.print(val == null ? " " : val.toString());
                }
                System.out.print("\n");
            }
        } catch (SQLException sqe) {
        }
        finally {
            // this close method merely returns the Connection to the pool
            // after implicitly closing related resources (Statements and ResultSets)
            con.close();
            System.out.println("SAMPLE: Closed Con");
        }


    }
 */
    public void create(UserModel userinfo) throws UserDAOSysException,
                                UserDAODupKeyException,
                                UserDAODBUpdateException,
                                UserDAOAppException {
        insertuser(userinfo);
    }

    public UserModel load(String id) throws UserDAOSysException,
                              UserDAOFinderException {
        return(selectuser(id));
    }

    public void store(UserModel userinfo) throws UserDAODBUpdateException,
                               UserDAOAppException,
                               UserDAOSysException  {
        updateuser(userinfo);
    }

    public void remove(String id) throws UserDAODBUpdateException,
                                UserDAOSysException {
        deleteuser(id);
    }

    public String findByPrimaryKey(String userId) throws
                                            UserDAOFinderException,
                                            UserDAOSysException {
        if (userExists(userId))
        {           
                        Debug.println("user exists" + userId);
            return (userId);
        }
        
        throw new UserDAOFinderException("primary key not found :"+userId);
    }

    private boolean userExists (String userId) throws UserDAOSysException {
        PreparedStatement stmt = null;
        ResultSet result = null;
        boolean returnValue = false;
        String queryStr ="SELECT user_ID FROM " +
                    DatabaseNames.USER_TABLE
                        + " WHERE user_ID = " + "'" + userId.trim() + "'";
        Debug.println("queryString is: "+ queryStr);

        try {
            getDBConnection();
             Debug.println("after getDB begin createPreparedStatement");
           
            stmt = createPreparedStatement(dbConnection, queryStr);
            result = stmt.executeQuery();
            if ( !result.next() ) {
                returnValue = false;
            } else {
                userId = result.getString(1);
                returnValue = true;
            }
        } catch(SQLException se) {
            Debug.println(se.toString());
            Debug.println("SQLException while checking for an"
                           + " existing user - id -> " + userId);
            throw new UserDAOSysException(
                           "SQLException while checking for an"
                           + " existing user - id -> " + userId + " :\n" + se);
        } finally {
            closeResultSet(result);
            closeStatement(stmt);
            closeConnection();
        }
        Debug.println("return value");
        return returnValue;
    }

    private boolean isValidData(UserModel userinfo) {
        if ( (userinfo.getUser_ID() == null) ||
             (userinfo.getEmail() == null) ||
             (userinfo.getF_name() == null) || 
             (userinfo.getL_name() == null) || 
             (userinfo.getPassword() == null) ||
             (userinfo.getRole() == null) )
            return (false);
        else
            return (true);
    }

    private void insertuser(UserModel userinfo) throws
                                 UserDAOSysException,
                                 UserDAODupKeyException,
                                 UserDAODBUpdateException,
                                 UserDAOAppException {

        if (!isValidData(userinfo))
            throw new UserDAOAppException("Illegal data values for insert");
        if (userExists(userinfo.getUser_ID()))
            throw new UserDAODupKeyException("user exists for "+
                                                userinfo.getUser_ID());

        PreparedStatement stmt = null;
        String queryStr = "INSERT INTO " + DatabaseNames.USER_TABLE 
            +  "(user_ID, password, f_name, l_name, location, phone, email, title, role) "
            + "VALUES ("        
            + "'" + userinfo.getUser_ID().trim() + "',"
            + "'" + userinfo.getPassword().trim() + "',"
            + "'" + userinfo.getF_name().trim() + "',"
            + "'" + userinfo.getL_name().trim() + "',"
            + "'" + userinfo.getLocation().trim() + "',"
            + "'" + userinfo.getPhone().trim() +"',"
            + "'" + userinfo.getEmail().trim() +"',"
            + "'" + userinfo.getTitle().trim() +"'," 
            + "'" + userinfo.getRole().trim() +"')" ;

        Debug.println("queryString is: "+ queryStr);

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

            if ( resultCount != 1 ) {
                throw new UserDAODBUpdateException(
                    "ERROR in USER_TABLE INSERT !! resultCount = " +
                                   resultCount);
            }
        } catch(SQLException ae) {
            throw new UserDAOSysException(
                        "SQLException while inserting new " +
                        "user; id = " + userinfo.getUser_ID() + " :\n" + ae);
        } finally {
            closeStatement(stmt);
            closeConnection();
        }
    }

    private UserModel selectuser(String userId) throws
                                         UserDAOSysException,
                                         UserDAOFinderException {

        PreparedStatement stmt = null;
        ResultSet result = null;

        String queryStr = "SELECT *"+
                    " FROM " + DatabaseNames.USER_TABLE +
                        " WHERE USER_ID = " + "'" + userId.trim() + "'";
        Debug.println("queryString is: "+ queryStr);

        try {
            getDBConnection();
            stmt = createPreparedStatement(dbConnection, queryStr);
            result = stmt.executeQuery();
            if ( !result.next() )
                throw new UserDAOFinderException(
                                  "No record for primary key " + userId);

            int i = 1;
            String user_ID = result.getString(i++);