package com.mysql.mysqlconnector;

import java.sql.*;

public class DbMain {
    public static void main(String[] args) throws ClassNotFoundException {
        System.out.println("Data base connection test:");
        Connection connection;
        var driver = "com.mysql.cj.jdbc.Driver";
        var url = "jdbc:mysql://127.0.0.1:3306/testdb?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai"; //注意，新版要求必须加上serverTimezone
        var user = "root";
        var password = "password";
        try {
            Class.forName(driver);
            var conn = DriverManager.getConnection(url, user, password);
            if (!conn.isClosed())
                System.out.println(String.format("conection to %s successfully.", url));
            //statement   执行不能使用参数的SQL
            Statement statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT * FROM student WHERE  RowId<999 ");
            ResultSetMetaData md = resultSet.getMetaData();
            var columnCount = md.getColumnCount();
            for (var i = 1; i <= columnCount; i++) {//注意起始数字
                System.out.println("---------------------");
                System.out.print(md.getColumnClassName(i) + "\t"); //java.lang.String
                System.out.print(md.getColumnDisplaySize(i) + "\t"); //255
                System.out.print(md.getColumnName(i) + "\t"); //name
                System.out.print(md.getColumnType(i) + "\t"); //12
                System.out.print(md.getColumnTypeName(i) + "\t"); //VARCHAR
                System.out.println(md.getColumnClassName(i) + "\t"); //java.lang.String
            }
            while (resultSet.next()) {
                for (var i = 1; i <= columnCount; i++) {
                    System.out.print(resultSet.getObject(i));
                    System.out.print("\t");
                }
                System.out.println();
            }
            statement.close();
            // prepareStatement
            var prepareStatement = conn.prepareStatement("SELECT * FROM student WHERE RowId<?");
            prepareStatement.setInt(1, 999);//注意这里，JDBC中的ColumnIndex是从1开始的。
            ResultSet results = prepareStatement.executeQuery();
            while (results.next()) {
                var rowId = results.getInt(1); //注意这里，JDBC中的ColumnIndex是从1开始的。
                var name = results.getString(2);
                var age = results.getInt(3);
                System.out.println(rowId + "\t" + name + "\t" + age);
            }
            prepareStatement.close();
            // insert
            var insertPrepareStatement = conn.prepareStatement("INSERT  INTO student(name,age) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS);
            insertPrepareStatement.setString(1, "Sindrol");
            insertPrepareStatement.setInt(2, 28);

            if (insertPrepareStatement.executeUpdate() > 0) {
                ResultSet gk = insertPrepareStatement.getGeneratedKeys();
                if (gk.next())
                    System.out.println("Insert into success. primary key RowId =" + gk.getInt("GENERATED_KEY"));
            }
            insertPrepareStatement.close();

            //batch commit.
            conn.setAutoCommit(false);//先停止自动提交。
            var batchPrepareStatement = conn.prepareStatement("INSERT INTO student(name,age) VALUES (?,?)", Statement.RETURN_GENERATED_KEYS);
            for (int i = 0; i < 5; i++) {
                batchPrepareStatement.setString(1, "Lingling_" + i);
                batchPrepareStatement.setInt(2, (20 + i));
                batchPrepareStatement.addBatch();
            }
            try {
                var intArrs = batchPrepareStatement.executeBatch();
                for (var eff : intArrs)
                    System.out.println("eff:" + eff);
                conn.commit();

batchPrepareStatement.clearBatch();//提交后，清空Batch。
                var keys = batchPrepareStatement.getGeneratedKeys();
                while (keys.next()) {
                    int rowId = keys.getInt("GENERATED_KEY");
                    System.out.println("rowid:" + rowId);
                }
            } catch (Exception ex) {
                System.out.println("commit failed and will rollback:" + ex.getMessage());
                conn.rollback();
            }finally {
                batchPrepareStatement.close();
            }


            if (!conn.isClosed()) conn.close();
        } catch (Exception ex) {
            System.out.println("SQL操作异常：" + ex.getMessage());
        }
    }
}

"C:\Program Files\Java\jdk-10\bin\java" -javaagent:D:\Applicaton\DevTools\JetBrains\IntelliJIdea\lib\idea_rt.jar=33464:D:\Applicaton\DevTools\JetBrains\IntelliJIdea\bin -Dfile.encoding=UTF-8 -classpath D:\Project\JavaProject\JavaDemo\out\production\JavaDemo;C:\Users\yungoal\.m2\repository\com\alibaba\fastjson\1.2.16\fastjson-1.2.16.jar;C:\Users\yungoal\.m2\repository\mysql\mysql-connector-java\6.0.6\mysql-connector-java-6.0.6.jar com.mysql.mysqlconnector.DbMain
Data base connection test:
conection to jdbc:mysql://127.0.0.1:3306/testdb?characterEncoding=utf8&useUnicode=true&useSSL=false&serverTimezone=Asia/Shanghai successfully.
---------------------
java.lang.Integer    11    rowid    4    INT    java.lang.Integer    
---------------------
java.lang.String    255    name    12    VARCHAR    java.lang.String    
---------------------
java.lang.Integer    11    age    4    INT    java.lang.Integer    
1    songxingzhu    27    
2    wanghaixing    28    
3    Sindrol    28    
4    Sindrol    28    
5    Sindrol    28    
6    Sindrol    28    
7    Sindrol    28    
8    Sindrol    28    
9    Lingling_0    20    
10    Lingling_1    21    
11    Lingling_2    22    
12    Lingling_3    23    
13    Lingling_4    24    
1    songxingzhu    27
2    wanghaixing    28
3    Sindrol    28
4    Sindrol    28
5    Sindrol    28
6    Sindrol    28
7    Sindrol    28
8    Sindrol    28
9    Lingling_0    20
10    Lingling_1    21
11    Lingling_2    22
12    Lingling_3    23
13    Lingling_4    24
Insert into success. primary key RowId =14
eff:1
eff:1
eff:1
eff:1
eff:1
rowid:15
rowid:16
rowid:17
rowid:18
rowid:19

Process finished with exit code 0