Java Tutorial/Database/Blob Clob

Blob and Clob data type

import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.io.Reader;
import java.io.StringWriter;
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 BlobClobEx {
  public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver").newInstance();
    Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "yourName", "mypwd");
    Statement stmt = conn.createStatement();
    createBlobClobTables(stmt);
    PreparedStatement pstmt = conn.prepareStatement("INSERT INTO BlobClob VALUES(40,?,?)");
    File file = new File("blob.txt");
    FileInputStream fis = new FileInputStream(file);
    pstmt.setBinaryStream(1, fis, (int) file.length());
    file = new File("clob.txt");
    fis = new FileInputStream(file);
    pstmt.setAsciiStream(2, fis, (int) file.length());
    fis.close();
    pstmt.execute();
    ResultSet rs = stmt.executeQuery("SELECT * FROM BlobClob WHERE id = 40");
    rs.next();
    java.sql.Blob blob = rs.getBlob(2);
    java.sql.Clob clob = rs.getClob(3);
    byte blobVal[] = new byte[(int) blob.length()];
    InputStream blobIs = blob.getBinaryStream();
    blobIs.read(blobVal);
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    bos.write(blobVal);
    blobIs.close();
    char clobVal[] = new char[(int) clob.length()];
    Reader r = clob.getCharacterStream();
    r.read(clobVal);
    StringWriter sw = new StringWriter();
    sw.write(clobVal);
    r.close();
    conn.close();
  }
  public static void createBlobClobTables(Statement stmt) throws Exception {
    String Sql = "CREATE TABLE BlobClob(Id NUMBER(3), b BLOB, c CLOB)";
    try {
      stmt.executeUpdate("DROP TABLE BlobClob");
    } catch (SQLException se) {
      if (se.getErrorCode() == 942)
        System.out.println("Error dropping BlobClob table:" + se.getMessage());
    }
    if (stmt.executeUpdate(Sql) == 0)
      System.out.println("BlobClob table created...");
  }
}

Getting and Inserting Binary Data into an Database Table

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    String sql = "INSERT INTO mysql_all_table (col_binarystream) VALUES(?)";
    PreparedStatement pstmt = connection.prepareStatement(sql);
    byte[] buffer = "some data".getBytes();
    pstmt.setBytes(1, buffer);
    pstmt.executeUpdate();
    pstmt.close();
    Statement stmt = connection.createStatement();
    ResultSet resultSet = stmt.executeQuery("SELECT * FROM mysql_all_table");
    while (resultSet.next()) {
      byte[] bytes = resultSet.getBytes("col_binarystream");
    }
  }
}

Getting BLOB Data from a Database Table: how to retrieves bytes from a BLOB.

import java.io.InputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "com.jnetdirect.jsql.JSQLDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1433";
    String mydatabase = serverName + ":" + portNumber;
    String url = "jdbc:JSQLConnect://" + mydatabase;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT col_blob FROM mysql_all_table");
    if (rs.next()) {
      Blob blob = rs.getBlob("col_blob");
      long blobLength = blob.length();
      int pos = 1; // position is 1-based
      int len = 10;
      byte[] bytes = blob.getBytes(pos, len);
      InputStream is = blob.getBinaryStream();
      int b = is.read();
    }
  }
}

Insert an Image

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
  public static void main(String[] argv) throws Exception {
    File file = new File("myimage.gif");
    FileInputStream fis = new FileInputStream(file);
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(
        "jdbc:oracle:thin:@//server.local:1521/prod", "scott", "tiger");
    conn.setAutoCommit(false);
    PreparedStatement ps = conn
        .prepareStatement("insert into images values (?,?)");
    ps.setString(1, file.getName());
    ps.setBinaryStream(2, fis, (int) file.length());
    ps.executeUpdate();
    ps.close();
    fis.close();
  }
}

Read BLOBs data from database

import java.io.File;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Main {
  static String url = "jdbc:oracle:thin:@localhost:1521:javaDemo";
  static String username = "username";
  static String password = "welcome";
  public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(url, username, password);
    String sql = "SELECT name, description, image FROM pictures ";
    PreparedStatement stmt = conn.prepareStatement(sql);
    ResultSet resultSet = stmt.executeQuery();
    while (resultSet.next()) {
      String name = resultSet.getString(1);
      String description = resultSet.getString(2);
      File image = new File("D:\\java.gif");
      FileOutputStream fos = new FileOutputStream(image);
      byte[] buffer = new byte[1];
      InputStream is = resultSet.getBinaryStream(3);
      while (is.read(buffer) > 0) {
        fos.write(buffer);
      }
      fos.close();
    }
    conn.close();
  }
}

Read CLOBs data from database

import java.io.File;
import java.io.FileWriter;
import java.io.Reader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class Main {
  private static String url = "jdbc:oracle:thin:@localhost:1521:javaDemo";
  private static String username = "java";
  private static String password = "welcome";
  public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(url, username, password);
    PreparedStatement stmt = conn.prepareStatement("SELECT name, description, data FROM documents ");
    ResultSet resultSet = stmt.executeQuery();
    while (resultSet.next()) {
      String name = resultSet.getString(1);
      String description = resultSet.getString(2);
      File data = new File("C:\\a.txt");
      Reader reader = resultSet.getCharacterStream(3);
      FileWriter writer = new FileWriter(data);
      char[] buffer = new char[1];
      while (reader.read(buffer) > 0) {
        writer.write(buffer);
      }
      writer.close();
    }
    conn.close();
  }
}

Retrieve an Image

import java.awt.Image;
import java.awt.Toolkit;
import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.sql.ResultSet;
public class Main {
  public static void main(String[] argv) throws Exception {
    ResultSet rset = null;
    InputStream stream = rset.getBinaryStream(1);
    ByteArrayOutputStream output = new ByteArrayOutputStream();
    int a1 = stream.read();
    while (a1 >= 0) {
      output.write((char) a1);
      a1 = stream.read();
    }
    Image myImage = Toolkit.getDefaultToolkit().createImage(output.toByteArray());
    output.close();
  }
}

Store BLOBs data into database

import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
  static String url = "jdbc:oracle:thin:@localhost:1521:javaDemo";
  static String username = "username";
  static String password = "welcome";
  public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(url, username, password);
    conn.setAutoCommit(false);
    String sql = "INSERT INTO pictures (name, description, image) VALUES (?, ?, ?)";
    PreparedStatement stmt = conn.prepareStatement(sql);
    stmt.setString(1, "java.gif");
    stmt.setString(2, "Java Official Logo");
    File image = new File("D:\\a.gif");
    FileInputStream   fis = new FileInputStream(image);
    stmt.setBinaryStream(3, fis, (int) image.length());
    stmt.execute();
    conn.rumit();
    fis.close();
    conn.close();
  }
}

Store CLOBs data into database?

import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
public class Main {
  private static String url = "jdbc:oracle:thin:@localhost:1521:xe";
  private static String username = "yourDatabase";
  private static String password = "welcome";
  public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(url, username, password);
    conn.setAutoCommit(false);
    String sql = "INSERT INTO documents (name, description, data) VALUES (?, ?, ?)";
    PreparedStatement stmt = conn.prepareStatement(sql);
    stmt.setString(1, "a.txt");
    stmt.setString(2, "b");
    File data = new File("C:\\a.txt");
    FileReader reader = new FileReader(data);
    stmt.setCharacterStream(3, reader, (int) data.length());
    stmt.execute();
    conn.rumit();
    reader.close();
    conn.close();
  }
}