JDBC编程
数据库简介
数据库(DB,Data Base)
数据库管理系统(DBMS,Data Base Management Systenm)
关系型数据库（RDB）
关系型数据库管理系统（RDBMS）
SQL语言（Structured Query Language）
- 使用关系模型的数据库语言，用于和各类数据库的交互，提供通用的数据管理和查询功能。
- 常用SQL指令：SELECT 、INSERT、DELETE、UPDATE、CREATE、DROP
ODBC（Open DataBase Connectivity开放式数据库互连）
- 数据库系统应用程序接口规范。
- 支持应用程序以标准的ODBC函数和sql语句操作各种不同类型的数据库
操作各种不同类型的数据库。
JDBC
JDBC（Java DataBase Connectivity）
JDBC功能:
- 支持基本SQL语句，在Java程序中实现数据库操作功能并简化操作过程
- 提供多样化的数据库连接方法
- 为各种不同的数据库提供统一的操作界面
JDBC API
- java.sql. DriverManager类
- java.sql.Driver接口
- java.sql.Connection接口
- java.sql.Statement接口
- java.sql.ResultSet接口
JDBC工作原理
JDBC驱动程序
数据库驱动程序（DataBase Driver）
JDBC驱动程序分类:
- 第一类：JDBC-OCBC桥
- 第二类：Java到本地API
- 第三类：Java到网络协议
- 第四类：Java到数据库协议
数据库URL
JDBC技术中使用数据库URL来标识目标数据库
数据库URL格式：
jdbc:<子协议名>:<子名称>- "jdbc"为协议名，确定不变；
- <子协议名>指定目标数据库的种类和具体连接方式；
- <子名称>指定具体的数据库/数据源连接信息（如数据库服务器的IP地址/通信端口号、ODBC数据源名称、连接用户名/密码等）。
- 子名称的格式和内容随子协议的不同而改变。
举例：
jdbc:oracle:thin:@222.212.32.14:1521:dbrbh
jdbc:microsoft:sqlserver://127.0.0.1:1433
jdbc:microsoft:sqlserver://127.0.0.1:1433,databasename=pubs
jdbc:mysql://127.0.0.1/db_rbhJDBC编程
JDBC编程基本步骤
1. 加载驱动程序（向系统注册所需的JDBC驱动程序）；
2. 建立到指定数据库的连接；
3. 提交数据库查询；
4. 取得查询结果
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class JDBCExample{
public static void main(String args[]){
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:dbrbh";
Connection conn = DriverManager.getConnection(url,"scott","tiger");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from dept");
while(rs.next()){
System.out.print("DeptNo: " + rs.getInt(1));
System.out.print("\tDeptName: " + rs.getString(2));
System.out.println("\tLOC: " + rs.getString(3));
}
rs.close();
stmt.close();
conn.close();
}catch(ClassNotFoundException e){
System.out.println("找不到指定的驱动程序类!");
}catch(SQLException e){
e.printStackTrace();
}
}
}
ResultSet常用get...()方法 SQL-Java类型对应关系
执行DML语句
create table student(
sid char(10),
name char(20),
age number(3));
insert into student values('J001','张三',18);
insert into student values('J002','李四',20);
insert into student values('J003','王五',19); import java.sql.*;
public class TestDML{
public static void main(String args[]){
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:dbrbh";
Connection conn = DriverManager.getConnection(url,"scott","tiger");
Statement stmt = conn.createStatement();
stmt.executeUpdate("insert into student values('J001','张三',18)");
stmt.executeUpdate("insert into student values('J002','李四',20)");
stmt.executeUpdate("insert into student values('J003','王五',19)");
ResultSet rs = stmt.executeQuery("select * from student");
while(rs.next()){
System.out.print("学号: " + rs.getString(1));
System.out.print("\t姓名: " + rs.getString(2));
System.out.println("\t年龄: " + rs.getInt(3));
}
rs.close();
stmt.executeUpdate("update student set age=age+10");
stmt.executeUpdate("delete from student where sid='J002'");
System.out.println("--------------更新/删除数据后---------------");
rs = stmt.executeQuery("select * from student");
while(rs.next()){
System.out.print("学号: " + rs.getString("sid"));
System.out.print("\t姓名: " + rs.getString("name"));
System.out.println("\t年龄: " + rs.getInt("age"));
}
rs.close();
stmt.close();
conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
执行DDL语句
import java.sql.*;
public class TestDDL{
public static void main(String args[]){
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try{
System.setProperty("jdbc.drivers","oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:dbrbh";
conn = DriverManager.getConnection(url,"scott","tiger");
stmt = conn.createStatement();
stmt.execute("create table mytable(id number, name char(10), phone char(10))");
stmt.executeUpdate("insert into mytable values(55,'Tom','67666939')");
rs = stmt.executeQuery("select * from mytable");
while(rs.next()){
System.out.println(rs.getString(1) + "\t" + rs.getString(2) + "\t" + rs.getString(3));
}
stmt.execute("drop table mytable");
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(rs != null){
rs.close();
}
}catch(Exception e){
e.printStackTrace();
}
try{
if(stmt != null){
stmt.close();
}
}catch(Exception e){
e.printStackTrace();
}
try{
if(conn != null){
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
}
}
获取数据库元数据
import java.sql.*;
public class TestMetaData{
public static void main(String args[]){
Connection conn = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:dbrbh";
conn = DriverManager.getConnection(url,"scott","tiger");
ResultSet rs = null;
DatabaseMetaData dmd = conn.getMetaData();
if (dmd == null) {
System.out.println ("No Meta available");
} else {
System.out.println ("数据库名称:" + dmd.getDatabaseProductName());
System.out.println ("数据库版本:" + dmd.getDatabaseProductVersion());
System.out.println ("数据库驱动程序：" + dmd.getDriverName());
System.out.println ("驱动程序版本号：" + dmd.getDriverVersion());
System.out.println ("并发访问的用户个数" + dmd.getMaxConnections());
System.out.println ("数据类型列表：" );
rs = dmd.getTypeInfo();
while(rs.next()){
System.out.println("\t" + rs.getString(1));
}
rs.close();
}
Statement stmt = conn.createStatement();
String s = "select * from dept";
rs = stmt.executeQuery(s);
System.out.println("数据表dept结构信息：");
ResultSetMetaData rsm = rs.getMetaData();
int columnCount = rsm.getColumnCount();
System.out.println("列序号\t列名\t数据类型");
for(int i=1;i<=columnCount;i++){
System.out.println(" " + i + " \t" +
rsm.getColumnName(i) + "\t" +
rsm.getColumnTypeName(i));
}
rs.close();
stmt.close();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(conn != null && !conn.isClosed()){
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
}
}
访问SQL Server数据库
准备SQL Server数据库JDBC驱动程序
1. 从微软网站下载JDBC驱动程序安装文件
2. 本地安装该驱动程序包
3. 重新设置CLASSPATH环境变量
import java.sql.*;
public class TestSQLServer{
public static void main(String[] args) {
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
Connection con = DriverManager.getConnection
("jdbc:microsoft:sqlserver://127.0.0.1:1433","sc","admin");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from mytable1");
while(rs.next()){
System.out.print("sid：" + rs.getInt(1));
System.out.print("\tname：" + rs.getString(2).trim());
System.out.print("\tsex：" + rs.getString(3).trim());
System.out.println();
}
rs.close();
stmt.close();
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
}JDBC-ODBC编程
工作原理：
JDBC驱动程序管理器并不直接操纵数据库驱动程序，而是调用JDBC-ODBC桥驱动程序操纵,ODBC驱动程序，进而连接各种类型的数据库。
编程步骤：
创建ODBC数据源
在程序中连接并操作ODBC数据源
访问Excel文件
import java.sql.*;
public class JDBC_ODBC{
public static void main(String args[]){
String drv = "sun.jdbc.odbc.JdbcOdbcDriver";
try{
Class.forName(drv);
Connection con = DriverManager.getConnection("jdbc:odbc:ExcelODBC1", "", "");
DatabaseMetaData dmd = con.getMetaData();
if (dmd == null) {
System.out.println ("No DBMeta available");
} else {
System.out.println ("DB Name:\t" + dmd.getDatabaseProductName());
System.out.println ("DB Version:\t" + dmd.getDatabaseProductVersion());
System.out.println ("DB Driver Name:\t" + dmd.getDriverName());
System.out.println ("Driver Version:\t" + dmd.getDriverVersion());
}
Statement stmt=con.createStatement();
ResultSet rs=stmt.executeQuery("select * from [Sheet1$]");
System.out.println("记录内容：");
System.out.println("\t姓 名\t年龄\t月 薪\t职 务" );
while(rs.next()){
System.out.print("\t" + rs.getString(1));
System.out.print("\t" + rs.getInt(2));
System.out.print("\t" + rs.getString(3));
System.out.println();
}
rs.close();
stmt.close();
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
}访问Access数据库
import java.sql.*;
public class TestAccess{
public static void main(String[] args) {
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:test", "", "");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from table1");
while(rs.next()){
System.out.print("编号:" + rs.getString(1));
System.out.print("\t姓名:" + rs.getString(2));
System.out.print("\t性别:" + rs.getString(3));
System.out.println();
}
rs.close();
stmt.close();
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
}访问SQL Server数据库
import java.sql.*;
public class TestSQLServer{
public static void main(String[] args) {
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc:odbc:mydb1", "", "");
//Connection con = DriverManager.getConnection("jdbc:odbc:mydb1;database=pubs", "", "");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from mytable1");
while(rs.next()){
System.out.print("编号：" + rs.getInt(1));
System.out.print("\t姓名：" + rs.getString(2).trim());
System.out.print("\t性别：" + rs.getString(3).trim());
System.out.println();
}
rs.close();
stmt.close();
con.close();
}catch(Exception e){
e.printStackTrace();
}
}
}使用属性文件配置环境
将数据库连接信息（URL、用户名/密码等）保存到专门的属性文件中，而不在程序中直接给出
#Oracle db info db_url = jdbc:oracle:thin:@127.0.0.1:1521:dbrbh username = scott password = tiger
import java.io.*;
import java.util.*;
import java.sql.*;
public class JDBCExample{
public static Connection getConnection()throws SQLException,IOException{
Properties ps = new Properties();
FileInputStream fis = new FileInputStream("mydb.properties");
ps.load(fis);
fis.close();
String url = ps.getProperty("db_url");
String username = ps.getProperty("username");
String psw = ps.getProperty("password");
Connection conn = DriverManager.getConnection(url,username,psw);
return conn;
}
public static void main(String args[]){
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = JDBCExample.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from dept");
while(rs.next()){
System.out.print("DeptNo: " + rs.getInt(1));
System.out.print("\tDeptName: " + rs.getString(2));
System.out.println("\tLOC: " + rs.getString(3));
}
rs.close();
stmt.close();
conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
OCI方式访问Oracle数据库
OCI方式连接时Oracle客户端配置
使用Net Configuration Assistant
使用Net Manager图形化工具
直接修改数据库配置文件"tnsnames.ora"
import java.sql.*;
public class TestOCI{
public static void main(String args[]){
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
//Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@mdb1","scott","tiger");
Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@(description=(address=(host=127.0.0.1)(protocol=tcp)(port=1521))(connect_data=(sid=ora9)))","scott", "tiger");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("select * from dept");
while(rs.next()){
System.out.print("DeptNo: " + rs.getInt(1));
System.out.print("\tDeptName: " + rs.getString(2));
System.out.println("\tLOC: " + rs.getString(3));
}
rs.close();
stmt.close();
conn.close();
}catch(Exception e){
e.printStackTrace();
}
}
}
可滚动/可更新结果集
结果集类型
不可滚动（FORWARD_ONLY）
滚动不敏感（SCROLL_INSENSITIVE）
滚动敏感（SCROLL_SENSITIVE）
结果集的并发模式
只读的（READ_ONLY）
可更新的（UPDATABLE）
Connection 接口中提供的重载方法createStatement()用于获取可滚动/可更新结果集。
方法格式：
Statement createStatement(int type,int concurrency)检测结果集类型
结果集类型与并发模式常量
ResultSet.TYPE_FORWARD_ONLY 不可滚到结果集
ResultSet.TYPE_SCROLL_INSENSITIVE 滚到不敏感结果集
ResultSet.TYPE_SCROLL_SENSITIVE 滚到敏感结果集
ResultSet.CONCUR_READ_ONLY 只读结果集
ResultSet.CONCUR_UPDATABLE 可更新结果集
使用JDBC元数据API进行检测
import java.sql.*;
public class TestMetaData{
public static void main(String args[]){
Connection conn = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:dbrbh";
conn = DriverManager.getConnection(url,"scott","tiger");
ResultSet rs = null;
DatabaseMetaData dmd = conn.getMetaData();
if (dmd == null) {
System.out.println ("No Meta available");
} else {
System.out.println ("数据库名称:" + dmd.getDatabaseProductName());
System.out.println ("数据库版本:" + dmd.getDatabaseProductVersion());
System.out.println ("数据库驱动程序：" + dmd.getDriverName());
System.out.println ("驱动程序版本号：" + dmd.getDriverVersion());
System.out.println ("--------------------------------------------");
System.out.println ("结果集类型及支持情况(true-支持,false-不支持)");
System.out.println ("TYPE_FORWARD_ONLY: " +
dmd.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY));
System.out.println ("TYPE_SCROLL_INSENSITIVE: " +
dmd.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE));
System.out.println ("TYPE_SCROLL_SENSITIVE: " +
dmd.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE));
System.out.println ("CONCUR_READ_ONLY: " +
dmd.supportsResultSetType(ResultSet.CONCUR_READ_ONLY));
System.out.println ("CONCUR_UPDATABLE: " +
dmd.supportsResultSetType(ResultSet.CONCUR_UPDATABLE));
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(conn != null){
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
}
}使用可滚动结果集
对于可滚动结果集，可以使用ResultSet接口中定义的下述方法进行遍历：
boolean next()
boolean previous()
boolean first()
boolean last()
void beforeFirst()
void afterLast()
boolean relative(int rows)
boolean absolute(int row)
int getRow()
import java.sql.*;
public class TestScrollResultSet{
public static void main(String args[]){
Connection conn = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:dbrbh";
conn = DriverManager.getConnection(url,"scott","tiger");
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("select * from student");
while(rs.next()){
showOneRow(rs);
}
System.out.println("-----------");
rs.last();
showOneRow(rs);
rs.first();
showOneRow(rs);
rs.beforeFirst();
rs.next();
showOneRow(rs);
rs.absolute(2);
showOneRow(rs);
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(conn != null){
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
}
public static void showOneRow(ResultSet rs) throws SQLException{
System.out.print("第" + rs.getRow() + "行: ");
System.out.print(rs.getString(1));
System.out.print("\t" + rs.getString(2));
System.out.print("\t" + rs.getString(3));
System.out.println();
}
}
使用可更新结果集
对于可更新结果集，可以使用ResultSet接口中定义的下述方法进行更新操作：
void updateXXX(String columnName, XXX x)
void updateXXX(int columnIndex, XXX x)
void updateRow()
void moveToInsertRow()
void insertRow()
void moveToCurrentRow()
void deleteRow()
void cancelRowUpdates()
import java.sql.*;
public class TestUpdatableResultSet{
public static void main(String args[]){
Connection conn = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:dbrbh";
conn = DriverManager.getConnection(url,"scott","tiger");
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs = stmt.executeQuery("select tid,name,salary from teacher");
//ResultSet rs = stmt.executeQuery("select teacher.* from teacher");
//更新前结果集中数据
while(rs.next()){
showOneRow(rs);
}
//更新和删除记录
rs.beforeFirst();
while(rs.next()){
String name = rs.getString("name").trim();
if(name.equals("李四")){
double sal = rs.getDouble("salary");
rs.updateDouble("salary", sal + 8888);
rs.updateRow();
}else if(name.equals("张三")){
rs.deleteRow();
}
}
//插入新记录
rs.moveToInsertRow();
rs.updateInt("tid",1005);
rs.updateString("name","张三峰");
rs.updateDouble("salary",2840);
rs.insertRow();
rs.close();
//结果集更新后后数据库中数据
System.out.println("--------------------------------------------");
rs = stmt.executeQuery("select * from teacher");
while(rs.next()){
showOneRow(rs);
}
rs.close();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(conn != null){
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
}
public static void showOneRow(ResultSet rs) throws SQLException{
System.out.print("第" + rs.getRow() + "行: ");
System.out.print(rs.getInt(1));
System.out.print("\t" + rs.getString(2));
System.out.print("\t" + rs.getDouble(3));
System.out.println();
}
}预处理语句
java.sql.PreparedStatement接口提供了执行预编译SQL语句的功能，它继承了java.sql.Statement接口。
Connection 对象的prepareStatement(String sql)方法可创建并返回PreparedStatement对象。
PreparedStatement接口主要方法：
void setXXX(int parameterIndex, XXX x）
ResultSet executeQuery()
int executeUpdate()
drop table mytable2;
create table mytable2(tid number(6), name char(20), salary number(7,2));import java.sql.*;
public class PrepStmt{
public static void main(String[] args){
Connection con = null;
PreparedStatement pst = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:dbrbh";
con = DriverManager.getConnection(url,"scott","tiger");
String sql = "insert into mytable2 values(?,?,?)";
pst = con.prepareStatement(sql);
for(int i = 101;i<200;i++){
pst.setInt(1,i);
pst.setString(2,"Tom" + i);
pst.setDouble(3,(100+i)*10);
pst.executeUpdate();
}
System.out.println("id:\tname:\tsalary:");
pst = con.prepareStatement("select * from mytable2 where id = ?");
pst.setInt(1,157);
ResultSet rs = pst.executeQuery();
if(rs.next()){
System.out.println(rs.getInt(1) + "\t" + rs.getString(2).trim() + "\t" + rs.getDouble(3));
}
int[] ids = {160,171,182};
for(int i:ids){
pst.setInt(1,i);
rs = pst.executeQuery();
if(rs.next()){
System.out.println(rs.getInt(1) + "\t" + rs.getString(2).trim() + "\t" + rs.getDouble(3));
}
}
}catch(Exception e){
System.err.println(e);
}finally{
try{
if(pst != null){
pst.close();
}
}catch(Exception e){
e.printStackTrace();
}
try{
if(con != null){
con.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
}
}调用存储过程
ava.sql.CallableStatement接口提供了调用数据库服务器端存储过程（Procedure）的功能，它继承了 java.sql. PreparedStatement接口。
Connection对象的prepareCall(String sql)方法可创建并返回CallableStatement对象。
CallableStatement接口主要方法：
void setXXX(int parameterIndex, XXX x）
boolean execute()
drop procedure MyProcedure;
drop table person;
create table person(id number primary key, name char(20), age number);
create or replace procedure MyProcedure (pid in number,name in char,age in char)
as
begin
insert into person values(pid,name,age);
end;
/import java.sql.*;
public class CallStmt{
public static void main(String[] args){
Connection con = null;
CallableStatement cst = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:dbrbh";
con = DriverManager.getConnection(url,"scott","tiger");
cst = con.prepareCall("{call MyProcedure(?,?,?)}");
cst.setInt(1,1001);
cst.setString(2,"Jenny");
cst.setInt(3,48);
cst.execute();
cst.close();
}catch(Exception e){
System.err.println(e);
}finally{
try{
if(con != null){
con.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
}
}
事务处理
和数据库中的事务管理模式相对应，JDBC中的Connection对象也可分为自动提交和非自动提交两种模式。
JDBC驱动程序的默认事务管理模式为"自动提交"。
Connection接口提供的事务处理相关方法：
void setAutoCommit(boolean autoCommit)
boolean getAutoCommit()
void commit()
void rollback()
import java.sql.*;
public class TestCommit{
public static void main(String args[]){
Connection conn = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url="jdbc:oracle:thin:@127.0.0.1:1521:dbrbh";
conn=DriverManager.getConnection(url,"scott","tiger");
boolean autoCommit = conn.getAutoCommit();
// 关闭自动提交功能
conn.setAutoCommit(false);
Statement stmt=conn.createStatement();
stmt.executeUpdate("insert into dept values (77,'Market','Beijing')");
stmt.executeUpdate("insert into dept values (88,'R&D','Shanghai')");
ResultSet rs=stmt.executeQuery("select * from dept");
while(rs.next()){
System.out.print("DeptNo: "+rs.getInt(1));
System.out.print("\tDeptName: "+rs.getString(2));
System.out.println("\tLOC: "+rs.getString(3));
}
// 提交事务
conn.commit();
// 恢复原来的提交模式
conn.setAutoCommit(autoCommit);
stmt.close();
}catch(Exception e){
System.out.println("操作失败、任务撤消！");
try{
// 回滚、取消前述操作
conn.rollback();
}catch(Exception e1){
e.printStackTrace();
}
}finally{
try{
if(conn != null){
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
}
}事务处理之部分回滚
JDBC3.0开始支持在事务中使用保存点（Savepoint），以实现对数据库事务的进一步控制、即支持部分回滚功能。
java.sql.Savepoint接口表示数据库事务中的保存点。
在Connection对象的rollback()方法中可以对当前事务中的保存点进行引用，从而将事务回滚到该保存点。
drop table teacher;
create table teacher(tid number(6), name char(20), salary number(7,2));import java.sql.*;
public class TestSavepoint{
public static void main(String[] args){
Connection conn = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:dbrbh";
conn = DriverManager.getConnection(url,"scott","tiger");
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.executeUpdate("insert into teacher values(11,'Tom',2500)");
stmt.executeUpdate("insert into teacher values(12,'John',3400)");
Savepoint sp1 = conn.setSavepoint("p1");
stmt.executeUpdate("insert into teacher values(13,'Billy',3150)");
Savepoint sp2 = conn.setSavepoint("p2");
stmt.executeUpdate("update teacher set salary = salary+8888 where tid = 12");
ResultSet rs = stmt.executeQuery("select avg(salary) from teacher");
rs.next();
double avg_sal = rs.getDouble(1);
if(avg_sal > 4000){
conn.rollback(sp1);
}else if(avg_sal >= 3000){
conn.rollback(sp2);
}
conn.commit();
rs = stmt.executeQuery("select * from teacher");
while(rs.next()){
System.out.println(rs.getInt(1) + "\t" + rs.getString(2).trim() + "\t" + rs.getDouble(3));
}
rs.close();
stmt.close();
}catch(Exception e){
System.out.println("Failure,rollback!");
try{
conn.rollback();
}catch(Exception e1){
e.printStackTrace();
}
e.printStackTrace();
}finally{
try{
if(conn != null){
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
}
}
批处理
JDBC2.0开始提供了对数据库操作的批处理（Batch Processing）功能，使用批处理功能避免了向数据库进行一连串的调用，从而可以显著提高程序的运行效率。
Statement接口提供的批处理相关方法：
void addBatch(String sql)
int[] executeBatch()
void clearBatch()
drop table teacher;
create table teacher(tid number(6), name char(20), salary number(7,2));import java.sql.*;
public class TestBatch{
public static void main(String[] args){
Connection conn = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:dbrbh";
conn = DriverManager.getConnection(url,"scott","tiger");
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.addBatch("insert into teacher values(11,'Tom',2500)");
stmt.addBatch("insert into teacher values(12,'John',3400)");
stmt.addBatch("insert into teacher values(13,'Billy',3150)");
stmt.addBatch("update teacher set salary = salary + 88");
stmt.executeBatch();
conn.commit();
ResultSet rs = stmt.executeQuery("select * from teacher");
while(rs.next()){
System.out.println(rs.getInt(1) + "\t" + rs.getString(2).trim() + "\t" + rs.getDouble(3));
}
rs.close();
stmt.close();
}catch(Exception e){
System.out.println("Failure,rollback!");
try{
conn.rollback();
}catch(Exception e1){
e.printStackTrace();
}
e.printStackTrace();
}finally{
try{
if(conn != null){
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
}
}
高级SQL类型BLOB/CLOB
高级SQL类型BLOB/CLOB
BLOB（Binary Large OBject，二进制大对象类型用于保存大规模的二进制数据
CLOB（Character Large OBject，文本大对象）类型则用于保存大规模的文本数据
JDBC2.0开始引入了对应于SQL99标准的多种高级数据类型，其中最重要的是两种大对象类型BLOB和CLOB。
使用BLOB类型
drop table Student_List;
create table Student_List(
Student_ID varchar2(20) primary key,
Student_Name varchar2(20) not null,
Student_Pic BLOB
);import java.sql.*;
import java.io.*;
public class SavePicture{
public static void main(String[] args){
Connection conn = null;
PreparedStatement stmt = null;
FileInputStream fis = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521dbrbh";
conn = DriverManager.getConnection(url, "scott","tiger");
String sql = "insert into Student_List values(?,?,?)";
stmt=conn.prepareStatement(sql);
stmt.setString(1,"s01");
stmt.setString(2,"Youyou");
File file = new File("girl.jpg");
fis = new FileInputStream(file);
stmt.setBinaryStream(3, fis, (int)file.length());
stmt.executeUpdate();
stmt.close();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(fis!=null){
fis.close();
}
}catch(IOException ioe){
ioe.printStackTrace();
}
try{
if(conn != null){
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
}
}import java.sql.*;
import java.io.*;
public class GetPicture{
public static void main(String[] args){
PreparedStatement stmt = null;
ResultSet rs = null;
Connection conn = null;
FileOutputStream fos = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url= "jdbc:oracle:thin:@localhost:1521:dbrbh";
conn = DriverManager.getConnection(url,"scott","tiger");
String sql="select * from Student_List where Student_ID='s01'";
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
rs.next();
File file = new File("d:\\kk.jpg");
fos = new FileOutputStream(file);
InputStream is = rs.getBinaryStream(3);
int len = 0;
byte b[] = new byte[4*1024];
while((len=is.read(b))!=-1)
{
fos.write(b,0,len);
}
fos.flush();
fos.close();
is.close();
rs.close();
stmt.close();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(fos!=null){
fos.close();
}
}catch(IOException ioe){
ioe.printStackTrace();
}
try{
if(conn != null){
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
}
}
使用CLOB类型
drop table book_list;
create table book_list(
bid varchar2(20) primary key,
name varchar2(40) not null,
content CLOB
);import java.sql.*;
import java.io.*;
public class SaveClob{
public static void main(String[] args){
Connection conn = null;
PreparedStatement stmt = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@localhost:1521:dbrbh";
conn = DriverManager.getConnection(url, "scott","tiger");
String sql = "insert into book_list values(?,?,?)";
stmt=conn.prepareStatement(sql);
stmt.setString(1,"b001");
stmt.setString(2,"99个简单法则");
BufferedReader br = new BufferedReader(new FileReader("a.txt"));
StringBuffer sb = new StringBuffer();
String s;
while((s=br.readLine()) != null){
sb.append(s + "\n");
}
br.close();
String content = sb.toString();
StringReader sr = new StringReader(content);
stmt.setCharacterStream(3, sr, content.length());
stmt.executeUpdate();
sr.close();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(conn != null){
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
}
}import java.sql.*;
import java.io.*;
public class GetClob{
public static void main(String[] args){
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
FileOutputStream fos = null;
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
String url= "jdbc:oracle:thin:@localhost:1521:dbrbh";
conn = DriverManager.getConnection(url,"scott","tiger");
String sql="select * from book_list where bid='b001'";
stmt = conn.prepareStatement(sql);
rs = stmt.executeQuery();
rs.next();
StringBuffer sb = new StringBuffer();
Reader rd = rs.getCharacterStream(3);
BufferedReader br = new BufferedReader(rd);
String s;
while((s=br.readLine())!=null)
{
sb.append(s + "\n");
}
System.out.println(sb.toString());
rs.close();
br.close();
stmt.close();
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if(conn != null){
conn.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
}
}