JPA 的封装和使用

BaseSqlQuery baseSqlQuery = (BaseSqlQuery) query;
        baseSqlQuery.orderBy("rand()", null);

@Data
public class SysNationCodeQueryQo extends PageableQo {

    @ApiModelProperty(value = "民族名称", example = "", required = false)
    private String name_Like;


    @ApiModelProperty(value = "民族编号", example = "", required = false)
    private String code;


    @ApiModelProperty(value = "根据指定条件排序", example = "", required = false)
    private LinkedHashMap<String, Sort.Direction> orderByCustomSort;


}

    // 这里第二个参数就是个性化排序的Map
    Specification query = new BaseSqlQuery(qo, qo.getOrderByCustomSort());
    nationCodeManager.findAll(query);

    // 这里第二个参数就是个性化排序的Map，例如自己可以定义为myOrderBy
    Specification query = new BaseSqlQuery(qo, qo.getMyOrderBy(),{"myOrderBy"});
    nationCodeManager.findAll(query);

        SmartSqlQuery query = (SmartSqlQuery) new SmartSqlQuery()
                .multiSelect(Arrays.asList("provinceCityId_Count@num", "cityLevel"))
                .groupBy("cityLevel")
                .orderBy("provinceCityId_Count", Sort.Direction.ASC)
                .setLimit(2).setOffset(1);

        List<ProvinceDo> doList = provinceCityManager.smartFind(query, ProvinceDo.class);

        BaseSqlQuery query = (BaseSqlQuery) new BaseSqlQuery()
                .setLimit(2).setOffset(1).setDataScope(DataScopeEnum.ALL);

        List<SysProvinceCityEntity> doList = provinceCityManager.findAll(query);

        Assert.assertTrue(true);

 selectFieldList.add("code_Count@num");

        SmartSqlQuery query = (SmartSqlQuery<SysNationCodeEntity>)
                new SmartSqlQuery<SysNationCodeEntity>().singleSelect("value_Max")
                        .append("status", CommonConst.COMMON_VALID);
        BigDecimal value = (BigDecimal) nationCodeManager.smartCount(query);

        List<String> multiSelect = Arrays.asList("userUuid_Count", "dateAt");
        List<TrendLongNumDo> doList = viewRecordManager
                .smartFind((SmartSqlQuery<TjsViewRecordEntity>) new SmartSqlQuery<TjsViewRecordEntity>(request)
                        .multiSelect(multiSelect).groupBy("dateAt"), TrendLongNumDo.class);
        doList = FillZeroUtils.fillUpZero(doList, request.getDateAt_Gte(), request.getDateAt_Lte());

        List<String> multiSelect = Arrays.asList("userUuid_CountDistinct", "dateAt");
        List<TrendLongNumDo> doList = viewRecordManager
                .smartFind((SmartSqlQuery<TjsViewRecordEntity>) new SmartSqlQuery<TjsViewRecordEntity>(request)
                        .multiSelect(multiSelect).groupBy("dateAt"), TrendLongNumDo.class);
        doList = FillZeroUtils.fillUpZero(doList, request.getDateAt_Gte(), request.getDateAt_Lte());

        List<String> multiSelect = Arrays.asList("userUuid_Count", "hourAt");
        List<TimeDivideTrendDto> doList = viewRecordManager
                .smartFind((SmartSqlQuery<TjsViewRecordEntity>) new SmartSqlQuery<TjsViewRecordEntity>(request)
                        .multiSelect(multiSelect).groupBy("hourAt"), TimeDivideTrendDto.class);
        doList = TrendUtils.fillUpZero(doList);

        SmartSqlQuery query = new SmartSqlQuery();
        List<String> selectFieldList = new ArrayList<>();
        selectFieldList.add("code_Count@num");
        selectFieldList.add("code");
        selectFieldList.add("createdAt_HourFormat@hourAt");
        query.multiSelect(selectFieldList);
        query.groupBy("createdAt_HourFormat");
        List<NationTrendWithDateTimeDto> result = nationCodeManager.smartFind(query, NationTrendWithDateTimeDto.class);

        SmartSqlQuery query = new SmartSqlQuery();
        List<String> selectFieldList = new ArrayList<>();
        selectFieldList.add("code_Count@num");
        selectFieldList.add("code");
        selectFieldList.add("createdAt_DateFormat@dateAt");
        query.multiSelect(selectFieldList);
        query.groupBy("createdAt_DateFormat");
        List<NationTrendWithDateTimeDto> result = nationCodeManager.smartFind(query, NationTrendWithDateTimeDto.class);

 selectFieldList.add("createdAt_DateFormat@dateAt");

 query.groupBy("createdAt_DateFormat");

@Data
@AllArgsConstructor
public class NationTrendWithDateTimeDto {

    /**
     * 数量
     */
    private Long num;
    /**
     * 名称
     */
    private String code;
    /**
     * 日期，请注意，是String类型
     */
    private String dateAt;
}

        List<String> selectFieldList = new ArrayList<>();
        selectFieldList.add("code_Count@num");
        selectFieldList.add("createdAt_MonthFormat@monthAt");

        SmartSqlQuery smartSqlQuery = (SmartSqlQuery) new SmartSqlQuery().multiSelect(selectFieldList)
                .groupBy("createdAt_MonthFormat");

        List<TrendMonthDo> trendList = nationCodeManager.smartFind(smartSqlQuery, TrendMonthDo.class);

        MonthDurationDo startMonthAt = new MonthDurationDo(2023, 1);
        MonthDurationDo endMonthAt = new MonthDurationDo(2024, 6);

        trendList = FillZeroUtils.fillUpZeroImpl(trendList, TrendMonthDo.class, startMonthAt, endMonthAt);

        log.info("trendList:{}", trendList);

        Assert.assertTrue(true);

@Data
public abstract class TrendBaseDo {

    /**
     * 返回日期信息，用于补零操作
     *
     * @return
     */
    public abstract LocalDate convertToDate();

    /**
     * 初始化数据
     *
     * @return
     */
    public abstract void setZero(LocalDate dateAt);

}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class NationTrendWithDateDto extends TrendBaseDo {

    /**
     * 数量
     */
    private Long num;
    /**
     * 名称
     */
    private String dateAt;

    @Override
    public LocalDate convertToDate() {
        LocalDate date = LocalDate.parse(dateAt);
        return date;
    }

    @Override
    public void setZero(LocalDate dateAt) {
        this.num = 0L;
        this.dateAt = dateAt.toString();
    }
}

        SmartSqlQuery query = new SmartSqlQuery();
        List<String> selectFieldList = new ArrayList<>();
        selectFieldList.add("code_Count@num");
        selectFieldList.add("createdAt_DateFormat@dateAt");
        query.multiSelect(selectFieldList);
        query.groupBy("createdAt_DateFormat");
        //用SmartFind获取的趋势数据
        List<NationTrendWithDateDto> result = nationCodeManager
                .smartFind(query, NationTrendWithDateDto.class);
        log.info("result:{}", JSON.toJSONString(result));
        //执行补零操作
        List<NationTrendWithDateDto> fillUpDto = FillZeroUtils
                .fillUpZeroImpl(result,NationTrendWithDateDto.class, LocalDate.now().minusDays(30),LocalDate.now());
        log.info("fillUpDto:{}", JSON.toJSONString(fillUpDto));

@Data
@AllArgsConstructor
@NoArgsConstructor
public class TableDo {

    /**
     * 名称
     */
    @ApiModelProperty(value = "表名", example = "")
    private String tableName;

    /**
     * 备注
     */
    @ApiModelProperty(value = "备注", example = "")
    private String tableComment;

    /**
     * 库
     */
    @ApiModelProperty(value = "库", example = "")
    private String tableSchema;

    /**
     * 类型
     */
    @ApiModelProperty(value = "类型", example = "")
    private String tableType;

}

        String sql = "SELECT\n" +
                "\tTABLE_NAME as tableName,\n" +
                "\tTABLE_COMMENT as tableComment,\n" +
                "\tTABLE_SCHEMA as tableSchema,\n" +
                "\tTABLE_TYPE as tableType\n" +
                "FROM\n" +
                "\tinformation_schema.TABLES \n" +
                "WHERE\n" +
                "\ttable_type = 'BASE TABLE' \n" +
                "\tAND table_schema = DATABASE ( );";

        List<TableDo> results = schemaManager.nativeQuery(sql, TableDo.class);

Specification query = new BaseSqlQuery()
	.append("status", CommonConstant.COMMON_VALID)
	.append("auditStatus", AuditStatusEnum.APPROVED)
	.append("position_In", positionEnumList);

List<SysUserEntity> userEntityList = userManager.findAll(query);

Specification<QuestionEntity> contentQuery = new BaseSqlQuery<QuestionEntity>()
            .append("content_Like", searchWord);
    Specification<QuestionEntity> labelsQuery = new BaseSqlQuery<QuestionEntity>()
            .append("labels_Like", searchWord);
    Specification<QuestionEntity> allQuery = contentQuery.or(labelsQuery);
    query = query.and(allQuery);

        BaseSqlQuery query = new BaseSqlQuery().append("status", CommonConst.COMMON_VALID);
        query.or(new BaseSqlQuery().append("name", "大连").append("cityCode", "106012"));

        List<SysProvinceCityEntity> entityList = provinceCityManager.findAll(query);

where status = '1' and ( name = '大连' or cityCode = '106012')

        List<SysNationCodeEntity> entityList = nationCodeManager
                .findAll(new BaseSqlQuery().append("code_FindInSet", "11"));

Specification query = new BaseSqlQuery().setDataScope(DataScopeEnum.NOT_DELETE);

Specification query = new BaseSqlQuery().setDataScope(DataScopeEnum.IS_DELETED);

Specification query = new BaseSqlQuery().setDataScope(DataScopeEnum.ALL);

    /**
     * 版本号
     */
    @Version
    @Column(name = "version")
    protected Integer version;

    @Lock(LockModeType.PESSIMISTIC_WRITE)
    public SysUserEntity findByUserId(Integer userId);

    /**
     * 带锁查找用户
     * @return
     */
    public SysUserEntity findByUserIdWithLock(Integer userId){
        SysUserEntity userEntity = repository.findByUserId(userId);
        return userEntity;
    }

        Specification query = new BaseSqlQuery().groupBy("status");

        List<SysNationCodeEntity> entityList = nationCodeManager.findAll(query);
        log.info("entityList:{}", JSON.toJSONString(entityList));

        Assert.assertTrue(true);

List<Integer> idList = new ArrayList<>();
        idList.add(1);
        idList.add(2);
        idList.add(3);

        nationCodeManager.deleteBatch(idList);

public void deleteBatch(List idList, Boolean force) {
        this.getRepository().deleteBatch(idList, force);
    }

package com.qrqy.chengfa.data.demander.group;

import io.swagger.annotations.ApiModelProperty;
import io.swagger.annotations.ApiOperation;
import lombok.Data;

import java.io.Serializable;

/**
 * @author TerryQi
 * @since 2022/8/12 13:56
 */
@Data
public class DemandersJsonDo implements Serializable {

    /**需求方id，即幼儿园id"**/
    @ApiModelProperty(value = "需求方id ，即幼儿园id", example = "")
    private Integer demanderId;


    /**需求方名称，即幼儿园名称"**/
    @ApiModelProperty(value = "需求方名称，即幼儿园名称", example = "")
    private String name;


    /**联系人姓名"**/
    @ApiModelProperty(value = "联系人姓名", example = "")
    private String contactName;


    /**联系人电话"**/
    @ApiModelProperty(value = "联系人电话", example = "")
    private String contactPhoneNumber;


    /**省"**/
    @ApiModelProperty(value = "省", example = "")
    private String province;


    /**市"**/
    @ApiModelProperty(value = "市", example = "")
    private String city;


    /**区"**/
    @ApiModelProperty(value = "区", example = "")
    private String area;


    /**需求方地址"**/
    @ApiModelProperty(value = "需求方地址", example = "")
    private String address;

}

package com.qrqy.chengfa.dao.entity;

import com.fasterxml.jackson.databind.annotation.JsonDeserialize;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.datatype.jsr310.deser.LocalDateTimeDeserializer;
import com.fasterxml.jackson.datatype.jsr310.ser.LocalDateTimeSerializer;
import com.qrqy.chengfa.data.demander.group.DemandersJsonDo;
import com.qrqy.chengfa.enums.purchase.task.PurchaseTaskStatusEnum;
import com.qrqy.developer.common.entity.CommonEntity;
import lombok.Data;
import lombok.EqualsAndHashCode;
import org.hibernate.annotations.Type;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

import javax.persistence.*;
import java.io.Serializable;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;

/**
 * @author : jCoder
 * @date : 2022-08-12 11:16
 */
@Data
@EqualsAndHashCode(callSuper = true)
@Entity
@Table(name = "cf_purchase_sub_task")
@EntityListeners(AuditingEntityListener.class)
public class CfPurchaseSubTaskEntity extends CommonEntity implements Serializable {

    private static final long serialVersionUID = 7406912254492452506L;

    /**
     * PK
     */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "purchase_sub_task_id")
    private Integer purchaseSubTaskId;

    /**
     * 采购任务id
     */
    @Column(name = "purchase_task_id")
    private Integer purchaseTaskId;

    /**
     * 子任务名称
     */
    @Column(name = "sub_task_name")
    private String subTaskName;


    @Column(name = "batch_no")
    private String batchNo;

    /**
     * 需求方分组id
     */
    @Column(name = "demander_group_id")
    private Integer demanderGroupId;

    /**
     * 需求方ids，多个id用逗号分割
     */
    @Column(name = "demander_ids")
    private String demanderIds;


    @Column(name = "demander_group_name")
    private String demanderGroupName;

    @Type(type = "json")
    @Column(name = "demanders_json",columnDefinition = "json")
    private List<DemandersJsonDo> demandersJson;

    /**
     * 报名开始时间
     */
    @JsonDeserialize(using = LocalDateTimeDeserializer.class)
    @JsonSerialize(using = LocalDateTimeSerializer.class)
    @Column(name = "sign_up_start_at")
    private LocalDateTime signUpStartAt;

    /**
     * 报名结束时间
     */
    @JsonDeserialize(using = LocalDateTimeDeserializer.class)
    @JsonSerialize(using = LocalDateTimeSerializer.class)
    @Column(name = "sign_up_end_at")
    private LocalDateTime signUpEndAt;

    /**
     * 任务状态，NOT_PUBLISH：未发布；HAS_PUBLISHED：已发布；FINISHED：已结束
     */
    @Column(name = "task_status")
    @Enumerated(EnumType.STRING)
    private PurchaseTaskStatusEnum taskStatus = PurchaseTaskStatusEnum.NOT_PUBLISH;


    /**
     * 需要商品总数量
     */
    @Column(name = "total_product_num")
    private Integer totalProductNum;

    /**
     * 需要商品总价格
     */
    @Column(name = "total_price")
    private BigDecimal totalPrice;


}

@Data
@MappedSuperclass
public abstract class Common2Entity<ID> {
    /**
     * 状态，0：失效，1：有效，主要考虑兼容性
     */
    @Column(name = "status")
    protected String status = "1";
    /**
     * 备注
     */
    @Column(name = "note")
    protected String note;
    /**
     * 排序
     */
    @Column(name = "sort")
    protected Integer sort = 99;
    /**
     * 删除标记，0：未删除；1：已删除
     */
    @Column(name = "deleted_flag")
    protected String deletedFlag = "0";
    /**
     * 创建用户
     */
    @CreatedBy
    @Column(name = "created_by")
    protected ID createdBy;
    /**
     * 创建时间戳
     */
    @JsonDeserialize(using = LocalDateTimeDeserializer.class)
    @JsonSerialize(using = LocalDateTimeSerializer.class)
    @CreatedDate
    @Column(name = "created_at")
    protected LocalDateTime createdAt;
    /**
     * 更新用户
     */
    @LastModifiedBy
    @Column(name = "updated_by")
    protected ID updatedBy;
    /**
     * 更新时间戳
     */
    @JsonDeserialize(using = LocalDateTimeDeserializer.class)
    @JsonSerialize(using = LocalDateTimeSerializer.class)
    @LastModifiedDate
    @Column(name = "updated_at")
    protected LocalDateTime updatedAt;
    /**
     * 删除用户
     */
    @Column(name = "deleted_by")
    protected ID deletedBy;
    /**
     * 删除时间戳
     */
    @JsonDeserialize(using = LocalDateTimeDeserializer.class)
    @JsonSerialize(using = LocalDateTimeSerializer.class)
    @Column(name = "deleted_at")
    protected LocalDateTime deletedAt;

}

@Data
@EqualsAndHashCode(callSuper = true)
@Entity
@Table(name = "sys_nation_code")
@EntityListeners(AuditingEntityListener.class)
public class SysNationCodeEntity extends Common2Entity<Integer> implements Serializable {

    private static final long serialVersionUID = 432550535284913460L;

    /**
     * PK
     */
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "nation_code_id")
    private Integer nationCodeId;
    /**
     * 民族名称
     */
    @Column(name = "name")
    private String name;
    /**
     * 民族编号
     */
    @Column(name = "code")
    private String code;
    /**
     * 状态，1：有效；0：无效
     */
    @Column(name = "status")
    private String status;

}

@Data
public class UserLoginDo<ID> implements IBaseUserDetails<ID>, Serializable {

    private static final long serialVersionUID = 5900016730059591925L;

    /**
     * 主键
     */
    private ID userId;
    /**
     * 预留id，一般为hash值
     */
    private String uuid;
    /**
     * 昵称
     */
    private String nickName;
    /**
     * 真实名称
     */
    private String realName;
    /**
     * 头像图片
     */
    private String avatar;
    /**
     * 手机号
     */
    private String phoneNumber;
    /**
     * 用户签名
     */
    private String sign;
    /**
     * 用户性别；UNKNOWN：保密；MALE：男性；FEMALE：女性；
     */
    private String gender;
    /**
     * 国家
     */
    private String country;
    /**
     * 省份
     */
    private String province;
    /**
     * 城市
     */
    private String city;
    /**
     * 地址
     */
    private String address;
    /**
     * 区
     */
    private String area;
    /**
     * 生日
     */
    private LocalDate birthday;
    /**
     * 语言
     */
    private String language;

    /**
     * 用户状态
     */
    private String status;

    /**
     * token信息，带Bearer头
     */
    private String token;

    /**
     * token信息，带Bearer头
     */
    private List<RoleDo> roleList;

    /**
     * 授权信息
     */
    private Collection<? extends GrantedAuthority> authorities;

    @Override
    public Collection<? extends GrantedAuthority> getAuthorities() {
        if (roleList==null){
            return new ArrayList<>();
        }
        return roleList.stream().map(role -> new SimpleGrantedAuthority("ROLE_" + role.getRoleId())).collect(Collectors.toList());
    }

    @Override
    public String getPassword() {
        return null;
    }

    @Override
    public String getUsername() {
        return this.realName;
    }

    @Override
    public boolean isAccountNonExpired() {
        return true;
    }

    @Override
    public boolean isAccountNonLocked() {
        return true;
    }

    @Override
    public boolean isCredentialsNonExpired() {
        return true;
    }

    @Override
    public boolean isEnabled() {
        return true;
    }
}

@Component
@Slf4j
public class SpringSecurityAuditorAware implements AuditorAware<Object> {
    @Override
    public Optional<Object> getCurrentAuditor() {
        try {
            Object principal = SecurityContextHolder.getContext().getAuthentication().getPrincipal();
            Object userId;
            if (principal instanceof IBaseUserDetails) {
                userId = ((IBaseUserDetails<Object>) principal).getUserId();
            } else {
                userId = null;
            }
            return Optional.ofNullable(userId);
        } catch (NullPointerException e) {
            return Optional.empty();
        }
    }
}