mysql测试jdbc连接数据库_JAVA中JDBC连接Mysql数据库简单测试

packagecom.mysql.mysqlconnector;import java.sql.*;public classDbMain {public static void main(String[] args) throwsClassNotFoundException {

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());

}

}

}