Does Hibernate 6 support dblink?

Hibernate ORM
1

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

2

You should not use the database-side table and column names in HQL query, rather the entity name and its attribute names:

from MrsCarStatus where seqNo > 592276 order by seqNo

I strongly suggest carefully reading our user guide to understand Hibernate functionality.

3

Hi mbladel,

Did the change, there is progress, the SQL become: from MrsCarStatus where seqNo > 592276 order by seqNo
still not work. the error is:

org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [select mcs1_0.CARSTATUSID,mcs1_0.CAR,mcs1_0.CARSTATUS,mcs1_0.CHANGEBY,mcs1_0.CHANGEDATE,mcs1_0.CLOSEDATE,mcs1_0.CONSIST,mcs1_0.DESCRIPTION,mcs1_0.DIRECTION,mcs1_0.HASLD,mcs1_0.INCIDENT,mcs1_0.INCIDENTLOCATION,mcs1_0.INCIDENTSTATUS,mcs1_0.INCIDENTTYPE,mcs1_0.NARRATIVE,mcs1_0.NARRATIVEDATE,mcs1_0.NARRATIVENUM,mcs1_0.ORGID,mcs1_0.PRIORITY,mcs1_0.REPORTDATE,mcs1_0.ROWSTAMP,mcs1_0.SEQID,mcs1_0.SPEED,mcs1_0.SYMPTOM from “MAXIMO.CARSTATUS@BEGL21” mcs1_0 where mcs1_0.SEQID>592276 order by mcs1_0.SEQID] [ORA-00942: table or view does not exist
] [n/a]
The problem in the SQL above is the double quotation on table:
“MAXIMO.CARSTATUS@BEGL21” When remove the double quotation, the sql runs well.
How to prevent the process from adding double quotation on table?
please help.
Thanks,
Eric

4

I’m not sure why the table identifier is being quoted, are you enabling global quoting? In any case, in Oracle double quotes around an identifier should only make it case-sensitive, so maybe check the case of the table schema / name or dblink?

5

I had not set it, actually, after I have this issue, I tried to manually set it to false.
Thanks,

Eric

6

I changed to synonyms, not use database link. This issue is avoided. But I preferred to database link. The old app I am upgrading is on hibernate 3.2 with database link, but mapping with xml instead of entity class in hibernate.cfg.xml.
Thanks,
Eric