Java Code Examples for org.nutz.dao.sql.Sql

The following examples show how to use org.nutz.dao.sql.Sql. These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source Project: NutzSite   Source File: MenuServiceImpl.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * 查询用户菜单
 *
 * @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);
}
 
Example 2
Source Project: NutzSite   Source File: GenServiceImpl.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 根据表名称查询信息
 *
 * @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);
}
 
Example 3
Source Project: NutzSite   Source File: GenServiceImpl.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 根据表名称查询列信息
 *
 * @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);
}
 
Example 4
Source Project: NutzSite   Source File: GenServiceImpl.java    License: Apache License 2.0 5 votes vote down vote up
/**
     * 查询数据库表信息
     *
     * @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());
    }
 
Example 5
Source Project: NutzSite   Source File: MenuServiceImpl.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * 查询用户权限
 *
 * @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);
}
 
Example 6
Source Project: nutzwx   Source File: DaoAccessTokenStore.java    License: Apache License 2.0 5 votes vote down vote up
@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;
}
 
Example 7
Source Project: nutzwx   Source File: DaoAccessTokenStore.java    License: Apache License 2.0 5 votes vote down vote up
@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);
}