JDBC
以mysql为例,可选择mysql8.x与mysql5.7
数据库地址:127.0.0.1
mysql端口:3306
用户名:root
密码:123456
测试数据库:test
一、导入mysql连接驱动
当前以maven为例工程为例
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.30</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.24</version>
<scope>provided</scope>
</dependency>
二 、JDBC常规步骤
基本常量
/**
* 驱动类
* 建议使用8.x版本的驱动,因为8.x完全兼容5.7
* 8.x
*/
public static final String DRIVER = "com.mysql.cj.jdbc.Driver";
//public static final String DRIVER = "com.mysql.jdbc.Driver"; //5.x
/**
* 连接路径
*/
public static final String URL = "jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8";
/**
* 用户名
*/
public static final String USER = "root";
/**
* 密码
*/
public static final String PWD = "123456";
/**
* 连接对象
*/
public static Connection conn = null;
1.加载驱动
static {
try {
//加载对象
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
}
2.获得连接对象
public static Connection getConnection() {
try {
conn = DriverManager.getConnection(URL, USER, PWD);
} catch (SQLException e) {
throw new RuntimeException(e);
}
return conn;
}
3.关闭连接处理
public static void closeAll(ResultSet rs, PreparedStatement ps, Connection conn) {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
4.完整DBUtil.java
public class DBUtil {
/**
* 驱动类
* 8.x
*/
public static final String DRIVER = "com.mysql.cj.jdbc.Driver";
//public static final String DRIVER = "com.mysql.jdbc.Driver"; //5.x
/**
* 连接路径
*/
public static final String URL = "jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8";
/**
* 用户名
*/
public static final String USER = "root";
/**
* 密码
*/
public static final String PWD = "123456";
/**
* 连接对象
*/
public static Connection conn = null;
static {
try {
//加载对象
Class.forName(DRIVER);
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
}
public static Connection getConnection() {
try {
conn = DriverManager.getConnection(URL, USER, PWD);
} catch (SQLException e) {
throw new RuntimeException(e);
}
return conn;
}
public static void closeAll(ResultSet rs, PreparedStatement ps, Connection conn) {
try {
if (rs != null) {
rs.close();
}
if (ps != null) {
ps.close();
}
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
5.创建数据库表student进行测试
建表SQL
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`stu_id` int(11) NOT NULL,
`stu_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`stu_age` int(11) NULL DEFAULT NULL,
`stu_sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`stu_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '小红', 18, '女');
INSERT INTO `student` VALUES (2, '小明', 20, '男');
SET FOREIGN_KEY_CHECKS = 1;
实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private int stuId;
private String stuName;
private int stuAge;
private String suSex;
}
6.执行SQL查询,获得数据集
List<Student> students = new ArrayList<>();
Connection conn = DBUtil.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
String sql = "SELECT * FROM student";
try {
//执行sql
ps = conn.prepareStatement(sql);
// 获得结果集
rs = ps.executeQuery();
while (rs.next()) {
// 数据封装
Student student = new Student();
student.setStuId(rs.getInt("stu_id"));
student.setStuAge(rs.getInt("stu_age"));
student.setStuName(rs.getString("stu_name"));
student.setSuSex(rs.getString("stu_sex"));
students.add(student);
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
DBUtil.closeAll(rs, ps, conn);
}
System.out.println(students);
结果
[Student(stuId=1, stuName=小红, stuAge=18, suSex=女), Student(stuId=2, stuName=小明, stuAge=20, suSex=男)]
7.带参数查询
Student student = null;
Connection conn = DBUtil.getConnection();
String sql = "SELECT * FROM student WHERE stu_id = ?";
ResultSet rs = null;
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(sql);
ps.setInt(1, 1);
rs = ps.executeQuery();
while (rs.next()) {
student = new Student(rs.getInt("stu_id"),
rs.getString("stu_name"),
rs.getInt("stu_age"),
rs.getString("stu_sex"));
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
DBUtil.closeAll(rs, ps, conn);
}
System.out.println(student);
结果
Student(stuId=1, stuName=小红, stuAge=18, suSex=女)