自定义SQL
<h1><center>nutz对自定义SQL 友好支持</center></h1>
<p>例如</p>
<pre><code> /**
* 查询用户菜单
*
* @param userId 用户id
* @return 菜单
*/
@Override
public List<Menu> getMenuList(String userId) {
String sqlstr = "select distinct m.id, m.parent_id, m.menu_name, m.url, m.perms , m.menu_type, m.icon, m.order_num, m.create_time " +
"from sys_menu m " +
"left join sys_role_menu rm on m.id = rm.menu_id " +
"left join sys_user_role ur on rm.role_id = ur.role_id " +
"left join sys_role ro on ur.role_id = ro.id " +
"where ur.user_id = @userId and m.menu_type in ('M', 'C') and m.visible = '0' " +
"order by m.order_num";
Sql sql = Sqls.create(sqlstr);
sql.params().set("userId", userId);
sql.setCallback(Sqls.callback.entities());
Entity<Menu> entity = dao().getEntity(Menu.class);
sql.setEntity(entity);
dao().execute(sql);
return sql.getList(Menu.class);
}</code></pre>
<p>同时还支持查询条件中添加SQL语句查询
列如: andInBySql</p>
<pre><code> /**
* 查询用户列表
*/
@At
@Ok("json")
public Object list(@Param("pageNum")Integer pageNum,
@Param("pageSize")Integer pageSize,
@Param("deptId") String deptId,
@Param("loginName") String loginName,
@Param("phonenumber") String phonenumber,
@Param("beginTime") Date beginTime,
@Param("endTime") Date endTime,
@Param("orderByColumn") String orderByColumn,
@Param("isAsc") String isAsc,
HttpServletRequest req) {
Cnd cnd = Cnd.NEW();
if (!Strings.isBlank(loginName)) {
cnd.and("user_name", "like", "%" + loginName + "%");
}
if (!Strings.isBlank(phonenumber)) {
cnd.and("phonenumber", "=", phonenumber);
}
if (Lang.isNotEmpty(beginTime)) {
cnd.and("create_time", ">=", beginTime);
}
if (Lang.isNotEmpty(endTime)) {
cnd.and("create_time", "<=", endTime);
}
if (!Strings.isBlank(deptId)) {
cnd.where().andInBySql("dept_id", "SELECT id FROM sys_dept WHERE FIND_IN_SET ('%s',ancestors)", deptId)
.or("dept_id", "=", deptId);
}
return userService.tableList(pageNum, pageSize, cnd, orderByColumn, isAsc, "dept");
}
</code></pre>
<p>详细文档:<a href="https://nutzam.com/core/dao/customized_sql.html">https://nutzam.com/core/dao/customized_sql.html</a></p>