Dealing with CLOBs

No matter dealing with which DB provider. It will always be needed to deal with Clob Object. Especially you would like to store useful information as Image in the Database. Here provides some tips in dealing with them.

1. To inject some information as a CLOB Object with some normal table properties:
   
   
   Use a PreparedStatement with INSERT SQL and set in all the attributes (setString, setInt, blah blah).
   
   Use an CLOB.empty_lob() for the Clob field.
   
   Execute the query
   
   
   Select out the newly added entry
   
   
   CLOB clob = rs.getCLOB("TRXMESSAGE");
   
   int count = clob.putString(1, txnMessage);
   
   rs.close();
   
   
   Commit at last
   



2. To inject the CLOB Object (Easier version):
   
   Use the command:
   
   opstmt = (OraclePreparedStatement)conn.prepareStatement(sql);
   
   opstmt.setStringForClob(1,str);
   
   opstmt.executeUpdate();
   



3. To retrieve the CLOB Object properly:
   In the source code provided below shows several way that you can manipulate the CLOBs.
   

package org.axiondb.types;

import java.io.InputStream;
import java.io.OutputStream;
import java.io.Reader;
import java.io.Writer;
import java.sql.Clob;
import java.sql.SQLException;

import junit.framework.TestCase;

import org.axiondb.jdbc.AxionClob;

public abstract class AbstractClobTest extends TestCase {

public AbstractClobTest(String testName) {
super(testName);
}

protected String _text = "The quick brown fox jumped over the lazy dogs.";

protected abstract AxionClob getClob() throws Exception;

public void testGetAsciiStream() throws Exception {
Clob clob = getClob();
InputStream in = clob.getAsciiStream();
StringBuffer buf = new StringBuffer();
for(int c=in.read();c != -1;c = in.read()) {
buf.append((char)c);
}
assertEquals(_text,buf.toString());
in.close();
}

public void testGetCharacterStream() throws Exception {
Clob clob = getClob();
Reader in = clob.getCharacterStream();
StringBuffer buf = new StringBuffer();
for(int c=in.read();c != -1;c = in.read()) {
buf.append((char)c);
}
assertEquals(_text,buf.toString());
in.close();
}

public void testTruncate() throws Exception {
AxionClob clob = getClob();
clob.truncate(("The quick".length()));
Reader in = clob.getCharacterStream();
StringBuffer buf = new StringBuffer();
for(int c=in.read();c != -1;c = in.read()) {
buf.append((char)c);
}
assertEquals("The quick",buf.toString());
in.close();
}

public void testGetSubstring() throws Exception {
AxionClob clob = getClob();
String substring = null;
try {
substring = clob.getSubString(("The quick".length()), " brown fox jumped".length());
} catch(SQLException e) {
return; // exit on unsupported
}
assertEquals(" brown fox jumped",substring);
}

public void testPositionOfString() throws Exception {
AxionClob clob = getClob();
String substring = " brown fox jumped";
long pos = -1;
try {
pos = clob.position(substring,3L);
} catch(SQLException e) {
return; // exit on unsupported
}
assertEquals(_text.indexOf(substring,3),(int)pos);
}

public void testPositionOfClob() throws Exception {
AxionClob clob = getClob();
String substring = " brown fox jumped";
long pos = -1;
try {
pos = clob.position(new StringClob(substring),3L);
} catch(SQLException e) {
return; // exit on unsupported
}
assertEquals(_text.indexOf(substring,3),(int)pos);
}

public void testPositionOfNullString() throws Exception {
AxionClob clob = getClob();
try {
clob.position((String)null,0L);
fail("Expected SQLException");
} catch(SQLException e) {
// expected
}
}

public void testPositionOfNullClob() throws Exception {
AxionClob clob = getClob();
try {
clob.position((Clob)null,0L);
fail("Expected SQLException");
} catch(SQLException e) {
// expected
}
}

public void testPositionOfStringNotFound() throws Exception {
AxionClob clob = getClob();
String substring = " bROWn fox jumped";
long pos = -1;
try {
pos = clob.position(substring,3L);
} catch(SQLException e) {
return; // exit on unsupported
}
assertEquals((int)pos,_text.indexOf(substring,3));
}

public void testSetAsciiStream() throws Exception {
AxionClob clob = getClob();
OutputStream out = null;
try {
out = clob.setAsciiStream(0L);
} catch(SQLException e) {
return; // exit on unsupported
}
out.write("The quick yellow dogs crept under the lazy foxes. Every good boy does fine.".getBytes("US-ASCII"));
out.close();

InputStream in = clob.getAsciiStream();
StringBuffer buf = new StringBuffer();
for(int c=in.read();c != -1;c = in.read()) {
buf.append((char)c);
}
assertEquals("The quick yellow dogs crept under the lazy foxes. Every good boy does fine.",buf.toString());
in.close();
}

public void testSetAsciiStream2() throws Exception {
AxionClob clob = getClob();
{
OutputStream out = null;
try {
out = clob.setAsciiStream(0L);
} catch(SQLException e) {
return; // exit on unsupported
}
out.write("The quick yellow dogs crept under the lazy foxes. Every good boy does fine.".getBytes("US-ASCII"));
out.close();

InputStream in = clob.getAsciiStream();
StringBuffer buf = new StringBuffer();
for(int c=in.read();c != -1;c = in.read()) {
buf.append((char)c);
}
assertEquals("The quick yellow dogs crept under the lazy foxes. Every good boy does fine.",buf.toString());
in.close();
}
clob.truncate(0);
{
OutputStream out = null;
try {
out = clob.setAsciiStream(0L);
} catch(SQLException e) {
return; // exit on unsupported
}
out.write("Every good boy does fine.".getBytes("US-ASCII"));
out.close();

InputStream in = clob.getAsciiStream();
StringBuffer buf = new StringBuffer();
for(int c=in.read();c != -1;c = in.read()) {
buf.append((char)c);
}
assertEquals("Every good boy does fine.",buf.toString());
in.close();
}
}

public void testSetCharacterStream() throws Exception {
AxionClob clob = getClob();
Writer out = null;
try {
out = clob.setCharacterStream(0L);
} catch(SQLException e) {
return; // exit on unsupported
}
out.write("The quick yellow dogs crept under the lazy foxes. Every good boy does fine.");
out.close();

InputStream in = clob.getAsciiStream();
StringBuffer buf = new StringBuffer();
for(int c=in.read();c != -1;c = in.read()) {
buf.append((char)c);
}
assertEquals("The quick yellow dogs crept under the lazy foxes. Every good boy does fine.",buf.toString());
in.close();
}

public void testSetCharacterStream2() throws Exception {
AxionClob clob = getClob();
{
Writer out = null;
try {
out = clob.setCharacterStream(0L);
} catch(SQLException e) {
return; // exit on unsupported
}
out.write("The quick yellow dogs crept under the lazy foxes. Every good boy does fine.");
out.close();

InputStream in = clob.getAsciiStream();
StringBuffer buf = new StringBuffer();
for(int c=in.read();c != -1;c = in.read()) {
buf.append((char)c);
}
assertEquals("The quick yellow dogs crept under the lazy foxes. Every good boy does fine.",buf.toString());
in.close();
}
clob.truncate(0);
{
Writer out = null;
try {
out = clob.setCharacterStream(0L);
} catch(SQLException e) {
return; // exit on unsupported
}
out.write("Every good boy does fine.");
out.close();

InputStream in = clob.getAsciiStream();
StringBuffer buf = new StringBuffer();
for(int c=in.read();c != -1;c = in.read()) {
buf.append((char)c);
}
assertEquals("Every good boy does fine.",buf.toString());
in.close();
}
}
}

Struts 2 Wrong Conceptual

1. It is true that Struts 2 Actions can be in Entity form however if you tied these 2 thing together. You will be having problem in placing View level attributes. Most of the time we will want to have different way in show in the UI and in representation in the backend level. The most correct design will be:
   
   class Action {
   
   private EntityA entityA
   
   private EntityB entityB
   }
   

Hibernate Retrieving Efficiency

Hibernate as what discussed in most of the forum is not able to handle batch of SQL queries efficiently. This can be proved by the experiments done below:

Testing SQL: SELECT * FROM CORPORATE c where c.STATUS = "ACTIVE".

Return 18K row of data, using normal SQL and setter getter mechanism to populate an entity. It takes 10 seconds

Return 18K row of data, using Hibernate Native SQL and addEntity mechanism. It takes 4 minutes.

Return 18K row of data, using Hibernate HQL. It takes 4 minutes also.

It indicates that it is a wrong concept that using Hibernate Native SQL will help in increasing the efficiency. Furthermore, it also proves that Hibernate did more "hidden" steps when populating an Entity.

Again it proves that Hibernate is not suitable in handling huge transactional data and batch processes.