- PreparedStatement对象可以使用输入和输出流来提供参数数据。能够将整个文件放入可以容纳大值的数据库列,例如CLOB和BLOB数据类型。
- 有以下方法可用于流式传输数据:
- 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中:
package com.geeklicreed.jdbc; //Import required packages import java.sql.*; import java.io.*; 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 = "02000059"; 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
- 编译和执行此java文件后,得到以下结果:
Connecting to database... Creating XML_Data table... <?xml version="1.0" encoding="UTF-8"?> <Employee> <id>125</id> <first>Max</first> <last>Su</last> <Salary>18000</Salary> <Dob>18-09-1978</Dob> </Employee> Goodbye!
- 在执行上面语句后,将在数据库:emp下创建一个名称为:xml_data的表,现在查询xml_data表中的数据,如下所示: