taskdaoimpl.java

J2EE & Tomcat books published by hope

        String queryStr = "INSERT INTO " + DatabaseNames.TASK_TABLE
        +"(task_ID, task_name, plan_start_dt, plan_end_dt, act_start_dt, act_end_dt,"
        + " status, predecessor, project, task_desc,  type, comment) "
        + "VALUES ("
        + "'" + generatedID + "',"
        + "'" + taskinfo.getTask_name().trim() + "',"
        + "'" + taskinfo.getPlan_start_dt().toString() + "',"
        + "'" + taskinfo.getPlan_end_dt().toString() + "',"
        + "'" + taskinfo.getAct_start_dt().toString() + "',"
        + "'" + taskinfo.getAct_end_dt().toString() + "',"
        + "'" + taskinfo.getStatus().trim() + "',"
        + "'" + taskinfo.getPredecessor().trim() + "',"
        + "'" + taskinfo.getProject().trim() + "',"
        + "'" + taskinfo.getTask_desc().trim() + "',"
        + "'" + taskinfo.getType().trim() + "',"
        + "'" + taskinfo.getComment().trim() + "')";

        Debug.println("queryString is: "+ queryStr);
        
        try {
            getDBConnection();
            stmt = createPreparedStatement(dbConnection, queryStr);
            int resultCount = stmt.executeUpdate();
            
            if ( resultCount != 1 ) {
                throw new TaskDAODBUpdateException(
                "ERROR in TASK_TABLE INSERT !! resultCount = " +
                resultCount);
            }
        } catch(SQLException ae) {
            throw new TaskDAOSysException(
            "SQLException while inserting new " +
            "task; id = " + taskinfo.getTask_ID() + " :\n" + ae);
        } finally {
            closeStatement(stmt);
            closeConnection();
        }
    }
    
    private TaskModel selecttask(String taskId) throws
    TaskDAOSysException,
    TaskDAOFinderException {
        
        PreparedStatement stmt = null;
        ResultSet result = null;
        
        String queryStr = "SELECT *"+
        " FROM " + DatabaseNames.TASK_TABLE +
        " WHERE TASK_ID = " + "'" + taskId.trim() + "'";
        Debug.println("queryString is: "+ queryStr);
        
        try {
            getDBConnection();
            stmt = createPreparedStatement(dbConnection, queryStr);
            result = stmt.executeQuery();
            if ( !result.next() )
                throw new TaskDAOFinderException(
                "No record for primary key " + taskId);
            
            int i = 1;
            String task_ID = result.getString(i++);
            String task_name = result.getString(i++);
            Date plan_start_dt = result.getDate(i++);
            Date plan_end_dt = result.getDate(i++);
            Date act_start_dt = result.getDate(i++);
            Date act_end_dt = result.getDate(i++);
            String status = result.getString(i++);
            String predecessor = result.getString(i++);
            String project = result.getString(i++);
            String task_desc = result.getString(i++);
            String type = result.getString(i++);
            String comment = result.getString(i++);
            
            return(new TaskModel(task_ID, task_name, plan_start_dt, plan_end_dt, act_start_dt, act_end_dt, status, predecessor, project, task_desc, type, comment ));
        } catch(SQLException ae) {
            throw new TaskDAOSysException("SQLException while getting " +
            "task; id = " + taskId + " :\n" + ae);
        } finally {
            closeResultSet(result);
            closeStatement(stmt);
            closeConnection();
        }
    }
    
    private void deletetask(String taskId) throws
    TaskDAODBUpdateException,
    TaskDAOSysException {
        String queryStr = "DELETE FROM " + DatabaseNames.TASK_TABLE
        + " WHERE task_ID = " + "'" + taskId.trim() + "'";
        PreparedStatement stmt = null;
        Debug.println("queryString is: "+ queryStr);
        
        try {
            getDBConnection();
            //stmt = dbConnection.createStatement();
            //int resultCount = stmt.executeUpdate(queryStr);
            stmt = createPreparedStatement(dbConnection, queryStr);
            int resultCount = stmt.executeUpdate();
            
            if (resultCount != 1)
                throw new TaskDAODBUpdateException
                ("ERROR deleteing task from TASK_TABLE!! resultCount = "+
                resultCount);
        } catch(SQLException se) {
            throw new TaskDAOSysException("SQLException while removing " +
            "task; id = " + taskId + " :\n" + se);
        } finally {
            closeStatement(stmt);
            closeConnection();
        }
    }
    
    private void updatetask(TaskModel taskinfo) throws
    TaskDAODBUpdateException,
    TaskDAOAppException,
    TaskDAOSysException {
        if (!isValidData(taskinfo))
            throw new TaskDAOAppException("Illegal data values for update");
        
        String queryStr = "UPDATE " + DatabaseNames.TASK_TABLE + " SET "
        +"task_name = " + "'" + taskinfo.getTask_name().trim() + "',"
        +"plan_start_dt = " + "'" + taskinfo.getPlan_start_dt().toString() + "',"
        +"plan_end_dt = " + "'" + taskinfo.getPlan_end_dt().toString() + "',"
        +"act_start_dt = " + "'" + taskinfo.getAct_start_dt().toString() + "',"
        +"act_end_dt = " + "'" + taskinfo.getAct_end_dt().toString() + "',"
        +"status = " + "'" + taskinfo.getStatus().trim() + "',"
        +"predecessor = " + "'" + taskinfo.getPredecessor().trim() + "',"
        +"project = " + "'" + taskinfo.getProject().trim() + "',"
        +"task_desc = " + "'" + taskinfo.getTask_desc().trim() + "',"
        +"type = " + "'" + taskinfo.getType().trim() + "',"
        +"comment = " + "'" + taskinfo.getComment().trim() + "'"
        + " WHERE task_ID = " + "'" + taskinfo.getTask_ID().trim() + "'";
        Debug.println("queryString is: "+ queryStr);
        
        PreparedStatement stmt = null;
        try {
            getDBConnection();
            stmt = createPreparedStatement(dbConnection, queryStr);
            int resultCount = stmt.executeUpdate();
            if (resultCount != 1)
                throw new TaskDAODBUpdateException
                ("ERROR updating task in TASK_TABLE!! resultCount = " +
                resultCount);
        } catch(SQLException se) {
            throw new TaskDAOSysException("SQLException while updating " +
            "task; id = " + taskinfo.getTask_ID() + " :\n" + se);
        } finally {
            closeStatement(stmt);
            closeConnection();
        }
    }
    
    private void getDBConnection() throws TaskDAOSysException {
        try {
            dbConnection = datasource.getConnection();
        } catch (SQLException se) {
            System.out.println("SQL Connection: " + se);
            System.out.println("Trace");
            se.printStackTrace();
            throw new TaskDAOSysException("SQL Exception while getting " +
            "DB connection : \n" + se);
        }
        return;
    }
    
    private void closeConnection() throws TaskDAOSysException {
        try {
            if (dbConnection != null && !dbConnection.isClosed()) {
                dbConnection.close();
            }
        } catch (SQLException se) {
            throw new TaskDAOSysException("SQL Exception while closing " +
            "DB connection : \n" + se);
        }
    }
    
    private void closeResultSet(ResultSet result) throws TaskDAOSysException {
        try {
            if (result != null) {
                result.close();
            }
        } catch (SQLException se) {
            throw new TaskDAOSysException("SQL Exception while closing " +
            "Result Set : \n" + se);
        }
    }
    
    private void closeStatement(PreparedStatement stmt) throws TaskDAOSysException {
        try {
            if (stmt != null) {
                stmt.close();
            }
        } catch (SQLException se) {
            throw new TaskDAOSysException("SQL Exception while closing " +
            "Statement : \n" + se);
        }
    }
    
    /**
     * This method allows us to create a prepared search statement that will be friendly
     * To Japanese in cloudscape and other databases.
     * Basically we use a prepared statement that contants '?' where Japanese characters
     * may occur and then we use the stmt.setString(index, "search string")
     *
     * This technique should not affect the English searchs.
     *
     */
    
    private PreparedStatement createPreparedStatement(Connection con, String querry)
    throws SQLException {
        ArrayList targetStrings = new ArrayList();
        String processedQuerry = "";
        int startIndex = 0;
        if (startIndex != -1) {
            int index = startIndex;
            int literalStart = -1;
            while (index < querry.length()) {
                if (querry.charAt(index) == '\'') {
                    if (literalStart == -1 && index + 1 < querry.length()) {
                        literalStart = index +1;
                    } else {
                        String targetString = querry.substring(literalStart, index);
                        targetStrings.add(targetString);
                        literalStart = -1;
                        processedQuerry += "?";
                        index++;
                    }
                }
                if (index < querry.length() && literalStart == -1) {
                    processedQuerry += querry.charAt(index);
                }
                index++;
            }
            PreparedStatement stmt = con.prepareStatement(processedQuerry + " ");
            Iterator it = targetStrings.iterator();
            int counter =1;
            while (it.hasNext()) {
                String arg = (String)it.next();
                stmt.setString(counter++, arg);
            }
            return stmt;
        } else {
            PreparedStatement stmt = con.prepareStatement(querry);
            return stmt;
        }
    }
private String generateID() throws  TaskDAOSysException, TaskDAOFinderException {
        
        PreparedStatement stmt = null;
        ResultSet result = null;
        int lastint = 0;
        String queryStr = "SELECT TASK_ID"+
        " FROM " + DatabaseNames.TASK_TABLE;
        Debug.println("queryString is: "+ queryStr);
        
        try {
            getDBConnection();
            stmt = createPreparedStatement(dbConnection, queryStr);
            result = stmt.executeQuery();
            while (result.next()) {
                String lasttask_ID = result.getString(1);
                int last = Integer.valueOf(lasttask_ID).intValue();
                if (last>lastint) lastint = last;
            }
            
            if (lastint == -1) {
                throw new TaskDAOFinderException(
                "No record for generateID ");
            }
            return( String.valueOf(lastint+1) );
        } catch(SQLException ae) {
            throw new TaskDAOSysException("SQLException while getting last ID " + ae);
        } finally {
            closeResultSet(result);
            closeStatement(stmt);
            closeConnection();
        }
    }

}