Advanced features—multi-table query and custom result set data processing


Multi-table query and custom result set data processing

The ORM provided by the JDBC module is mainly for single-entity operations. Actual business often involves multi-table related queries and returning multiple tables. Fields cannot automatically convert JDBC result set records into entity objects in a single-entity ORM. In this case, custom processing of the result set data is required to meet business needs.

If you want to implement customized processing of result set data, you need to understand the following related interfaces and classes:

  • IResultSetHandler interface: Result set data processing interface, used to complete the Each row of records in the original data of the JDBC result set is converted into a target object. The JDBC module provides three implementations of this interface by default:

    EntityResultSetHandler: An interface implementation that uses entity classes to store result set data. This The class has been integrated into the business logic of the ISession session interface and is only used to process the data conversion of a single entity;

    MapResultSetHandler: adopts the interface implementation of Map to store result set data;

    ArrayResultSetHandler: adopts Object[ ] Interface implementation for array storage of result set data;

  • ResultSetHelper class: Data result set auxiliary processing tool, used to help developers conveniently read and traverse the data content in the result set , only supports the result set data type generated by ArrayResultSetHandler and MapResultSetHandler;

The following introduces how to use the IResultSetHandler interface and the ResultSetHelper class together through a simple multi-table association query:

Sample code one: Use ArrayResultSetHandler or MapResultSetHandler to process result set data; 

    IResultSet<Object[]> _results = JDBC.get().openSession(new ISessionExecutor<IResultSet<Object[]>>() {
        public IResultSet<Object[]> execute(ISession session) throws Exception {
            // 通过查询对象创建SQL语句:
            //
            // SELECT u.id id, u.username username, ue.money money 
            //          FROM user u LEFT JOIN user_ext ue ON u.id = ue.uid
            //
            Select _uSelect = Select.create(User.class, "u")
                    .join(Join.left(UserExt.TABLE_NAME).alias("ue")
                            .on(Cond.create()
                                    .opt("u", User.FIELDS.ID, Cond.OPT.EQ, "ue", UserExt.FIELDS.UID)))
                    .field(Fields.create()
                            .add("u", User.FIELDS.ID, "id")
                            .add("u", User.FIELDS.USER_NAME, "username")
                            .add("ue", UserExt.FIELDS.MONEY, "money"));

            // 执行查询并指定采用Object[]数组存储结果集数据，若采用Map存储请使用：IResultSetHandler.MAP
            return session.find(SQL.create(_uSelect), IResultSetHandler.ARRAY);
        }
    });

    // 采用默认步长(step=1)逐行遍历
    ResultSetHelper.bind(_results).forEach(new ResultSetHelper.ItemHandler() {
        public boolean handle(ResultSetHelper.ItemWrapper wrapper, int row) throws Exception {
            System.out.println("当前记录行数: " + row);

            // 通过返回的结果集字段名取值
            String _id = wrapper.getAsString("id");
            String _uname = wrapper.getAsString("username");

            // 也可以通过索引下标取值
            Double _money = wrapper.getAsDouble(2);

            // 也可以直接将当前行数据赋值给实体对象或自定义JavaBean对象
            wrapper.toEntity(new User());

            // 当赋值给自定义的JavaBean对象时需要注意返回的字段名称与对象成员属性名称要一一对应并且要符合命名规范
            // 例如：对象成员名称为"userName"，将与名称为"user_name"的字段对应
            wrapper.toObject(new User());

            // 返回值将决定遍历是否继续执行
            return true;
        }
    });

    // 采用指定的步长进行数据遍历，此处step=2
    ResultSetHelper.bind(_results).forEach(2, new ResultSetHelper.ItemHandler() {
        public boolean handle(ResultSetHelper.ItemWrapper wrapper, int row) throws Exception {
            // 代码略......
            return true;
        }
    });

Sample code two: Use custom IResultSetHandler to process result set data; 

    // 自定义JavaBean对象，用于封装多表关联的结果集的记录
    public class CustomUser {

        private String id;

        private String username;

        private Double money;

        // 忽略Getter和Setter方法
    }

    // 修改示例一的代码，将结果集中的每一条记录转换成自定义的CustomUser对象
    IResultSet<CustomUser> _results = JDBC.get().openSession(new ISessionExecutor<IResultSet<CustomUser>>() {
        public IResultSet<CustomUser> execute(ISession session) throws Exception {
            Select _uSelect = Select.create(User.class, "u")
                    .join(Join.left(UserExt.TABLE_NAME).alias("ue")
                            .on(Cond.create()
                                    .opt("u", User.FIELDS.ID, Cond.OPT.EQ, "ue", UserExt.FIELDS.UID)))
                    .field(Fields.create()
                            .add("u", User.FIELDS.ID, "id")
                            .add("u", User.FIELDS.USER_NAME, "username")
                            .add("ue", UserExt.FIELDS.MONEY, "money"));

            // 通过实现IResultSetHandler接口实现结果集的自定义处理
            return session.find(SQL.create(_uSelect), new IResultSetHandler<CustomUser>() {
                public List<CustomUser> handle(ResultSet resultSet) throws Exception {
                    List<CustomUser> _results = new ArrayList<CustomUser>();
                    while (resultSet.next()) {
                        CustomUser _cUser = new CustomUser();
                        _cUser.setId(resultSet.getString("id"));
                        _cUser.setUsername(resultSet.getString("username"));
                        _cUser.setMoney(resultSet.getDouble("money"));
                        //
                        _results.add(_cUser);
                    }
                    return _results;
                }
            });
        }
    });