incomedco.java

struts+oracle+ Eclipse+tomcat海鲜超市

/*
 * 创建日期 2005-5-18
 *
 * TODO 要更改此生成的文件的模板，请转至
 * 窗口 － 首选项 － Java － 代码样式 － 代码模板
 */
package income.dco;

import income.vo.IncomeVo;

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

import dept.vo.DeptBean;

import util.DBConn;
import util.PageControl;
import util.SystemDate;



/**
 * @author yanjinkk
 * 
 * TODO 要更改此生成的类型注释的模板，请转至 窗口 － 首选项 － Java － 代码样式 － 代码模板
 */
public class IncomeDCO {
	public int sumNote = 0;

	public String getIncomeId() {
		String incomeId = null;
		//查询语句
		String sql = "  select income_seq.nextval as income_id from dual  ";

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			//得到连接
			conn =DBConn.getDBConn();
			pstmt = conn.prepareStatement(sql);
			//执行查询
			rs = pstmt.executeQuery();
			//得到查询结果
			if (rs.next()) {

				incomeId = rs.getString("income_id");

			}

		} catch (SQLException e) {

			e.printStackTrace();
		} finally {

			try {//关闭连接
				if (rs != null) {

					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.close();
				}

			} catch (SQLException e1) {

				e1.printStackTrace();
			}

		}
		//返回id
		return incomeId;

	}

	public int insertIncome(IncomeVo incomevo) {
		int flag = 0;

		String sql = "    INSERT INTO TABLE_INCOME "
				+ "(INCOME_ID,DEPT_ID,DAILY_INCOME,BUSINESS_DATE,LST_MOD_TIMESTEMP)"
				+ "VALUES(?,?,?,TO_DATE(?,'YYYY-MM-DD'),TO_DATE(?,'YYYY-MM-DD'))  ";
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {

			conn = DBConn.getDBConn();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, getIncomeId());
			pstmt.setString(2, incomevo.getDeptId());
			pstmt.setString(3, incomevo.getDailyIncome());
			pstmt.setString(4, SystemDate.getSystemDate());
			pstmt.setString(5, SystemDate.getSystemDate());

			flag = pstmt.executeUpdate();

			conn.commit();

		} catch (SQLException e) {

			try {
				conn.rollback();
			} catch (SQLException e1) {

				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.setAutoCommit(true);
					conn.close();
				}

			} catch (Exception e1) {

				e1.printStackTrace();
			}
		}

		return flag;

	}

	public int updateIncome(IncomeVo incomevo) {
		int flag = 0;

		String sql = "     update table_income"
				+ " set DEPT_ID=?,DAILY_INCOME=?,LST_MOD_TIMESTEMP=TO_DATE(?,'YYYY-MM-DD')"
				+ " where INCOME_ID=?  ";
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {

			conn = util.DBConn.getDBConn();
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sql);

			pstmt.setString(1, incomevo.getDeptId());
			pstmt.setString(2, incomevo.getDailyIncome());
			pstmt.setString(3, SystemDate.getSystemDate());
			pstmt.setString(4, incomevo.getIncomeId());
			
			flag = pstmt.executeUpdate();

			conn.commit();

		} catch (SQLException e) {

			try {
				conn.rollback();
			} catch (SQLException e1) {

				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.setAutoCommit(true);
					conn.close();
				}

			} catch (Exception e1) {

				e1.printStackTrace();
			}
		}

		return flag;
	}

	public List selectAllIncomeInfo(String curpage) {
		List list = new ArrayList();
		PageControl pageControl = new PageControl();
		//查询语句
		String sql = " SELECT INCOME_ID,d.dept_name, i.DEPT_ID,DAILY_INCOME,to_char(BUSINESS_DATE,'yyyy/mm/dd') as BUSINESS_DATE, to_char(LST_MOD_TIMESTEMP,'yyyy/mm/dd')LST_MOD_TIMESTEMP FROM TABLE_INCOME i,table_dept d where i.dept_id=d.dept_id order by income_id desc ";

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			//得到连接
			conn = util.DBConn.getDBConn();
			pstmt = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,//指针可移动
					ResultSet.CONCUR_READ_ONLY);
			
			//执行查询
			rs = pstmt.executeQuery();

			rs.absolute(-1);	  //指针到尾记录

			sumNote = rs.getRow();//总共多少条纪录

			pageControl.init(Integer.parseInt(curpage), sumNote);//初使话

			if (sumNote > 0) {
				if (pageControl.getStart() == 0) {
					rs.absolute(1);

				} else {
					rs.absolute(pageControl.getStart());
			
				}
			
			}
			//得到查询结果
			do{
				//用deptvo的对象封装所有数据
				IncomeVo incomevo = new IncomeVo();

				incomevo.setIncomeId(rs.getString("INCOME_ID"));
				incomevo.setDeptId(rs.getString("DEPT_ID"));
				incomevo.setDailyIncome(rs.getString("DAILY_INCOME"));
				incomevo.setBusinessDate(rs.getString("BUSINESS_DATE"));
				incomevo.setLastModDate(rs.getString("LST_MOD_TIMESTEMP"));
				incomevo.setDeptName(rs.getString("DEPT_NAME"));
				//将查询结果放到list中
				list.add(incomevo);
				if(!rs.next()) {
					break;
				}
				
			}while (rs.getRow() < pageControl.getEnd() + 1);

		} catch (SQLException e) {

			e.printStackTrace();
		} finally {

			try {//关闭连接
				if (rs != null) {

					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.close();
				}

			} catch (SQLException e1) {

				e1.printStackTrace();
			}

		}

		return list;

	}

	public IncomeVo selectIncomeById(String incomeId) {
		IncomeVo incomevo = new IncomeVo();

		//查询语句
		String sql = "  SELECT INCOME_ID, DEPT_ID,DAILY_INCOME,BUSINESS_DATE, LST_MOD_TIMESTEMP"
					+" FROM TABLE_INCOME"
					+" WHERE INCOME_ID=?  ";

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			//得到连接
			conn = util.DBConn.getDBConn();
			pstmt = conn.prepareStatement(sql);
			//执行查询
			pstmt.setString(1, incomeId);
			rs = pstmt.executeQuery();
			//得到查询结果
			if (rs.next()) {
				//用deptvo的对象封装所有数据
				incomevo.setIncomeId(rs.getString("INCOME_ID"));
				incomevo.setDeptId(rs.getString("DEPT_ID"));
				incomevo.setDailyIncome(rs.getString("DAILY_INCOME"));
				incomevo.setBusinessDate(rs.getString("BUSINESS_DATE"));
				incomevo.setLastModDate(rs.getString("LST_MOD_TIMESTEMP"));


			}

		} catch (SQLException e) {

			e.printStackTrace();
		} finally {

			try {//关闭连接
				if (rs != null) {

					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.close();
				}

			} catch (SQLException e1) {

				e1.printStackTrace();
			}

		}

		return incomevo;

	}
	
	public List selectAllDeptName() {
		List list = new ArrayList();

		//查询语句
		String sql = "  select dept_id,dept_name from table_dept order by dept_id desc ";

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			//得到连接
			conn = DBConn.getDBConn();
			pstmt = conn.prepareStatement(sql);
			//执行查询
			rs = pstmt.executeQuery();
			//得到查询结果
			while (rs.next()) {
				//用deptvo的对象封装所有数据
				DeptBean deptVo = new DeptBean();
				deptVo.setDeptId(rs.getString("dept_id"));
				deptVo.setDeptName(rs.getString("dept_name"));
				//将查询结果放到list中
				list.add(deptVo);
			}

		} catch (SQLException e) {

			e.printStackTrace();
		} finally {

			try {//关闭连接
				if (rs != null) {

					rs.close();
				}
				if (pstmt != null) {
					pstmt.close();
				}
				if (conn != null) {
					conn.close();
				}

			} catch (SQLException e1) {

				e1.printStackTrace();
			}

		}

		return list;

	}

}