Cannot select data with filter on fields in @EmbeddedId PK

Hibernate ORM
1

I have an Oracle table MRS_CON_HIST, the primary key has 4 columns, the corresponding entity is MrsConHist, @EmbeddedId is MrsConHistPK. One of the key members is CON_NO (conNo) . I failed in retrieving MRS_CON_HIST records with filter on the field(s) in MrsConHistPK. As the following samples:
from MrsConHist where getComp_id().getConNo() = '102' or
from MrsConHist where CON_NO = '102' //or conNo='102'
the error is attached at the end.
My code:
1. MrsConHist.java

package com.maris.entity;
import java.util.Date;
import java.util.HashSet;
import java.util.Set;
import jakarta.persistence.Column;
import jakarta.persistence.EmbeddedId;
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.JoinColumn;
import jakarta.persistence.ManyToOne;
import jakarta.persistence.OneToMany;
import jakarta.persistence.Table;

@Table(name = "MRS_CON_HIST")
@Entity
public class MrsConHist implements java.io.Serializable {

	private static final long serialVersionUID = 1L;
	@EmbeddedId
	private MrsConHistPK comp_id;
    @Column(name = "DATETIME_OUTSRV")
	private Date datetimeOutsrv;    
    @Column(name = "RUN_TIME_HRS")
	private int runTimeHrs;
    @Column(name = "RUN_TIME_MIN")
	private int runTimeMin;
    @Column(name = "CON_STAT")
	private String conStat;
    @Column(name = "CON_SIZE")
	private int conSize;
    @Column(name = "ORIGIN_LOC")
	private String originLoc;
    @Column(name = "OUTSRV_LOC")
	private String outsrvLoc;
    @Column(name = "CON_NO_SPLIT")
	private String conNoSplit;
    @Column(name = "DASH_NO_SPLIT")
	private int dashNoSplit;
    @Column(name = "RUN_TIME_SPLIT_MIN")
	private int runTimeSplitMin;
    @Column(name = "RUN_TIME_SPLIT_HRS")
	private int runTimeSplitHrs;
	 @Column(name = "ORIGIN_LOC_SWAP")
	private String originLocSwap;
	 @Column(name = "WASH_IND")
	private String washInd;
	 @Column(name = "MADE_IN_ERR_IND")
	private String madeInErrInd;
	 @Column(name = "DATETIME_LAST_UPD")
	private Date datetimeLastUpd;
	 @Column(name = "TERM_LAST_UPD")
	private String termLastUpd;
	 @Column(name = "DATETIME_MAINT_UPD")
	private Date datetimeMaintUpd;
	 @Column(name = "TERM_MAINT_UPD")
	private String termMaintUpd;
	 @Column(name = "CON_NO_SWAP")
	private String conNoSwap;
	 @Column(name = "DASH_NO_SWAP")
	private int dashNoSwap;
	 @Column(name = "FROM_YRD_TRKG_IND")
	private String fromYrdTrkgInd;
	 @Column(name = "REV_TRAIN_PLAN")
	private String revTrainPlan;
	 @Column(name = "COMB_CON_NO_L")
	private String combConNoL;
	 @Column(name = "COMB_CON_NO_R")
	private String combConNoR;
	 @Column(name = "COMB_DASH_NO_L")
	private int combDashNoL;
	 @Column(name = "COMB_DASH_NO_R")
	private int combDashNoR;
	 @Column(name = "COMB_WITH_IND")
	private String combWithInd;
	 @Column(name = "COMB_PSEUDO_L_IND")
	private String combPseudoLInd;
	 @Column(name = "COMB_PSEUDO_R_IND")
	private String combPseudoRInd;
	 @Column(name = "DATETIME_MAKE_TRAIN")
	private Date datetimeMakeTrain;
	 @Column(name = "DATETIME_INSRV_ICS")
	private Date datetimeInsrvIcs;
	 @Column(name = "DISPATCH_TO_LOC")
	private String dispatchToLoc;
	 @Column(name = "DISPATCH_TO_YRD")
	private String dispatchToYrd;
	 @Column(name = "LAYUP_ACTION_CD")
	private String layupActionCd;
	 @Column(name = "NO_CARS_IND")
	private String noCarsInd;
	 @Column(name = "MSG_LOC")
	private String msgLoc;
	 @Column(name = "ORIENT_OF_CARS")
	private String orientOfCars;
	 @Column(name = "SCANNER_ID")
	private String scannerId;
	 @Column(name = "TIME_INSRV_ORIG")
	private String scannerDir;
	 @Column(name = "SCANNER_DIR")
	private Date timeInsrvOrig;

	@ManyToOne
	@JoinColumn(name="CON_NO", referencedColumnName = "CON_NO")	
	private MrsCon mrsCon;
		 
	@OneToMany(mappedBy="mrsConHist", fetch = FetchType.LAZY ,orphanRemoval = true )
	private Set<MrsVehIncd> mrsVehIncds = new HashSet<MrsVehIncd>(0);

	@OneToMany(mappedBy="mrsConHist", fetch = FetchType.LAZY)
	private Set<MrsConHistCar> mrsConHistCars = new HashSet<MrsConHistCar>(0);
	
	public MrsConHist() {
	}

	public MrsConHist(MrsConHistPK comp_id, int runTimeHrs, int runTimeMin,
			String conStat, int conSize, String originLoc, String outsrvLoc,
			String conNoSplit, int dashNoSplit, int runTimeSplitMin,
			int runTimeSplitHrs, String originLocSwap, String washInd,
			String madeInErrInd, Date datetimeLastUpd, String termLastUpd,
			String termMaintUpd, String conNoSwap, int dashNoSwap,
			String fromYrdTrkgInd, String revTrainPlan, String combConNoL,
			String combConNoR, int combDashNoL, int combDashNoR,
			String combWithInd, String combPseudoLInd, String combPseudoRInd,
			String dispatchToLoc, String dispatchToYrd, String layupActionCd,
			String noCarsInd, String msgLoc, String orientOfCars,
			String scannerId, String scannerDir )//,String conNo, Integer dashNo, java.util.Date dateInsrvRev, Date datetimeInsrv) 
	{
		this.comp_id = comp_id;
		this.runTimeHrs = runTimeHrs;
		this.runTimeMin = runTimeMin;
		this.conStat = conStat;
		this.conSize = conSize;
		this.originLoc = originLoc;
		this.outsrvLoc = outsrvLoc;
		this.conNoSplit = conNoSplit;
		this.dashNoSplit = dashNoSplit;
		this.runTimeSplitMin = runTimeSplitMin;
		this.runTimeSplitHrs = runTimeSplitHrs;
		this.originLocSwap = originLocSwap;
		this.washInd = washInd;
		this.madeInErrInd = madeInErrInd;
		this.datetimeLastUpd = datetimeLastUpd;
		this.termLastUpd = termLastUpd;
		this.termMaintUpd = termMaintUpd;
		this.conNoSwap = conNoSwap;
		this.dashNoSwap = dashNoSwap;
		this.fromYrdTrkgInd = fromYrdTrkgInd;
		this.revTrainPlan = revTrainPlan;
		this.combConNoL = combConNoL;
		this.combConNoR = combConNoR;
		this.combDashNoL = combDashNoL;
		this.combDashNoR = combDashNoR;
		this.combWithInd = combWithInd;
		this.combPseudoLInd = combPseudoLInd;
		this.combPseudoRInd = combPseudoRInd;
		this.dispatchToLoc = dispatchToLoc;
		this.dispatchToYrd = dispatchToYrd;
		this.layupActionCd = layupActionCd;
		this.noCarsInd = noCarsInd;
		this.msgLoc = msgLoc;
		this.orientOfCars = orientOfCars;
		this.scannerId = scannerId;
		this.scannerDir = scannerDir;
		/*
        this.conNo = conNo;
        this.dashNo = dashNo;
        this.dateInsrvRev = dateInsrvRev;
        this.datetimeInsrv = datetimeInsrv;*/
	}

	public MrsConHist(MrsConHistPK comp_id, Date datetimeOutsrv,
			int runTimeHrs, int runTimeMin, String conStat, int conSize,
			String originLoc, String outsrvLoc, String conNoSplit,
			int dashNoSplit, int runTimeSplitMin, int runTimeSplitHrs,
			String originLocSwap, String washInd, String madeInErrInd,
			Date datetimeLastUpd, String termLastUpd, Date datetimeMaintUpd,
			String termMaintUpd, String conNoSwap, int dashNoSwap,
			String fromYrdTrkgInd, String revTrainPlan, String combConNoL,
			String combConNoR, int combDashNoL, int combDashNoR,
			String combWithInd, String combPseudoLInd, String combPseudoRInd,
			Date datetimeMakeTrain, Date datetimeInsrvIcs,
			String dispatchToLoc, String dispatchToYrd, String layupActionCd,
			String noCarsInd, String msgLoc, String orientOfCars,
			String scannerId, String scannerDir, Date timeInsrvOrig,//String conNo, Integer dashNo, java.util.Date dateInsrvRev, Date datetimeInsrv,
			 MrsCon mrsCon, Set<MrsConHistCar> mrsConHistCars,
			Set<MrsVehIncd> mrsVehIncds) {
		this.comp_id = comp_id;
		this.datetimeOutsrv = datetimeOutsrv;
		this.runTimeHrs = runTimeHrs;
		this.runTimeMin = runTimeMin;
		this.conStat = conStat;
		this.conSize = conSize;
		this.originLoc = originLoc;
		this.outsrvLoc = outsrvLoc;
		this.conNoSplit = conNoSplit;
		this.dashNoSplit = dashNoSplit;
		this.runTimeSplitMin = runTimeSplitMin;
		this.runTimeSplitHrs = runTimeSplitHrs;
		this.originLocSwap = originLocSwap;
		this.washInd = washInd;
		this.madeInErrInd = madeInErrInd;
		this.datetimeLastUpd = datetimeLastUpd;
		this.termLastUpd = termLastUpd;
		this.datetimeMaintUpd = datetimeMaintUpd;
		this.termMaintUpd = termMaintUpd;
		this.conNoSwap = conNoSwap;
		this.dashNoSwap = dashNoSwap;
		this.fromYrdTrkgInd = fromYrdTrkgInd;
		this.revTrainPlan = revTrainPlan;
		this.combConNoL = combConNoL;
		this.combConNoR = combConNoR;
		this.combDashNoL = combDashNoL;
		this.combDashNoR = combDashNoR;
		this.combWithInd = combWithInd;
		this.combPseudoLInd = combPseudoLInd;
		this.combPseudoRInd = combPseudoRInd;
		this.datetimeMakeTrain = datetimeMakeTrain;
		this.datetimeInsrvIcs = datetimeInsrvIcs;
		this.dispatchToLoc = dispatchToLoc;
		this.dispatchToYrd = dispatchToYrd;
		this.layupActionCd = layupActionCd;
		this.noCarsInd = noCarsInd;
		this.msgLoc = msgLoc;
		this.orientOfCars = orientOfCars;
		this.scannerId = scannerId;
		this.scannerDir = scannerDir;
		this.timeInsrvOrig = timeInsrvOrig;   	
		this.mrsCon = mrsCon;				
		this.mrsConHistCars = mrsConHistCars;

		 this.mrsVehIncds = mrsVehIncds;
	}

	public MrsConHistPK getComp_id() {
		return this.comp_id;
	}

	public void setComp_id(MrsConHistPK comp_id) {
		this.comp_id = comp_id;
	}

	public Date getDatetimeOutsrv() {
		return this.datetimeOutsrv;
	}

	public void setDatetimeOutsrv(Date datetimeOutsrv) {
		this.datetimeOutsrv = datetimeOutsrv;
	}

	public int getRunTimeHrs() {
		return this.runTimeHrs;
	}

	public void setRunTimeHrs(int runTimeHrs) {
		this.runTimeHrs = runTimeHrs;
	}

	public int getRunTimeMin() {
		return this.runTimeMin;
	}

	public void setRunTimeMin(int runTimeMin) {
		this.runTimeMin = runTimeMin;
	}

	public String getConStat() {
		return this.conStat;
	}

	public void setConStat(String conStat) {
		this.conStat = conStat;
	}

	public int getConSize() {
		return this.conSize;
	}

	public void setConSize(int conSize) {
		this.conSize = conSize;
	}

	public String getOriginLoc() {
		return this.originLoc;
	}

	public void setOriginLoc(String originLoc) {
		this.originLoc = originLoc;
	}

	public String getOutsrvLoc() {
		return this.outsrvLoc;
	}

	public void setOutsrvLoc(String outsrvLoc) {
		this.outsrvLoc = outsrvLoc;
	}

	public String getConNoSplit() {
		return this.conNoSplit;
	}

	public void setConNoSplit(String conNoSplit) {
		this.conNoSplit = conNoSplit;
	}

	public int getDashNoSplit() {
		return this.dashNoSplit;
	}

	public void setDashNoSplit(int dashNoSplit) {
		this.dashNoSplit = dashNoSplit;
	}

	public int getRunTimeSplitMin() {
		return this.runTimeSplitMin;
	}

	public void setRunTimeSplitMin(int runTimeSplitMin) {
		this.runTimeSplitMin = runTimeSplitMin;
	}

	public int getRunTimeSplitHrs() {
		return this.runTimeSplitHrs;
	}

	public void setRunTimeSplitHrs(int runTimeSplitHrs) {
		this.runTimeSplitHrs = runTimeSplitHrs;
	}

	public String getOriginLocSwap() {
		return this.originLocSwap;
	}

	public void setOriginLocSwap(String originLocSwap) {
		this.originLocSwap = originLocSwap;
	}

	public String getWashInd() {
		return this.washInd;
	}

	public void setWashInd(String washInd) {
		this.washInd = washInd;
	}

	public String getMadeInErrInd() {
		return this.madeInErrInd;
	}

	public void setMadeInErrInd(String madeInErrInd) {
		this.madeInErrInd = madeInErrInd;
	}

	public Date getDatetimeLastUpd() {
		return this.datetimeLastUpd;
	}

	public void setDatetimeLastUpd(Date datetimeLastUpd) {
		this.datetimeLastUpd = datetimeLastUpd;
	}

	public String getTermLastUpd() {
		return this.termLastUpd;
	}

	public void setTermLastUpd(String termLastUpd) {
		this.termLastUpd = termLastUpd;
	}

	public Date getDatetimeMaintUpd() {
		return this.datetimeMaintUpd;
	}

	public void setDatetimeMaintUpd(Date datetimeMaintUpd) {
		this.datetimeMaintUpd = datetimeMaintUpd;
	}

	public String getTermMaintUpd() {
		return this.termMaintUpd;
	}

	public void setTermMaintUpd(String termMaintUpd) {
		this.termMaintUpd = termMaintUpd;
	}

	public String getConNoSwap() {
		return this.conNoSwap;
	}

	public void setConNoSwap(String conNoSwap) {
		this.conNoSwap = conNoSwap;
	}

	public int getDashNoSwap() {
		return this.dashNoSwap;
	}

	public void setDashNoSwap(int dashNoSwap) {
		this.dashNoSwap = dashNoSwap;
	}

	public String getFromYrdTrkgInd() {
		return this.fromYrdTrkgInd;
	}

	public void setFromYrdTrkgInd(String fromYrdTrkgInd) {
		this.fromYrdTrkgInd = fromYrdTrkgInd;
	}

	public String getRevTrainPlan() {
		return this.revTrainPlan;
	}

	public void setRevTrainPlan(String revTrainPlan) {
		this.revTrainPlan = revTrainPlan;
	}

	public String getCombConNoL() {
		return this.combConNoL;
	}

	public void setCombConNoL(String combConNoL) {
		this.combConNoL = combConNoL;
	}

	public String getCombConNoR() {
		return this.combConNoR;
	}

	public void setCombConNoR(String combConNoR) {
		this.combConNoR = combConNoR;
	}

	public int getCombDashNoL() {
		return this.combDashNoL;
	}

	public void setCombDashNoL(int combDashNoL) {
		this.combDashNoL = combDashNoL;
	}

	public int getCombDashNoR() {
		return this.combDashNoR;
	}

	public void setCombDashNoR(int combDashNoR) {
		this.combDashNoR = combDashNoR;
	}

	public String getCombWithInd() {
		return this.combWithInd;
	}

	public void setCombWithInd(String combWithInd) {
		this.combWithInd = combWithInd;
	}

	public String getCombPseudoLInd() {
		return this.combPseudoLInd;
	}

	public void setCombPseudoLInd(String combPseudoLInd) {
		this.combPseudoLInd = combPseudoLInd;
	}

	public String getCombPseudoRInd() {
		return this.combPseudoRInd;
	}

	public void setCombPseudoRInd(String combPseudoRInd) {
		this.combPseudoRInd = combPseudoRInd;
	}

	public Date getDatetimeMakeTrain() {
		return this.datetimeMakeTrain;
	}

	public void setDatetimeMakeTrain(Date datetimeMakeTrain) {
		this.datetimeMakeTrain = datetimeMakeTrain;
	}

	public Date getDatetimeInsrvIcs() {
		return this.datetimeInsrvIcs;
	}

	public void setDatetimeInsrvIcs(Date datetimeInsrvIcs) {
		this.datetimeInsrvIcs = datetimeInsrvIcs;
	}

	public String getDispatchToLoc() {
		return this.dispatchToLoc;
	}

	public void setDispatchToLoc(String dispatchToLoc) {
		this.dispatchToLoc = dispatchToLoc;
	}

	public String getDispatchToYrd() {
		return this.dispatchToYrd;
	}

	public void setDispatchToYrd(String dispatchToYrd) {
		if (dispatchToYrd == null) {
			this.dispatchToYrd = " ";
		} else {
			this.dispatchToYrd = dispatchToYrd;
		}
	}

	public String getLayupActionCd() {
		return this.layupActionCd;
	}

	public void setLayupActionCd(String layupActionCd) {
		this.layupActionCd = layupActionCd;
	}

	public String getNoCarsInd() {
		return this.noCarsInd;
	}

	public void setNoCarsInd(String noCarsInd) {
		this.noCarsInd = noCarsInd;
	}

	public String getMsgLoc() {
		return this.msgLoc;
	}

	public void setMsgLoc(String msgLoc) {
		this.msgLoc = msgLoc;
	}

	public String getOrientOfCars() {
		return this.orientOfCars;
	}

	public void setOrientOfCars(String orientOfCars) {
		this.orientOfCars = orientOfCars;
	}

	public String getScannerId() {
		return this.scannerId;
	}

	public void setScannerId(String scannerId) {
		this.scannerId = scannerId;
	}

	public String getScannerDir() {
		return this.scannerDir;
	}

	public void setScannerDir(String scannerDir) {
		this.scannerDir = scannerDir;
	}

	public Date getTimeInsrvOrig() {
		return this.timeInsrvOrig;
	}

	public void setTimeInsrvOrig(Date timeInsrvOrig) {
		this.timeInsrvOrig = timeInsrvOrig;
	}

	
	public MrsCon getMrsCon() {
		return this.mrsCon;
	}

	public void setMrsCon(MrsCon mrsCon) {
		this.mrsCon = mrsCon;
	}
	
	public Set<MrsConHistCar> getMrsConHistCars() {
		return this.mrsConHistCars;
	}

	public void setMrsConHistCars(Set<MrsConHistCar> mrsConHistCars) {
		this.mrsConHistCars = mrsConHistCars;
	}  

	public Set<MrsVehIncd> getMrsVehIncds() {
		return this.mrsVehIncds;
	}

	public void setMrsVehIncds(Set<MrsVehIncd> mrsVehIncds) {
		this.mrsVehIncds = mrsVehIncds;
	}

}

2. MrsConHistPK.java

package com.maris.entity;

import jakarta.persistence.*;
import java.util.Date;
import java.util.Objects;

@Embeddable
public class MrsConHistPK  implements java.io.Serializable {

	private static final long serialVersionUID = 1L;
	 @Column(name = "CAN_NO",insertable=false, updatable=false)
	 private String conNo;
	 @Column(name = "DASH_NO",insertable=false, updatable=false)
     private Integer dashNo;
	 @Column(name = "DATE_INSRV_REV",insertable=false, updatable=false)
     private Date dateInsrvRev;
	 @Column(name = "DATETIME_INSRV",insertable=false, updatable=false)
     private Date datetimeInsrv;

    public MrsConHistPK() {
    }

    public MrsConHistPK(String conNo, Integer dashNo, java.util.Date dateInsrvRev, Date datetimeInsrv) {
       this.conNo = conNo;
       this.dashNo = dashNo;
       this.dateInsrvRev = dateInsrvRev;
       this.datetimeInsrv = datetimeInsrv;
    }

    public String getConNo() {
        return this.conNo;
    }
    
    public void setConNo(String conNo) {
        this.conNo = conNo;
    }

    public Integer getDashNo() {
        return this.dashNo;
    }
    
    public String getDashNoStr() {
        return (this.dashNo).toString();
    }
    
    public void setDashNo(Integer dashNo) {
        this.dashNo = dashNo;
    }
  
    public Date getDateInsrvRev() {
        return this.dateInsrvRev;
    }
    
    public void setDateInsrvRev(java.util.Date dateInsrvRev) {
        this.dateInsrvRev = dateInsrvRev;
    }
 
    public Date getDatetimeInsrv() {
        return this.datetimeInsrv;
    }
    
    public void setDatetimeInsrv(Date datetimeInsrv) {
        this.datetimeInsrv = datetimeInsrv;
    }

	@Override
	public boolean equals(Object o) {
		if (this == o) {
			return true;
		}
		if (o == null || getClass() != o.getClass()) {
			return false;
		}
		MrsConHistPK pk = (MrsConHistPK) o;
		return Objects.equals(conNo, pk.conNo) &&
				Objects.equals(dashNo, pk.dashNo)&&
				Objects.equals(dateInsrvRev, pk.dateInsrvRev)&&
				Objects.equals(datetimeInsrv, pk.datetimeInsrv);
	}

	@Override
	public int hashCode() {
		return Objects.hash(conNo, dashNo, dateInsrvRev, datetimeInsrv);
	}
}

3. function retrieve the data:

public List<MrsConHist> retrieveList_MrsConHist(String sql) throws Exception {
	Session session = null;
	List<MrsConHist> MrsConHistList = null;		
	try{
		session = sessionFactory.openSession();
		SelectionQuery <MrsConHist>query =  session.createSelectionQuery(sql, MrsConHist.class);
		MrsConHistList = query.list();			
	}
	catch(Exception ex) {
		MarisUtility.logException(ex,  "in retrieveList_MrsConHist");
	        throw new Exception (" in retrieveList_MrsConHist");
	}
	finally {
	    if (session != null) {
		  session.close();
	    }
	 }
	return MrsConHistList;
}

I tried a query in 3 different ways, none worked:
SQL 1 "from MrsConHist where getComp_id().getConNo() = '102' "
Error: org.hibernate.query.SyntaxException: At 1:43 and token '(', mismatched input '(', expecting one of the following tokens: <EOF>, '.', '+', '-', '*', '/', '%', '||', AND, BY, DAY, EPOCH, GROUP, HOUR, MINUTE, MONTH, NANOSECOND, OR, ORDER, QUARTER, SECOND, SELECT, WEEK, YEAR [from MrsConHist where getComp_id().getConNo() = '102' ]

SQL 2) "from MrsConHist where CON_NO = '102' "
Error: org.hibernate.query.SemanticException: Could not interpret path expression 'CON_NO'

SQL 3) "from MrsConHist where conNo = '102' "
Error: org.hibernate.query.SemanticException: Could not interpret path expression 'conNo'

My system: Hibernate 6.6, Java 11, Oracle 19.0
Please help. Thanks

2

I solved this problem.
.```
session = sessionFactory.openSession();
EntityManager entityManager = session.getEntityManagerFactory().createEntityManager();
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
entityManager.getTransaction().begin();
CriteriaQuery cr = cb.createQuery(MrsConHist.class);
Root root = cr.from(MrsConHist.class);
cr.select(root);
Predicate predicate1 = null;
Predicate predicate2 = null;
predicate1 =cb.equal(root.get(“comp_id”). get(“dateInsrvRev”), date);
cr.where(
predicate1,
cb.equal((root.get(“comp_id”).get(“conNo”)), conNo),
cb.equal((root.get(“comp_id”).get(“dashNo”)), dashNo)
);
entityManager.createQuery(cr).getResultList();
entityManager.getTransaction().commit();

3

Looks like you were able to solve you problem using the Criteria API. Just for reference, Hibernate’s HQL syntax requires you to use property names, that is the names of the attributes in your entity mappings, not getters or SQL column names, read more about it here.