关于select a.*,b.PROJECT_NAME from t_module a left join t_project b on a.PROJECT_ID = b.ID where b.project_name like '%测试模块%'
19 回复
给2个思路
- 先取为Record,然后转Pojo及获取属性
Sql sql = Sqls.queryRecord("select a.*,b.PROJECT_NAME from t_module a left join t_project b on a.PROJECT_ID = b.ID where b.project_name like '%测试模块%'");
dao.execute(sql);
List<Record> list = sql.getList(Record.class);
for (Record re : list) {
ModuleBean m = re.toEntity(dao.getEntity(ModuleBean.class, "a.");
String projectName = re.getString("b.PROJECT_NAME");
// 继续你想做的事
}
- 自定义SqlCallback
final List<XXXX> list = new ArrayList();
Sql sql = Sqls.create("select a.*,b.PROJECT_NAME from t_module a left join t_project b on a.PROJECT_ID = b.ID where b.project_name like '%测试模块%'");
sql.setCallback(new SqlCallback(){
public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
while (rs.next()) {
ModuleBean m = dao.getEntity(ModuleBean.class).getObject(rs, null, "a.");
String projectName = rs.getString("b.PROJECT_NAME");
}
}
});
dao.execute(sql);
@wendal 兽总 刚刚试用了一下您的第一条思路 但是在
ModuleBean m = re.toEntity(dao.getEntity(ModuleBean.class, "a.");
这句话 现在是报错的，然后我去看了一下现在的API是
Entity getEntity(java.lang.Class classOfT)
是不是已经更新过了？
@wendal 兽总，在这两个方法中加入Criteria都不会生效的吗？
Sql sql = Sqls.create("SELECT .....");
Criteria cri = Cnd.cri();
.....
sql.setCondition(cri);
上述的代码中cri的条件没有加入，这个动态的条件应该如何增加呢？难道是只能直接在SQL语句中拼接？
Criteria cri = Cnd.cri();
if (Strings.isNotBlank(moduleName)) {
cri.where().and("module", "=", moduleName);
}
String sqlStatement = "SELECT log.*, admin.nick_name AS nickName FROM gb_admin_operatelog log LEFT JOIN gb_sysadmin admin ON log.sa_id = admin.sa_id"
Pager pager = dao().createPager(curPage, pageSize);
Sql sql = Sqls.queryRecord(sqlStatement);
sql.setPager(pager);
sql.setCondition(cri);
sql.setCallback(callback);
dao().execute(sql);
Console输出的日志内容：
2017-4-18 11:3:24.614 DEBUG [qtp559384732-23] SELECT log.*, admin.nick_name AS nickName FROM gb_admin_operatelog log LEFT JOIN gb_sysadmin admin ON log.sa_id = admin.sa_id LIMIT 0, 10
从日志上来看并没有把cri的条件加进去，这里module是有值的。
少了$condition
String sqlStatement = "SELECT log.*, admin.nick_name AS nickName FROM gb_admin_operatelog log LEFT JOIN gb_sysadmin admin ON log.sa_id = admin.sa_id $condition";
@wendal 呃，这两天还是发现个问题，如果这个关联是有分页查询的情况下，这个动态条件在统计总记录数时怎么办？
String sqlStatement = "SELECT log.*, admin.nick_name AS nickName FROM gb_admin_operatelog log LEFT JOIN gb_sysadmin admin ON log.sa_id = admin.sa_id $condition";
在这段代码中，在$condition条件中会加入表的另名：log和admin, 那个在下面那个代码中怎么处理？ 也是写个自定义的SQL吗？
/**
* EasyUI DataGrid 组件分页方法
* @param sqlStatement SQL语句
* @param curPage 当前页码
* @param pageSize 每页记录
* @param cri 条件
* @param callback 自字义回调方法
* @return 组件数据
*/
public Map<String, Object> easyuiDGPagerWithFetch(String sqlStatement, Integer curPage, Integer pageSize, Criteria cri, SqlCallback callback) {
Map<String, Object> data = new HashMap<>();
List<T> datas = null;
Pager pager = dao().createPager(curPage, pageSize);
try {
// read from https://nutz.cn/yvr/t/v101fe6uu2jo9o8egsp99ujjck
Sql sql = Sqls.queryRecord(sqlStatement);
sql.setPager(pager);
sql.setCondition(cri);
sql.setCallback(callback);
dao().execute(sql);
datas = sql.getList(getEntityClass());
} catch (Exception e) {
logger.errorf("Query %s create easyui data grid failed!!! Conditions: %s",
getEntityClass().getSimpleName(),
cri.toString());
logger.error(e);
}
data.put("rows", datas);
try {
//TODO 如何处理动态条件下的别名操作
data.put("total", dao().count(getEntityClass(), cri));
} catch (Exception e) {
logger.errorf("Count %s total items failed!!!", getEntityClass().getSimpleName());
logger.error(e);
}
return data;
}
@wendal , 这个方法貌似不能满足需求呀，打印出来的语句如下：
select count(1) from (SELECT log.*, admin.nick_name AS nickName FROM operatelog log LEFT JOIN sysadmin admin ON log.sa_id = admin.sa_id WHERE log.create_time BETWEEN '2017-04-16 00:00:00' AND '2017-04-21 23:59:59' ORDER BY log.create_time DESC LIMIT 0, 10)as _nutz_tmp_vhkb1gejb4h36rvcepfj7bjuqi"
也就是这个count的SQL要重新构建啦。
添加回复
请先登陆