package com.lsy.dao.impl;

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.lsy.dao.ProductDao;
import com.lsy.model.Product;

public class ProductDaoImp implements ProductDao {
    @Override
    public List<Product> findAll() {
        Product product;
        List<Product> list = new ArrayList<>();
        String sql = "select * from productlsy ";
        try (
                //1.创建数据库连接
                Connection conn = DBHelper.getConnection();
                //2.创建语句对象
                PreparedStatement pstm = conn.prepareStatement(sql);
                //3.执行查询
                ResultSet rs = pstm.executeQuery();) {
            // 6.遍历结果集
            while (rs.next()) {
                product = new Product();

                product.setProductid(rs.getString("productid"));
                product.setCategory(rs.getString("category"));
                product.setCname(rs.getString("cname"));
                product.setEname(rs.getString("ename"));
                product.setImage(rs.getString("image"));
                product.setDescn(rs.getString("descn"));
                product.setUnitcost(rs.getDouble("unitcost"));
                product.setListprice(rs.getDouble("listprice"));

                list.add(product);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return list;
    }

    @Override
    public List<Product> findByCategory(String categorylsy) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Product> products = new ArrayList<>();
        try {
            conn = DBHelper.getConnection();
            String sql = "select * from productlsy where category = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, categorylsy);
            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"));

                products.add(p);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return products;
    }

    @Override
    public Product findById(String productid) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        Product product = null;
        try {
            conn = DBHelper.getConnection();
            String sql = "select * from productlsy where productid = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, productid);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                product = new Product();

                product.setProductid(rs.getString("productid"));
                product.setCategory(rs.getString("category"));
                product.setCname(rs.getString("cname"));
                product.setEname(rs.getString("ename"));
                product.setImage(rs.getString("image"));
                product.setDescn(rs.getString("descn"));
                product.setUnitcost(rs.getDouble("unitcost"));
                product.setListprice(rs.getDouble("listprice"));

            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }

        }
        return product;
    }

    @Override
    public int create(Product product) {
        String sql = "insert into productlsy values(?,?,?,?,?,?,?,?)";
        try (
                Connection connection = DBHelper.getConnection();
                PreparedStatement pstm = connection.prepareStatement(sql);
        ) {
            // 6.遍历结果集
            pstm.setString(1, product.getProductid());
            pstm.setString(2, product.getCategory());
            pstm.setString(3, product.getCname());
            pstm.setString(4, product.getEname());
            pstm.setString(5, product.getImage());
            pstm.setString(6, product.getDescn());
            pstm.setDouble(7, product.getUnitcost());
            pstm.setDouble(8, product.getListprice());

            int a = pstm.executeUpdate();
            System.out.printf("成功插入%d数据\n", a);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return 0;
    }

    @Override
    public int delect(Product product) {
        String sql = "DELETE  from productlsy where productid = ?";

        try (
                Connection connection = DBHelper.getConnection();

                PreparedStatement pstm = connection.prepareStatement(sql)) {

            pstm.setString(1, product.getProductid());

            int affectedRows = pstm.executeUpdate();
            System.out.printf("成功删除%d条数据。\n", affectedRows);


        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    //    @Override
//    public int modify(Productlsy productlsy) {
//        return 0;
//    }
    public static void main(String[] args) {
        ProductDaoImp dao = new ProductDaoImp();
//       Productlsy productlsy = new Productlsy();
//        Scanner scanner = new Scanner(System.in);
//        productlsy.setProductidlsy(scanner.next());
//        productlsy.setCategorylsy("admin");
//        productlsy.setCnamelsy("admin");
//        productlsy.setEnamelsy("admin");
//        productlsy.setImagelsy("admin");
//        productlsy.setDescnlsy("admin");
//        productlsy.setUnitcostlsy(1);
//        productlsy.setListpricelsy(1);

//        dao.delect(productlsy);
//        dao.create(productlsy);

//       dao.findAll();

//        System.out.println(dao.findById("qww"));

    }
}