Java Tutorial/Database/Blob Clob

Blob and Clob data type

   <source lang="java">

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

}</source>

Getting and Inserting Binary Data into an Database Table

   <source lang="java">

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

}</source>

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

   <source lang="java">

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

}</source>

Insert an Image

   <source lang="java">

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

}</source>

Read BLOBs data from database

   <source lang="java">

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

}</source>

Read CLOBs data from database

   <source lang="java">

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

}</source>

Retrieve an Image

   <source lang="java">

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

}</source>

Store BLOBs data into database

   <source lang="java">

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

}</source>

Store CLOBs data into database?

   <source lang="java">

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

}</source>