package com.company;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by ttc on 18-1-22.
 */
public class JDBCDemoFinal {

    //增删改


    public static Sort loc(String username,int password) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        Sort Sort = null;
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM Sort WHERE username = ? AND PASSWORD = ?");
        preparedStatement.setString(1, username);
        preparedStatement.setInt(2, password);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (resultSet.next()) {
            Sort = new Sort();
            Sort.setSid(resultSet.getInt("sid"));
            Sort.setSname(resultSet.getString("sname"));
            Sort.setBirthday(resultSet.getDate("birthday"));
            Sort.setMail(resultSet.getString("mail"));
            Sort.setUsername(resultSet.getString("username"));
            Sort.setPassword(resultSet.getInt("password"));
            Sort.setRole(resultSet.getInt("role"));


        }
        return Sort;
    }


      //修改
    public static void updateSort(Sort sort) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement =
                connection.prepareStatement("update sort set sname = ?, mail = ?, username = ?, birthday = ?,password=?,role = ? where sid = ?");
        preparedStatement.setString(1,sort.getSname());
        preparedStatement.setString(2,sort.getMail());
        preparedStatement.setString(3,sort.getUsername());
        preparedStatement.setInt(5,sort.getPassword());
        preparedStatement.setInt(6,sort.getRole());

        //java.util.date--->java.sql.Timestamp
        Date date = sort.getBirthday();
        //得到date对应的时间戳
        long timestamp = date.getTime();
        //通过时间戳创建java.sql.Timestamp
        java.sql.Timestamp sqlDate = new java.sql.Timestamp(timestamp);
        preparedStatement.setTimestamp(4,sqlDate);

        preparedStatement.setInt(7,sort.getSid());

        preparedStatement.executeUpdate();


    }


    //删除
    public static int deleteSortByID(int id) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("delete from sort where sid = ?");
        preparedStatement.setInt(1,id);

        int rows = preparedStatement.executeUpdate();
//        JDBCUtils.close(preparedStatement,connection);
        return rows;
    }

    //新增
    public static void AddSort(Sort sort) throws SQLException {
        Connection connection = JDBCUtils.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement("insert into sort(sname,mail,username,birthday,password,role) values(?,?,?,?,?,?)");
        preparedStatement.setString(1,sort.getSname());
        preparedStatement.setString(2,sort.getMail());
        preparedStatement.setString(3,sort.getUsername());
        preparedStatement.setInt(5,sort.getPassword());
        preparedStatement.setInt(6,sort.getRole());


        //java.util.date--->java.sql.Timestamp
        Date date = sort.getBirthday();
        //得到date对应的时间戳
        long timestamp = date.getTime();
        //通过时间戳创建java.sql.Timestamp
        java.sql.Timestamp sqlDate = new java.sql.Timestamp(timestamp);
        preparedStatement.setTimestamp(4,sqlDate);

        preparedStatement.executeUpdate();
//        JDBCUtils.close(preparedStatement,connection);

    }


    //查全部，查一个


    public static Sort getSortByID(int id) throws SQLException {
        Connection connection = JDBCUtils.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement("select * from sort where sid = ?");
        preparedStatement.setInt(1, id);

        ResultSet resultSet = preparedStatement.executeQuery();
        Sort sort = null;
        if (resultSet.next() == true) {
            sort = new Sort();
            int sid = resultSet.getInt("sid");
            String sname = resultSet.getString("sname");
            String username = resultSet.getString("username");
            Integer password = resultSet.getInt("password");
            Date birthday = resultSet.getTimestamp("birthday");
            String mail = resultSet.getString("mail");
            Integer role = resultSet.getInt("role");


            sort.setSid(sid);
            sort.setUsername(username);
            sort.setSname(sname);
            sort.setPassword(password);
            sort.setBirthday(birthday);
            sort.setMail(mail);
            sort.setRole(role);


        }
        return sort;
    }

    public static List<Sort> getSortBysname(String sname) throws SQLException {
        Connection connection = JDBCUtils.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement("select * from sort where sname LIKE ?");
        preparedStatement.setString(1,"%"+sname+"%");

        List<Sort> sortList = new ArrayList<>();
        ResultSet resultSet = preparedStatement.executeQuery();
        Sort sort = null;
        while(resultSet.next() == true)
        {
            int sid = resultSet.getInt("sid");
            String sname1 = resultSet.getString("sname");
            String username = resultSet.getString("username");
            Integer password = resultSet.getInt("password");
            Date birthday = resultSet.getTimestamp("birthday");
            String mail = resultSet.getString("mail");
            Integer role = resultSet.getInt("role");

            Sort sort1 = new Sort();
            sort1.setSid(sid);
            sort1.setUsername(username);
            sort1.setSname(sname1);
            sort1.setPassword(password);
            sort1.setBirthday(birthday);
            sort1.setMail(mail);
            sort1.setRole(role);

            sortList.add(sort1);
        }

//        JDBCUtils.close(resultSet,preparedStatement,connection);
        return sortList;

    }

    public static List<Sort> getAllSorts() throws SQLException {
        Connection connection = JDBCUtils.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement("select * from sort");
        List<Sort> sortList = new ArrayList<>();

        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next() == true)
        {
            int sid = resultSet.getInt("sid");
            String sname = resultSet.getString("sname");
            String username = resultSet.getString("username");
            Integer password = resultSet.getInt("password");
            Date birthday = resultSet.getTimestamp("birthday");
            String mail = resultSet.getString("mail");
            Integer role = resultSet.getInt("role");

            Sort sort = new Sort();
            sort.setSid(sid);
            sort.setUsername(username);
            sort.setSname(sname);
            sort.setPassword(password);
            sort.setBirthday(birthday);
            sort.setMail(mail);
            sort.setRole(role);

            sortList.add(sort);
        }
//        JDBCUtils.close(resultSet,preparedStatement,connection);
        return sortList;


    }

//    public static void main(String[] args) throws SQLException {
//        System.out.println("查一个");
//        Sort sort = getSortByID(11);
//        if(sort != null)
//        {
//            System.out.println(sort);
//        }
//        else
//        {
//            System.out.println("没查到");
//        }
//
//        System.out.println("查全部");
//        List<Sort> sortList = getAllSorts();
//        for(Sort sort1:sortList)
//        {
//            System.out.println(sort1);
//        }









}