Complex queries

Introduction

In this example the DAO *.xml files will be modified to make complex queries against other database tables.

Database structure

We will update the OfferCandidatesDao DAO to add new information in the *.xml. In this case, you will try to add a new query that reflects the other tables that are used from this DAO to relate the offers, candidates, and status. As this table only contains identifiers, our new query will allow us to know all the data and not only its identifiers.

database

Modify the DAO to add a complex query

In the *.xml, we will add a new <Queries> tag in which we will add the new queries using the <Query> tag. In this query, we can indicate the query to be executed through the <Sentence> tag. As we want to use the columns and conditions that you indicate through the request, we will use the following markers:

Query markers

Marker Meaning
#COLUMNS# Columns to be queried
#WHERE# Columns to filter the query
#WHERE_CONCAT# Adds more columns to filter the query with the AND sql operator
#ORDER# Column to order the queried values with the ORDER BY sql operator
#ORDER_CONCAT# Adds more columns to order the queried values with a comma (,)
#SCHEMA# Gets the database schema

Example

<Queries>
    <Query>
        <Sentence>
            <![CDATA[
                SELECT
                #COLUMNS#
                FROM
                #SCHEMA#.OFFER_CANDIDATES
                #WHERE#
            ]]>
        </Sentence>
    </Query>
</Queries>

This will be the query to execute:

SELECT ID, OFFER_ID, CANDIDATE_ID, OFFER_CANDIDATE_STATUS FROM PUBLIC.OFFER_CANDIDATES WHERE ID = 200

There are two ways to order the queries values, with the #ORDER# marker or with a <OrderColumn> tag

Example with #ORDER#

<Queries>
    <Query>
        <Sentence>
            <![CDATA[
                SELECT
                #COLUMNS#
                FROM
                OFFER_CANDIDATES
                #ORDER#
            ]]>
        </Sentence>
    </Query>
</Queries>

This will be the query to execute:

SELECT ID, OFFER_ID, CANDIDATE_ID, OFFER_CANDIDATE_STATUS FROM PUBLIC.OFFER_CANDIDATES ORDER BY ID

Example with <OrderColumn>

<Queries>
    <Query>
        <OrderColumns>
            <OrderColumn name="CANDIDATE_ID" type="ASC"/>
        </OrderColumns>
        <Sentence>
            <![CDATA[
                SELECT
                #COLUMNS#
                FROM
                OFFER_CANDIDATES
            ]]>
        </Sentence>
    </Query>
</Queries>

This will be the query to execute:

SELECT ID, OFFER_ID, CANDIDATE_ID, OFFER_CANDIDATE_STATUS FROM PUBLIC.OFFER_CANDIDATES ORDER BY CANDIDATE_ID

There are two types of OrderColumn, ASC (Ascendent) or DESC (Descendent).

Ambiguous columns

The use of the <AmbiguousColumn> tag will indicate which are the ambiguous columns (in this case, the DESCRIPTION column of OFFER_CANDIDATE_STATUS and OFFER, using the name of DESC_STATUS for OFFER_CANDIDATE_STATUS). Otherwise in case we want to modify the query that is performed by default, the identifier of the tag would be default <Query id = "default">. As we want to keep the query by default, we will put another identifier.

Example

 <Queries>
  <Query id="details">
   <AmbiguousColumns>
    <AmbiguousColumn name="DESC_STATUS" prefix="OS"
     databaseName="DESCRIPTION" />
    <AmbiguousColumn name="OFF_STATUS" prefix="OCS"
     databaseName="DESCRIPTION" />
   </AmbiguousColumns>
   <Sentence>
   <![CDATA[
     SELECT
      #COLUMNS#
     FROM
      PUBLIC.OFFER_CANDIDATES OC
     INNER JOIN PUBLIC.OFFER O ON
      OC.OFFER_ID = O.ID
     INNER JOIN PUBLIC.CANDIDATE C ON
      OC.CANDIDATE_ID = C.ID
     INNER JOIN PUBLIC.OFFER_STATUS OS ON
      O.OFFER_STATUS = OS.ID
     INNER JOIN PUBLIC.OFFER_CANDIDATE_STATUS OCS ON
      O.OFFER_STATUS = OCS.ID
     #WHERE#
   ]]>
   </Sentence>
  </Query>
 </Queries>

Complete example

OfferCandidatesDao.xml

<?xml version="1.0" encoding="UTF-8"?>
<JdbcEntitySetup
 xmlns="http://www.ontimize.com/schema/jdbc"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xsi:schemaLocation="http://www.ontimize.com/schema/jdbc http://www.ontimize.com/schema/jdbc/ontimize-jdbc-dao.xsd"
 catalog="" schema="${mainschema}" table="OFFER_CANDIDATES"
 datasource="mainDataSource" sqlhandler="dbSQLStatementHandler">
 <DeleteKeys>
  <Column>ID</Column>
 </DeleteKeys>
 <UpdateKeys>
  <Column>ID</Column>
 </UpdateKeys>
 <GeneratedKey>ID</GeneratedKey>

 <Queries>
  <Query id="details">
   <AmbiguousColumns>
    <AmbiguousColumn name="DESC_STATUS" prefix="OS"
     databaseName="DESCRIPTION" />
    <AmbiguousColumn name="OFF_STATUS" prefix="OCS"
     databaseName="DESCRIPTION" />
   </AmbiguousColumns>
   <Sentence>
   <![CDATA[
     SELECT
      #COLUMNS#
     FROM
      PUBLIC.OFFER_CANDIDATES OC
     INNER JOIN PUBLIC.OFFER O ON
      OC.OFFER_ID = O.ID
     INNER JOIN PUBLIC.CANDIDATE C ON
      OC.CANDIDATE_ID = C.ID
     INNER JOIN PUBLIC.OFFER_STATUS OS ON
      O.OFFER_STATUS = OS.ID
     INNER JOIN PUBLIC.OFFER_CANDIDATE_STATUS OCS ON
      O.OFFER_STATUS = OCS.ID
     #WHERE#
   ]]>
   </Sentence>
  </Query>
 </Queries>
</JdbcEntitySetup>

In the java file, we will add a new constant, which will have the same value as the name of the identifier that we have established for the query.

OfferCandidatesDao.java

package com.ontimize.model.core.dao;

import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Repository;

import com.ontimize.jee.server.dao.common.ConfigurationFile;
import com.ontimize.jee.server.dao.jdbc.OntimizeJdbcDaoSupport;

@Repository("OfferCandidatesDao")
@Lazy
@ConfigurationFile(configurationFile = "dao/OfferCandidatesDao.xml", configurationFilePlaceholder = "dao/placeholders.properties")
public class OfferCandidatesDao extends OntimizeJdbcDaoSupport {

 public static final String ATTR_ID ="ID";
 public static final String ATTR_OFFER_ID ="OFFER_ID";
 public static final String ATTR_CANDIDATE_ID ="CANDIDATE_ID";
 public static final String ATTR_OFFER_CANDIDATE_STATUS ="OFFER_CANDIDATE_STATUS";
 public static final String QUERY_OFFER_DETAILS = "details";
  
}

Modify the interface and the service to add the new method

We will update the IOfferService interface to add the new method that will perform the query. In case of being the default query, it would not be necessary to do any of the steps indicated below.

IOfferService.java

public interface IOfferService {

        ...
 // OFFER CANDIDATES
 ...
 public EntityResult offerCandidateDetailsQuery(Map<String, Object> keyMap, List<String> attrList) throws OntimizeJEERuntimeException;

        ...
 // OFFER CANDIDATES STATUS
        ...
}

In the service, we implement the new interface method, using the daoHelper to create the query, but adding a new parameter after the list of columns to query, which will be the identifier of the query that we have created in the DAO (and that we have associated with a constant in the corresponding java file).

OfferService.java

@Service("OfferService")
@Lazy
public class OfferService implements IOfferService {

 ...

 @Override
 public EntityResult offerCandidateDetailsQuery(Map<String, Object> keyMap, List<String> attrList)
   throws OntimizeJEERuntimeException {
  return this.daoHelper.query(this.offerCandidatesDao, keyMap, attrList, OfferCandidatesDao.QUERY_OFFER_DETAILS);
 }

 ...
}