0

I have 2 tables TABLE1 and TABLE2.Table1 is having name and Table2 is having email and Phone.

To get the name,email and phone,I query as below

 query = entityManagerUtil.createNativeQuery("select s.Name,c.Phone1,c.Email1 from Table1 s,Table2 c where c.id= s.NodeID and s.NodeID =21")

Now my next requirement is to update name,email and phone.As these parameters are present in different tables so I am searching for single query which will update 2 tables.Unfortunately I am using sql server and there is no way to update 2 tables using single query

So I am thinking to use @Transactional and 2 queries to update 2 tables like the follow

@Transactional
public void updateDetails()
{
Query query1=   entityManagerUtil.entityManager.createNativeQuery("update Table1 set  Name='' where id in (select NodeID from Table 2) and NodeID=21");
Query query2=   entityManagerUtil.entityManager.createNativeQuery("update Table2 set  Email='' and phone1='' where NodeID in (select id from Table 2) and NodeID=21");
query1.executeUpdate();
query2.executeUpdate();

}

Is there any other better way to update 2 tables?

Community
  • 1
  • 1
rocking
  • 4,729
  • 9
  • 30
  • 45
  • 1
    You cannot update 2 tables in one query with an SQL statement, so you cannot do that in Hibernate. Either change your schema or your Transactional approach is the way to go. – mavroprovato Jan 09 '15 at 10:12
  • @mavroprovato what schema change your refer to?Is the way I am using(@Transactional) is valid? – rocking Jan 09 '15 at 10:14
  • I mean to put both columns in the same table. As for the Transactional annotation it looks ok, but I cannot tell you for sure without seeing more of your code: It works only inside spring beans, you have to put some configuration, etc. You *must* read and understand this: http://docs.spring.io/spring/docs/current/spring-framework-reference/html/transaction.html – mavroprovato Jan 09 '15 at 10:20

2 Answers2

0

you can use JDBCTemplate

http://sujitpal.blogspot.com.es/2007/03/spring-jdbctemplate-and-transactions.html

It allows to do multiple queries with one connection, so you save some time instead of doing it twice.

Álvaro Gómez
  • 306
  • 1
  • 6
0

Why don't you use Hibernate entities for that. Just load the entities associated with Table1 and table2, modify them and let the automatic dirty checking mechanism to update the tables on your behalf. That's one reason for using an ORM by the way.

Vlad Mihalcea
  • 142,745
  • 71
  • 566
  • 911
  • Thanks for the answer,I read your blog but I dont know how to code for it.It would be nice if you please give an example please – rocking Jan 09 '15 at 16:51
  • First, read the whole Hibernate documentation ful. Second , try an example after knowing the basics. If you still have problems then ask another SO question against an actual code attempt – Vlad Mihalcea Jan 09 '15 at 17:10
  • I dont have entities for the tables and for this purpose I am using native query.The reason is desktop application is windows application built in .net and so they are using sql server.I am giving them an ios application using their existing database so I can not use model classes or entities for it.Hope you understand – rocking Jan 09 '15 at 17:16
  • Then, you need to add entities. It's not complicated at all. – Vlad Mihalcea Jan 09 '15 at 17:19
  • Hope you understand why I dont have entities and why I use native query – rocking Jan 09 '15 at 17:20