package flowershop.daoimpl;

import java.sql.Connection;
import java.sql.DriverManager;
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 flowershop.dao.UserDao;
import flowershop.model.User;

public class UserDaoImpl implements UserDao {

    //查询所有
    @Override
    public List<User> findAll() {
        // TODO 自动生成的方法存根
        ArrayList<User> users = new ArrayList<User>();
        
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://127.0.0.1:3306/flowershop?userSSL=false&serverTimezone=Asia/Shanghai"; 
             String user = "root";
             String password = "Zhang13075099941";
             
             Connection conn = DriverManager.getConnection(url, user, password);
             
             String sql = "select * from user"; 
                Statement sts = conn.createStatement();
                ResultSet rs = sts.executeQuery(sql);
                
                while (rs.next()) {
                    User user1 = new User();
                    user1.setUserid(rs.getString("userid"));
                    user1.setUsername(rs.getString("username"));
                    user1.setPassword(rs.getString("password"));
                    user1.setAddress(rs.getString("address"));
                    user1.setPhone(rs.getString("phone"));
                   
                    users.add(user1);
                    
                    System.out.println(user1.getUserid()+" " +user1.getUsername()
                    +" "+user1.getPassword()+" "+user1.getAddress()+" "
                    +user1.getPhone());
                }

                rs.close();
                sts.close();
                conn.close();

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

            return users;
        
        
    }

    //查询用户
    @Override
    public User findById(String userid) {
        // TODO 自动生成的方法存根
        User user = new User();

        Connection conn ;
        PreparedStatement pstmt ;
        ResultSet rs ;
        try {
            conn = Jdbc.getConnection(); 
            String sql = "select userid, username, password, address,  phone from user where userid = ?"; 
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, userid);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                user = new User();
                user.setUserid(rs.getString("userid"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setAddress(rs.getString("address"));
                user.setPhone(rs.getString("phone"));
            } 

            rs.close();
            pstmt.close();
            conn.close();

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return user;
        }

    //创建
    @Override
    public int save(User user) {
        // TODO 自动生成的方法存根
        String sql = "insert into user (userid,password,username,address,phone) values (?,?,?,?,?,?,?)";

        try(    //2.创建数据库连接
                Connection conn = Jdbc.getConnection();
                //3.创建语句对象
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ){
            //4.绑定参数
            pstmt.setString(1, user.getUserid());
            pstmt.setString(2, user.getPassword());
            pstmt.setString(4, user.getUsername());
            pstmt.setString(5, user.getAddress());
            pstmt.setString(8, user.getPhone());

            //5.执行
            int a = pstmt.executeUpdate();
            System.out.printf("成功创建%d数据\n",a);

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }

        return 0;
    }
    
  //插入
    @Override
    public int create(User user) {

        String sql = "insert into user(userid,password,username,address,phone) values (?,?,?,?,?,?,?)";

        try(    //2.创建数据库连接
                Connection conn = Jdbc.getConnection();
                //3.创建语句对象
                PreparedStatement pstmt = conn.prepareStatement(sql);
        ){
            //4.绑定参数
        	 pstmt.setString(1, user.getUserid());
             pstmt.setString(2, user.getPassword());
             pstmt.setString(4, user.getUsername());
             pstmt.setString(5, user.getAddress());
             pstmt.setString(8, user.getPhone());


            //5.执行
            int a = pstmt.executeUpdate();
            System.out.printf("成功插入%d数据\n",a);

        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }

        return 0;
    }
    
//修改
    @Override
    public int modify(User user) {
    	String sql = "update user set password = ? where userid = ?";
		
		try ( // 2.创建数据库连接
				Connection conn = Jdbc.getConnection();
				// 3. 创建语句对象
				PreparedStatement pstmt= conn.prepareStatement(sql)
				) {
				// 4. 绑定参数
			
				pstmt.setString(1, user.getPassword());
				pstmt.setString(2, user.getUserid());

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

        return 0;
    }
//删除
    @Override
    public int remove(User user) {
    	String sql = "delete from user where userid = ?";
		
		try ( // 2.创建数据库连接
				Connection conn = Jdbc.getConnection();
				// 3. 创建语句对象
				PreparedStatement pstmt = conn.prepareStatement(sql)) {

				// 4. 绑定参数
				pstmt.setString(1, user.getUserid());
				
				// 5. 执行修改（C、U、D）
				int affectedRows = pstmt.executeUpdate();
				System.out.printf("成功删除%d条数据。\n", affectedRows);
				
			} catch (SQLException e) {
				e.printStackTrace();
			}
        return 0;
    }
    public static void main(String[] args) {
        UserDaoImpl user = new UserDaoImpl();
        for (User al : user.findAll()) {
            System.out.println(al);
        }

        System.out.println("-----------");
        System.out.println(user.findById(""));
       

    }

}