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"); //

                System.out.print(md.getColumnName(i) + "\t"); //name

                System.out.print(md.getColumnType(i) + "\t"); //

                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