Meaningless Drivel is fun!*
The moose likes Object Relational Mapping and the fly likes Need suggestion Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Need suggestion" Watch "Need suggestion" New topic
Author

Need suggestion

Abhishek Purwar
Ranch Hand

Joined: Dec 15, 2007
Posts: 63

Hi all,

I need suggestion regarding an issue.
I want to execute multiple statements on a table
at one go in order but i don't want to use stored procedure.

For e.g.

LOCK TABLE nested_category WRITE;

SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;

UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);

UNLOCK TABLES;

I want to execute this statement using HibernateTemplate.
Can any one suggest me how to proceed???

Thanks to all for their suggestion.


Abhishek Purwar,
Bangalore.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Why do you need to lock the table to do what you are doing? And why do they need to be "in one go"?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Abhishek Purwar
Ranch Hand

Joined: Dec 15, 2007
Posts: 63

Paul Sturrock wrote:Why do you need to lock the table to do what you are doing? And why do they need to be "in one go"?



Paul,

I want a lock on table because i want to first perform update and then insert.
And it should be in one go because if some error occurs then every change should be roll back and only one user can modify at a time because if multiple user modify same data then error occured.
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

It's going to turn your database into a real bottleneck (effectively a single user repository) if you use pessimistic locking where you would normally use a transaction. Any reason you can't use a transaction?
Eduardo Yañez Parareda
Ranch Hand

Joined: Oct 09, 2008
Posts: 92

Abhishek Purwar wrote:Hi all,

I need suggestion regarding an issue.
I want to execute multiple statements on a table
at one go in order but i don't want to use stored procedure.

For e.g.

LOCK TABLE nested_category WRITE;

SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;

UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);

UNLOCK TABLES;

I want to execute this statement using HibernateTemplate.
Can any one suggest me how to proceed???

Thanks to all for their suggestion.



I wouldn't do that with HibernateTemplate....

* Open Transaction
* Select
* Update
* Update
* Insert
* Commit Transaction
* If something go bad then Rollback

That's all you need.

http://serfj.sourceforge.net - Simplest Ever REST Framework for Java
Abhishek Purwar
Ranch Hand

Joined: Dec 15, 2007
Posts: 63

@Paul and Eduardo

Can you guys suggest me how should i achieve my requirement using transaction with hibernatetemplate???
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

In this order:
  • Start a transaction
  • Do your reads, updates and inserts
  • Commit or rollback your transaction depending on success
  • Eduardo Yañez Parareda
    Ranch Hand

    Joined: Oct 09, 2008
    Posts: 92

    Abhishek Purwar wrote:@Paul and Eduardo

    Can you guys suggest me how should i achieve my requirement using transaction with hibernatetemplate???


    No, I've never used HibernateTemplate, but here you have the Javadoc: http://static.springsource.org/spring/docs/2.5.6/api/org/springframework/orm/hibernate3/HibernateTemplate.html

    Also, it's possible you need to read something about HibernateTransactionManager:
    http://static.springsource.org/spring/docs/2.5.6/api/org/springframework/orm/hibernate3/HibernateTransactionManager.html

    Abhishek Purwar
    Ranch Hand

    Joined: Dec 15, 2007
    Posts: 63

    Paul Sturrock wrote:In this order:
  • Start a transaction
  • Do your reads, updates and inserts
  • Commit or rollback your transaction depending on success


  • Paul,
    Thanks for your suggestion.
    Can you provide me with sample code example that will be really helpful???
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336

    Hibernate's documentation is your best bet (see here)
    Abhishek Purwar
    Ranch Hand

    Joined: Dec 15, 2007
    Posts: 63

    Thnaks Paul.
    Abhishek Purwar
    Ranch Hand

    Joined: Dec 15, 2007
    Posts: 63

    Hey Paul and Eduardo ,

    I followed transaction way for implementation but i have one issue.
    Below is my code - snippet :



    In the above code snippet, i rolled back the transaction in case exception occured.But rolled back is not working because changes done by update statement is present in database table.
    Can any one suggest me what is the error in the code and why roll-back is not working??

    Thanks for all suggestion.
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336


    But rolled back is not working because changes done by update statement is present in database table.

    In which case you are not running in a transaction, or your rollback statement is not being run.
    Abhishek Purwar
    Ranch Hand

    Joined: Dec 15, 2007
    Posts: 63

    Paul Sturrock wrote:

    But rolled back is not working because changes done by update statement is present in database table.

    In which case you are not running in a transaction, or your rollback statement is not being run.


    Hey Paul,

    If error occurs during some transaction for e.g. if any statement fails then i want to roll back the transaction.

    In the code snippet, if you see there is an IF-ELSE block. Intentionally , i am forcing my transaction to throw error and if error comes then it goes to catch block.
    In catch block, i am rolling back the transaction but when i check database table, i observed that changes made by UPDATE statement is there.

    I want the table to show data which is before UPDATE statement.
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336

    Your transaction will only rollback if it's not null, and the exception thrown is a HibernateException. If either of these things are not true the transaction will not rollback.
    Eduardo Yañez Parareda
    Ranch Hand

    Joined: Oct 09, 2008
    Posts: 92

    Abhishek Purwar wrote:
    Paul Sturrock wrote:

    But rolled back is not working because changes done by update statement is present in database table.

    In which case you are not running in a transaction, or your rollback statement is not being run.


    Hey Paul,

    If error occurs during some transaction for e.g. if any statement fails then i want to roll back the transaction.

    In the code snippet, if you see there is an IF-ELSE block. Intentionally , i am forcing my transaction to throw error and if error comes then it goes to catch block.
    In catch block, i am rolling back the transaction but when i check database table, i observed that changes made by UPDATE statement is there.

    I want the table to show data which is before UPDATE statement.



    Please, print the resulting stack trace... May be the exception thrown is not yours but another Exception, in that case, since your catch (Exception) is not rolling back the transaction, changes in DB aren't reverted.
    Abhishek Purwar
    Ranch Hand

    Joined: Dec 15, 2007
    Posts: 63

    Rollback is not working due to DB engine.
    In MySQL, MYISAM engine does not support transaction and thats why rollback is not working.
    After changing it to InnoDB, it is working fine.
    Eduardo Yañez Parareda
    Ranch Hand

    Joined: Oct 09, 2008
    Posts: 92

    Abhishek Purwar wrote:Rollback is not working due to DB engine.
    In MySQL, MYISAM engine does not support transaction and thats why rollback is not working.
    After changing it to InnoDB, it is working fine.


    That happened because of using strange things
    Paul Sturrock
    Bartender

    Joined: Apr 14, 2004
    Posts: 10336

    Yeah - the MyISAM engine is not really worth using unless all you need something like a single table repository. Its non-transactional and it doesn't honour constraints (i.e. its not really a relational database).
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Need suggestion