EJB Container Managed Transaction with JDBC Result Set and MDB

Question

I have a Stateless session bean. In the following method I loop the list to insert each of user into the table 'user'.

void saveUserList(List<User> users) {
  try {
    conn = dataSource.getConnection();
    PreparedStatement ps;

    try {
      ps = conn.prepareStatement(INSERT_USER_LIST);

      for (User user : users) {
        ps.setString(1, user.getName);

        if (ps.executeUpdate() > 0) {
          sendJMSMessage();
        }
      }
    }
  } catch (SQLException e) {
    // catch error!!
  } finally {
    closeResource(conn);
  }
}

Upon each successful insert, the trigger flag is sent to an MDB. Here is the sendJMSMessage method...

void sendJMSMessage() {
  conn = connectionFactory.createQueueConnection();
  qsession = conn.createQueueSession(false, Session.AUTO_ACKNOWLEDGE);
  QueueSender sender = qsession.createSender(queue);
  conn.start();

  TextMessage flag = qsession.createTextMessage("triggerFlag");
  flag.setJMSDeliveryMode(DeliveryMode.NON_PERSISTENT);
  sender.send(flag);
}

Then it will invoke the MDB's onMessage method. Here, in the onMessage, I want to fetch all users from the table 'user', including the last inserted user (which may be uncommitted yet when the loop is still iterating the list). The fetch process happened in another stateless EJB that is invoked by the onMessage. I expected the record is fetched there. But the result sets got nothing.

void onMessage(Message message) {
  TextMessage obj = (TextMessage) message;

  if (obj.getText.equals("triggerFlag")) {
    ArrayList<User> users = someBean.getUsers();
    // Do something with them, as well as the new user.
  }
}

With the current code, I usually need to re-execute the saveUserList() with passing a different list in order to get the expected result. What I wanted to know is:

1. Is it possible to do that?
2. When is the first insert transaction get commited?
3. Can MDB reads the uncommited changes in a table?
4. What is the proper way to read the table changes from MDB that was triggered by an EJB?
5. Is there alternatives method to do this?

Answer 1

1. Yes, it is possible.
2. It really depends on how you got the connection (or DataSource). If you unwrapped it from an injected EntityManager, then the transaction is automatically committed when your (first entered) EJB method returns (this is so by default, i.e if nothing otherwise annotated!)
3. Yes, if the MDB's connection uses the lowest transaction isolation level "READ_UNCOMMITTED". This is not trivial to implement and again depends on how you get the connection.
4. (&5)Probably the cleanest way to solve your problem is to control the transaction manually (use an injected UserTransaction, instead of Container Managed Transactions (CMT)). Only you are done if the entire list of users (not for every user as you do now) and after you have committed the transaction (programtically with UserTransaction), you are 100% that your second stateless bean will see all your users. In this case you should sent the message only once.