JDBC流ASCII和二進位制資料


PreparedStatement物件可以使用輸入和輸出流來提供引數資料。能夠將整個檔案放入可以容納大值的資料庫列,例如CLOBBLOB資料型別。

有以下方法可用於流式傳輸資料 -

  • setAsciiStream():此方法用於提供大的ASCII值。
  • setCharacterStream():此方法用於提供較大的UNICODE值。
  • setBinaryStream():此方法用於提供較大的二進位制值。

setXXXStream()方法除了引數預留位置之外還需要額外的引數和檔案大小。此引數通知驅動程式使用流向資料庫傳送多少資料。

範例

考慮要將XML檔案xml_data.xml上傳到資料庫表中。下面是XML檔案的內容 -

<?xml version="1.0"?>
<Employee>
    <id>125</id>
    <first>Max</first>
    <last>Su</last>
    <Salary>18000</Salary>
    <Dob>18-08-1978</Dob>
<Employee>

將此XML檔案儲存在要執行此範例的同一目錄中。

此範例將在資料庫建立一個表:xml_data,然後將檔案xml_data.xml上傳到此表中。

複製以下範例程式碼,並儲存在檔案:StreamingData.java 中,編譯並執行如下 -

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

public class StreamingData {
   // 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 = "root";
   static final String PASS = "123456";

   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,125);
      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=125";
      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

編譯上面程式碼,如下 -

F:\worksp\jdbc>javac -Djava.ext.dirs=F:\worksp\jdbc\libs StreamingData.java

執行上面編譯後的程式碼,得到以下結果 -

F:\worksp\jdbc>java -Djava.ext.dirs=F:\worksp\jdbc\libs StreamingData
Connecting to database...
Thu Jun 01 21:42:00 CST 2017 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.
Creating XML_Data table...
<?xml version="1.0"?>
<Employee>
        <id>125</id>
        <first>Max</first>
        <last>Su</last>
        <Salary>18000</Salary>
        <Dob>18-08-1978</Dob>
<Employee>
Goodbye!

F:\worksp\jdbc>

在執行上面語句後,將在資料庫:emp下建立一個名稱為:xml_data的表,現在查詢xml_data表中的資料,如下所示 -

mysql> select * from xml_data;
+-----+-----------------------------------------------------------------------------------------------------------------------------------+
| id  | Data                                                                                                                              |
+-----+-----------------------------------------------------------------------------------------------------------------------------------+
| 125 | <?xml version="1.0"?>
<Employee>
    <id>125</id>
    <first>Max</first>
    <last>Su</last>
    <Salary>18000</Salary>
    <Dob>18-08-1978</Dob>
<Employee> |
+-----+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set

mysql>