一、JDBC介绍
JDBC (Java DataBase Contectivity) 是Sun提供的一套Api,通过Java语言和数据进行连接的API(Application Programma Interface)
JDBC 是Java语言(JDK)为完成数据库的访问操作提供的一套统一的标准
二、JDBC步骤
三、JDBC入门案例
JDBC 是用Java代码完成数据访问的规范
3.1 创建Java项目
3.1.1 创建包和类
3.2 加载驱动
3.2.1下载驱动jar包
对应数据库匹配版本的驱动jar包
- 地址https://mvnrepository.com/
- 搜索 mysql
- 选择版本下载
MySQL数据库版本为5.x,驱动jar包建议使用 5.1.47
MySQL数据库版本为8.x,驱动jar包建议使用 8.0.x
3.2.2 将驱动jar文件添加到Java应用
- 在java应用中创建lib文件夹
- 将下载好的jar文件拷贝—粘贴到lib目录
- 将驱动jar文件设置为java库:选择lib/驱动jar—右键—Add as Library… —OK
3.3 注册驱动
通过反射机制,将驱动jar文件中提供的驱动类载入到JVM中
// 1.注册驱动(驱动jar包中的驱动类(Driver)的路径:com.mysql.cj.jdbc.Driver)
Class.forName("com.mysql.cj.jdbc.Driver");
3.4 创建连接
//2.创建连接:通过数据库驱动管理器 DriverManager(java.sql.Driver)获取连接
// JDBC JDK提供的数据库连接的规范 --- java.sql
// java.sql.Connection接口 一个对象就表示一个数据库连接
// url 数据库的统一资源定位器(定位数据库连接资源)
// url参数
// characterEncoding 设置编码
// useSSL=true 使用SSL通信
// useTimezone=true 设置客户端和服务端时区转换
// 一个参数的写法(&表示多个参数的连接符):
// String url ="jdbc:mysql://localhost:3306/db_test2?user=root&password=root&characterEncoding=utf8";
String url = "jdbc:mysql://localhost:3306/db_test2";
Connection connection = DriverManager.getConnection(url,"root","password");
3.5 编写SQL指令
//3.编写要执行的SQL指令:SQL指令中需要的参数可以通过字符串拼接的形式(会导致SQL注入)
String sql = ("insert into books(book_name,book_author,book_price,book_stock,book_desc) " +
"values('%s','%s','%s','%d','%s')").formatted(name, author, price, stock, desc);
3.6 加载SQL指令
//4.加载SQL指令:获取SQL指令的加载器
// java.sql.Statement 对象 可以理解为 SQL指令的“加载器”
// java.sql.PreparedStatement 对象 SQL指令的“预编译加载器”
Statement statement = connection.createStatement();
3.7 执行SQL、获取结果
//5.执行SQL获取执行结果
// a. 如果SQL指令为DQL,则 ResultSet rs = statement.executeQuery(sql); rs中就是查询结果
// b. 如果SQL指令为DML,则 int i = statement.executeUpdate(sql); i就表示DML操作影响的数据行数
// 如果i>0,表示DML操作是成功的;如果i=0表示DML操作对数据表中的数据没有影响
int i = statement.executeUpdate(sql);
3.8 处理结果
//6.处理结果(业务)
// 添加操作:返回值i>0表示添加成功,否则表示添加失败
// 修改操作:返回值i>0表示修改对数据有影响,如果i=0 表示对数据库没有影响
// 删除操作:返回值i>0表示删除对数据有影响,如果i=0 表示对数据库没有影响
// 查询操作:从ResultSet rs中取出查询结果,封装到Java对象中
System.out.println(i>0?"添加成功":"添加失败");
3.9 关闭连接
//7.关闭连接
// 如果执行DML操作:需要关闭Statement和Connection
// 如果执行DQL操作:需要关闭ResultSet、Statement和Connection
// 关闭之前先判空、由小到大关闭
if(statement != null && !statement.isClosed()){
statement.close();
}
if(connection !=null && !connection.isClosed()){
connection.close();
}
四、JDBC增删查改操作实例
4.1 Insert(增)操作
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestInsertBook {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String name = "Python进阶之路";
String author = "老张";
double price = 25.22;
int stock = 14;
String desc = "值得推荐";
// 通过的JDBC将上述图书信息存储到MySQL db test.3、books表中
// 1.注册驱动(
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 创建连接
String url="jdbc:mysql://localhost:3306/db_test";
Connection connection=DriverManager.getConnection(url, "root", "123456");
// 3. 编写要执行的SQL指令
String sql = ("insert into books(book_name,book_author,book_price,book_stock,book_desc) " +
"values('%s','%s','%s','%d','%s')").formatted(name, author, price, stock, desc);
// 4. 加载SQL指令:获取SQL指令的加载器
Statement statement=connection.createStatement();
// 5. 执行SQL获取执行结果
int i=statement.executeUpdate(sql);
// 6. 处理结果(业务)
System.out.println(i>0?"添加成功":"添加失败");
// 7. 关闭连接
if(statement!=null&& !statement.isClosed()){
statement.close();
}
if(connection != null&& !connection.isClosed()){
connection.close();
}
}
}
4.2 Delete(删)操作
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDeleteBook {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
int bid = 3;
//使用JDBC,根据图书编号删除图书信息
//1.注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.创建连接
String url = "jdbc:mysql://localhost:3306/db_test";
Connection connection = DriverManager.getConnection(url, "root", "123456");
//3.编写SQL
String sql = "delete from books where book_id=%d".formatted(bid);
//4.获取SQL加载器
Statement statement = connection.createStatement();
//5.执行S0L
int i = statement.executeUpdate(sql);
//6.处理结果
System.out.println(i>0?"删除成功":"删除失败");
//7.关闭连接
if (!statement.isClosed()) {
statement.close();
}
if (!connection.isClosed()) {
connection.close();
}
}
}
4.3 Update(修改)操作
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class TestUpdateBook {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
int bid=4;
String name = "Android 实践教程";
String author = "老张";
double price = 99.6;
int stock = 14;
String desc = "值得推荐";
Class.forName("com.mysql.cj.jdbc.Driver");
String url="jdbc:mysql://localhost:3306/db_test?characterEncoding=utf-8";
Connection connection = DriverManager.getConnection(url, "root", "123456");
String sql= ("update books set book_name='%s',book_author='%s',book_price='%f',book_stock='%d'," +
"book_desc='%s' where book_id=%d").formatted(name, author, price, stock, desc,bid);
Statement statement = connection.createStatement();
int result=statement.executeUpdate(sql);
System.out.println(result>0?"更新成功":"更新失败");
if(!statement.isClosed()){
statement.close();
}
if(!connection.isClosed()){
connection.close();
}
}
}
4.4 Select(查询)操作
结果集处理
4.4.1 查询一条结果
import java.sql.*;
public class TestSelectBook {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
int bid=4;
// 根据图书编号查询一本书
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/db_test";
Connection connection= DriverManager.getConnection(url,"root","123456");
String sql="select book_id,book_name,book_author,book_price,book_stock,book_desc from books " +
"where book_id="+bid;
Statement statement = connection.createStatement();
//通过executeQuery.方法执行查询语句,并且将查询的结果存放到一个Resultset对象中(结果集,
ResultSet rs=statement.executeQuery(sql);
//处理结果:从rs中获取查询结果
if (rs.next()){
int bookId= rs.getInt("book_id");
String bookName = rs.getString("book_name");
String bookAuthor = rs.getString("book_author");
double bookPrice = rs.getDouble("book_price");
int bookStock = rs.getInt("book_stock");
String bookDesc = rs.getString("book_desc");
System.out.println(bookId+"\t"+bookName+"\t"+bookAuthor+"\t"+bookPrice+"\t"+bookStock+"\t"+bookDesc);
}
if(!rs.isClosed()){
rs.close();
}
if(!statement.isClosed()){
statement.close();
}
if (!connection.isClosed()) {
connection.close();
}
}
}
4.4.2 查询全部结果
import java.sql.*;
public class TestSelectBook {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 查询全部图书信息
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/db_test";
Connection connection= DriverManager.getConnection(url,"root","123456");
String sql="select book_id,book_name,book_author,book_price,book_stock,book_desc from books ";
Statement statement = connection.createStatement();
//通过executeQuery.方法执行查询语句,并且将查询的结果存放到一个Resultset对象中(结果集,
ResultSet rs=statement.executeQuery(sql);
//处理结果:从rs中获取查询结果
while (rs.next()){
int bookId= rs.getInt("book_id");
String bookName = rs.getString("book_name");
String bookAuthor = rs.getString("book_author");
double bookPrice = rs.getDouble("book_price");
int bookStock = rs.getInt("book_stock");
String bookDesc = rs.getString("book_desc");
System.out.println(bookId+"\t"+bookName+"\t"+bookAuthor+"\t"+bookPrice+"\t"+bookStock+"\t"+bookDesc);
}
if(!rs.isClosed()){
rs.close();
}
if(!statement.isClosed()){
statement.close();
}
if (!connection.isClosed()) {
connection.close();
}
}
}
五、JDBC的核心类与接口
- java.sql.Connection接口数据库连接
- java.sql.Statement接口SQL指令的“加载/执行器”
- java.sql.ResultSet接口结果集
5.1 DriverManage类
- 注册驱动
- 创建数据库链接
- 注册驱动
//在Oriver类中的静态初始化块中,注册驱动:DriverManager.registerDriver(new Driver());
Class.forName("com.mysql.cj.jdbc.Driver")
// 在我们的应用程序中手动注册驱动的代码也可以省略【Class.forName("com.mysql.cj.jdbc.Driver");】
// 如果我们没有手动注册驱动,驱动管理器在获取连接的时候发现没有注册驱动测读取驱动jr/ETA-
// INF/servicesjava,sql.Driver文件中配置的驱动类路径进行注册
- 获取链接
// ur1 数据库服务器的地址
// username 数据库连接用户名
// password 数据库连接密码
Connectionconnection DriverManager.getConnection(url,"root","123456");
5.2 Connection 接口
Connection对象表示Java应用程序与数据库之间的连接
- 通过Connection接口对象,获取执行SQL语句的Statementi对象
- 完成数据的事务管理
5.2.1 Connection接口
- Statement接口:编译执行静态SQL指令
Statement statement = connection.createStatement();
- Prepared Statement 接口:继承了Statement接口,预编译动态SQL指令 (解决SQL注入问题)
PrepareStatement preparestatement =connection.prepareStatement();
- CallableStatement接口:继承了PrepareStatement接口,可以调用存储过程
CallableStatement callablestatement = connection.prepareCall()
5.2.2 事务管理
//开启事务(关闭事务自动提交)
connection.setAutoCommit(false);
//事务回滚
connection.rollback();
//提交事务
connection.commit();
5.3 Statement接口
用于编译、执行接口
// 执行DML操作SQL指令
int i = statement.executeUpdate(sql);
// 执行DQL操作SQL指令
ResultSet rs = statement.executeQuery(sql);
5.4 ResultSet 接口
ResultSet接口对象,表示查询操作返回的结果集,提供了便利的方法用于获取结果集中的数据
- 判断结果集中是否还有数据未取出
RestultSet rs = statement.executeQuery(select_statement);
// 判断rs中是否还有数据
rs.next();
- 获取rs指向的结果集中某行的数据
int id = rs.getInt(String columnLable); // 获取rs指向行的列数据(字段名)
int id = rs.getInt(int columnIndex);
rs.getString("");
rs.getDouble();
...
rs.getDate(); //获取日期类型数据
六、SQL注入问题
6.1 什么是SQL注入问题
在JDBC操作SQL指令编写过程中,如果SQL指令中需要数据,我们可以通过字符串拼接的形式将参数拼接到SQL指令中,如String sq1=“delete from books where book_id=”+s;(s就是拼接到SQL中的变量)
使用字符串拼接变量的形式来设置SQL语句中的数据,可能会导致因变量值的改变引起$QL指令的原意发生改变,这就被称为SQL注入。SQL注入问题是需要避免的。例如:
- 如果s的值为1,SQL指令:delete from books where book id=1
- 如果s的值为1or1=1,SQL指令:delete from books where book_id=1or1=1
6.2 如何解决SQL 注入问题
使用PreparedStatementi进行SQL预编译解决SQL注入问题:
在编写SQL指令时,如果SQL指令中需要参数,一律使用?参数占位符
如果SQL指令中有 ?,在JDBC操作步骤中不再使用Statement,而是从Conection对象获取PreparedStatement对SQL指令进行预编译Preparedstatement preparedstatement=connection.preparestatement(sql);
预编译完成之后,通过PreparedStatementi对象给预编译后的SQL指令的~~?~~复制
- prepareadStatement.setInt(参数占位符序号,值);
- prepareadStatement.setString(参数占位符序号,值);
SQL指令中的所有~~?~~完成赋值之后,通过PreparedStatement执行SQL,执行SQL时不再加载SQL
- int i = prepareadStatement.executeUpdate();
- ResultSet .rs = preparedStatement.executeQuery();
6.3 使用PreparedStatementi预编译案例
6.3.1 删除操作
package jdbc;
import java.sql.*;
import java.util.Scanner;
public class TestDeleteBook {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//使用JDBC,根据图书编号删除图书信息
Scanner sc = new Scanner(System.in);
System.out.println("请输入你要删除图书的ID:");
String s= sc.nextLine();
int bid= Integer.parseInt(s);
//1.注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.创建连接
String url = "jdbc:mysql://localhost:3306/db_test";
Connection connection = DriverManager.getConnection(url, "root", "123456");
//3.编写SQL
String sql = "delete from books where book_id=?";
//4.如果SQL指令有参数占位符?,则从Connection获取PreparedStatement子预编译SQL指令
//预编译:在SQL指令中的参数赋值之前对SQL执行的语法结构进行编译
PreparedStatement preparedstatement = connection.prepareStatement(sql);
preparedstatement.setInt(1, bid);
//5.执行S0L
int i = preparedstatement.executeUpdate();
//6.处理结果
System.out.println(i>0?"删除成功":"删除失败");
//7.关闭连接
if (!preparedstatement.isClosed()) {
preparedstatement.close();
}
if (!connection.isClosed()) {
connection.close();
}
}
}
6.3.2 添加操作
package jdbc;
import com.mysql.cj.xdevapi.PreparableStatement;
import java.sql.*;
public class TestInsertBook {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
String name = "Python进阶之路";
String author = "老张";
double price = 25.22;
int stock = 14;
String desc = "值得推荐";
// 1.注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2. 创建连接
String url = "jdbc:mysql://localhost:3306/db_test";
Connection connection = DriverManager.getConnection(url, "root", "123456");
// 3. 编写要执行的SQL指令
String sql = "insert into books(book_name,book_author,book_price,book_stock,book_desc) " +
"values(?,?,?,?,?)";
// 4. 加载SQL指令:获取SQL指令的加载器
// SQL中有 ?使用PreparedStatement预编译SQL
PreparedStatement preparedStatement= connection.prepareStatement(sql);
//给SQL指令中的 ?复制
preparedStatement.setString(1,name);
preparedStatement.setString(2,author);
preparedStatement.setDouble(3,price);
preparedStatement.setInt(4,stock);
preparedStatement.setString(5,desc);
// 5. 执行SQL获取执行结果
int i = preparedStatement.executeUpdate();
// 6. 处理结果(业务)
System.out.println(i > 0 ? "添加成功" : "添加失败");
// 7. 关闭连接
if (!preparedStatement.isClosed()) {
preparedStatement.close();
}
if (!connection.isClosed()) {
connection.close();
}
}
}
6.3.3 修改操作
package jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TestUpdateBook_1 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
int bid = 6;
String name = "java实践教程";
String author = "李老头";
double price = 56.3;
int stock = 10;
String desc = "神作";
Class.forName("com.mysql.cj.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/db_test?characterEncoding=utf8";
Connection connection = DriverManager.getConnection(url, "root", "123456");
String sql = "update books set book_name=?,book_author=?,book_price=?,book_stock=?,book_desc=? where book_id=?";
PreparedStatement preparedstatement = connection.prepareStatement(sql);
preparedstatement.setString(1, name);
preparedstatement.setString(2, author);
preparedstatement.setDouble(3, price);
preparedstatement.setInt(4, stock);
preparedstatement.setString(5, desc);
preparedstatement.setInt(6, bid);
int i = preparedstatement.executeUpdate();
System.out.println(i > 0 ? "修改成功" : "修改失败");
if (!preparedstatement.isClosed()) {
preparedstatement.close();
}
if (!connection.isClosed()) {
connection.close();
}
}
}
注意:
- 如果SQL指令中需要参数,则使用PreparedStatement进行SQL预编译,
- 如果SQL指令中没有参数,建议使用Statement(Statement,执行效率要高于PreparedStatement)
6.3.4 根据ID查询图书信息
package jdbc;
import java.sql.*;
public class TestSelectBook_1 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
int bid=4;
// 查询一本图书信息
Class.forName("com.mysql.cj.jdbc.Driver"); // 动态加载mysql驱动类
String url = "jdbc:mysql://localhost:3306/db_test";
Connection connection = DriverManager.getConnection(url, "root", "123456");
String sql = "select book_id,book_name,book_author,book_price,book_stock,book_desc from books where book_id=? ";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, bid);
//通过executeQuery.方法执行查询语句,并且将查询的结果存放到一个Resultset对象中(结果集,
ResultSet rs = preparedStatement.executeQuery();
//处理结果:从rs中获取查询结果
while (rs.next()) {
int bookId = rs.getInt("book_id");
String bookName = rs.getString("book_name");
String bookAuthor = rs.getString("book_author");
double bookPrice = rs.getDouble("book_price");
int bookStock = rs.getInt("book_stock");
String bookDesc = rs.getString("book_desc");
System.out.println(bookId + "\t" + bookName + "\t" + bookAuthor + "\t" + bookPrice + "\t" + bookStock + "\t" + bookDesc);
}
if (!rs.isClosed()) {
rs.close();
}
if (!preparedStatement.isClosed()) {
preparedStatement.close();
}
if (!connection.isClosed()) {
connection.close();
}
}
}
6.3.5查询所有图书信息
package jdbc;
import java.sql.*;
public class TestSelectBook {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 所有图书信息
Class.forName("com.mysql.cj.jdbc.Driver"); // 动态加载mysql驱动类
String url = "jdbc:mysql://localhost:3306/db_test";
Connection connection= DriverManager.getConnection(url,"root","123456");
String sql="select book_id,book_name,book_author,book_price,book_stock,book_desc from books ";
PreparedStatement preparedStatement=connection.prepareStatement(sql);
//通过executeQuery.方法执行查询语句,并且将查询的结果存放到一个Resultset对象中(结果集,
ResultSet rs=preparedStatement.executeQuery(sql);
//处理结果:从rs中获取查询结果
while (rs.next()){
int bookId= rs.getInt("book_id");
String bookName = rs.getString("book_name");
String bookAuthor = rs.getString("book_author");
double bookPrice = rs.getDouble("book_price");
int bookStock = rs.getInt("book_stock");
String bookDesc = rs.getString("book_desc");
System.out.println(bookId+"\t"+bookName+"\t"+bookAuthor+"\t"+bookPrice+"\t"+bookStock+"\t"+bookDesc);
}
if(!rs.isClosed()){
rs.close();
}
if(!preparedStatement.isClosed()){
preparedStatement.close();
}
if (!connection.isClosed()) {
connection.close();
}
}
}
七、工具类封装
7.1 代码的复用性
在我们的应用程序中,如果需要完成相同的操作,相同的代码无需重复编写,我们只需一次编写多次调用即可!
JDBC数据库编程是由一个固定的步骤:
- 注册驱动
- 创建连接
- 编写SQL
- 获取Statement对象
- 执行SQL
- 处理结果
- 关闭连接
7.2 工具类封装
DBManager
DBUtil
JDBCUtil
DBHelper
7.3 封装案例
package jdbcutil;
import java.sql.*;
public class DB_Util {
/**
* 返回数据库连接对象
*/
public static Connection getConnection() {
Connection connection = null;
try {
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/db_test?characterEncoding=ufg8)";
connection = DriverManager.getConnection(url, "root", "123456");
} catch (SQLException e) {
System.out.println("~~~~~~~~~~~~数据库连接失败~~~~~~~~~~~~~~~");
} catch (ClassNotFoundException e) {
System.out.println("~~~~~~~~~~~~数据库驱动加载失败~~~~~~~~~~~~");
}
return connection;
}
/**
* 关闭连接
* 多态的应用:使用Statement接口做参数,既可以传递Statement接口对象,
* 也可以传递PreparedStatement接口对象
*/
public static void close(Statement statement, Connection connection) {
try {
if (!statement.isClosed()) {
statement.close();
}
if (!connection.isClosed()) {
connection.close();
}
} catch (SQLException e) {
System.out.println("~~~~~~~~~数据库关闭失败~~~~~~~~~~");
}
}
/**
* 方法重载
*/
public static void close(PreparedStatement preparedStatement, Connection connection) {
try {
if (!preparedStatement.isClosed()) {
preparedStatement.close();
}
if (!connection.isClosed()) {
connection.close();
}
} catch (SQLException e) {
System.out.println("~~~~~~~~~数据库关闭失败~~~~~~~~~~");
}
}
public static void close(ResultSet resultSet, PreparedStatement preparedStatement, Connection connection) {
try {
if (!resultSet.isClosed()) {
resultSet.close();
}
if (!preparedStatement.isClosed()) {
preparedStatement.close();
}
if (!connection.isClosed()) {
connection.close();
}
} catch (SQLException e) {
System.out.println("~~~~~~~~~数据库关闭失败~~~~~~~~~~");
}
}
public static void close(ResultSet resultSet, Statement Statement, Connection connection) {
try {
if (!resultSet.isClosed()) {
resultSet.close();
}
if (!Statement.isClosed()) {
Statement.close();
}
if (!connection.isClosed()) {
connection.close();
}
} catch (SQLException e) {
System.out.println("~~~~~~~~~数据库关闭失败~~~~~~~~~~");
}
}
}