0

I'm trying to get the following query using the Criteria API:

select lang.* from LKUP_LANG lang LEFT OUTER JOIN LANG_SEQUENCES seq ON
lang.SHORT_NAME=seq.LANG_CODE and (lang.DISPLAY_NAME is not null)
order by nvl(seq.SEQ_LANG_CODE, 2147483647) asc;

I did:

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Lang> criteriaQuery = builder.createQuery(Lang.class);
Root<Lang> languageRoot = criteriaQuery.from(Lang.class);

List<Predicate> predicates = new ArrayList<>();
// added some predicates
criteriaQuery.orderBy(...);

Tried adding 

Join<Lang, LANG_SEQUENCES> join = languageRoot.join("languageSequences",JoinType.LEFT);

The entity looks like

LanguageSequences
@Id
@Column(name = "LANG_CODE")
private String langCode;

@Column(name = "SEQ_LANG_CODE")
private int seqLangcode;

@OneToOne
@Fetch(FetchMode.JOIN)
@JoinColumn(name = "LANG_CODE", referencedColumnName = "SHORT_NAME")
private Lang lang;

and

Lang
@Column(name = "DISPLAY_NAME", unique = true)
private String displayName;

@Id
@Column(name = "SHORT_NAME")
private String shortName;

@OneToOne(cascade = CascadeType.ALL, mappedBy = "lkupLanguage")
private LanguageSequences languageSequences;

Tried using joins in different ways but it looks like I'm missing something. I get the orderBy, the predicates all correct. I'm using JPA 2.1 and Oracle.

Gaurav Goenka
  • 152
  • 12

1 Answers1

0

In JPA you can not select a sequence, you should use native querys

JLazar0
  • 1,257
  • 1
  • 11
  • 22