productsdao.java

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

package dao;

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

import conn.DBConnection;
import domain.Products;

public class ProductsDao {
    public List<Products> getName(){
		
    	Connection conn = DBConnection.getConn();
    	String sql = "SELECT products.title FROM webpk.products,webpk.offers WHERE products.productsid=offers.productsid GROUP BY products.productsid";
    	List<Products> plist= new ArrayList<Products>();
    	try{
    		Statement stmt = (Statement) conn.createStatement();
    		ResultSet rs = stmt.executeQuery(sql);
    		
    		Products pro = null;
    		while(rs.next()){
    			pro = new Products();
    		    pro.setTitle(rs.getString("title"));
    		    plist.add(pro);
    		}
    		
    	}catch(Exception e){
    		e.printStackTrace();
    	}finally{
    		DBConnection.closeConn();
    	}
    	
    	return plist;
    }
    public Products getID(String name){
    	Connection conn = DBConnection.getConn();
    	PreparedStatement pstmt =null;
    	Products p = null;
    	String sql = "SELECT p.productsid FROM webpk.products p WHERE p.title=?";
    	try{
    		pstmt = conn.prepareStatement(sql);
    		pstmt.setString(1, name);
    		ResultSet rs = pstmt.executeQuery();
    		
    		while(rs.next()){
    			p = new Products();
    			p.setProductsid(rs.getInt("productsid"));
    			
    		}
    	}catch(Exception e){
    		e.printStackTrace();
    	}finally{
    		DBConnection.closeConn();
    	}
    	return p;
    }
    public List<Products> getProductsMessage(String name){
    	Connection conn = DBConnection.getConn();
    	Statement stmt =null;
    	List<Products> plist = new ArrayList<Products>();
    	Products p = null;
    	String sql = "SELECT * FROM webpk.products";
    	try{
    		stmt = conn.createStatement();
    		
    		ResultSet rs = stmt.executeQuery(sql);
    		
    		while(rs.next()){
    			p = new Products();
    			p.setProductsid(rs.getInt("productsid"));
    			p.setTitle(rs.getString("title"));
    			p.setTypeid(rs.getInt("typeid"));
    			p.setPhoto(rs.getString("photo"));
    			p.setRemark(rs.getString("remark"));
    			plist.add(p);
    		}
    	}catch(Exception e){
    		e.printStackTrace();
    	}finally{
    		DBConnection.closeConn();
    	}
    	return plist;
    }
  
    /**
	 * 获得表中数据记录的个数
	 *  
	 * @return totalRecord 表中的总记录数
	 */
	public static int getRowNumber() {
		Connection conn = DBConnection.getConn();
		int totalRecord = 0;
		try {
			Statement stmt = conn.createStatement();
			String tsql = "SELECT count(*) FROM webpk.products";
			ResultSet rs = stmt.executeQuery(tsql);
			rs.next();
			totalRecord = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				try {
					DBConnection.closeConn();
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
		
		return totalRecord;
	}

	/**
	 * 获得总的页数
	 * 
	 * @param pageSize
	 *            每页显示的条数
	 * @return 返回总页数
	 */
	public static 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 addProduct(String name,int id,String remark){
		Connection conn = null;
		PreparedStatement pstmt = null;
		int count = 0;
		String sql = "INSERT INTO webpk.products VALUE(null,?,?,null,?)";
		
		try{
			conn = DBConnection.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setInt(2, id);
			pstmt.setString(3,remark);
			count = pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			DBConnection.closeConn();
		}
		return count;
	}
	/*
	 * 删除商品信息
	 */
	public int delProduct(int id){
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		int count = 0;
		String sql = "DELETE FROM webpk.products WHERE products.productsid=?";
		
		try{
			
			conn = DBConnection.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			
			count = pstmt.executeUpdate();
			System.out.println("如果是1，说明删除成功："+count);
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			DBConnection.closeConn();
		}
		return count;
	}
	/*
	 * 根据商品ID查商品名称
	 */
    public Products getProductTitle(int id){
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT p.title FROM webpk.products p WHERE p.productsid=?";
		Products p = new Products();
		
		try{
			
			conn = DBConnection.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, id);
			rs = pstmt.executeQuery();
			rs.next();
			p.setTitle(rs.getString("title"));
			
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			DBConnection.closeConn();
		}
		return p;
	}

    public int addProduct(String name){
		
		Connection conn = null;
		PreparedStatement pstmt = null;
		String remark = "用户添加";
		int count = 0;
		String sql = "INSERT INTO webpk.products VALUE(null,?,?,null,?)";
		
		try{
			conn = DBConnection.getConn();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setInt(2, 0);
			pstmt.setString(3,remark);
			count = pstmt.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally {
			DBConnection.closeConn();
		}
		return count;
	}
}