一、概念
二、JDBC的意义
JDBC可以做些什么?
- 连接到数据库
- 在Java app中执行SQL命令
- 处理结果。
三、关于JDBC的五种重要接口
四、代码示例
|项目|:一个定义类,一个接口,一个实现接口类,一个测试类
package com.etc;
import java.sql.*;
/**
* 数据库连接的封装
*/
public class DbUtil {
// JDBC 驱动名及数据库 URL
public static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
public static final String URL = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC";
// 数据库的用户名与密码,需要根据自己的设置
public static final String USER = "root";
public static final String PASSWORD = "root";
private static Connection conn = null;
private DbUtil(){}
public static Connection getConnection(){
if (conn == null){
try {
//1.加载驱动程序
Class.forName(JDBC_DRIVER);
//2. 获得数据库连接
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
}
return conn;
}
//关闭连接对象
public static void closeConnection(Connection con){
if (con != null){
try {
con.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//关闭处理语句对象
public static void closeStatement(Statement stt){
if (stt != null){
try {
stt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
//关闭预处理语句对象
public static void closepp(PreparedStatement pst){
if (pst != null){
try {
pst.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
复制代码
JDBC 用户测试类
public class MainTest {
//获取连接
Connection conn = DbUtil.getConnection();
复制代码
查询
@Test
public void getUser() throws SQLException {
String sql = "SELECT id2, username FROM `user` ";
Statement stm = conn.createStatement();
ResultSet rs = stm.executeQuery(sql);
// 展开结果集数据库
while (rs.next()) {
// 通过字段检索
int id = rs.getInt("id2");
String name = rs.getString("username");
// 输出数据
System.out.print("ID: " + id);
System.out.print(", 名称: " + name);
System.out.print("\n");
}
DbUtil.closeStatement(stm);
DbUtil.closeConnection(conn);
System.out.println("Goodbye!");
}
复制代码
删除
@Test
public void delectUser() {
try {
String sql2 = "DELETE from user where id = ?";
PreparedStatement ps = conn.prepareStatement(sql2);
ps.setString(1, "2");
int i = ps.executeUpdate();
if (i == 1) {
System.out.println("删除成功");
} else {
System.out.println("删除失败");
}
DbUtil.closePreparedStatement(ps);
DbUtil.closeConnection(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
复制代码
添加
@Test
public void installUser() {
//添加
try {
String sql1 = "INSERT INTO user(username,password) VALUES(?,?);";
PreparedStatement ps = conn.prepareStatement(sql1);
ps.setString(1, "kalista");
ps.setString(2, "123456");
int result = ps.executeUpdate();
if (result == 1) {
System.out.println("添加成功");
} else {
System.out.println("添加失败");
}
DbUtil.closePreparedStatement(ps);
DbUtil.closeConnection(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
复制代码
更新
@Test
public void updateUser() {
try {
String sql3 = "UPDATE user set username = ? WHERE id = ?";
PreparedStatement ps = null;
ps = conn.prepareStatement(sql3);
ps.setString(1, "妞妞");
ps.setString(2, "3");
int result = ps.executeUpdate();
if (result == 1) {
System.out.println("更新成功");
} else {
System.out.println("更新失败");
}
DbUtil.closePreparedStatement(ps);
DbUtil.closeConnection(conn);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
复制代码
jdbc处理事务的方法:
java.sql.Connection.setAutoCommit(boolean) 默认为true,所有的SQL执行完之后自动提交事务
java.sql.Connection.commit() 手动提交事务
java.sql.Connection.rollback() 回滚事务