org.nutz.dao.Sqls Java Examples

/**
 * 查询用户菜单
 *
 * @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);
}
 

/**
 * 根据表名称查询信息
 *
 * @param tableName
 * @return
 */
private TableInfo selectTableByName(String tableName) {
    String sqlstr = "select table_name, table_comment, create_time, update_time from information_schema.tables " +
            "where table_comment <> '' and table_schema = (select database()) and table_name = @tableName";
    Sql sql = Sqls.create(sqlstr);
    sql.params().set("tableName", tableName);
    sql.setCallback(Sqls.callback.entities());
    Entity<TableInfo> entity = dao.getEntity(TableInfo.class);
    sql.setEntity(entity);
    dao.execute(sql);
    return sql.getObject(TableInfo.class);
}
 

/**
 * 根据表名称查询列信息
 *
 * @param tableName 表名称
 * @return 列信息
 */
private List<ColumnInfo> selectTableColumnsByName(String tableName) {
    String sqlstr = "select column_name, data_type, column_comment from information_schema.columns " +
            "where table_name = @tableName and table_schema = (select database()) order by ordinal_position";
    Sql sql = Sqls.create(sqlstr);
    sql.params().set("tableName", tableName);
    sql.setCallback(Sqls.callback.entities());
    Entity<ColumnInfo> entity = dao.getEntity(ColumnInfo.class);
    sql.setEntity(entity);
    dao.execute(sql);
    return sql.getList(ColumnInfo.class);
}
 

/**
     * 查询数据库表信息
     *
     * @param tableName
     * @param tableComment
     * @param pageNumber
     * @param pageSize
     * @return
     */
    @Override
    public TableDataInfo selectTableList(String tableName, String tableComment,
                                         int pageNumber, int pageSize, String orderByColumn, String isAsc) {
        String sqlstr = "select table_name, table_comment, create_time, update_time from information_schema.tables " +
                "where table_comment <> '' and table_schema = (select database()) ";
        if (Strings.isNotBlank(tableName)) {
            sqlstr += "and table_name like @tableName";
        }
        if (Strings.isNotBlank(tableComment)) {
            sqlstr += "and table_comment like @tableComment";
        }
        if (Strings.isNotBlank(orderByColumn) && Strings.isNotBlank(isAsc)) {
            MappingField field =dao.getEntity(TableInfo.class).getField(orderByColumn);
            if(Lang.isNotEmpty(field)){
                sqlstr += " order by " + field.getColumnName() + " " + isAsc;
            }

        }
        Sql sql = Sqls.create(sqlstr);
        sql.params().set("tableName", "%" + tableName + "%");
        sql.params().set("tableComment", "%" + tableComment + "%");
        sql.setCallback(Sqls.callback.entities());
        Entity<TableInfo> entity = dao.getEntity(TableInfo.class);

        Pager pager = dao.createPager(pageNumber, pageSize);
        //记录数需手动设置
        pager.setRecordCount((int) Daos.queryCount(dao, sql));
        sql.setPager(pager);
        sql.setEntity(entity).setCondition(Cnd.wrap(sqlstr));
        dao.execute(sql);
//        return sql.getList(TableInfo.class);
        return new TableDataInfo(sql.getList(TableInfo.class), pager.getRecordCount());
    }
 

/**
 * 查询用户权限
 *
 * @param userId
 * @return
 */
@Override
public List<String> getPermsByUserId(String userId) {
    String sqlstr = " select distinct m.perms 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 " +
            " where ur.user_id = @userId";
    Sql sql = Sqls.create(sqlstr);
    sql.params().set("userId", userId);
    sql.setCallback(Sqls.callback.strList());
    dao().execute(sql);
    return sql.getList(String.class);
}
 

@Override
public WxAccessToken get() {
    Sql sql = Sqls.fetchRecord(fetch);
    if (params != null)
        sql.params().putAll(params);
    dao.execute(sql);
    Record record = sql.getObject(Record.class);
    WxAccessToken at = new WxAccessToken();
    at.setToken(record.getString(tableAccessToken));
    at.setExpires(record.getInt(tableAccessTokenExpires));
    at.setLastCacheTimeMillis(record.getLong(tableAccessTokenLastat));
    return at;
}
 

@Override
public void save(String token, int time, long lastCacheTimeMillis) {
    Sql sql = Sqls.create(update);
    if (params != null)
        sql.params().putAll(params);
    sql.params().set(tableAccessToken, token);
    sql.params().set(tableAccessTokenExpires, time);
    sql.params().set(tableAccessTokenLastat, lastCacheTimeMillis);
    dao.execute(sql);
}
 

public EUser updateRoles(EUser vo) {
	dao().execute(Sqls.create("delete from sys_user_role where userid=" + vo.getId()));
	return _insertRelation(vo, "roles");
}
 

public ERole updateAuthorities(ERole vo) {
	dao().execute(Sqls.create("delete from sys_role_authority where role_id=" + vo.getId()));
	return _insertRelation(vo, "authorities");
}