fellowshipdao.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.Fellowship;

/**
 * @author 刘海鹏
 * @version 友情连接表DAO
 */
public class FellowshipDAO {
	private Connection conn = null;
	private PreparedStatement ps = null;
	private DBConnection dao = null;
	
	//构造方法
	public FellowshipDAO() {
       this.dao = new DBConnection();
	}
	
	//通过Id查询
	public Fellowship selectFellowshipById(String fellowshipId){
		this.conn = this.dao.getConnection();
		Fellowship fellowship = null;
		try {
			this.ps = this.conn.prepareStatement("select * from fellowship where fellowshipId=?");
			this.ps.setString(1, fellowshipId);
			ResultSet rs = this.ps.executeQuery();
			while(rs.next()){
				String websiteName = rs.getString("websiteName");
				String websiteAddress = rs.getString("websiteAddress");
				fellowship = new Fellowship(fellowshipId, websiteName, websiteAddress);
			}
			this.dao.closeResultSet(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dao.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return fellowship;
	}
	
	//分页查询
	public List<Fellowship> selectFellowshipPage(int pageSize, int pageNum){
		this.conn = this.dao.getConnection();
		List<Fellowship> list = new ArrayList<Fellowship>();
		Fellowship fellowship = null;
		try {
			this.ps = this.conn.prepareStatement("select top " + pageSize
					+ "* from fellowship where fellowshipTableId not in(select top "
					+ pageSize * pageNum
					+ " fellowshipTableId from fellowship order by fellowshipTableId)order by fellowshipTableId");
			ResultSet rs = this.ps.executeQuery();
			while(rs.next()){
				String fellowshipId = rs.getString("fellowshipId");
				String websiteName = rs.getString("websiteName");
				String websiteAddress = rs.getString("websiteAddress");
				fellowship = new Fellowship(fellowshipId, websiteName, websiteAddress);
				list.add(fellowship);
			}
			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 insertFellowship(Fellowship fellowship){
		this.conn = this.dao.getConnection();
		String fellowshipId = this.getTempId();
		boolean flag = false;
		try {
			this.ps = this.conn.prepareStatement("insert into fellowship(fellowshipId, websiteName, websiteAddress)" +
					"values(?,?,?)");
			this.ps.setString(1, fellowshipId);//2.5修改
			this.ps.setString(2, fellowship.getWebsiteName());
			this.ps.setString(3, fellowship.getWebsiteAddress());
			this.ps.executeUpdate();
			this.ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
			flag = true;
		}finally{
			this.dao.closeConnection(conn);
		}
		return flag;
	}
	
	//通过Id删除某条车队信息
	public void deleteFellowshipById(String fellowshipId){
		this.conn = this.dao.getConnection();
		try {
			this.ps = this.conn.prepareStatement("delete from fellowship where fellowshipId=?");
			this.ps.setString(1, fellowshipId);
			this.ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dao.closePrepStmt(ps);
			this.dao.closeConnection(conn);
		}
	}
	
	//修改信息
	public boolean updateFellowship(Fellowship fellowship){
		this.conn = this.dao.getConnection();
		boolean flag= true;
		try {
			this.ps = this.conn.prepareStatement("update fellowship set " +
					"websiteName=?, websiteAddress=? where fellowshipId=?");
			this.ps.setString(1, fellowship.getWebsiteName());
			this.ps.setString(2, fellowship.getWebsiteAddress());
			this.ps.setString(3, fellowship.getFellowshipId());
			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 fellowship");
			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号 格式：4位流水号
	private String getTempId(){
		String temp = null;
		this.conn = this.dao.getConnection();
		try {
			this.ps = this.conn.prepareStatement("select max(fellowshipId) from fellowship");
			ResultSet rs = this.ps.executeQuery();
			if (rs.next()){
				Integer id = rs.getInt(1) + 1;
				// 生成编号
				temp = id.toString();
			}
		} catch (SQLException e){
			e.printStackTrace();
		}
		return temp;
	}
}