JDBC ASCII和二进制数据

JDBC教程 - JDBC ASCII和二进制数据

我们可以使用PreparedStatement对象将图像文件，doc文件或其他二进制数据保存到具有CLOB和BLOB数据类型列的数据库表中。

setAsciiStream()保存大的ASCII值。

setCharacterStream()保存大的UNICODE值。

setBinaryStream()保存大二进制值。

例子

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {
  static final String JDBC_DRIVER = "org.hsqldb.jdbcDriver";
  static final String DB_URL = "jdbc:hsqldb:mem:db_file";
  static final String USER = "sa";
  static final String PASS = "";

  public static void main(String[] args) throws Exception {
    Connection conn = null;
    PreparedStatement pstmt = null;
    Statement stmt = null;
    ResultSet rs = null;
    Class.forName(JDBC_DRIVER);
    conn = DriverManager.getConnection(DB_URL, USER, PASS);
    stmt = conn.createStatement();
    createXMLTable(stmt);
    File f = new File("build.xml");
    long fileLength = f.length();
    FileInputStream fis = new FileInputStream(f);
    String SQL = "INSERT INTO XML_Data VALUES (?,?)";
    pstmt = conn.prepareStatement(SQL);
    pstmt.setInt(1, 100);
    pstmt.setAsciiStream(2, fis, (int) fileLength);
    pstmt.execute();
    fis.close();
    SQL = "SELECT Data FROM XML_Data WHERE id=100";
    rs = stmt.executeQuery(SQL);
    if (rs.next()) {
      InputStream xmlInputStream = rs.getAsciiStream(1);
      int c;
      ByteArrayOutputStream bos = new ByteArrayOutputStream();
      while ((c = xmlInputStream.read()) != -1)
        bos.write(c);
      System.out.println(bos.toString());
    }
    rs.close();
    stmt.close();
    pstmt.close();
    conn.close();
  }

  public static void createXMLTable(Statement stmt) throws SQLException {
    String streamingDataSql = "CREATE TABLE XML_Data (id INTEGER, Data CLOB)";
       // stmt.executeUpdate("DROP TABLE XML_Data");
    stmt.executeUpdate(streamingDataSql);
  }
}