carlinedao.java

基于j2ee的物流软件

package com.wuliu.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wuliu.DBConnection.DBConnection;
import com.wuliu.entity.CarLine;

public class CarLineDAO
{

	private Connection conn = null;
	private PreparedStatement ps = null;
	private DBConnection DBC = null;

	public CarLineDAO()
	{
		this.DBC = new DBConnection();
	}

	/*
	 * @author 陈磊 查询所有的车队路线
	 * 
	 */
	public List<CarLine> selectAllCarLineTableMsg()
	{
		this.conn = this.DBC.getConnection();
		List<CarLine> list = new ArrayList<CarLine>();
		CarLine carLine = null;
		try
		{
			this.ps = this.conn.prepareStatement("select * from CarLineTable");
			ResultSet rs = this.ps.executeQuery();
			while (rs.next())
			{
				int id = rs.getInt("CarLineId");
				String carLineStart = rs.getString("CarLineStart");
				String carLineEnd = rs.getString("CarLineEnd");
				String carLinePass = rs.getString("CarLinePass");
				String carLineRemark = rs.getString("CarLineRemark");

				carLine = new CarLine(id, carLineStart, carLineEnd,
						carLinePass, carLineRemark);
				list.add(carLine);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			this.DBC.closePrepStmt(ps);
			try
			{
				this.conn.close();
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	/*
	 * 
	 * 插入路线
	 * 
	 */
	public boolean addCarLine(CarLine carLine)
	{
		this.conn = this.DBC.getConnection();
		boolean flag = true;
		try
		{
			this.ps = this.conn
					.prepareStatement("insert into CarLineTable (CarLineStart,CarLineEnd,CarLinePass,CarLineRemark)values(?,?,?,?)");
			this.ps.setString(1, carLine.getCarLineStart());
			this.ps.setString(2, carLine.getCarLineEnd());
			this.ps.setString(3, carLine.getCarLinePass());
			this.ps.setString(4, carLine.getCarLindeRemark());
			this.ps.executeUpdate();
		} catch (SQLException e)
		{
			e.printStackTrace();
			flag = false;
		} finally
		{
			this.DBC.closePrepStmt(ps);
			try
			{
				this.conn.close();
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}

	/*
	 * 
	 * 删除路线
	 * 
	 */
	public boolean deleteCarLineTable(int id)
	{
		this.conn = this.DBC.getConnection();
		boolean flag = true;
		try
		{
			this.ps = this.conn
					.prepareStatement("delete from CarLineTable where  CarLineId=? ");
			this.ps.setInt(1, id);
			this.ps.executeUpdate();
		} catch (SQLException e)
		{
			// TODO Auto-generated catch block
			e.printStackTrace();
			flag = false;
		} finally
		{
			this.DBC.closePrepStmt(ps);
			try
			{
				this.conn.close();
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}

	/*
	 * 根据ID查找
	 * 
	 */
	public CarLine selectCarLineTableMsgByName(int id)
	{
		this.conn = this.DBC.getConnection();
		CarLine carLineTable = null;
		try
		{
			this.ps = conn
					.prepareStatement("select * from CarLineTable where CarLineId=?");
			ps.setInt(1, id);
			ResultSet rs = this.ps.executeQuery();
			while (rs.next())
			{
				String carLineStart = rs.getString("CarLineStart");
				String carLineEnd = rs.getString("CarLineEnd");
				String carLinePass = rs.getString("CarLinePass");
				String carLineRemark = rs.getString("CarLineRemark");
				carLineTable = new CarLine(id, carLineStart, carLineEnd,
						carLinePass, carLineRemark);
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			this.DBC.closePrepStmt(ps);
			try
			{
				this.conn.close();
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return carLineTable;
	}

	/*
	 * 
	 * 根据(终点)查找所有信息
	 * 
	 */
	public CarLine selectCarLineTableMsgByName(String CarLineEnd)
	{
		this.conn = this.DBC.getConnection();
		CarLine carLineTable = null;
		try
		{
			this.ps = conn
					.prepareStatement("select * from CarLineTable where CarLineEnd=?");
			ps.setString(1, CarLineEnd);
			ResultSet rs = this.ps.executeQuery();
			while (rs.next())
			{
				int id = rs.getInt("CarLineId");
				String carLineStart = rs.getString("CarLineStart");
				String carLineEnd = rs.getString("CarLineEnd");
				String carLinePass = rs.getString("CarLinePass");
				String carLineRemark = rs.getString("CarLineRemark");

				carLineTable = new CarLine(id, carLineStart, carLineEnd,
						carLinePass, carLineRemark);

			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally
		{
			this.DBC.closePrepStmt(ps);
			try
			{
				this.conn.close();
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return carLineTable;
	}

	/*
	 * 修改
	 */
	public boolean updateConsignee(CarLine c)
	{
		this.conn = this.DBC.getConnection();
		boolean flag = true;
		try
		{
			this.ps = this.conn
					.prepareStatement("update CarLineTable set CarLineStart=?,CarLineEnd=?,CarLinePass=?,CarLineRemark=? where CarLineId=?");
			this.ps.setString(1, c.getCarLineStart());
			this.ps.setString(2, c.getCarLineEnd());
			this.ps.setString(3, c.getCarLinePass());
			this.ps.setString(4, c.getCarLindeRemark());
			this.ps.setInt(5, c.getId());
			this.ps.executeUpdate();
		} catch (SQLException e)
		{
			e.printStackTrace();
			flag = false;
		} finally
		{
			this.DBC.closePrepStmt(ps);
			try
			{
				this.conn.close();
			} catch (SQLException e)
			{
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return flag;
	}
	//分页查询车队信息
	public List<CarLine> selectCarLinePage(int pageSize, int pageNum){
		this.conn = this.DBC.getConnection();
		List<CarLine> list = new ArrayList<CarLine>();
		CarLine carline = null;
		try {
			this.ps = this.conn.prepareStatement("select top " + pageSize
					+ "* from CarLineTable where CarLineId not in(select top "
					+ pageSize * pageNum
					+ " CarLineId from CarLineTable order by CarLineId)order by CarLineId");
			ResultSet rs = this.ps.executeQuery();
			while(rs.next()){
				int id = rs.getInt("CarLineId");
				String carLineStart = rs.getString("carLineStart");
				String carLineEnd = rs.getString("carLineEnd");
				String carLinePass = rs.getString("carLinePass");
				String carLindeRemark = rs.getString("CarLineRemark");
				carline = new CarLine(id, carLineStart, carLineEnd, carLinePass,carLindeRemark);
				list.add(carline);
			}
			this.DBC.closeResultSet(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.DBC.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	//通过页面大小获取总页数
	public int getPageNum(int pageSize) {
		int num = 0;
		this.conn = this.DBC.getConnection();
		try {
			this.ps = this.conn.prepareStatement("select count(*) from CarLineTable");
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				this.DBC.closePrepStmt(ps);
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (num % pageSize == 0) {
			return num / pageSize;
		} else {
			return num / pageSize + 1;
		}
	}
	public static void main(String[] args)
	{
		CarLineDAO dao = new CarLineDAO();
		dao.selectCarLinePage(4, 1);
		
	}
}