Hi Community,
I create my Querys using Criteria.
The user of my program is able to do some selections and in order to this selection I create the Criteria.
This works fine, but one combination doesn't work and I don't know why.
I've got a branch and a branch can have a country and a status. (and a few other properties, but there are not important now)
If I create my Criteria, that the country and the status are restricted, I become the Error "could not execute query"
Following a few lines of code which are used to do the Criteria.
My branch-class
Code:
@ManyToOne(fetch = FetchType.EAGER, cascade = CascadeType.PERSIST)
@JoinColumn(name = "OrtID", insertable=true, updatable=true)
private Standort myOrt;
@OneToMany(fetch=FetchType.LAZY, cascade = CascadeType.PERSIST, mappedBy="myFiliale")
@OrderBy(value = "aktiv asc, erfasstam asc, erfasstum asc")
private Set <FilialStatus> filialstatus = new HashSet<FilialStatus>();
My Location-class
Code:
@Column(name = "PLZ")
private String plz;
@Column(name = "BEZEICHNUNG",insertable=true, updatable=true)
private String ort;
@ManyToOne(fetch=FetchType.EAGER,cascade = CascadeType.ALL)
@JoinColumn(name = "KREISID", insertable=true, updatable=true)
private Kreis myKreis;
@ManyToOne(fetch=FetchType.EAGER,cascade = CascadeType.ALL)
@JoinColumn(name="BUNDESLANDID",insertable=true, updatable=true)
private Bundesland myBundesland;
@ManyToOne(fetch=FetchType.EAGER,cascade = CascadeType.ALL)
@JoinColumn(name = "LANDID", insertable=true, updatable=true)
private Land myLand;
My country-class
Code:
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "ID")
private Integer id;
@Column(name = "BEZEICHNUNG", insertable=true, updatable=true)
private String bezeichnung;
@Column(name = "LANDKZ", insertable=true, updatable=true)
private String landkz;
@OneToMany
private Set <Standort> standort = new HashSet<Standort>();
My "Branch-Status"-class
Code:
@Id
private FilialStatusPK pk = new FilialStatusPK();
@ManyToOne(fetch=FetchType.EAGER,cascade = CascadeType.PERSIST)
@JoinColumn(name = "UNTERSTATUSID", referencedColumnName="UNTERSTATUSID", insertable=false, updatable=false)
private Unterstatus myUnterStatus;
@ManyToOne(fetch=FetchType.EAGER,cascade = CascadeType.PERSIST)
@JoinColumn(name = "FILIALID", referencedColumnName="FILIALID", insertable=false, updatable=false)
private Filiale myFiliale;
@Column(name = "ERFASSTAM")
private Date erfasstam;
@Column(name = "ERFASSTUM")
private Time erfasstum;
@Column(name = "AKTIV")
private Integer aktiv;
and my under-status-class
Code:
@ManyToOne(fetch=FetchType.EAGER,cascade = CascadeType.PERSIST)
@JoinColumn(name = "OBERSTATUSID", insertable=true, updatable=true)
private Status myStatus;
@Column(name="BEZEICHNUNG")
private String bezeichnung;
Now I create my Criteria with this Methods (if I restrict Country and Status)
Code:
private Criteria searchKundeByStatus(Criteria crit, Object[] statuse) {
if (statuse != null && statuse.length > 0) {
Integer[] statids = new Integer[statuse.length];
Unterstatus stat = null;
for (int index = 0; index < statuse.length; index++) {
stat = (Unterstatus) statuse[index];
statids[index] = stat.getUnterstatusID();
}
crit.createAlias("filialstatus", "filstat")
.add(Restrictions.eq("filstat.aktiv", 1))
.add(Restrictions.in("filstat.myUnterStatus.unterstatusID", statids));
}
return crit;
}
private Criteria searchKundeByLand(Criteria crit, Object[] laender) {
if (laender != null && laender.length > 0) {
Integer[] laenderid = new Integer[laender.length];
for (int index = 0; index < laender.length; index++) {
laenderid[index] = ((Land) laender[index]).getID();
}
crit.createAlias("myOrt.myLand", "land")
.add(Restrictions.in("land.id", laenderid));
}
return crit;
}
The finally criteria (give out with .toString()) is:
Quote:
CriteriaImpl(de.Database.Filiale:this[Subcriteria(myOrt.myLand:land), Subcriteria(filialstatus:filstat)][land.id in (2), filstat.aktiv=1, filstat.myUnterStatus.unterstatusID in (1)])
now I do:
Code:
crit.list();
to get my values.
At this point I get:
Quote:
org.hibernate.exception.SQLGrammarException: could not execute query
java.sql.SQLException: [SQL5001] Qualifikationsmerkmal für Spalte oder Tabelle LAND1_ nicht definiert.
The SQL generated is
Code:
Hibernate:
/* criteria query */ select
this_.FILIALID as FILIALID5_13_,
.....
filstat2_.FILIALID as FILIALID12_0_,
filstat2_.UNTERSTATUSID as UNTERSTA2_12_0_,
filstat2_.AKTIV as AKTIV12_0_,
filstat2_.ERFASSTAM as ERFASSTAM12_0_,
filstat2_.ERFASSTUM as ERFASSTUM12_0_,
.....
standort8_.ID as ID0_4_,
standort8_.BUNDESLANDID as BUNDESLA4_0_4_,
standort8_.KREISID as KREISID0_4_,
standort8_.LANDID as LANDID0_4_,
standort8_.BEZEICHNUNG as BEZEICHN2_0_4_,
standort8_.PLZ as PLZ0_4_,
bundesland9_.ID as ID2_5_,
bundesland9_.BEZEICHNUNG as BEZEICHN2_2_5_,
kreis10_.ID as ID1_6_,
kreis10_.BEZEICHNUNG as BEZEICHN2_1_6_,
land11_.ID as ID3_7_,
land11_.BEZEICHNUNG as BEZEICHN2_3_7_,
land11_.LANDKZ as LANDKZ3_7_,
.....
unterstatu16_.UNTERSTATUSID as UNTERSTA1_11_11_,
unterstatu16_.BEZEICHNUNG as BEZEICHN2_11_11_,
unterstatu16_.OBERSTATUSID as OBERSTAT3_11_11_,
status17_.ID as ID10_12_,
status17_.BEZEICHNUNG as BEZEICHN2_10_12_
from
FILIALE this_
inner join
FILIALSTATUS filstat2_
on this_.FILIALID=filstat2_.FILIALID
left outer join
FILIALE filiale5_
on filstat2_.FILIALID=filiale5_.FILIALID
left outer join
STANDORT standort8_
on filiale5_.OrtID=standort8_.ID
left outer join
BUNDESLAND bundesland9_
on standort8_.BUNDESLANDID=bundesland9_.ID
left outer join
KREIS kreis10_
on standort8_.KREISID=kreis10_.ID
left outer join
LAND land11_
on standort8_.LANDID=land11_.ID
left outer join
UNTERSTATUS unterstatu16_
on filstat2_.UNTERSTATUSID=unterstatu16_.UNTERSTATUSID
left outer join
STATUS status17_
on unterstatu16_.OBERSTATUSID=status17_.ID
where
land1_.ID in (
?
)
and filstat2_.AKTIV=?
and filstat2_.UNTERSTATUSID in (
?
)
And of course I see, that "land1_" isnt created. But I don't know why it's getting used by the Criteria and not "land_11".
Any ideas why this error happens?
If i only restrict the query by Stauts OR by Land it works fine. But in combination it doesn't work
Login
FAQ
Search
