title: How to call multiple DAO functions in a transaction
body: <p>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 <code>.jar</code> which gets initialized with the used datasource. What I want to achieve is have my business logic level code do something like:</p>
  
  <pre><code>Connection conn = datasource.getConnection();
  conn.setAutoCommit(false);
  DAOObject1.query1(params, conn);
  DAOObject2.query4(params, conn);
  conn.commit();
  conn.setAutoCommit(false);
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>//Pseudocode
  try{
    Datasource.startTransactionLogic();
    DAO1.query(params);
    DAO2.query(params);
    Datasource.endAndCommitTransactionLogic();
  }
  catch(SQLException e){
    Datasource.rollbackTransaction();
  }
  </code></pre>
  
  <p>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 <code>savepoints</code>?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Let me point out that each <code>DAO</code> object's method, right now, obtains its own connection object. Here is an example of how our <code>DAO</code> classes will be:</p>
  
  <pre><code>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;
  }
  }
  </code></pre>
  
  <p>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 <code>Connection</code> object:</p>
  
  <pre><code>public void exampleUpdateMethod(DTO object, Connection connection) {
      //table update logic
  }
  </code></pre>
  
  <p>What we want is to avoid having such methods in our 'database api' <code>.jar</code> but instead be able to define the beginning and commit of a transaction in our business logic layer, like mentioned in the pseudocode above.</p>