I'm trying to execute a SQL query with Castor OQLQuery to retrieve datas from two differents table into on DTO object to send a JsonResponse later on.
I was able to write to correct query in SQL but the project I'm working on use Castor v0.9.5.3 and I have to use the OQLQuery.
public static List<AvisOperationsDTO> findAvisOperationsFromCurLitLiaison(List<String> curLit, List<String> curLiaison) throws QueryException, DatabaseNotFoundException {
List<AvisOperationsDTO> avisoperations = new ArrayList<>();
StringBuilder builder = new StringBuilder("CALL SQL SELECT * FROM ( ");
// SubQuery: Avis a partir du CUR LIT ou CUR LIAISON
builder.append(" ( SELECT ll.LIAISONCUR AS \"curLiaison\", lit.LITCUR AS \"curLit\", lit.LITCODE AS \"ouvrageIdr\", lit.LITDESIGNATION AS \"ouvrageAdr\", ")
.append("'avis' AS \"typeObjet\", avis.DATE_CREATION AS \"dateCreation\", avis.DESCRIPTION AS \"description\", avis.AVIS_ID AS \"identifiant\", ")
.append("avis.STATUT AS \"statut\", compNature.LSTVALEUR AS \"equipement\", NULL AS \"debutPlanifie\" ")
.append("FROM PLS_NAVIS avis ")
.append("JOIN PLS_LIAISONSOUTERRAINE ls ON ls.OUVR_ID = avis.OUVR_ID ")
.append("JOIN PLS_LIT lit ON lit.LITCODE = ls.LITCODE ")
.append("JOIN PLS_LIAISON_LIT ll ON ll.LITCUR = lit.LITCUR ")
.append("JOIN PLS_LISTE compNature ON avis.LST_ID_COMP_NATURE = compNature.LST_ID ")
.append("WHERE avis.STATUT IN ('Créé', 'En surveillance') ")
.append("AND avis.AVIS_ID NOT IN (SELECT op.AVIS_ID FROM PLS_OPERATION op WHERE op.AVIS_ID = avis.AVIS_ID) ")
.append("AND ( lit.LITCUR IN ($1) OR ll.LIAISONCUR IN ($2) ) ");
builder.append(" ) UNION ( ");
//SubQuery: Avis à partir du CUR LIAISON
builder.append("SELECT ll.LIAISONCUR AS \"curLiaison\", lit.LITCUR AS \"curLit\", lit.LITCODE AS \"ouvrageIdr\", lit.LITDESIGNATION AS \"ouvrageAdr\", ")
.append("'opération' AS \"typeObjet\", op.DATE_CREATION AS \"dateCreation\", ")
.append("CONCAT(CONCAT(nature.LSTVALEUR, ' - '), op.COMMENTAIRE) AS \"description\", op.OP_ID AS \"identifiant\", op.STATUT AS \"statut\", ")
.append("CASE WHEN op.TYPE_OP = 'Conditionnelle' THEN ( ")
.append("SELECT compNature.LSTVALEUR FROM PLS_NAVIS avis ")
.append("JOIN PLS_LISTE compNature ON avis.LST_ID_COMP_NATURE = compNature.LST_ID ")
.append("WHERE op.AVIS_ID = avis.AVIS_ID ")
.append(" ) ELSE ( ")
.append("SELECT eq.TYPE_EQ FROM PLS_OP_NAVIS_EQUIPEMENT eq WHERE eq.OPERATION_ID = op.OP_ID AND Rownum = 1 ")
.append(") END AS \"equipement\", op.DATE_DEB_OP AS \"debutPlanifie\" ")
.append("FROM PLS_OPERATION op ")
.append("JOIN PLS_LIAISONSOUTERRAINE ls ON ls.OUVR_ID = op.OUVR_ID ")
.append("JOIN PLS_LIT lit ON lit.LITCODE = ls.LITCODE ")
.append("JOIN PLS_LISTE nature ON op.LST_ID_NATURE = nature.LST_ID ")
.append("JOIN PLS_LIAISON_LIT ll ON ll.LITCUR = lit.LITCUR ")
.append("WHERE op.STATUT NOT IN ('EXEC', 'PEXE') ")
.append("AND ( lit.LITCUR in ($3) OR ll.LIAISONCUR IN ($4) ) ");
builder.append(" ) ORDER BY \"ouvrageIdr\") AS ")
.append(AvisOperationsDTO.class.getName());
StringJoiner joinerLit = new StringJoiner(",");
StringJoiner joinerLiaison = new StringJoiner(",");
try {
Database db = CastorJDOFactory.getDatabase();
db.begin();
db.getQuery();
OQLQuery query = db.getOQLQuery(builder.toString());
QueryResults results = null;
// .....
} catch (Exception e) {
// .....
}
return avisoperations;
}
But when I call OQLQuery query = db.getOQLQuery(builder.toString()); Castor return this error:
org.exolab.castor.jdo.QueryException: Could not find an engine supporting class com.rte.infocable.api.dto.AvisOperationsDTO
I've created a mapping xml file for my dto class:
<!DOCTYPE database PUBLIC "-//EXOLAB/Castor JDO Configuration DTD Version 1.0//EN" "/WEB-INF/mapping.dtd">
<mapping>
<class name="com.rte.infocable.api.dto.AvisOperationsDTO" identity="identifiant">
<description>AvisOperationDTO</description>
<cache-type type="none"/>
<field name="curLiaison" type="string" required="true">
<sql name="curLiaison" type="varchar" />
</field>
<field name="curLit" type="string" required="true">
<sql name="curLit" type="string" />
</field>
<field name="ouvrageIdr" type="string" required="true">
<sql name="ouvrageIdr" type="string" />
</field><field name="ouvrageAdr" type="string" required="true">
<sql name="ouvrageAdr" type="string" />
</field>
<field name="typeObjet" type="string" required="true">
<sql name="typeObjet" type="string" />
</field>
<field name="dateCreation" type="java.time.LocalDateTime" required="true">
<sql name="dateCreation" type="date" />
</field>
<field name="description" type="string" required="true">
<sql name="description" type="varchar" />
</field>
<field name="identifiant" type="integer" required="true">
<sql name="identifiant" type="integer" />
</field>
<field name="statut" type="string" required="true">
<sql name="statut" type="varchar" />
</field>
<field name="equipement" type="string" required="true">
<sql name="equipement" type="string" />
</field>
<field name="debutPlanifie" type="java.time.LocalDateTime" required="true">
<sql name="debutPlanifie" type="Date" />
</field>
</class>
</mapping>
Then added this file in database.xml and databaseEchanges.xml as same as others xml mapping files used in the project:
<!DOCTYPE databases PUBLIC "-//EXOLAB/Castor JDO Configuration DTD Version 1.0//EN" "/WEB-INF/jdo-conf.dtd">
<database>
<mapping href="classes/mapping/AvisOperationsDTO.xml"/>
</database>
I've tried reading a great part of the Castor doc and few SO posts but I couldn't find anyone with the same issue.
I'd like to be able to run my SQL Query through OQLQuery with Castor whatever the type it returns as far as I can do it in one query.
Note: it's not a viable solution for now to change the ORM nor upgrade Castor.
While debug with IntelliJ, I saw that Castor try to get the info type of my query through an attribut called _typeInfo (LockEngone.getClassMolder()::103) but this HashMap does not contain the type refering to my dto class.
I found the solution to my problem, I needed to add a map-to clause in the xml mapping file as follow:
<mapping>
<class name="com.rte.infocable.api.dto.AvisOperationsDTO" identity="ouvrageIdr">
<description>AvisOperationDTO</description>
<cache-type type="none"/>
<map-to table="PLS_NAVIS"/>
</mapping>
Event though my DTO class is not related to the oracle Table, it allow Castor to refer to the mapping file. With the use of CALL SQL Castor does not check if my FROM clause is similar to the map-to table name as the output fields are the same as I configure in the mapping file.