PreparedStatement对象能够使用输入和输出流来提供参数数据.这使您可以将整个文件放入可以
保存大值的数据库列中,例如CLOB和BLOB数据类型.
有以下方法,可用于流data :
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:\>