Hello,
Boundary conditions:
- ORACLE 11g XE with the latest JDBC driver
- Blob size: 250MB
- CPU consumption includes database running on the same machine like the test
I compared 4 variations of BLOB handling:
- JPA byte[]
- JPA java.sql.Blob streaming
- JDBC streaming (ResultSet.setBinaryStream)
- JDBC byte[]
Results:
Variant:
JPA byte[],
JPA Blob stream,
JDBC byte[], JDBC stream
Elapsed Time (s):
20,
50,
25, 30
Max Memory (MB):
750,
750,
600, 5
Average Memory (MB):
600,
270,
600, 5
CPU Load (%):
80,
60,
65, 60
CPU Worktime (s):
16,
30,
16, 18
My Questions:
- Is it neccessary that JPA java.sql.Blob with Hibernate needs so much memory? Do there exists a setting or hint to get an behavior like JDBC streaming?
- What pitfalls await me if i make a Hibernate user type to use JDBC streaming?
Regards
Alexander
Relevant code snipet (JPA byte[]):
Code:
@Lob
@Column(length = 100000000)
public byte[] getBigBlob() {
return bigBlob;
}
...
public static void testBlobAsArray() throws Exception {
EntityManager em = JpaUtil.getEntityManager();
B_VerschiedeneLobs o = new B_VerschiedeneLobs();
// First i have to load the bytes into memory
byte[] bigArray = FileUtils.readFileToByteArray(testFile);
o.setBigBlob(bigArray);
em.getTransaction().begin();
em.persist(o);
em.getTransaction().commit();
}
Relevant code snipet (JPA java.sql.Blob streaming):
Code:
@Lob
@Column(length = 100000000)
public Blob getBigStreamedBlob() {
return bigStreamedBlob;
}
...
public static void testBlobAsStream() throws Exception {
EntityManager em = JpaUtil.getEntityManager();
B_VerschiedeneLobs o = new B_VerschiedeneLobs();
LobHelper lh = em.unwrap(Session.class).getLobHelper();
FileInputStream fis = new FileInputStream(testFile);
em.getTransaction().begin();
Blob blob = lh.createBlob(fis, -1);
// blob = SerializableBlobProxy.generateProxy( blob );
o.setBigStreamedBlob(blob);
em.persist(o);
em.getTransaction().commit();
fis.close();
}
Relevant code snipet (JDBC byte[]):
Code:
public static void testBlobArrayWithJdbc() {
Connection con = null;
try {
DriverConfig config = JpaUtil.loadDriverConfigFromPersistenceXml();
oracle.jdbc.pool.OracleDataSource ds = new oracle.jdbc.pool.OracleDataSource();
ds.setURL(config.url);
ds.setUser(config.user);
ds.setPassword(config.password);
con = ds.getConnection();
// Deleting the record for re-testing
Statement sta = con.createStatement();
sta.executeUpdate("DELETE FROM B_VerschiedeneLobs WHERE id = '1'");
// Inserting CLOB value with a PreparedStatement
PreparedStatement ps = con
.prepareStatement("insert into B_VerschiedeneLobs (bigStreamedBlob, id) values (?, 1)");
byte[] bigArray = FileUtils.readFileToByteArray(testFile);
ps.setBytes(1, bigArray);
int count = ps.executeUpdate();
ps.close();
sta.close();
con.close();
} catch (Exception e) {
System.err.println("Exception: " + e.getMessage());
e.printStackTrace();
}
}
Relevant code snipet (JDBC stream):
Code:
public static void testBlobStreamWithJdbc() {
Connection con = null;
try {
DriverConfig config = JpaUtil.loadDriverConfigFromPersistenceXml();
oracle.jdbc.pool.OracleDataSource ds = new oracle.jdbc.pool.OracleDataSource();
ds.setURL(config.url);
ds.setUser(config.user);
ds.setPassword(config.password);
con = ds.getConnection();
// Inserting CLOB value with a PreparedStatement
PreparedStatement ps = con
.prepareStatement("insert into B_VerschiedeneLobs (bigStreamedBlob, id) values (?, 1)");
File fileIn = new File("test.txt");
InputStream bodyIn = new FileInputStream(fileIn);
ps.setBinaryStream(1, bodyIn);
int count = ps.executeUpdate();
bodyIn.close();
ps.close();
sta.close();
con.close();
} catch (Exception e) {
System.err.println("Exception: " + e.getMessage());
e.printStackTrace();
}
}
Login
FAQ
Search