Java （janfal）数据一对多关系处理--分页查询再遍历，遍历数据再去查询数据

1.业务场景：

现表中字段是一对多关系，如：

id	device
4141001	1,2,4,6
4141002	1,2,4,6,8

 

id	type	frequency	is_open
4141001	1	5	0
4141001	2	1	0

 

现需要查询 4141001 下的所有设备的参数及开关数据，是一对多的关系，所以考虑返回数据里设备参数是数组类型。

2.示例代码

思路：先分页查询，然后遍历分页查询的数据再去挨个查询每个id的设备参数

/*控制层*/    
public void laypage() {
        String id= getPara("id");
        Integer limit = getParaToInt("limit");
        Integer pageNum = getParaToInt("page");
        String authorization = getRequest().getHeader("Authorization");
        UserVO user = JwtUtil.decode(authorization);
        LayerUiTb layTable = null;
        try {
            /*分页查*/
            Page<EnavmarkStation> page = EnavmarkStation.dao.stationWarnlaypage(limit, pageNum, id, user);
            layTable = new LayerUiTb(0, "成功", page.getTotalRow(), page.getList());

            /*遍历数据，通过id去查询另一个表中的设备数据存在settings中*/
            List<EnavmarkStation> list = page.getList();
            for (EnavmarkStation enavmarkStation : list) {
                final Integer id= enavmarkStation.getInt("id");
                String sql = "SELECT * FROM enavmark_warntime_setting WHERE id= ?";
                enavmarkStation.put("settings", Db.use(Constant.ENAVMSI).find(sql, id));
            }

            renderJson(layTable);
        } catch (Exception e) {
            renderJson(LayerUiTb.returnNull());
            e.printStackTrace();
        }
    }

/*模糊查询*/
public Page<EnavmarkStation> stationWarnlaypage(Integer limit, Integer pageNum, String id, UserVO user) {
        String select = "select s.*, s.device,st_astext(s.geom) as geom,s.company ";
        StringBuilder form = new StringBuilder();
        form.append(" from enavmark_station s where 1=1 ");
        Record paras = new Record();
        if (StrKit.notBlank(id)) {
            form.append(" AND s.id::character varying like ?");
            SqlUtil.assemblyParam(paras, "%" + id+ "%");
        }
        form.append(changeSql(user));
        form.append(" order by s.iddesc");
        return dao.paginate(pageNum, limit, select, form.toString(), paras.getColumnValues());
    }

3.测试：