Need suggestion

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.

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

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?

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.

@Paul and Eduardo

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

In this order:

Start a transaction

Do your reads, updates and inserts

Commit or rollback your transaction depending on success

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

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???

Hibernate's documentation is your best bet (see here)

Thnaks Paul.

Hey Paul and Eduardo ,

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

this.hibernateTemplate.execute(new HibernateCallback() {
        	public Object doInHibernate(Session session) throws HibernateException {
        		Transaction transaction = null;
        		try {
        			transaction = session.beginTransaction();        			
        			Long parentId = folder.getParentFolderId();
        			if (parentId == null) {
						parentId = new Long(0);
					}
        			if(siblingId != null && siblingId > 0){
        				parentId = siblingId.longValue();
        			}
        			Folder parentFolderDetails = null;
        			if(parentId != null && parentId > 0)
        				<[b]SELECT STATEMENT[/b]>
        			Integer childeId = 0;
        			if(parentFolderDetails != null){
        				childeId = parentFolderDetails.getLeftChild();
	        			if(siblingId != null && siblingId > 0){
	        				childeId = parentFolderDetails.getRightChild();
	        			}
        			}
        			if(childeId != null && childeId > 0){        				
        				<[b]UPDATE STATEMENT[/b]>
        			}	
        			if (folder.getFolderName() != null && !"".equalsIgnoreCase(folder.getFolderName().trim())) {
        				<[b]INSERT STATEMENT[/b]>
        			}else{
        				throw new HibernateException("Error occured.");
        			}
        			
        			session.flush();        			
        			transaction.commit();
				} catch (HibernateException he) {
					if(transaction != null)
						transaction.rollback();
					he.printStackTrace();
				} catch (Exception e) {
					e.printStackTrace();
				}finally {
					try {						
						if(session != null)
							session.close();
					} catch (Exception e) {
						e.printStackTrace();
					}
				}
				return null;
        	}
		});

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.

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.

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.

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.

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.

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.

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

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