Reading and Writing Blob and Clob Data Database Columns

Description

Demo Code

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.Reader;
import java.io.Writer;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Blob;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Main {
  public static void main(String[] args) {
    Connection conn = null;/*from w w  w  .j  av  a  2  s.  com*/
    try {
      conn = JDBCUtil.getConnection();
      String inPicturePath = "picture.jpg";
      String inResumePath = "resume.txt";

      try {
        insertPersonDetail(conn, 1, 101, inPicturePath, inResumePath);
        JDBCUtil.commit(conn);
      } catch (SQLException e) {
        System.out.print("Inserting person details failed: ");
        System.out.println(e.getMessage());
        JDBCUtil.rollback(conn);
      }
      String outPicturePath = "out_picture.jpg";
      String outResumePath = "out_resume.txt";

      try {
        retrievePersonDetails(conn, 1, outPicturePath, outResumePath);
        JDBCUtil.commit(conn);
      } catch (SQLException e) {
        System.out.print("Retrieving person details failed: ");
        System.out.println(e.getMessage());
        JDBCUtil.rollback(conn);
      }
    } catch (Exception e) {
      System.out.println(e.getMessage());
      JDBCUtil.rollback(conn);
    } finally {
      JDBCUtil.closeConnection(conn);
    }
  }

  public static void insertPersonDetail(Connection conn, int personDetailId,
      int personId, String pictureFilePath, String resumeFilePath)
      throws SQLException {

    String SQL = "insert into person_detail "
        + "(person_detail_id, person_id, picture, resume) " + "values "
        + "(?, ?, ?, ?)";

    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(SQL);
      pstmt.setInt(1, personDetailId);
      pstmt.setInt(2, personId);

      if (pictureFilePath != null) {
        Blob pictureBlob = conn.createBlob();
        readInPictureData(pictureBlob, pictureFilePath);
        pstmt.setBlob(3, pictureBlob);
      }

      if (resumeFilePath != null) {
        Clob resumeClob = conn.createClob();
        readInResumeData(resumeClob, resumeFilePath);
        pstmt.setClob(4, resumeClob);
      }

      pstmt.executeUpdate();
    } catch (IOException | SQLException e) {
      throw new SQLException(e);
    } finally {
      JDBCUtil.closeStatement(pstmt);
    }
  }

  public static void retrievePersonDetails(Connection conn, int personDetailId,
      String picturePath, String resumePath) throws SQLException {

    String SQL = "select person_id, picture, resume " + "from person_detail "
        + "where person_detail_id = ?";

    PreparedStatement pstmt = null;
    try {
      pstmt = conn.prepareStatement(SQL);
      pstmt.setInt(1, personDetailId);
      ResultSet rs = pstmt.executeQuery();

      while (rs.next()) {
        Blob pictureBlob = rs.getBlob("picture");
        if (pictureBlob != null) {
          savePicture(pictureBlob, picturePath);
          pictureBlob.free();
        }

        Clob resumeClob = rs.getClob("resume");
        if (resumeClob != null) {
          saveResume(resumeClob, resumePath);
          resumeClob.free();
        }
      }
    } catch (IOException | SQLException e) {
      throw new SQLException(e);
    } finally {
      JDBCUtil.closeStatement(pstmt);
    }
  }

  public static void readInPictureData(Blob pictureBlob, String pictureFilePath)
      throws FileNotFoundException, IOException, SQLException {

    int startPosition = 1;
    OutputStream out = pictureBlob.setBinaryStream(startPosition);

    FileInputStream fis = new FileInputStream(pictureFilePath);

    int b = -1;
    while ((b = fis.read()) != -1) {
      out.write(b);
    }

    fis.close();
    out.close();
  }

  public static void readInResumeData(Clob resumeClob, String resumeFilePath)
      throws FileNotFoundException, IOException, SQLException {

    int startPosition = 1; // start writing from the beginning
    Writer writer = resumeClob.setCharacterStream(startPosition);
    FileReader fr = new FileReader(resumeFilePath);

    int b = -1;
    while ((b = fr.read()) != -1) {
      writer.write(b);
    }
    fr.close();
    writer.close();
  }

  public static void savePicture(Blob pictureBlob, String filePath)
      throws SQLException, IOException {
    FileOutputStream fos = new FileOutputStream(filePath);
    InputStream in = pictureBlob.getBinaryStream();

    int b = -1;
    while ((b = in.read()) != -1) {
      fos.write((byte) b);
    }

    fos.close();
  }

  public static void saveResume(Clob resumeClob, String filePath)
      throws SQLException, IOException {
    FileWriter fw = new FileWriter(filePath);
    Reader reader = resumeClob.getCharacterStream();

    int b = -1;
    while ((b = reader.read()) != -1) {
      fw.write((char) b);
    }

    fw.close();
  }

}

class JDBCUtil {
  public static Connection getConnection() throws SQLException {
    // Register the Java DB embedded JDBC driver
    Driver derbyEmbeddedDriver = null;// new
                                      // org.apache.derby.jdbc.EmbeddedDriver();
    DriverManager.registerDriver(derbyEmbeddedDriver);

    // Construct the connection URL
    String dbURL = "jdbc:derby:beginningJavaDB;create=true;";
    String userId = "root";
    String password = "password";

    // Get a connection
    Connection conn = DriverManager.getConnection(dbURL, userId, password);

    // Set the auto-commit off
    conn.setAutoCommit(false);

    return conn;
  }

  public static void closeConnection(Connection conn) {
    try {
      if (conn != null) {
        conn.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void closeStatement(Statement stmt) {
    try {
      if (stmt != null) {
        stmt.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void closeResultSet(ResultSet rs) {
    try {
      if (rs != null) {
        rs.close();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void commit(Connection conn) {
    try {
      if (conn != null) {
        conn.commit();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void rollback(Connection conn) {
    try {
      if (conn != null) {
        conn.rollback();
      }
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }

  public static void main(String[] args) {
    Connection conn = null;
    try {
      conn = getConnection();
      System.out.println("Connetced to the database.");
    } catch (SQLException e) {
      e.printStackTrace();
    } finally {
      closeConnection(conn);
    }
  }
}
Reading and Writing Blob and Clob Data Database Columns - Java JDBC

Description

Demo Code

Result

Related Tutorials
