看日志打印的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;
}
```