Mybatis-Plus联表查询与数据验证示例-CSDN博客

mybatis-plus联表查询

pom.xml坐标

<!-- mybatis-plus-join -->
<dependency>
    <groupId>com.github.yulichang</groupId>
    <artifactId>mybatis-plus-join</artifactId>
    <version>1.2.4</version>
</dependency>

使用步骤：

新建Mapper(SmsAssetConsumableOutStorageApplyProcessDetailMPJMapper)

@Component(value = "SmsAssetConsumableOutStorageApplyProcessDetailMPJMapper")
public interface SmsAssetConsumableOutStorageApplyProcessDetailMPJMapper extends MPJBaseMapper<SmsAssetConsumableOutStorageApplyProcessDetail> {
}

注入

@Autowired
SmsAssetConsumableOutStorageApplyProcessDetailMPJMapper smsAssetConsumableOutStorageApplyProcessDetailMPJMapper;

使用示例：

//SELECT
//	*
//FROM
//	sms_asset_consumable_in_storage_apply_process_detail
//	LEFT JOIN sms_asset_consumable_in_storage_apply ON sms_asset_consumable_in_storage_apply.id = sms_asset_consumable_in_storage_apply_process_detail.apply_id
//WHERE
//	sms_asset_consumable_in_storage_apply_process_detail.allow_user_id = '1670620062848978945'
//ORDER BY
//	sms_asset_consumable_in_storage_apply.create_time,
//	sms_asset_consumable_in_storage_apply_process_detail.end_time
MPJLambdaWrapper<SmsAssetConsumableInStorageApplyProcessDetail> mpjLambdaWrapper = new MPJLambdaWrapper<>();
mpjLambdaWrapper.selectAll(SmsAssetConsumableInStorageApplyProcessDetail.class)
        .selectAll(SmsAssetConsumableInStorageApply.class)
        .selectAs(SmsAssetConsumableInStorageApply::getCreateTime, "applyCreateTime")
        .selectAs(SmsAssetConsumableInStorageApplyProcessDetail::getId, "detailId")
        .leftJoin(SmsAssetConsumableInStorageApply.class, SmsAssetConsumableInStorageApply::getId, SmsAssetConsumableInStorageApplyProcessDetail::getApplyId)
        .eq(SmsAssetConsumableInStorageApplyProcessDetail::getAllowUserId, userId)
        .orderByDesc(SmsAssetConsumableInStorageApply::getCreateTime)
        .orderByDesc(SmsAssetConsumableInStorageApplyProcessDetail::getEndTime);

数据校验(@Valid 和 @Validated)

pom.xml

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
    <version>xxx</version>
</dependency>

使用案例

package com.zyq.beans;
 
import javax.validation.constraints.NotBlank;
import javax.validation.constraints.NotNull;
 
import org.hibernate.validator.constraints.Length;
import org.hibernate.validator.constraints.Range;
 
/**
 * 员工对象
 * 
 * @author sunnyzyq
 * @since 2019/12/13
 */
@Data
public class Employee {
 
    /** 姓名 */
    @NotBlank(message = "请输入名称")
    @Length(message = "名称不能超过个 {max} 字符", max = 10)
    public String name;
 
    /** 年龄 */
    @NotNull(message = "请输入年龄")
    @Range(message = "年龄范围为 {min} 到 {max} 之间", min = 1, max = 100)
    public Integer age;
    
}

@Valid

在这里插入图片描述

@Validated

首先我们创建一个校验异常捕获类 ValidExceptionHandler ，然后打上 @RestControllerAdvice 注解，该注解表示他会去抓所有 @Controller 标记类的异常，并在异常处理后返回以 JSON 或字符串的格式响应前端。

package com.zyq.config;
 
import org.springframework.validation.BindException;
import org.springframework.web.bind.annotation.ExceptionHandler;
import org.springframework.web.bind.annotation.RestControllerAdvice;
 
@RestControllerAdvice
public class ValidExceptionHandler {
 
    @ExceptionHandler(BindException.class)
    public String validExceptionHandler(BindException exception) {
        return exception.getAllErrors().get(0).getDefaultMessage();
    }
 
}

实体类字段常用注解

在这里插入图片描述

Java实体类的属性类型与数据库表字段类型对应表

Java数据类型	Hibernate数据类型	标准SQL数据类型 (PS:对于不同的DB可能有所差异)
byte、java.lang.Byte	byte	TINYINT
short、java.lang.Short	short	SMALLINT
int、java.lang.Integer	integer	INGEGER
long、java.lang.Long	long	BIGINT
float、java.lang.Float	float	FLOAT
double、java.lang.Double	double	DOUBLE
java.math.BigDecimal	big_decimal	NUMERIC
char、java.lang.Character	character	CHAR(1)
boolean、java.lang.Boolean	boolean	BIT
java.lang.String	string	VARCHAR
boolean、java.lang.Boolean	yes_no	CHAR(1)(‘Y’或‘N’)
boolean、java.lang.Boolean	true_false	CHAR(1)(‘Y’或‘N’)
java.util.Date、java.sql.Date	date	DATE
java.util.Date、java.sql.Time	time	TIME
java.util.Date、java.sql.Timestamp	timestamp	TIMESTAMP
java.util.Calendar	calendar	TIMESTAMP
java.util.Calendar	calendar_date	DATE
byte[]	binary	VARBINARY、BLOB
java.lang.String	text	CLOB
java.io.Serializable	serializable	VARBINARY、BLOB
java.sql.Clob	clob	CLOB
java.sql.Blob	blob	BLOB
java.lang.Class	class	VARCHAR
java.util.Locale	locale	VARCHAR
java.util.TimeZone	timezone	VARCHAR
java.util.Currency	currency	VARCHAR

数据库存JSON数据

存基本数据类型

//创建分支节点(上下虚拟节点及两个审批节点)并处理节点间关系
ArrayList<AppProcessNodeConfig> nodeList = new ArrayList<>();
AppProcessNodeConfig approverLeft = initializeApproverNode(prevNode.getAppId());
AppProcessNodeConfig approverRight = initializeApproverNode(prevNode.getAppId());
AppProcessNodeConfig dummyUp = initializeDummyNode(prevNode.getAppId());
AppProcessNodeConfig dummyDown = initializeDummyNode(prevNode.getAppId());

approverLeft.setPrevId(JSON.toJSONString(Arrays.asList(dummyUp.getId())));
approverLeft.setNextId(JSON.toJSONString(Arrays.asList(dummyDown.getId())));
approverRight.setPrevId(JSON.toJSONString(Arrays.asList(dummyUp.getId())));
approverRight.setNextId(JSON.toJSONString(Arrays.asList(dummyDown.getId())));
dummyUp.setPrevId(JSON.toJSONString(Arrays.asList(prevNodeId)));
dummyUp.setNextId(JSON.toJSONString(Arrays.asList(approverLeft.getId(), approverRight.getId())));
dummyDown.setPrevId(JSON.toJSONString(Arrays.asList(approverLeft.getId(), approverRight.getId())));
dummyDown.setNextId(JSON.toJSONString(Arrays.asList(nextNodeId)));

取基本数据类型

//获取父节点及其子idList
AppProcessNodeConfig prevNode = appProcessNodeConfigMapper.selectById(prevNodeId);
if (isNullOrEmpty(prevNode)) {
    return CommonResult.failed(CommonCodeEnum.NODE_NOT_EXIST);
}
List<String> prevNodeSonIdList = JSON.parseArray(prevNode.getNextId(), String.class);

存对象

@Data
@EqualsAndHashCode(callSuper = false)
public class AuthorityResp implements Serializable {
    private static final long serialVersionUID = 1L;
    
    @ApiModelProperty(value = "控件名称")
    private String name;
    @ApiModelProperty(value = "是否可读")
    private Boolean readable;
    @ApiModelProperty(value = "是否可编辑")
    private Boolean editable;
}

private String getAuthority(String appId, Boolean readable, Boolean editable) {
    List<AuthorityResp> authorityRespList = new ArrayList<>();
    AppTextControlConfig textControlConfig = appTextControlConfigMapper.selectOne(new LambdaQueryWrapper<AppTextControlConfig>()
                .eq(AppTextControlConfig::getControlOrderId, controlOrder.getId()));
    AuthorityResp authorityResp = new AuthorityResp();
    authorityResp.setName(textControlConfig.getName());
    authorityResp.setReadable(readable);
    authorityResp.setEditable(editable);
    authorityRespList.add(authorityResp);
    return JSON.toJSONString(authorityRespList);
}

private AppProcessNodeConfig initializeApproverNode(String appId) {
    AppProcessNodeConfig approver = new AppProcessNodeConfig();
    approver.setAppId(appId);
    approver.setName("审批");
    approver.setType(APPROVE_NODE);
    approver.setApproverType(DYNAMIC_APP_SUBMITTER);
    //设置权限
    String authority = getAuthority(appId, true, true);
    approver.setAuthority(authority);
    approver.setIsDeliver(true);
    approver.setIsRollback(true);
    approver.setIsSign(true);
    approver.setIsStamp(true);
    approver.setIsOpinion(true);
    appProcessNodeConfigMapper.insert(approver);
    return approver;
}

传统的`SimpleDateFormat`类计算时间差

用SimpleDateFormat计算时间差的方法，网上找了一份，自己跑了一遍，可以使用，贴在下面

/**
* 用SimpleDateFormat计算时间差
* @throws ParseException 
*/
public static void calculateTimeDifferenceBySimpleDateFormat() throws ParseException {
    SimpleDateFormat simpleFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm");
    /*天数差*/
    Date fromDate1 = simpleFormat.parse("2018-03-01 12:00");  
    Date toDate1 = simpleFormat.parse("2018-03-12 12:00");  
    long from1 = fromDate1.getTime();  
    long to1 = toDate1.getTime();  
    int days = (int) ((to1 - from1) / (1000 * 60 * 60 * 24));  
    System.out.println("两个时间之间的天数差为：" + days);

    /*小时差*/
    Date fromDate2 = simpleFormat.parse("2018-03-01 12:00");  
    Date toDate2 = simpleFormat.parse("2018-03-12 12:00");  
    long from2 = fromDate2.getTime();  
    long to2 = toDate2.getTime();  
    int hours = (int) ((to2 - from2) / (1000 * 60 * 60));
    System.out.println("两个时间之间的小时差为：" + hours);

    /*分钟差*/
    Date fromDate3 = simpleFormat.parse("2018-03-01 12:00");  
    Date toDate3 = simpleFormat.parse("2018-03-12 12:00");  
    long from3 = fromDate3.getTime();  
    long to3 = toDate3.getTime();  
    int minutes = (int) ((to3 - from3) / (1000 * 60));  
    System.out.println("两个时间之间的分钟差为：" + minutes);
}

两个时间之间的天数差为：11
两个时间之间的小时差为：264
两个时间之间的分钟差为：15840

去掉字符串中的空格(包括空格及tab等空数据)

StringUtils.deleteWhitespace(str);

获取JSON中某个字段值

String access_token = JSONObject.parseObject(generalResponse.getResult()).getJSONObject("data").getString("access_token");
String refresh_token = JSONObject.parseObject(generalResponse.getResult()).getJSONObject("data").getString("refresh_token");