概述
JDBC(Java Data Base Connectivity) 是一种用于执行SQL的Java API,可以为多种关系型数据库提供统一访问,它由一组Java语言编写的类和接口组成,是Java访问数据库的标准规范。
准备
- 创建MySQL表名worker,字段id, name, age, job
- pom.xml添加依赖
<dependencies> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.18</version> </dependency> </dependencies>
两种执行SQL的方法
package test;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
public class testMysql {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
// mysql8.0以上版本
String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
String URL = "jdbc:mysql://localhost:3306/test?user=root&password=123456&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
try{
Class.forName(JDBC_DRIVER); // 加载驱动
conn = DriverManager.getConnection(URL); // 建立连接
stmt = conn.createStatement(); // 创建statement对象,用来执行sql
String values = "(\"xiaozhao\", 21, \"develepment\")," +
"(\"xiaoqian\", 22, \"develepment\")," +
"(\"xiaosun\", 23, \"develepment\")," +
"(\"xiaoli\", 24, \"develepment\")";
stmt.execute("insert into worker (name, age, job) values " + values);
/**
// 通过PreparedStatement对象执行预编译 SQL语句
String sql = "select * from worker where id = ?";
PreparedStatement preparedStatement = conn.prepareStatement(sql);
preparedStatement.setString(1, "5"); // 设置参数:第一个参数表示第几个占位符,第二个参数表示具体值,注意参数类型和方法对应
rs = preparedStatement.executeQuery();
while(rs.next()){
String id = rs.getString("id");
String name = rs.getString("name");
Integer age = rs.getInt("age");
String job = rs.getString("job");
System.out.println(String.format("%s %s %d %s", id, name, age, job));
}
*/
}catch(ClassNotFoundException e){
System.out.println("加载驱动异常");
e.printStackTrace();
}catch(SQLException e){
System.out.println("数据库异常");
e.printStackTrace();
}finally{ // 关闭资源
System.out.println("执行结束");
try {
if (stmt != null) {
stmt.close();
}
}catch(SQLException e){
e.printStackTrace();
}
try{
if (conn != null){
conn.close();
}
}catch(SQLException e){
e.printStackTrace();
}
}
}
}