package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import entity.UserInfo;
import util.DBConnection;

//DAO:Data Access Object
//完成对表userinfo的增删改查（CURD）功能
public class UserInfoDAO {
    // 查询全部
    public List<UserInfo> selectAll() throws SQLException {
        List<UserInfo> users = new ArrayList<UserInfo>();
        String sql = "select * from  userinfo";

        // 1. 获取数据库连接
        Connection connection = DBConnection.getConnection();

        // 2. 创建Statement，执行SQL语句
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery(sql);

        // 3. 处理结果集
        while (rs.next()) {
            UserInfo user = new UserInfo();
            user.setName(rs.getString("name"));
            user.setPassword(rs.getString("password"));
            user.setAge(rs.getInt("age"));
            user.setSex(rs.getString("sex"));
            user.setBirthday(new Date(rs.getDate("birthday").getTime()));

            users.add(user);
        }
        // 4. 释放资源
        rs.close();
        stmt.close();
        connection.close();
        return users;
    }

    public UserInfo selectByName(String name) throws SQLException {

        String sql = "select * from userinfo where name=‘" + name + "‘";

        // 1. 获取数据库连接
        Connection connection = DBConnection.getConnection();

        // 2. 创建Statement，执行SQL语句
        Statement stmt = connection.createStatement();
        ResultSet rs = stmt.executeQuery(sql);

        // 3. 处理结果集\
        UserInfo user = null;
        if (rs.next()) {
            user = new UserInfo();
            user.setId(rs.getInt("userid"));
            user.setName(rs.getString("name"));
            user.setPassword(rs.getString("password"));
            user.setAge(rs.getInt("age"));
            user.setSex(rs.getString("sex"));
            user.setBirthday(rs.getDate("birthday"));

        }
        // 4. 释放资源
        rs.close();
        stmt.close();
        connection.close();
        return user;
    }

    // 按条件查询
    public List<UserInfo> selectBySex(String sex) throws SQLException {
        List<UserInfo> users = new ArrayList<UserInfo>();
        String sql = "SELECT * FROM userinfo WHERE sex=?";

        // 1. 获取数据库连接
        Connection connection = DBConnection.getConnection();

        // 2. 创建Statement，执行SQL语句
        PreparedStatement pstmt = connection.prepareStatement(sql);
        pstmt.setString(1, sex);
        ResultSet rs = pstmt.executeQuery(sql);

        // 3. 处理结果集
        while (rs.next()) {
            UserInfo user = new UserInfo();
            user.setName(rs.getString("name"));
            user.setPassword(rs.getString("password"));
            user.setAge(rs.getInt("age"));
            user.setSex(rs.getString("sex"));
            user.setBirthday(new Date(rs.getDate("birthday").getTime()));

            users.add(user);
        }
        // 4. 释放资源
        rs.close();
        pstmt.close();
        connection.close();
        return users;
    }

    // 增加
    public int insert(UserInfo user) throws SQLException {
        String sql = "insert into userinfo(name,password,age,sex,birthday) values(?,?,?,?,?)";
        // 1. 获取数据库连接
        Connection connection = DBConnection.getConnection();

        // 2. 创建PreparedStatement
        PreparedStatement pstmt = connection.prepareStatement(sql);

        // 3. 给PreparedStatement的参数赋值
        pstmt.setString(1, user.getName());
        pstmt.setString(2, user.getPassword());
        pstmt.setInt(3, user.getAge());
        pstmt.setString(4, user.getSex());
        pstmt.setDate(5, new java.sql.Date(user.getBirthday().getTime()));

        // 4. 执行SQL语句
        int num = pstmt.executeUpdate();

        // 5. 释放资源
        pstmt.close();
        connection.close();
        return num;
    }

    // 修改密码
    public int update(String name, String password) throws SQLException {
        String sql = "update userinfo set password=? where name=?";
        // 1. 获取数据库连接
        Connection connection = DBConnection.getConnection();

        // 2. 创建PreparedStatement
        PreparedStatement pstmt = connection.prepareStatement(sql);

        // 3. 给PreparedStatement的参数赋值
        pstmt.setString(1, password);
        pstmt.setString(2, name);

        // 4. 执行SQL语句
        int num = pstmt.executeUpdate();

        // 5. 释放资源
        pstmt.close();
        connection.close();
        return num;
    }

    public int delete(String name) throws SQLException {
        String sql = "delete from userinfo where name=?";
        // 1. 获取数据库连接
        Connection connection = DBConnection.getConnection();

        // 2. 创建PreparedStatement
        PreparedStatement pstmt = connection.prepareStatement(sql);

        // 3. 给PreparedStatement的参数赋值
        pstmt.setString(1, name);

        // 4. 执行SQL语句
        int num = pstmt.executeUpdate();

        // 5. 释放资源
        pstmt.close();
        connection.close();
        return num;
    }

    public static void main(String[] args) {
        UserInfoDAO dao = new UserInfoDAO();
        try {
            List<UserInfo> users = dao.selectAll();
            System.out.println(users);
            users = dao.selectBySex("男");
            System.out.println(users);

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            Date date = sdf.parse("1998-01-01");
            UserInfo user = new UserInfo(3, "niit", "123456", 18, "男", date);
            int num = dao.insert(user);
            if (num >= 0) {
                System.out.println("插入成功");
            }
        } catch (SQLException | ParseException e) {
            e.printStackTrace();
        }
    }
}