cmsdbaccess.java

java 编写的程序

    return file;
}

/**
 * Reads a task from the Cms.
 *
 * @param id The id of the task to read.
 *
 * @return a task object or null if the task is not found.
 *
 * @exception CmsException Throws CmsException if something goes wrong.
 */
public CmsTask readTask(int id) throws CmsException {
    ResultSet res = null;
    CmsTask task = null;
    PreparedStatement statement = null;
    Connection con = null;

    try {
        con = DriverManager.getConnection(m_poolName);
        statement = con.prepareStatement(m_cq.get("C_TASK_READ"));
        statement.setInt(1, id);
        res = statement.executeQuery();
        if (res.next()) {
            id = res.getInt(m_cq.get("C_TASK_ID"));
            String name = res.getString(m_cq.get("C_TASK_NAME"));
            int autofinish = res.getInt(m_cq.get("C_TASK_AUTOFINISH"));
            java.sql.Timestamp starttime = SqlHelper.getTimestamp(res, m_cq.get("C_TASK_STARTTIME"));
            java.sql.Timestamp timeout = SqlHelper.getTimestamp(res, m_cq.get("C_TASK_TIMEOUT"));
            java.sql.Timestamp endtime = SqlHelper.getTimestamp(res, m_cq.get("C_TASK_ENDTIME"));
            java.sql.Timestamp wakeuptime = SqlHelper.getTimestamp(res, m_cq.get("C_TASK_WAKEUPTIME"));
            int escalationtype = res.getInt(m_cq.get("C_TASK_ESCALATIONTYPE"));
            int initiatoruser = res.getInt(m_cq.get("C_TASK_INITIATORUSER"));
            int originaluser = res.getInt(m_cq.get("C_TASK_ORIGINALUSER"));
            int agentuser = res.getInt(m_cq.get("C_TASK_AGENTUSER"));
            int role = res.getInt(m_cq.get("C_TASK_ROLE"));
            int root = res.getInt(m_cq.get("C_TASK_ROOT"));
            int parent = res.getInt(m_cq.get("C_TASK_PARENT"));
            int milestone = res.getInt(m_cq.get("C_TASK_MILESTONE"));
            int percentage = res.getInt(m_cq.get("C_TASK_PERCENTAGE"));
            String permission = res.getString(m_cq.get("C_TASK_PERMISSION"));
            int priority = res.getInt(m_cq.get("C_TASK_PRIORITY"));
            int state = res.getInt(m_cq.get("C_TASK_STATE"));
            int tasktype = res.getInt(m_cq.get("C_TASK_TASKTYPE"));
            String htmllink = res.getString(m_cq.get("C_TASK_HTMLLINK"));
            task = new CmsTask(id, name, state, tasktype, root, parent, initiatoruser, role, agentuser,
                                originaluser, starttime, wakeuptime, timeout, endtime, percentage,
                                permission, priority, escalationtype, htmllink, milestone, autofinish);
        }
    } catch (SQLException exc) {
        throw new CmsException(exc.getMessage(), CmsException.C_SQL_ERROR, exc);
    } catch (Exception exc) {
        throw new CmsException(exc.getMessage(), CmsException.C_UNKNOWN_EXCEPTION, exc);
    } finally {
        // close all db-resources
        if(res != null) {
             try {
                 res.close();
             } catch(SQLException exc) {
                 // nothing to do here
             }
        }
        if(statement != null) {
             try {
                 statement.close();
             } catch(SQLException exc) {
                 // nothing to do here
             }
        }
        if(con != null) {
             try {
                 con.close();
             } catch(SQLException exc) {
                 // nothing to do here
             }
        }
    }
    return task;
}
/**
 * Reads all tasks of a user in a project.
 * @param project The Project in which the tasks are defined.
 * @param agent The task agent
 * @param owner The task owner .
 * @param group The group who has to process the task.
 * @tasktype C_TASKS_ALL, C_TASKS_OPEN, C_TASKS_DONE, C_TASKS_NEW
 * @param orderBy Chooses, how to order the tasks.
 * @param sort Sort Ascending or Descending (ASC or DESC)
 *
 * @return A vector with the tasks
 *
 * @exception CmsException Throws CmsException if something goes wrong.
 */
public Vector readTasks(CmsProject project, CmsUser agent, CmsUser owner, CmsGroup role, int tasktype, String orderBy, String sort) throws CmsException {
    boolean first = true;
    Vector tasks = new Vector(); // vector for the return result
    CmsTask task = null; // tmp task for adding to vector
    ResultSet recset = null;
    Connection con = null;
    Statement statement = null;


    // create the sql string depending on parameters
    // handle the project for the SQL String
    String sqlstr = "SELECT * FROM " + m_cq.get("C_TABLENAME_TASK") + " WHERE ";
    if (project != null) {
        sqlstr = sqlstr + m_cq.get("C_TASK_ROOT") + "=" + project.getTaskId();
        first = false;
    } else {
        sqlstr = sqlstr + m_cq.get("C_TASK_ROOT") + "<>0 AND " + m_cq.get("C_TASK_PARENT") + "<>0";
        first = false;
    }

    // handle the agent for the SQL String
    if (agent != null) {
        if (!first) {
            sqlstr = sqlstr + " AND ";
        }
        sqlstr = sqlstr + m_cq.get("C_TASK_AGENTUSER") + "=" + agent.getId();
        first = false;
    }

    // handle the owner for the SQL String
    if (owner != null) {
        if (!first) {
            sqlstr = sqlstr + " AND ";
        }
        sqlstr = sqlstr + m_cq.get("C_TASK_INITIATORUSER") + "=" + owner.getId();
        first = false;
    }

    // handle the role for the SQL String
    if (role != null) {
        if (!first) {
            sqlstr = sqlstr + " AND ";
        }
        sqlstr = sqlstr + m_cq.get("C_TASK_ROLE") + "=" + role.getId();
        first = false;
    }
    sqlstr = sqlstr + getTaskTypeConditon(first, tasktype);

    // handel the order and sort parameter for the SQL String
    if (orderBy != null) {
        if (!orderBy.equals("")) {
            sqlstr = sqlstr + " ORDER BY " + orderBy;
            if (orderBy != null) {
                if (!orderBy.equals("")) {
                    sqlstr = sqlstr + " " + sort;
                }
            }
        }
    }
    try {
        con = DriverManager.getConnection(m_poolName);
        statement = con.createStatement();
        recset = statement.executeQuery(sqlstr);

        // if resultset exists - return vector of tasks
        while (recset.next()) {
            task = new CmsTask(recset.getInt(m_cq.get("C_TASK_ID")),
                                recset.getString(m_cq.get("C_TASK_NAME")),
                                recset.getInt(m_cq.get("C_TASK_STATE")),
                                recset.getInt(m_cq.get("C_TASK_TASKTYPE")),
                                recset.getInt(m_cq.get("C_TASK_ROOT")),
                                recset.getInt(m_cq.get("C_TASK_PARENT")),
                                recset.getInt(m_cq.get("C_TASK_INITIATORUSER")),
                                recset.getInt(m_cq.get("C_TASK_ROLE")),
                                recset.getInt(m_cq.get("C_TASK_AGENTUSER")),
                                recset.getInt(m_cq.get("C_TASK_ORIGINALUSER")),
                                SqlHelper.getTimestamp(recset, m_cq.get("C_TASK_STARTTIME")),
                                SqlHelper.getTimestamp(recset, m_cq.get("C_TASK_WAKEUPTIME")),
                                SqlHelper.getTimestamp(recset, m_cq.get("C_TASK_TIMEOUT")),
                                SqlHelper.getTimestamp(recset, m_cq.get("C_TASK_ENDTIME")),
                                recset.getInt(m_cq.get("C_TASK_PERCENTAGE")),
                                recset.getString(m_cq.get("C_TASK_PERMISSION")),
                                recset.getInt(m_cq.get("C_TASK_PRIORITY")),
                                recset.getInt(m_cq.get("C_TASK_ESCALATIONTYPE")),
                                recset.getString(m_cq.get("C_TASK_HTMLLINK")),
                                recset.getInt(m_cq.get("C_TASK_MILESTONE")),
                                recset.getInt(m_cq.get("C_TASK_AUTOFINISH")));
            tasks.addElement(task);
        }
    } catch (SQLException exc) {
        throw new CmsException(exc.getMessage(), CmsException.C_SQL_ERROR, exc);
    } catch (Exception exc) {
        throw new CmsException(exc.getMessage(), CmsException.C_UNKNOWN_EXCEPTION, exc);
    } finally {
        // close all db-resources
        if(statement != null) {
             try {
                 statement.close();
             } catch(SQLException exc) {
                 // nothing to do here
             }
        }
        if(con != null) {
             try {
                 con.close();
             } catch(SQLException exc) {
                 // nothing to do here
             }
        }
    }
    return tasks;
}
    /**
     * Writes a property for a file or folder.
     *
     * @param meta The property-name of which the property has to be read.
     * @param value The value for the property to be set.
     * @param resourceId The id of the resource.
     * @param resourceType The Type of the resource.
     *
     * @exception CmsException Throws CmsException if operation was not succesful
     */
    public void writeProperty(String meta, int projectId, String value, CmsResource resource,
                                      int resourceType)
        throws CmsException {
        CmsPropertydefinition propdef = readPropertydefinition(meta, resourceType);
        if( propdef == null) {
            // there is no propertydefinition for with the overgiven name for the resource
            throw new CmsException("[" + this.getClass().getName() + "] " + meta,
                CmsException.C_NOT_FOUND);
        } else {
            // write the property into the db
            PreparedStatement statement = null;
            Connection con = null;
            //int onlineProject = getOnlineProject(projectId).getId();
            int onlineProject = I_CmsConstants.C_PROJECT_ONLINE_ID;
            String usedPool;
            String usedStatement;
            if (projectId == onlineProject){
                usedPool = m_poolNameOnline;
                usedStatement = "_ONLINE";
            } else {
                usedPool = m_poolName;
                usedStatement = "";
            }
            boolean newprop=true;
            try {
                con = DriverManager.getConnection(usedPool);
                if( readProperty(propdef.getName(), projectId, resource, resourceType) != null) {
                    // property exists already - use update.
                    // create statement
                    statement = con.prepareStatement(m_cq.get("C_PROPERTIES_UPDATE"+usedStatement));
                    statement.setString(1, value);
                    statement.setInt(2, resource.getResourceId());
                    statement.setInt(3, propdef.getId());
                    statement.executeUpdate();
                    newprop=false;
                } else {
                    // property dosen't exist - use create.
                    // create statement
                    statement = con.prepareStatement(m_cq.get("C_PROPERTIES_CREATE"+usedStatement));
                    statement.setInt(1, nextId(m_cq.get("C_TABLE_PROPERTIES")));
                    statement.setInt(2, propdef.getId());
                    statement.setInt(3, resource.getResourceId());
                    statement.setString(4, value);
                    statement.executeUpdate();
                    newprop=true;
                }
            } catch(SQLException exc) {
                throw new CmsException("[" + this.getClass().getName() + "] " + exc.getMessage(),
                    CmsException.C_SQL_ERROR, exc);
            }finally {
                // close all db-resources
                if(statement != null) {
                     try {
                         statement.close();
                     } catch(SQLException exc) {
                         // nothing to do here
                     }
                }
                if(con != null) {
                     try {
                         con.close();
                     } catch(SQLException exc) {
                         // nothing to do here
                     }
                }
            }
        }
    }

    /**
     * Writes a user to the database.
     *
     * @param user the user to write
     * @exception thorws CmsException if something goes wrong.
     */
    public void writeUser(CmsUser user)
        throws CmsException {
        byte[] value=null;
        PreparedStatement statement = null;
        Connection con = null;

        try {
            con = DriverManager.getConnection(m_poolName);
            // serialize the hashtable
            ByteArrayOutputStream bout= new ByteArrayOutputStream();
            ObjectOutputStream oout=new ObjectOutputStream(bout);
            oout.writeObject(user.getAdditionalInfo());
            oout.close();
            value=bout.toByteArray();

            // write data to database
            statement = con.prepareStatement(m_cq.get("C_USERS_WRITE"));

            statement.setString(1,user.getDescription());
            statement.setString(2,user.getFirstname());
            statement.setString(3,user.getLastname());
            statement.setString(4,user.getEmail());
            statement.setTimestamp(5, new Timestamp(user.getLastlogin()));
            statement.setTimestamp(6, new Timestamp(user.getLastUsed()));
            statement.setInt(7,user.getFlags());
            statement.setBytes(8,value);
            statement.setInt(9, user.getDefaultGroupId());
            statement.setString(10,user.getAddress());
            statement.setString(11,user.getSection());
            statement.setInt(12,user.getType());
            statement.setInt(13,user.getId());
            statement.executeUpdate();
        }
        catch (SQLException e){
            throw new CmsException("["+this.getClass().getName()+"]"+e.getMessage(),CmsException.C_SQL_ERROR, e);
        }
        catch (IOException e){
            throw new CmsException("[CmsAccessUserInfoMySql/addUserInformation(id,object)]:"+CmsException. C_SERIALIZATION, e);
        } finally {
            // close all db-resources
            if(statement != null) {
                 try {
                     statement.close();
                 } catch(SQLException exc) {
                     // nothing to do here
                 }
            }
            if(con != null) {
                 try {
                     con.close();
                 } catch(SQLException exc) {
                     // nothing to do here
                 }
            }
        }
    }
}