orclclerkplandao.java

这是一个基于大唐电信的一个任务管理模块程序

package com.datang.struts.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.datang.struts.common.DbCommon;
import com.datang.struts.daoface.ClerkPlanDao;
import com.datang.struts.dto.TangPlan;
import com.datang.struts.dto.TangPlanPro;
import com.datang.struts.util.DbUtil;

public class OrclClerkPlanDao implements ClerkPlanDao{


	public int addClerkPlan(TangPlan plan) {
		Connection conn = DbUtil.getConnection();
		String sql = "INSERT INTO tang_plan(plan_id,plan_name,plan_description,task_id,clerk_id,plan_state,plan_start,plan_exp_end,plan_end,plan_feedback) "
					+"VALUES (seq_tang_plan.nextval,?,?,?,?,?,?,?,?,?)";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, plan.getPlan_name());
			pstmt.setString(2, plan.getPlan_description());
			pstmt.setInt(3, plan.getTask_id());
			pstmt.setString(4, plan.getClerk_id());
			pstmt.setString(5, plan.getPlan_state());
			pstmt.setDate(6, plan.getPlan_start());
			pstmt.setDate(7, plan.getPlan_exp_end());
			pstmt.setDate(8, plan.getPlan_end());
			pstmt.setString(9, plan.getPlan_feedback());
			pstmt.executeUpdate();
			return DbCommon.INSERT_CLERKPLAN_RIGHT;
		} catch (SQLException e) {
			if(DbCommon.DEBUG)
				e.printStackTrace();
			return DbCommon.INSERT_CLERKPLAN_ERROR;
		}finally{
			try {
				pstmt.close();
			} catch (SQLException e) {
				if(DbCommon.DEBUG)
					e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				if(DbCommon.DEBUG)
					e.printStackTrace();
			}
		}
	}

	public int deleteClerkPlan(TangPlan plan) {
		Connection conn = DbUtil.getConnection();
		String sql = "DELETE tang_plan WHERE plan_id=?";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, plan.getPlan_id());
			pstmt.executeUpdate();
			return DbCommon.DELETE_CLERKPLAN_RIGHT;
		} catch (SQLException e) {
			if(DbCommon.DEBUG)
				e.printStackTrace();
			return DbCommon.DELETE_CLERKPLAN_ERROR;
		}finally{
			try {
				pstmt.close();
			} catch (SQLException e) {
				if(DbCommon.DEBUG)
					e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				if(DbCommon.DEBUG)
					e.printStackTrace();
			}
		}
	}

	public ArrayList selectClerkPlan(TangPlanPro planpro) {
		ArrayList<TangPlan> al = new ArrayList<TangPlan>();
		Connection conn = DbUtil.getConnection();
		String sql = DbUtil.appendSelectPlanSql(planpro);
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				TangPlan plan = new TangPlan();
				plan.setPlan_id(rs.getInt(1));
				plan.setPlan_name(rs.getString(2));
				plan.setPlan_description(rs.getString(3));
				plan.setTask_id(rs.getInt(4));
				plan.setClerk_id(rs.getString(5));
				plan.setPlan_state(rs.getString(6));
				plan.setPlan_start(rs.getDate(7));
				plan.setPlan_exp_end(rs.getDate(8));
				plan.setPlan_end(rs.getDate(9));
				plan.setPlan_feedback(rs.getString(10));
				plan.setPlan_feedback_msg(rs.getString(11));
				al.add(plan);				
			}
			return al;
		} catch (SQLException e) {
			if(DbCommon.DEBUG)
				e.printStackTrace();
			return null;
		}finally{
			try {
				rs.close();
			} catch (SQLException e) {
				if(DbCommon.DEBUG)
					e.printStackTrace();
			}
			try {
				stmt.close();
			} catch (SQLException e) {
				if(DbCommon.DEBUG)
					e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				if(DbCommon.DEBUG)
					e.printStackTrace();
			}
		}
	}

	public int updateClerkPlan(TangPlan plan) {
		Connection conn = DbUtil.getConnection();
		String sql = "UPDATE tang_plan SET plan_name=?,plan_description=?,plan_state=?,plan_start=?,plan_exp_end=?,plan_end=?,plan_feedback=?,plan_feedback_msg=? WHERE plan_id=?";
		PreparedStatement pstmt = null;
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, plan.getPlan_name());
			pstmt.setString(2, plan.getPlan_description());
			pstmt.setString(3, plan.getPlan_state());
			pstmt.setDate(4, plan.getPlan_start());
			pstmt.setDate(5, plan.getPlan_exp_end());
			pstmt.setDate(6, plan.getPlan_end());
			pstmt.setString(7, plan.getPlan_feedback());
			pstmt.setString(8, plan.getPlan_feedback_msg());
			pstmt.setInt(9, plan.getPlan_id());
			pstmt.executeUpdate();
			return DbCommon.UPDATE_CLERKPLAN_RIGHT;
		} catch (SQLException e) {
			if(DbCommon.DEBUG)
				e.printStackTrace();
			return DbCommon.UPDATE_CLERKPLAN_ERROR;
		}finally{
			try {
				pstmt.close();
			} catch (SQLException e) {
				if(DbCommon.DEBUG)
					e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				if(DbCommon.DEBUG)
					e.printStackTrace();
			}
		}
	}

}