Mismatch Between the Number of Records fetched By hibernate and Count on DB

Question

I have a Entity Like below

@Entity
@Table(name = "table1")
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class TableOneEntity {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;

@Column(name = "created_by")
private String createdBy;

@Column(name = "title_in_unicode")
private String titleInUnicode;


@OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, orphanRemoval = true)
@JoinColumn(name = "table1_id")
@LazyCollection(LazyCollectionOption.FALSE)
private Set<ErrorMessages> errorMessages = new HashSet<ErrorMessages>();

The Error Message Entity is like

@Entity
@Table(name = "error_messages")
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
public class ErrorMessages {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private Long errorId;

@Column(name = "table1_id")
private Long table1Id;

@Column(name = "field_key")
private String fieldKey;

@Column(name = "field_value")
private String fieldValue;

I am using hibernate criteria to fetch all the records from the DB;

@SuppressWarnings("unchecked")
@Override
public List listTableOneEntites()
{
Criteria criteria = getSession().createCriteria(TableOneEntity.class);
return (List) criteria.list();
}

when I printing the size of the List its giving me 417 but on doing the count * on DB on the same table the entries are 410 .

But my colleague who is running the same code in his local machine with the same dump is getting both the entries as 410.

We both are using mysql DB.
Has anyone any idea about this mismatch in count.

Have you tried stopping and restarting your program? Hibernate maintains it's own cache, so if someone or some other process has deleted records, your hibernate would be out of date. — racraman
yes I have re-deployed and restarted my tomcat but the same problem is still there — sid297

haggisandchips haggisandchips · Accepted Answer · 2016-05-20T13:22:26

You are eagerly fetching the ErrorMessage entity which is a one to many so my guess is that some of the TableOneEntity's have more than one ErrorMessage's joined to them.

Try adding criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY). If this works then you may wish to research it further - I can't remember if it forces a switch to multiple selects rather than a join but my gut feeling is that since you only have one join it should still be able to do it efficiently.

Why your colleague would get different results is beyond me however - do you both definitely have the exact same data? Are you executing the joined query when you run the count *? If not then try that and see if yours jumps to 417.

Mismatch Between the Number of Records fetched By hibernate and Count on DB

1 Answers