记录功能实现方法

一、用户活跃统计

使用Spring拦截器+redis指令setBit+countBit
拦截器：
使用Spring拦截器
1.实现HandlerInterceptor重写里面三个方法
2.继承HandlerInterceptorAdapter重写里面三个方法

定时任务：
使用的是Spring定时任务，启动类添加@Scheduled注解
凌晨12点记录前一天用户活跃数进数据库

注意事项：
如果查询当天活跃数，是从redis中获取
（做判断去判定指定结束时间是否为当天时间）
使用RedisTemplate

//setBit
redisTemplate.opsForValue().setBit(key,offset,value);
//bitCount
redisTemplate.execute((RedisCallback<Long>) con -> con.bitCount(key.getBytes()));

setBit后key存储到redis中乱码，bitCount统计时为空

使用StringRedisTemplate或Jedis Key不会乱码

stringRedisTemplate.opsForValue().setBit(key,offset,value);
stringRedisTemplate.execute((RedisCallback<Long>) con -> con.bitCount(key.getBytes()));

jedis.setbit(key,offset,value);
jedis.bitcount(key）;

@Component
public class UserInterceptor extends HandlerInterceptorAdapter {

    @Resource
    private StringRedisTemplate stringRedisTemplate;

    // 在业务处理器处理请求之前被调用
    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception{

        return true;
    }
    // 在业务处理器处理请求完成之后，生成视图之前执行
    @Override
    public void postHandle(HttpServletRequest request, HttpServletResponse response, Object handler, ModelAndView modelAndView)
            throws Exception{
    }
    // 在DispatcherServlet完全处理完请求之后被调用，可用于清理资源
    @Override
    public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex)
            throws Exception{
        String authorization = request.getHeader("Authorization");
        if(authorization!=null){
            String token = authorization.substring(8);
            //验证token是否失效
            if(!JwtTokenUtil.checkToken(token)){
                String date = DateUtils.format(new Date());
                Claims claims = JwtTokenUtil.parseToken(token,"HX_SECRET");
                //System.out.println(date);
                String key="userlogin:"+date;
                long uid = Long.parseLong(claims.get("UID").toString());
                Boolean bit = stringRedisTemplate.opsForValue().getBit(key, uid);
                if(bit){
                    System.out.println("用户已签到");
                }else {
                    stringRedisTemplate.opsForValue().setBit(key,uid,true);
                    System.out.println("用户登录签到成功----"+uid);
                }

            }

        }
    }
}

//检验token是否有效，无效返回true
public static Boolean checkToken(String token){

        Claims claims = Jwts.parser()
                .setSigningKey(APPSECRET)//签名
                //getBody会获取用户的一些信息
                .parseClaimsJws(token)
                .getBody();
        long exp = Long.parseLong(claims.get("exp").toString());
        long expDate = new Date(exp * 1000L).getTime();
        long nowDate = System.currentTimeMillis();
        return expDate<nowDate;
    }

拦截器配置类
拦截器执行在bean实例化前执行的，那么我们就让拦截器执行的时候实例化拦截器Bean，在拦截器配置类里面先实例化拦截器
不然redis会报空指针异常

@Configuration
public class WebAppConfigurer implements WebMvcConfigurer {

    @Bean
    public UserInterceptor getUserInterceptor(){
        return new UserInterceptor();
    }

    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        // 可添加多个
        registry.addInterceptor(getUserInterceptor()).addPathPatterns("/**");
    }
}

二、订单金额统计

场景：默认返回最近7天订单金额，根据接收起始和结束时间返回数据

//获取指定时间内的日期
    private  static List<String > getBetweenDays(String timeStart,String timeEnd) throws Exception {
        if(StringUtils.isEmpty(timeStart)||StringUtils.isEmpty(timeEnd)){
            return null;
        }
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");

        Date start = df.parse(timeStart);
        Date end = df.parse(timeEnd);
        if(StringUtils.isEmpty(start)||StringUtils.isEmpty(end)){
            return null;
        }
        List<String> result = new ArrayList<>();
        Calendar tempStart = Calendar.getInstance();
        tempStart.setTime(start);

        tempStart.add(Calendar.DAY_OF_YEAR,1);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Calendar tempEnd = Calendar.getInstance();
        tempEnd.setTime(end);
        
        result.add(sdf.format(start));//添加起始时间
        while(tempStart.before(tempEnd)){
            result.add(sdf.format(tempStart.getTime()));
            tempStart.add(Calendar.DAY_OF_YEAR, 1);
        }
        result.add(sdf.format(end));//添加结束时间
        return result;
    }

public Result betweenMoneyCount(@LoginUser LoginUserInfo info,
                                          String timeStart,
                                          String timeEnd) throws Exception {
        //获取所有订单
        List<OmOrder> orderList = orderService.listAll();
        //指定时间内的日期
        if(timeStart==null && timeEnd==null){
            //最近7天订单金额
            ArrayList<CountVO> list=new ArrayList<>();
            for(int i=0;i<7;i++){
                CountVO vo = new CountVO();
                int a=i;
                //Util.addDays()网上找的时间工具类，-1就是前一天
                String date = DateUtils.format(Util.addDays(new Date(), -a));
                //order.getStatus是状态
                List<OmOrder> Order = orderList.stream().filter(order ->
                        DateUtils.format(new Date(order.getCreateTime() * 1000L)).equals(date) &&
                                (order.getStatus() == 1 || order.getStatus() == 2 || order.getStatus() == 3))
                        .collect(Collectors.toList());
                //存放订单金额数组
                List<BigDecimal> bigDecimals = new ArrayList<>();
                //将当天订单金额存放进数组
                Order.forEach(order->{bigDecimals.add(order.getTotalPrice()); });
                //将数组里的金额相加
                BigDecimal Money = bigDecimals.stream().reduce(BigDecimal.ZERO, BigDecimal::add);
                vo.setDatetime(date);
                vo.setCount(""+Money);
                list.add(vo);
            }
            return new Result(true, StatusCode.OK, "统计成功", list, info.getToken());
        }else{
            //指定时间内的日期
            List<String> dateList = getBetweenDays(timeStart, timeEnd);
            ArrayList<CountVO> list = new ArrayList<>();

            for(int i=0;i<dateList.size();i++){
                CountVO vo = new CountVO();
                String date = dateList.get(i);
                List<OmOrder> Order = orderList.stream().filter(order ->
                        DateUtils.format(new Date(order.getCreateTime() * 1000L)).equals(date) &&
                                (order.getStatus() == 1 || order.getStatus() == 2 || order.getStatus() == 3))
                        .collect(Collectors.toList());
                List<BigDecimal> bigDecimals = new ArrayList<>();
                Order.forEach(order->{bigDecimals.add(order.getTotalPrice()); });
                BigDecimal Money = bigDecimals.stream().reduce(BigDecimal.ZERO, BigDecimal::add);
                vo.setDatetime(date);
                vo.setCount(""+Money);
                list.add(vo);
            }
            return new Result(true, StatusCode.OK, "统计成功", list, info.getToken());
        }
    }

三、查询能绑定的设备

需求：
1.设备绑定家庭 一家庭多设备(一对多)
2.房间绑定家庭 一家庭多房间(一对多)
3.家庭下绑定A、B、C三个设备，房间1绑定了A设备，房间2去查询能绑定的设备只会显示B、C设备

//查看房间能绑定的家庭设备
    @Override
    public List<Device> findDeviceByHomeId(Integer roomId) {
        //房间id查询家庭id
        Room room = roomDao.findRoomByRoomId(roomId);
        Integer homeId = room.getHomeId();

        //家庭id查询绑定到家庭的设备ids
        List<Device> deviceList = deviceDao.findDeviceByHomeId(homeId);

        //查看家庭下所有房间
        List<Integer> roomIds = new ArrayList<>();
        List<Room> roomList = roomDao.findRoomListByHomeId(homeId);
        roomList.forEach(room1 -> roomIds.add(room1.getRoomId()));

        //查询房间绑定的设备
        List<DeviceRoom> deviceRoomList = deviceRoomDao.findDeviceListByRoomIds(roomIds);
        if (deviceRoomList != null) {
            List<String> deviceIds = new ArrayList<>();
            deviceRoomList.forEach(deviceRoom -> deviceIds.add(deviceRoom.getDeviceId()));
            List<Device> deviceList1 = deviceDao.findDeviceByDeviceIds(deviceIds);
            //两集合取差集
            deviceList.removeAll(deviceList1);
            return deviceList;
        }

        return deviceList;
    }

//传入房间list集合，查询所有房间绑定的设备

<select id="findDeviceListByRoomIds" resultMap="Map" parameterType="java.util.List">
        select
        <include refid="attrList"/>
        from device_room
        <where>
            <choose>
                <when test="list != null">
                    device_room_state=0 and room_id in
                    <foreach collection="list" item="roomId" open="(" separator="," close=")">
                        #{roomId}
                    </foreach>
                </when>
                <otherwise>
                    1=2
                </otherwise>
            </choose>
        </where>
    </select>

//查询设备，条件为设备id集合

<select id="findDeviceByDeviceIds" resultMap="Map" parameterType="java.util.List">
        select
        <include refid="DeviceList"/>
        from device_info
        <where>
            <choose>
                <when test="list != null and list.size() > 0 ">
                    device_id in
                    <foreach collection="list" item="deviceId" open="(" separator="," close=")">
                        #{deviceId}
                    </foreach>
                </when>
                <otherwise>
                    1=2
                </otherwise>
            </choose>
        </where>
    </select>

四、使用XXL-SSO认证框架下添加用户授权功能

思路：使用aop在方法前去判断该用户是否拥有此方法的使用权限
使用XXL-SSO认证框架，请求头中“authorization”存放token
根据token可以拿到登录用户的uid

切面：

	@Pointcut("@annotation(com.hx.mall.hxmalladmin.annotation.PermissionCheck)")
    public void doFindPermission() {}

    @Before("doFindPermission()")
    public void before(JoinPoint joinPoint) throws Exception {
        RequestAttributes ra = RequestContextHolder.getRequestAttributes();
        ServletRequestAttributes sra = (ServletRequestAttributes) ra;
        HttpServletRequest request = sra.getRequest();
        // 获取请求头
        Enumeration<String> enumeration = request.getHeaderNames();
        String token="";
        while (enumeration.hasMoreElements()) {
            String name = enumeration.nextElement();
            if(name.equals("authorization")){
                //拿到请求头中的token
                token = request.getHeader(name).substring(8);
                //解析token拿到uid
                Claims claims = JwtTokenUtil.parseToken(token, "HX_SECRET");
                String uid = claims.get("UID").toString();
                //根据uid获取该用户拥有的权限集合
                List<String> userPermissions = hxUserRoleDao.findUserPermissions(uid);
                //根据请求头获取url
                String url = request.getRequestURI();
                //根据url查询菜单表获取permissions
                String permissions = hxMenusDao.findPermissionsByUrl(url);
                if(permissions ==null || permissions.length()==0){ throw new ErrorCodeException(ErrorCode.SYSTEM_INTERFACE_ERROR); }
                //判断该permissions是否在集合中
                if(!userPermissions.contains(permissions)){
                    throw new ErrorCodeException(ErrorCode.SYSTEM_PERMISSION_ERROR);
                }
                break;
            }
        }
    }

SQL：

<!-- 根据userId查询拥有的菜单permission集合   -->
	<select id="findUserPermissions" resultType="string">
        select m.permission
        from hx_user_role ur join hx_role_menus rm join hx_menus m
        on ur.role_id=rm.role_id and rm.menu_id=m.id
        where ur.user_id=#{userId}
    </select>

<!-- 查询所有菜单/按钮以及对应的上级菜单名称-->
	<select id="findMenuList" resultType="map">
        <!-- 方案1 (左外关联)-->
<!--        select c.*,p.name parentName-->
<!--        from hx_menus c left join hx_menus p-->
<!--        on c.parent_id=p.id-->
        <!-- 方案2 (内嵌查询)-->
        select c.*,(select name
        from hx_menus p
        where c.parent_id=p.id) parentName
        from hx_menus c
    </select>

<!-- 基于菜单ids查找一级菜单并且其类型为菜单 -->
<!-- 如二级菜单id2位条件 结果上级菜单id url name 子菜单id 2 -->
<!-- 该功能根据登录用户查询拥有的菜单，查询一级菜单实现动态显示 -->
	<resultMap type="com.hx.mall.pojo.dto.HxUserMenuVo" id="UserMenuVo">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="url" column="url"/>
        <collection property="childMenus"
                    ofType="com.hx.mall.pojo.dto.HxUserMenuVo">
            <id property="id" column="cid"/>
            <result property="name" column="cname"/>
            <result property="url" column="curl"/>
        </collection>
    </resultMap>
    
    <!-- 基于菜单id查找一级菜单并且其类型为菜单 -->
    <select id="findMenusByIds" resultMap="UserMenuVo">
        select p.id,p.name,p.url,c.id cid,c.name cname,c.url curl
        from hx_menus p join hx_menus c
        on c.parent_id=p.id
        <where>
            <foreach collection="list"
                     open="("
                     close=")"
                     separator="or"
                     item="menuId">
                c.id=#{menuId}
            </foreach>
            and p.parent_id is null
        </where>
    </select>

@Data
public class HxUserMenuVo {
    private Integer id;
    private String name;
    private String url;
    private List<HxUserMenuVo> childMenus;
}

五、系统后台阿里云OSS多文件上传，获取文件

多文件上传
将MultipartFile数组遍历传入阿里OSS
数据库存入：
1、文件名称
2、后台自定义的文件组id
3、文件的类型
4、文件的大小
5、文件的在阿里云的路劲

@ApiOperation("多文件上传")
@PostMapping("/inputStreams")
public Object inputStream(@RequestParam(value = "files") MultipartFile[] files,
                          @RequestParam("groupId") Long groupId) throws IOException {

    for(MultipartFile file : files){
        byte[] bytes = file.getBytes();
        String originalFilename = file.getOriginalFilename();
        ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(bytes);
        String s = uploadService.inputStreamS(byteArrayInputStream, originalFilename, Long.valueOf(file.getBytes().length),groupId);
        if(s==null){
            return new Result(false, StatusCode.ERROR, "上传失败");
        }
    }
    return new Result(true, StatusCode.OK, "上传成功");
}

@Override
public String inputStreamS(ByteArrayInputStream byteArrayInputStream, String fileName, Long length, Long groupId) {

    try {
        // 后缀名
        //存在OSS里的文件路劲
        String key = judge1(fileName);
        //文件类型名称
        String typeName = fileName.substring(fileName.lastIndexOf(".")).substring(1);
        //System.out.println("获取文件名：" + fileName );
        //System.out.println("文件key：" + key);
        //System.out.println("后缀名"+typeName);
        //上传文件   (上传文件流的形式)
        PutObjectResult putResult = initOss().putObject(ALIYUN_OSS_BUCKET_NAME, key, byteArrayInputStream);
        SimplifiedObjectMeta metadata = initOss().getSimplifiedObjectMeta(ALIYUN_OSS_BUCKET_NAME, key);

        System.out.println(metadata.getSize());
        // 带进度条的上传
//            initOss().putObject(new PutObjectRequest(ALIYUN_OSS_BUCKET_NAME, key, byteArrayInputStream).
//                    <PutObjectRequest>withProgressListener(new PutObjectProgressListener(length)));
        initOss().shutdown();

        //保存到数据库中
        FileInfo info = new FileInfo();
        info.setApi(key);
        info.setName(fileName);
        info.setFileGroupId(groupId);
        info.setUpdateTime(new Date());
        info.setDeleteStatus(0);
        info.setTypeName(typeName);
        FileType fileType = fileTypeMapper.selectByTypeName(typeName);
        if(fileType ==null ){
            FileType type = new FileType();
            type.setName(typeName);
            type.setUpdateTime(new Date());
            type.setDeleteStatus(0);
            fileTypeMapper.insert(type);
            //System.out.println("typeId-------------"+type.getId());
            info.setFileTypeId(type.getId());
        }else {
            info.setFileTypeId(fileType.getId());
            System.out.println("-------存在");
        }
        Map<String, String> map = new HashMap<>();
        map.put("文件类型",typeName);
        DecimalFormat obj = new DecimalFormat("#.00");

        String fileSize=obj.format(metadata.getSize()/1024)+" KB";
        map.put("大小",fileSize);
        info.setAttributeValue(map.toString());
        fileInfoMapper.insert(info);
        return key;

    }catch (Exception e){
        e.printStackTrace();
        return null;
    }
}

获取文件
1、通过文件组id获取文件url
2、通过文件类型获取文件url

//根据文件组id获取所有文件的url
@RequestMapping(value = "/findByGroupId", method = RequestMethod.GET)
@ApiOperation(value = "获取文件组内的所有文件", notes = "通过文件组id搜索文件")
public Object findFileByGroupId(@LoginUser LoginUserInfo userInfo,
                              @RequestParam("groupId") Long groupId,
                              @RequestParam(value = "pageNum", defaultValue = "1") Integer pageNum,
                              @RequestParam(value = "pageSize", defaultValue = "10") Integer pageSize){

    List<String> urlList=fileInfoService.selectByGroupId2(groupId,pageNum,pageSize);
    if(urlList != null && urlList.size() != 0) {
        PageInfo<String> pageInfo = new PageInfo<>(urlList);
        List<URL> list = new ArrayList<>();
        urlList.forEach(api->{list.add(uploadService.view(api));});
        return new Result(true, StatusCode.OK, "分页查询列表成功",
                new PageResult<URL>(pageInfo.getPageNum(), pageInfo.getPageSize(),
                        pageInfo.getPages(), pageInfo.getTotal(), list),
                userInfo.getToken(), false);
    }else {
        return new Result(true, StatusCode.OK, "无列表数据", userInfo.getToken());
    }
}

通过文件路劲获取阿里云该文件的url

@Override
public URL view(String api) {
    /**
     * url超时时间
     */
    Date expirations = new Date(System.currentTimeMillis() +  3600L * 1000 * 24 * 365);
    URL url = initOss().generatePresignedUrl(ALIYUN_OSS_BUCKET_NAME, api, expirations);
    return url;
}

**
* 初始化OSSClient对象
*/
public OSS initOss(){
   String endpoint = "https://oss-cn-beijing.aliyuncs.com";
   String accessKeyId = "xxxxxxxxxxx";
   String accessKeySecret = "xxxxxxxxxxxxxxxx";
   OSS ossClient = new OSSClientBuilder().build(endpoint, accessKeyId, accessKeySecret);
   return ossClient;
}

六、SpringBoot+Mybatis配置多数据源连多个数据库

1、application.xml文件

spring:
	datasource:
    type: com.zaxxer.hikari.HikariDataSource
    primary:
	    driver-class-name: com.mysql.cj.jdbc.Driver
	    url: jdbc:mysql://localhost:3306/hx_fileadmin?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai
	    username: root
	    password: root
	    minimumIdle: 5
	    maximumPoolSize: 15
	    autoCommit: true
	    idleTimeout: 30000
	    poolName: DatebookHikariCP
	    maxLifetime: 1800000
	    connectionTimeout: 30000
	    connectionTestQuery: SELECT 1
	    
    second:
	    driver-class-name: com.mysql.cj.jdbc.Driver
	    url: jdbc:mysql://127.0.0.1:3306/hx_user?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
	    username: root
	    password: root
	    minimumIdle: 5
	    maximumPoolSize: 15
	    autoCommit: true
	    idleTimeout: 30000
	    poolName: DatebookHikariCP
	    maxLifetime: 1800000
	    connectionTimeout: 30000
	    connectionTestQuery: SELECT 1

2、建立连接数据源的配置文件

@Data
@Configuration
@ConfigurationProperties(prefix = "spring.datasource.primary")
@MapperScan(basePackages = "com.example.demo.mapper.primary",
        sqlSessionFactoryRef = "primarySqlSessionFactory")
public class PrimaryDataSourceConfig {
    private String url;
    private String username;
    private String password;
    private String driverClassName;
    private int minimumIdle;
    private int maximumPoolSize;
    private boolean autoCommit;
    private long idleTimeout;
    private String poolName;
    private long maxLifetime;
    private long connectionTimeout;
    private String connectionTestQuery;

    @Bean(name = "primaryDataSource")
    @Primary
    public HikariDataSource dataSource(){
        HikariDataSource hikariDataSource = new HikariDataSource();
        hikariDataSource.setDriverClassName(driverClassName);
        hikariDataSource.setUsername(username);
        hikariDataSource.setPassword(password);
        hikariDataSource.setJdbcUrl(url);
        hikariDataSource.setMinimumIdle(minimumIdle);
        hikariDataSource.setMaximumPoolSize(maximumPoolSize);
        hikariDataSource.setAutoCommit(autoCommit);
        hikariDataSource.setIdleTimeout(idleTimeout);
        hikariDataSource.setPoolName(poolName);
        hikariDataSource.setMaxLifetime(maxLifetime);
        hikariDataSource.setConnectionTimeout(connectionTimeout);
        hikariDataSource.setConnectionTestQuery(connectionTestQuery);
        return hikariDataSource;
    }

    @Primary
    @Bean(name = "primaryTransactionManager")
    public DataSourceTransactionManager transactionManager(){
        return new DataSourceTransactionManager(dataSource());
    }


    @Bean("primarySqlSessionFactory")
    @Primary
    public SqlSessionFactory sqlSessionFactory(
            @Qualifier("primaryDataSource")DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(
                // 设置mybatis的xml所在位置
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/primary/*.xml")
        );
        bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return bean.getObject();
    }
}

@Data
@Configuration
@ConfigurationProperties(prefix = "spring.datasource.second")
@MapperScan(basePackages = "com.example.demo.mapper.second",
        sqlSessionFactoryRef = "secondSqlSessionFactory")
public class SecondDataSourceConfig {
    private String url;
    private String username;
    private String password;
    private String driverClassName;
    private int minimumIdle;
    private int maximumPoolSize;
    private boolean autoCommit;
    private long idleTimeout;
    private String poolName;
    private long maxLifetime;
    private long connectionTimeout;
    private String connectionTestQuery;


    @Bean(name = "secondDataSource")
    public HikariDataSource dataSource(){
        HikariDataSource hikariDataSource = new HikariDataSource();
        hikariDataSource.setDriverClassName(driverClassName);
        hikariDataSource.setUsername(username);
        hikariDataSource.setPassword(password);
        hikariDataSource.setJdbcUrl(url);
        hikariDataSource.setMinimumIdle(minimumIdle);
        hikariDataSource.setMaximumPoolSize(maximumPoolSize);
        hikariDataSource.setAutoCommit(autoCommit);
        hikariDataSource.setIdleTimeout(idleTimeout);
        hikariDataSource.setPoolName(poolName);
        hikariDataSource.setMaxLifetime(maxLifetime);
        hikariDataSource.setConnectionTimeout(connectionTimeout);
        hikariDataSource.setConnectionTestQuery(connectionTestQuery);
        return hikariDataSource;
    }

    @Bean(name = "secondTransactionManager")
    public DataSourceTransactionManager transactionManager(){
        return new DataSourceTransactionManager(dataSource());
    }


    @Bean("secondSqlSessionFactory")
    public SqlSessionFactory sqlSessionFactory(
            @Qualifier("secondDataSource") DataSource dataSource) throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(
                // 设置mybatis的xml所在位置
                new PathMatchingResourcePatternResolver().getResources("classpath*:mapper/second/*.xml")
        );
        bean.getObject().getConfiguration().setMapUnderscoreToCamelCase(true);
        return bean.getObject();
    }
}

七、传入父id遍历所有无限子id

SQL是从网上找的,18可以改成#{id}

select * from (
      select t1.*,
      if(find_in_set(parent_id, @pids) > 0, @pids := concat(@pids, ',', id), 0) as ischild
      from (
           select * from file_group t where t.delete_status = 0 order by parent_id asc,id asc
          ) t1,
          (select @pids := 18) t2
     ) t3 where ischild != 0

八、传入id查询所有父id

SQL是从网上找的

SELECT T2.id, T2.name
FROM (
SELECT
@r AS _id,
(SELECT @r := parent_id FROM file_group WHERE id = _id) AS parent_id,
@l := @l + 1 AS lvl
FROM
(SELECT @r := #{id}, @l := 0) vars,
file_group h
<where>
  @r <![CDATA[<]]><![CDATA[>]]> 0) T1
</where>
JOIN file_group T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC

九、Date比较大小

场景数据库存的是date类型的数据 年月日

	java.sql.Date now = new java.sql.Date(System.currentTimeMillis());
	System.out.println(now);//2022-04-15
	System.out.println(now.getTime());//1650003045553

但是从数据库字段获取2022-04-15数据转为时间戳是1649952000000
当同第一天去做比较时，需要这样去判断

	SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
	java.util.Date now =simpleDateFormat.parse(new Date(System.currentTimeMillis()).toString());
	if (now.compareTo(userMember.getEndTime()) != 1) {
	}