D10067 - Jooq/CRUD, Paging/Sorting

This chapter will focus on Jooq advanced search operation, it could be used in different pagination list table, you can refer current tutorial to check more details about how to write advanced searching operation.

Demo projects:

Here are the all the apis that Ux.Jooq class provided for paging, sorting

In current version, zero system provided above three apis only, if you want to use dto, you must provide the mapping up.god.file that has been introduced in previous tutorial:

src/main/resources/pojo/tabular.yml

type: "up.god.domain.tables.pojos.SysTabular"
mapping:
  pkId: key
  isActive: active
  zsigma: sigma
  zlanguage: language
  zcreateTime: createTime
  scode: code
  stype: type
  iorder: order
  sname: name
columns:
  name: S_NAME
  code: S_CODE
  type: S_TYPE
  order: I_ORDER
  language: Z_LANGUAGE
  createTime: Z_CREATE_TIME
  updateTime: Z_UPDATE_TIME
  active: IS_ACTIVE
  sigma: Z_SIGMA

1. Source Code

1.1. Api

package up.god.micro.advanced;

import io.vertx.up.annotations.Address;
import io.vertx.up.annotations.EndPoint;

import javax.ws.rs.BodyParam;
import javax.ws.rs.POST;
import javax.ws.rs.Path;

@EndPoint
@Path("/api")
public interface SearchApi {
    @Path("tabular/search")
    @POST
    @Address("ZERO://QUEUE/SEARCH")
    String search(@BodyParam String name);
}

Because we’ll serialize the body data into Inquiry directly, here we recommend to use POST instead of GET method do searching.

1.2. Consumer

package up.god.micro.advanced;

import io.vertx.core.Future;
import io.vertx.core.json.JsonObject;
import io.vertx.up.unity.Ux;
import io.vertx.up.annotations.Address;
import io.vertx.up.annotations.Queue;
import io.vertx.up.commune.Envelop;

import javax.inject.Inject;

@Queue
public class SearchWorker {

    @Inject
    private transient SearchStub searchStub;

    @Address("ZERO://QUEUE/SEARCH")
    public Future<JsonObject> search(final Envelop envelop) {
        final JsonObject data = Ux.getJson(envelop);
        // The second parameter is the yml up.god.file name "tabular.yml"
        return this.searchStub.search(Ux.getInquiry(data, "tabular"));
    }
}

1.3. Stub ( Interface )

package up.god.micro.advanced;

import io.vertx.core.Future;
import io.vertx.core.json.JsonObject;
import io.vertx.up.atom.query.Inquiry;

public interface SearchStub {

    Future<JsonObject> search(final Inquiry inquiry);
}

1.4. Service ( Implementation )

package up.god.micro.advanced;

import io.vertx.core.Future;
import io.vertx.core.json.JsonObject;
import io.vertx.up.unity.Ux;
import io.vertx.up.atom.query.Inquiry;
import up.god.domain.tables.daos.SysTabularDao;

public class SearchService implements SearchStub {
    @Override
    public Future<JsonObject> search(final Inquiry inquiry) {
        return Ux.Jooq.on(SysTabularDao.class)
                .searchAndAsync(inquiry, "tabular");
    }
}

2. Points for searching

3. Testing

Here we prepared different cases to test this searching api.

URL : http://localhost:6093/api/tabular/search

Method : POST

3.1. Empty Request

Request :

{
}

Response :

{
    "data":{
        "list":[
            {
                "key": 1,
                "active": true,
                "name": "挂牌价",
                "code": "Standard",
                "type": "code.pricecat",
                "order": 1,
                "sigma": "ENhwBAJPZuSgIAE5EDakR6yrIQbOoOPq",
                "language": "cn",
                "createTime": "2018-02-07T12:09:32"
            },
            ......
        ],
        "count":168
    }
}

Returned all the records in your database and the count.

3.2. Paging

Request :

{
    "pager":{
        "page":1,
        "size":10
    }
}

Response :

{
    "data":{
        "list":[
            {
                "key": 1,
                "active": true,
                "name": "挂牌价",
                "code": "Standard",
                "type": "code.pricecat",
                "order": 1,
                "sigma": "ENhwBAJPZuSgIAE5EDakR6yrIQbOoOPq",
                "language": "cn",
                "createTime": "2018-02-07T12:09:32"
            },
            ......,
            {
                "key": 10,
                "active": true,
                "name": "预留房",
                "code": "Left",
                "type": "room.status",
                "jconfig": "{\"icon\":\"green heart\"}",
                "order": 3,
                "sigma": "ENhwBAJPZuSgIAE5EDakR6yrIQbOoOPq",
                "language": "cn",
                "createTime": "2018-02-07T12:09:32"
            }
        ],
        "count":168
    }
}

3.3. Sorting

Request :

{
    "pager":{
        "page":1,
        "size":10
    },
    "sorter":[
        "type,DESC",
        "order,DESC"
    ]
}

Response :

{
    "data":{
        "list":[
            {
                "key": 144,
                "active": true,
                "name": "历史宾客",
                "code": "History",
                "type": "traveler.status",
                "order": 2,
                "sigma": "ENhwBAJPZuSgIAE5EDakR6yrIQbOoOPq",
                "language": "cn",
                "createTime": "2018-02-07T12:09:32"
            },
            ......,
            {
                "key": 159,
                "active": true,
                "name": "客房",
                "code": "Room",
                "type": "shift.type",
                "order": 1,
                "sigma": "ENhwBAJPZuSgIAE5EDakR6yrIQbOoOPq",
                "language": "cn",
                "createTime": "2018-02-07T12:09:32"
            }
        ],
        "count":168
    }
}

3.4. Projection

In this request you can set some filters for returned columns.

Request :

{
    "pager":{
        "page":1,
        "size":10
    },
    "sorter":[
        "type,DESC",
        "order,DESC"
    ],
    "projection":[
        "active",
        "sigma",
        "language"
    ]
}

Response :

{
    "data":{
        "list":[
            {
                "key": 144,
                "name": "历史宾客",
                "code": "History",
                "type": "traveler.status",
                "order": 2,
                "createTime": "2018-02-07T12:09:32"
            },
            ......,
            {
                "key": 159,
                "name": "客房",
                "code": "Room",
                "type": "shift.type",
                "order": 1,
                "createTime": "2018-02-07T12:09:32"
            }
        ],
        "count":168
    }
}

3.5. Criteria

The last core parameters are criteria for condition setting.

Request :

{
    "pager":{
        "page":1,
        "size":10
    },
    "sorter":[
        "type,DESC",
        "order,DESC"
    ],
    "projection":[
        "active",
        "sigma",
        "language"
    ],
    "criteria":{
        "order,<":2
    }
}

Response :

{
    "data":{
        "list":[
            {
                "key": 143,
                "name": "在住宾客",
                "code": "OnGoing",
                "type": "traveler.status",
                "order": 1,
                "createTime": "2018-02-07T12:09:32"
            },
            ......,
            {
                "key": 38,
                "name": "现金",
                "code": "Cash",
                "type": "pay.type",
                "order": 1,
                "createTime": "2018-02-07T12:09:32"
            }
        ],
        "count":38
    }
}

4. Summary

From this chapter you should know how to do common searching with zero system Jooq.