Refresh Your Java - Before Java Interview
We are Agile, believe in less Documentation - Only Quick notes (Java Interview Questions) of Java/J2ee Read more....
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;
}
}
This Portal is intended to put all Java/J2ee related topics at one single place for quick referance, not only Technical , but also the Project Management Related thing such as Development Process methodoogies build process, unit testing etc.,
This Portal has More than 500 Java Interview Questions (also could be Considered as Quick Notes) very neatly separated topic by topic with simple diagrams which makes you easily understandable. Importantly these are from our Realtime expericance.