departmentinfodao.java

基于j2ee的物流软件

package com.wuliu.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 java.util.List;

import com.wuliu.DBConnection.DBConnection;
import com.wuliu.entity.DepartmentInfo;

public class DepartmentInfoDAO
{

	/**
	 * @author 崔斌 - 部门信息
	 */
	/* 新增一个部门 */
	public void insertDepartmentInfo(DepartmentInfo department)
	{
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			PreparedStatement ps = cn.prepareStatement("insert into "
					+ "DepartmentInfoTable(DepartmentId,DepartmentName)"
					+ " values(?,?)");
			ps.setString(1, department.getDepartmentId());
			ps.setString(2, department.getDepartmentName());
			ps.addBatch();
			ps.executeUpdate();
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			db.closeConnection(cn);
		}
	}

	/* 删除一个部门 */
	public void deleteDepartmentInfo(String departmentId)
	{
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			Statement st = cn.createStatement();
			st.executeUpdate("delete from DepartmentInfoTable where DepartmentId='"
							+ departmentId + "'");
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			db.closeConnection(cn);
		}
	}

	/* 修改一个部门的信息 */
	public void updateDepartmentInfo(String departmentId, String departmentName)
	{
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			PreparedStatement ps = cn
					.prepareStatement("update DepartmentInfoTable set DepartmentName=? where DepartmentId=?");
			ps.setString(1, departmentName);
			ps.setString(2, departmentId);
			ps.addBatch();
			ps.executeUpdate();
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			db.closeConnection(cn);
		}
	}

	/* 查询一个部门 */
	public DepartmentInfo selectDepartmentById(String departmentId)
	{
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		DepartmentInfo Department = null;
		try
		{
			Statement st = cn.createStatement();
			ResultSet rs = st
					.executeQuery("select * from DepartmentInfoTable where DepartmentId='"
							+ departmentId + "'");
			if (rs.next())
			{
				int id = rs.getInt("DepartmentInfoTableId");
				String DepartmentId = rs.getString("DepartmentId");
				String DepartmentName = rs.getString("DepartmentName");
				Department = new DepartmentInfo(id, DepartmentId,
						DepartmentName);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			db.closeConnection(cn);
		}
		return Department;
	}

	/* 查询所有的部门信息 */
	public List<DepartmentInfo> selectDepartments()
	{
		List<DepartmentInfo> list = new ArrayList<DepartmentInfo>();
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			Statement st = cn.createStatement();
			ResultSet rs = st.executeQuery("select * from DepartmentInfoTable");
			while (rs.next())
			{
				int id = rs.getInt("DepartmentInfoTableId");
				String DepartmentId = rs.getString("DepartmentId");
				String DepartmentName = rs.getString("DepartmentName");
				DepartmentInfo Department = new DepartmentInfo(id,
						DepartmentId, DepartmentName);
				list.add(Department);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			db.closeConnection(cn);
		}
		return list;
	}
	//通过页面大小获取总页数
	public int getPageNum(int pageSize) {
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		int num = 0;
		try {
			PreparedStatement ps = cn.prepareStatement("select count(*) from DepartmentInfoTable");
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
		if (num % pageSize == 0) {
			return num / pageSize;
		} else {
			return num / pageSize + 1;
		}
	}
	// 分页查询商品信息
	public List<DepartmentInfo> selectDepartmentInfoPage(int pageSize, int pageNum) {
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		List<DepartmentInfo> list = new ArrayList<DepartmentInfo>();
		try {
			PreparedStatement ps = cn.prepareStatement("select top " + pageSize
							+ "* from DepartmentInfoTable where DepartmentInfoTableId not in(select top "
							+ pageSize * pageNum
							+ " DepartmentInfoTableId from DepartmentInfoTable order by DepartmentInfoTableId)order by DepartmentInfoTableId");
			ResultSet rs =ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("DepartmentInfoTableId");
				String DepartmentId = rs.getString("DepartmentId");
				String DepartmentName = rs.getString("DepartmentName");
				DepartmentInfo Department = new DepartmentInfo(id,
						DepartmentId, DepartmentName);
				list.add(Department);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
		return list;
	}
	public static void main(String[] args)
	{
//		DepartmentInfo department = new DepartmentInfo("HQBM","后勤部门");
//		DepartmentInfoDAO ddao = new DepartmentInfoDAO();
//		ddao.insertDepartmentInfo(department);
//		ddao.updateDepartmentInfo("HQBM", "后勤部");
//		ddao.deleteDepartmentInfo("HQBM");
//		department = ddao.selectDepartmentById("CJ");
//		System.out.print(department.getId()+","+department.getDepartmentName());		
//		
//		List<DepartmentInfo> list = ddao.selectDepartments();
//		Iterator<DepartmentInfo> it = list.iterator();
//		while(it.hasNext()){
//			DepartmentInfo d = it.next();
//			System.out.println(d.getDepartmentName());
//		}
//		
//		List<DepartmentInfo> list = ddao.selectDepartmentInfoPage(3,1);
//		Iterator<DepartmentInfo> it = list.iterator();
//		while(it.hasNext()){
//			DepartmentInfo d = it.next();
//			System.out.println(d.getDepartmentName());
//		}
//		
	}

}