goodstypedao.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.GoodsType;

/**
 * @author 刘海鹏
 * @version GoodsTypeDAO
 */
//2.5日崔斌新增自动生成ID
public class GoodsTypeDAO {
	private Connection conn = null;
	private PreparedStatement ps = null;
	private DBConnection dBConnection = null;

	public GoodsTypeDAO() {
       this.dBConnection = new DBConnection();
	}
	
	//通过Id查询商品分类
	public GoodsType selectGoodsTypeById(int id){
		this.conn = this.dBConnection.getConnection();
		GoodsType goodsType = null;
		try {
			this.ps = this.conn.prepareStatement("select * from GoodsTypeTable where GoodsTypeTableId=?");
			this.ps.setInt(1, id);
			ResultSet rs = this.ps.executeQuery();
			while(rs.next()){
				String GoodsTypeId = rs.getString("GoodsTypeId");
				String GoodsTypeName = rs.getString("GoodsTypeName");
				goodsType = new GoodsType(id, GoodsTypeId, GoodsTypeName);
			}
			this.dBConnection.closeResultSet(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dBConnection.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return goodsType;
	}
	/*
	 * void 添加方法to刘海鹏，查询所有记录,1-14
	 */
	public List<GoodsType> selectGoodsType(){
		this.conn=this.dBConnection.getConnection();
		List<GoodsType> list = new ArrayList<GoodsType>();
		String sql="select * from GoodsTypeTable";
		try {
			this.ps=this.conn.prepareStatement(sql);
			ResultSet rs=this.ps.executeQuery();
			while(rs.next()){
				int id = rs.getInt("GoodsTypeTableId");
				String goodsTypeId = rs.getString("GoodsTypeId");
				String goodsTypeName = rs.getString("GoodsTypeName");
				GoodsType goodsType = new GoodsType(id, goodsTypeId, goodsTypeName);
				list.add(goodsType);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			this.dBConnection.closePrepStmt(ps);
			this.dBConnection.closeConnection(conn);
		}
		return list;
		
	}
	
	//分页查询商品分类
	public List<GoodsType> selectGoodsTypePage(int pageSize, int pageNum){
		this.conn = this.dBConnection.getConnection();
		List<GoodsType> list = new ArrayList<GoodsType>();
		GoodsType goodsType = null;
		try {
			this.ps = this.conn.prepareStatement("select top " + pageSize
					+ "* from GoodsTypeTable where GoodsTypeTableId not in(select top "
					+ pageSize * pageNum
					+ " GoodsTypeTableId from GoodsTypeTable order by GoodsTypeTableId)order by GoodsTypeTableId");
			ResultSet rs = this.ps.executeQuery();
			while(rs.next()){
				int id = rs.getInt("GoodsTypeTableId");
				String goodsTypeId = rs.getString("GoodsTypeId");
				String goodsTypeName = rs.getString("GoodsTypeName");
				goodsType = new GoodsType(id, goodsTypeId, goodsTypeName);
				list.add(goodsType);
			}
			this.dBConnection.closeResultSet(rs);
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dBConnection.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	//插入一条商品分类
	public void insertGoodsType(GoodsType goodsType){
		GoodsTypeDAO gtdao = new GoodsTypeDAO();
		this.conn = this.dBConnection.getConnection();
		try {
			this.ps = this.conn.prepareStatement("insert into GoodsTypeTable(GoodsTypeId, GoodsTypeName)" +
					"values(?,?)");
			this.ps.setString(1,gtdao.getTempId());//2.5修改
			this.ps.setString(2, goodsType.getGoodsTypeName());
			this.ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dBConnection.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	//通过Id删除某条商品分类
	public void deleteGoodsTypeById(int id){
		this.conn = this.dBConnection.getConnection();
		try {
			this.ps = this.conn.prepareStatement("delete from GoodsTypeTable where GoodsTypeTableId=?");
			this.ps.setInt(1, id);
			this.ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dBConnection.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	//修改商品分类
	public void updateGoodsTypeById(GoodsType goodsType){
		this.conn = this.dBConnection.getConnection();
		try {
			this.ps = this.conn.prepareStatement("update GoodsTypeTable set " +
					"GoodsTypeName=? where GoodsTypeTableId=?");
			this.ps.setString(1, goodsType.getGoodsTypeName());
			this.ps.setInt(2, goodsType.getId());
			this.ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			this.dBConnection.closePrepStmt(ps);
			try {
				this.conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
	//通过页面大小获取总页数
	public int getPageNum(int pageSize) {
		int num = 0;
		this.conn = this.dBConnection.getConnection();
		try {
			this.ps = this.conn.prepareStatement("select count(*) from GoodsTypeTable");
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				num = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			try {
				this.dBConnection.closePrepStmt(ps);
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (num % pageSize == 0) {
			return num / pageSize;
		} else {
			return num / pageSize + 1;
		}
	}
	/* 自动生成ID号 
	 * 格式：TYPE+流水号 TYPE0001
	 * */
	private String getTempId()
	{
		String temp = null;
		this.conn = this.dBConnection.getConnection();
		try
		{
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("select max(GoodsTypeTableId) from " +
					"GoodsTypeTable");
			if (rs.next())
			{
				int no = rs.getInt(1) + 1;
				// 生成员工编号
				temp =MessageFormat.format("TYPE{0,number,0000}",no);;
			}
		} catch (SQLException e)
		{
			e.printStackTrace();
		} finally{
			try {
				this.dBConnection.closePrepStmt(ps);
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return temp;
	}

}