• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Need suggestion

 
Abhishek Purwar
Ranch Hand
Posts: 63
Eclipse IDE Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Why do you need to lock the table to do what you are doing? And why do they need to be "in one go"?
 
Abhishek Purwar
Ranch Hand
Posts: 63
Eclipse IDE Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 93
IntelliJ IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Abhishek Purwar
Ranch Hand
Posts: 63
Eclipse IDE Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
@Paul and Eduardo

Can you guys suggest me how should i achieve my requirement using transaction with hibernatetemplate???
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
    Posts: 93
    IntelliJ IDE Java VI Editor
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 63
    Eclipse IDE Java Spring
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 10336
    Eclipse IDE Hibernate Java
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Hibernate's documentation is your best bet (see here)
     
    Abhishek Purwar
    Ranch Hand
    Posts: 63
    Eclipse IDE Java Spring
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thnaks Paul.
     
    Abhishek Purwar
    Ranch Hand
    Posts: 63
    Eclipse IDE Java Spring
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 10336
    Eclipse IDE Hibernate Java
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator

    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
    Posts: 63
    Eclipse IDE Java Spring
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 10336
    Eclipse IDE Hibernate Java
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 93
    IntelliJ IDE Java VI Editor
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 63
    Eclipse IDE Java Spring
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 93
    IntelliJ IDE Java VI Editor
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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
    Posts: 10336
    Eclipse IDE Hibernate Java
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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).
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic