How to execute few queries in one transaction in my DAO class? Is it possible to do it creating one query?

Question

I'm new in Java development and I need some help with writing batch of queries in my java DAO class. In my DAO class I would like to write an update query that will update few tables. Something like:

UPDATE deals, vehicles SET deals.client= "client",
vehicles.vehicleIdNumber="123456"
WHERE deals.id ="123" and deals.id= vehicles.id;

This will work if I execute it in MySQL but if I want to do this in my DAO class I have to write two queries in batch. The problem is that I don't know how to do it. From what I've already researched I can only find some examples using JDBC, Spring, Hibernate, but I don't use any of this in my application.

When I do an update for a single table I have something like this:

public void updateDeal(String dealNumber, Integer dealType) {
        Query upQ = em.createQuery(
                        "update Deals d set d.dealType = :dealType  where d.dealNumber= :dealNumber")
                .setParameter("dealType", dealType)
                .setParameter("dealNumber", dealNumber);
        upQ.executeUpdate();

How can I update two or more tables in one transaction similar to the above, but without using JDBC, Spring, Hibernate...

Tnx!

UPDATE: I realized that I haven't explained it well what I meant with no using JDBC...my mistake. So I have JDBC, I have connection with my DB and it's working fine with a single table statements. I actually want to know if there is other way of doing this rather than something like: http://w3processing.com/index.php?subMenuId=146 In this example you have to first get connection to the DB, than create statement and than add all the queries in the batch. When I'm creating a query for updating one table is pretty simple (it's exactly like I have shown in the code example above). I want to know if there is some simpler way of doing this for updating more than one table. I don't want to create each time a connection to my DB.

I've tried something like:

public void updateDeal(Integer id, String client,
            String vechicleNumber)
Query query = em.createQuery("UPDATE Deals, Vehicles SET deal.client=:client, vehicle.vehicleNumber=:vechicleNumber WHERE deal.id =:id AND deal.id=vehicles.id ").
              setParameter("client", client).setParameter("id", dealID).
              setParameter("vechicleNumber ", vechicleNumber );

and I get this error:

According to the error in the console,  I have synatax error:
Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: , near line 1, column 35

First of all, there's no reason why you would not be able to use the same SQL through the DAO as you do directly. Secondly - you seem to already be using Hibernate (in the form of your `em`) and certainly JDBC. Thirdly - without JDBC you'll need to write your own network communication system for the DB, so you best either find out what technology you DO use for DBs, or go grab a MySQL low-level protocol specification sheet — Ordous, Sep 04 '14 at 10:09
I haven't explained my problem well in the first place. I have updated the question now. Query --> import javax.persistence.Query; em -->import javax.persistence.EntityManager; — Jordan, Sep 04 '14 at 12:07
You should edit your question title as well, because it's very misleading. If I understand you correctly, you want to know if it's possible to batch-update tables in a single statement. Which is more an `SQL` question in my opinion. But anyway, you should write that in the title as well, because many will only see the title and you might get more/better answers with a better title. — Sebastian_H, Sep 04 '14 at 12:38
Check out [`EntityManager.getTransaction().begin()`](http://docs.oracle.com/javaee/6/api/javax/persistence/EntityManager.html#getTransaction%28%29) and `.commit()`. — JimmyB, Sep 05 '14 at 08:36

score 1 · Answer 1 · answered Sep 04 '14 at 09:55

1

If possible can you use a stored procedure or a function to make it one transaction

answered Sep 04 '14 at 09:55

m b

310
1
8

I would like to do it as simple as possible. Similar to what I already use for other queries. Check my update. – Jordan Sep 04 '14 at 13:15

How to execute few queries in one transaction in my DAO class? Is it possible to do it creating one query?

1 Answers1