I have post another seems similar topic in the site, and fix by lucky, Now I get another one, Again probably entity map issue. but tried all I could, cost me a lot of time in vain. Please help. I have two entities.
- MrsCOnHistCars
@Table(name = "MRS_CON_HIST_CARS")
@Entity
@org.hibernate.annotations.DynamicUpdate
public class MrsConHistCars implements java.io.Serializable {
private static final long serialVersionUID = 1L;
@EmbeddedId
private MrsConHistCarPK comp_id;
@Column(name = "CAR_ORIENTATION")
private String carOrientation;
@Column(name = "CAR_ORDER")
private int carOrder;
@Column(name = "CUM_HRS")
private long cumHrs;
@Column(name = "CUM_MIN")
private int cumMin;
@Column(name = "END_CAR_HRS")
private long endCarHrs;
@Column(name = "END_CAR_MIN")
private int endCarMin;
@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;
@ManyToOne(fetch = FetchType.LAZY) //MODEL A.2
@JoinColumns({
@JoinColumn(name = "CON_NO", insertable = false, updatable = false),
@JoinColumn(name = "DASH_NO", insertable = false, updatable = false),
@JoinColumn(name = "DATE_INSRV_REV", insertable = false, updatable = false),
@JoinColumn(name = "DATETIME_INSRV", insertable = false, updatable = false)
})
private MrsConHist mrsConHist;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "CON_NO")
private MrsCon mrsCon;
public MrsConHistCars() {
}
public MrsConHistCarPK getComp_id() {
return this.comp_id;
}
public void setComp_id(MrsConHistCarPK comp_id) {
this.comp_id = comp_id;
}
//get/set ....
}
the comp key:
@Embeddable
public class MrsConHistCarPK implements java.io.Serializable {
private static final long serialVersionUID = 1L;
@Column(name = "CON_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;
@Column(name = "CAR_NO")
private String carNo;
public MrsConHistCarPK() {
}
//get/set and @Override
}
MrsCon entity:
@Table(name = "MRS_CON")
@Entity
@org.hibernate.annotations.DynamicUpdate
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( fetch = FetchType.LAZY, cascade = {})
@JoinColumn(name="CON_NO")
private Set<MrsVeh> mrsVehs = new HashSet<MrsVeh>(0);
@OneToMany( fetch = FetchType.LAZY)
@JoinColumn(name="CON_NO")
private Set<MrsConHist> mrsConHists;
@OneToMany(mappedBy ="mrsCon", fetch = FetchType.LAZY)
private Set<MrsConHistCars> mrsConHistsCars ;
public MrsCon() {
}
public Set<MrsVeh> getMrsVehs() {
return this.mrsVehs;
}
public void setMrsVehs(Set<MrsVeh> mrsVehs) {
this.mrsVehs = mrsVehs;
}
public Set<MrsConHist> getMrsConHists() {
return this.mrsConHists;
}
public void setMrsConHists(Set<MrsConHist> mrsConHists) {
this.mrsConHists = mrsConHists;
}
public Set<MrsConHistCars> getMrsConHistCars() {
return this.mrsConHistsCars;
}
public void setMrsConHistCarss(Set<MrsConHistCars> mrsConHistCars) {
this.mrsConHistsCars = mrsConHistCars;
}
//other set/get
}
The query:
String sql001 = "SELECT {T1.*}, {T2.*} FROM MRS_CON T1, MRS_CON_HIST_CARS T2"
+ " WHERE T1.CON_NO ='" + conNo
+ "' AND T1.CON_NO = T2.CON_NO AND T1.DASH_NO = T2.DASH_NO AND
T1.DATE_INSRV_REV = T2.DATE_INSRV_REV AND "
+ " T1.DATETIME_INSRV = T2.DATETIME_INSRV AND T1.CON_STAT IN('SRV','CAR','CON') ORDER BY T2.CAR_ORDER
Call this function:
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;
}
I got error. please find the mis-aligned fields in the hibernate dynamic native SQL query:
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.CAR_NO car_no1_7_1_,
T2.CON_NO con_no2_7_1_,
T2.DASH_NO dash_no3_7_1_,
T2.DATE_INSRV_REV date_insrv_rev4_7_1_,
T2.DATETIME_INSRV datetime_insrv5_7_1_,
T2.CAR_ORDER car_order6_7_1_,
T2.CAR_ORIENTATION car_orientation7_7_1_,
T2.CUM_HRS cum_hrs8_7_1_,
T2.CUM_MIN cum_min9_7_1_,
T2.DATETIME_LAST_UPD datetime_last_upd10_7_1_,
T2.DATETIME_MAINT_UPD datetime_maint_upd11_7_1_,
T2.END_CAR_HRS end_car_hrs12_7_1_,
T2.END_CAR_MIN end_car_min13_7_1_,
**T2.TERM_LAST_UPD con_no2_7_1_,**
** T2.TERM_MAINT_UPD dash_no3_7_1_**
FROM
MRS_CON T1,
MRS_CON_HIST_CARS T2
WHERE
T1.CON_NO ='387'
AND T1.CON_NO = T2.CON_NO
AND T1.DASH_NO = T2.DASH_NO
AND T1.DATE_INSRV_REV = T2.DATE_INSRV_REV
AND T1.DATETIME_INSRV = T2.DATETIME_INSRV
AND T1.CON_STAT IN('SRV', 'CAR', 'CON')
ORDER BY
T2.CAR_ORDER
Please find T2.TERM_LAST_UPD used the same align of T2.CON_NO ,
T2.TERM_MAINT_UPD used the same align of T2.DASH_NO.
the error message:
16:28:39.584 [main] WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 17006, SQLState: 99999
16:28:39.586 [main] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Invalid column name
16:28:39.700 [main] DEBUG org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl - JDBC transaction marked for rollback-only (exception provided for stack trace)
java.lang.Exception: exception just for purpose of providing stack trace
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.markRollbackOnly(JdbcResourceLocalTransactionCoordinatorImpl.java:309) [hibernate-core-6.6.0.Final.jar:6.6.0.Final]
at org.hibernate.engine.transaction.internal.TransactionImpl.markRollbackOnly(TransactionImpl.java:203) [hibernate-core-6.6.0.Final.jar:6.6.0.Final]
at org.hibernate.internal.AbstractSharedSessionContract.markForRollbackOnly(AbstractSharedSessionContract.java:542) [hibernate-core-6.6.0.Final.jar:6.6.0.Final]
at org.hibernate.internal.ExceptionConverterImpl.rollbackIfNecessary(ExceptionConverterImpl.java:264) [hibernate-core-6.6.0.Final.jar:6.6.0.Final]
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:160) [hibernate-core-6.6.0.Final.jar:6.6.0.Final]
at org.hibernate.query.spi.AbstractSelectionQuery.list(AbstractSelectionQuery.java:144) [hibernate-core-6.6.0.Final.jar:6.6.0.Final]
at org.hibernate.query.Query.getResultList(Query.java:120) [hibernate-core-6.6.0.Final.jar:6.6.0.Final]
at gov.bart.maris.dao.AbstractDAO.get2EntitiesList(AbstractDAO.java:1888) [classes/:?]
at gov.bart.maris.dao.MarisConsistDao.retrieveCarStatusAndRunTimeCal(MarisConsistDao.java:78) [classes/:?]
at gov.bart.maris.control.ClearTrainService.fetchCars(ClearTrainService.java:620) [classes/:?]
at gov.bart.maris.control.ClearTrainService.checkYrds(ClearTrainService.java:404) [classes/:?]
at gov.bart.maris.control.ClearTrainService.checkNoCarInd(ClearTrainService.java:284) [classes/:?]
at gov.bart.maris.control.ClearTrainService.checkStat(ClearTrainService.java:238) [classes/:?]
at gov.bart.maris.control.ClearTrainService.validConStat(ClearTrainService.java:210) [classes/:?]
at gov.bart.maris.control.ClearTrainService.processClearTrain(ClearTrainService.java:177) [classes/:?]
at gov.bart.maris.control.ClearTrainBusinessService.processClearTrain(ClearTrainBusinessService.java:22) [classes/:?]
at gov.bart.maris.Model.DataServices.MessageProcessingService.process1FDispatch(MessageProcessingService.java:756) [classes/:?]
at gov.bart.maris.Model.DataServices.MessageProcessingService.process1Fmessage(MessageProcessingService.java:719) [classes/:?]
at gov.bart.maris.Model.DataServices.MessageProcessingService.evaluateTransCode(MessageProcessingService.java:587) [classes/:?]
at gov.bart.maris.Model.DataServices.MessageProcessingService.evaluateMessageRequest(MessageProcessingService.java:415) [classes/:?]
at gov.bart.maris.Model.DataServices.MessageProcessingService.processMessages(MessageProcessingService.java:176) [classes/:?]
at gov.bart.maris.control.ControlProgramProcess.execute(ControlProgramProcess.java:488) [classes/:?]
at gov.bart.maris.control.ControlProgramProcess.main(ControlProgramProcess.java:74) [classes/:?]
16:28:39.823 [main] DEBUG org.hibernate.resource.transaction.internal.SynchronizationRegistryStandardImpl - Clearing local Synchronizations
16:28:39.842 [main] INFO org.hibernate.engine.internal.StatisticalLoggingSessionEventListener - Session Metrics {
124800 nanoseconds spent acquiring 1 JDBC connections;
156300 nanoseconds spent releasing 1 JDBC connections;
6113900 nanoseconds spent preparing 1 JDBC statements;
47842800 nanoseconds spent executing 1 JDBC statements;
0 nanoseconds spent executing 0 JDBC batches;
0 nanoseconds spent performing 0 L2C puts;
0 nanoseconds spent performing 0 L2C hits;
0 nanoseconds spent performing 0 L2C misses;
0 nanoseconds spent executing 0 flushes (flushing a total of 0 entities and 0 collections);
0 nanoseconds spent executing 0 pre-partial-flushes;
31300 nanoseconds spent executing 1 partial-flushes (flushing a total of 0 entities and 0 collections)
}
Any help please. I tried to debug, but cannot find the place to set breakpoint.
Thanks,
Eric