carteaminfodao.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.text.MessageFormat;
import java.util.ArrayList;
import java.util.List;

import com.wuliu.DBConnection.DBConnection;
import com.wuliu.entity.CarTeamInfo;

/**
 * @author 刘海鹏
 * @version 车队信息表
 */
//2.5崔斌添加自动生成ID号
public class CarTeamInfoDAO {
	private Connection conn = null;
	private PreparedStatement ps = null;
	private DBConnection dao = null;
	
	//构造方法
	public CarTeamInfoDAO() {
       this.dao = new DBConnection();
	}
	
	//通过Id查询车队信息
	public CarTeamInfo selectCarTeamInfoById(int carTeamInfoTableId){
		this.conn = this.dao.getConnection();
		CarTeamInfo carTeamInfo = null;
		try {
			this.ps = this.conn.prepareStatement("select * from CarTeamInfoTable where CarTeamInfoTableId=?");
			this.ps.setInt(1, carTeamInfoTableId);
			ResultSet rs = this.ps.executeQuery();
			while(rs.next()){
				String carTeamId = rs.getString("CarTeamId");
				String carTeamName = rs.getString("CarTeamName");
				String chargeId = rs.getString("ChargeId");
				carTeamInfo = new CarTeamInfo(carTeamInfoTableId, carTeamId, carTeamName, chargeId);
			}
			this.dao.closeResultSet(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dao.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return carTeamInfo;
	}
	//通过CarTeamId查询车队信息
	public CarTeamInfo selectCarTeamInfoByCarTeamId(String id){
		this.conn = this.dao.getConnection();
		CarTeamInfo carTeamInfo = null;
		try {
			this.ps = this.conn.prepareStatement("select * from CarTeamInfoTable where CarTeamId=?");
			this.ps.setString(1, id);
			ResultSet rs = this.ps.executeQuery();
			while(rs.next()){
				String carTeamId = rs.getString("CarTeamId");
				String carTeamName = rs.getString("CarTeamName");
				String chargeId = rs.getString("ChargeId");
				carTeamInfo = new CarTeamInfo(carTeamId, carTeamName, chargeId);
			}
			this.dao.closeResultSet(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dao.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return carTeamInfo;
	}
	//分页查询车队信息
	public List<CarTeamInfo> selectCarTeamInfoPage(int pageSize, int pageNum){
		this.conn = this.dao.getConnection();
		List<CarTeamInfo> list = new ArrayList<CarTeamInfo>();
		CarTeamInfo carTeamInfo = null;
		try {
			this.ps = this.conn.prepareStatement("select top " + pageSize
					+ "* from CarTeamInfoTable where CarTeamInfoTableId not in(select top "
					+ pageSize * pageNum
					+ " CarTeamInfoTableId from CarTeamInfoTable order by CarTeamInfoTableId)order by CarTeamInfoTableId");
			ResultSet rs = this.ps.executeQuery();
			while(rs.next()){
				int id = rs.getInt("CarTeamInfoTableId");
				String carTeamId = rs.getString("CarTeamId");
				String carTeamName = rs.getString("CarTeamName");
				String chargeId = rs.getString("ChargeId");
				carTeamInfo = new CarTeamInfo(id, carTeamId, carTeamName, chargeId);
				list.add(carTeamInfo);
			}
			this.dao.closeResultSet(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dao.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	//插入一条车队信息
	public boolean insertCarTeamInfo(CarTeamInfo carTeamInfo){
		this.conn = this.dao.getConnection();
		CarTeamInfoDAO ctdao = new CarTeamInfoDAO();
		boolean flag = true;
		try {
			this.ps = this.conn.prepareStatement("insert into CarTeamInfoTable(carTeamId, carTeamName, chargeId)" +
					"values(?,?,?)");
			this.ps.setString(1, ctdao.getTempId());//2.5修改
			this.ps.setString(2, carTeamInfo.getCarTeamName());
			this.ps.setString(3, carTeamInfo.getChargeId());
			this.ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			flag = false;
		}finally{
			this.dao.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}
	
	//通过Id删除某条车队信息
	public void deleteCarTeamInfoById(int id){
		this.conn = this.dao.getConnection();
		try {
			this.ps = this.conn.prepareStatement("delete from CarTeamInfoTable where CarTeamInfoTableId=?");
			this.ps.setInt(1, id);
			this.ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dao.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	//修改车队信息
	public boolean updateCarTeamInfo(CarTeamInfo carTeamInfo){
		this.conn = this.dao.getConnection();
		boolean flag= true;
		try {
			this.ps = this.conn.prepareStatement("update CarTeamInfoTable set " +
					"CarTeamId=?, CarTeamName=?, ChargeId=? where CarTeamInfoTableId=?");
			this.ps.setString(1, carTeamInfo.getCarTeamId());
			this.ps.setString(2, carTeamInfo.getCarTeamName());
			this.ps.setString(3, carTeamInfo.getChargeId());
			this.ps.setInt(4, carTeamInfo.getCarTeamInfoTableId());
			this.ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			flag= false;
		}finally{
			this.dao.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}
	
	//通过页面大小获取总页数
	public int getPageNum(int pageSize) {
		int num = 0;
		this.conn = this.dao.getConnection();
		try {
			this.ps = this.conn.prepareStatement("select count(*) from CarTeamInfoTable");
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				this.dao.closePrepStmt(ps);
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (num % pageSize == 0) {
			return num / pageSize;
		} else {
			return num / pageSize + 1;
		}
	}
	/* 自动生成ID号 2.5增加
	 * 格式：CTI+年份号+流水号 CTI0901
	 * */
	private String getTempId()
	{
		String temp = null;
		DBConnection db = new DBConnection();
		Connection cn = db.getConnection();
		// 获得年份，如2009为09
		GetDate date = new GetDate();
		String year = date.getDate().substring(2,4);
		try
		{
			Statement st = cn.createStatement();
			ResultSet rs = st.executeQuery("select max(CarTeamInfoTableId) from " +
					"CarTeamInfoTable");
			if (rs.next())
			{
				int no = rs.getInt(1) + 1;
				// 生成编号
				temp =MessageFormat.format("CTI{0}{1,number,00}",year,no);;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}finally
		{
			db.closeConnection(cn);
		}
		return temp;
	}
	/*
	 * 
	 * 模糊查询所有的车队信息
	 * 
	 */
	public List<CarTeamInfo> selectCarTeamInfoMsg(String CarTeamId)
	{
		this.conn = this.dao.getConnection();
		List<CarTeamInfo> list = new ArrayList<CarTeamInfo>();
		CarTeamInfo carTeamInfo = null;
		try
		{
			this.ps = this.conn.prepareStatement("select * from CarTeamInfoTable where CarTeamId like ?");
			ps.setString(1,"%"+CarTeamId+"%");
			ResultSet rs = this.ps.executeQuery();
			
			while (rs.next())
			{
				String carTeamId = rs.getString("CarTeamId");
				String carTeamName = rs.getString("carTeamName");
				String chargeId = rs.getString("chargeId");
				carTeamInfo = new CarTeamInfo(carTeamId,carTeamName,chargeId);
				list.add(carTeamInfo);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		}
		this.dao.closeConnection(conn);
		return list;
	}
	
//	public static void main(String[] args){
//		CarTeamInfoDAO ctdao = new CarTeamInfoDAO();
//		List<CarTeamInfo> list = ctdao.selectCarTeamInfoMsg("0");
//		Iterator<CarTeamInfo> it = list.iterator();
//		while(it.hasNext()){
//			CarTeamInfo car = it.next();
//			System.out.println(car.getCarTeamName());
//		}
//	}
	
	
}