本章目标:
- 了解JDBC的概念以及4种驱动分类
了解一般数据库的基本操作以及SQL语法的基本操作- 使用JDBC进行数据库的开发
- 使用DriverManager、Connection、Statement、PreparedStatement、ResultSet对数据库进行增、删、改、查操作
- 区分Statement和PreparedStatement的使用
- 使用JDBC处理大数据
- 掌握事务的概念以及JDBC对事务的支持
- 掌握JDBC连接主流数据库的操作步骤(Oracle)
- 了解JDBC中的元数据操作
17.1 JDBC概述
17.1.1 JDBC简介
JDBC(Java Database Connectivity, Java数据库连接),提供了一种与平台无关的、用于执行SQL语句的标准Java API, 可以方便地实现多种关系型数据库的统一操作。它由一组用Java语言编写的类和接口组成。
在实际开发中,可以直接使用JDBC进行各个数据库的连接与操作,而且可以方便地向数据库中发送各种SQL命令。在JDBC中提供的是一套标准的接口,这样,各个支持Java的数据库生厂商只要按照此接口提供相应的实现,就都可以使用JDBC进行操作,极大地体现了Java的可移植性的设计思想。
JDBC的全称是Java Database Connectivity,即Java数据库连接,它是一种可以执行SQL语句的Java API,程序可以通过JDBC API连接到关系数据库,并使用结构化查询语言(SQL,数据库标准的查询语言)来完成对数据库的查询、更新。
Java语言的各种跨平台特性,都采用相似的结构,因为它们都需要让相同的程序在不同的平台上运行,所以都需要中间的转换程序(为了实现Java程序的跨平台性,Java为不同的操作系统提供了不同的虚拟机)。同样,为了使JDBC程序可以跨平台,则需要不同的数据库厂商提供相应的驱动程序。如下图所示:
正是通过JDBC驱动的转换,才使得使用相同的JDBC API编写的程序,在不同的数据库系统上运行良好。Sun提供的JDBC可以完成以下三个基本工作
- 与数据库建立连接
- 执行SQL语句
- 获得SQL语句的执行结果
通过JDBC的这三个功能,应用程序就可以使用JDBC来操作数据库系统了。
17.1.2 JDBC驱动分类
数据库驱动程序是JDBC程序和数据库之间的转换层,数据库驱动程序负责将JDBC调用映射成特定的数据库调用。下图显示了JDBC示意图
- ODBC的全称是Open Database Connectivity,即开发数据库连接。ODBC和JDBC很像,严格地说,应该说JDBC模仿了ODBC的设计。ODBC也允许应用程序通过一组通用的API访问不同的数据库管理系统,从而使得基于ODBC的应用程序可以在不同的数据库之间切换。同样,ODBC也需要各数据库厂商提供相应的驱动程序,而ODBC则负责管理这些驱动程序。
JDBC本身提供的是一套数据库操作标准,而这些标准又需要各个数据库厂商实现,所以针对每一个数据库厂商都会提供一个JDBC的驱动程序,目前比较常见的JDBC驱动程序可分为以下4类:
- JDBC-ODBC桥驱动, 是Sun公司蹄冻的一个标准的JDBC操作,直接利用微软的ODBC进行数据库的连接操作。但是这种操作性能较低。这种驱动将JDBC API映射到ODBC API。JDBC-ODBC也需要驱动(由Sun公司提供实现)。
- JDBC本地驱动,直接使用各个数据库生厂商提供的JDBC驱动程序,但是因为其只能应用在特定的数据库上,会丧失程序的可移植性;但是,这样操作的性能较高。 直接将JDBC API映射成数据库特定的客户端API。这种驱动包含特定数据的本地代码,用于访问特定数据库的客户端。
- JDBC网络驱动,将JDBC转换为与DBMS无关的网络协议,之后又被某个服务器转换为一种DBMS协议。这种网络服务器中间件能够将它的纯Java客户机连接到多种不同的数据库上,所用的具体协议取决于提供者。通常,这是最为灵活的JDBC驱动程序。 支持三层结构的JDBC访问方式,主要用于Applet阶段,通过Applet访问数据库。
- 本地协议纯JDBC驱动,将JDBC调用直接转换为DBMS所使用的网络协议,这将允许从客户机机器上直接调用DBMS服务器,是Intranet访问的一个很实用的解决方法。是纯Java的,直接与数据库实例交互。这种驱动是智能的,它知道数据库使用的底层协议。这种驱动是目前最流行的JDBC驱动。
通常建议选择第4种JDBC驱动,这种驱动器避开了本地代码,减少了应用开发的复杂性,也减少了产生冲突和出错的可能。如果对性能有严格的要求,则可以考虑使用第2种JDBC驱动;但是用这种驱动,则势必会增加编码和维护的困难。
- 相对于ODBC而言,JDBC更加简单。总结起来,JDBC比ODBC多了如下几个优势:
- ODBC更加复杂,ODBC中有几个命令需要配置很多复杂的选项,而JDBC则采用简单、直观的方式来管理数据库连接;
- JDBC比ODBC安全性更高、更易部署
17.1.3 JDBC的主要操作类及接口
JDBC提供了独立于数据库的统一API,用以执行SQL命令。
JDBC API由以下常用的接口和类组成。
- DriverManager: 用于管理JDBC驱动的服务类。 程序中使用该类的主要功能是获取Connection对象。
- public static synchronized Connection getConnection(String url, String user, String pass) throws SQLException: 该方法获得url对应数据库的连接。
- Connection: 代表数据库连接对象,每个Connection代表一个物理连接会话。要想访问数据库,必须先获得数据库的连接。
- Statement createStatement() throws SQLException: 该方法返回一个Statement对象。
- PreparedStatement preparedStatement(String sql) throws SQLException: 该方法返回预编译的Statement对象,即将SQL语句提交到数据库进行预编译。
- CallableStatement prepareCall(String sql) throws SQLException: 该方法返回CallableStatement对象(CallableStatement对象用于调用存储过程)。
以上3个方法都返回用于执行SQL语句的Statement对象,PreparedStatement、CallableStatement是Statement的子类,只有获得了Statement之后才可以执行SQL语句。
Connection还有以下用于控制事务的方法:
- Savepoint setSavepoint(): 创建一个保存点
- Savepoint setSavepoint(String name): 以指定名字来创建一个保存点
- void setTransactionIsolation(int level): 设置事务的隔离级别
- void rollback(): 回滚事务
- void rollback(Savepoint savepoint): 将事务回滚到指定的保存点
- void setAutoCommit(Boolean autoCommit): 关闭自动提交, 打开事务
- void commit(): 提交事务
- Statement: 用于执行SQL语句的工具接口。 该对象既可用于执行DDL、DCL语句;也可用于执行DML语句,还可用于执行SQL查询。当执行SQL查询时,返回查询到的结果集。
- ResultSet executeQuery(String sql) throws SQLException: 该方法用于执行查询语句,并返回受影响的结果对应的ResultSet对象。该方法只能用于执行查询语句!!
- int executeUpdate(String sql) throws SQLException: 该方法用于执行DML语句,并返回受影响的行数; 该方法也可用于执行DDL语句,执行DDL语句将返回0!!
- boolean execute(String sql) throws SQLException: 该方法可执行任何SQL语句。如果执行后第一个结果为ResultSet对象,则返回true;如果执行后的第一个结果为受影响的行数或没有任何结果,则返回false
- PreparedStatement: 预编译的Statement对象。PreparedStatement是Statement的子接口,它允许数据库预编译SQL语句(这些SQL语句通常带有参数),以后每次只改变SQL命令的参数,避免了数据库每次都需要编译SQL语句,因此性能更好。相对于Statement而言,使用PreparedStatement执行SQL语句时,无须再传入SQL语句,只要为预编译的SQL语句传入参数值即可。所以它比Statement多了以下方法。
- void setXxx(int parameterIndex, Xxx value): 该方法根据传入参数值得类型不同,需要使用不同的方法。传入的值根据索引传给SQL语句中指定位置的参数。
Note:PreparedStatement同样有executeUpdate()、executeQuery()和execute()三个方法,只是这三个方法无须接收SQL字符串,因为PreparedStatement对象已经预编译了SQL命令,只要为这些命令传入参数即可。
- ResultSet: 结果集对象。该对象包含访问查询结果的方法,ResultSet可以通过列索引或列名获得列数据。它包含了以下常用方法来移动记录指针!!!
- void close() throws SQLException: 释放ResultSet对象
- boolean absolute(int row): 将结果集的记录指针移动到第row行。如果row是负数,则移动到倒数第row行;如果移动后的记录指针指向一条有效记录,则返回true
- void beforeFirst(): 将ResultSet的记录指针定位到首行之前,这是ResultSet结果集记录指针的初始状态 --- 记录指针的起始位置位于第一行之前!!
- boolean first(): 将ResultSet的记录指针定位到首行。如果移动后的记录指针指向一条有效记录,则返回true
- boolean previous(): 将ResultSet的记录指针定位到上一行。如果移动后的记录指针指向一条有效记录,则返回true
- boolean next(): 将ResultSet的记录指针定位到下一行。如果移动后的记录指针指向一条有效记录,则返回true
- boolean last(): 将ResultSet的记录指针定位到最后一行。如果移动后的记录指针指向一条有效记录,则返回true
- void afterLast(): 将ResultSet的记录指针定位到最后一行之后
当把记录指针移动到指定行之后,ResultSet可以通过getXxx(int columnIndex)或getXxx(String columnLabel)方法来获取当前行、指定列的值。
前者根据列索引获取值;后者根据列名获取值!!
17.2 JDBC操作步骤
JDBC编程大致按如下步骤进行:
(1) 加载数据库驱动。 通常我们使用Class类的forName()静态方法来加载驱动。
//加载数据库驱动
Class.forName(DRIVERCLASS); //其中DRIVERCLASS就是数据库驱动类所对应的字符串。
- 各个数据库厂商都会提供JDBC的驱动程序开发包,直接把JDBC操作所需要的开发包(一般为 *.jar或*.zip) 配置到classpath路径即可!!!
- 数据库厂商总会提供相应的文档,其中会有关于驱动类的介绍;不仅如此,文档中还会提供数据库URL写法,以及连接数据库的范例代码。
(2)通过DriverManager获取数据库连接。DriverManager提供了如下方法:
//获取数据库连接
DriverManager.getConnection(String url, String user, String password)
//如果想运行Windows验证来连接数据库的话,可以用如下的代码
public static final String url = "jdbc:sqlser://localhost:port_number;instanceName=****;integratedSecurity=true;databaseName=My_db;"
DriverManager.getConnect(url);
当使用DriverManager获取数据库时,通常需要传入3个参数:数据库URL、登录数据库的用户名和密码。这3个参数中用户名和密码通常由DBA(数据库管理员)分配, 而且该用户还应该具有相应的权限,才可以执行相应的SQL语句。
数据库URL通常遵循如下写法:
jdbc:subprotocol:other stuff
上面URL写法中的jdbc是固定的;而subprotocol指定连接到特定数据库的驱动,后面的other和stuff也不是固定的 --- 特定的数据库有特定的URL写法,具体可以参照各数据库JDBC驱动的文档!!!
- JDBC的连接地址实际上由3个部分组成: jdbc协议: JDBC URL中的协议总是jdbc; 子协议:驱动程序名或数据库连接机制(这种机制可由一个或多个驱动程序支持)的名称; 子名称:一种标识数据库的方法,必须遵循 “//主机名:端口/子协议” 的标准URL命名约定!!!!
(3)通过Connection对象创建Statement对象。Connection创建Statement对象有以下3个方式:
- createStatement(): 创建基本的Statement对象
- prepareStatement(String sql): 根据传入的SQL语句创建预编译的Statement对象
- prepareCall(String sql): 根据传入的SQL语句创建CallableStatement对象
(4)使用Statement对象执行SQL语句。所有的Statement都有如下3个方法来执行SQL语句
- execute(): 可以执行任何SQL语句,但是比较麻烦
- executeUpdate(): 主要用于执行DML和DDL语句;执行DML语句返回受SQL语句影响的行数;执行DDL语句则返回0
- executeQuery(): 只能执行查询语句,执行后返回代表查询结果的ResultSet对象。
(5)操作结果集 如果执行的SQL语句是查询语句,则执行结果返回一个ResultSet对象,该对象里保存了SQL语句查询的结果。程序可以通过操作该ResultSet对象来取出查询结果。ResultSet对象主要提供了如下方法:
- next()、previous()、first()、last()、beforeFirst()、afterLast()、absolute()等移动记录指针的方法
- getXxx()方法获取记录指针指向行、特定列的值。该方法既可使用列索引作为参数;也可使用列名作为参数。使用列索引作为参数性能更好!!!使用列名作为参数可读性更好。
ResultSet的实质是一个查询结果集,在逻辑结构上非常类似于一个表。
(6)回收数据库资源(关闭数据库连接), 包括关闭ResultSet、Statement和Connection等资源
17.3 连接数据库
17.3.1 配置数据库的驱动程序(以MS SQL数据库为例)
如果要使用MS SQL数据库进行开发,首先必须将MS SQL数据库的驱动程序配置到classpath中,直接修改本机的环境变量classpath属性即可。在classpath中添加SQL数据库的驱动程序*.jar的完整路径!!!
17.3.2 加载驱动程序
加载数据库驱动程序是JDBC操作的第一步,在之前要将数据库的驱动程序直接配置到classpath中。然后就可以直接进行加载。
不同的数据库驱动程序路径是不一样的。MS SQL中的数据库驱动程序路径是: com.microsoft.sqlserver.jdbc.SQLServerDriver。。。
范例:加载驱动程序
public class LoadDatabaseDriverDemo01{
//定义MSSQL数据库的驱动程序
public static final String DBDRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
public static void main(String args[]){
try{
Class.forName(DBDRIVER);
System.out.println("JDBC Driver of MS SQL server was loaded successfully!!!");
}catch(ClassNotFoundException e){
e.printStackTrace();
}
}
}
如果上面程序出现异常,则可能是环境变量classpath设置有问题,应该首先排除!!
17.3.3 连接及关闭数据库
如果数据库驱动程序可以正常加载,就可以使用DriverManager类来连接数据库。
在DriverManager中,提供的主要操作就是得到一个数据库的连接;getConnection()方法就是取得连接对象,此方法返回的是一个Connection对象。不过使用哪种方式连接,都必须提供一个数据库的连接地址。
- 在程序操作中,数据库的资源都是非常有限的,要求开发者在操作完数据库之后,必须将其关闭!!如果没有及时关闭数据库连接,在程序运行中就会产生无法连接到数据库的异常!
package org.forfan06.jdbcdemo;
import java.sql.*;
public class ConnSQL {
public static void main(String args[]) throws Exception{
//1.加载驱动,使用反知识,现在记住这么写
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
try(
//2.使用DriverManager获取数据库连接
//其中返回的Connection就代表了Java程序和数据库的连接
//不同数据库的URL写法需要查询驱动文档,用户名、密码由DBA分配
Connection conn = DriverManager.getConnection("jdbc:sqlserver://HSBSH206V22;instanceName=HSBSH206V22;integratedSecurity=true;DatabaseName=JDBC_DB");
//3。使用Connection来创建一个Statement对象
Statement stmt = conn.createStatement();
//4.执行SQL语句
/*
* Statement有3种执行SQL语句的方法:
* 1. execute()可执行任何SQL语句 --- 返回一个boolean值
* 如果执行后第一个结果是ResultSet,则返回true;否则返回false
* 2. executeUpdate()执行DML语句 --- 返回一个整数(代表被SQL语句影响的记录条数)
* 3. executeQuery()执行select语句 --- 返回查询到的结果集
*/
ResultSet rs = stmt.executeQuery("select s.* , teacher_name" + " from student_table s, teacher_table t"
+ " where t.teacher_id = s.java_teacher");
){
//ResultSet有一系列的getXxx(列索引|列名)方法,用于获取记录指针
//指向行、特定列的值,不断地使用next()记录将记录指针下移一行
//如果移动之后记录指针依然指向有效行,则next()方法返回true
while(rs.next()){
System.out.println(rs.getInt(1) + "\t"
+ rs.getString(2) + "\t"
+ rs.getInt(3) + "\t"
+ rs.getString(4) + "\t");
}
}
}
}
SQL代码如下:
CREATE database JDBC_DB;
CREATE TABLE student_table
(student_id int IDENTITY PRIMARY KEY,
student_name varchar(255),
java_teacher int
);
CREATE TABLE teacher_table
(teacher_id int IDENTITY PRIMARY KEY,
teacher_name varchar(255),
);
INSERT INTO student_table(student_name, java_teacher)
values('Dylan', 1), ('Lynn',2), ('Linda', 2),('Tyler', 1);
INSERT INTO teacher_table(teacher_name)
values('Tony'), ('Jason');
SELECT * FROM student_table;
SELECT * FROM teacher_table;
17.4 执行SQL语句的方式
JDBC不仅可以执行查询,也可以执行DDL、DML等SQL语句,从而允许通过JDBC最大限度地控制数据库。
17.4.1 使用executeUpdate方法执行DDL和DML语句
Statement提供了3个方法来执行SQL语句,这里介绍使用executeUpdate()来执行DDL和DML语句。使用Statement执行DDL和DML语句的步骤与执行普通查询语句(executeQuery())的步骤基本相似,区别在于执行了DDL语句后返回值为0,执行了DML语句后返回值为受影响的记录条数!!!
(1)下面程序示范使用executeUpdate()方法创建数据表。我们并没有直接把数据库连接信息写在程序里,而是使用了一个sql.ini文件(就是一个属性文件properties文件)来保存数据库连接信息,这是比较成熟的做法 --- 当需要把应用程序从开发环境移植到生产环境时,无须修改源代码,只需要修改sql.ini配置文件即可。
程序清单:
import java.util.Properties;
import java.sql.*;
public class ExecuteDDL{
private String driver;
private String url;
private String user;
private String pass;
public void initParam(String paramFile) throws Exception{
//使用Properties类来加载属性文件
Properties props = new Properties();
props.load(new FileInputStream(paramFile));
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
pass = props.getProperty("pass");
}
public void createTable(String sql) throws Exception{
//加载驱动
Class.forName(driver);
try(
//获取数据库连接
Connection conn = DriverManager.getConnection(url, user, pass);
//使用Connection来创建一个Statement对象
Statement stmt = conn.createStatement();
){
//执行DDL语句,创建数据表
stmt.executeUpdate(sql);
}
}
public static void main(String args[]) throws Exception{
ExecuteDDL ed = new ExecuteDDL();
ed.initParam("sql.ini");
ed.createTable("CREATE TABLE JDBC_TEST"
+ " (jdbc_id int IDENTITY PRIMARY KEY, "
+ "jdbc_name varchar(255), "
+ "jdbc_desc text);");
System.out.println("-----建表成功-----");
}
}
(2)使用executeUpdate()执行DML语句与执行DDL语句基本相似,区别是executeUpdate()执行DDL语句后返回0;而执行DML语句后返回受影响的记录条数。
下面程序将执行一条insert语句。因为使用了带子查询的insert语句,所以可以一次性插入多条语句。
程序清单:
import java.util.Properties;
import java.sql.*;
public class ExecuteDML{
private String driver;
private String url;
private String user;
private String pass;
public void initParam(String paramFile) throws Exception{
//使用Properties类来加载属性文件
Properties props = new Properties(); props.load(new FileInputStream(paramFile));
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
pass = props.getProperty("pass");
}
public int insertData(String sql) throws Exception{
//记载驱动
Class.forName(driver);
try(
//获取数据库连接
Connection conn = DriverManager.getConnection(url, user, pass);
//使用Connection来创建一个Statement对象
Statement stmt = conn.createStatement();
){
//执行DML语句,返回受影响的记录条数
return stmt.executeUpdate(sql);
}
}
public static void main(String args[]) throws Exception{
ExecuteDML ed = new ExecuteDML();
ed.initParam("sql.ini");
int result = ed.insertData("INSERT INTO JDBC_TEST(jdbc_name, jdbc_desc)"
+ "SELECT s.student_name, t.teacher_name "
+ "FROM student_table s, teacher_table t "
+ "WHERE s.java_teacher = t.teacher_id;");
System.out.println("--系统中共有" + result + "条记录受影响--");
}
}
17.4.2 使用execute方法执行SQL语句
Statement的execute()方法几乎可以执行任何SQL语句,但它执行SQL语句时比较麻烦,通常我们没有必要使用execute()方法来执行SQL语句,而使用executeQuery()或executeUpdate()方法更简单。但是,如果不清楚SQL语句的类型,则只能使用execute()来执行该SQL语句了。
使用execute()方法执行SQL语句的返回值只是boolean值,它表明执行该SQL语句是否返回了ResultSet对象。Statement提供了以下两个方法来获取执行结果ResultSet对象!!!
- getRestulSet(): 获取该Statement执行查询语句所返回的ResultSet对象
- getUpdateCount(): 获取该Statement()执行DML语句所影响的记录行数。
下面程序示范使用Statement的execute()方法来执行任意的SQL语句,执行不同的SQL语句时产生不同的输出。
程序清单:
import java.util.Properties;
import java.sql.*;
public class ExecuteSQL{
private String driver;
private String url;
private String user;
private String pass;
public void initParam(String paramFile) throws Exception{
//使用Properties类来加载属性文件
Properties props = new Properties(); props.load(new FileInputStream(paramFile));
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
pass = props.getProperty("pass");
}
public int executeSql(String sql) throws Exception{
//记载驱动
Class.forName(driver);
try(
//获取数据库连接
Connection conn = DriverManager.getConnection(url, user, pass);
//使用Connection来创建一个Statement对象
Statement stmt = conn.createStatement();
){
//执行SQL语句,返回boolean值表示是否包含ResultSet
boolean hasResultSet = stmt.execute(sql);
//如果执行后有ResultSet结果集
if(hasResultSet){
try(
//获取结果集
ResultSet rs = stmt.getResultSet()
){
//ResultSetMetaData是用于分析结果集的元数据接口
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();
//迭代输出ResultSet对象
while(rs.next()){
//依次输出每列的值
for(int i = 0; i < columnCount; i++){
System.out.print(rs.getString(i + 1) + "\t");
}
System.out.print("\n");
}
}
}else{
System.out.println("该SQL语句影响的记录有" + stmt.getUpdateCount() + "条!");
}
}
}
public static void main(String args[]) throws Exception{
ExecuteSQL es = new ExecuteSQL();
es.initParam("sql.ini");
System.out.println("-----执行删除表的DDL语句-----");
es.executeSql("DROP TABLE JDBC_TEST");
System.out.println("-----执行建表的DDL语句-----");
es.executeSql("CREATE TABLE JDBC_TEST"
+ "(test_id int IDENTITY PRIMARY KEY, "
+ "test_name varchar(255))");
System.out.println("-----执行插入数据的DML语句-----");
es.executeSql("INSERT INTO TABLE JDBC_TEST(test_name) "
+ "SELECT student_name FROM student_table");
System.out.println("-----执行查询数据的查询语句-----");
es.executeSql("SELECT * FROM JDBC_TEST");
}
}
- 此时,上面的程序没有根据各列的数据类型调用相应的getXxx()方法,而是直接使用了getString()方法来取得值,这是可以的。
- 因为ResultSet的getString()方法几乎可以获取除Blob之外的任意类型列的值,这是因为所有的数据类型都可以自动转换成字符串类型
17.4.3 使用PreparedStatement执行SQL语句
问题:如果我们经常需要反复执行一条结构相似的SQL语句,例如:
insert into student_table values(null, 'forfan06', 1);
insert into student_table values(null, 'dylan', 2);
对于这两条SQL语句而言,它们的结构基本相似,只是执行插入时插入的值不同而已。在这种情况下,我们可以使用下面带占位符(?)参数的SQL语句来代替它:
insert into student_table values(null, ?, ?);
但是,Statement执行SQL语句时不允许使用问号占位符参数,而且这个问号占位符参数必须获得值后才可以执行。
结果办法:
为了满足上面的功能,JDBC提供了PreparedStatement接口,它是Statement接口的子接口,它可以预编译SQL语句,预编译后的SQL语句被存储在PreparedStatement对象中,然后可以使用该对象多次高效地执行该语句。
简而言之,使用PreparedStatement比使用Statement的效率要高!!!!
- PreparedStatement对象的创建,使用Connection的preparedStatement()方法,该方法需要传入一个SQL字符串,该SQL字符串可以包含占位符参数。
//创建一个PreparedStatement对象
pstmt = conn.preparedStatement("insert into student_table values(null, ?, 1)");
- PreparedStatement提供的方法: PreparedStatement也提供了execute()、executeQuery()、executeUpdate()3个方法来执行SQL语句,不过这3个方法无须参数,因为PreparedStatement已经存储了预编译的SQL语句。
使用PreparedStatement预编译SQL语句时,该SQL语句可以带占位符参数,因此在执行SQL语句之前必须为这些参数传入参数值,PreparedStatement提供了一系列的setXxx(int index, Xxx value)方法来传入参数值。
============================================================
如果程序很清楚PreparedStatement预编译SQL语句中各参数的类型,则使用相应的setXxx()方法来传入参数即可;
如果程序不清楚PreparedStatement预编译SQL语句中各参数的类型,则可以使用setObject()方法来传入参数。此时,由PreparedStatement来负责类型转换!!!!
============================================================
- 下面程序示范了使用Statement和PreparedStatement分别插入100条记录的对比。使用Statement需要传入100条SQL语句,但使用PreparedStatement则只需要传入1条预编译的SQL语句,然后100次为该PreparedStatement的参数设值即可!!
程序清单:
import java.util.Properties;
import java.sql.*;
public class PreparedStatementTest{
private String driver;
private String url;
private String user;
private String pass;
public void initParam(String paramFile) throws Exception{
//使用Properties类来加载属性文件
Properties props = new Properties();
props.load(new FileInputStream(paramFile));
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
pass = props.getProperty("pass");
//记载驱动
Class.forName(driver);
}
public int insertUseStatement() throws Exception{
long start = System.currentTimeMillis();
try(
//获取数据库连接
Connection conn = DriverManager.getConnect(url, user, pass);
//使用Connection来创建一个Statement对象
Statement stmt = conn.createStatement();
){
//需要使用100条SQL语句来插入100条记录
for(int i = 0; i < 100; i++){
stmt.executeUpdate("INSERT INTO student_table values("
+ "null, 'Name" + i + "', 1)");
}
System.out.println("使用Statement耗时:" + (System.currentTimeMillis() - start));
}
}
public void insertUserPrepare() throws Exception{
long start = System.currentTimeMillis();
try(
//获取数据库连接
Connection conn = DriverManager.getConnection(url, user, pass);
//使用Connection来创建一个PreparedStatement对象
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO student_table values(null, ?, 1)");
){
//100次为PreparedStatement的参数设值,就可以插入100条记录
for(int i = 0; i < 100; i++){
pstmt.setString(1, "Name" + i);
pstmt.executeUpdate();
}
System.out.println("使用PreparedStatement耗时:" + (System.currentTimeMillis() - start));
}
}
public static void main(String args[]) throws Exception{
PreparedStatementTest pt = new PreparedStatementTest();
pt.initParam("sql.ini");
pt.insertUseStatement();
pt.insertUsePreparedStatemetn();
}
}
由程序运行结果可以发现: PreparedStatement 的执行效率比Statement的执行效率高!!
除此之外,使用PreparedStatement还有一个优势 --- 当SQL语句中要使用参数时, 无须“拼接”SQL字符串。而使用Statement则要“拼接”SQL字符串,如上面程序中的executeUpdate()方法的参数!!!这是相当容易出现错误的(注意上面参数中的单引号,这是因为SQL语句中的字符串必须使用单引号引起来)。尤其是当SQL语句中有多个字符串参数时, “拼接”这条SQL语句时就更容易出错了。
使用PreparedStatement则只需要使用问号占位符来代替这些参数即可!!!降低了编程复杂度。
使用PreparedStatement还有一个很好的作用 --- 用于防止SQL注入!!!!(SQL注入是一个较常见的Cracker入侵方式,它利用SQL语句的漏洞来入侵)
- 下面以一个登陆窗口来介绍这种SQL注入的结果。 登陆窗口包含两个文本框,一个用于输入用户名,一个用于输入密码,系统根据用于输入与JDBC_TEST表里的记录进行匹配,如果找到对应记录则提示登陆成功。
程序清单:
import java.io.*;
import java.util.Properties;
import java.sql.*;
public class LoginFrame{
private final String PROP_FILE = "sql.ini";
private String driver;
//url是数据库的服务地址
private String url;
private String user;
private String pass;
//登陆界面的GUI组件
privae JFrame jf = new JFrame("登陆");
private JTextField userField = new JTextField(20);
private JTextField passField = new JTextField(20);
private JButton loginButton = new JButton("登陆");
public void init() throws Exception{
Properties connProp = new Properties();
connProp.load(new FileInputStream(PROP_FILE));
driver = connProp.getProperty("driver");
url = connProp.getProperty("url");
user = connProp.getProperty("user");
pass = connProp.getProperty("password");
//加载驱动
Class.forName(driver);
//为登陆按钮添加时间监听器
loginButton.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
if(validate(userField.getText(), passField.getText())){
//登陆成功则显示“登陆成功”
JOptionPane.showMessageDialog(jf, "登陆成功");
}else{
//否则显示“登陆失败”
JOptionPane.showMessageDialog(jf, "登陆失败");
}
}
});
jf.add(userField, BorderLayout.NORTH);
jf.add(passField);
jf.add(loginButton, BorderLayout.SOUTH);
jf.pack();
jf.setVisible(true);
}
private boolean validate(String userName, String userPass){
//执行查询的SQL语句
String sql = "select * from jdbc_test "
+ "where jdbc_name='" + userName
+ "' and jdbc_desc='" + userPass + "'";
System.out.println(sql);
try(
Connection conn = DriverManager.getConnection(url, user, pass);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)
){
//如果查询的ResultSet里有超过一条的记录,则登陆成功
if(rs.next()){
return true;
}
}catch(Exception e){
e.printStackTrace();
}
return false;
}
public static void main(String args[]) throws Exception{
new LoginFrame().init();
}
}
如果输入正常,运行没有任何问题。
但是,如果用户名处输入: ' or true or ' ,也会显示“登录成功”对话框。,此时的sql语句是 select * from jdbc_test where jdbc_name='' or ture or '' and jdbc_desc='' 这个判断条件就是true。 SQL把这个true当成了直接量!!!!
- 此时,如果把上面的validate方法换成使用PreparedStatement来执行验证,而不是直接使用Statement。
程序清单:
import java.io.*;
import java.util.Properties;
import java.sql.*;
public class LoginFrame{
private final String PROP_FILE = "sql.ini";
private String driver;
//url是数据库的服务地址
private String url;
private String user;
private String pass;
//登陆界面的GUI组件
privae JFrame jf = new JFrame("登陆");
private JTextField userField = new JTextField(20);
private JTextField passField = new JTextField(20);
private JButton loginButton = new JButton("登陆");
public void init() throws Exception{
Properties connProp = new Properties();
connProp.load(new FileInputStream(PROP_FILE));
driver = connProp.getProperty("driver");
url = connProp.getProperty("url");
user = connProp.getProperty("user");
pass = connProp.getProperty("password");
//加载驱动
Class.forName(driver);
//为登陆按钮添加时间监听器
loginButton.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent e){
if(validate(userField.getText(), passField.getText())){
//登陆成功则显示“登陆成功”
JOptionPane.showMessageDialog(jf, "登陆成功");
}else{
//否则显示“登陆失败”
JOptionPane.showMessageDialog(jf, "登陆失败");
}
}
});
jf.add(userField, BorderLayout.NORTH);
jf.add(passField);
jf.add(loginButton, BorderLayout.SOUTH);
jf.pack();
jf.setVisible(true);
}
private boolean vadidate(String userName, String userPass){
try(
Connection conn = DriverManager.getConnection(url, user, pass);
PreparedStatement pstmt = conn.PreparedStatement("select * from jdbc_test where jdbc_name=? and jdbc_desc=?");
){
pstmt.setString(1, userName);
pstmt.setString(2, userPass);
try(
ResultSet rs = pstmt.executeQuery()
){
//如果查询的ResultSet里有超过一条的记录,则登陆成功
if(rs.next()){
return true;
}
}
}catch(Exception e){
e.printStackTrace();
}
return false;
}
public static void main(String args[]) throws Exception{
new LoginFrame().init();
}
}
将上面的validate()方法改为使用PreparedStatement来执行SQL语句之后,即使用户输入 ' or true or ' , 系统一样会显示“登陆失败”对象框。
=====================================================================
总体来看,使用PreparedStatement比使用Statement多了以下3个好处:
- PreparedStatement预编译SQL语句,性能更好
- PreparedStatement无须“拼接”SQL语句,编程更简单
- PreparedStatement可以防住SQL注入,安全性更好
基于以上3点,通常推荐避免使用Statement来执行SQL语句,改为使用PreparedStatement执行SQL语句
=====================================================================
使用PreparedStatement执行带占位符参数的SQL语句时, SQL语句中的占位符参数只能代替普通值,不要使用占位符代替表名、列名等数据库对象,更不要用占位符参数来代替SQL语句中的insert、select等关键字!!!!
17.4.4 使用CallableStatement调用存储过程
下面的SQL语句可以在MS SQL数据库中创建一个简单的存储过程:
create procedure add_pro(@a int, @b int , @sum int output)
as
begin
set @sum = @a + @b;
end
go
上面的程序创建了名为add_pro的存储过程,该存储过程包括3个参数: a、b是传入参数,而sum使用OUTPUT修饰,是传出参数。
Java调用存储过程使用CallableStatement,可以通过Connection的prepareCall()方法来创建CallableStatement对象!!创建该对象时需要传入调用存储过程的SQL语句。调用存储过程的SQL语句总是这种格式: {call 过程名(?, ?, ?...)},其中的问号作为存储过程参数的占位符。例如下面的代码就创建了调用上面存储过程的CallableStatement对象。
//使用Connection来创建一个CallableStatement对象
cstmt = Connection.prepareCall("{call add_pro(?, ?, ?)}");
存储过程的参数既有传入参数,也有传出参数。
所谓传入参数就是Java程序必须为这些参数传入值,可以通过CallableStatement的setXxx()方法为传入参数设置值;
所谓传出参数就是Java程序可以通过该参数获取存储过程里的值,CallableStatement需要调用registerOutParameter()方法来注册该参数。如下代码所示:
//注册CallableStatement的第三个参数是int类型
cstmt.registerOutParameter(3, Type.INTEGER);
经过上面步骤之后,就可以调用CallableStatement的execute()方法来执行存储过程了,执行结束后通过CallableStatement对象的getXxx(int index) 方法来获取指定传出参数的值。
public calss CallableStatementTest{
private String driver;
private String url;
private String user;
private String pass;
public void initParam(String paramFile) throws Exception{
//使用Properties类来加载属性文件
Properties props = new Properties();
props.load(new FileInputStream(paramFile));
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
pass = props.getProperty("pass");
}
public void callProcedure() throws Exception{
//加载驱动
Class.forName(driver);
try(
//获取数据库连接
Connection conn = DriverManager.getConnection(url, user, pass);
//使用Connection来创建一个CallableStatement对象
CallableStatement cstmt = conn.prepareCall("{call add_pro(?, ?, ?)}");
){
cstmt.setInt(1, 4);
cstmt.setInt(2, 5);
//注册CallableStatement的第三个参数是int类型
cstmt.registerOutParameter(3, Types.INTEGER);
//执行存储过程
cstmt.execute();
//获取并输出存储过程传出参数的值
System.out.println("执行结果是:" + cstmt.getInt(3));
}
}
public static void main(String args[]){
CallableStatement ct = new CallableStatement();
ct.initParam("sql.ini");
ct.callProcedure();
}
}
17.5 管理结果集
JDBC 使用ResultSet来封装执行查询得到的查询结果,然后通过移动ResultSet的记录指针来取出结果集的内容。除此之外,JDBC还允许通过ResultSet来更新记录,并提供了ResultSetMetaData来获取ResultSet对象的相关信息
17.5.1 可滚动、可更新的结果集
我们提到ResultSet定位记录指针的方法有absolute()、previous()等方法,但是前面程序自始至终都只用了next()方法来移动记录指针,实际上也可以使用absolute()、previous()、last()等方法来移动记录指针。可以使用absolute()、previous()、afterLast()等方法自由移动记录指针的ResultSet被称为可滚动的结果集。
======在JDK1.4以前,默认打开的ResultSet是不可滚动的,必须在创建Statement或PreparedStatement时传入额外的参数。从JDK1.5以后,默认打开的ResultSet就是可滚动的,无须传入额外的参数=======
以默认方式打开的ResultSet是不可更新的,如果希望创建可更新的ResultSet,则必须在创建Statement或PreparedStatement时传入额外的参数。Connection在创建Statement或PreparedStatement时还可额外传入如下两个参数:
- resultSetType: 控制ResultSet的类型,该参数可以取如下3个值
- ResultSet.TYPE_FORWARD_ONLY: 该常量控制记录指针只能向前移动。这是JDK1.4以前的默认值
- ResultSet.TYPE_SCROLL_INSENSITIVE: 该常量控制记录指针可以自由移动(可滚过结果集),但底层数据的改变不会影响ResultSet的内容
- ResultSet.TYPE_SCROLL_SENSITIVE: 该常量控制记录指针可以自由移动(可滚动结果集),而且底层数据的改变会影响ResultSet的内容
===========TYPE_SCROLL_INSENSITIVE、TYPE_SCROLL_SENSITIVE这两个常量的作用需要底层数据库驱动的支持,对于有些数据库驱动来说,这两个常量并没有太大区别================
- resultSetConcurrency: 控制ResultSet的并发类型,该参数可以接受如下3个值
- ResultSet.CONCUR_READ_ONLY: 该常量指示ResultSet是只读的并发模式(默认)
- ResultSet_CONCUR_UPDATABLE: 该常量指示ResultSet是可更新的并发模式。
下面代码通过这两个参数创建了一个PreparedStatement对象,由该对象生成的ResultSet对象将是可滚动、可更新的结果集
//使用Connection创建一个PreparedStatement对象
//传入控制结果集可滚动、可更新的参数
pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
需要指出的是,可更新的结果集还需要满足以下两个条件:
- 所有数据都应该来自一个表
- 选出的数据集必须包含主键列
通过该PreparedStatement产生的ResultSet就是可滚动、可更新的,程序可调用ResultSet的updateXxx(int columnIndex, Xxx value)方法来修改记录指针所指记录、特定列的值,最后调用ResultSet的updateRow()方法来提交修改。
下面程序示范了这种创建可滚动、可更新的结果集的方法
程序清单:
public class ResultSetTest{
private String driver;
private String url;
private String user;
private String pass;
public void initParam(String paramFile) throws Exception{
//使用Properties类来加载属性文件
Properties props = new Properties();
props.load(new FileInputStream(paramFile));
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
pass = props.getProperty("pass");
}
public void query(String sql) throws Exception{
//加载驱动
Class.forName(driver);
try(
//获取数据库连接
Connection conn = DriverManager.getConnection(url, user, pass);
//使用Connection来创建一个PreparedStatement对象
//传入控制结果集可滚动、可更新的参数
PreparedStatement pstmt = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
ResultSet = pstmt.executeQuery()
){
rs.last();
int rowCount = rs.getRow();
for(int i = rowCount; i > 0; i--){
rs.absolute(i);
System.out.println(rs.getString(1) + "\t"
+ rs.getString(2) + "\t"
rs.getString(3));
//修改记录指针所指记录、第2列的值
rs.updateString(2, "学生名" + i);
//提交修改
rs.updateRow();
}
}
}
public static void main(String args[]) throws Exception{
ResultSetTest rt = new ResultSetTest();
rt.initParam("sql.ini");
rt.query("select * from student_table");
}
}
运行上面的程序,将会看到student_table表中的记录被倒过来输出了, 因为是从最大记录行开始输出的。而且当程序运行结束后,student_table表中所有记录的student_name列的值都被修改了
**********如果要创建可更新的结果集,则使用查询语句查询的数据通常只能来自于同一个数据表,而且查询结果集中的数据列必须包含主键列,否则将会引起更新失败**********
17.5.2 处理Blob类型数据
Blob(Binary Long Object)是二进制长对象的意思!!Blob列通常用于存储大文件,典型的Blob内容是一张图片或一个声音文件,由于它们的特殊性,必须使用特殊的方式来存储。使用Blob列可以把图片、声音等文件的二进制数据保存在数据库里,并可以从数据库里恢复指定文件。
Blob数据的存储: 如果需要将图片插入数据库,显然不能直接通过普通的SQL语句来完成,因为有一个关键的问题 --- Blob常量无法表示!!!所以将Blob数据插入数据库需要使用PreparedStatement,该对象有一个方法 : setBinaryStream(int parameterIndex, InputStream x), 该方法可以为指定参数传入二进制输入流,从而可以实现将Blob数据保存到数据库的功能。
Blob数据的读取: 当我们需要从ResultSet里取出Blob数据时, 可调用ResultSet的getBlob(int columnIndex()方法,该方法将返回一个Blob对象,Blog对象提供了getBinaryStream()方法来获取该Blob数据的输入流,也可以使用Blob对象提供的getBytes()方法直接取出该Blob对象封装的二进制数据。
问了把图片放入数据库,本程序先使用如下SQL语句来建立一个数据表。
create table img_table
(img_id int IDENTITY PRIMARY KEY,
img_name varchar(255),
--创建一个image类型的数据列,用于保存图片数据
img_data image,
)
下面程序可以实现图片“上传” --- 实际上就是将图片保存到数据库, 并在右边的列表框中显示图片的名字,当用于双击列表框中的图片名时,左边窗口将显示该图片 --- 实质就是根据选中的ID从数据库里查找图片,并将其显示出来。
package org.forfan06.jdbcdemo;
import java.util.*;
import javax.swing.*;
import java.sql.*;
import java.awt.BorderLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.io.*;
public class BlobTest {
JFrame jf = new JFrame("图片管理程序");
private static Connection conn;
private static PreparedStatement insert;
private static PreparedStatement query;
private static PreparedStatement queryAll;
//定义一个DefaultListModel对象
private DefaultListModel<ImageHolder> imageModel = new DefaultListModel<>();
private JList<ImageHolder> imageList = new JList<>();
private JTextField filePath = new JTextField(26);
private JButton browserBn = new JButton("...");
private JButton uploadBn = new JButton("上传");
private JLabel imageLabel = new JLabel();
//以当前路径创建文件选择器
JFileChooser chooser = new JFileChooser(".");
//创建文件过滤器
ExtensionFileFilter filter = new ExtensionFileFilter();
static{
try{
Properties props = new Properties();
props.load(new FileInputStream("sql.ini"));
String driver = props.getProperty("driver");
String url = props.getProperty("url");
String user = props.getProperty("user");
String pass = props.getProperty("pass");
Class.forName(driver);
//获取数据库连接
conn = DriverManager.getConnection(url, user, pass);
//创建执行插入的PreparedStatement对象
//该对象执行插入后可以返回自动生成的主键
insert = conn.prepareStatement("insert into img_table(img_name, img_data)"
+ " values(?,?)", Statement.RETURN_GENERATED_KEYS);
//创建两个PreparedStatement对象,用户查询指定图片,查询所有图片
query = conn.prepareStatement("select img_data from img_table"
+ " where img_id=?");
queryAll = conn.prepareStatement("select img_id, "
+ " img_name from img_table");
}catch(Exception e){
e.printStackTrace();
}
}
public void init() throws Exception{
//---初始化文件选择器---
filter.addExtension("jpg");
filter.addExtension("jpeg");
filter.addExtension("gif");
filter.addExtension("png");
filter.setDescription("图片文件(*.jpg, *.jpeg, *.gif, *.png)");
chooser.addChoosableFileFilter(filter);
//禁止“文件类型”下拉列表中显示“所有文件”选项
chooser.setAcceptAllFileFilterUsed(false);
//---初始化程序界面---
fillListModel();
filePath.setEditable(false);
//只能单选
imageList.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
JPanel jp = new JPanel();
jp.add(filePath);
jp.add(browserBn);
browserBn.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent event){
//显示文件对话框
int result = chooser.showDialog(jf, "浏览图片文件上传");
//如果用户选择了APPROVE(赞同)按钮,即打开,保存等效按钮
if(result == JFileChooser.APPROVE_OPTION){
filePath.setText(chooser.getSelectedFile().getPath());
}
}
});
jp.add(uploadBn);
uploadBn.addActionListener(new ActionListener(){
public void actionPerformed(ActionEvent avt){
//如果上传文件的文本框有内容
if(filePath.getText().trim().length() > 0){
//将指定文件保存到数据库
upload(filePath.getText());
//晴空文本框内容
filePath.setText("");
}
}
});
JPanel left = new JPanel();
left.setLayout(new BorderLayout());
left.add(new JScrollPane(imageLabel), BorderLayout.CENTER);
left.add(jp, BorderLayout.SOUTH);
jf.add(left);
imageList.setFixedCellWidth(160);
jf.add(new JScrollPane(imageList), BorderLayout.EAST);
imageList.addMouseListener(new MouseAdapter(){
public void mouseClicked(MouseEvent e){
//如果双击鼠标
if(e.getClickCount() >= 2){
//取出选中的List项
ImageHolder cur = (ImageHolder)imageList.getSelectedValue();
try{
//显示选中项对应的Image
showImage(cur.getId());
}catch(SQLException sqle){
sqle.printStackTrace();
}
}
}
});
jf.setSize(620, 400);
jf.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
jf.setVisible(true);
}
//---查找img_table表填充ListModel---
public void fillListModel() throws SQLException{
try(
//执行查询
ResultSet rs = queryAll.executeQuery()){
//先清除所有元素
imageModel.clear();
//把查询的全部记录添加到ListModel中
while(rs.next()){
imageModel.addElement(new ImageHolder(rs.getInt(1), rs.getString(2)));
}
}
}
//---将指定图片放入数据库---
public void upload(String fileName){
//截取文件名
String imageName = fileName.substring(fileName.lastIndexOf('\\') + 1, fileName.lastIndexOf('.'));
File f = new File(fileName);
try(
InputStream is = new FileInputStream(f)){
//设置图片名参数
insert.setString(1, imageName);
//设置二进制流参数
insert.setBinaryStream(2, is, (int)f.length());
int affect = insert.executeUpdate();
if(affect == 1){
//重新更新ListModel,让JList显示最新的图片列表
fillListModel();
}
}catch(Exception e){
e.printStackTrace();
}
}
//---根据图片Id来显示图片---
public void showImage(int id) throws SQLException{
//设置参数
query.setInt(1, id);
try(
//执行查询
ResultSet rs = query.executeQuery()){
if(rs.next()){
//取出Blob列
Blob imgBlob = rs.getBlob(1);
//取出Blob列里的数据
ImageIcon icon = new ImageIcon(imgBlob.getBytes(1L, (int)imgBlob.length()));
imageLabel.setIcon(icon);
}
}
}
//--- main method---
public static void main(String args[]) throws Exception{
new BlobTest().init();
}
//创建FileFilter的子类,用以实现文件过滤功能
class ExtensionFileFilter extends javax.swing.filechooser.FileFilter{
private String description = "";
private ArrayList<String> extensions = new ArrayList<String>();
//自定义方法,用于添加文件扩展名
public void addExtension(String extension){
if(!extension.startsWith(".")){
extension = "." + extension;
extensions.add(extension.toLowerCase());
}
}
//用于设置该文件过滤器的描述文本
public void setDescription(String aDescription){
description = aDescription;
}
//继承FileFilter类必须实现的抽象方法,返回该文件过滤器的描述文本
public String getDescription(){
return description;
}
//继承FileFilter类必须实现的抽象方法判断该文件过滤器是否接受该文件
public boolean accept(File f){
//如果该文件是路径,则接受该文件
if(f.isDirectory()){
return true;
}
//将文件名转换为小写(全部转为小写后比较,用于忽略文件名大小写)
String name = f.getName().toLowerCase();
//遍历所有可接受的扩展名,如果扩展名相同,则该文件就可接受
for(String extension : extensions){
if(name.endsWith(extension)){
return true;
}
}
return false;
}
}
class ImageHolder{
//封装图片的ID
private int id;
//封装图片的名字
private String name;
public ImageHolder(){}
public ImageHolder(int id, String name){
this.id = id;
this.name = name;
}
//id的setter和getter方法
public void setId(int id){
this.id = id;
}
public int getId(){
return this.id;
}
//name的setter和getter方法
public void setName(String name){
this.name = name;
}
public String getName(){
return this.name;
}
//重写toString方法,返回图片名
public String toString(){
return name;
}
}
}
注解:
连接本地数据库,可以使用以下方法:
String DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
String url = "jdbc:sqlserver://HSBSH20622;instanceName=HSBSH206V22;integratedSecurity=true;DatabaseName=JDBC_DB";
Class.forName(DRIVER);
Connection conn = DriverManager.getConnection(url);
问题:
imageList没有任何显示!!!!!!!!!
17.5.3 处理Clob类型数据
大对象处理主要指的是CLOB和BLOB两种类型的字段。在CLOB中可以存储海量文字,例如,存储一部《三国演义》或者《红楼梦》等等;在BLOB中可以存储二进制数据,如图片、电影等。
如果在程序中要想处理这样的大对象操作,则必须使用PreparedStatement完成,所有的内容要通过IO流的方式从大文本字段中保存和读取。
PreparedStatement提供了下面方法来专门用于写入大对象数据:
void setAsciiStream(int parameterIndex, InputStream x, int length) throws SQLException //将指定的输入流写入数据库的文本字段
void setBinaryStream(int parameterIndex, InputStream x, int length) throws SQLException //将二进制的输入流数据写入到二进制字段中
大对象设置到数据库中后,在查询时就需要使用ResultSet将其读取进来。在ResultSet类中提供了以下方法来读取大对象数据。
以上方法实际上分为两组:Clob和AsciiStream一般都返回大文本;而Blob和BinaryStream一般都返回二进制数据。
--------------------------------------------------华丽的分割线-----------------------------------------------------
CLOB表示大文本数据,在MySQL中提供了LONGTEXT表示大文本数据,此字段的最大保存数据量为4GB。例如,有如下的数据库创建脚本:
DROP TABLE userclob;
CREATE TABLE userclob
(
id INT AUTO_INCREMENT PRIMARY KEY,
name varchar(30) NOT NULL,
note LONGTEXT
);
下面向上表中插入数据,其中对于note的内容,使用一个txt文件表示(随便在里面增加点文本内容,保存至D盘,命名为csdn.txt)
范例:写入大文本数据
package org.forfan06.jdbcdemo;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class ClobDemo01{
//定义MySQL的数据库驱动程序
public static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
//定义MySQL数据库的链接地址
public static final String DBURL = "jdbc:mysql://localhost:3306/HSBSH206V22";
//MySQL数据库的连接用户名
public static final String DBUSER = "root";
//MySQL数据库的连接密码
public static final String DBPASS = "root";
public static void main(String args[]) throws Exception{
Connection conn = null; //数据库连接
PreparedStatement pstmt = null; //数据库操作
String name = "forfan06";
String sql = "INSERT INTO userclob(name, note) VALUES(?, ?)";
Class.forName(DBDRIVER); //加载数据库驱动程序
//连接MySQL数据库时,要写上连接的用户名和密码
conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
pstmt = conn.prepareStatement(sql); //实例化PreparedStatement
//声明一个File对象,用于找到要操作的大文本文件
File f = new File("D:" + File.separator + "csdn.txt");
InputStream input = null; //通过输入流读取内容
input = new FileInputStream(f); //通过输入流读取文件
pstmt.setString(1, name); //设置第一个“?”内容
pstmt.setAsciiStream(2, input, (int) f.length()); //设置输入流
pstmt.executeUpdate(); //执行数据库更新操作
pstmt.close(); //操作关闭
conn.close(); //数据库关闭
}
}
因为内容保存在文本中,所以要使用FileInputStream类将文本文件读取进来,之后直接通过PreparedStatement对象将其写入到对应的大文本字段中。
写入完成后,下面直接使用ResultSet将其读取进来,因为写入时是按照输入流的方法写入的,所以此时也需要按照输入流的方式读取进来。
范例:读取大文本字段
//package org.forfan06.jdbcdemo;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Scanner;
public class ClobDemo02{
//定义MySQL的数据库驱动程序
public static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
//定义MySQL数据库的连接地址
public static final String DBURL = "jdbc:mysql://local:3306/HSBSH206V22";
//MySQL数据库的连接用户名
public static final String DBUSER = "root";
//MySQL数据库的连接密码
public static final String DBPASS = "root";
public static void main(String args[]) throws Exception{
Connection conn = null; //数据库连接
PreparedStatement pstmt = null; //数据库操作
ResultSet rs = null; //保存结果集
int id = 1; //id
String sql = "SELECT name, note FROM userclob WHERE id=?";
Class.forName(DBDRIVER); //加载数据库驱动程序
//连接MySQL数据库时,连接用户名和密码
conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
pstmt = conn.prepareStatement(sql); //实例化PreparedStatement
pstmt.setInt(1, id); //设置查询的id
rs = pstmt.executeQuery(); //查询
if(rs.next()){ //判断是否有内容
String name = rs.getString(1); //取出name列的内容
StringBuffer note = new StringBuffer();
System.out.println("姓名:" + name);
InputStream input = rs.getAsciiStream(2); //接收全部的大文本数据
Scanner scan = new Scanner(input); //接收数据
scan.useDelimiter("\r\n"); //将文件换行作为分割符
while(scan.hasNext()){
note.append(scan.next()).append("\n"); //不断读取内容
}
System.out.println("内容:" + note); //输出内容
input.close();
} pstmt.close(); conn.close();
}
}
以上程序中,为了操作方便,使用了Scanner类接收全部的输入内容。由于在文本保存时存在换行,所以使用了“\r\n” 作为分割符,之后通过循环的方式不断地把内容取出并将内容保存在StringBuffer对象中。
以上的做法是将大文本数据内容直接通过ResultSet读取进来,当然也可以使用ResultSet提供的getClob()方法,将全部的内容变为Clob对象的内容。直接使用Clob可以方便地去的大文本的数据,也可以对这些文本数据进行一些简单的操作。如截取指定长度的文本等等
范例:使用Clob读取内容
//package org.forfan06.jdbcdemo;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class ClobDemo03{
//定义MySQL的数据库驱动程序
public static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
//定义MySQL数据库的连接地址
public static final String DBURL = "jdbc:mysql://local:3306/HSBSH206V22";
//MySQL数据库的连接用户名
public static final String DBUSER = "root";
//MySQL数据库的连接密码
public static final String DBPASS = "root";
public static void main(String args[]) throws Exception{
Connection conn = null; //数据库连接
PreparedStatement pstmt = null; //数据库操作
ResultSet rs = null; //保存结果集
int id = 1; //id
String sql = "SELECT name, note FROM userclob WHERE id=?";
Class.forName(DBDRIVER); //加载数据库驱动程序
//连接MySQL数据库时,连接用户名和密码
conn = DriverManager.getConnection(DBURL, DBUSER, DBPASS);
pstmt = conn.prepareStatement(sql); //实例化PreparedStatement
pstmt.setInt(1, id); //设置查询的id
rs = pstmt.executeQuery(); //查询
if(rs.next()){ //判断是否有内容
String name = rs.getString(1); //取出name列的内容
Clob c = rs.getClob(2); //取出大文本数据
String note = c.getSubString(1, (int)c.length());
System.out.println("姓名:" + name);
System.out.println("内容:" + note);
c.truncate(100); //读取100个长度的内容
System.out.println("部分读取内容:" + c.getSubString(1, (int)c.length()));
}
pstmt.close(); //操作关闭
conn.close(); //数据库关闭
}
}
17.5.4 使用ResultSetMetaData分析结果集
当执行SQL查询后,可以通过移动记录指针来遍历ResultSet的每条记录;当时,程序可能不清楚该ResultSet里包含哪些数据列,以及每个数据列的数据类型。那么,此时可以通过ResultSetMetaData来获取关于ResultSet的描述信息。
============================================
MetaData的意思是元数据,即描述其他数据的数据;因此ResultSetMetaData封装了描述ResultSet对象的数据;后面介绍的DatabaseMetaData则封装了描述Database的数据
============================================
ResultSet里包含一个getMetaData()方法,该方法返回该ResultSet对应的ResultSetMetaData对象。 一旦获取了ResultSetMetaData对象,就可以通过ResultSetMetaData提供的大量方法来返回ResultSet的描述信息。
ResultSetMetaData常用的方法有如下3个:
int getColumnCount(); //返回该ResultSet的列数量
String getColumnName(int columnIndex); //返回指定索引的列名
int getColumnType(int columnIndex); //返回指定索引的列类型
范例:查询执行器(当用户在文本框内输入合法的查询语句并执行成功后,下面的表格将会显示查询结果)
package org.forfan06.jdbcdemo;
public class QueryExecutor{
JFrame jf = new JFrame("查询执行器");
private JScrollPane scrollPane;
private JButton execBn = new JButton("查询");
//用于输入查询语句的文本框
private JTextField sqlField = new JTextField(45);
private static Connection conn;
private static Statement stmt;
//采用静态初始化块来初始化Connection、Statement对象
static{
try{
Properties props = new Properties();
props.load(new FileInputStream("mysql.ini"));
String drivers = props.getProperty("driver");
String url = props.getProperty("url");
String username = props.getProperty("user");
String password = props.getProperty("password");
//加载数据库驱动程序
Class.forName(drivers);
//取得数据库连接
conn = DriverManager.getConnection(url, username, password);
}catch(Exception e){
e.printStackTrace();
}
}
//---初始化界面的方法---
public void init(){
JPanel top = new JPanel();
top.add(new JLabel("输入查询语句:"));
top.add(sqlField);
top.add(execBn);
//为执行按钮、单行文本框添加时间监听器
execBn.addActionListener(new ExceListener());
sqlField.addActionListener(new ExceListener());
jf.add(top, BorderLayout.NORTH);
jf.setSize(640, 480);
jf.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
jf.setVisible(true);
}
//定义监听器
class ExceListener implements ActionListener{
public void actionPerformed(ActionEvent evt){
//删除原来的JTable(JTable使用scrollPane来包装)
if(scrollPane != null){
jf.remove(scrollPane);
}
try(
//根据用户输入的SQL语句执行查询
ResultSet rs = stmt.executeQuery(sqlField.getText())){
//取出ResultSet的MetaData
ResultSetMetaData rsmd = rs.getMetaData();
Vector<String> columnNames = new Vector<>();
Vector<Vector<String>> data = new Vector<>();
//把ResultSet的所有列名添加到Vector里
for(int i = 0; i < rsmd.getColumnCount(); i++){
columnNames.add(rsmd.getColumnName(i + 1));
}
//把ResultSet的所有记录添加到Vector里
while(rs.next()){
Vector<String> v = new Vector<>();
for(int i = 0; i < rsmd.getColumnCount(); i++){
v.add(rs.getString(i + 1));
}
}
//创建新的JTable
JTable table = new JTable(data, columnNames);
scrollPane = new JScrollPane(table);
//添加新的Table
jf.add(scrollPane);
//更新主窗口
jf.validate();
}catch(Exception e){
e.printStackTrace();
}
}
}
public static void main(String args[]){
new QueryExecutor().init();
}
}
======================================
虽然ResultSetMetaData可以准确地分析出ResultSet里包含多少列,以及每列的列名、数据类型等,但是,使用ResultSetMetaData需要一定的系统开销,因此如果在编程过程中已经知道ResultSet里包含多少列,以及每列的列名、数据类型等信息,就没有必要使用ResultSetMetaData来分析该ResultSet对象了!!!
======================================
17.6 事务处理
对于任何数据库应用而言,事务都是非常重要的,事务时保证底层数据完整的重要手段,没有事务支持的数据库应用,那将是非常脆弱的。
17.6.1 事务的概念及数据库的事务支持
事务是由一步或几步数据库操作序列组成的逻辑执行单元,这系列操作要么全部执行,要么全部放弃执行。
程序和事务是两个不同的概念。一般而言,一段程序中可能包含多个事务!!
事务处理在数据库开发中有着非常重要的作用,所谓事务就是所有的操作要么一起成功,要么一起失败,事务本身具有原子性(Atomicity)、一致性(Consistency)、隔离性或独立性(Isolation)、持久性(Durability)4个特征,这4个特征也被称为ACID特征。
- 原子性: 原子性是事务最小的单元,是不可再分割的单元,相当于一个个小的数据库操作,这些操作必须同时完成,如果一个失败了,则一切的操作将全部失败。(例如,甲向乙转账:如果甲这边转账失败,那么乙那边余额增加的事务也将失败)
- 一致性: 指的是在数据库操作的前后是完全一致的,保证数据的有效性,如果事务正常操作则系统会维持有效性,如果事务出现了错误,则回到最原始状态,也要维持其有效性,这样保证事务开始时和结束时系统处于抑制状态。(甲向乙转账: 如果二者都转账成功,则保持其一致性,甲余额减少,乙余额增加; 如果转账失败,则保持操作前的一致性,甲、乙的余额数不变!!)
- 隔离性: 多个事务可以同时进行且彼此之间无法访问,只有当事务完成最终操作时,才可以看到结果。
- 持久性: 当一个系统崩溃时,一个事务依然可以坚持提交,当一个事务完成后,操作的结果保存在磁盘中,永远不会被回滚。
数据库的事务由下列语句组成:
- 一组DML语句,经过这组DML语句修改后的数据将保持较好的一致性
- 一条DDL语句
- 一条DCL语句
DDL和DCL语句最多只能有一条,因为DDL和DCL语句都会导致事务立即提交!!!
当事务所包含的全部数据库操作都成功执行后,应该提交(commit)事务,使这些修改永远生效。事务提交有两种方式:显式提交和自动提交
- 显式提交: 使用commit
- 自动提交: 执行DDL或DCL语句,或者程序正常退出
当事务所包含的任意一个数据库操作执行失败后,应该回滚(rollback)事务,使该事务所做的修改全部失效。事务回滚有两种方式:显式回滚或自动回滚
- 显式回滚: 使用rollback
- 自动回滚: 系统错误或强行退出
17.6.2 MySQL对事务的支持
MySQL默认关闭事务(即打开自动提交),在默认情况下,用户在MySQL控制台输入一条DML语句,这条DML语句将会立即保存到数据库里。为了开始MySQL的事务支持,可以显示调用如下命令:
SET AUTOCOMMIT = {0 | 1} 0表示关闭自动提交(即开启事务)
======================================================================
自动提交和开启事务恰好相反,如果开启自动提交就是关闭事务;关闭自动提交就是开启事务!!!
======================================================================
一旦在MySQL的命令行窗口中输入 set autocommit = 0开启了事务,该命令行窗口的所有DML语句都不会立即生效,上一个事务结束后第一条DML语句将开始一个新的事务,而后续执行的所有SQL语句都处于该事务中,除非显式地使用commit来提交事务,或者正常退出,或者运行DDL、DCL语句导致事务隐式提交。
当然,也可以使用rollback回滚来结束事务,使用rollback结束事务将导致本次事务中DML语句所做的修改全部失效。
======================================================================
一个MySQL命令行窗口代表一次连接Session,在该窗口里设置set autocommit = 0,相当于关闭了该连接Session的自动提交,对其他连接不会有任何影响,也就是对其他MySQL命令行窗口不会有任何影响。在数据库操作中把每一个连接到此数据库上的用户都称为一个Session。
======================================================================
除此之外,如果不想关闭整个命令行窗口的自动提交,而只是想临时性地开始事务,则可以使用MySQL提供的start transcation或begin两个命令,它们都表示临时性地开始一次事务,处于start transcation或begin后的DML语句不会立即生效,除非使用commit显式提交事务,或者执行DDL、DCL语句来隐式提交事务。如以下SQL代码将不会对数据库有任何影响。
#临时开始事务
begin;
#向student_table表中插入3条记录
insert into student_table values(null, 'xx', 1);
insert into student_table values(null, 'yy', 1);
insert into student_table values(null, 'zz', 1);
#查询student_table表的记录
select * from student_table; #1
#回滚事务
rollback;
#再次查询
select * from student_table #2
执行上面SQL语句中的第一条查询语句将会看到刚刚插入的3条记录,如果打开MySQL的其他命令行窗口将看不到这3条记录 -- 这正体现了事务的隔离性。 接着程序rollback了事务中的全部修改,执行第二条查询语句将看到数据库又恢复到事务开始前的状态。
提交,不管是显式提交还是隐式提交,都会结束当前事务;
回滚,不管是显式回滚还是隐式回滚,都会结束当前事务。
除此之外,MySQL还提供了savepoint来设置事务的中间点,通过使用savepoint设置事务的中间点可以让事务回滚到指定中间点,而不是回滚全部事务。一旦设置了中间点后,就可以使用rollback回滚到指定中间点。
======================================================================
普通的提交、回滚都会结束当前事务,但是,回滚到指定中间点因为依然处于事务之中,所以不会结束当前事务!!!
======================================================================
***************************在MySQL中,如果要应用事务处理,则应该按照以下顺序输入命令***********************************************
- 取消自动提交, 执行SET AUTOCOMMIT = 0. 这样所有的更新指令并不会立刻发送到数据表 中,而只存在于当前的session。
- 开启事务, 执行 START TRANSCATION或BEGIN。
- 编写数据库更新语句,如增加、修改、删除,可以在编写的更新语句之前记录事务的保存点,使用SAVEPOINT指令。
- 提交事务,如果确信数据库的修改没有任何的错误,则使用COMMIT提交事务,在提交事务之前对数据库所做的全部操作都将保存在session中。
- 事务回滚,如果发现执行的SQL语句有错误,则使用ROLLBACK命令全部撤消,或者使用ROLLBACK TO SAVEPOINT记录点,让其回滚到指定的位置。
当一个事务进行时,其他的session是无法看到此事务的操作状态的。 即此session对数据库所做的一切修改,如果没有提交事务,则其他session是无法看到此session操作结果的!!!!!!!!
17.6.3 JDBC的事务支持
JDBC连接也提供了事务支持,JDBC连接的事务是由Connection提供,Connection默认打开自动提交,即关闭事务。在这种情况下,每条SQL语句一旦执行,便会立即提交到数据库,永久生效,无法对其进行回滚操作。
可以调用Connection的setAutoCommit()方法来关闭自动提交,开启事务,如下所示
//关闭自动提交,开启事务
conn.setAutoCommit(false);
=====================================================================
程序中还可调用Connection提供的getAutoCommit()方法来返回该连接的自动提交模式
=====================================================================
一旦事务开始之后,程序可以像平常一样创建Statement对象,创建了Statement对象之后,可以执行任意多条DML语句
stmt.executeUpdate(...);
stmt.executeUpdate(...);
stmt.executeUpdate(...);
上面这些SQL语句虽然被执行了,但是,这些SQL所做的修改不会生效,因为事务还没有结束。如果所有的SQL语句都执行成功,程序可以调用Connection的commit()方法来提交事务:
//提交事务
conn.commit();
如果任意一条SQL语句执行失败,则应该用Connection的rollback()方法来回滚事务:
//回滚事务
conn.rollback();
=====================================================================
实际上,当Connection遇到一个未处理的SQLException异常时,系统将会非正常退出,事务也会自动回滚。但是如果程序捕捉到了该异常,则需要在异常处理块中显式地回滚事务
=====================================================================
范例:程序中出现未处理的SQLException异常时,系统将自动回滚事务
package org.forfan06.jdbcdemo;
import java.sql.*;
import java.util.Properties;
import java.io.*;
public class TransactionTest{
private String driver;
private String url;
private String user;
private String pass;
public void initParam(String paramFile) throws Exception{
//使用Properties类来加载属性文件
Properties props = new Properties();
props.load(new FileInputStream(paramFile));
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
pass = props.getProperty("pass");
}
public void insertTransaction(String[] sqls) throws Exception{
//记载驱动
Class.forName(driver);
try(
Connection conn = DriverManager.getConnection(url, user, pass)
){
//关闭自动提交,开启事务
conn.setAutoCommit(false);
try(
//使用Connection来创建一个Statement对象
Statement stmt = conn.createStatement()
){
//循环多次SQL语句
for(String sql:sqls){
stmt.executeUpdate(sql);
}
}
//提交事务
conn.commit();
}
}
public static void main(String args[]) throws Exception{
TransactionTest tt = new TransactionTest();
tt.initParam("my.ini");
String[] sqls = new String[]{
"insert into student_table values(null, 'aaa', 1)",
"insert into student_table values(null, 'bbb', 1)",
"insert into student_table values(null, 'ccc', 1)",
//下面这条SQL语句将会违反外键约
//因为teacher_table表中没有ID为5的记录
"insert into student_table values(null, 'ccc', 5)"
};
tt.insertTransaction(sqls);
}
}
上面程序中的conn.setAutoCommit(false) 和 conn.commit()只是开启事务、提交事务的代码,该程序中并没有回滚事务的代码。但是,当程序执行到第4条SQL语句时,这条语句将会引起外键约束异常,该异常没有得到处理,引起程序非正常结束,所以事务自动回滚。
Connection也提供了设置中间点的方法:setSavepoint(),Connection提供了两个方法来设置中间点。
- Savepoint setSavepoint(): 在当前事务中创建一个未命名的中间点,并返回代表该中间点的Savepoint对象
- Savepoint setSavepoint(String name): 在当前事务中创建一个具有指定名称的中间点,并返回代表该中间点的Savepoint对象
通常来说,设置中间点没有太大的必要指定名称,因为Connection回滚到指定中间点时,并不是根据名字回滚的,而是根据中间点对象回滚的。Connection提供了rollback(Savepoint savepoint)方法来回滚到指定中间点。
=====================================================================
在JDBC中,如果要想进行事务处理,也需要按照指定的步骤完成:
- 取消Connection中设置的自动提交方式“conn.setAutoCommit(false);”。
- 如果批处理操作成功,则执行提交事务“conn.commit();”。
- 如果操作失败,则肯定会引发异常,在异常处理中让事务回滚“conn.rollback();”。
- 如果需要,可以设置Savepoint “Savepoint sp = conn.setSavepoint();”。
=====================================================================
17.6.4 批量更新
JDBC还提供了一个批量更新的功能,使用批量更新时,多条SQL语句将被作为一批操作被同时收集,并同时提交。
=====================================================================
批量更新必须得到底层数据库的支持,可通过调用DatabaseMetaData的supportsBatchUpdates()方法来查看底层数据库是否支持批量更新。
=====================================================================
使用批量更新也需要先创建一个Statement对象,然后利用该对象的addBatch()方法将多条SQL语句同时收集起来,最后调用Statement对象的executeBatch()方法同时执行这些SQL语句,如下:
Statement stmt = conn.createStatement();
//使用Statement同时收集多个SQL语句
stmt.addBatch(sql1);
stmt.addBatch(sql2);
stmt.addBatch(sql3);
...
//同时执行所有的SQL语句
stmt.executeBatch();
执行executeBatch() 方法将返回一个int[]数据,因为使用Statement执行DDL、DML语句都将返回一个int值,而执行多条DDL、DML语句将会返回多个int值,多个int值就组成了这个int[]数组。如果在批量更新的addBatch()方法中添加了select查询语句,程序将直接出现错误。
为了让批量操作可以正确地处理错误,必须把批量执行的操作视为单个事务,如果批量更新在执行过程中失败,则让事务回滚到批量操作开始之前的状态。为了达到这种效果,程序应该在开始批量操作之前先关闭自动提交,然后开始收集更新语句,当批量操作结束之后,提交事务,并恢复之前的自动提交模式。
//保存当前的自动的提交模式
Boolean autoCommit = conn.getAutoCommit();
//关闭自动提交
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
//使用Statement同时收集多条SQL语句
stmt.addBatch(sql1);
stmt.addBatch(sql2);
stmt.addBatch(sql3);
...
//同时提交所有的SQL语句
stmt.executeBatch();
//提交修改
conn.commit();
//恢复原有的自动提交模式
conn.setAutoCommit(autoCommit);
17.7 分析数据库信息
大部分时候,我们只需要对指定数据库进行插入(C)、查询(R)、修改(U)、删除(D)、等CRUD操作;但是,在某些时候,我们需要动态地获取数据库的相关信息,例如数据库里的数据表信息、列信息。除此之外,如果希望在程序中动态地利用底层数据库所提供的特殊功能,则都需要动态分析数据库相关的信息。
17.7.1 使用DatabaseMetaData分析数据库信息
JDBC提供了DatabaseMetaData来封装数据库连接对应数据库的信息,通过Connection提供的getMetaData()方法就可以获取数据库对应的DatabaseMetaData对象。
DatabaseMetaData接口通常由驱动程序供应商提供实现,其目的是让用户了解底层数据库的相关信息。使用该接口的目的是发现如何处理底层数据库,尤其是对于试图与多个数据库一起使用的应用程序-----因为应用程序需要在多个数据库之间切换,所以必须利用该接口来找出底层数据库的功能,例如,调用supportsCorrelatedSubqueries()方法查看是否可以使用关联子查询,或者调用supportsBatchUpdates()方法查看是否可以使用批量更新。
许多DatabaseMetaData方法以ResultSet对象的形式返回查询信息,然后使用ResultSet的常规方法(如getString()和getInt())即可从这些ResultSet对象中获取数据。如果查询的信息不可用,则将返回一个空ResultSet对象。
DatabaseMetaData的很多方法都需要传入一个xxxPattern模式字符串,这里的xxxPattern不是正则表达式,而是SQL里的模式字符串,即用百分号(%)代表任意多个字符,使用下划线(_)代表一个字符。在通常情况下,如果把该模式字符串的参数值设置为null,即表明该参数不作为过滤条件。
下面程序通过DatabaseMetaData分析当前Connection连接对应数据库的一些基本信息,包括当前数据库包含多少数据表,存储过程,student_table表的数据列、主键、外键等信息。
package org.forfan06.jdbcdemo;
import java.sql.*;
import java.io.*;
import java.util.*;
public class DatabaseMetaDataTest{
private String driver;
private String url;
private String user;
private String pass;
public void initParam(String paramFile) throws Exception{
//使用Properties类来加载属性文件
Properties props = new Properties();
props.load(new FileInputStream(paramFile));
driver = props.getProperty("driver");
url = props.getProperty("url");
user = props.getProperty("user");
pass = props.getProperty("pass");
}
public void info() throws Exception{
//加载驱动
Class.forName(driver);
try(
//获取数据库连接
Connection conn = DriverManager.getConnection(url, user, pass);
){
//获取DatabaseMetaData对象
DatabaseMetaData dbmd = conn.getMetaData();
//获取MySQL支持的所有表类型
ResultSet rs = dbmd.getTableTypes();
System.out.println("---MySQL支持的表类型信息---");
printResultSet(rs);
//获取当前数据库的全部数据表
rs = dbmd.getTables(null, null, "%", new String[]{"TABLE"});
System.out.println("---当前数据库里的数据表信息---");
printResultSet(rs);
//获取student_table表的主键
rs = dbmd.getPrimaryKeys(null, null, "student_table");
System.out.println("---student_table表的主键信息---");
printResultSet(rs);
//获取当前数据库的全部存储过程
rs = dbmd.getProcedures(null, null, "%");
System.out.println("---当前数据库里的存储过程信息---");
printResultSet(rs);
//获取teacher_table表和student_table表之间的外键约束
rs = dbmd.getCrossReference(null, null, "teacher_table", null, null, "student_table");
System.out.println("---teacher_table表和student_table表之间的外键约束---");
printResultSet(rs);
//获取student_table表的全部数据列
rs = dbmd.getColumns(null, null, "student_table", "%");
System.out.println("---student_table表的全部数据列---");
printResultSet(rs);
}
}
public void printResultSet(ResultSet rs) throws SQLException{
ResultSetMetaData rsmd = rs.getMetaData();
//打印ResultSet的所有列标题
for(int i = 0; i < rsmd.getColumnCount(); i++){
System.out.print(rsmd.getColumnName(i + 1) + "\t");
}
System.out.print("\n");
//打印ResultSet的全部数据
while(rs.next()){
for(int i = 0; i < rsmd.getColumnCount(); i ++){
System.out.print(rs.getString(i + 1) + "\t");
}
System.out.print("\n");
}
rs.close();
}
public static void main(String args[]) throws Exception{
DatabaseMetaDataTest dmdt = new DatabaseMetaDataTest();
dmdt.initParam("sql.ini");
dmdt.info();
}
}
上面程序中的info方法就是使用DatabaseMetaData分析数据库信息的示例代码。运行上面程序,将可以看到通过DatabaseMetaData分析数据库信息的结果。
17.7.2 使用系统表分析数据库信息
除了可以使用DatabaseMetaData来分析底层数据库信息之外,如果已经确定应用程序所使用的数据库系统,则可以通过数据库的系统表来分析数据库信息。前面已经提到过,系统表又称为数据字典,数据字典的数据通常由数据库系统负责维护,用户通常只能查询数据字典,而不能修改数据字典的内容。
====================================================================
几乎所有的数据库都会提供系统表供用户查询,用户可以通过查询系统表来获得数据库的相关信息。
对于相MySQL和SQL Server这样的数据库,它们还提供一个系统数据库来存储这些系统表。系统表相当于视图,用户只能查看系统表的数据,不能直接修改系统表中的数据。
====================================================================
MySQL数据库使用information_schema数据库来保存系统表,在该数据库里包含了大量系统表,常用系统表的简单介绍如下:
- tables: 存放数据库里所有数据表的信息
- schemata: 存放数据库里所有数据库(与MySQL的Schema对应)的信息
- views: 存放数据库里所有视图的信息
- columns: 存放数据库里所有列的信息
- triggers: 存放数据库里所有触发器的信息
- routines: 存放数据库里所有存储过程和函数的信息
- key_column_usage: 存放数据库里所有具有约束的键信息
- table_constraints: 存放数据库里全部约束的表信息
- statistics: 存放数据库里全部索引的信息
从这些系统表中取得的数据库信息会更加准确。 例如,
若要查询当前MySQL数据库中包含多少数据库及其详细信息,则可以查询schemata系统表;
如果需要查询指定数据库中的全部数据表,则可以查询tables系统表;
如果需要查询指定数据库的全部数据列,就可以查询columns系统表。
select * from schemata;
select * from tables where table_schema = 'select_test';
select * from columns where table_name = 'student_table';
17.7.3 选择合适的分析方式
通常而言,如果使用DatabaseMetaData来分析数据库信息,则具有更好的跨数据库特性,应用程序可以做到数据库无关;但可能无法准确获取数据库的更多细节。
使用数据库系统表来分析数据库系统信息会更加准确,但使用系统表也有坏处 ---- 这种方式与底层数据库耦合严重,采用这种方式将会导致程序只能运行在特定的数据库之上。
通常来说,如果需要获得数据库信息,包括该数据库驱动提供了哪些功能,则应该利用DatabaseMetaData来了解该数据库支持哪些功能。完全可能出现这样一种情况:对于底层数据库支持的功能,但数据库驱动没有提供该功能,程序还是不能使用该功能。使用DatabaseMetaData则不会出现这种问题。
如果需要纯粹地分析数据库的静态对象,例如分析数据库系统里包含多少数据库、数据表、视图、索引等信息,则利用系统会更加合适。
==================================================================================
如果系统利用系统表时具有更好的通用性,程序可以通过DatabaseMetaData的getDatabaseProductName()、getDatabaseProductVersion()方法来获取底层数据库的产品名、产品版本号,还可以通过DatabaseMetaData的getDriverName()和getDriverVersion()方法获取驱动程序名和驱动程序版本号。
==================================================================================
17.8 使用连接池管理连接
数据库连接的建立以及关闭是极耗费系统资源的操作,在多层结构的应用环境中,这种资源的耗费对系统性能影响尤为明显。通过前面介绍的方式(通过DriverManager获取连接)获取的数据库连接,一个数据库连接对象均对应一个物理数据库连接,每次操作都打开一个物理连接,使用完后立即关闭连接。频繁地打开、关闭连接将造成系统性能低下。
数据库连接池的解决方案是: 当应用程序启动时,系统主动建立足够的数据库连接,并将这些连接组成一个连接池。每次应用程序请求数据库连接时,无须重新打开连接,而是从连接池中取出已有的连接使用,使用完后不再关闭数据库连接,而是直接将连接归还给连接池。通过使用连接池,将大大提供程序的运行效率。
对于共享资源的恶情况,有一个通用的设计模式:资源池(Resource Pool),用于解决资源的频繁请求、释放所造成的性能下降。为了解决数据库连接的频繁请求,JDBC2.0规范引入了数据库连接池技术。
数据库连接池是Connection对象的工厂。数据库连接池的常用参数如下:
- 数据库的初始连接数
- 连接池的最大连接数
- 连接池的最小连接数
- 连接池每次增加的容量
JDBC的数据库连接池使用javax.sql.DataSource来表示,DataSource只是一个接口,该接口通常由商用服务器(如WebLogic、WeSphere)等提供实现,也有一些开源组织提供实现(如DBCP和C3P0)。
============================================================================
DataSource通常被称为数据源,它包含连接池和连接池管理两个部分,但习惯上我们也经常把DataSource称为连接池
============================================================================
17.8.1 DBCP数据源
DBCP是Apache软件基金组织下的开源连接池实现,该连接池依赖该组织下的另一个开源系统: common-pool。 如果需要使用该连接池实现,则应在系统中增加如下两个jar文件
- commons-dbcp.jar: 连接池的实现
- commons-pool.jar: 连接池实现的依赖库
============================================================================
登陆http://commons.apache.org/proper/commons-dbcp 和http://commons.apache.org/proper/commons-pool/ 就可以下载commons-pool.zip和commons-dbcp.zip两个压缩文件,解压缩这两个文件即可得到上面的两个JAR文件。
为了在程序中使用这两个JAR文件,应该把它们添加到系统的类加载路径中(比如添加到CLASSPATH环境变量中)。
============================================================================
Tomcat的连接池正式采用该连接池实现的。数据库连接池既可以与应用服务器整合使用,也可以由应用程序独立使用。下面的代码片段示范了使用DBCP来获得数据库连接的方式:
//创建数据源对象
BasicDataSource ds = new BasicDataSource();
//设置连接池所需的驱动
ds.setDriverClassName("com.mysql.jdbc.Driver");
//设置连接数据库的URL
ds.setUrl("jdbc:mysql://localhost:3306/javaee");
//设置连接数据库的用户名
ds.setUsername("root");
//设置连接数据库的密码
ds.setPassword("pass");
//设置连接池的初始连接数
ds.setInitialSize(5);
//设置连接池最多可有多少个活动连接数
ds.setMaxActive(20);
//设置连接池中最少有2个空闲的连接
ds.setMinIdle(2);
数据源和数据库连接不同,数据源无须创建多个,它是产生数据库连接的工厂,因此整个应用只需要一个数据源即可。也就是说,对于一个应用,上面代码只要执行一次即可。建议把上面程序中的ds设置成static成员变量,并且在应用开始时立即初始化数据源对象,程序中所有需要获取数据库连接的地方直接访问该ds对象,并获取数据库连接即可。通过DataSource获取数据库连接的代码示例如下:
//通过数据源获取数据库连接
Connection conn = ds.getConnection();
当数据库访问结束后,程序还是像以前一样关闭数据库连接,如下代码所示:
//释放数据库连接
conn.close();
但是,上面代码并没有关闭据库的物理连接,它仅仅把数据库连接释放,归还给连接池,让其他客户端可以使用该连接.
17.8.2 C3P0数据源
相比之下,C3P0数据源性能更胜一筹,Hibernate就推荐使用该连接池。C3P0连接池不仅可以自动清理不再使用的Connection,还可以自动清理Statement和ResultSet。C3P0连接池需要版本为1.3以上的JRE。如果需要使用C3P0连接池,则应在系统中增加如下JAR文件。
- c3p0-0.9.1.2.jar: C3P0连接池的实现。
//创建连接池实例
ComboPooledDataSource ds = new ComboPooledDataSource();
//设置连接池连接数据库所需的驱动
ds.setDriverClass("com.mysql.jdbc.Driver");
//设置连接数据库的url
ds.setJdbcUrl("jdbc:mysql://localhost:3306/javaee");
//设置连接数据库的用户名、密码
ds.setUser("root");
ds.setPassword("root");
//设置连接池的最大连接数
ds.setMaxPoolSize(40);
//设置连接池的最小连接数
ds.setMinPoolSize(2);
//设置连接池的初始连接数
ds.setInitialPoolSize(10);
//设置连接池的缓存Statment的最大数
ds.setMaxStatements(180);
在程序中创建C3P0连接池的方法与前面介绍的创建DBCP连接池的方法基本类似。一旦获取了C3P0连接池之后,程序同样可以通过如下代码来获取数据库连接:
//获得数据库连接
Connection conn = ds.getConnection();
17.9 本章要点
- JDBC提供了一套与平台无关的标准数据库操作接口和类,只要是支持Java的数据库厂商,所提供的数据库都可以使用JDBC操作。
- JDBC的操作步骤如下: (1)加载驱动程序:驱动程序由各个数据库生厂商提供; (2)连接数据库: 连接时需要提供连接路径、用户名、密码; (3) 实例化操作: 通过连接对象实例化Statement或PreparedStatement对象; (4)操作数据库: 使用Statement或PreparedStatement操作,如果是查询,则全部的查询结果使用ResultSet进行接收。
- JDBC提供了大对象的操作类,操作大对象时使用IO流的方式写入,读取时也可以使用Clob、Blob方便操作。
- 可以使用CallableStatement调用数据库中的存储过程
- 可以在创建Statement或PreparedStatement时,指定ResultSet的操作类型和并发性,这样就可以使用ResultSet进行滚动以及使用ResultSet直接更新数据库操作。
- JDBC2.0中提供的重要特性就是批处理操作,此操作可以让多条SQL语句一次性执行完毕。
- MySQL提供了事务的支持命令,在JDBC中也同样可以进行事务操作,JDBC事务操作的步骤如下: (1)取消自动提交;(2)使用手工提交方式;(3)如果出现了操作错误,则一切操作回滚。
- 在JDBC中可以使用DatabaseMetaData和ResultSetMetaData分析数据库
- 各个数据库生厂都会提供各自的数据库驱动程序,在使用JDBC连接不同数据库时都需要将驱动程序配置到classpath中。
17.10 习题
- 建立一个用户表(数据表和表中的数据列自行创建),使用键盘输入列的信息,并将信息保存在数据库中
- 编写一个程序,可以通过此程序完成一个表的创建操作,输入表名称、各个列的名称及类型,输入完成后直接通过JDBC创建指定的表
- 使用键盘输入流,接收一段大文本数据,通过输入的路径输入保存的图片路径,并将这些数据保存在数据表中,数据表由用户自行创建
- 将第6章的宠物商店程序修改为使用数据库保存全部的宠物信息,并可以实现关键字查找
- 建立一张雇员表(雇员编号、姓名、工作、雇佣日期、基本工资、部门名称),在命令窗口下将表中的全部数据列出。