Which layer can I put the mysql transaction database logic?

Question

Basically I have two services each one of them handle methods for each persistent object that I have in my project, these services hold some method which the endpoint(Google) will call to perform something. I'm using Google Could Endpoints + Mysql Cloud + Hibernate.

Two POs

@Entity
public class Device {
    ...
}

@Entity
public class User {
    ...
}

The services for each one of POs

public class DeviceService {
    Device getDevice(Long devId){
        return new Dao().getById(devId, Device.class);
    }
    void allocateDevice(Long userId){
        User u = new UserService().getUser(userId);
        ... do stuff
    }
}

public class UserService {
    User getUser(Long userId){
        return new Dao().getById(userId, User.class);
    }
}

The endpoint for each one

public class DeviceEndpoint {
    @ApiMethod(
        name = "device.get",
        path = "device/{devId}",
        httpMethod = ApiMethod.HttpMethod.GET
    )
    Device getDevice(Long devId){
        MyEntityManager em = new MyEntityManager();
        try {
           em.getTransaction().begin();
           new DeviceService().getDevice(devId);
           em.getTransaction().commit();
        }finally {
            em.cleanup(); //custom method to rollback also
        }
        return device;
    }

    @ApiMethod(
        name = "device.allocate",
        path = "device/{userId}/allocate",
        httpMethod = ApiMethod.HttpMethod.GET
    )
    void allocateDevice(Long deviceId){
        MyEntityManager em = new MyEntityManager();
        try {
           em.getTransaction().begin();
           new DeviceService().allocateDevice(userId);
           em.getTransaction().commit();
        }finally {
            em.cleanup(); //custom method to rollback also
        }
    }
}

I would like to know where I put the database transaction logic(begin,commit,rollback).

Dao layer

Firstly I had inserted into Dao class, but every query/insert/update I had to open and close the connection and when I had to use more than one CRUD I did several open/close connections and it had been expensive and delayed. Example: In one endpoint request I want to obtain some object from db and update. Two operations and two open/close connections.

Endpoint layer(as example)

Secondly I put the logic to open/close on endpoint methods(as example above), but they said(my work colleagues) it isn't a good pattern, begin and commit transactions in this layer isn't a good idea, then they suggested to do the third option.

Service layer

Put that logic(begin/commit/rollback) into Service layer, in each method, I tried but, some methods call another and that last also open and close the connection, so when the second method return, the transaction came closed.

Please, let me know case missing some important info.

First, you can't put any logic in the service layer. It should tells you almost nothing about what the object actually does, And as Oracle doc says: Use a Data Access Object (DAO) to abstract and encapsulate all access to the data source. The DAO manages the connection with the data source to obtain and store data. you could add a business model layer and add the logic in — Fady Saad
As I mentioned in the first option that I had made, that logic into DAO did not avoid, for example when I want to perform a few operations in the database for just one request, it has opened and committed the several times. I need avoid this behavior, because its so expensive, on the endpoint layer seems very faster than the other. — D. Pazeto

Yaron Yaron · Accepted Answer · 2017-07-26T16:48:25

Typically This type of action is performed in the Service Layer as this layer is there to provide logic to operate on the data sent to and from the DAO layer - that being said you could bundle these together into the same module.

The comment "I tried but, some methods call another and that last also open and close the connection, so when the second method return, the transaction came closed." Is interesting; I am not sure how you are managing your connections; but you may want/need to revisit if your connections are being closed before transactions are completed - you may want to look at Hibernates HibernateTransactionManager

Where should "@Transactional" be place Service Layer or DAO

Which layer can I put the mysql transaction database logic?

1 Answers