不灭的焱

[手册]OQL语法

Datasets

• OQL
  • OQL Select Clause
  • OQL From Clause
  • OQL Where Clause
  • OQL Group by Clause
  • OQL Order by Clause
  • OQL Limit Clause
  • OQL Expressions
    • OQL Aggregation
    • OQL Functions
    • OQL Operators
    • OQL Parameters
    • OQL Subqueries

OQL官方教程： 在线手册

Query data from the Mendix database and the result will be mapped to a Mendix entity. The example below is part of the Example module within the project, located on the GitHub repository.

Considering the following domain model:

One can execute a query obtaining all objects of ExamplePerson as a table. The rows within this table can be converted to non persistent entities for further usage. Therefore a ExamplePersonResult non persistent entity is modeled.

Parameters can be used using their corresponding actions (for each data type there's one activity defined:

All parameters should be added before executing an OQL query.

Adding an parameter requires:

The name of the parameter, without a $ (e.g. Gender). The value. Executing an OQL query requires:

The OQL statement. A resulting entity (e.g. OQLExample.ExamplePersonResult). Amount. Offset.

After executing an OQL query, all previously set parameters are cleared.

The example query used to obtain the ExamplePersonResults are is:

SELECT 
    P.id ExamplePersonResult_ExamplePerson, 
    P.Name Name, 
    P.Number Number, 
    P.DateOfBirth DateOfBirth, 
    P.Age Age, 
    P.LongAge LongAge, 
    P.HeightInFloat HeightInFloat, 
    P.HeightInDecimal HeightInDecimal, 
    P.Active Active, 
    P.Gender Gender 
FROM 
    OQLExample.ExamplePerson P 
WHERE 
    P.Active = $Active 
    AND P.Age = $Age AND P.DateOfBirth = $DateOfBirth 
    AND P.Gender = $Gender 
    AND P.HeightInDecimal = $HeightInDecimal 
    AND P.HeightInFloat = $HeightInFloat 
    AND P.LongAge = $LongAge 
    AND P.Name = $Name 
    AND P.Number = $Number 
    AND P/OQL.MarriedTo/OQL.ExamplePerson/ID = $MarriedTo

In the example above, the resulting columns Name, Number, DateOfBirth, Age, etc. are mapped to their corresponding attributes in ExamplePersonResult. The column ExamplePersonResult_ExamplePerson is mapped to the association (so one can retrieve the original persistent entity if needed).

// BEGIN USER CODE
List<IMendixObject> result = new LinkedList<>();
try {
    /*
      抓包结果：
        SELECT
            `A`.`id` AS `BlogVO_Blog`,
            `A`.`blognum`,
            `A`.`title`,
            `B`.`sortname`,
            `A`.`ordernum`,
            `A`.`viewcount`
        FROM
            `common$blog` `A` LEFT OUTER
        JOIN `common$blog_sort` `atj1Common$Blog_Sort` ON
            `atj1Common$Blog_Sort`.`common$blogid` = `A`.`id` LEFT OUTER
        JOIN `common$sort` `B` ON
            `B`.`id` = `atj1Common$Blog_Sort`.`common$sortid`
        WHERE
            1 = 1
        LIMIT 5
    */
    String SQL = "SELECT " +
        "A.ID BlogVO_Blog ,A.BlogNum,A.Title, B.SortName,A.OrderNum,A.ViewCount " +
        "FROM Common.Blog A " +
        "LEFT JOIN A/Common.Blog_Sort/Common.Sort B " +
        "WHERE 1=1 ";
    BlogSearchForm blogSearchForm = this.BlogPageHelper.getBlogPageHelper_BlogSearchForm();

    Map<String, Object> paramMap = new HashMap<>();

    // 标题
    if (StrUtil.isNotEmpty(blogSearchForm.getTitle())) {
        SQL += " AND A.Title LIKE $Title ";
        paramMap.put("Title", "%" + blogSearchForm.getTitle() + "%");
    }

    // 查看次数
    if (StrUtil.isNotEmpty(blogSearchForm.getViewCount())) {
        SQL += " AND A.ViewCount = $ViewCount ";
        paramMap.put("ViewCount", blogSearchForm.getViewCount());
    }

    // 所属分类
    Sort sort = blogSearchForm.getBlogSearchForm_Sort();
    if (ObjUtil.isNotEmpty(sort)) {
        SQL += " AND B.ID = $Sort ";
        paramMap.put("Sort", sort.getMendixObject().getId());
    }

    result = DbHelper.getMendixObjectListByOQL(getContext(), SQL, paramMap, BlogVO.entityName, 5L, 0L);
} catch (Exception e) {
    LogHelper.error("【搜索文章】", e.getMessage(), e);
}

return result;

// END USER CODE