Problem with querying table which has blob field and the query returns large rows (Using Hibernate and Lucene)

We were having a issue with one of our design. We were having a table which used to store entire file in blob column. When we query a table with some criteria and if the query returns large number of results or the rows and each row containing large blob, we were getting Out of Memory error in Java. To solve this we made a slight modification to our design of tables.

We created two tables file and filecontent and mapped them by one-to-one mapping using hibernate. The blob column was now shifted to filecontent table. We were using lucene to index the content of file. Since blob column was now moved to filecontent table, the indexing which we did on file table didn’t work. To solve this, we used @EmbeddedObject annotation in file object. Our lucene indexing problem was now solved. Indexes were getting created, but now the same problem occurred again. The hibernate-lucene in turn does a join query when we search through index. As a result again the huge blob is getting retrieved along with other columns. To solve this we again changed our design, but this time only in hibernate configuration.

We created a transient field of type byte[] in file object and made it indexable. So we were having the file’s content in two object (file and filecontent), but in file it was transient and indexable and in filecontent it was persistent and non-indexable.
Also while mapping file and filecontent object using hibernate, we specified LAZY loading filecontent object.