JDBC（五）

如果JDBC连接是在自动提交模式下，它在默认情况下，那么每个SQL语句都是在其完成时提交到数据库。

这可能是对简单的应用程序，但有三个原因，你可能想关闭自动提交和管理自己的事务：

• 为了提高性能

• 为了保持业务流程的完整性

• 使用分布式事务

若要控制事务，以及何时更改应用到数据库。它把单个SQL语句或一组SQL语句作为一个逻辑单元，而且如果任何语句失败，整个事务失败。

若要启用，而不是JDBC驱动程序默认使用auto-commit模式手动事务支持，使用Connection对象的的setAutoCommit()方法。如果传递一个布尔值false到setAutoCommit()，关闭自动提交。可以传递一个布尔值true将其重新打开。

例如，如果有一个名为conn Connection对象，以下代码来关闭自动提交：

conn.setAutoCommit(false);

提交和回滚

一旦已经完成了变化，要提交更改，然后调用commit（在连接对象）方法，如下所示：

conn.commit( );

否则回滚更新对数据库所做的使用命名连接conn，使用下面的代码：

conn.rollback( );

下面的例子演示了如何使用一个提交和回滚对象：

try{
   //Assume a valid connection object conn
   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();
   
   String SQL = "INSERT INTO Employees  " +
                "VALUES (106, 20, 'Rita', 'Tez')";
   stmt.executeUpdate(SQL);  
   //Submit a malformed SQL statement that breaks
   String SQL = "INSERTED IN Employees  " +
                "VALUES (107, 22, 'Sita', 'Singh')";
   stmt.executeUpdate(SQL);
   // If there is no error.
   conn.commit();
}catch(SQLException se){
   // If there is any error.
   conn.rollback();
}

在这种情况下没有上述INSERT语句会成功，一切都将被回滚。

为了更好地理解，建议学习事务提交实例代码.

使用保存点：

新的JDBC3.0保存点的接口提供了额外的事务控制。他们的环境中，如Oracle的PL/ SQL中的大多数现代的DBMS支持保存点。

当设置一个保存点在事务中定义一个逻辑回滚点。如果发生错误，过去一个保存点，则可以使用rollback方法来撤消要么所有的改变或仅保存点之后所做的更改。

Connection对象有两个新的方法，可帮助管理保存点：

• setSavepoint(String savepointName): 定义了一个新的保存点。它也返回一个Savepoint 对象。

• releaseSavepoint(Savepoint savepointName): 删除一个保存点。请注意，它需要一个Savepoint 对象作为参数。这个对象通常是由setSavepoint()方法生成一个保存点。

有一个rollback ( String savepointName ) 方法回滚工作到指定的保存点。 

下面的例子演示如何使用Savepoint对象：

try{
   //Assume a valid connection object conn
   conn.setAutoCommit(false);
   Statement stmt = conn.createStatement();
   
   //set a Savepoint
   Savepoint savepoint1 = conn.setSavepoint("Savepoint1");
   String SQL = "INSERT INTO Employees " +
                "VALUES (106, 20, 'Rita', 'Tez')";
   stmt.executeUpdate(SQL);  
   //Submit a malformed SQL statement that breaks
   String SQL = "INSERTED IN Employees " +
                "VALUES (107, 22, 'Sita', 'Tez')";
   stmt.executeUpdate(SQL);
   // If there is no error, commit the changes.
   conn.commit();

}catch(SQLException se){
   // If there is any error.
   conn.rollback(savepoint1);
}

在这种情况下没有上述INSERT语句会成功，一切都将被回滚。

异常处理，可以处理在一个受控制的方式异常情况，如程序定义的错误。

当异常情况发生时，将引发异常。抛出这个词意味着当前执行的程序停止，并控制被重定向到最近的适用的catch子句。如果没有适用 catch 子句存在，那么程序的执行结束。

JDBC的异常处理非常类似于Java Excpetion处理，但对于JDBC，最常见的异常处理的是 java.sql.SQLException.

SQLException 方法:

SqlException异常可以在驱动程序和数据库出现在这两种。当出现这样的异常时，抛出：SQLException类型的对象将被传递到catch子句。

传递的SQLException对象具有可用于检索有关异常的附加信息下面的方法：

方法	描述
getErrorCode( )	获取与异常关联的错误号。
getMessage( )	获取JDBC驱动程序的错误消息由驱动程序处理错误或获取Oracle错误号和消息的一个数据库错误。
getSQLState( )	获取XOPEN SQLSTATE字符串。对于JDBC驱动程序的错误，没有有用的信息从该方法返回。对于一个数据库错误，则返回五位XOPEN SQLSTATE代码。这种方法可以返回null。
getNextException( )	获取异常链的下一个Exception对象。
printStackTrace( )	打印当前的异常，或者抛出，其回溯到标准错误流。
printStackTrace(PrintStream s)	打印此抛出，其回溯到指定的打印流。
printStackTrace(PrintWriter w)	打印此抛出，其回溯到指定的打印写入。

通过利用可从Exception对象捕获异常的信息，并适当地继续运行程序。这里是一个try块的一般形式为：

try {
   // Your risky code goes between these curly braces!!!
}
catch(Exception ex) {
   // Your exception handling code goes between these 
   // curly braces, similar to the exception clause 
   // in a PL/SQL block.
}
finally {
   // Your must-always-be-executed code goes between these 
   // curly braces. Like closing database connection.
}

例如：

学习下面的代码示例来了解试试 try....catch...finally 块的使用。

//STEP 1. Import required packages
import java.sql.*;

public class JDBCExample {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
   static final String DB_URL = "jdbc:mysql://localhost/EMP";

   //  Database credentials
   static final String USER = "username";
   static final String PASS = "password";
   
   public static void main(String[] args) {
   Connection conn = null;
   try{
      //STEP 2: Register JDBC driver
      Class.forName("com.mysql.jdbc.Driver");

      //STEP 3: Open a connection
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL,USER,PASS);

      //STEP 4: Execute a query
      System.out.println("Creating statement...");
      Statement stmt = conn.createStatement();
      String sql;
      sql = "SELECT id, first, last, age FROM Employees";
      ResultSet rs = stmt.executeQuery(sql);

      //STEP 5: Extract data from result set
      while(rs.next()){
         //Retrieve by column name
         int id  = rs.getInt("id");
         int age = rs.getInt("age");
         String first = rs.getString("first");
         String last = rs.getString("last");

         //Display values
         System.out.print("ID: " + id);
         System.out.print(", Age: " + age);
         System.out.print(", First: " + first);
         System.out.println(", Last: " + last);
      }
      //STEP 6: Clean-up environment
      rs.close();
      stmt.close();
      conn.close();
   }catch(SQLException se){
      //Handle errors for JDBC
      se.printStackTrace();
   }catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
   }finally{
      //finally block used to close resources
      try{
         if(conn!=null)
            conn.close();
      }catch(SQLException se){
         se.printStackTrace();
      }//end finally try
   }//end try
   System.out.println("Goodbye!");
}//end main
}//end JDBCExample

现在让我们来编译上面的例子如下：

C:>javac JDBCExample.java
C:>

当运行JDBCExample，如果没有问题它会产生以下结果，否则相应的错误将被捕获并会显示错误消息：

C:>java JDBCExample
Connecting to database...
Creating statement...
ID: 100, Age: 18, First: Zara, Last: Ali
ID: 101, Age: 25, First: Mahnaz, Last: Fatma
ID: 102, Age: 30, First: Zaid, Last: Khan
ID: 103, Age: 28, First: Sumit, Last: Mittal
C:>

试试上面的例子中通过传递错误的数据库名称或错误的用户名或密码，并检查结果。

批处理允许将相关的SQL语句组合成一个批处理和一个调用数据库提交。

当一次发送多个SQL语句到数据库，可以减少通信开销的数额，从而提高了性能。

• JDBC驱动程序不需要支持此功能。应该使用DatabaseMetaData.supportsBatchUpdates()方法来确定目标数据库支持批量更新处理。如果你的JDBC驱动程序支持此功能的方法返回true。

• 声明addBatch()方法，PreparedStatement和CallableStatement用于各个语句添加到批处理。executeBatch()将用于启动所有组合在一起的语句的执行。

• executeBatch()将返回一个整数数组，数组中的每个元素代表了各自的更新语句的更新计数。

• 可以添加语句批量处理，可以用theclearBatch()方法删除它们。此方法删除所有已添加的addBatch()方法的语句。但是，不能有选择性地选择要删除的语句。

批处理和Statement对象：

下面是步骤，使用批处理使用说明书对象的典型顺序：

• 使用createStatement()方法创建一个Statement对象。

• 设置使用自动提交为false，使用 setAutoCommit().

• 添加任意多个到批量使用addBatch SQL语句（上创建语句对象）的方法。

• 执行使用executeBatch()将方法上创建表对象中的所有SQL语句。

• 最后，提交使用commit()方法的所有更改。

例如：

下面的代码段提供了使用Statement对象批量更新中的一个例子：

// Create statement object
Statement stmt = conn.createStatement();

// Set auto-commit to false
conn.setAutoCommit(false);

// Create SQL statement
String SQL = "INSERT INTO Employees (id, first, last, age) " +
             "VALUES(200,'Zia', 'Ali', 30)";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);

// Create one more SQL statement
String SQL = "INSERT INTO Employees (id, first, last, age) " +
             "VALUES(201,'Raj', 'Kumar', 35)";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);

// Create one more SQL statement
String SQL = "UPDATE Employees SET age = 35 " +
             "WHERE id = 100";
// Add above SQL statement in the batch.
stmt.addBatch(SQL);

// Create an int[] to hold returned values
int[] count = stmt.executeBatch();

//Explicitly commit statements to apply changes
conn.commit();

为了更好地理解，建议学习研究JDBC批处理用Statement对象示例代码.

批处理使用prepareStatement结果对象：

下面是步骤，使用批处理用prepareStatement结果对象的典型顺序：

• 创建SQL语句的占位符。

• 使用任一prepareStatement()方法创建prepareStatement结果对象。

• 设置使用setAutoCommit()自动提交为false。

• 添加任意多个批量使用addBatch SQL语句（上创建语句对象）的方法。

• 执行使用executeBatch()将方法上创建表对象中的所有SQL语句。

• 最后，提交使用commit()方法的所有更改。

下面的代码段提供了使用prepareStatement结果对象批量更新的一个例子：

// Create SQL statement
String SQL = "INSERT INTO Employees (id, first, last, age) " +
             "VALUES(?, ?, ?, ?)";

// Create PrepareStatement object
PreparedStatemen pstmt = conn.prepareStatement(SQL);

//Set auto-commit to false
conn.setAutoCommit(false);

// Set the variables
pstmt.setInt( 1, 400 );
pstmt.setString( 2, "Pappu" );
pstmt.setString( 3, "Singh" );
pstmt.setInt( 4, 33 );
// Add it to the batch
pstmt.addBatch();

// Set the variables
pstmt.setInt( 1, 401 );
pstmt.setString( 2, "Pawan" );
pstmt.setString( 3, "Singh" );
pstmt.setInt( 4, 31 );
// Add it to the batch
pstmt.addBatch();

//add more batches
.
.
.
.
//Create an int[] to hold returned values
int[] count = stmt.executeBatch();

//Explicitly commit statements to apply changes
conn.commit();

为了更好地理解，建议学习研究实例代码.

在前而我们已经学习了如何使用使用JDBC存储过程，JDBC Statements。本教程是类似，但它会将有关JDBC的SQL转义语法的附加信息。

正如一个Connection对象创建Statement和PreparedStatement对象，它也创造了CallableStatement对象这将被用来执行调用数据库存储过程。

创建CallableStatement对象：

假设，需要执行以下Oracle存储过程：

CREATE OR REPLACE PROCEDURE getEmpName 
   (EMP_ID IN NUMBER, EMP_FIRST OUT VARCHAR) AS
BEGIN
   SELECT first INTO EMP_FIRST
   FROM Employees
   WHERE ID = EMP_ID;
END;

注意: 上面已经写过Oracle存储过程，但我们正在使用MySQL数据库，写相同的存储过程对于MySQL如下，以EMP数据库中创建它：

DELIMITER $$

DROP PROCEDURE IF EXISTS `EMP`.`getEmpName` $$
CREATE PROCEDURE `EMP`.`getEmpName` 
   (IN EMP_ID INT, OUT EMP_FIRST VARCHAR(255))
BEGIN
   SELECT first INTO EMP_FIRST
   FROM Employees
   WHERE ID = EMP_ID;
END $$

DELIMITER ;

三种类型的参数有：IN，OUT和INOUT。PreparedStatement对象只使用IN参数。 CallableStatement对象可以使用所有的三个。

这里是每个定义：

参数	描述
IN	它的值是在创建SQL语句时未知的参数。将值绑定到setXXX（）方法的参数。
OUT	其值是由它返回的SQL语句提供的参数。你从OUT参数的getXXX（）方法检索值。
INOUT	同时提供输入和输出值的参数。绑定setXXX（）方法的变量，并与getXXX（）方法检索值。

下面的代码片段显示了如何使用该Connection.prepareCall（）方法实例化基于上述存储过程CallableStatement对象：

CallableStatement cstmt = null;
try {
   String SQL = "{call getEmpName (?, ?)}";
   cstmt = conn.prepareCall (SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   . . .
}

String变量的SQL表示存储过程，使用参数占位符。

使用CallableStatement对象是使用PreparedStatement对象。必须将值绑定到所有的参数执行该语句之前，否则将收到一个SQLException。

如果有IN参数，只要按照适用于PreparedStatement对象相同的规则和技巧;使用对应于要绑定Java数据类型的setXXX（）方法。

当使用OUT和INOUT参数就必须采用额外CallableStatement方法的registerOutParameter（）。registerOutParameter（）方法JDBC数据类型绑定到数据类型的存储过程返回。

一旦调用存储过程，用getXXX（）方法的输出参数检索值。这种方法投射SQL类型的值检索到Java数据类型。

关闭CallableStatement 对象：

正如关闭其他Statement对象，出于同样的原因，也应该关闭CallableStatement对象。

close（）方法简单的调用将完成这项工作。如果关闭了Connection对象首先它会关闭CallableStatement对象为好。然而，应该始终明确关闭的CallableStatement对象，以确保正确的清除。

CallableStatement cstmt = null;
try {
   String SQL = "{call getEmpName (?, ?)}";
   cstmt = conn.prepareCall (SQL);
   . . .
}
catch (SQLException e) {
   . . .
}
finally {
   cstmt.close();
}

为了更好地理解，建议研究学习Callable实例代码.

JDBC的SQL转义语法：

转义语法使能够使用通过使用标准的JDBC方法和属性，无法使用数据库的某些特性的灵活性。

一般的SQL转义语法格式如下：

{keyword 'parameters'}

这里有以下这些，会发现非常有用的，而这样做的JDBC编程的转义序列：

d, t, ts 关键字:

他们帮助确定日期，时间和时间戳记文字。如所知，没有两个数据库管理系统是基于时间和日期的方式相同。此转义语法告诉驱动程序呈现在目标数据库的格式，日期或时间。实现例子：

{d 'yyyy-mm-dd'}

其中yyyy=年，mm =月，DD =日。使用这种语法 {d '2009-09-03'}是2009年3月9日。

下面是一个简单的例子说明如何插入日期表：

//Create a Statement object
stmt = conn.createStatement();
//Insert data ==> ID, First Name, Last Name, DOB
String sql="INSERT INTO STUDENTS VALUES" +
             "(100,'Zara','Ali', {d '2001-12-16'})";

stmt.executeUpdate(sql);

同样，可以使用以下两种语法之一，无论是 t 或 ts： 

{t 'hh:mm:ss'}

其中hh=小时，mm=分，ss=秒。使用此语法 {t '13:30:29'}是下午1点三十分29秒.

{ts 'yyyy-mm-dd hh:mm:ss'}

这是上述两种语法 'd' 和  't' 来表示时间戳结合语法。

escape 关键字:

该关键字标识LIKE子句中使用的转义字符。有用使用SQL通配符％，其中匹配零个或多个字符时。例如：

String sql = "SELECT symbol FROM MathSymbols
              WHERE symbol LIKE '\%' {escape ''}";
stmt.execute(sql);

如果使用反斜杠字符（）作为转义字符，还必须使用两个反斜杠字符在Java字符串字面，因为反斜杠也是一个Java转义字符。

fn 关键字:

此关键字代表在DBMS中使用标量函数。例如，可以使用SQL length函数计算GE字符串的长度：

{fn length('Hello World')}

这将返回11，字符串 'Hello World'的长度。.

call 关键字:

此关键字是用来调用存储过程。例如，对于一个存储过程，需要一个IN参数，请使用以下语法：

{call my_procedure(?)};

对于一个存储过程，需要一个IN参数并返回一个OUT参数，使用下面的语法：

{? = call my_procedure(?)};

oj 关键字:

此关键字用来表示外部联接。其语法如下：

{oj outer-join}

外连接表={LEFT| RIGHT| FULL}外连接{表|外连接}的搜索条件。例如：

String sql = "SELECT Employees 
              FROM {oj ThisTable RIGHT
              OUTER JOIN ThatTable on id = '100'}";
stmt.execute(sql);

PreparedStatement对象必须使用输入和输出流提供参数数据的能力。这使能够将整个文件到数据库列，可容纳较大的值，如CLOB和BLOB数据类型。

有下列方法，可用于将数据传送：

• setAsciiStream(): 这个方法是用来提供大的ASCII值。

• setCharacterStream(): 这个方法是用来提供大的UNICODE值。

• setBinaryStream(): 这个方法是用来提供大的二进制值。

setXXXStream()方法需要一个额外的参数，文件大小，除了参数占位符。这个参数通知驱动有多少数据要​​使用的流被发送到数据库中。

例子

考虑到要上传一个XML文件XML_Data.xml到数据库表。下面是这个XML文件的内容：

<?xml version="1.0"?>
<Employee>
<id>100</id>
<first>Zara</first>
<last>Ali</last>
<Salary>10000</Salary>
<Dob>18-08-1978</Dob>
<Employee>

保持这个XML文件在要运行这个例子相同的目录。

这个例子将创建一个数据库表XML_Data，然后提交XML_Data.xml将被上传到该表中。

复制下面的例子JDBCExample.java，编译并运行，如下所示：

// Import required packages
import java.sql.*;
import java.io.*;
import java.util.*;

public class JDBCExample {
   // JDBC driver name and database URL
   static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
   static final String DB_URL = "jdbc:mysql://localhost/EMP";

   //  Database credentials
   static final String USER = "username";
   static final String PASS = "password";
   
   public static void main(String[] args) {
   Connection conn = null;
   PreparedStatement pstmt = null;
   Statement stmt = null;
   ResultSet rs = null;
   try{
      // Register JDBC driver
      Class.forName("com.mysql.jdbc.Driver");

      // Open a connection
      System.out.println("Connecting to database...");
      conn = DriverManager.getConnection(DB_URL,USER,PASS);

      //Create a Statement object and build table
      stmt = conn.createStatement();
      createXMLTable(stmt);

      //Open a FileInputStream
      File f = new File("XML_Data.xml");
      long fileLength = f.length();
      FileInputStream fis = new FileInputStream(f);

      //Create PreparedStatement and stream data
      String SQL = "INSERT INTO XML_Data VALUES (?,?)";
      pstmt = conn.prepareStatement(SQL);
      pstmt.setInt(1,100);
      pstmt.setAsciiStream(2,fis,(int)fileLength);
      pstmt.execute();

      //Close input stream
      fis.close();

      // Do a query to get the row
      SQL = "SELECT Data FROM XML_Data WHERE id=100";
      rs = stmt.executeQuery (SQL);
      // Get the first row
      if (rs.next ()){
         //Retrieve data from input stream
         InputStream xmlInputStream = rs.getAsciiStream (1);
         int c;
         ByteArrayOutputStream bos = new ByteArrayOutputStream();
         while (( c = xmlInputStream.read ()) != -1)
            bos.write(c);
         //Print results
         System.out.println(bos.toString());
      }
      // Clean-up environment
      rs.close();
      stmt.close();
      pstmt.close();
      conn.close();
   }catch(SQLException se){
      //Handle errors for JDBC
      se.printStackTrace();
   }catch(Exception e){
      //Handle errors for Class.forName
      e.printStackTrace();
   }finally{
      //finally block used to close resources
      try{
         if(stmt!=null)
            stmt.close();
      }catch(SQLException se2){
      }// nothing we can do
      try{
         if(pstmt!=null)
            pstmt.close();
      }catch(SQLException se2){
      }// nothing we can do
      try{
         if(conn!=null)
            conn.close();
      }catch(SQLException se){
         se.printStackTrace();
      }//end finally try
   }//end try
   System.out.println("Goodbye!");
}//end main

public static void createXMLTable(Statement stmt) 
   throws SQLException{
   System.out.println("Creating XML_Data table..." );
   //Create SQL Statement
   String streamingDataSql = "CREATE TABLE XML_Data " +
                             "(id INTEGER, Data LONG)";
   //Drop table first if it exists.
   try{
      stmt.executeUpdate("DROP TABLE XML_Data");
   }catch(SQLException se){
   }// do nothing
   //Build table.
   stmt.executeUpdate(streamingDataSql);
}//end createXMLTable
}//end JDBCExample

现在让我们来编译上面的例子如下：

C:>javac JDBCExample.java
C:>

当运行JDBCExample，它会产生以下结果：

C:>java JDBCExample
Connecting to database...
Creating XML_Data table...
<?xml version="1.0"?>
<Employee>
<id>100</id>
<first>Zara</first>
<last>Ali</last>
<Salary>10000</Salary>
<Dob>18-08-1978</Dob>
<Employee>
Goodbye!
C:>