I am trying to write a simple java application that reads a large number of records from a Oracle database (using JDBC) and loads them into a mySQL database through hibernate.

The application crashes with java.lang.OutOfMemoryError.

As the application runs I can see it steadilly using up memory until it eventually falls over. I am commiting the Transaction, closing the Session and creating a new Session and Transaction every 1000 records. I think hibernate is either failing to release some kind of resource, or it is performing some action on the existing data before creating each record which is consuming more memory as more records are created.

I am not sure how to proceed, your help would be much appreciated.

Source follows;

package X
lots of import's
public class XLoader {

private static Logger log = Logger.getLogger("X.XLoader");

public static void main(String[] args) throws Throwable {

log.info("Processing starting at " + new Date());

Connection conn = null;
try {

// Create the JDBC Driver
final OracleDriver driver = new OracleDriver();

// Load the Oracle JDBC driver
DriverManager.registerDriver(driver);

// Establish Read-only JDBC Connection to the PAS database
conn = (Connection)DriverManager.getConnection("jdbc:oracle:oci8:@XXX", "XXX", "XXX");
conn.setAutoCommit(false);

// Loading...
loadPerson(conn);

log.info("Processing completed at " + new Date());

} catch (Throwable e) {
log.fatal("Unhandled Exception - processing halted\n" + e.toString());
throw e;
} finally {
conn.close();
System.exit(1);
log.info("Finished at " + new Date());
}
}

private static void loadPerson(Connection conn) throws Throwable {

log.info("Processing people at " + new Date());

// Declare statment and result set
PreparedStatement stmt = null;
ResultSet rset = null;
Session session = null;
Transaction tx = null;
SessionFactory sf = new Configuration().configure("/XX.xml").buildSessionFactory();

try {

// Load people
String sql = "BIG SELECT STATEMENT HERE";

log.info("Reading people at " + new Date());
stmt = conn.prepareStatement(sql,
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
rset = stmt.executeQuery();

if (rset.getFetchSize() < 1)
throw new SQLException("No People found");

session = sf.openSession();
tx = session.beginTransaction();
log.info("Loading people at " + new Date());
int counter = 0;
String lastPayroll = "";
Payroll payroll = null;

while (rset.next()) {

// Create new payrolls as we find them
String thisPayroll = rset.getString(1);
if (!thisPayroll.equals(lastPayroll)) {
payroll = new Payroll();
payroll.setName(rset.getString(1));
payroll.setFrequency(rset.getString(2));
payroll.setStatus(rset.getString(3));
payroll.setFirstPaydate(rset.getDate(4));
payroll.setNextPaydate(rset.getDate(5));
payroll.setPrevPaydate(rset.getDate(6));
payroll.setRunDate(rset.getDate(7));
payroll.setAccountName(rset.getString(8));
payroll.setAccountNumber(rset.getInt(9));
payroll.setSortcode(rset.getInt(10));
payroll.setBacsName(rset.getString(11));
payroll.setBacsNumber(rset.getInt(12));
ArrayList payrollMemberships = new ArrayList();
payroll.setMemberships(payrollMemberships);
session.save(payroll);
}
lastPayroll = thisPayroll;

// Create people
Person person = new Person();
person.setReference(rset.getString(13));
person.setSurname(rset.getString(14));
person.setSex(rset.getString(15).charAt(0));
person.setForenames(rset.getString(16));
person.setInitials(rset.getString(17));
person.setTitle(rset.getString(18));
person.setNiNumber(rset.getString(19));
person.setBirth(rset.getDate(20));
ArrayList personMemberships = new ArrayList();
person.setMemberships(personMemberships);
ArrayList personAddresses = new ArrayList();
person.setAddresses(personAddresses);
session.save(person);

// Create memberships
Membership membership = new Membership();
membership.setStart(rset.getDate(21));
membership.setStatus("Paying");
membership.setPerson(person);
membership.setPayroll(payroll);
person.addMembership(membership);
payroll.addMembership(membership);
session.save(membership);

// Create addresses
Address address = new Address();
address.setStart(new Date());
address.setLine1(rset.getString(22));
address.setLine2(rset.getString(23));
address.setTown(rset.getString(24));
address.setCounty(rset.getString(25));
address.setPostcode(rset.getString(26));
address.setPerson(person);
person.addAddress(address);
session.save(address);

if ((++counter % 1000) == 0) {
tx.commit();
long payid = payroll.getId().longValue();
session.close();
session = sf.openSession();
tx = session.beginTransaction();
payroll = (Payroll)session.load(Payroll.class, new Long(payid));
log.info("Loaded " + counter);
}

}

tx.commit();
session.close();
sf.close();
log.info("Total People Loaded " + counter);

rset.close();
stmt.close();

// Handle errors
} catch (Throwable e) {
log.fatal(e.getMessage(), e);
rset.close();
stmt.close();
if (sf != null) sf.close();
if (session != null) session.close();
if (tx != null) tx.rollback();
throw e;
}
log.info("Finished processing people at " + new Date());
}
}

One possible thing may be (I'm sure I'll be corrected if wrong) is to do a session.flush() and then do a session.evict(...) before you close the session. I found evicting objects I had no more use for freed up memory and I believe that you have to perform a flush() anyway for the changes to be made.

-G

Thanks for your suggestion Brannor, it was a good idea but unfortunately flushing and evicting objects hasn't stopped the problem.

Any other ideas would be gratefully received.

- Jeff

Hmm. Perhaps try reduce the 1000 to a smaller value (perhaps start at 10, then move upwards till you find a value that doesn't use too much memory). I think it purely comes down to loading up a TON of objects into memory, before eventually flushing the session.

Each "set" contains:
1 payroll object
1 person object
1 membership object
1 address object
3 ArrayLists that hold various objects.

(+- 7000 objects cached per session)

Closing and getting a new session is rather quick, as the session factory actually maintains the connection (pooling) and will open it again when you need it. So don't worry too much about batching a smaller number.

Try the smaller batch, and let us know what happens. :)

-G

I have fixed the problem by changing the 1 to many relationship between payrolls and membership from a set to a bag. This not only stopped the OutOFMemoryException but also sped the whole thing up dramatically.

This relationship is more of a 'one to very many indeed' relationship, I think that when something is added to a set hibernate ensures that it does not already exist by loading all the keys into memory, and eventually there were just to many keys! A bag allows duplicates so it doesn't bother with this test.

That is just a guess though.

Thanks again for your help Brannor,

- Jeff

Good to hear. :)

I personally use bags for all my collections. I don't really have to worry about duplicates anywhere, as my database design is so strict it'll never happen.

<touches wood>

As for speed. When I first started with Hibernate, and knew little of correct session management, and evicting, etc. I was processing my XML files(*) at around 1 every 2-3 seconds. Today I average between 10 and 15 a second. :)


(*) 1) transform XML file - ancient DTD format - (ByteArrayStream) through an XSL to get correctly formatted XML.
2) Unmarshal the new XML file against a JAXBContext.
3) Parse the objects to convert values and perform data checks.
4) Store XML to Database using Hibernate.

I am quite chuffed with the system, and the speed, and ease of saving with Hibernate (once I got it working ;) ) is very nice indeed. I only have one issue which currently works, but for some reason cannot cascade a foreign key twice. i.e. 1-1-1-M.

Anyway. Time your mass inserting and see how quick it is.

-G

Is there anyway you could post your code for doing mass inserts? I too am attempting to read data from an xml file and post it to the DB via Hibernate.

A code example to look at would be a huge help.

Thanks,

Quenten

qvanegeren@yahoo.com

mySQL ->Text -> Oracle or Oracle->Text->mySQL.
I do not think you need to overuse hibernate with objects and mappings for trivial use cases.

The reason I'm using Hibernate for this data loading is due to the fact that my XML files are pretty complex, and also due to the fact that I'm using the session.saveOrUpdate() feature of Hibernate, since there is a possibility that the XML files will be run through more than once.

I've written some code, but I keep running out of database connections in the middle of my load (even though the connections on the DB side are set high enough). I figure I'm probably setting something up incorrectly, which is why I was hoping Brannor might post his code for reference...

Quenten

I think it is not pragmatic to implement object model, map it DB and load data to memory for import. It takes a time for development and doe's not perform (It will be a very big problem if file size increases).
I can understand it if you use some advanced processing/validation and transformations before to import file, but I prefer to find something simple before start design object models.

Ok. I hit this too, initially. So I wrote a class that would handle all my database connections, whether Hibernate or MS SQL, or MYSQL.

Connection handling is one of the most common issues I see amongst some of the developers I've worked with through the years, and I try to get them to do this:

1) Create a connection only when you NEED it.
2) ALWAYS make sure to close a connection you've opened, as soon as possible.

With Hibernate, I found that it does what it says, it pools your connections to the database, i.e. the first time you call sessionFactory.openSession() it creates a session, and a connection to the database, when you do the session.close() it doesn't actually remove the connection, but pools it till you eventually sessionFactory.close(), which kills everything.

So with any app, my personal view is:

1) Create your Hibernate SessionFactory at the start (or when your first session is required).
2) create a Session from that ONLY when you need it.
3) As soon as you've loaded/saved, close the session to free up the connection
4) When you're completely done, close your SessionFactory to close all connections.

If none of this helps, I can let you have my database class to look at, and of course, modify to fit your project. :)

And Baliukas, I also have complex XMLs, and process them in batches of up to 60,000 at a time (legacy database files)...having to modify, and check data as it goes. But yes, for mass importing of structured, well formatted, "perfect" data, you can do that completely outside of a Java environment.

-G