DataBase Blob Update,Retrive, Delete by using PreparedStatement:

This following examples guide us on how to Save, Retrieve,Delete a Document
from data which has been stored as a Blob

In this Example DocumentVO, hold the Document as byte[], we store, load, delete this
binaru data using Prepared Statement.

import java.io.BufferedInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import com.salesorder.interfaces.DocumentVO;
import com.salesorder.util.DBUtil;

public class DocumentDAO {

    private final static String SEQ_SQL = "select max(DOC_ID) from SALESORDER_DOCS";

    private final static String INSERT_SQL = " insert into SALESORDER_DOCS "
            + " (DOC_ID, DOC_TYPE, DOC, LST_UPDT, DOC_DESC, RPT_ID )"
            + " values (?, ?, EMPTY_BLOB(), SYSDATE, ?, ?) ";

    private final static String SELECT_BLOB_SQL = "select DOC from SALESORDER_DOCS where DOC_ID = ? FOR UPDATE";

    private final static String UPDATE_BLOB_SQL = " update SALESORDER_DOCS set DOC = ?, DOC_NME = ? where DOC_ID = ?";

    private final static String UPDATE_SQL = " Update SALESORDER_DOCS set "
            + " DOC_TYPE = ?, " + " LST_UPDT = SYSDATE, " + " DOC_DESC = ?, "
            + " RPT_ID = ?, DOC = EMPTY_BLOB() " + " where DOC_ID = ? ";

    private final static String SELECT_DOC_SQL = "select DOC_ID, DOC_NME, DOC_TYPE, DOC, DOC_DESC, LST_UPDT from SALESORDER_DOCS where DOC_ID = ? FOR UPDATE";

    public static final String DELETE_SQL = "delete from SALESORDER_DOCS where DOC_ID = ?";

    static DocumentVO create(DocumentVO vo) throws SQLException,
             IOException {

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Blob blob = null;
        long seqId = -1;
        OutputStream os  = null;
        try {
            con = DBUtil.getConnection();

            // Get the sequence id
            ps = con.prepareStatement(SEQ_SQL);
            rs = ps.executeQuery();
            if (rs.next()) {
           
                seqId = rs.getInt(1) + 1;
            }
            ps = con.prepareStatement(INSERT_SQL);
            ps.setLong(1, seqId);
            ps.setInt(2, vo.getDocumentType());
            ps.setString(3, vo.getDescription());

            if (vo.getReportId() > 0) {
                ps.setLong(4, vo.getReportId());
            } else {
                ps.setNull(4, Types.INTEGER);
            }
           
            ps.executeUpdate();
           
            ps = con.prepareStatement(SELECT_BLOB_SQL);
            ps.setLong(1, seqId);

            rs = ps.executeQuery();
           
            if (rs.next()) {
                blob = rs.getBlob(1);
                vo.setDocumentId(new Long(seqId));
                vo.setLastUpdate(rs.getTimestamp(2));
            }

            if (blob != null) {

                // Prepare BLOB with data
                //blob.setBytes(0, vo.getDocument());
             os = blob.setBinaryStream(1);
                os.write(vo.getDocument());
                os.flush();

                ps = con.prepareStatement(UPDATE_BLOB_SQL);
               
                ps.setBlob(1, blob);
                ps.setString(2, vo.getDocumentName());
                ps.setLong(3, seqId);
                ps.executeUpdate();
            }
        } finally {
   if (os != null){
    os.close();
   }
            if (rs != null)
                rs.close();
            if (ps != null)
                ps.close();
            if (con != null)
                con.close();
        }

        return vo;
    }

    static DocumentVO retrieve(long docId) throws SQLException,
            NamingException, IOException {
        DocumentVO vo = null;

        PreparedStatement ps = null;
        Connection con = null;
        ResultSet rs = null;
        try {
            con = DBUtil.getConnection();
            ps = con.prepareStatement(SELECT_DOC_SQL);
            ps.setLong(1, docId);
            rs = ps.executeQuery();

            if (rs.next()) {
                vo = new DocumentVO();
                vo.setDocumentId(new Long(rs.getLong(1)));
                vo.setDocumentName(rs.getString(2));
                vo.setDocumentType(rs.getInt(3));
                vo.setDescription(rs.getString(6));
                vo.setLastUpdate(rs.getTimestamp(6));
                // get blob
                Blob blob = rs.getBlob(4);
                BufferedInputStream bin = new BufferedInputStream(blob
                        .getBinaryStream());
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                byte[] bytes = new byte[2048];
                int bytesRead = 0;
                int end = 0;
                while ((bytesRead = bin.read(bytes)) != -1) {
                    baos.write(bytes, end, bytesRead);
                }
                vo.setDocument(baos.toByteArray());
            }
        } finally {
            if (ps != null)
                ps.close();
            if (rs != null)
                rs.close();
            if (con != null)
                con.close();
        }

        return vo;
    }

    public static DocumentVO updateDocument(DocumentVO vo) throws SQLException,
            NamingException, IOException {

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Blob blob = null;
        OutputStream os = null;

        try {
            con = DBUtil.getConnection();

            ps = con.prepareStatement(UPDATE_SQL);
            ps.setInt(1, vo.getDocumentType());
            ps.setString(2, vo.getDescription());

            if (vo.getReportId() > 1) {
                ps.setLong(3, vo.getReportId());
            } else {
                ps.setNull(3, Types.INTEGER);
            }

            ps.setLong(4, vo.getDocumentId().longValue());
            ps.executeUpdate();

            if (vo.getDocument() != null && vo.getDocument().length > 0) {

                ps = con.prepareStatement(SELECT_BLOB_SQL);
                ps.setLong(1, vo.getDocumentId().longValue());

                rs = ps.executeQuery();

                if (rs.next()) {
                    blob = rs.getBlob(1);
                    vo.setLastUpdate(rs.getTimestamp(2));
                }

                if (blob != null) {

                    // Prepare BLOB with data
                    //blob.setBytes(0, vo.getDocument());
                    os = blob.setBinaryStream(1);
                    os.write(vo.getDocument());
                    os.flush();

                    ps = con.prepareStatement(UPDATE_BLOB_SQL);
                    ps.setBlob(1, blob);
                    ps.setString(2, vo.getDocumentName());
                    ps.setLong(3, vo.getDocumentId().longValue());
                    ps.executeUpdate();
                }
            }
        } finally {
         if (os != null){
    os.close();
   }
            if (rs != null)
                rs.close();
            if (ps != null)
                ps.close();
            if (con != null)
                con.close();
        }

        return vo;
    }

    public static boolean deleteDocument(long docId) throws SQLException
             {

        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Blob blob = null;
        try {
            con = DBUtil.getConnection();

            ps = con.prepareStatement(DELETE_SQL);

            ps.setLong(1, docId);

            ps.executeUpdate();
        } finally {
            if (rs != null)
                rs.close();
            if (ps != null)
                ps.close();
            if (con != null)
                con.close();
        }

        return true;
    }
}

DocumentVO :

public class DocumentVO  implements java.io.Serializable
{
   private java.lang.String documentName;
   private java.lang.String description;
   private int documentType;
   private java.util.Date lastUpdate;
   private java.lang.Long documentId;
  
   private byte[] document= null;
 
   public byte[] getDocument(){
  return document;
  
 }
 
 public void setDocument(byte[] document){
  this.document = document;
 }
  
 
   public java.lang.String getDocumentName()
   {
   return this.documentName;
   }

   public void setDocumentName( java.lang.String documentName )
   {
   this.documentName = documentName;
   documentNameHasBeenSet = true;

   }

     public int getDocumentType()
   {
   return this.documentType;
   }

   public void setDocumentType( int documentType )
   {
   this.documentType = documentType;
   documentTypeHasBeenSet = true;

   }
  }

Back to top