find all constraints using foreign key

Question

I have two entities viz:

State

@Entity
@Table(name = "State")
public class StateEntity {

   @Column(name = "id", length = 36, nullable = false, unique = true)
   private String id;

  @ManyToOne (fetch = FetchType.LAZY)
  @JoinColumn(name = "InsurerId", nullable = false)
  private InsurerEntity insurer;

  @Column(name ="StateName", length = 50, nullable = false)
  private String stateName;

//getters and setters

}

Insurer

@Entity
@Table(name = "Insurer")
public class InsurerEntity {

  @Column(name = "InsurerId", length = 36, nullable = false, unique = true)
  private String insurerId;

  @Column(name = "InsurerName", length = 100, nullable = true)
  private String insurerName;

  @OneToMany(mappedBy = "state", fetch = FetchType.LAZY)
  private List<StateEntity> stateEntityList;

//getters and setters

}

the insurer's id gets saved in state database as 'insurerid' and I want to retrieve it using hibernate criteria query but I can't seem to figure it out. It's easy to get foreign key using statename but when I try vice versa nothing works

I'm trying to execute this query: SELECT StateName FROM Mydb.state where InsurerId='74'

I tried this:1

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    @Transactional(readOnly = true)
    public List<StateEntity> findByForeignId(String id) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<StateEntity> criteriaQuery = criteriaBuilder.createQuery(StateEntity.class);
        Root<StateEntity> root = criteriaQuery.from(StateEntity.class);

        criteriaQuery = criteriaQuery.select(root).where(criteriaBuilder.equal(root.get("insurer"), id));
        try {
            return entityManager.createQuery(criteriaQuery).getResultList();
        } catch (NoResultException nre) {
            return null;
        } catch (NonUniqueResultException nure) {
            return null;
        }
    }

But it returns:

java.lang.IllegalArgumentException: Parameter value [74] did not match expected type [in.unlimitdigital.poleasy.entities.InsurerEntity (n/a)]

And:2

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    @Transactional(readOnly = true)
    public List<ModelEntity> findByForeignId(String id) {
        CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        CriteriaQuery<ModelEntity> criteriaQuery = criteriaBuilder.createQuery(ModelEntity.class);
        Root<BrandEntity> root = criteriaQuery.from(BrandEntity.class);

        criteriaQuery = criteriaQuery.multiselect(root).where(criteriaBuilder.equal(root.get("id"), id));
        try {
            return entityManager.createQuery(criteriaQuery).getResultList();
        } catch (NoResultException nre) {
            return null;
        } catch (NonUniqueResultException nure) {
            return null;
        }
    }

it returns:

java.lang.ClassCastException: org.hibernate.hql.internal.ast.tree.SqlNode cannot be cast to org.hibernate.hql.internal.ast.tree.FromReferenceNode

Please help me with this...

Answer 1

The issue is you missed the id variable inside your InsurerEntity class.

Change this line :

criteriaQuery = criteriaQuery.select(root).where(criteriaBuilder.equal(root.get("insurer"), id));

To this :

criteriaQuery = criteriaQuery.select(root).where(criteriaBuilder.equal(root.get("insurer").get("id"), id));