Help coderanch get a
new server
by contributing to the fundraiser
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

sql-insert and stored procedure

 
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Anoop Krishnan
Ranch Hand
Posts: 163
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Did anybody looked to this problem
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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) .
 
Ranch Hand
Posts: 89
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 163
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 89
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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 do some of my very best work in water. Like this tiny ad:
We need your help - Coderanch server fundraiser
https://coderanch.com/t/782867/Coderanch-server-fundraiser
reply
    Bookmark Topic Watch Topic
  • New Topic