How to call multiple DAO functions in a transaction

Question

I'm looking for a way to call multiple DAO functions in a transaction but I am NOT using spring or any such framework. What we actually have is a Database api type .jar which gets initialized with the used datasource. What I want to achieve is have my business logic level code do something like:

Connection conn = datasource.getConnection();
conn.setAutoCommit(false);
DAOObject1.query1(params, conn);
DAOObject2.query4(params, conn);
conn.commit();
conn.setAutoCommit(false);

however I want to avoid passing the connection object in every single function since this is not the correct way to do it. Right now in the few transactions we have we use this but we are looking for a way to stop passing the connection object to the database layer or even create it outside of it. I'm looking for something along the lines of:

//Pseudocode
try{
  Datasource.startTransactionLogic();
  DAO1.query(params);
  DAO2.query(params);
  Datasource.endAndCommitTransactionLogic();
}
catch(SQLException e){
  Datasource.rollbackTransaction();
}

Could I achieve this through EJBs? Right now we're not using DAOs through injection, we're creating them by hand but we're about to migrate to EJBs and start using them via the container. I've heard that all queries executed by EJBs are transactional but how does it know what to rollback to? Through savepoints?

EDIT:

Let me point out that each DAO object's method, right now, obtains its own connection object. Here is an example of how our DAO classes will be:

public class DAO {
public DTO exampleQueryMethod(Integer id) {
    DTO object = null;
    String sql = "SELECT * FROM TABLE_1 WHERE ID = ?";
    try (
        Connection connection = datasourceObject.getConnection();
        PreparedStatement statement = connection.prepareStatement(sql)
    ) {
        statement.setInt(1,  id);
        try (ResultSet resultSet = statement.executeQuery()) {
            if (resultSet.next()) {
                object = DAO.map(resultSet);
            }
        }
    }
    return object;
}
}

Right now what we're doing for methods that need to be in a transaction is to have a second copy of them that receive a Connection object:

public void exampleUpdateMethod(DTO object, Connection connection) {
    //table update logic
}

What we want is to avoid having such methods in our 'database api' .jar but instead be able to define the beginning and commit of a transaction in our business logic layer, like mentioned in the pseudocode above.

Answer 1

EDIT: After accumulating a few more years of experience, I'd like to point out that the simplest and most correct answer to this question was to use ThreadLocal objects to contain the Connection (since it's request scoped only a single threads executes it). Unfortunately at the time I didn't know the existence of such a construct.

@G. Demecki has the right idea but I followed a different implementation. Interceptors couldn't solve the problem (at least from what I saw) because they need to be attached to each function that is supposed to use them. Also once an interceptor is attached, calling the function will always have it intercepted which is not my goal. I wanted to be able to explicitly define the beginning and ending of a transaction, and have every sql executed between these 2 statements be part of the SAME transaction, without it having access to the database's related objects (like connection, transaction etc) through argument passing. The way I was able to achieve this (and quite elegant in my opinion) is the following:

I created a ConnectionWrapper object like so:

@RequestScoped
public class ConnectionWrapper {

@Resource(lookup = "java:/MyDBName")
private DataSource dataSource;

private Connection connection;

@PostConstruct
public void init() throws SQLException {
    this.connection = dataSource.getConnection();
}

@PreDestroy
public void destroy() throws SQLException {
    this.connection.close();
}

public void begin() throws SQLException {
    this.connection.setAutoCommit(false);
}

public void commit() throws SQLException {
    this.connection.commit();
    this.connection.setAutoCommit(true);
}

public void rollback() throws SQLException {
    this.connection.rollback();
    this.connection.setAutoCommit(true);
}

public Connection getConnection() {
    return connection;
}
}

My DAO objects themselves follow this pattern:

@RequestScoped
public class DAOObject implements Serializable {

private Logger LOG = Logger.getLogger(getClass().getName());

@Inject
private ConnectionWrapper wrapper;

private Connection connection;

@PostConstruct
public void init() {
    connection = wrapper.getConnection();
}

public void query(DTOObject dto) throws SQLException {
    String sql = "INSERT INTO DTO_TABLE VALUES (?)";
    try (PreparedStatement statement = connection.prepareStatement(sql)) {
        statement.setString(1, dto.getName());
        statement.executeUpdate();
    }
}
}

Now I can easily have a jax-rs resource which @Injects these objects and starts and commits a transaction, without having to pass any Connection or UserTransaction around.

@Path("test")
@RequestScoped
public class TestResource {

@Inject
ConnectionWrapper wrapper;

@Inject
DAOObject dao;

@Inject
DAOObject2 dao2;

@GET
@Produces(MediaType.TEXT_PLAIN)
public Response testMethod() throws Exception {
    try {
        wrapper.begin();
        DTOObject dto = new DTOObject();
        dto.setName("Name_1");
        dao.query(dto);
        DTOObject2 dto2 = new DTOObject2();
        dto2.setName("Name_2");
        dao2.query2(dto2);
        wrapper.commit();
    } catch (SQLException e) {
        wrapper.rollback();
    }
    return Response.ok("ALL OK").build();
}
}

And everything works perfectly. No Interceptors or looking around InvocationContext etc.

There are only 2 things bothering me:

1. I have not yet found a way to have a dynamic JNDI name on @Resource(lookup = "java:/MyDBName") and this bothers me. In our AppServer we have defined many datasources and the one used by the application is dynamically chosen according to an .xml resource file packaged with the war. Which means that I can't know the datasource JNDI on compile time. There is the solution of obtaining a datasource through InitialContext() environment variable but I'd love to be able to get it as a resource from the server. I could also create a @Produces producer and inject it that way but still.
2. I'm not really sure why ConnectionWrapper's @PostConstruct gets called BEFORE the DAOObject's @PostConstruct. It is the correct and desired behavior but I haven't understood why. I'm guessing since DAOObject @Injects a ConnectionWrapper, its @PostConstruct takes precedence since it has to have finished before the DAOObjects's can even start but this is just a guess.