JPA EntityManager createQuery returns no results

Question

The following line is attempting to retrieve all rows from the database table "Sourcing" using a JPA Container-Managed JPA2.0 EntityManager, however it returns no results, even though there appear to be 2 rows in the table:

em.createQuery("from Sourcing").getResultList();

I am using JBoss 6.0, JVM 1.6, MySQL 5.5.10 and Hibernate 3.6.3

The class that originally persists the Sourcing object is (summarised):

@Stateless
@Local
public final class MyBean implements MyBeanLocal {

    @PersistenceContext
    private EntityManager em;

    public MyBean() {}

    public void methodA() {
        methodB();
    }

    private void methodB() {
        methodC();
    }

    private void methodC() {
        Sourcing sourcing = new Sourcing( … values ...);
        em.persist(sourcing);
    }
}

hibernate.show_sql output on the console shows the SQL statement from Hibernate's perspective (being sent to MySQL)

select sourcing0_.sourcingId as sourcingId7_, sourcing0_.foundResults as foundRes2_7_, sourcing0_.numberOfAdults as numberOf3_7_, sourcing0_.numberofchildren as numberof4_7_, sourcing0_.numberofinfants as numberof5_7_, sourcing0_.numberofseniors as numberof6_7_, sourcing0_.scopeBeginDateTime as scopeBeg7_7_, sourcing0_.scopeEndDateTime as scopeEnd8_7_, sourcing0_.searchId as searchId7_, sourcing0_.searchDateTime as searchDa9_7_, sourcing0_.serviceId as serviceId7_, sourcing0_.sourceId as sourceId7_, sourcing0_.sourcingFinishDateTime as sourcin10_7_, sourcing0_.sourcingStartedDateTime as sourcin11_7_, sourcing0_.timeStamp as timeStamp7_, sourcing0_.version as version7_ from sourcing sourcing0_

The following output from the MySQL log file shows that the rows were previously inserted and committed:

223 Query   insert into sourcing (foundResults, numberOfAdults, numberofchildren, numberofinfants, numberofseniors, scopeBeginDateTime, scopeEndDateTime, searchId, searchDateTime, serviceId, sourceId, sourcingFinishDateTime, sourcingStartedDateTime, timeStamp, version) values (0, 1, 0, 0, 0, null, null, 1, '2011-08-15 00:00:00', 372, 2, null, '2011-07-18 15:47:00', null, 0)
223 Query   insert into sourcing (foundResults, numberOfAdults, numberofchildren, numberofinfants, numberofseniors, scopeBeginDateTime, scopeEndDateTime, searchId, searchDateTime, serviceId, sourceId, sourcingFinishDateTime, sourcingStartedDateTime, timeStamp, version) values (0, 1, 0, 0, 0, null, null, 1, '2011-08-15 00:00:00', 530, 2, null, '2011-07-18 15:47:00', null, 0)
224 Connect user@localhost on mydatabase
224 Query   /* mysql-connector-java-5.1.15 ( Revision: ${bzr.revision-id} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect'
224 Query   /* mysql-connector-java-5.1.15 ( Revision: ${bzr.revision-id} ) */SELECT @@session.auto_increment_increment
224 Query   SHOW COLLATION
224 Query   SET NAMES latin1
224 Query   SET character_set_results = NULL
224 Query   SET autocommit=1
224 Query   SET sql_mode='STRICT_TRANS_TABLES'
224 Query   SELECT @@session.tx_isolation
224 Query   SET autocommit=0
.....
223 Query   commit
224 Query   select sourcing0_.sourcingId as sourcingId7_, sourcing0_.foundResults as foundRes2_7_, sourcing0_.numberOfAdults as numberOf3_7_, sourcing0_.numberofchildren as numberof4_7_, sourcing0_.numberofinfants as numberof5_7_, sourcing0_.numberofseniors as numberof6_7_, sourcing0_.scopeBeginDateTime as scopeBeg7_7_, sourcing0_.scopeEndDateTime as scopeEnd8_7_, sourcing0_.searchId as searchId7_, sourcing0_.searchDateTime as searchDa9_7_, sourcing0_.serviceId as serviceId7_, sourcing0_.sourceId as sourceId7_, sourcing0_.sourcingFinishDateTime as sourcin10_7_, sourcing0_.sourcingStartedDateTime as sourcin11_7_, sourcing0_.timeStamp as timeStamp7_, sourcing0_.version as version7_ from sourcing sourcing0_

In summary, any idea why createQuery("from Sourcing").getResultList() returns no results, even though:

1. Running "select * from Sourcing" from the MySQL command line returns 2 results
2. copying and pasting the raw SQL output from Hibernate into the MySQL command line also returns 2 results
3. the MySQL log shows that the rows have been inserted and committed to the database

Something strange seems to be going on here! Where should I look next? Transactions, caching?

UPDATE: The Entity class is defined as follows (some getters and setters removed):

@Entity
@Table(name = "sourcing")
public class Sourcing implements java.io.Serializable, Cloneable {

private static final long serialVersionUID = 1L;
private Integer sourcingId;
private Service service;
private Search search;
private Source source;
private Date searchDateTime;
private Date sourcingStartedDateTime;
private Date sourcingFinishedDateTime;
private Date scopeBeginDateTime;
private Date scopeEndDateTime;
private Integer numberOfAdults;
private Integer numberOfChildren;
private Integer numberOfInfants;
private Integer numberOfSeniors;
private Boolean foundResults;

private int version;
private Date timeStamp;

private boolean isFareMonitoring = false;

private List<Flight> flights = new ArrayList<Flight>(0);
private List<Segment> segments = new ArrayList<Segment>(0);
private List<Fare> fares = new ArrayList<Fare>(0);

public Sourcing() {
}

public Sourcing(Search search, Source source, Service service,
        Date sourcingStartedDateTime,
        Integer numberOfAdults, Integer numberOfChildren,
        Integer numberOfInfants, Integer numberOfSeniors,
        Boolean foundResults) {

    this.search = search;
    this.source = source;
    this.service = service;
    this.sourcingStartedDateTime = sourcingStartedDateTime;
    this.numberOfAdults = numberOfAdults;
    this.numberOfChildren = numberOfChildren;
    this.numberOfInfants = numberOfInfants;
    this.numberOfSeniors = numberOfSeniors;
    this.foundResults = foundResults;

    if (search.isFareMonitoring()) {
        this.isFareMonitoring = true;
    }
}

@Id
@GeneratedValue(strategy = IDENTITY)
@Column(name = "sourcingId", unique = true, nullable = false)
public Integer getSourcingId() {
    return this.sourcingId;
}

public void setSourcingId(Integer sourcingId) {
    this.sourcingId = sourcingId;
}

@Version
@Column(columnDefinition="TINYINT")
public int getVersion() {
    return version;
}

public void setVersion(int version) {
    this.version = version;
}

@Temporal(TemporalType.TIMESTAMP)
@Column(name="timeStamp", columnDefinition="TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP")
public Date getTimeStamp() {
    return timeStamp;
}

public void setTimeStamp(Date timeStamp) {
    this.timeStamp = timeStamp;
}

 // to make 3 days, beginOffSet = -1, endOffSet = 1 [ i.e. -1, 0, 1]
public static void setScope(Sourcing sourcing, int beginOffSet, int endOffSet) {  

    Calendar cal = new GregorianCalendar();
    cal.setTime(sourcing.getSearchDateTime());

    cal.add(Calendar.DAY_OF_MONTH, beginOffSet);
    sourcing.setScopeBeginDateTime(cal.getTime());

    cal.add(Calendar.DAY_OF_MONTH, (-beginOffSet + endOffSet)); // --1 + 1 = 2
    sourcing.setScopeEndDateTime(cal.getTime());
}

@Transient
public final boolean isFareMonitoring() {
    return isFareMonitoring;
}

public final void setFareMonitoring(boolean isFareMonitoring) {
    this.isFareMonitoring = isFareMonitoring;
}
}

---

UPDATE (July 20th) I enabled org.hibernate.jdbc and org.hibernate.loader TRACE logging as suggested. The outputs are below:

18:25:02,891 DEBUG [com.myproject.beans.ReceiveFareBean] SLEEPING FOR 3 SECONDS ......
18:25:05,890 DEBUG [com.myproject.beans.ReceiveFareBean] AWAKE. 
18:25:05,891 TRACE [org.hibernate.engine.query.QueryPlanCache] located HQL query plan in cache (from Sourcing)
18:25:05,891 TRACE [org.hibernate.engine.query.QueryPlanCache] located HQL query plan in cache (from Sourcing)
18:25:05,891 TRACE [org.hibernate.engine.query.HQLQueryPlan] find: from Sourcing
18:25:05,891 TRACE [org.hibernate.engine.QueryParameters] named parameters: {}
18:25:05,891 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
18:25:05,891 DEBUG [org.hibernate.jdbc.ConnectionManager] opening JDBC connection
18:25:05,891 DEBUG [org.hibernate.SQL] select sourcing0_.sourcingId as sourcingId7_, sourcing0_.foundResults as foundRes2_7_, sourcing0_.numberOfAdults as numberOf3_7_, sourcing0_.numberofchildren as numberof4_7_, sourcing0_.numberofinfants as numberof5_7_, sourcing0_.numberofseniors as numberof6_7_, sourcing0_.scopeBeginDateTime as scopeBeg7_7_, sourcing0_.scopeEndDateTime as scopeEnd8_7_, sourcing0_.searchId as searchId7_, sourcing0_.searchDateTime as searchDa9_7_, sourcing0_.serviceId as serviceId7_, sourcing0_.sourceId as sourceId7_, sourcing0_.sourcingFinishDateTime as sourcin10_7_, sourcing0_.sourcingStartedDateTime as sourcin11_7_, sourcing0_.timeStamp as timeStamp7_, sourcing0_.version as version7_ from sourcing sourcing0_
18:25:05,892 INFO  [STDOUT] Hibernate: select sourcing0_.sourcingId as sourcingId7_, sourcing0_.foundResults as foundRes2_7_, sourcing0_.numberOfAdults as numberOf3_7_, sourcing0_.numberofchildren as numberof4_7_, sourcing0_.numberofinfants as numberof5_7_, sourcing0_.numberofseniors as numberof6_7_, sourcing0_.scopeBeginDateTime as scopeBeg7_7_, sourcing0_.scopeEndDateTime as scopeEnd8_7_, sourcing0_.searchId as searchId7_, sourcing0_.searchDateTime as searchDa9_7_, sourcing0_.serviceId as serviceId7_, sourcing0_.sourceId as sourceId7_, sourcing0_.sourcingFinishDateTime as sourcin10_7_, sourcing0_.sourcingStartedDateTime as sourcin11_7_, sourcing0_.timeStamp as timeStamp7_, sourcing0_.version as version7_ from sourcing sourcing0_
18:25:05,892 TRACE [org.hibernate.jdbc.AbstractBatcher] preparing statement
18:25:05,892 TRACE [org.hibernate.loader.Loader] Bound [1] parameters total
18:25:05,892 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open ResultSet (open ResultSets: 0, globally: 0)
18:25:05,893 TRACE [org.hibernate.loader.Loader] processing result set
18:25:05,893 TRACE [org.hibernate.loader.Loader] done processing result set (0 rows)
18:25:05,893 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close ResultSet (open ResultSets: 1, globally: 1)
18:25:05,893 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
18:25:05,893 TRACE [org.hibernate.jdbc.AbstractBatcher] closing statement
18:25:05,893 DEBUG [org.hibernate.jdbc.ConnectionManager] aggressively releasing JDBC connection
18:25:05,893 DEBUG [org.hibernate.jdbc.ConnectionManager] releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]
18:25:05,893 TRACE [org.hibernate.loader.Loader] total objects hydrated: 0
18:25:05,893 DEBUG [org.hibernate.engine.StatefulPersistenceContext] initializing non-lazy collections
18:25:05,893 DEBUG [com.myproject.beans.ReceiveFareBean] NUMBER OF SOURCINGS CURRENTLY IN THE DATABASE = 0
18:25:05,893 DEBUG [com.myproject.beans.ReceiveFareBean] SLEEPING FOR 3 SECONDS ......

---

UPDATE July 21st:

I attempted the "select * from Sourcing" query using a JPA Criteria query (below) instead of .createQuery("from Sourcing") to see if it made a difference, however it didn't solve the issue (still no results returned)

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Sourcing> criteriaQuery = criteriaBuilder.createQuery(Sourcing.class);
Root<Sourcing> sourcing = criteriaQuery.from(Sourcing.class);
criteriaQuery.select(sourcing);
TypedQuery<Sourcing> typedQuery = em.createQuery(criteriaQuery);
List<Sourcing> sourcings = typedQuery.getResultList();

Answer 1

Solved: Rows in the database are not made available to other transactions (i.e. EntityManager.merge()) until the EJB bean has fully executed, therefore allowing the transaction to COMMIT to the database. By using MySQL logging, I noticed that Hibernate was issuing the statement "SET AUTOCOMMIT=0", which will cause inserts/updates etc to be proceeded by a COMMIT statement in order for subsequent queries to see those rows. So there was a while-loop towards the end of my EJB Message-Driven-Bean (could have been a Session bean) that was preventing the bean from fully executing, and therefore committing to the database. I didn't need to set any per-method Transactional values inside any of the beans, and they could be happily nested, and use multiple EntityManagers for the same transaction (within the Container Managed Transaction environment)