一、引用库
maven库：mysql:mysql-connector-java:6.0.6
二、SDK环境
JAVA JDK10
三、测试代码
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
