/* */package com.sun.j2ee.workflow.assignment.dao;import java.sql.Connection;import java.sql.Date;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.assignment.dao.AssignmentDAO;import com.sun.j2ee.workflow.util.DatabaseNames;import com.sun.j2ee.workflow.assignment.model.AssignmentModel;import com.sun.j2ee.workflow.assignment.exceptions.AssignmentDAOSysException;import com.sun.j2ee.workflow.assignment.exceptions.AssignmentDAOAppException;import com.sun.j2ee.workflow.assignment.exceptions.AssignmentDAODBUpdateException;import com.sun.j2ee.workflow.assignment.exceptions.AssignmentDAOFinderException;import com.sun.j2ee.workflow.assignment.exceptions.AssignmentDAODupKeyException;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 AssignmentDAOImpl implements AssignmentDAO {    private transient Connection dbConnection = null;    private transient DataSource datasource   = null;    public AssignmentDAOImpl() throws AssignmentDAOSysException {        try {            InitialContext ic = new InitialContext();            datasource = (DataSource) ic.lookup(JNDINames.WORKFLOW_DATASOURCE);        } catch (NamingException ne) {            throw new AssignmentDAOSysException("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(AssignmentModel assignmentinfo) throws AssignmentDAOSysException, AssignmentDAODupKeyException, AssignmentDAODBUpdateException, AssignmentDAOAppException {        insertassignment(assignmentinfo);    }    public AssignmentModel load(String id) throws AssignmentDAOSysException, AssignmentDAOFinderException {        return(selectassignment(id));    }    public void store(AssignmentModel assignmentinfo) throws AssignmentDAODBUpdateException, AssignmentDAOAppException, AssignmentDAOSysException  {        updateassignment(assignmentinfo);    }    public void remove(String id) throws AssignmentDAODBUpdateException, AssignmentDAOSysException {        deleteassignment(id);    }    public String findByPrimaryKey(String assignmentId) throws AssignmentDAOFinderException, AssignmentDAOSysException {        if (assignmentExists(assignmentId))            return (assignmentId);        throw new AssignmentDAOFinderException("primary key not found :"+assignmentId);    }    private boolean assignmentExists (String assignmentId) throws AssignmentDAOSysException {        PreparedStatement stmt = null;        ResultSet result = null;        boolean returnValue = false;        String queryStr ="SELECT assign_ID FROM " +                    DatabaseNames.ASSIGNMENT_TABLE                        + " WHERE assign_ID = " + "'" + assignmentId.trim() + "'";        Debug.println("queryString is: "+ queryStr);        try {            getDBConnection();            stmt = createPreparedStatement(dbConnection, queryStr);            result = stmt.executeQuery();            if ( !result.next() ) {                returnValue = false;            } else {                assignmentId = result.getString(1);                returnValue = true;            }        } catch(SQLException se) {            throw new AssignmentDAOSysException(                           "SQLException while checking for an"                           + " existing assignment - id -> " + assignmentId + " :\n" + se);        } finally {            closeResultSet(result);            closeStatement(stmt);            closeConnection();        }        return returnValue;    }    private boolean isValidData(AssignmentModel assignmentinfo) {        if ( (assignmentinfo.getAssign_ID() == null) ||             (assignmentinfo.getAssign_name() == null) ||              (assignmentinfo.getDesc() == null) )            return (false);        else            return (true);    }    private void insertassignment(AssignmentModel assignmentinfo) throws                                 AssignmentDAOSysException,                                 AssignmentDAODupKeyException,                                 AssignmentDAODBUpdateException,                                 AssignmentDAOAppException {        if (!isValidData(assignmentinfo))            throw new AssignmentDAOAppException("Illegal data values for insert");        if (assignmentExists(assignmentinfo.getAssign_ID()))            throw new AssignmentDAODupKeyException("assignment exists for "+                                                assignmentinfo.getAssign_ID());        PreparedStatement stmt = null;        String queryStr = "INSERT INTO " + DatabaseNames.ASSIGNMENT_TABLE            +"(assign_ID, assign_name, task, user, desc, comment) "            + "VALUES ("            + "'" + assignmentinfo.getAssign_ID().trim() + "',"            + "'" + assignmentinfo.getAssign_name().trim() + "',"            + "'" + assignmentinfo.getTask().trim() + "',"            + "'" + assignmentinfo.getUser().trim() + "',"            + "'" + assignmentinfo.getDesc().trim() + "',"            + "'" + assignmentinfo.getComment().trim() + "')";         Debug.println("queryString is: "+ queryStr);        try {            getDBConnection();            stmt = createPreparedStatement(dbConnection, queryStr);            int resultCount = stmt.executeUpdate();            if ( resultCount != 1 ) {                throw new AssignmentDAODBUpdateException(                    "ERROR in ASSIGNMENT_TABLE INSERT !! resultCount = " +                                   resultCount);            }        } catch(SQLException ae) {            throw new AssignmentDAOSysException(                        "SQLException while inserting new " +                        "assignment; id = " + assignmentinfo.getAssign_ID() + " :\n" + ae);        } finally {            closeStatement(stmt);            closeConnection();        }    }    private AssignmentModel selectassignment(String assignmentId) throws                                         AssignmentDAOSysException,                                         AssignmentDAOFinderException {        PreparedStatement stmt = null;        ResultSet result = null;        String queryStr = "SELECT *"+                    " FROM " + DatabaseNames.ASSIGNMENT_TABLE +                        " WHERE assign_ID = " + "'" + assignmentId.trim() + "'";        Debug.println("queryString is: "+ queryStr);        try {            getDBConnection();            stmt = createPreparedStatement(dbConnection, queryStr);            result = stmt.executeQuery();            if ( !result.next() )                throw new AssignmentDAOFinderException(                                  "No record for primary key " + assignmentId);            int i = 1;            String assign_ID = result.getString(i++);            String assign_name = result.getString(i++);            String task = result.getString(i++);            String user = result.getString(i++);            String desc = result.getString(i++);            String comment = result.getString(i++);                        return(new AssignmentModel(assign_ID, assign_name, task, user, desc, comment ));