deptdao.java

struts+oracle+ Eclipse+tomcat海鲜超市

/*
 * 创建日期 2007-6-11
 *
 * TODO 要更改此生成的文件的模板，请转至
 * 窗口 － 首选项 － Java － 代码样式 － 代码模板
 */
package dept.dao;

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;
/**
 * @author yanjinkk
 *
 * TODO 要更改此生成的类型注释的模板，请转至
 * 窗口 － 首选项 － Java － 代码样式 － 代码模板
 */
public class DeptDAO {
	public int sumNote = 0;
	
	public List selectAllDeptInfo() {
		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;

	}
	
	
	
	
	
	
	
	
	public List selectDeptInfo(String curpage) {
		List list = new ArrayList();
		PageControl pageControl = new PageControl();
		
		String sql = " select dept_id as id ,dept_name as name,dept_descreption as description from table_dept order by id desc ";

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			//得到连接
			conn = 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的对象封装所有数据
				DeptBean deptVo = new DeptBean();
				deptVo.setDeptId(rs.getString("id"));
				deptVo.setDeptName(rs.getString("name"));
				deptVo.setDescription(rs.getString("description"));
				
				//将查询结果放到list中
				list.add(deptVo);
				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 int getDeptId() {
		int deptId = 0;
		//查询语句
		String sql = "  select dept_seq.nextval as dept_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()) {

				deptId = rs.getInt("dept_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 deptId;

	}

	public int insertDept(DeptBean deptVo) {
		int flag = 0;

		String sql = " INSERT INTO TABLE_DEPT (DEPT_ID,DEPT_NAME,DEPT_DESCREPTION)VALUES(?,?,?)  ";
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {

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

			pstmt.setInt(1, getDeptId());
			pstmt.setString(2, deptVo.getDeptName());
			pstmt.setString(3, deptVo.getDescription());

			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 updateDept(DeptBean deptVo) {
		int flag = 0;

		String sql = " update TABLE_DEPT set DEPT_NAME=?,DEPT_DESCREPTION=? WHERE DEPT_ID=?  ";
		Connection conn = null;
		PreparedStatement pstmt = null;

		try {
			//得到连接
			conn = DBConn.getDBConn();
			//不自动提交
			conn.setAutoCommit(false);
			pstmt = conn.prepareStatement(sql);
			//设置站位符
			pstmt.setString(1, deptVo.getDeptName());
			pstmt.setString(2, deptVo.getDescription());
			pstmt.setString(3, deptVo.getDeptId());

			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 DeptBean selectDeptById(String deptId) {
		DeptBean deptvo = new DeptBean();

		//查询语句
		String sql = "  select dept_id,dept_name,dept_descreption from table_dept where dept_id=?  ";

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

		try {
			//得到连接
			conn = DBConn.getDBConn();
			pstmt = conn.prepareStatement(sql);
			//执行查询
			pstmt.setString(1,deptId);
			rs = pstmt.executeQuery();
			//得到查询结果
			if (rs.next()) {
				//用deptvo的对象封装所有数据
				deptvo.setDeptId(rs.getString("dept_id"));
				deptvo.setDeptName(rs.getString("dept_name"));
				deptvo.setDescription(rs.getString("dept_descreption"));

			}

		} 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 deptvo;

	}

}