Dear experts:
I have a query sqlConHist2: SELECT {T1.}, {T2.} FROM MRS_CON T1, MRS_CON_HIST T2 WHERE T1.CON_NO =‘381’ AND T1.CON_NO = T2.CON_NO AND T1.DASH_NO = T2.DASH_NO AND T2.CON_STAT IN (‘SRV’,‘CON’,‘CAR’);
get error: Unable to find column position by name: wash_ind44_6_1_. but seems it is not related to T2.wash_ind. when call the function myDao().get2EntitiesList(MrsCon.class, MrsConHist.class, sqlConHist2) :
public <T1, T2> List<Object[]> get2EntitiesList(Class<T1> eClass1, Class<T2> eClass2, String sqlQuery) throws Exception {
List<Object[]> resultList = null;
Session session = null;
Transaction transaction = null; // Declare transaction
try {
session = sessionFactory.getCurrentSession();
transaction = session.beginTransaction();
NativeQuery<Object[]> query = session.createNativeQuery(sqlQuery, Object[].class);
query.addEntity("T1", eClass1);
query.addEntity("T2", eClass2);
resultList = query.getResultList();
transaction.commit();
} catch (Exception e) {
if (transaction != null) {
transaction.rollback();
}
MarisUtility.logDBException(e);
throw new Exception("ThrowedDBException");
} finally {
if (session != null && session.isOpen()) {
session.close();
}
}
return resultList;
}
The entity for table MRS_CON_HIST and the PK is:
@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", length=1)
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 = "SCANNER_DIR")
private String scannerDir;
@Column(name = "TIME_INSRV_ORIG")
private Date timeInsrvOrig;
@ManyToOne
@JoinColumn(name="CON_NO", insertable = false, updatable = false)
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 MrsConHistPK getComp_id() {
return this.comp_id;
}
public void setComp_id(MrsConHistPK comp_id) {
this.comp_id = comp_id;
}
public String getMsgLoc() {
return this.msgLoc;
}
public void setMsgLoc(String msgLoc) {
this.msgLoc = msgLoc;
}
... other set/get ....
}
The PK:
@Embeddable
public class MrsConHistPK implements java.io.Serializable {
private static final long serialVersionUID = 1L;
@Column(name = "CON_NO") // ZUO TO DO, insertable=false, updatable=false
private String conNo;
@Column(name = "DASH_NO")
private Integer dashNo;
@Column(name = "DATE_INSRV_REV")
private Date dateInsrvRev;
@Column(name = "DATETIME_INSRV")
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 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
...
}
entity for MRS_CON:
@Table(name = "MRS_CON")
@Entity
public class MrsCon implements java.io.Serializable {
private static final long serialVersionUID = 1L;
@Id
@Column(name = "CON_NO")
private String conNo;
@Column(name = "DASH_NO")
private int dashNo;
@Column(name = "DATE_INSRV_REV")
private Date dateInsrvRev;
@Column(name = "DATETIME_INSRV")
private Date datetimeInsrv;
@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 = "DATETIME_LAST_UPD")
private Date datetimeLastUpd;
@Column(name = "CON_SIZE")
private int conSize;
@Column(name = "ORIGIN_LOC")
private String originLoc;
@Column(name = "OUTSRV_LOC")
private String outsrvLoc;
@Column(name = "TERM_LAST_UPD")
private String termLastUpd;
@Column(name = "CON_NO_SPLIT")
private String conNoSplit;
@Column(name = "DATETIME_MAINT_UPD")
private Date datetimeMaintUpd;
@Column(name = "DASH_NO_SPLIT")
private int dashNoSplit;
@Column(name = "RUN_TIME_SPLIT_HRS")
private int runTimeSplitHrs;
@Column(name = "RUN_TIME_SPLIT_MIN")
private int runTimeSplitMin;
@Column(name = "TERM_MAINT_UPD")
private String termMaintUpd;
@Column(name = "ORIGIN_LOC_SWAP")
private String originLocSwap;
@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 = "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 = "SCANNER_DIR")
private String scannerDir;
@OneToMany(mappedBy="mrsCon", fetch = FetchType.LAZY)
private Set<MrsVeh> mrsVehs = new HashSet<MrsVeh>(0);
@OneToMany(mappedBy="mrsCon", fetch = FetchType.LAZY)
private Set<MrsConHist> mrsConHists = new HashSet<MrsConHist>(0);
public String getConNo() {
return this.conNo;
}
public void setConNo(String conNo) {
this.conNo = conNo;
}
public int getDashNo() {
return this.dashNo;
}
public void setDashNo(int dashNo) {
this.dashNo = dashNo;
}
// other get/set...
}
Hibernate dynamic native SQL query, internally auto generated
SELECT
T1.CON_NO con_no1_5_0_,
T1.CON_NO_SPLIT con_no_split2_5_0_,
T1.CON_SIZE con_size3_5_0_,
T1.CON_STAT con_stat4_5_0_,
T1.DASH_NO dash_no5_5_0_,
T1.DASH_NO_SPLIT dash_no_split6_5_0_,
T1.DATE_INSRV_REV date_insrv_rev7_5_0_,
T1.DATETIME_INSRV datetime_insrv8_5_0_,
T1.DATETIME_INSRV_ICS datetime_insrv_ics9_5_0_,
T1.DATETIME_LAST_UPD datetime_last_upd10_5_0_,
T1.DATETIME_MAINT_UPD datetime_maint_upd11_5_0_,
T1.DATETIME_OUTSRV datetime_outsrv12_5_0_,
T1.DISPATCH_TO_LOC dispatch_to_loc13_5_0_,
T1.DISPATCH_TO_YRD dispatch_to_yrd14_5_0_,
T1.MSG_LOC msg_loc15_5_0_,
T1.NO_CARS_IND no_cars_ind16_5_0_,
T1.ORIENT_OF_CARS orient_of_cars17_5_0_,
T1.ORIGIN_LOC origin_loc18_5_0_,
T1.ORIGIN_LOC_SWAP origin_loc_swap19_5_0_,
T1.OUTSRV_LOC outsrv_loc20_5_0_,
T1.RUN_TIME_HRS run_time_hrs21_5_0_,
T1.RUN_TIME_MIN run_time_min22_5_0_,
T1.RUN_TIME_SPLIT_HRS run_time_split_hrs23_5_0_,
T1.RUN_TIME_SPLIT_MIN run_time_split_min24_5_0_,
T1.SCANNER_DIR scanner_dir25_5_0_,
T1.SCANNER_ID scanner_id26_5_0_,
T1.TERM_LAST_UPD term_last_upd27_5_0_,
T1.TERM_MAINT_UPD term_maint_upd28_5_0_,
T2.CON_NO con_no1_6_1_,
T2.DASH_NO dash_no2_6_1_,
T2.DATE_INSRV_REV date_insrv_rev3_6_1_,
T2.DATETIME_INSRV datetime_insrv4_6_1_,
T2.COMB_CON_NO_L comb_con_no_l5_6_1_,
T2.COMB_CON_NO_R comb_con_no_r6_6_1_,
T2.COMB_DASH_NO_L comb_dash_no_l7_6_1_,
T2.COMB_DASH_NO_R comb_dash_no_r8_6_1_,
T2.COMB_PSEUDO_L_IND comb_pseudo_l_ind9_6_1_,
T2.COMB_PSEUDO_R_IND comb_pseudo_r_ind10_6_1_,
T2.COMB_WITH_IND comb_with_ind11_6_1_,
T2.CON_NO_SPLIT con_no_split12_6_1_,
T2.CON_NO_SWAP con_no_swap13_6_1_,
T2.CON_SIZE con_size14_6_1_,
T2.CON_STAT con_stat15_6_1_,
T2.DASH_NO_SPLIT dash_no_split16_6_1_,
T2.DASH_NO_SWAP dash_no_swap17_6_1_,
T2.DATETIME_INSRV_ICS datetime_insrv_ics18_6_1_,
T2.DATETIME_LAST_UPD datetime_last_upd19_6_1_,
T2.DATETIME_MAINT_UPD datetime_maint_upd20_6_1_,
T2.DATETIME_MAKE_TRAIN datetime_make_train21_6_1_,
T2.DATETIME_OUTSRV datetime_outsrv22_6_1_,
T2.DISPATCH_TO_LOC dispatch_to_loc23_6_1_,
T2.DISPATCH_TO_YRD dispatch_to_yrd24_6_1_,
T2.FROM_YRD_TRKG_IND from_yrd_trkg_ind25_6_1_,
T2.LAYUP_ACTION_CD layup_action_cd26_6_1_,
T2.MADE_IN_ERR_IND made_in_err_ind27_6_1_,
T2.MSG_LOC con_no1_6_1_,
T2.NO_CARS_IND msg_loc28_6_1_,
T2.ORIENT_OF_CARS no_cars_ind29_6_1_,
T2.ORIGIN_LOC orient_of_cars30_6_1_,
T2.ORIGIN_LOC_SWAP origin_loc31_6_1_,
T2.OUTSRV_LOC origin_loc_swap32_6_1_,
T2.REV_TRAIN_PLAN outsrv_loc33_6_1_,
T2.RUN_TIME_HRS rev_train_plan34_6_1_,
T2.RUN_TIME_MIN run_time_hrs35_6_1_,
T2.RUN_TIME_SPLIT_HRS run_time_min36_6_1_,
T2.RUN_TIME_SPLIT_MIN run_time_split_hrs37_6_1_,
T2.SCANNER_DIR run_time_split_min38_6_1_,
T2.SCANNER_ID scanner_dir39_6_1_,
T2.TERM_LAST_UPD scanner_id40_6_1_,
T2.TERM_MAINT_UPD term_last_upd41_6_1_,
T2.TIME_INSRV_ORIG term_maint_upd42_6_1_,
T2.WASH_IND time_insrv_orig43_6_1_
FROM
MRS_CON T1,
MRS_CON_HIST T2
WHERE
T1.CON_NO ='381'
AND T1.CON_NO = T2.CON_NO
AND T1.DASH_NO = T2.DASH_NO
AND T2.CON_STAT IN ('SRV', 'CON', 'CAR')
Please find the the code T2.MSG_LOC con_no1_6_1_, T2.MSG_LOC takes the same align as T2.CON_NO. and after this line, all the aligns are wrong. The query failed, the error message is :
Unable to find column position by name: wash_ind44_6_1_.
I checked the entity, the fields order and name mappings in the two entity are matched the order in oracle table.
environment: oracle 19.6, hibernate-core 6.6.0, jakarta.persistence 3.1, on Eclipse 2022-06
If you have time, please forward your instruction.
Thanks