当前位置：首页 > news >正文

Java中校验导入字段长度与数据库字段长度一致性

news 来源：原创 2024/9/20 3:00:55

需求：使用EasyExcel导入数据时，根据数据库字段长度校验导入字段的长度。使用的数据库是mysql。若是一般的校验需求， Spring Validation 或 Hibernate Validator 即可满足。

实现步骤：

获取需要校验的表，查询出字段相关信息
对比导入字段与数据库字段长度，不符合则跑出异常
实体字段的属性应该与数据库字段对应的驼峰命名相同，如数据库是user_age, 实体属性应是userAge;
只校验字符串类型的字段

代码示例

数据库字段信息类：

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;/*** @author sjy* @date 2024/9/4 17:59* @description:*/
@Data
public class DatabaseInfoVo {@ApiModelProperty("表名")private String tableName;@ApiModelProperty("列名")private String columnName;@ApiModelProperty("字段注释")private String comment;@ApiModelProperty("字段长度")private Integer length;
}

xml查询语句示例：

<select id="findDatabaseSchemaInfo" resultType="xxxxx.vo.DatabaseInfoVo">SELECTTABLE_NAME AS tableName,COLUMN_NAME AS columnName,COLUMN_COMMENT AS comment,CHARACTER_MAXIMUM_LENGTH AS lengthFROMINFORMATION_SCHEMA.COLUMNSWHERETABLE_SCHEMA = #{useDbname}AND TABLE_NAME in ('yourtable','yourtable','yourtable','yourtable','yourtable')AND CHARACTER_MAXIMUM_LENGTH IS NOT NULLAND COLUMN_NAME NOT IN ('KSKID','KSJCXXID')
</select>

这里只需要校验固定的几张表，想要灵活的话可以修改成参数传递进来。

对比逻辑：

private final ThreadLocal<Map<String, DatabaseInfoVo>> databaseInfoLocal = new ThreadLocal<>();
// 使用完成需要释放
public void removeThreadLocal() {databaseInfoLocal.remove();}/*** 校验导入字段值与数据库字段值长度* @param dto*/private void validDatabaseWord (yourDto dto) {Map<String, DatabaseInfoVo> databaseInfoVoMap = databaseInfoLocal.get();if (CollectionUtils.isEmpty(databaseInfoVoMap)) {List<DatabaseInfoVo> databaseSchemaInfoList = yourmapper.findDatabaseSchemaInfo(useDbname);if (databaseSchemaInfoList != null) {databaseInfoVoMap = databaseSchemaInfoList.stream().filter(databaseInfoVo ->// xx表只需要校验两个字段!"table".equals(databaseInfoVo.getTableName()) ||"column1".equals(databaseInfoVo.getColumnName()) ||"column2".equals(databaseInfoVo.getColumnName())).collect(Collectors.toMap(databaseInfoVo -> toCamelCase(databaseInfoVo.getColumnName()),databaseInfoVo -> databaseInfoVo,(existing, replacement) -> existing // 处理键冲突时保留现有的值));} else {databaseInfoVoMap = Maps.newHashMap();}databaseInfoLocal.set(databaseInfoVoMap);}// 获取实体的属性Field[] fields = yourDto.class.getDeclaredFields();for (Field field : fields) {// 仅处理String类型的属性if (field.getType().equals(String.class)) {// 允许访问私有字段field.setAccessible(true);String fieldName = field.getName();if (databaseInfoVoMap.containsKey(fieldName)) {DatabaseInfoVo databaseInfo = databaseInfoVoMap.get(fieldName);if (ObjectUtil.isNotNull(databaseInfo)) {// 获取数据库列的长度int dbColumnLength = databaseInfo.getLength();try {// 获取实体属性的值Object fieldValue = field.get(dto);if (fieldValue != null) {String value = (String) fieldValue;if (value.length() > dbColumnLength) {// 如果没有引入swagger，直接使用列描述即可// throw new CheckedException(databaseInfo.getComment() + "超过长度,最大长度:" + databaseInfo.getLength());ApiModelProperty apiModelProperty = field.getAnnotation(ApiModelProperty.class);String apiPropertyValue = apiModelProperty.value();// 执行处理逻辑，例如截断、抛出异常、记录日志等throw new CheckedException(apiPropertyValue + "超过长度,最大长度:" + databaseInfo.getLength());}}} catch (IllegalAccessException e) {// 处理访问异常e.printStackTrace();}}}}}}/*** 数据库字段转驼峰* @param word* @return*/public String toCamelCase(String word) {if (StrUtil.isAllBlank(word)) {return "";}StringBuilder result = new StringBuilder();boolean toUpperCase = false;for (char ch : word.toCharArray()) {if (ch == '_') {toUpperCase = true;} else {if (toUpperCase) {result.append(Character.toUpperCase(ch));toUpperCase = false;} else {result.append(Character.toLowerCase(ch));}}}return result.toString();}