Chenile Mybatis Query Tutorial | Chenile - Documentation and Guidelines

How to write a new Mybatis based query in Chenile?

Edit me

Please do read about the Chenile Query framework before you read this tutorial. That would give you some context on what is Chenile Mybatis Query. As we saw there, Chenile already has a configured query to fetch data from a query database using Mybatis. We can configure new queries to execute in Mybatis. No code needed!

We will see how to do this in this article. Please download the student-query-service from chenile samples

Understanding the configurations in student-query-service

You will find that there is no code in src/main/java. We straightaway skip to configurations at src/main/resources. By convention, we use com.{companyname}.{orgname}.query.service.mapper to keep the configuration files. The code generator uses that convention to generate the code. For the samples, we used org.chenile.samples instead of com.{companyname}.{orgname}.

You will find two files. One is a typical Mybatis mapper file and the other one is a JSON file that contains the query meta data. Any query in Chenile needs to be configured using a query meta data file.

The JSON file

First let us look at the JSON.

[
	{

        "id": "Student.getAll",
        "name": "students",
        "columnMetadata": {
			"id" : {
				"name": "id",
				"filterable": true,
				"columnType": "Text"
			},
			"name" : {
				"name": "name",
				"columnType" : "Text",
				"likeQuery": true,
				"filterable": true
			},
			"branch": {
				"name": "branch",
				"filterable": true,
				"columnType": "Text",
				"containsQuery": true,
				"sortable": true
			},
			"phone": {
				"name" : "phone",
				"filterable": true,
				"columnType": "Text"
			},
			"percentage" : {
				"name": "name",
				"columnType" : "Text",
				"filterable": true
			},
			"email": {
				"name": "email",
				"columnType" : "Text",
				"likeQuery": true,
				"filterable": true
			}
		},	
		"flexiblePropnames": false,
        "paginated" : true,
        "sortable" : true
    }
    
]

As you see , the query meta data is not tied to Mybatis. You can use this to configure any query in Chenile. We will support other types of queries in the future using the same meta data. Here we will use the id to map the query to mybatis. The name will be visible to the user. All requests to the getAll query would be made to /q/students. This would internally map it to the Mybatis Srudents.getAll query that will be defined in the mapper file. The column meta data is useful and will be passed verbatim in SearchResponse so that the UI can use this information to display the screen as stated in the page on query framework

It is important to explicitly enable pagination and sorting at the query level. Once the query is defined we can write the corresponding Mybatis mapper file.

For paginated queries, Chenile runs the `<queryId>-count` mapper by default. If this query is expensive for a specific query, add `"countQueryEnabled": false` to that query definition. If the service disables count queries globally but this query still needs exact totals, add `"countQueryEnabled": true`. When this property is absent, the query follows the global `query.pagination.countQueryEnabled` setting.

Truth table:

| Query JSON `countQueryEnabled` | Global `query.pagination.countQueryEnabled` | Effective behavior |
| --- | --- | --- |
| `true` | `true` | Count query runs |
| `true` | `false` | Count query runs |
| `true` | absent | Count query runs |
| `false` | `true` | Count query does not run |
| `false` | `false` | Count query does not run |
| `false` | absent | Count query does not run |
| absent | `true` | Count query runs |
| absent | `false` | Count query does not run |
| absent | absent | Count query runs |

When a UI or client needs only the total number of matching rows, pass `"countOnly": true` in the search request:

	{
	  "countOnly": true,
	  "filters": {
	    "branch": ["Bangalore"]
	  },
	  "pageNum": 1,
	  "numRowsInPage": 25
	}

In this mode Chenile runs only the `getAll-count` query, skips the `getAll` list query, returns no rows, and fills `maxRows` and `maxPages`. This request flag overrides `countQueryEnabled`, so it still works when count queries are disabled for normal list requests.

The Mapper file

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">	
<mapper namespace = "Student">


<resultMap id = "result" type = "map">
   <result property = "id" column = "ID"/>
   <result property = "name" column = "NAME"/>
   <result property = "branch" column = "BRANCH"/>
   <result property = "percentage" column = "PERCENTAGE"/>
   <result property = "phone" column = "PHONE"/>
   <result property = "email" column = "EMAIL"/>
</resultMap>

<!-- the getAll query supports pagination. So make sure that there exists a count query
with the name getAll-count for such queries. Also all paginated queries must have 
${pagination} as part of them as shown below. -->
<select id='getAll-count' resultType="int" >
	select count(*) from student
    <where>
    <if test="branch != null">
         branch in
        <foreach item="item" index="index" collection="branch"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </if>
    <if test="name != null">
        AND name like #{name}
    </if>
    <if test="phone != null">
        AND phone = #{phone}
    </if>
  </where>
</select>

<!-- The getAll query supports an elaborate where clause. 
The conditional constructs ensure that the clause is constructed only if 
specific filters are passed. Notice that 
branch supports an IN clause and name supports a like clause. 
This information must be reflected in the column meta data in the definitions JSON that 
accompanies this mapper.
Since this query is sortable the orderby clause is important.
Since this query supports pagination the ${pagination} is mandatory. 
We also need the count query above
Make sure that sortable and paginated are set to true in the corresponding query definitions 
-->
<select id = "getAll" resultMap = "result">	
   SELECT * FROM student
    <where>
    <if test="branch != null">
         branch in
	    <foreach item="item" index="index" collection="branch"
	             open="(" separator="," close=")">
	        #{item}
	    </foreach>
    </if>
    <if test="name != null">
        AND name like #{name}
    </if>
    <if test="phone != null">
        AND phone = #{phone}
    </if>
  </where>
  ${orderby} ${pagination}
</select>
    	
</mapper>

Here we define the getALl query in the student namespace. A few observations: 1. getAll is a paginated query. Since paginated queries need to explicitly return maxPages and maxCount by default, it is important to define a “count query” as well. The count query is called getAll-count by convention. This allows Chenile to know where to look for it. A query can opt out by setting "countQueryEnabled": false; in that case Chenile uses no-count pagination and returns pagination.nextPageAvailable.

The orderby and pagination variables need to be added verbatim at the end of the getAll query (not the count query). This allows pagination and sorting. The paginated and sortable fields in the query meta data (in the JSON) must be set to true.
The count query has exactly the same where clause as the getAll query.
like filters are treated using the key word like as shown above
between filters need to be treated using upper bound and lower bound (not shown in this example)
contains filters must have the foreach loop as shown above

With these two files in place, you are all set. Please see the src/test/ for the testcases and feature files.

Adding a tenant-specific query override

In a multi-tenant product, a client/tenant may need a different query while the UI and API contract remain the same. Do not create a tenant-specific URL. Keep the same external query name and add a tenant-specific metadata entry.

Base definition:

{
  "id": "Student.getAll",
  "name": "students",
  "paginated": true,
  "sortable": true,
  "columnMetadata": {
    "id": {
      "name": "id",
      "filterable": true,
      "columnType": "Text"
    }
  }
}

Tenant override:

{
  "tenantId": "tenant1",
  "id": "tenant1.Student.getAll",
  "name": "students",
  "paginated": true,
  "sortable": true,
  "columnMetadata": {
    "id": {
      "name": "id",
      "filterable": true,
      "columnType": "Text"
    }
  }
}

The public request remains:

curl -X POST http://localhost:8080/q/students \
  -H 'Content-Type: application/json' \
  -H 'x-chenile-tenant-id: tenant1' \
  -d '{"pageNum":1,"numRowsInPage":20}'

For tenant1, Chenile executes the tenant-specific mapper id tenant1.Student.getAll. For another tenant with no override, Chenile falls back to the base mapper id Student.getAll.

The MyBatis mapper namespace must match the metadata id prefix:

<mapper namespace="tenant1.Student">
  <select id="getAll-count" resultType="int">
    select count(*) from student where id > 20
  </select>

  <select id="getAll" resultMap="result">
    select * from student where id > 20 ${orderby} ${pagination}
  </select>
</mapper>

For paginated tenant overrides, remember that the count query follows the resolved query id. If metadata resolves to tenant1.Student.getAll, the count mapper is tenant1.Student.getAll-count.

Configure tenant datasources in application.yml:

query:
  defaultTenantId: tenant1
  datasources:
    tenant1:
      type: com.zaxxer.hikari.HikariDataSource
      jdbcUrl: jdbc:postgresql://localhost:5433/query_tenant1
      username: query_user
      password: query_password
    tenant2:
      type: com.zaxxer.hikari.HikariDataSource
      jdbcUrl: jdbc:postgresql://localhost:5433/query_tenant2
      username: query_user
      password: query_password

If query.defaultTenantId is configured, missing or blank tenant headers use the default and log a warning. If it is not configured, missing or blank tenant information fails with Q723. If a tenant header is present but not configured, Chenile does not fall back to the default tenant.

Tags: