- 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表中的数据,如下所示:
转载于:https://blog.51cto.com/12402717/1980824