NutzCN Logo
问答 打印的sql能查到数据, 代码里就是获取不到数据
发布于 25天前 作者 qq_157e5a0c 108 次浏览 复制 上一个帖子 下一个帖子
标签:

看日志打印的sql是

select count(1) from (SELECT uid, app_name, chat_name, MAX(create_time) AS create_time  FROM device_chat_log  WHERE app_name='zkINf/arnJ+tiVLDURrp9w==' GROUP BY uid,app_name,chat_name )as _nutz_tmp_74b30aj838i1arussoe8j9rgdn

这个sql放到sql工具执行一点问题也没有,但是在代码里就是没返回数据, 也没报错,不知道为啥

为方便测试,提供源sql

DROP TABLE IF EXISTS `device_chat_log`;
CREATE TABLE `device_chat_log`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `tenant_id` bigint NULL DEFAULT NULL,
  `user_id` bigint NULL DEFAULT NULL,
  `uid` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `app_name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `contacter` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `send` tinyint(1) NULL DEFAULT NULL,
  `content` varchar(3500) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `create_time` datetime NULL DEFAULT NULL,
  `staff_id` bigint NULL DEFAULT NULL,
  `msg_type` int NULL DEFAULT NULL,
  `friend_uid` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `chat_name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `send_name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `dept_id` bigint NULL DEFAULT NULL,
  `staff_name` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `staff_mobile` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `amount` bigint NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `uk_uid`(`uid` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 19 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of device_chat_log
-- ----------------------------
INSERT INTO `device_chat_log` VALUES (28, 1570292599879630848, NULL, '213124124122421', 'zkINf/arnJ+tiVLDURrp9w==', NULL, 1, 'ONTbgtuEUu7VslPi5ahkDoNH6izcI4BBt8A16WI2mJg=', '2022-10-23 10:10:10', 17, 100, NULL, 'wwQB5enxuuGM13EEBL22TQ==', 'wwQB5enxuuGM13EEBL22TQ==', 6, 'mkTjF1YHE+df2PuWlnlSZw==', 's/SzlVd5q/ka38ctJX7gSQ==', 0);
INSERT INTO `device_chat_log` VALUES (29, 1570292599879630848, NULL, '213124124122421', 'zkINf/arnJ+tiVLDURrp9w==', NULL, 0, '/Z2H9nNyb86ayRRJmdxEcFpwiuBb7cQDR9kxXeN/d7A=', '2022-10-24 10:10:10', 17, 100, NULL, 'wwQB5enxuuGM13EEBL22TQ==', 'wwQB5enxuuGM13EEBL22TQ==', 6, 'mkTjF1YHE+df2PuWlnlSZw==', 's/SzlVd5q/ka38ctJX7gSQ==', 0);
INSERT INTO `device_chat_log` VALUES (30, 1570292599879630848, NULL, '213124124122421', 'zkINf/arnJ+tiVLDURrp9w==', NULL, 1, 'Ero/P0HvQ9lp4gGTZIsioRsXI03WLeUDush5x5MeUMk=', '2022-10-24 13:10:10', 17, 100, NULL, 'uhNFKx8gXoSJkzumh2ZFuQ==', 'uhNFKx8gXoSJkzumh2ZFuQ==', 6, 'mkTjF1YHE+df2PuWlnlSZw==', 's/SzlVd5q/ka38ctJX7gSQ==', 0);
INSERT INTO `device_chat_log` VALUES (31, 1570292599879630848, NULL, '213124124122421', 'xk87DZXIASI0IBpVmqv/uA==', NULL, 1, 'q7pV+7OimuAUtmRSIfuxtxsXI03WLeUDush5x5MeUMk=', '2022-10-24 12:10:10', 17, 100, NULL, 'mkTjF1YHE+df2PuWlnlSZw==', 'mkTjF1YHE+df2PuWlnlSZw==', 6, 'mkTjF1YHE+df2PuWlnlSZw==', 's/SzlVd5q/ka38ctJX7gSQ==', 0);
INSERT INTO `device_chat_log` VALUES (32, 1570292599879630848, NULL, '213124124122421', 'xk87DZXIASI0IBpVmqv/uA==', NULL, 1, 'qBGpRq8CBjUMhSxoSp1OIBsXI03WLeUDush5x5MeUMk=', '2022-10-24 15:10:10', 17, 100, NULL, 'PV6jXsez1BhDPadbkaufHQ==', 'PV6jXsez1BhDPadbkaufHQ==', 6, 'mkTjF1YHE+df2PuWlnlSZw==', 's/SzlVd5q/ka38ctJX7gSQ==', 0);
INSERT INTO `device_chat_log` VALUES (33, 1570292599879630848, NULL, '213124124122421', 'zkINf/arnJ+tiVLDURrp9w==', NULL, 1, NULL, '2022-10-24 15:10:10', 17, 1400, NULL, 'PV6jXsez1BhDPadbkaufHQ==', 'PV6jXsez1BhDPadbkaufHQ==', 6, 'mkTjF1YHE+df2PuWlnlSZw==', 's/SzlVd5q/ka38ctJX7gSQ==', 10);
INSERT INTO `device_chat_log` VALUES (34, 1570292599879630848, NULL, '213124124122421', 'zkINf/arnJ+tiVLDURrp9w==', NULL, 1, NULL, '2022-10-24 15:10:10', 17, 1600, NULL, 'PV6jXsez1BhDPadbkaufHQ==', 'PV6jXsez1BhDPadbkaufHQ==', 6, 'mkTjF1YHE+df2PuWlnlSZw==', 's/SzlVd5q/ka38ctJX7gSQ==', 20);
INSERT INTO `device_chat_log` VALUES (35, 1570292599879630848, NULL, '213124124122421', 'zkINf/arnJ+tiVLDURrp9w==', NULL, 1, NULL, '2022-10-24 15:10:10', 17, 1700, NULL, 'PV6jXsez1BhDPadbkaufHQ==', 'PV6jXsez1BhDPadbkaufHQ==', 6, 'mkTjF1YHE+df2PuWlnlSZw==', 's/SzlVd5q/ka38ctJX7gSQ==', 30);
INSERT INTO `device_chat_log` VALUES (36, 1570292599879630848, NULL, '213124124122421', 'zkINf/arnJ+tiVLDURrp9w==', NULL, 1, NULL, '2022-10-24 15:10:10', 17, 1400, NULL, 'PV6jXsez1BhDPadbkaufHQ==', 'PV6jXsez1BhDPadbkaufHQ==', 6, 'mkTjF1YHE+df2PuWlnlSZw==', 's/SzlVd5q/ka38ctJX7gSQ==', 5);

源代码如下

public class Test {

    public static void main(String[] args) {
        //开启驼峰下划线转换
        Daos.FORCE_HUMP_COLUMN_NAME = true;

        // 创建一个数据源
        SimpleDataSource dataSource = new SimpleDataSource();
        dataSource.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/tenant_jsolo?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");

        // 创建一个NutDao实例,在真实项目中, NutDao通常由ioc托管, 使用注入的方式获得.
        Dao dao = new NutDao(dataSource);

        SqlTemplate sqlTemplate = new SqlTemplate(dao);

        Cnd cnd = Cnd.NEW();
        cnd.andEX(ChatLog.Fields.appName, "=", "zkINf/arnJ+tiVLDURrp9w==");
        cnd.groupBy(ChatLog.Fields.uid, ChatLog.Fields.appName, ChatLog.Fields.chatName);

        //设置下查询的表,以便能自动转换查询字段
        Entity<ChatLog> entity = dao.getEntityHolder().getEntity(ChatLog.class);
        Pojo pojo = dao.pojoMaker().makeQuery(entity);
        cnd.setPojo(pojo);

        String sqlStr = "SELECT uid, app_name, chat_name, MAX(create_time) AS create_time  FROM device_chat_log $condition";
        Sql sql = Sqls.create(sqlStr).setCondition(cnd);

        PageResultVO<ChatVO> chatVOPageResultVO = selectPage(dao, ChatVO.class, sql, 1, 100);
        System.out.println(chatVOPageResultVO);
    }

    /**
     * @param targetClass
     * @return
     */
    protected static <P> PageResultVO<P> selectPage(Dao dao, Class<P> targetClass, Sql sql, int pageNumber, int pageSize) {
        Pager pager = dao.createPager(pageNumber, pageSize);
        sql.setPager(pager);
        //先查总共多少条,是0直接返回了
        pager.setRecordCount((int) Daos.queryCount(dao, sql));// 记录数需手动设置

        List<P> list = null;
        if(Number.class.isAssignableFrom(targetClass) || targetClass.equals(String.class) || targetClass.equals(Date.class) || targetClass.isEnum()){
            //简单对象list
            sql.setCallback(new SqlCallback() {
                @Override
                public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                    List<P> list = new ArrayList<P>();
                    while (rs.next()) {
                        P result = Castors.me().castTo(rs.getObject(1), targetClass);
                        list.add(result);
                    }
                    return list;
                }
            });

            dao.execute(sql);
            list = sql.getList(targetClass);
        }else{
            //javabean对象list
            Entity<P> entity = dao.getEntity(targetClass);
            sql.setCallback(new QueryEntityCallback(){
                @Override
                public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                    Entity<?> en = entity;
                    FieldMatcher fmh = sql.getContext().getFieldMatcher();
                    if (null == fmh)
                        sql.getContext().setFieldMatcher(FieldFilter.get(en.getType()));
                    return process(rs, en, sql.getContext());
                }
            });
            dao.execute(sql);
            list = sql.getList(entity.getType());
        }
        return new PageResultVO<>(list, pager);
    }
}

/** * 聊天记录 * @author hanjie.l */ @Data @Table("device_chat_log") @FieldNameConstants @TableMeta("{'mysql-charset':'utf8mb4'}") @TableIndexes({@Index(name="uk_uid", fields={"uid"}, unique = false)}) public class ChatLog implements IEntity<Long> { /** * 主键id */ @Id private Long id; /** * 租户id */ @Column private Long tenantId; /** * 所属员工唯一标识 */ @Column private Long staffId; /** * 所属部门id * {@link Dept#getId()} */ @Column private Long deptId; /** * 所属人员姓名 * {@link Staff#getName()} */ @Column private String staffName; /** * 所属人员手机号 * {@link Staff#getMobile()} */ @Column private String staffMobile; /** * 设备唯一标识 */ @Column @ColDefine(width = 20) private String uid; /** * 消息类型 * {@link org.dimenspace.tenant.constant.MsgType} */ @Column private int msgType; /** * 应用名 */ @Column private String appName; /** * 联系人 */ @Column private String chatName; /** * 联系人 */ @Column private String sendName; /** * true发送(付款) false接收(收款) */ @Column private Boolean send; /** * 内容 */ @Column private String content; /** * 资金往来数额(分) */ @Column private Long amount; /** * 创建时间 */ @Column private Date createTime; } ````

/**
* 会话vo
*/
@View
@Data
public class ChatVO {

@ApiModelProperty("设备唯一id")
private String uid;

@ApiModelProperty("应用名")
private String appName;

@ApiModelProperty("会话名")
private String chatName;

@ApiModelProperty("创建时间")
private Date createTime;

}

```

1 回复

知道问题在哪了,忽略,代码没贴全,自定义adaptor惹的祸

添加回复
请先登陆
回到顶部