jobsinfodao.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.JobsInfo;

public class JobsInfoDAO
{

	/**
	 * @author 崔斌 － 职位信息DAO
	 */
	/*新增一个职位*/
	public void insertJobsInfo(JobsInfo job){
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			PreparedStatement ps = cn.prepareStatement("insert into " +
					"JobsInfoTable(JobsId,JobsName,DescribleJobs) " +
					"values(?,?,?)");
			ps.setString(1,job.getJobsId());
			ps.setString(2,job.getJobsName());
			ps.setString(3,job.getDescribleJobs());
			ps.addBatch();
			ps.executeUpdate();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
	}
	/*删除一个职位*/
	public void deleteJobsInfo(String JobsId){
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			Statement st = cn.createStatement();
			st.executeUpdate("delete from JobsInfoTable where JobsId='"+JobsId+"'");
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
	}
	/*修改一个职位,修改名称,备注*/
	public void updateJobsInfo(String JobsId,String JobsName,String DescribleJobs){
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			PreparedStatement ps = cn.prepareStatement("update JobsInfoTable set JobsName = ?,describlejobs=? where jobsid=?");
			ps.setString(1,JobsName);
			ps.setString(2,DescribleJobs);
			ps.setString(3,JobsId);
			ps.addBatch();
			ps.executeUpdate();
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
	}
	/*查找一个职位*/
	public JobsInfo selectJobById(String JobsId){
		JobsInfo job = null;
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			Statement st =  cn.createStatement();
			ResultSet rs = st.executeQuery("select * from JobsInfoTable where JobsId='"+JobsId+"'");
			if(rs.next()){
				int id = rs.getInt("JobsInfoTableId");
				String jobsId =rs.getString("jobsId");
				String JobsName = rs.getString("JobsName");
				String DescribleJobs = rs.getString("DescribleJobs");
				job = new JobsInfo(id,jobsId,JobsName,DescribleJobs);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		return job;
	}
	/*查找全部职位*/
	public List<JobsInfo> selectJobs(){
		List<JobsInfo> list = new ArrayList<JobsInfo>();
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		try
		{
			Statement st = cn.createStatement();
			ResultSet rs = st.executeQuery("select * from JobsInfoTable");
			while(rs.next()){
				int id = rs.getInt("JobsInfoTableId");
				String jobsId =rs.getString("jobsId");
				String JobsName = rs.getString("JobsName");
				String DescribleJobs = rs.getString("DescribleJobs");
				JobsInfo job = new JobsInfo(id,jobsId,JobsName,DescribleJobs);
				list.add(job);
			}
		} 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 JobsInfoTable");
			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<JobsInfo> selectJobsInfoPage(int pageSize, int pageNum) {
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		List<JobsInfo> list = new ArrayList<JobsInfo>();
		try {
			PreparedStatement ps = cn.prepareStatement("select top " + pageSize
							+ "* from JobsInfoTable where JobsInfoTableId not in(select top "
							+ pageSize * pageNum
							+ " JobsInfoTableId from JobsInfoTable order by JobsInfoTableId)order by JobsInfoTableId");
			ResultSet rs =ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("JobsInfoTableId");
				String jobsId =rs.getString("jobsId");
				String JobsName = rs.getString("JobsName");
				String DescribleJobs = rs.getString("DescribleJobs");
				JobsInfo job = new JobsInfo(id,jobsId,JobsName,DescribleJobs);
				list.add(job);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			db.closeConnection(cn);
		}
		return list;
	}
	
	public static void main(String[] args)
	{
//		JobsInfo job = new JobsInfo("HQBM","后勤部门");
//		JobsInfoDAO jdao = new JobsInfoDAO();
//		jdao.insertJobsInfo(job);
//		jdao.updateJobsInfo("HQBM","asdasda", "1111111");
//		jdao.deleteJobsInfo("HQBM");
//		job = jdao.selectJobById("BMJL");
//		System.out.print(job.getId()+","+job.getJobsName()+","+job.getJobsId()+","+job.getDescribleJobs());
//		
//		List<JobsInfo> list = jdao.selectJobs();
//		Iterator<JobsInfo> it = list.iterator();
//		while(it.hasNext()){
//			JobsInfo jobs = it.next();
//			System.out.println(jobs.getJobsName());
//		}
		
		
//		List<JobsInfo> list = jdao.selectJobsInfoPage(2, 1);
//		Iterator<JobsInfo> it = list.iterator();
//		while(it.hasNext()){
//			JobsInfo jobs = it.next();
//			System.out.println(jobs.getJobsName());
//		}
		
	}

}