carmanagedao.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.CarManage;

/**
 * @author 刘海鹏
 * @version 车辆管理表DAO
 */

public class CarManageDAO {
	private Connection conn = null;
	private PreparedStatement ps = null;
	private DBConnection dao = null;

	// 构造方法
	public CarManageDAO() {
		this.dao = new DBConnection();
	}

	// 通过Id查询车辆管理表
	public CarManage selectCarManageById(int id) {
		this.conn = this.dao.getConnection();
		CarManage carManage = null;
		try {
			this.ps = this.conn
					.prepareStatement("select * from CarManageTable where Code=?");
			this.ps.setInt(1, id);
			ResultSet rs = this.ps.executeQuery();
			while (rs.next()) {
				String carId = rs.getString("CarId");
				String carTeamId = rs.getString("CarTeamId");
				String brand = rs.getString("Brand");
				String style = rs.getString("Style");
				String carLoad = rs.getString("CarLoad");
				String usedTime = rs.getString("UsedTime");
				String driverTime = rs.getString("DriverTime");
				String licenceNumber = rs.getString("LicenceNumber");
				String licenceStyle = rs.getString("LicenceStyle");
				String transpotStyle = rs.getString("TranspotStyle");
				String remark = rs.getString("Remark");
				carManage = new CarManage(id, carId, carTeamId, brand, style,
						carLoad, usedTime, driverTime, licenceNumber,
						licenceStyle, transpotStyle, remark);
			}
			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 carManage;
	}
	/*
	 * void 1-14 添加查询所有信息方法
	 */
	public List<CarManage> selectCarManage(){
		this.conn = this.dao.getConnection();
		List<CarManage> list = new ArrayList<CarManage>();
		String sql="select * from CarManageTable";
		try {
			this.ps=this.conn.prepareStatement(sql);
			ResultSet rs = this.ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("Code");
				String carId = rs.getString("CarId");
				String carTeamId = rs.getString("CarTeamId");
				String brand = rs.getString("Brand");
				String style = rs.getString("Style");
				String carLoad = rs.getString("CarLoad");
				String usedTime = rs.getString("UsedTime");
				String driverTime = rs.getString("DriverTime");
				String licenceNumber = rs.getString("LicenceNumber");
				String licenceStyle = rs.getString("LicenceStyle");
				String transpotStyle = rs.getString("TranspotStyle");
				String remark = rs.getString("Remark");
				CarManage carManage = new CarManage(id, carId, carTeamId, brand, style,
						carLoad, usedTime, driverTime, licenceNumber,
						licenceStyle, transpotStyle, remark);
				list.add(carManage);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.dao.closePrepStmt(ps);
			this.dao.closeConnection(conn);
		}
		return list;
		
	}

	// 分页查询车辆管理表
	public List<CarManage> selectCarManagePage(int pageSize, int pageNum) {
		this.conn = this.dao.getConnection();
		List<CarManage> list = new ArrayList<CarManage>();
		CarManage carManage = null;
		try {
			this.ps = this.conn.prepareStatement("select top " + pageSize
					+ "* from CarManageTable where Code not in(select top "
					+ pageSize * pageNum
					+ " Code from CarManageTable order by Code)order by Code");
			ResultSet rs = this.ps.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("Code");
				String carId = rs.getString("CarId");
				String carTeamId = rs.getString("CarTeamId");
				String brand = rs.getString("Brand");
				String style = rs.getString("Style");
				String carLoad = rs.getString("CarLoad");
				String usedTime = rs.getString("UsedTime");
				String driverTime = rs.getString("DriverTime");
				String licenceNumber = rs.getString("LicenceNumber");
				String licenceStyle = rs.getString("LicenceStyle");
				String transpotStyle = rs.getString("TranspotStyle");
				String remark = rs.getString("Remark");
				carManage = new CarManage(id, carId, carTeamId, brand, style,
						carLoad, usedTime, driverTime, licenceNumber,
						licenceStyle, transpotStyle, remark);
				list.add(carManage);
			}
			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;
	}
	/* 自动生成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(Code) from " +
					"CarManageTable");
			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 boolean insertCarManage(CarManage carManage) {
		this.conn = this.dao.getConnection();
		CarManageDAO dao = new CarManageDAO();
		boolean flag = true;
		try {
			this.ps = this.conn
					.prepareStatement("insert into CarManageTable(carId, carTeamId, brand, " +
							"style, carLoad, usedTime, driverTime, licenceNumber, " +
							"licenceStyle, transpotStyle, remark) values(?,?,?,?,?,?,?,?,?,?,?)");
			this.ps.setString(1, carManage.getCarId());
			this.ps.setString(2, dao.getTempId());
			this.ps.setString(3, carManage.getBrand());
			this.ps.setString(4, carManage.getStyle());
			this.ps.setString(5, carManage.getCarLoad());
			this.ps.setString(6, carManage.getUsedTime());
			this.ps.setString(7, carManage.getDriverTime());
			this.ps.setString(8, carManage.getLicenceNumber());
			this.ps.setString(9, carManage.getLicenceStyle());
			this.ps.setString(10, carManage.getTranspotStyle());
			this.ps.setString(11, carManage.getRemark());
			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 deleteCarManageById(int id) {
		this.conn = this.dao.getConnection();
		try {
			this.ps = this.conn
					.prepareStatement("delete from CarManageTable where Code=?");
			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 updateCarManage(CarManage carManage) {
		this.conn = this.dao.getConnection();
		boolean flag = true;
		try {
			this.ps = this.conn
					.prepareStatement("update CarManageTable set carId=?, carTeamId=?, brand=?, style=?, carLoad=?, usedTime=?, driverTime=?, licenceNumber=?, licenceStyle=?, transpotStyle=?, remark=? where Code=?");
			this.ps.setString(1, carManage.getCarId());
			this.ps.setString(2, carManage.getCarTeamId());
			this.ps.setString(3, carManage.getBrand());
			this.ps.setString(4, carManage.getStyle());
			this.ps.setString(5, carManage.getCarLoad());
			this.ps.setString(6, carManage.getUsedTime());
			this.ps.setString(7, carManage.getDriverTime());
			this.ps.setString(8, carManage.getLicenceNumber());
			this.ps.setString(9, carManage.getLicenceStyle());
			this.ps.setString(10, carManage.getTranspotStyle());
			this.ps.setString(11, carManage.getRemark());
			this.ps.setInt(12, carManage.getId());
			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 CarManageTable");
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				this.dao.closePrepStmt(ps);
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (num % pageSize == 0) {
			return num / pageSize;
		} else {
			return num / pageSize + 1;
		}
	}
	
}