sql-insert and stored procedure

[Anoop Krishnan]

I want you to look to this simple insert mechanism using sql-insert and stored procedure.
I am using Hibernate 3 and MySql 5.5.8.

I have the following table HourOfTheDayDaily in my database webalizer
Field Type Null Key Default Extra DOMAIN varchar(50) NO PRI NULL YEAR "smallint(4) unsigned" NO PRI NULL MONTH "tinyint(2) unsigned" NO PRI NULL DATE "tinyint(2) unsigned" NO PRI NULL HOUR "tinyint(2) unsigned" NO PRI NULL PAGES "mediumint(8) unsigned" NO NULL HITS "mediumint(8) unsigned" NO NULL VOLUME "int(10) unsigned" NO NULL

This table is related to the object com.financial.webalizer.core.context.HourOfTheDay in the hibernate config file as given below.
<class name="com.financial.webalizer.core.context.HourOfTheDay" table="HourOfTheDayDaily" > <composite-id name="name" class="com.financial.webalizer.core.key.ChronoStatisticalContextHour" > <key-property name="id" column="DOMAIN"></key-property> <key-property name="year" column="YEAR"></key-property> <key-property name="monthOfTheYear" column="MONTH"></key-property> <key-property name="dayOfTheMonth" column="DATE"></key-property> <key-property name="hourOfTheDay" column="HOUR"></key-property> </composite-id> <property name="files" column="PAGES"></property> <property name="hits" column="HITS"></property> <property name="dataSize" column="VOLUME"></property> <!-- The order of the parameters in the database procedure must be the same order in which hibernate pass them to the procedure --> <sql-insert callable="true">{call webalizer.insertHourOfTheDay(?,?,?,?,?,?,?,?)}</sql-insert> </class>

The following code will insert the object to the database

/**
	 * Update core statistics for any context
	 * 
	 * @param <ID> The context name identifier for a given Statistical context
	 * @param statistics The statistical context to be deleted.
	 * @param name The primary key of the statistical context.
	 */
	public static <ID extends Persistant> void updateCoreStatistics(final Context<ID> statistics, final Class<ID> id) {
		Session session = SELF.hbmSessionFactory.openSession();
		
		Transaction tx = session.beginTransaction();
		session.merge(statistics);
		tx.commit();
		session.close();
		if (LogLogger.isInfoEnabled()) {
			LogLogger.info(String.format("UPDATED -> Core Statistics %1$s", statistics.getName()));
		}
	}

Now please read what I want to do. There can be more than one commit with the same primary key (Please note that my primary key is a composite key).
I want to do an insert on the first attempt that means if a record with the give primary key is not present yet in the table .
If there is a record present in the table I want update the table colums PAGES,HITS and VOLUME by adding the new values to the values in the table.
I tried INSERT ...ON DUPLICATE UPDATE but it will not work on records with composite primary key.
Next option was to use a stored procedure . So I prepared a stored procedure insertHourOfTheDay as given below.
DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `insertHourOfTheDay`( IN pages MEDIUMINT(8), IN hits MEDIUMINT(8), IN volume INTEGER, IN domain VARCHAR(50), IN year SMALLINT(4), IN month TINYINT(2), IN date TINYINT(2), IN hour TINYINT(2) ) BEGIN DECLARE updated INT DEFAULT 0; DECLARE rowCount INT DEFAULT 0; DECLARE _pages,_hits,_volume INT; DECLARE curToUpdate CURSOR FOR SELECT COUNT(DOMAIN),PAGES,HITS,VOLUME FROM hourofthedaydaily h WHERE h.DOMAIN=domain AND h.YEAR=year AND h.MONTH=month AND h.DATE=date AND h.HOUR=hour; DECLARE CONTINUE HANDLER FOR NOT FOUND SET updated = 1; DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET updated = 1; OPEN curToUpdate; _loop : LOOP FETCH curToUpdate INTO rowCount,_pages,_hits,_volume; IF updated = 1 THEN LEAVE _loop; END IF; IF rowCount = 0 THEN INSERT INTO hourofthedaydaily (DOMAIN,YEAR,MONTH,DATE,HOUR,PAGES,HITS,VOLUME) VALUES (domain,year,month,date,hour,pages,hits,volume); ELSE SET _pages := _pages + pages; SET _hits := _hits + hits; SET _volume := _volume + volume; UPDATE hourofthedaydaily h SET h.PAGES=_pages, h.HITS=_hits, h.VOLUME=_volume WHERE h.DOMAIN=domain AND h.YEAR=year AND h.MONTH=month AND h.DATE=date AND h.HOUR=hour; END IF; END LOOP; CLOSE curToUpdate; END

My problem is Nothing happens when I run my code no data is inserted or updated and i get the following exceptions.

org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
	at org.hibernate.jdbc.Expectations$BasicExpectation.checkBatched(Expectations.java:85)
	at org.hibernate.jdbc.Expectations$BasicExpectation.verifyOutcome(Expectations.java:70)
	at org.hibernate.jdbc.BatchingBatcher.checkRowCounts(BatchingBatcher.java:90)
	at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
	at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
	at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:266)
	at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:167)
	at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
	at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:50)
	at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1028)
	at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:366)
	at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
	at com.financial.webalizer.service.PersistanceService.updateCoreStatistics(PersistanceService.java:72)
	at com.financial.webalizer.parser.DomainCache.saveCoreHourlyStatisticData(DomainCache.java:222)
	at com.financial.webalizer.parser.DomainCache.saveCoreStatisticData(DomainCache.java:98)
	at com.financial.webalizer.parser.ApacheLogParser.parse(ApacheLogParser.java:258)
	at com.financial.webalizer.Webalizer.processCommandLineArguments(Webalizer.java:72)
	at com.financial.webalizer.Webalizer.main(Webalizer.java:35)

and

org.hibernate.exception.JDBCConnectionException: Cannot open connection
	at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:97)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
	at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
	at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)
	at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
	at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:142)
	at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:85)
	at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1354)
	at com.financial.webalizer.service.PersistanceService.updateCoreStatistics(PersistanceService.java:70)
	at com.financial.webalizer.parser.DomainCache.saveCoreHourlyStatisticData(DomainCache.java:222)
	at com.financial.webalizer.parser.DomainCache.saveCoreStatisticData(DomainCache.java:98)
	at com.financial.webalizer.parser.ApacheLogParser.parse(ApacheLogParser.java:258)
	at com.financial.webalizer.Webalizer.processCommandLineArguments(Webalizer.java:72)
	at com.financial.webalizer.Webalizer.main(Webalizer.java:35)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
	at sun.reflect.GeneratedConstructorAccessor34.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.Util.getInstance(Util.java:381)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
	at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1095)
	at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2031)
	at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:718)
	at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46)
	at sun.reflect.GeneratedConstructorAccessor27.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:406)
	at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:302)
	at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:282)
	at java.sql.DriverManager.getConnection(DriverManager.java:582)
	at java.sql.DriverManager.getConnection(DriverManager.java:154)
	at org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:133)
	at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
	... 10 more

I am debugging this simple code for last three days and could not figure out any thing . Can any body help me.

[Anoop Krishnan]

Did anybody looked to this problem

[Paul Sturrock]

Seems an odd choice to mix Hibernate and stored procedures. Does this not kind of undermine the database agnostic nature of an ORM?

If you are assigning a key to your entity, could you not just control this yourself? What I mean is your code could check if there is a duplicate in the table and change the value accordingly. In the odd occasions where a duplicate is inserted between the check and the insert your could catch the exception and try again.

Also, I would recommend just swapping to a surrogate key. Makes life much easier in the long run and there really is no reason to use anything other than a surrogate key (unless of course you cannot change the schema) .

[neetika sharma]

Anoop Krishnan wrote:Did anybody looked to this problem

Hi,

Were you able to find the solution!!! I am stuck with exactly same issue. Sql insert and SP call.....
Please update me, if you have some solution now.

[Anoop Krishnan]

Neetika

Please do not use SP for insert. it is not a Hibernate way.

You must map a DB table to a Java Bean
Add this mapping to your hibernate configuration
Use Hibernate Session's save method with an instance of your bean this will insert a row in your table
if you want to check for duplicates use get method to get an existing reference.

[neetika sharma]

Anoop Krishnan wrote:Neetika

Please do not use SP for insert. it is not a Hibernate way.

You must map a DB table to a Java Bean
Add this mapping to your hibernate configuration
Use Hibernate Session's save method with an instance of your bean this will insert a row in your table
if you want to check for duplicates use get method to get an existing reference.

Thanks anoop for responding.

I will try with hibernate forum,otherwise will use the work around though.