Using 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

ontimize-boot-tutorial
- api
  - src
    - main
      - java
        
        com
        
        ontimize
        
        api
        
        core
        
        service
        
        ICandidateService.java
        
        IMasterService.java
        
        IOfferService.java
        
        IUserService.java
  - pom.xml
- boot
  - src
    - main
      - java
        
        com
        
        ontimize
        
        boot
        
        core
        
        ServerApplication.java
      - resources
        
        application.yml
  - pom.xml
- frontend
  - src
    - main
      - ngx
        
        aot-config
        
        helpers.js
        
        index.ejs
        
        vendor-aot.ts
        
        webpack-aot.config.js
        
        src
        
        app
        
        login
        
        login-routing.module.ts
        
        login.component.html
        
        login.component.scss
        
        login.component.ts
        
        login.module.ts
        
        login.theme.scss
        
        main
        
        home
        
        home-routing.module.ts
        
        home.component.html
        
        home.component.scss
        
        home.component.ts
        
        home.module.ts
        
        main-routing.module.ts
        
        main.component.html
        
        main.component.scss
        
        main.component.ts
        
        main.module.ts
        
        shared
        
        app.menu.config.ts
        
        app.services.config.ts
        
        shared.module.ts
        
        app-routing.module.ts
        
        app.component.html
        
        app.component.scss
        
        app.component.ts
        
        app.config.ts
        
        app.module.ts
        
        assets
        
        css
        
        app.scss
        
        loader.css
        
        i18n
        
        en.json
        
        es.json
        
        images
        
        no-image.png
        
        ontimize.png
        
        ontimize_web_log.png
        
        sidenav-closed.png
        
        sidenav-opened.png
        
        user_profile.png
        
        js
        
        domchange.js
        
        keyboard.js
        
        environments
        
        environment.prod.ts
        
        environment.ts
        
        favicon.ico
        
        index.html
        
        main.ts
        
        polyfills.ts
        
        styles.scss
        
        test.ts
        
        tsconfig.app.json
        
        tsconfig.spec.json
        
        angular.json
        
        package-lock.json
        
        package.json
        
        README.md
        
        tsconfig.aot.json
        
        tsconfig.json
        
        tslint.json
      - resources
        
        application.properties
  - pom.xml
- model
  - src
    - main
      - db
        
        templateDB.script
        
        templateDB.txt
      - java
        
        com
        
        ontimize
        
        model
        
        core
        
        dao
        
        CandidateDao.java
        
        EducationDao.java
        
        ExperienceLevelDao.java
        
        OfferCandidatesDao.java
        
        OfferCandidateStatusDao.java
        
        OfferDao.java
        
        OfferStatusDao.java
        
        OriginDao.java
        
        ProfileDao.java
        
        StatusDao.java
        
        UserDao.java
        
        UserRoleDao.java
        
        service
        
        CandidateService.java
        
        MasterService.java
        
        OfferService.java
        
        UserService.java
      - resources
        
        dao
        
        CandidateDao.xml
        
        EducationDao.xml
        
        ExperienceLevelDao.xml
        
        OfferCandidatesDao.xml
        
        OfferCandidateStatusDao.xml
        
        OfferDao.xml
        
        OfferStatusDao.xml
        
        OriginDao.xml
        
        placeholders.properties
        
        ProfileDao.xml
        
        RoleDao.xml
        
        RoleServerPermissionDao.xml
        
        ServerPermissionDao.xml
        
        StatusDao.xml
        
        UserDao.xml
        
        UserRoleDao.xml
  - pom.xml
- ws
  - src
    - main
      - java
        
        com
        
        ontimize
        
        ws
        
        core
        
        rest
        
        CandidateRestController.java
        
        MainRestController.java
        
        MasterRestController.java
        
        OfferRestController.java
        
        TestRestController.java
        
        UserRestController.java
  - pom.xml
- pom.xml
- README.md

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.

ontimize-boot-tutorial
- api
  - src
    - main
      - java
        
        com
        
        ontimize
        
        api
        
        core
        
        service
        
        ICandidateService.java
        
        IMasterService.java
        
        IOfferService.java
        
        IUserService.java
  - pom.xml
- boot
  - src
    - main
      - java
        
        com
        
        ontimize
        
        boot
        
        core
        
        ServerApplication.java
      - resources
        
        application.yml
  - pom.xml
- frontend
  - src
    - main
      - ngx
        
        aot-config
        
        helpers.js
        
        index.ejs
        
        vendor-aot.ts
        
        webpack-aot.config.js
        
        src
        
        app
        
        login
        
        login-routing.module.ts
        
        login.component.html
        
        login.component.scss
        
        login.component.ts
        
        login.module.ts
        
        login.theme.scss
        
        main
        
        home
        
        home-routing.module.ts
        
        home.component.html
        
        home.component.scss
        
        home.component.ts
        
        home.module.ts
        
        main-routing.module.ts
        
        main.component.html
        
        main.component.scss
        
        main.component.ts
        
        main.module.ts
        
        shared
        
        app.menu.config.ts
        
        app.services.config.ts
        
        shared.module.ts
        
        app-routing.module.ts
        
        app.component.html
        
        app.component.scss
        
        app.component.ts
        
        app.config.ts
        
        app.module.ts
        
        assets
        
        css
        
        app.scss
        
        loader.css
        
        i18n
        
        en.json
        
        es.json
        
        images
        
        no-image.png
        
        ontimize.png
        
        ontimize_web_log.png
        
        sidenav-closed.png
        
        sidenav-opened.png
        
        user_profile.png
        
        js
        
        domchange.js
        
        keyboard.js
        
        environments
        
        environment.prod.ts
        
        environment.ts
        
        favicon.ico
        
        index.html
        
        main.ts
        
        polyfills.ts
        
        styles.scss
        
        test.ts
        
        tsconfig.app.json
        
        tsconfig.spec.json
        
        angular.json
        
        package-lock.json
        
        package.json
        
        README.md
        
        tsconfig.aot.json
        
        tsconfig.json
        
        tslint.json
      - resources
        
        application.properties
  - pom.xml
- model
  - src
    - main
      - db
        
        templateDB.script
        
        templateDB.txt
      - java
        
        com
        
        ontimize
        
        model
        
        core
        
        dao
        
        CandidateDao.java
        
        EducationDao.java
        
        ExperienceLevelDao.java
        
        OfferCandidatesDao.java
        
        OfferCandidateStatusDao.java
        
        OfferDao.java
        
        OfferStatusDao.java
        
        OriginDao.java
        
        ProfileDao.java
        
        StatusDao.java
        
        UserDao.java
        
        UserRoleDao.java
        
        service
        
        CandidateService.java
        
        MasterService.java
        
        OfferService.java
        
        UserService.java
      - resources
        
        dao
        
        CandidateDao.xml
        
        EducationDao.xml
        
        ExperienceLevelDao.xml
        
        OfferCandidatesDao.xml
        
        OfferCandidateStatusDao.xml
        
        OfferDao.xml
        
        OfferStatusDao.xml
        
        OriginDao.xml
        
        placeholders.properties
        
        ProfileDao.xml
        
        RoleDao.xml
        
        RoleServerPermissionDao.xml
        
        ServerPermissionDao.xml
        
        StatusDao.xml
        
        UserDao.xml
        
        UserRoleDao.xml
  - pom.xml
- ws
  - src
    - main
      - java
        
        com
        
        ontimize
        
        ws
        
        core
        
        rest
        
        CandidateRestController.java
        
        MainRestController.java
        
        MasterRestController.java
        
        OfferRestController.java
        
        TestRestController.java
        
        UserRestController.java
  - pom.xml
- pom.xml
- README.md

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);
 }

 ...
}