package com.liming.mysql;

import java.io.IOException;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.sql.*;

public class MysqlTest {
    public static void main(String[] args) throws IOException {
     //   System.out.println("Hello JDBC");
        Connection connection = null;
       // PreparedStatement preparedStatement = null;
        Statement st = null;
        ResultSet resultSet = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/mydb?" +
                            "serverTimezone=Asia/Shanghai","root","******");
      //      System.out.println(connection);

      //      Statement sqlStatement = connection.createStatement();

      //     ResultSet resultSet = sqlStatement.executeQuery("SELECT * FROM `user`");
      //      sqlStatement.executeUpdate("INSERT INTO `user`(username,password,regtime)VALUES('老八','000000',NOW());");

          /*  String sql = "SELECT * FROM `user` WHERE `id`>? and `id`!=?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,1);
            preparedStatement.setInt(2,4);

            resultSet = preparedStatement.executeQuery();

            while (resultSet.next()) {
                System.out.print(resultSet.getInt(1)+"-------");
                System.out.print(resultSet.getString(2)+"-------");
                System.out.print(resultSet.getString(3)+"-------");
                System.out.print(resultSet.getDate(4)+"\n");
            //    System.out.println();

            }*/

          connection.setAutoCommit(false);  //将自动提交设置为false
/*

            st = connection.createStatement();

            long start = System.currentTimeMillis();

            for (int i = 0; i < 20000; i++) {//添加批量处理SQL语句
                //将给定的SQL命令添加到此Statement对象的当前命令列表中。 该列表中的命令可以通过调用方法executeBatch作为批处理执行。
                st.addBatch("INSERT INTO `user` (username,password,regtime) VALUES ('linli"+i+"','888888',NOW());");
            }
            //确定执行此批处理语句
            st.executeBatch();
*/
            PreparedStatement ps1 = connection.prepareStatement("INSERT INTO `user`(username,password,regtime)VALUES('静香','444444',NOW());");
            ps1.executeUpdate();
            System.out.println("添加静香");
            try {
                Thread.sleep(5000);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
            PreparedStatement ps2 = connection.prepareStatement("INSERT INTO `user`(username,password,regtime)VALUES('小叮当','555555',NOW());");
            ps2.executeUpdate();
            System.out.println("添加小叮当");

            //提交SQL语句到数据库
            connection.commit();//手动提交

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
           try {
                connection.rollback();//异常回滚
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            //统一关闭资源
    //       Utils.autoCloseable(resultSet,preparedStatement,connection);
           Utils.autoCloseable(resultSet,st,connection);
        }
    }
}
class Utils {

    public static void autoCloseable(AutoCloseable... cs){
        for(AutoCloseable c : cs){
            try {
                if (c != null) {
                    c.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }

        }

    }
}