JDBC Tutorial - JDBC ASCII and Binary Data

• « Previous
• Next »

We can use a PreparedStatement object to save image files, doc files or other binary data into database table which has CLOB and BLOB data typed column.

setAsciiStream() saves large ASCII values.

setCharacterStream() saves large UNICODE values.

setBinaryStream() saves large binary values.

Example

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);
  }
}

• Next »
• « Previous