com.jfinal.plugin.activerecord.Db Java Examples

/**
 * 删除 action
 */
@Before(IdsRequired.class)
public void deleteAction() {
    String ids = getPara("ids").replaceAll(",", "','");
    Db.tx(() -> {
        // 删除角色数据
        String sql = "delete from sys_role where id in ('" + ids + "')";
        Db.update(sql);
        // 删除 角色用户 中间表
        sql = "delete from sys_user_role where sysRoleId in ('" + ids + "')";
        Db.update(sql);
        // 删除角色菜单中间表
        sql = "delete from sys_role_menu where sysRoleId in ('" + ids + "')";
        Db.update(sql);
        // 通知类型 角色 中间表
        sql = "delete from sys_notice_type_sys_role where sysRoleId in ('" + ids + "')";
        Db.update(sql);
        return true;
    });
    renderSuccess(DELETE_SUCCESS);
}
 

@Override
@Cacheable(name = "user_permission", key = "role:#(roleId)", nullCacheEnable = true)
public List<Permission> findPermissionListByRoleId(long roleId) {
    String sql = "select * from role_permission_mapping where role_id = ? ";
    List<Record> rolePermissionRecords = Db.find(sql, roleId);
    if (rolePermissionRecords == null || rolePermissionRecords.isEmpty()) {
        return null;
    }

    List<Permission> permissionList = new ArrayList<>();
    for (Record rolePermissionRecord : rolePermissionRecords) {
        Permission permission = findById(rolePermissionRecord.getLong("permission_id"));
        if (permission != null) {
            permissionList.add(permission);
        }
    }

    return permissionList;
}
 

@Override
public void run() {
    if (countsMap.isEmpty()) {
        return;
    }

    Map<Long, AtomicLong> articleViews = new HashMap<>(countsMap);
    countsMap.clear();

    for (Map.Entry<Long, AtomicLong> entry : articleViews.entrySet()) {
        Db.update("update article set comment_count = comment_count + "
                + entry.getValue().get()
                + " where id = ? ", entry.getKey());
        Aop.get(ArticleService.class).removeCacheById(entry.getKey());
    }
}
 

@Override

    @CachesEvict({
            @CacheEvict(name = "articles", key = "*"),
            @CacheEvict(name = "article-category", key = "#(articleId)"),
    })
    public void doUpdateCategorys(long articleId, Long[] categoryIds) {

        Db.tx(() -> {
            Db.update("delete from article_category_mapping where article_id = ?", articleId);

            if (categoryIds != null && categoryIds.length > 0) {
                List<Record> records = new ArrayList<>();
                for (long categoryId : categoryIds) {
                    Record record = new Record();
                    record.set("article_id", articleId);
                    record.set("category_id", categoryId);
                    records.add(record);
                }
                Db.batchSave("article_category_mapping", records, records.size());
            }

            return true;
        });
    }
 

@Override
@Cacheable(name = "user_permission", key = "user_permissions:#(userId)", nullCacheEnable = true)
public List<Permission> findPermissionListByUserId(long userId) {

    Set<Permission> permissions = new HashSet<>();
    String sql = "select * from user_role_mapping where user_id = ? ";
    List<Record> userRoleRecords = Db.find(sql, userId);
    if (userRoleRecords != null) {
        for (Record userRoleRecord : userRoleRecords) {
            List<Permission> rolePermissions = findPermissionListByRoleId(userRoleRecord.getLong("role_id"));
            if (rolePermissions != null) {
                permissions.addAll(rolePermissions);
            }
        }
    }

    return new ArrayList<>(permissions);
}
 

@Override
public void run() {
    if (countsMap.isEmpty()) {
        return;
    }

    Map<Long, AtomicLong> articleViews = new HashMap<>(countsMap);
    countsMap.clear();

    for (Map.Entry<Long, AtomicLong> entry : articleViews.entrySet()) {
        Db.update("update article set view_count = view_count + "
                + entry.getValue().get()
                + " where id = ? ", entry.getKey());
        Aop.get(ArticleService.class).removeCacheById(entry.getKey());
    }
}
 

@Override
public void intercept(final Invocation inv) {
	Db.tx(new IAtom() {
		@Override
		public boolean run() throws SQLException {
			try {
				inv.invoke();
			} catch (Exception e) {
				if (inv.getTarget() instanceof ControllerExt) {
					ControllerExt controller = inv.getTarget();
					controller.onExceptionError(e);
				}
				return false;
			}
			return true;
		}
	});
}
 

private static int[] batchSave(List<? extends ModelExt<?>> models, int batchSize) {
	ModelExt<?> m = models.get(0);
	Map<String, Object> attrs = m.attrs();
	Table table = m.table();

	StringBuilder sql = new StringBuilder();
	List<Object> paras = Lists.newArrayList();

	DbKit.getConfig().getDialect().forModelSave(table, attrs, sql, paras);

	Object[][] batchPara = new Object[models.size()][attrs.size()];

	for (int i = 0; i < models.size(); i++) {
		int j = 0;
		for (String key : attrs.keySet()) {
			batchPara[i][j++] = models.get(i).get(key);
		}
	}
	return Db.batch(sql.toString(), batchPara, batchSize);
}
 

@Override
public void doUpdateTags(long userId, Long[] tagIds) {
    Db.tx(() -> {
        Db.update("delete from user_tag_mapping where user_id = ?", userId);

        if (tagIds != null && tagIds.length > 0) {
            List<Record> records = new ArrayList<>();
            for (long tagId : tagIds) {
                Record record = new Record();
                record.set("user_id", userId);
                record.set("tag_id", tagId);
                records.add(record);
            }
            Db.batchSave("user_tag_mapping", records, records.size());
        }

        return true;
    });
}
 

@Override
@JFinalTx
public void saveOrUpdate(Long roleId, List<Long> menuIdList) {
	//先删除角色与菜单关系
	deleteBatch(new Long[]{roleId});

	if(menuIdList.size() == 0){
		return ;
	}

	//保存角色与菜单关系
	List<SysRoleMenu> list = new ArrayList<>(menuIdList.size());
	for(Long menuId : menuIdList){
		SysRoleMenu sysRoleMenu = new SysRoleMenu();
		sysRoleMenu.setMenuId(menuId);
		sysRoleMenu.setRoleId(roleId);

		list.add(sysRoleMenu);
	}

	Db.batchSave(list, list.size());
}
 

/**
 * 角色配置权限 菜单更新
 */
@Before(Tx.class)
public void menuTreeUpdate() {
    String roleId = get("roleId");
    String menuIds = get("menuIds");
    if (StringUtils.isEmpty(roleId)) {
        renderFail("roleId 参数不可为空.");
        return;
    }
    // 删除 角色原有菜单
    String deleteSql = "delete from  sys_role_menu where sysRoleId = ?";
    Db.update(deleteSql, roleId);

    // 添加 角色新菜单
    if (StringUtils.notEmpty(menuIds)) {
        String[] menuIdAry = menuIds.split(",");
        for (String menuId : menuIdAry) {
            SysRoleMenu sysRoleMenu = new SysRoleMenu();
            sysRoleMenu.setSysRoleId(roleId).setSysMenuId(menuId)
                    .setCreater(WebUtils.getSessionUsername(this))
                    .setCreateTime(new Date())
                    .save();
        }
    }
    renderSuccess("菜单权限操作成功");
}
 

@Override
@CachesEvict({
        @CacheEvict(name = "user_role", key = "*"),
        @CacheEvict(name = "user_permission", key = "*")
})
public boolean doResetUserRoles(long userId, Long... RoleIds) {
    if (RoleIds == null || RoleIds.length == 0) {
        return Db.delete("delete from user_role_mapping where user_id = ? ", userId) > 0;
    }

    return Db.tx(() -> {
        Db.delete("delete from user_role_mapping where user_id = ? ", userId);

        List<Record> records = new ArrayList<>();
        for (Long roleId : RoleIds) {
            Record record = new Record();
            record.set("user_id", userId);
            record.set("role_id", roleId);
            records.add(record);
        }

        Db.batchSave("user_role_mapping", records, records.size());

        return true;
    });
}
 

/**
 * 删除 action
 */
@Before({IdsRequired.class, Tx.class})
public void deleteAction() {
    String ids = getPara("ids").replaceAll(",", "','");
    String deleteSql;

    // 删从表
    deleteSql = "delete from ex_staff_education where exStaffId in ('" + ids + "')";
    Db.update(deleteSql);
    deleteSql = "delete from ex_staff_experience where exStaffId in ('" + ids + "')";
    Db.update(deleteSql);
    deleteSql = "delete from ex_staff_family where exStaffId in ('" + ids + "')";
    Db.update(deleteSql);
    // 删主表
    deleteSql = "delete from ex_staff where id in ( '" + ids + "' ) ";
    Db.update(deleteSql);

    renderSuccess(DELETE_SUCCESS);
}
 

/**
 * 批量删除
 */
@RequirePermission("sysVisitLog:delete")
@Before(IdsRequired.class)
public void deleteAction() {
    String ids = getPara("ids").replaceAll(",", "','");
    String sql = "delete from sys_visit_log where  id in ('" + ids + "')";
    int number = Db.update(sql);

    // 发送系统通知
    String noticeTypeCode = "delVisitLog";
    Map<String, Object> params = new HashMap<>();
    params.put("username", WebUtils.getSessionUsername(this));
    params.put("datetime", new DateTime().toString("yyyy-MM-dd HH:mm:ss"));
    params.put("number", number);
    SysNoticeService service = Duang.duang(SysNoticeService.class);
    service.sendNotice(noticeTypeCode, params);

    renderSuccess(DELETE_SUCCESS);
}
 

@Override
public void run() {
    if (countsMap.isEmpty()) {
        return;
    }

    Map<Long, AtomicLong> pageViews = new HashMap<>(countsMap);
    countsMap.clear();

    for (Map.Entry<Long, AtomicLong> entry : pageViews.entrySet()) {
        Db.update("update single_page set view_count = view_count + "
                + entry.getValue().get()
                + " where id = ? ", entry.getKey());
        Aop.get(SinglePageService.class).deleteCacheById(entry.getKey());
    }
}
 

/**
 * 查询某节点子孙树节点
 * @param rootId 根id
 * @param tableName 数据表名
 * @param idFieldName id 字段
 * @param pidFieldName pid 字段名
 * @return
 */
public static String getSonTreeIds(String rootId, String tableName, String idFieldName, String pidFieldName) {
    String sTemp = "$";
    String sTempChd = rootId;
    Record recordTemp;
    while (sTempChd != null) {
        sTemp = sTemp.concat(",").concat(sTempChd);
        recordTemp = Db.findFirst("SELECT group_concat(" + idFieldName + ") as sTempChd  FROM " + tableName + " where FIND_IN_SET(" + pidFieldName + ",'" + sTempChd + "')>0;");
        if (recordTemp == null) {
            sTempChd = null;
        } else {
            sTempChd = recordTemp.getStr("sTempChd");
        }
    }
    return sTemp.replaceAll("\\$,", "");
}
 

@Override
public int updateBatch(Long[] jobIds, int status){
	Map<String, Object> map = new HashMap<>();
	map.put("list", jobIds);
	map.put("status", status);
    List<ScheduleJob> scheduleJobList = new ArrayList<>();
    for (Long jobId : jobIds) {
        ScheduleJob scheduleJob = new ScheduleJob();
        scheduleJob.setJobId(jobId);
        scheduleJob.setStatus(status);
        scheduleJobList.add(scheduleJob);
    }

    Db.batchUpdate(scheduleJobList, scheduleJobList.size());
	return 1;
}
 

public boolean execute() {
	return Db.tx(new IAtom() {
		@Override
		public boolean run() throws SQLException {
			try {
				Tx.this.sql();
			} catch (Exception e) {
				Tx.this.error(e);
				return false;
			}
			return true;
		}
	});
}
 

/**
 * 获得 未读消息数量
 */
public void noticeUnreadCount() {
    SysUser sysUser = WebUtils.getSysUser(this);
    String sql = " select count(1) as unreadCount from sys_notice_detail where receiver = ? and hasRead !='Y' ";
    Record record = Db.findFirst(sql, sysUser.getId());
    Ret ret = Ret.create().setOk().set("unreadCount", record == null ? 0 : record.get("unreadCount"));
    renderJson(ret);
}
 

/**
 * delete
 */
@Before(IdsRequired.class)
public void deleteAction() {
    String ids = get("ids").replaceAll(",", "','");
    Db.tx(() -> {
        // 修改删除标志
        String sql = "update sys_user set delFlag = 'X' where id in ('" + ids + "')";
        Db.update(sql);
        return true;
    });
    renderSuccess(DELETE_SUCCESS);
}
 

private void build() {
    Long articleCount = Db.queryLong("select count(*) from article where `status` = ? ", Article.STATUS_NORMAL);
    if (articleCount != null && articleCount > 0) {
        int totalPage = (int) ((articleCount + 100) / 100);
        for (int i = 1; i <= totalPage; i++) {
            String name = "article_" + i;
            SitemapManager.me().addProvider(Aop.inject(new ArticleSitemapProvider(name, i)));
        }
    }
}
 

/**
 * 当前用户通知全部设置为已读
 */
public void noticeSetAllRead() {
    SysUser sysUser = WebUtils.getSysUser(this);
    SqlPara sqlPara = new SqlPara();
    String sql = "update sys_notice_detail set hasRead = 'Y' , readTime = ? where receiver = ? and hasRead = 'N' ";
    sqlPara.setSql(sql).addPara(new Date()).addPara(sysUser.getId());
    Db.update(sqlPara);
    renderSuccess("设置 全部已读 操作成功");
}
 

public void databaseReady() {
	Db.update("update news set news_time=?",new Date());
	for(int id=1;id<8;id++) {
		Db.update("update users set latest_log_time=? where id=?",RecommendKit.getInRecTimestamp(25+id),id);
	}
	Db.update("update newslogs set view_time=?",new Date());
	
	
}
 

@Override
public BigDecimal queryPayoutAmount(Long userId) {
    return Db.queryBigDecimal("select sum(change_amount) from user_amount_statement where user_id = ? and change_amount < 0  and action  = ? and created > ?"
            , userId
            , UserAmountStatement.ACTION_PAYOUT
            , DateUtils.truncate(new Date(), Calendar.MONTH));
}
 

/**
 * 删除 sysDictGroup
 */
@Before(IdsRequired.class)
public void deleteGroupAction() {
    // 设置删除标志
    String ids = getPara("ids").replaceAll(",", "','");
    Db.tx(() -> {
        String sql = "update sys_dict set delFlag = 'X' where groupCode in (select groupCode from sys_dict_group where id in ('" + ids + "'))";
        Db.update(sql);
        sql = "update sys_dict_group set delFlag = 'X' where id in ('" + ids + "')";
        Db.update(sql);
        return true;
    });
    renderSuccess(DELETE_SUCCESS);
}
 

public boolean updateByKV(String name, Object value) {
    if (Db.queryInt("select siteId from " + TABLE_NAME + " where name=?", name) != null) {
        Db.update("update " + TABLE_NAME + " set value=? where name=?", value, name);
    } else {
        Db.update("insert " + TABLE_NAME + "(`value`,`name`) value(?,?)", value, name);
    }
    return true;
}
 

@Override
public void run() {
    if (utmList.isEmpty()) {
        return;
    }

    List<Utm> tempUtmList = new ArrayList<>(utmList);
    utmList.clear();

    Db.batchSave(tempUtmList, 1000);

}
 

/**
 * 删除 sysDict
 */
@Before(IdsRequired.class)
public void deleteDictAction() {
    String ids = getPara("ids").replaceAll(",", "','");
    String sql = "update sys_dict set delFlag = 'X' where id in ('" + ids + "')";
    Db.update(sql);
    renderSuccess(DELETE_SUCCESS);
}
 

@Before(IdsRequired.class)
public void deleteTaskLogAction() {
    String ids = getPara("ids").replaceAll(",", "','");
    String sql = "delete from sys_task_log where id in ('" + ids + "')";
    Db.update(sql);
    renderSuccess(DELETE_SUCCESS);
}
 

@Override
public void run() {
    if (countsMap.isEmpty()) {
        return;
    }

    Map<Long, AtomicLong> articleViews = new HashMap<>(countsMap);
    countsMap.clear();

    for (Map.Entry<Long, AtomicLong> entry : articleViews.entrySet()) {
        Db.update("update product_comment set reply_count = reply_count + "  + entry.getValue().get() + " where id = ? ", entry.getKey());
        Aop.get(ProductCommentService.class).deleteCacheById(entry.getKey());
    }
}