Java Code Examples for com.jfinal.plugin.activerecord.Db#findFirst()

/**
 * 查询某节点子孙树节点
 * @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("\\$,", "");
}
 

/**
 * Data Count
 */
public Long dataCount() {
	SqlPara sql = SqlpKit.select(this, "count(*) AS cnt");
	Record record = Db.findFirst(sql);
	if (null != record) {
		return record.get("cnt");
	}
	return 0L;
}
 

@Test
    public void converModel() {
        Record record = Db.findFirst("select * from product p where p.sn ='20180715210707'");
        String str = JSON.toJSONString(record.getColumns());
        Product product = JSON.parseObject(str, Product.class);
//        Product product = RecordUtils.converModel(Product.class, record);
        log.info("{}", product);
    }
 

/**
 * 获得 未读消息数量
 */
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);
}
 

/**
 * 通过用户id 查询 角色id
 *
 * @param userId
 * @return
 */
public String findRoleIdsByUserId(String userId) {
    String sql = " select GROUP_CONCAT(c.id) as roleIds" +
            "  from sys_user a, sys_user_role b,sys_role c " +
            "  where a.id = b.sysUserId and b.sysRoleId = c.id  and a.id = ? ";
    Record record = Db.findFirst(sql, userId);
    return record.getStr("roleIds");
}
 

/**
 * 通过用户查询角色编码
 *
 * @param userId
 * @return
 */
public String findRoleCodesByUserId(String userId) {
    String sql = "select GROUP_CONCAT(c.roleCode) as roleCodes" +
            "  from sys_user a, sys_user_role b,sys_role c " +
            "  where a.id = b.sysUserId and b.sysRoleId = c.id  and a.id = ? ";
    Record record = Db.findFirst(sql, userId);
    return record.getStr("roleCodes");
}
 

/**
 * 检查用户账号是否被注册*
 */
@ClearInterceptor
public void checkUser() {
    String loginName = getPara("loginName");
    if (StringUtils.isEmpty(loginName)) {
        renderArgumentError("loginName can not be null");
        return;
    }
    //检查手机号码是否被注册
    boolean exists = Db.findFirst("SELECT * FROM t_user WHERE loginName=?", loginName) != null;
    renderJson(new BaseResponse(exists ? Code.SUCCESS:Code.FAIL, exists ? "registered" : "unregistered"));
}
 

/**
 * 1. 检查是否被注册*
 * 2. 发送短信验证码*
 */
@ClearInterceptor
public void sendCode() {
    String loginName = getPara("loginName");
    if (StringUtils.isEmpty(loginName)) {
        renderArgumentError("loginName can not be null");
        return;
    }

    //检查手机号码有效性
    if (!SMSUtils.isMobileNo(loginName)) {
        renderArgumentError("mobile number is invalid");
        return;
    }

    //检查手机号码是否被注册
    if (Db.findFirst("SELECT * FROM t_user WHERE loginName=?", loginName) != null) {
        renderJson(new BaseResponse(Code.ACCOUNT_EXISTS,"mobile already registered"));
        return;
    }

    String smsCode = SMSUtils.randomSMSCode(4);
    //发送短信验证码
    if (!SMSUtils.sendCode(loginName, smsCode)) {
        renderFailed("sms send failed");
        return;
    }
    
    //保存验证码数据
    RegisterCode registerCode = new RegisterCode()
            .set(RegisterCode.MOBILE, loginName)
            .set(RegisterCode.CODE, smsCode);

    //保存数据
    if (Db.findFirst("SELECT * FROM t_register_code WHERE mobile=?", loginName) == null) {
        registerCode.save();
    } else {
        registerCode.update();
    }
    
    renderJson(new BaseResponse("sms sended"));
    
}
 

/**
 * 用户注册
 */
   @ClearInterceptor()
public void register(){
	//必填信息
	String loginName = getPara("loginName");//登录帐号
       int code = getParaToInt("code", 0);//手机验证码
       int sex = getParaToInt("sex", 0);//性别
       String password = getPara("password");//密码
	String nickName = getPara("nickName");//昵称
   	//头像信息，为空则使用默认头像地址
   	String avatar = getPara("avatar", AppProperty.me().defaultUserAvatar());

       //校验必填项参数
	if(!notNull(Require.me()
               .put(loginName, "loginName can not be null")
               .put(code, "code can not be null")//根据业务需求决定是否使用此字段
               .put(password, "password can not be null")
               .put(nickName, "nickName can not be null"))){
		return;
	}

       //检查账户是否已被注册
       if (Db.findFirst("SELECT * FROM t_user WHERE loginName=?", loginName) != null) {
           renderJson(new BaseResponse(Code.ACCOUNT_EXISTS, "mobile already registered"));
           return;
       }
       
       //检查验证码是否有效, 如果业务不需要，则无需保存此段代码
       if (Db.findFirst("SELECT * FROM t_register_code WHERE mobile=? AND code = ?", loginName, code) == null) {
           renderJson(new BaseResponse(Code.CODE_ERROR,"code is invalid"));
           return;
       }
       
	//保存用户数据
	String userId = RandomUtils.randomCustomUUID();

	new User()
               .set("userId", userId)
               .set(User.LOGIN_NAME, loginName)
	        .set(User.PASSWORD, StringUtils.encodePassword(password, "md5"))
               .set(User.NICK_NAME, nickName)
	        .set(User.CREATION_DATE, DateUtils.getNowTimeStamp())
	        .set(User.SEX, sex)
               .set(User.AVATAR, avatar)
               .save();
	
       //删除验证码记录
       Db.update("DELETE FROM t_register_code WHERE mobile=? AND code = ?", loginName, code);
       
	//返回数据
	renderJson(new BaseResponse("success"));
}