PreparedStatement
對(duì)象可以使用輸入和輸出流來(lái)提供參數(shù)數(shù)據(jù)。能夠?qū)⒄麄€(gè)文件放入可以容納大值的數(shù)據(jù)庫(kù)列,例如CLOB
和BLOB
數(shù)據(jù)類型。
有以下方法可用于流式傳輸數(shù)據(jù) -
setAsciiStream()
:此方法用于提供大的ASCII值。setCharacterStream()
:此方法用于提供較大的UNICODE值。setBinaryStream()
:此方法用于提供較大的二進(jìn)制值。setXXXStream()
方法除了參數(shù)占位符之外還需要額外的參數(shù)和文件大小。此參數(shù)通知驅(qū)動(dòng)程序使用流向數(shù)據(jù)庫(kù)發(fā)送多少數(shù)據(jù)。
考慮要將XML文件xml_data.xml
上傳到數(shù)據(jù)庫(kù)表中。下面是XML文件的內(nèi)容 -
<?xml version="1.0"?>
<Employee>
<id>125</id>
<first>Max</first>
<last>Su</last>
<Salary>18000</Salary>
<Dob>18-08-1978</Dob>
<Employee>
將此XML文件保存在要運(yùn)行此示例的同一目錄中。
此示例將在數(shù)據(jù)庫(kù)創(chuàng)建一個(gè)表:xml_data,然后將文件xml_data.xml
上傳到此表中。
復(fù)制以下示例代碼,并保存在文件:StreamingData.java 中,編譯并運(yùn)行如下 -
// 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
執(zhí)行上面編譯后的代碼,得到以下結(jié)果 -
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>
在執(zhí)行上面語(yǔ)句后,將在數(shù)據(jù)庫(kù):emp
下創(chuàng)建一個(gè)名稱為:xml_data
的表,現(xiàn)在查詢xml_data
表中的數(shù)據(jù),如下所示 -
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>