package jpestore.dao.mysql;

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 jpestore.dao.productDAO;
import jpestore.domain.Product;

public class ProductDaolmp implements productDAO {

	@Override
	public List<Product> findAll() {
		String sql = "select productid,category,cname,ename,image,listprice,unitcost,descn from product";
		List<Product>list = new ArrayList<Product>();
		try(
			Connection conn = DBHelper.getConnection();
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery()){
			while(rs.next()) {
				Product p = new Product();
			p.setProductid(rs.getString("productid"));
			p.setCategory(rs.getString("category"));
			p.setCname(rs.getString("cname"));
			p.setEname(rs.getString("Ename"));
			p.setImage(rs.getString("Image"));
			p.setListprice(rs.getDouble("listprice"));
			p.setUnitcost(rs.getDouble("unitcost"));
			p.setDescn(rs.getString("descn"));
			list.add(p);
			}
				}	 catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}
		return list;
	}
	public Product findBYID(String productid) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		
		String sql = "select productid,category,cname,ename,image,listprice,unitcost,descn from product where productid = ?";
		
		try {
			// 2.创建数据库连接
			conn = DBHelper.getConnection(); //1.加载驱动程序
			// 3.创建语句对象
			pstmt = conn.prepareStatement(sql);
			// 4.绑定参数
			pstmt.setString(1, productid);
			// 5.执行查询
			rs = pstmt.executeQuery();
			// 6.遍历结果集
			if (rs.next()) {

				Product p = new Product();
				
				p.setProductid(rs.getString("productid"));
				p.setCategory(rs.getString("category"));
				p.setCname(rs.getString("cname"));
				p.setEname(rs.getString("ename"));
				p.setImage(rs.getString("image"));
				p.setListprice(rs.getDouble("listprice"));
				p.setUnitcost(rs.getDouble("unitcost"));
				p.setDescn(rs.getString("descn"));
				
				return p;
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				DBHelper.close(rs, pstmt, conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return null;
	}
	
	@Override
	public List<Product> finByCategory(String category) {
	Connection conn = null;
	PreparedStatement pstmt = null ;
	ResultSet rs = null;
	List<Product>list = new ArrayList<Product>();
	String sql = "select productid,category,cname,ename,image,listprice,unitcost,descn from product where category=?";
	try {
		// 2.创建数据库连接
		conn = DBHelper.getConnection(); //1.加载驱动程序
		// 3.创建语句对象
		pstmt = conn.prepareStatement(sql);
		// 4.绑定参数
		pstmt.setString(1, category);
		// 5.执行查询
		rs = pstmt.executeQuery();
		// 6.遍历结果集
		while (rs.next()) {

			Product p = new Product();
			
			p.setProductid(rs.getString("productid"));
			p.setCategory(rs.getString("category"));
			p.setCname(rs.getString("cname"));
			p.setEname(rs.getString("ename"));
			p.setImage(rs.getString("image"));
			p.setListprice(rs.getDouble("listprice"));
			p.setUnitcost(rs.getDouble("unitcost"));
			p.setDescn(rs.getString("descn"));
			
			list.add(p);
		}
		
	} catch (SQLException e) {
		e.printStackTrace();
	} finally {
		try {
			DBHelper.close(rs, pstmt, conn);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	return list;
}
@Override
public int create(Product product) {
	// TODO Auto-generated method stub
	
	String sql = "insert into product (productid,category,cname,ename,image,listprice,unitcost,descn) values (?,?,?,?,?,?,?,?)";
	
	try(    //2.创建数据库连接
			Connection conn = DBHelper.getConnection();
			//3.创建语句对象
			PreparedStatement pstmt = conn.prepareStatement(sql);
			 ){
		//4.绑定参数
		pstmt.setString(1, product.getProductid());
		pstmt.setString(2, product.getCategory());
		pstmt.setString(3, product.getCname());
		pstmt.setString(4, product.getEname());
		pstmt.setString(5, product.getImage());
		pstmt.setDouble(6, product.getListprice());
		pstmt.setDouble(7, product.getUnitcost());
		pstmt.setString(8, product.getDescn());
		
		//5.执行
		int executeUpdate = pstmt.executeUpdate();
		System.out.printf("成功插入%d数据\n",executeUpdate);
		
	} catch (Exception e) {
		// TODO: handle exception
		e.printStackTrace();
	}
	
	return 0;
}

@Override
public int modify(Product product) {
	// TODO Auto-generated method stub
	
	String sql = "update product set unitcost = ? where productid = ?";
	
	try ( // 2.创建数据库连接
			Connection conn = DBHelper.getConnection();
			// 3. 创建语句对象
			PreparedStatement pstmt= conn.prepareStatement(sql)
			) {
			// 4. 绑定参数
			
			pstmt.setDouble(1, product.getUnitcost());
			pstmt.setString(2, product.getProductid());

			// 5. 执行修改（C、U、D）
			int executeUpdate = pstmt.executeUpdate();
			System.out.printf("成功更新%d条数据。\n", executeUpdate);
		} catch (SQLException e) {
			e.printStackTrace();
		}

	return 0;
}

@Override
public int remove(Product product) {
	// TODO Auto-generated method stub
	
	String sql = "delete from product where productid = ?";
	
	try ( // 2.创建数据库连接
			Connection conn = DBHelper.getConnection();
			// 3. 创建语句对象
			PreparedStatement pstmt = conn.prepareStatement(sql)) {

			// 4. 绑定参数
			pstmt.setString(1, product.getProductid());
			
			// 5. 执行修改（C、U、D）
			int executeUpdate = pstmt.executeUpdate();
			System.out.printf("成功删除%d条数据。\n", executeUpdate);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	return 0;
}}