import java.math.BigDecimal;
import java.sql.*;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class JDBCConnect04 {
    public static void main(String[] args) {

    }
    //分页查询
    public static List<Student> selectStudentByPage(Integer page, Integer size, String orderBy) {
        Connection connection = null; //和数据库的连接
        PreparedStatement preparedStatement = null;//用于发送对数据库的请求，进行数据库的增删改查操作
        ResultSet resultSet = null;//用于获取返回的数据
        //注册驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");//使用什么驱动连接数据库

            //创建数据库连接
            String ur1 = "jdbc:mysql://localhost:3306/test_db_char?useUnicode=true&characterEncoding=UTF-8&userSSL=false&serverTimezone=GMT%2B8";
            //用户名和密码
            String user = "root";
            String password = "123456";
            connection = DriverManager.getConnection(ur1, user, password);
            String[] condition = orderBy.split("-");
            String sql = "SELECT * FROM tb_students_info ORDER BY" + condition[0] + " " + condition[1] + "LIMIT ?,?";
            page = page <= 1 ? 0 : (page - 1) * size;

            preparedStatement = connection.prepareStatement(sql);
            System.out.println(condition);
            preparedStatement.setInt(1, page);
            preparedStatement.setInt(2, size);
            resultSet = preparedStatement.executeQuery();
            List<Student> studentList = new ArrayList<>();
            while (resultSet.next()) {
                Long id = resultSet.getLong(1);
                String username = resultSet.getString(2);
                String passWord = resultSet.getString(3);
                String name = resultSet.getString(4);
                Long deptId = resultSet.getLong(5);
                Integer age = resultSet.getInt(6);
                Boolean sex = resultSet.getBoolean(7);
                Integer height = resultSet.getInt(8);
                BigDecimal money = resultSet.getBigDecimal(9);
                Date login_date = resultSet.getDate("login_date");
                //toInstant是将Date对象转换成Instant对象
                Instant instant = new java.util.Date(login_date.getTime()).toInstant();
                //atZone()是获取系统默认时区时间的方法，atOffset是设置偏移量，我们在东八区 加8个小时就是北京时间
                LocalDateTime localDateTime = instant.atZone(ZoneOffset.ofHours(8)).toLocalDateTime();
                Student student = new Student(id, name, password, name, deptId, age, sex, height, money, localDateTime);
                studentList.add(student);
            }
            return studentList;

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            if(resultSet !=null){
                try{
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(preparedStatement !=null){
                try{
                    preparedStatement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(connection !=null){
                try{
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return new ArrayList<>();
    }
    public static int countStudents(){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
            try {
                Class.forName("com.mysql.cj.jdbc.Driver");
                String ur1="jdbc:mysql://127.0.0.1:3306/test_db_char?useUnicode=true&characterEncoding=UTF-8&userSSL=false&serverTimezone=GMT%2B8";
                String username="root";
                String password="123456";
                connection =DriverManager.getConnection(ur1,username,password);
                String sql = "SELECT COUNT(*) FROM tb_students_info";
                preparedStatement = connection.prepareStatement(sql);
                resultSet = preparedStatement.executeQuery();
                while (resultSet.next()) {
                    return resultSet.getInt(1);
                }
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            finally {
                if (resultSet != null) {
                    try {
                        resultSet.close();
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                }
                if (preparedStatement != null) {
                    try {
                        preparedStatement.close();
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                }
                if (connection != null) {
                    try {
                        connection.close();
                    } catch (SQLException throwables) {
                        throwables.printStackTrace();
                    }
                }
            }
        return 0;


    }
}