producttypedao.java

交易撮合系统是一套买卖信息沟通的平台

package dao;

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

import java.sql.PreparedStatement;

import conn.DBConnection;
import domain.ProductType;
import domain.Products;

public class ProductTypeDao {
	
	public List<ProductType> getZongLei(){
		Connection conn = DBConnection.getConn();
		Statement stmt = null;
		ResultSet rs = null;
		
		String sql = "SELECT p.title FROM webpk.producttype p WHERE p.parent='商品'";
		
		List<ProductType> plist = new ArrayList<ProductType>();
		
		try{
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			
			ProductType pt = null;
			while(rs.next()){
				pt = new ProductType();
				pt.setTitle(rs.getString("title"));
				plist.add(pt);				
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			DBConnection.closeConn();
		}
		return plist;
	}
	
	public List<ProductType> getCilei(String n){
		Connection conn = DBConnection.getConn();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "SELECT p.title FROM webpk.producttype p WHERE p.parent=?";
		List<ProductType> clist = new ArrayList<ProductType>();
		
		try{
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1,n);
			rs = pstmt.executeQuery();
			
			ProductType pt = null;
			while(rs.next()){
				pt = new ProductType();
				pt.setTitle(rs.getString("title"));
				clist.add(pt);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			DBConnection.closeConn();
		} 
		return clist;
	}
	public List<ProductType> getProductTypeMessage(int id){
    	Connection conn = DBConnection.getConn();
    	PreparedStatement pstmt = null;
    	List<ProductType> ptlist = new ArrayList<ProductType>();
    	ProductType pt = null;
    	String sql = "SELECT * FROM webpk.producttype WHERE typeid=?";
    	try{
    		pstmt = (PreparedStatement)conn.prepareStatement(sql);
    		pstmt.setInt(1, id);
    		
    		ResultSet rs = pstmt.executeQuery();
    		
    		while(rs.next()){
    			pt = new ProductType();
    			
    			pt.setTypeid(rs.getInt("typeid"));
    			pt.setTitle(rs.getString("title"));
    			pt.setParent(rs.getString("parent"));
    			pt.setRemark(rs.getString("remark"));
    			ptlist.add(pt);
    		}
    	}catch(Exception e){
    		e.printStackTrace();
    	}finally{
    		DBConnection.closeConn();
    	}
    	return ptlist;
    }
	
	/**
	 * 获得表中数据记录的个数
	 *  
	 * @return totalRecord 表中的总记录数
	 */
	public int getRowNumber() {
		Connection conn = DBConnection.getConn();
		int totalRecord = 0;
		try {
			Statement stmt = conn.createStatement();
			String tsql = "SELECT count(*) FROM webpk.producttype";
			ResultSet rs = stmt.executeQuery(tsql);
			rs.next();
			totalRecord = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBConnection.closeConn();
		}
		
		return totalRecord;
	}

	/**
	 * 获得总的页数
	 * 
	 * @param pageSize
	 *            每页显示的条数
	 * @return 返回总页数
	 */
	public int getTotalPage(int pageSize) {
		int totalPage = 1;
		int tmpPage = 0;
		int rowNum = getRowNumber();
		tmpPage = rowNum % pageSize;
		if (tmpPage == 0) {
			totalPage = rowNum / pageSize;
		} else {
			totalPage = (int)(Math.floor(rowNum / pageSize) + 1);
		}
		if (totalPage == 0) {
			totalPage = 1;
		}
		return totalPage;
	}
	/*
     * 添加商品类型信息
     */
	public int addProductType(String ptype,String parent,String remark){
		Connection conn = null;
		PreparedStatement pstmt = null;
		int count = 0;
		String sql = "INSERT INTO webpk.producttype VALUE(null,?,?,?)";
		
		try{
			conn = DBConnection.getConn();
			pstmt = (PreparedStatement)conn.prepareStatement(sql);
			pstmt.setString(1, ptype);
			pstmt.setString(2, parent);
			pstmt.setString(3,remark);
			count = pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			DBConnection.closeConn();
		}
		return count;
	}
	/*
	 * 更改商品类型信息
	 */
	public int changeProductType(int id,String ptype,String parent,String remark){
		Connection conn = null;
		PreparedStatement pstmt = null;
		int count = 0;
		String sql = "UPDATE webpk.producttype p SET p.title=?,p.parent=?,p.remark=? WHERE p.typeid=?";
		
		try{
			conn = DBConnection.getConn();
			pstmt = (PreparedStatement)conn.prepareStatement(sql);
			pstmt.setString(1,ptype);
			pstmt.setString(2, parent);
			pstmt.setString(3, remark);
			pstmt.setInt(4, id);
			count = pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DBConnection.closeConn();
		}
		return count;
	}
}