Hello,
I had post a similar question. Later I did some changes, still not work, but the error is different, I repost. Please help. In case you have instruction or good example, please help.
I have difficulty with DBLINK in hibernate. I got the Exception when retrieve my data with the SQL:
"from MAXIMO.CARSTATUS@BEGL21 where SEQID > '592276' order by SEQID"
The error:
java.lang.IllegalArgumentException: org.hibernate.query.SyntaxException: At 1:21, token recognition error at: '@' [from MAXIMO.CARSTATUS@BEGL21 where SEQID > '592276' order by SEQID]
My db connection setting in my hibernate.cfg.xml:
<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver</property>
<property name="connection.url">jdbc:oracle:thin:@//dcdbdeng.adm.eng:1521/bengsd</property>
<property name="connection.username">mbsf1</property>
<property name="connection.password">mbsf1</property>
<property name="hibernate.dialect">org.hibernate.dialect.OracleDialect</property>
<property name="hibernate.connection.autocommit">false</property>
<property name="show_sql">true</property>
I need to fetch data from another Oracle database (not bengsd) via public dblink.
The entity of MAXIMO.CARSTATUS@BEGL21:
package com.eng.maris.entity;
import java.util.Date;
import jakarta.persistence.Column;
import jakarta.persistence.Entity;
import jakarta.persistence.Id;
import jakarta.persistence.Table;
@Table(name = "MAXIMO.CARSTATUS@BEGL21") //or @Table(name = "CARSTATUS@BEGL21", schema="MAXIMO")
@Entity
public class MrsCarStatus implements java.io.Serializable {
private static final long serialVersionUID = 1L;
@Id
@Column(name = "CARSTATUSID")
private String carStatusId;
@Column(name = "DESCRIPTION")
private String description;
@Column(name = "ORGID")
private String orgId;
@Column(name = "HASLD")
private String hasld;
@Column(name = "CONSIST")
private String consist;
@Column(name = "CAR")
private String car;
@Column(name = "INCIDENT")
private String incident;
@Column(name = "REPORTDATE")
private Date reportDate;
@Column(name = "INCIDENTSTATUS")
private String incidentStatus;
@Column(name = "INCIDENTTYPE")
private String incidentType;
@Column(name = "SYMPTOM")
private String symptom;
@Column(name = "INCIDENTLOCATION")
private String incidentLocation;
@Column(name = "PRIORITY")
private String priority;
@Column(name = "SPEED")
private String speed;
@Column(name = "DIRECTION")
private String direction;
@Column(name = "CLOSEDATE")
private Date closeDate;
@Column(name = "NARRATIVENUM")
private String narrativeNum;
@Column(name = "NARRATIVE")
private String narrative;
@Column(name = "NARRATIVEDATE")
private Date narrativeDate;
@Column(name = "ROWSTAMP")
private String rowStamp;
@Column(name = "CARSTATUS")
private String carStatus;
@Column(name = "CHANGEBY")
private String changeBy;
@Column(name = "CHANGEDATE")
private Date changeDate;
@Column(name = "SEQID")
private Integer seqNo;
public Integer getSeqNo() {
return seqNo;
}
public void setSeqNo(Integer seqNo) {
this.seqNo = seqNo;
}
public String getCarStatusId() {
return carStatusId;
}
public void setCarStatusId(String carStatusId) {
this.carStatusId = carStatusId;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public String getOrgId() {
return orgId;
}
public void setOrgId(String orgId) {
this.orgId = orgId;
}
public String getHasld() {
return hasld;
}
public void setHasld(String hasld) {
this.hasld = hasld;
}
public String getConsist() {
return consist;
}
public void setConsist(String consist) {
this.consist = consist;
}
public String getCar() {
return car;
}
public void setCar(String car) {
this.car = car;
}
public String getIncident() {
return incident;
}
public void setIncident(String incident) {
this.incident = incident;
}
public Date getReportDate() {
return reportDate;
}
public void setReportDate(Date reportDate) {
this.reportDate = reportDate;
}
public String getIncidentStatus() {
return incidentStatus;
}
public void setIncidentStatus(String incidentStatus) {
this.incidentStatus = incidentStatus;
}
public String getIncidentType() {
return incidentType;
}
public void setIncidentType(String incidentType) {
this.incidentType = incidentType;
}
public String getSymptom() {
return symptom;
}
public void setSymptom(String symptom) {
this.symptom = symptom;
}
public String getIncidentLocation() {
return incidentLocation;
}
public void setIncidentLocation(String incidentLocation) {
this.incidentLocation = incidentLocation;
}
public String getPriority() {
return priority;
}
public void setPriority(String priority) {
this.priority = priority;
}
public String getSpeed() {
return speed;
}
public void setSpeed(String speed) {
this.speed = speed;
}
public String getDirection() {
return direction;
}
public void setDirection(String direction) {
this.direction = direction;
}
public Date getCloseDate() {
return closeDate;
}
public void setCloseDate(Date closeDate) {
this.closeDate = closeDate;
}
public String getNarrativeNum() {
return narrativeNum;
}
public void setNarrativeNum(String narrativeNum) {
this.narrativeNum = narrativeNum;
}
public String getNarrative() {
return narrative;
}
public void setNarrative(String narrative) {
this.narrative = narrative;
}
public Date getNarrativeDate() {
return narrativeDate;
}
public void setNarrativeDate(Date narrativeDate) {
this.narrativeDate = narrativeDate;
}
public String getRowStamp() {
return rowStamp;
}
public void setRowStamp(String rowStamp) {
this.rowStamp = rowStamp;
}
public String getCarStatus() {
return carStatus;
}
public void setCarStatus(String carStatus) {
this.carStatus = carStatus;
}
public String getChangeBy() {
return changeBy;
}
public void setChangeBy(String changeBy) {
this.changeBy = changeBy;
}
public Date getChangeDate() {
return changeDate;
}
public void setChangeDate(Date changeDate) {
this.changeDate = changeDate;
}
}
Exception threw when retreive the data:
// from MAXIMO.CARSTATUS@BEGL21 where SEQID > '592276' order by SEQID
// Error: java.lang.IllegalArgumentException: org.hibernate.query.SyntaxException: At 1:21, token recognition error at: '@' [from MAXIMO.CARSTATUS@BEGL21 where SEQID > '592276' order by SEQID]
public List<MrsCarStatus> retrieveList_MrsCarStatus(String sql) {
Session session = null;
List<MrsCarStatus> retList = null;
try{
session = sessionFactory.openSession();
session = sessionFactory.openSession();
List mrsVehIncd = session.getEntityManagerFactory().createEntityManager().createQuery(
sql, MrsCarStatus.class)
.getResultList();
}
catch(Exception e) {
System.out.println("List Size: " + retList.size() );
e.printStackTrace();
}
return retList;
}
The table in another database:
CREATE TABLE "MAXIMO"."CARSTATUS"
( "CAR" VARCHAR2(50 BYTE),
"CARSTATUS" VARCHAR2(50 BYTE),
"CARSTATUSID" NUMBER NOT NULL ENABLE,
"CHANGEBY" VARCHAR2(30 BYTE),
"CHANGEDATE" DATE,
"CLOSEDATE" DATE,
"CONSIST" VARCHAR2(50 BYTE),
"DESCRIPTION" VARCHAR2(50 BYTE),
"DIRECTION" VARCHAR2(50 BYTE),
"HASLD" NUMBER NOT NULL ENABLE,
"INCIDENT" VARCHAR2(10 BYTE),
"INCIDENTLOCATION" VARCHAR2(50 BYTE),
"INCIDENTSTATUS" VARCHAR2(50 BYTE),
"INCIDENTTYPE" VARCHAR2(50 BYTE),
"NARRATIVE" VARCHAR2(50 BYTE),
"NARRATIVEDATE" DATE,
"NARRATIVENUM" VARCHAR2(50 BYTE),
"ORGID" VARCHAR2(8 BYTE),
"PRIORITY" VARCHAR2(50 BYTE),
"REPORTDATE" DATE,
"SPEED" VARCHAR2(50 BYTE),
"SYMPTOM" VARCHAR2(50 BYTE),
"ROWSTAMP" VARCHAR2(40 BYTE) NOT NULL ENABLE,
"SEQID" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MAXDATA" ;
CREATE UNIQUE INDEX "MAXIMO"."CARSTATUS_NDX1" ON "MAXIMO"."CARSTATUS" ("CARSTATUSID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "MAXINDEX" ;
CREATE OR REPLACE EDITIONABLE TRIGGER "MAXIMO"."CARSTATUS_T" BEFORE INSERT OR UPDATE ON CARSTATUS FOR EACH ROW DECLARE NEXTVAL INTEGER; BEGIN SELECT MAXSEQ.NEXTVAL INTO NEXTVAL FROM DUAL; :NEW.ROWSTAMP := NEXTVAL; END;
/
ALTER TRIGGER "MAXIMO"."CARSTATUS_T" ENABLE;
CREATE OR REPLACE EDITIONABLE TRIGGER "MAXIMO"."CARSTATUS_SEQID_T"
before insert on carstatus
for each row
begin
select CARSTATUSSEQIDSEQ.nextval into :new.seqid from dual;
end;
/
ALTER TRIGGER "MAXIMO"."CARSTATUS_SEQID_T" ENABLE;
I am on Hibernate 6.5.2, java 11, Oracle 19c. Should/How to I set something in hrbernate.cfg.xml for MAXIMO db?
Thanks,
Eric