JDBC操作全攻略
JDBC简介
JDBC(Java Database Connectivity),也称为Java数据库连接,是Java用于连接数据库的接口规范,需要注意的是,JDBC并没有提供的实现,具体的实现是由数据库提供商,比如MySQL,Oracle等负责提供,JDBC提供了一整套完整的连接规范,包括了Driver
,DriverManager
,Connection
,Statement
,PreparedStatement
,ResultSet
,DatabaseMetaData
,ResultSetMetaData
,ParameterMetaData
等等
连接数据库
在Java程序中,操作数据库时,通常需要几个步骤
1. 导入对应的JDBC实现:由于JDBC本身没有提供对应的实现,也不太可能提供对应的实现,所以需要导入对应数据库供应商提供的实现,比如MySQL的实现
2. 在程序中手动注册对应的数据库驱动
3. 建立并且打开对应的连接
4. 通过连接获取一个Statement对象,用于发送SQL,并且获得对应的结果
5. 从Statement中获得结果集对象
6. 关闭对应的结果集、Statement以及连接
接下来通过代码来具体查看如何操作
首先是导入MySQL的实现,对应的Maven依赖为
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.38</version>
</dependency>
然后建立测试类TestJDBC.java
注册MySQL驱动
// 注册MySQL驱动
Class.forName("com.mysql.jdbc.Driver");
建立连接
private static final String SQL_URL = "jdbc:mysql://localhost:3306/spring"; // 数据库连接地址
private static final String SQL_USER_NAME = "root"; // 账号
private static final String SQL_PASSWORD = "huanfeng"; // 密码
// 建立连接
Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);
// 获得Statment对象
Statement statement = connection.createStatement();
完整的过程如下所示
private static final String SQL_URL = "jdbc:mysql://localhost:3306/spring";
private static final String SQL_USER_NAME = "root";
private static final String SQL_PASSWORD = "huanfeng";
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
// 注册MySQL驱动
Class.forName("com.mysql.jdbc.Driver");
// 建立连接
connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);
String sql = "SELECT * FROM user";
// 获得Statement
statement = connection.createStatement();
// 获得ResultSet
resultSet = statement.executeQuery(sql);
// 操作ResultSet
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
// 注意关闭的顺序,应该从ResultSet再到Statement再到Connection
// 关闭ResultSet
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭Statement
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
// 关闭Connection
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
这里一定要注意,资源用完之后一定要关闭,因为建立一个对应的连接是非常消耗资源的,而如果使用完之后不关闭资源,会造成非常大的资源浪费,为了证实这一点,下面进行一个简单的测试,测试建立一个Connection所消耗的时间
@Test
public void testConnection() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
long start = System.currentTimeMillis();
Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);
long end = System.currentTimeMillis();
connection.close();
System.out.printf("cost time %d ms\n", (end - start));
}
对应的输出结果如下所示
cost time 360 ms
````
可以看到,建立一个Connection连接需要消耗非常多的时间,原因在于,建立连接的时候,本质上是通过驱动程序与数据库之间建立一个Socket连接,对于Socket有了解的朋友应该知道,建立Socket连接是比较消耗时间的,而且维持一个Socket连接也是非常消耗资源的,所以当资源使用完毕之后,应该关闭对应的资源,当然,更好的做法是使用数据库连接池技术,将Connection对象缓存起来,来避免频繁创建Connection对象。
<div class="se-preview-section-delimiter"></div>
## JDBC实战
上面大概了解了JDBC的操作步骤以及基本的注意事项,接下来通过一个例子来使用JDBC操作数据库,加深对JDBC使用的了解
建立连接的过程同上,这里不重复叙述
<div class="se-preview-section-delimiter"></div>
### 插入数据
<div class="se-preview-section-delimiter"></div>
```Java
@Test
public void testInsert() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);
String sql = "Insert into user(id, name) value(4, 'Tom')";
Statement statement = connection.createStatement();
int result = statement.executeUpdate(sql);
System.out.println(result);
statement.close();
connection.close();
}
上面是最简单的插入操作,不过一般来说,我们会插入参数而不是固定的数值,所以上面的内容会变成
int id = 4;
String name = "Tom";
// 拼接SQL语句
String sql = "Insert into user(id, name) value("+ id +", '"+ name +"')";
通过拼接SQL语句,然后使用Statement来执行语句是解决插入参数的一种方式,但是,通过这种方式进行数据库操作会出现SQL注入的危险,比如说,当有人把参数name的内容填写为 Tom'); DELETE FROM USER; --
,那么此时拼接后的SQL语句就会变成insert into user(id, name) values(4, 'tom'); delete from user; --
此时就非常危险了,虽然这里是不会出现问题的,因为statement一次只能执行一条语句,但是如果是查询后面被加上这些内容,那就非常危险了。为了避免被注入的情况发生,JDBC提供了另外一种方式,采用预编译处理SQL,然后使用PreparedStatement设置参数以及执行对应的SQL语句,如下。
// 采用占位符来处理
String sql = "Insert into user(id, name) value(?, ?)";
PreparedStatement statement = connection.prepareStatement(sql);
// 设置对应的参数,注意这里是从1开始,不是从0开始
statement.setInt(1, id);
statement.setString(2, name);
// 这里也可以直接使用
// statement.setObject(1, id);
// statement.setObject(2, name);
// 不需要指定对应的参数类型,将其交给JDBC进行判断即可
JDBC是推荐使用PreparedStatement来进行SQL处理,而不是Statement来处理,原因在于PreparedStatement提供了预处理机制,可以预防SQL注入的发送,而且由于是采用预处理机制,所以PreparedStatement的执行效率要高于Statement
删除数据
@Test
public void testDelete() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);
int id = 3;
String sql = "DELETE FROM USER where id = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setObject(1, id);
int result = statement.executeUpdate();
statement.close();
connection.close();
}
修改数据
@Test
public void testUpdate() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);
int id = 2;
String name = "Jack";
String sql = "UPDATE USER SET NAME = ? where id = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setObject(1, name);
statement.setObject(2, id);
int result = statement.executeUpdate();
statement.close();
connection.close();
}
查询数据
谈到查询数据,这里就有一个新的对象需要介绍,ResultSet,在JDBC中,查询的结果一般都是以行为单位的,可以是单行也可以是多行,JDBC将每一行都封装成一个ResultSet对象,对于ResultSet的操作,其实就是一个迭代的过程,在获取的时候,需要通过 ResultSet.getXX(ID)
或者ResultSet.getXX(CLO_NAME)
来获取对应行的数据,然后通过ResultSet.next()
将指针移动到下一行。
@Test
public void testQuery() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);
int id = 1;
String sql = "SELECT id, name FROM USER where id = ?";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setObject(1, id);
// 查询返回的ResultSet
ResultSet resultSet = statement.executeQuery();
// 遍历ResultSet
while (resultSet.next()){
// 取出对应的数据
int n_id = resultSet.getInt("id");
String n_name = resultSet.getString("name");
// 封装成对应的对象
User user = new User(n_id, n_name);
System.out.println("user: " + user);
}
// 关闭对应的连接
resultSet.close();
statement.close();
connection.close();
}
大对象处理
所谓的大对象,指的是数据库中定义的CLOB(Character Large Object )、BLOB(Binary Large Object),这两个数据类型是用于把包含信息量比较大的数据存储在列中,当做一个属性,其中CLOB主要是用于存放字符类型大对象,比如说一本书的内容,BLOB主要用于存放二进制类型的大对象,比如说一部小电影。JDBC同样为操作这两种数据类型提供支持,不过,操作起来跟普通的数据类型有所不同,具体如下所示
@Test
public void testBLOB() throws ClassNotFoundException, SQLException, IOException, InterruptedException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);
// 存入Blob类型的数据
File file = new File("d:/img.jpg");
int id = 121;
String name = "jack";
String sql = "insert into user(id, name, b_data) values(?, ?, ?)";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setObject(1, id);
statement.setObject(2, name);
// 这里需要注意,类型应该为Blob,直接传一个输入流即可
statement.setBlob(3, new FileInputStream(file));
int result = statement.executeUpdate();
System.out.println(result);
// 读取Blob类型的数据
sql = "SELECT b_data FROM user WHERE id = ?";
statement = connection.prepareStatement(sql);
statement.setObject(1, id);
ResultSet resultSet = statement.executeQuery();
if (resultSet.next()){
// 注意这里,返回的类型是BLob
Blob b_data = resultSet.getBlob("b_data");
InputStream inputStream = b_data.getBinaryStream();
byte[] buf = new byte[inputStream.available()];
FileOutputStream fileOutputStream = new FileOutputStream(new File("d:/new_img.jpg"));
inputStream.read(buf);
fileOutputStream.write(buf);
fileOutputStream.flush();
System.out.println("finish");
}
resultSet.close();
statement.close();
connection.close();
}
CLOB的操作基本同BLOB,只是对应的类型设置为CLOB即可,需要注意的是,在MySQL中,没有数据类型为CLOB的数据类型,其对应的是text,这里在创建数据表的时候需要注意一下。
批量处理
所谓的批处理,其实就是同时指定多个SQL语句,通常由两种做法,一种是循环执行多个SQL语句,另外一种则是采用JDBC提供的Batch功能,具体如下所示
@Test
public void testBatch() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);
String sql = "insert into user(id, name) values(?, 'Tom')";
PreparedStatement statement = connection.prepareStatement(sql);
long start = System.currentTimeMillis();
// 普通循环执行
for (int i = 0; i < 1000; i++){
statement.setObject(1, i);
statement.executeUpdate();
}
long end = System.currentTimeMillis();
System.out.printf("cost time %d\n", end - start);
statement.close();
statement = connection.prepareStatement(sql);
start = System.currentTimeMillis();
// 使用Batch执行
for (int i = 1001 ; i < 2000; i++){
statement.setObject(1, i);
statement.addBatch();
}
statement.executeBatch();
end = System.currentTimeMillis();
System.out.printf("cost time %d\n", end - start);
statement.close();
connection.close();
}
有点可惜的是,经过测试,这两种方式的性能很接近,不知道是我操作的问题还是事实确实是如此,还望有懂这个的朋友指点指点,
元数据
所谓的元数据,也就是是用于定义其他数据的数据,在SQL中,元数据可以理解为就是数据库、表的定义数据,在JDBC中,有三种类型的元数据,分别是DatabaseMetaData
,ResultSetMetaData
,ParameterMetaData
,分别是数据库相关信息,结果集相关信息,以及PreparedStatement语句中的相关参数信息,具体操作如下所示
@Test
public void testMetaData() throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(SQL_URL, SQL_USER_NAME, SQL_PASSWORD);
// DatabaseMetaData 相关的信息,比如数据库供应商名称,版本等
DatabaseMetaData databaseMetaData = connection.getMetaData();
System.out.println("version : " + databaseMetaData.getDatabaseMajorVersion());
System.out.println("name : " + databaseMetaData.getDatabaseProductName());
int id = 4;
String sql = "select id, name from user where id < ? ";
PreparedStatement statement = connection.prepareStatement(sql);
statement.setObject(1, id);
// ParameterMetaData 相关信息,比如参数个数
ParameterMetaData parameterMetaData = statement.getParameterMetaData();
int paramCount = parameterMetaData.getParameterCount();
System.out.println("parameter count : " + paramCount);
ResultSet resultSet = statement.executeQuery();
// ResultSetMetaData 相关信息,比如取出来的数据的列名
ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
int resultCount = resultSetMetaData.getColumnCount();
System.out.println("resultCount : " + resultCount);
for (int i = 0; i < resultCount; i++) {
System.out.print(resultSetMetaData.getColumnName(i + 1) + " ");
}
System.out.println();
}
事务管理
所谓的事务,是指做一件事情,这件事情有多个步骤,这多个步骤这件,要么都完成,要么都不完成,事务具有四个特性ACID,分别是原子性,一致性,独立性,持久性
在JDBC中,可以通过connection.setAutoCommit(true/false);
开控制事务,默认为true,也就是默认自动提交事务。通过connection.commit()
手动提交事务,connection.setSavepoint();
设置保存点,connection.rollback();
回滚到保存到。