JDBC(Java Data Base Connectivity [ˌkɒnekˈtɪvəti])是一种用于执行 SQL 语句的 Java API,可以为多种关系数据库提供统一访问,它由一组用 Java 语言编写的类和接口组成。JDBC 提供了一种基准,据此可以构建更高级的工具和接口,使数据库开发人员能够编写数据库应用程序。
JDBC 为访问不同的数据库提供了一种统一的途径,为开发者屏蔽了一些细节问题。
JDBC 的目标是使 Java 程序员使用 JDBC 可以连接任何提供了 JDBC 驱动程序的数据库系统,这样就使得程序员无需对特定的数据库系统的特点有过多的了解,从而大大简化和加快了开发过程。
持久化(persistence [pəˈsɪstəns]):把数据保存到可掉电式存储设备中以供之后使用。大多数情况下,特别是企业级应用,数据持久化意味着将内存中的数据保存到硬盘上加以”固化”,而持久化的实现过程大多通过各种关系数据库来完成。 持久化的主要应用是将内存中的数据存储在关系型数据库中,当然也可以存储在磁盘文件、XML数据文件中。
驱动(driver [ˈdraɪvə(r)]):我们安装好数据库之后,我们的应用程序也是不能直接使用数据库的,必须要通过相应的数据库驱动程序,通过驱动程序去和数据库打交道。其实也就是数据库厂商的 JDBC 接口实现,即对 Connection [kəˈnekʃn]等接口的实现类的 jar 文件。
2.基本操作
前期准备
1、在 mysql 中创建一个库,并创建相应测试表和插入一些测试数据。
2、新建一个 Java 工程,并导入 MySQL 数据库驱动包,将 mysql 驱动拷入项目根目录并右键添加到项目依赖(Add As Library)。
JDBC 操作数据步骤一般分为如下五个步骤:
一、加载数据库驱动类。
Class.forName(driver)
二、建立连接(Connection)
Connection connection = DriverManager.getConnection(url,user,password);
三、创建用于向数据库发送 SQL 的 Statement 对象,并发送 sql
Statement statement = connection.createStatement();
四、从结果集的 ResultSet 中取出返回数据。
ResultSet resultSet = statement.excuteQuery(sql);
五、关闭相关资源,并释放内存
*.close();
案例:
import java.sql.*;
public class Jdbc01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false", "root", "123456");
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from student");
while (resultSet.next()) {
System.out.println(resultSet.getString(1));
}
resultSet.close();
statement.close();
connection.close();
}
}
常见驱动类(driver)的全路径一般如下,导入相应的驱动 jar 包后即可加载相应的驱动类:
mysql 驱动 com.mysql.jdbc.Driver
oracle 驱动 oracle.jdbc.driver.OracleDriver
sqlserver 驱动 com.microsoft.jdbc.sqlserver.SQLServerDriver
URL:
用于标识数据库的位置,一般由协议、主机、端口和数据库名构成,程序员通过 URL 地址指定 JDBC 程序连接的数据库信息,常见 URL 的写法如下:
Oracle— jdbc:oracle:thin:@localhost:1521:实例ID
SqlServer— jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=databaseName
MySql— jdbc:mysql://localhost:3306/databaseName
MySQL 连接的 url 格式如下,常用参数一般有 characterEncoding=utf-8&useSSL=false
注意:如果是连接本地,主机一般填写 127.0.0.1 或 localhost 和省略主机和端口。如果是连接服务器或虚拟机上的数据库服务则填写对应设备的 ip 地址和端口即可。
3.数据库常用对象
在使用 jdbc 过程中经常会使用到如下对象。
1.Connection [kəˈnekʃn]:
Jdbc 程序中的 Connection,它用于代表数据库的连接,Connection 是数据库编程中最重要的一个对象,客户端与数据库所有交互都是通过 Connection 对象完成的,这个对象的常用方法:
- createStatement():创建向数据库发送 sql 的 Statement 对象。
- prepareStatement(sql) :创建向数据库发送预编译 sql 的 PrepareSatement 对象。
- prepareCall(sql):创建执行存储过程的 callableStatement 对象。
- setAutoCommit(boolean autoCommit):设置事务是否自动提交。
- commit() :在链接上提交事务。
- rollback() :在此链接上回滚事务。
2.Statement [ˈsteɪtmənt]:
Jdbc 程序中的 Statement 对象用于向数据库发送 SQL 语句, Statement 对象常用方法:
- executeQuery(String sql) :用于向数据发送查询语句。
- executeUpdate(String sql):用于向数据库发送 insert、update 或 delete 语句
- execute(String sql):用于向数据库发送任意 sql 语句
- addBatch(String sql) :把多条 sql 语句放到一个批处理中。
- executeBatch():向数据库发送一批 sql 语句执行。
3.ResultSet [rɪˈzʌlt set]:
Jdbc 程序中的 ResultSet 用于代表 Sql 语句的执行结果。Resultset 封装执行结果时,采用的类似于表格的方式。ResultSet 对象维护了一个指向表格数据行的游标,初始的时候,游标在第一行之前,调用 resultSet.next() 方法,可以使游标指向具体的数据行,进行调用方法获取该行的数据。
ResultSet 提供检索不同类型字段的方法,常用的有:
- getString(int index)、getString(String columnName):通过索引或者字段名获得在数据库里是 varchar、char 等类型的数据对象。
- getFloat(int index)、getFloat(String columnName):通过索引或者字段名获得在数据库里是 Float 类型的数据对象。
- getDate(int index)、getDate(String columnName):通过索引或者字段名获得在数据库里是 Date 类型的数据。
- getBoolean(int index)、getBoolean(String columnName):通过索引或者字段名获得在数据库里是 Boolean 类型的数据。
- getObject(int index)、getObject(String columnName):通过索引或者字段名获取在数据库里任意类型的数据。
注意:ResultSet 的 index 是从 1 开始的,这与我们平时所写代码注意区分。
ResultSet 还提供了对结果集进行滚动的方法:
- next():移动到下一行
- previous():移动到前一行
- absolute(int row):移动到指定行
- beforeFirst():移动 resultSet 的最前面。
- afterLast() :移动到 resultSet 的最后面。
注意:释放资源,Jdbc 程序运行完后,切记要释放程序在运行过程中,创建的那些与数据库进行交互的对象,这些对象通常有 ResultSet,Statement 和 Connection 对象。
释放资源按照“后开先关”原则,顺序如下:ResultSet → Statement → Connection
为确保资源释放代码能正确运行,资源释放代码在使用 try-catch 时一定要放在 finally 语句中。
4.SQL 注入与 PreparedStatement
是用户利用某些系统没有对输入数据进行充分的检查,从而进行恶意破坏的行为。 statement 存在 sql 注入攻击问题,例如登陆用户名采用 ' or 1=1 # or 时无论密码是否正确都将登录成功。
import java.sql.*;
public class Jdbc01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
//String name = "Jack";
String name = "'or 1=1 #";
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false", "root", "123456");
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("select * from student where studentname='" + name + "' and studentage='20'");
//最终SQL变成下面这种
//SELECT * FROM student WHERE studentname=''or 1=1 #' and studentage='20'
while (resultSet.next()) {
System.out.println(resultSet.getString("studentname"));
}
resultSet.close();
statement.close();
connection.close();
}
}
SQL 注入的根本原因是 SQL 字符串的原始拼接。它可能会导致数据库被恶意破坏,这是任何开发都需要注意的问题。随着 jdbc 的发展,为防范 SQL 注入,可以采用 PreparedStatement [prɪˈpeəd][ˈsteɪtmənt] 取代 Statement。 PreperedStatement 是 Statement 的子类,它的实例对象可以通过调用 connection.preparedStatement() 方法获得,相对于 Statement 对象而言: PreperedStatement 可以避免 SQL 注入的问题。 Statement 会使数据库频繁编译 SQL,可能造成数据库缓冲区溢出。PreparedStatement 可对 SQL 进行预编译,从而提高数据库的执行效率。 并且 PreperedStatement 对于 SQL 中的参数,允许使用占位符的形式进行替换,简化 SQL 语句的编写。
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql:///my_test?useSSL=false", "root", "123456");
PreparedStatement statement = connection.prepareStatement("select * from `jdbc_test` where `name` = ? and `pwd` = ?");
statement.setObject(1,"admin");
statement.setObject(2,"123");
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
System.out.println(resultSet.getString(1) + "\t" + resultSet.getString(2) + "\t" + resultSet.getString(3));
}
resultSet.close();
statement.close();
connection.close();
注意:?不能作为字段名和表名的占位符,PreparedStatement 会为占位符 ? 的两边自动加上单引号,这样会使得 SQL 语句不可执行或找不到对应的表和字段,比如使用将表名设置为占位符,数据库执行 SQL 语句时,表名会用单引号引起来,这样会使得 SQL 语句执行出错或者查询不出数据。但这句代码在 SQL 中执行不会报错,但是查询没有结果。
5.数据库的 CRUD
CRUD 是增删改查的简写,分别对应:增加(Create [kriˈeɪt])、查询(Retrieve [rɪˈtriːv])、修改(Update [ˈʌpdeɪt])和删除(Delete [dɪˈliːt]),使用 jdbc 通常也就是为了完成数据库的增删改查。增删改三种操作对 jdbc 都是相同的语法执行 statement.executeUpdate() 方法,该方法返回数据库的影响行数。如果数据库的影响行数为 0 这说明修改失败。
增加:
private static boolean add(User user) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb?useSSL=false", "root", "123456");
PreparedStatement statement = connection.prepareStatement("insert into user (name,password,age) values (?,?,?)");
statement.setObject(1, user.getName());
statement.setObject(2, user.getPassword());
statement.setObject(3, user.getAge());
int i = statement.executeUpdate();//返回数据库影响行数
statement.close();
connection.close();
return i != 0;
}
查询:
private static List<User> query() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false", "root", "123456");
PreparedStatement statement = connection.prepareStatement("select * from user");
ResultSet resultSet = statement.executeQuery();
List<User> list = new ArrayList<>();
while (resultSet.next()) {
int id = resultSet.getInt("id");
int age = resultSet.getInt("age");
String name = resultSet.getString("name");
String password = resultSet.getString("password");
list.add(new User(id, name, password, age));
}
resultSet.close();
statement.close();
connection.close();
return list;
}
6.数据库分页
在 MySQL 中分页使用 limit 关键字实现:
Select * from table limit [M,]N ;
- M:记录开始索引位置(从 0 开始)
- N:取多少条记录
一般来说 M 和我们的 page 的关系为 M =(page-1)*size 换句话说,我们分页查询第 page 页,有 size 条数据时使用的 SQL 如下:
select * from user limit (page-1)*size,size
注意:limit 后面不能进行数值运算,所以我们需要在 java 代码中运算后填入,代码如下:
int page = 2;
int size = 10;
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false", "root", "123456");
PreparedStatement statement = connection.prepareStatement("select * from student limit ?,?");
statement.setObject(1, (page - 1) * size);
statement.setObject(2, size);
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
System.out.println(resultSet.getObject(2));
}
resultSet.close();
statement.close();
connection.close();
Oracle 中没有 limit 关键字,分页需要依赖行号(rownum)和子查询完成分页,SQL 如下:
select * from ( select rownum r_, row_.* from ( select * from student order by id ) row_ where rownum <=Y ) where r_>=X ;
- X:起始索引位置。
- Y:结束索引位置。
7.获取数据库自增主键
在实际开发中,我们经常需要获取插入数据的自增 id 针对这样的需求通常我们有两种解决办法。
1.插入完成后查询最大的 id 值:适用低并发的情况,如果数据库有多个线程同时插入时获取到的 id 将是不准确的。
2.使用 jdbc 中相应的 API 获取自增主键:适用全部情况。
获取数据库自增主键只能针对插入 SQL,用法如下:
String sql = "insert into user(name,password,email,birthday) values('abc','123','abc@sina.com','1978-08-08')";
PreparedStatement st = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
st.executeUpdate();
ResultSet rs = st.getGeneratedKeys(); //得到插入行的主键
if(rs.next())
System.out.println(rs.getObject(1));
}
8.调用存储过程
创建表和存储过程
create table p_user(
id int primary key auto_increment,
name varchar(10),
sex char(2)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into p_user(name,sex) values('A',"男");
insert into p_user(name,sex) values('B',"女");
insert into p_user(name,sex) values('C',"男");
创建存储过程(查询得到男性或女性的数量,如果传入的是0就女性否则是男性)
CREATE PROCEDURE ges_user_count(IN sex_id INT, OUT user_count INT)
BEGIN
IF sex_id=0 THEN
SELECT COUNT(*) FROM p_user WHERE p_user.sex='女' INTO user_count;
ELSE
SELECT COUNT(*) FROM p_user WHERE p_user.sex='男' INTO user_count;
END IF;
END
测试调用存储过程
SET @user_count = 0;
CALL ges_user_count(1, @user_count);
SELECT @user_count;
查询得到男性或女性的数量, 如果传入的是 0 就女性否则是男性
jdbc 调用存储过程
private static void t2() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useSSL=false", "root", "123456");
CallableStatement prepareCall = connection.prepareCall("{call ges_user_count(?, ?)}");
prepareCall.setInt(1, 1);
prepareCall.registerOutParameter(2, Types.INTEGER);
prepareCall.execute();
System.out.println(prepareCall.getString(2));
connection.close();
}