File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes Object Relational Mapping and the fly likes sql-insert and stored procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "sql-insert and stored procedure" Watch "sql-insert and stored procedure" New topic
Author

sql-insert and stored procedure

Anoop Krishnan
Ranch Hand

Joined: May 03, 2001
Posts: 163
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


This table is related to the object com.financial.webalizer.core.context.HourOfTheDay in the hibernate config file as given below.


The following code will insert the object to the database


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.


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


and





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


I just want to know is there any body call my bean's Getter and Setter methods with "Please" in front - My favorite quip from Bugzilla
Anoop Krishnan
Ranch Hand

Joined: May 03, 2001
Posts: 163
Did anybody looked to this problem
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

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) .


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
neetika sharma
Ranch Hand

Joined: May 19, 2006
Posts: 89
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
Ranch Hand

Joined: May 03, 2001
Posts: 163
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
Ranch Hand

Joined: May 19, 2006
Posts: 89
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: sql-insert and stored procedure
 
Similar Threads
Restore problem
Illegal attempt to associate a collection with two open sessions
JOIN syntax for HQL . Need help!
SQLInsert annotation and composite ids
Group By and Date Function Hibernate on MySql