carteaminfodao.java

老牌java开发的物流管理系统 详细的我也没有怎么看 好像是恒基的

package com.wl.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.wl.dbconnection.DBManager;
import com.wl.entity.CarTeamInfo;


/**
 * 车队信息表
 * 
 * @author FengBo
 * 
 */
public class CarteaminfoDAO {
	
	DBManager db = new DBManager();
	boolean flag;
    /**
     * 添加车队信息的方法
     * @param car
     * @return  boolean
     */
	public boolean InsertCarteaminfo(CarTeamInfo car) {
		String sql = "insert into carteaminfotable values(?,?,?)";
		flag = db.openDB();
		if (flag) {
			try {
				db.ps = db.conn.prepareStatement(sql);
				db.ps.setString(1, car.getCarTeamId());
				db.ps.setString(2, car.getCarTeamName());
				db.ps.setInt(3, car.getChargeId());
				db.ps.executeUpdate();
			} catch (SQLException e) {
				return false;
			} finally {
				db.closeDB();
			}
		}
		return true;
	}
	/**
	 * 根据车队编号删除车队信息
	 * @param carteamid
	 * @return  boolean
	 */
	public boolean deleteCarTeamId(int Id){
		String sql = "delete carteaminfotable where Id=?";
		DBManager db = new DBManager();
		db.openDB();
		try {
			db.ps =db.conn.prepareStatement(sql);
			db.ps.setInt(1, Id);
			db.ps.executeUpdate();
		} catch (SQLException e) {
			return false;
		}
		finally{
			db.closeDB();
		}
		return true;		
	}
	/**
	 * 查询车队信息
	 * @return  list
	 */
	public List<CarTeamInfo> selectCarteaminfo(){
		List<CarTeamInfo> list = new ArrayList<CarTeamInfo>();
		flag = db.openDB();
		String sql = "select * from carteaminfotable";
		if(flag){
			try {
				db.ps =db.conn.prepareStatement(sql);
				ResultSet st = db.ps.executeQuery();
				while(st.next()){
					CarTeamInfo car = new CarTeamInfo(st.getInt(1), st.getString(2),st.getString(3),st.getInt(4));
					list.add(car);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			finally{
				db.closeDB();
			}
		}
		else{
			System.out.println("db not open");
		}
		return list;
	}
	
	/**
	 * 根据车队编号查询车队详细信息
	 * @param CarTeamId
	 * @return  list
	 */
	public CarTeamInfo selectCarTeamId(int id) {
		flag = db.openDB();
		CarTeamInfo car = null;
		String sql = "select * from carteaminfotable where Id=?";
		if(flag){
			try {
				db.ps = db.conn.prepareStatement(sql);
				db.ps.setInt(1, id);
				ResultSet st =db.ps.executeQuery();
				while(st.next()){
					 car = new CarTeamInfo(st.getInt(1),st.getString(2),st.getString(3),st.getInt(4));
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally{
				db.closeDB();
			}
		}
		return car;
	}
	/**
	 * 根据编号查询具体信息
	 * @param c
	 * @return
	 */
	public List<CarTeamInfo> uselectCarTeamId(String id) {
		List<CarTeamInfo> list = new ArrayList<CarTeamInfo>();
		flag = db.openDB();
		CarTeamInfo c=null;
		String sql = "select c.id,c.carteamid,c.carteamname,e.employeename from carteaminfotable c join employeeinfotable e on c.chargeid=e.id where c.carteamid like ?";
		if(flag){
			try {
				db.ps = db.conn.prepareStatement(sql);
				db.ps.setString(1, "%"+id+"%");
				ResultSet st =db.ps.executeQuery();
				while(st.next()){
					 c = new CarTeamInfo(st.getInt(1),st.getString(2),st.getString(3),st.getString(4));
					 list.add(c);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally{
				db.closeDB();
			}
		}
		return list;
	}
	public int updateCarTeam(CarTeamInfo c)
	{
		flag = db.openDB();
		int rows = 0;
		if(flag)
		{
			String sql = "update carteaminfotable set carTeamId=?,carTeamName=?,chargeId=? where Id=?";
			try {
				db.ps = db.conn.prepareStatement(sql);
				db.ps.setInt(4, c.getId());
				db.ps.setString(1, c.getCarTeamId());
				db.ps.setString(2, c.getCarTeamName());
				db.ps.setInt(3, c.getChargeId());
			    rows = db.ps.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			finally{
				db.closeDB();
			}
		}
		return rows;
	}
	public List<CarTeamInfo> queryNameForId() {
		flag=db.openDB();
		List<CarTeamInfo> list = new ArrayList<CarTeamInfo>();
		if(flag)
		{
			String sql="select distinct chargeid ,employeename  from carteaminfotable right join employeeinfotable on carteaminfotable.chargeid=employeeinfotable.id";
			try {
				db.ps = db.conn.prepareStatement(sql);
				ResultSet rs = db.ps.executeQuery();
				while(rs.next()) {
					int id = rs.getInt("chargeid");
					String name = rs.getString("employeename");
					CarTeamInfo car = new CarTeamInfo(id,name);
					list.add(car);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			finally{
				db.closeDB();
			}
		}
		else{
			System.out.println("db not open");
		}
		return list;
	}
//	public static void main(String[] args) {
//		CarteaminfoDAO d = new CarteaminfoDAO();
//		System.out.println(d.uselectCarTeamId("10005"));
//	}
public List<CarTeamInfo> queryAllCarteam() {
		
		List<CarTeamInfo> list = new ArrayList<CarTeamInfo>();
		flag = db.openDB();
		
		if(flag)
		{
			try {
				String sql="select c.id,carteamid ,carteamname,chargeid,employeename from carteaminfotable as c join EmployeeInfoTable as e on c.chargeid=e.id";
				db.ps = db.conn.prepareStatement(sql);
				ResultSet st = db.ps.executeQuery();
				while(st.next())
				{
					int id = st.getInt("id");
					String carteamid = st.getString("carteamid");
					String carteamname = st.getString("carteamname");
					String name = st.getString("employeename");
					int chargeid = st.getInt("chargeid");
					CarTeamInfo car = new CarTeamInfo(id,carteamid,carteamname,chargeid,name);
					list.add(car);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
			finally{
				db.closeDB();
			}
		}
		else {
			System.out.println("db not open");
		}
		return list;
	}
	/**
	 * 分页查询商品信息(2009.2.17 袁新锋修改)
	 */
	public List<CarTeamInfo> selectcarteamPage(int pageSize, int pageNum){
		 List<CarTeamInfo> list = new  ArrayList<CarTeamInfo>();
		 flag = db.openDB();
		 if(flag){
			 try{
				 String sql="select top " + pageSize
					+ " c.id,carteamid ,carteamname,chargeid,e.employeename from carteaminfotable as c join EmployeeInfoTable as e on c.chargeid=e.id where c.Id not in(select top "
					+ pageSize * pageNum
					+ " c.Id from carteaminfotable as c join EmployeeInfoTable as e on c.chargeid=e.id order by c.Id )order by c.Id";
				 db.ps = db.conn.prepareStatement(sql);
				ResultSet rs=db.ps.executeQuery();
				 while (rs.next()) {
					 CarTeamInfo c = new CarTeamInfo(rs.getInt(1), rs.getString(2), rs.getString(3),rs.getInt(4), rs.getString(5));
					 list.add(c);
				}
						
			 }catch(Exception e){
				 System.out.print("数据库连接失败！");
			 }
			 finally {
					db.closeDB();// 关闭数据库
				}
			 }
			 
			return list;
			 
			}
	public static void main(String[] args) {
	CarteaminfoDAO d = new CarteaminfoDAO();
	System.out.println(d.selectcarteamPage(1, 2)+" ");
}
			
	/**
	 * 通过页面大小获取总页数(2009.2.17 袁新锋修改)
	 * @param pageSize
	 * @return num
	 */
	public int getPageNum(int pageSize) {
		int num = 0;
		 db.openDB();// 打开数据库
			try {
				db.ps=db.conn.prepareStatement("select count(*) from carteaminfotable");
				ResultSet rs = db.ps.executeQuery();
				while(rs.next()){
					num = rs.getInt(1);
				}
				if (num % pageSize == 0) {
					return num / pageSize;
				} else {
					return num / pageSize + 1;
				}
			} catch (SQLException e) {
				System.out.println("数据库连接失败！");
				e.printStackTrace();
			}finally{
				db.closeDB();
			}
			return num;
		}
}