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 int updateSort(Sort sort) throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        PreparedStatement preparedStatement = 
                connection.prepareStatement("update sort set sname = ?, sprice = ?, sdesc = ?, sdate = ? where sid = ?");
        preparedStatement.setString(1,sort.getSname());
        preparedStatement.setDouble(2,sort.getSprice());
        preparedStatement.setString(3,sort.getSdesc());

        //java.util.date--->java.sql.Timestamp
        Date date = sort.getSdate();
        //得到date對應(yīng)的時(shí)間戳
        long timestamp = date.getTime();
        //通過時(shí)間戳創(chuàng)建java.sql.Timestamp
        java.sql.Timestamp sqlDate = new java.sql.Timestamp(timestamp);
        preparedStatement.setTimestamp(4,sqlDate);

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

        int rows = preparedStatement.executeUpdate();
        return rows;

    }


    //刪除
    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 int AddSort(Sort sort) throws SQLException {
        Connection connection = JDBCUtils.getConnection();

        PreparedStatement preparedStatement = connection.prepareStatement("insert into sort(sname,sprice,sdesc,sdate) values(?,?,?,?)");
        preparedStatement.setString(1,sort.getSname());
        preparedStatement.setDouble(2,sort.getSprice());
        preparedStatement.setString(3,sort.getSdesc());

        //java.util.date--->java.sql.Timestamp
        Date date = sort.getSdate();
        //得到date對應(yīng)的時(shí)間戳
        long timestamp = date.getTime();
        //通過時(shí)間戳創(chuàng)建java.sql.Timestamp
        java.sql.Timestamp sqlDate = new java.sql.Timestamp(timestamp);
        preparedStatement.setTimestamp(4,sqlDate);

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


    //查全部，查一個(gè)


    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");
            double dPrice = resultSet.getDouble("sprice");
            String sdesc = resultSet.getString("sdesc");
            Date date = resultSet.getTimestamp("sdate");

            sort.setSid(sid);
            sort.setSprice(dPrice);
            sort.setSname(sname);
            sort.setSdesc(sdesc);
            sort.setSdate(date);

        }

        JDBCUtils.close(resultSet,preparedStatement,connection);
        return sort;

    }

    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");
            double dPrice = resultSet.getDouble("sprice");
            String sdesc = resultSet.getString("sdesc");
            Date date = resultSet.getTimestamp("sdate");

            Sort sort = new Sort();
            sort.setSid(sid);
            sort.setSprice(dPrice);
            sort.setSname(sname);
            sort.setSdesc(sdesc);
            sort.setSdate(date);

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


    }

    public static void main(String[] args) throws SQLException {
        System.out.println("查一個(gè)");
        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);
        }

        System.out.println("刪除");
        //刪除
        deleteSortByID(8);
        System.out.println("插入");

        Sort sort1 = new Sort();
        sort1.setSname("小飾品");
        sort1.setSprice(5600);
        sort1.setSdesc("好看");
        Date date = new Date();
        sort1.setSdate(date);
        AddSort(sort1);

        System.out.println("修改");
        Sort sort2 = new Sort();
        sort2.setSname("汽車");
        sort2.setSprice(5600);
        sort2.setSdesc("好看");
        Date date2 = new Date();
        sort2.setSdate(date2);
        sort2.setSid(13);





    }
}