package com.example.unique.domain;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.example.unique.common.annotation.unique.Unique;
import lombok.Data;
 * @author anganing
 * @version 1.0.0
 * @date 2021/7/31 10:56
@Data
@TableName("user")
@Unique(tableName = "user", columns = {"username", "name"}, message = "用户名或姓名已存在!")
public class UserDO {
	@TableId(type = IdType.AUTO)
	private Long id;
	private String username;
	private String password;
	private String name;
package com.example.unique.common.annotation.unique;
import javax.validation.Constraint;
import javax.validation.Payload;
import java.lang.annotation.*;
 * 字段唯一约束注解
 * @author anganing
 * @version 1.0.0
 * @date 2021/7/31 11:19
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.TYPE, ElementType.ANNOTATION_TYPE})
@Constraint(validatedBy = {UniqueValidator.class})
public @interface Unique {
	 * 实体对应的表名
	String tableName() default "";
	 * 唯一字段列名(数据库字段名)
    String[] columns() default {};
	 * 校验不通过消息提示
	String message() default "已存在该字段!";
	Class<?>[] groups() default {};
	Class<? extends Payload>[] payload() default {};
package com.example.unique.common.annotation.unique;
import lombok.SneakyThrows;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.annotation.Resource;
import javax.validation.ConstraintValidator;
import javax.validation.ConstraintValidatorContext;
import java.lang.reflect.Field;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
 * @author anganing
 * @version 1.0.0
 * @date 2021/7/31 12:15
public class UniqueValidator implements ConstraintValidator<Unique, Object> {
	@SneakyThrows
	@Override
	public boolean isValid(Object obj, ConstraintValidatorContext context) {
		Class<?> aClass = obj.getClass();
		Field[] fields = aClass.getDeclaredFields();
		// 用户提交的id
		Long id = null;
		// 唯一约束列名和值
		Map<String, Object> uniqueColumnsMap = new HashMap<>(3);
		// 获取用户提交的id值, 唯一约束字段名及值
        for (Field field : fields) {
	        field.setAccessible(true);
	        String fieldName = field.getName();
	        if ("id".equals(fieldName)) {
		        id = (Long) field.get(obj);
            for (String column : columns) {
	            if (Objects.equals(column, fieldName)) {
		            uniqueColumnsMap.put(camel2Underline(column), field.get(obj));
		// 检查唯一约束字段是否重复
		boolean isUnique = false;
		for (String column : uniqueColumnsMap.keySet()) {
			// 查询sql select id from tableName where column='value' limit 1
			String sb = "select id from " + tableName + " where " + column + "=" + "'" + uniqueColumnsMap.get(column) + "'" + " limit 1";
			List<Long> dbIds = jdbcTemplate.queryForList(sb, Long.class);
			// 如果数据库的id和用户提交的id相等则认为该字段唯一
			if (dbIds.isEmpty() || Objects.equals(dbIds.get(0), id) || Objects.equals(id, null)) {
				isUnique = true;
			}else {
				isUnique = false;
				break;
        // 主观认为用户输入的都是不唯一的
		return isUnique;
	@Override
	public void initialize(Unique unique) {
		this.tableName = unique.tableName();
		this.columns = unique.columns();
	 * 驼峰转下划线
	 * @param str
	 * @return
	public String camel2Underline(String str) {
		if (str == null || str.trim().isEmpty()){
			return "";
		int len = str.length();
		StringBuilder sb = new StringBuilder(len);
		sb.append(str.substring(0, 1).toLowerCase());
		for (int i = 1; i < len; i++) {
			char c = str.charAt(i);
			if (Character.isUpperCase(c)) {
				sb.append("_");
				sb.append(Character.toLowerCase(c));
			} else {
				sb.append(c);
		return sb.toString();
	 * 实体对应的表名
	private String tableName;
	 * 唯一字段列名(数据库字段名)
	private String[] columns;
	 * jdbcTemplate
	@Resource
	private JdbcTemplate jdbcTemplate;
重现步骤(如果有就写完整)
优化（逻辑删除和普通字段）
public class UniqueValidator implements ConstraintValidator<Unique, Object> {
    //处理逻辑删除
    private static final String DELETE_FLAG = "deleteFlag";
    @Autowired
    private DataSource dataSource;
    @SneakyThrows
    @Override
    public boolean isValid(Object obj, ConstraintValidatorContext context) {
        Class<?> aClass = obj.getClass();
        TableInfo tableInfo = TableInfoHelper.getTableInfo(aClass);
        String tableName = tableInfo.getTableName();
        //如果有逻辑删除列，则增加到查询中
        String sqlLoginDelete = tableInfo.getLogicDeleteSql(true, true);
        String idColumn = tableInfo.getKeyProperty();
        // field列表中竟然不包含keyfield的信息
        Object id = null;//tableInfo.getFieldList().stream().filter(r->r.getColumn().equalsIgnoreCase(tableInfo.getKeyColumn())).findFirst().get().getField().get(obj);
        List<Field> fields = new ArrayList<>();
        fields.addAll(Arrays.asList(aClass.getDeclaredFields()));
        if (!Objects.isNull(aClass.getSuperclass()))
            fields.addAll(Arrays.asList(aClass.getSuperclass().getDeclaredFields()));
        for (Field field : fields) {
            field.setAccessible(true);
            if (field.getName().equalsIgnoreCase(tableInfo.getKeyColumn())) {
                id = field.get(obj);
                break;
        // 检查唯一约束字段是否重复
        boolean isUnique = false;
        for (UniqueConfig config : configs) {
            // 唯一约束列名和值
            List<FieldWhere> uniqueColumnsMap = new ArrayList<>();
            for (String column : config.getColumns()) {
                TableFieldInfo tableField = tableInfo.getFieldList().stream().filter(r -> r.getColumn().equalsIgnoreCase(column)).findFirst().orElse(null);
                //不在列表中的都是未绑定数据库字段
                if (Objects.isNull(tableField)) {
                    uniqueColumnsMap.add(FieldWhere.of(camel2Underline(column), "=?", tableField.getField().get(obj)));
                } else {
                    uniqueColumnsMap.add(FieldWhere.of(tableField.getProperty(), "=?", tableField.getField().get(obj)));
            //组合条件查询,出错立即跳出
            if (!Objects.isNull(id))
                uniqueColumnsMap.add(FieldWhere.of(idColumn, "<>?", id));
            String sql = String.format("select 1 from %s where %s   %s limit 1;", tableName,
                    uniqueColumnsMap.stream().map(r -> r.name + r.op).collect(Collectors.joining(" and ")),
                    sqlLoginDelete
            //多数据源支持
            JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
            //List<Long> dbIds = jdbcTemplate.queryForList(sql, paras, Long.class);
            List<Long> dbIds = jdbcTemplate.queryForList(sql, uniqueColumnsMap.stream().map(r -> r.v).toArray(), Long.class);
            if (dbIds.isEmpty()) //不存在另一个符合条件的，可以认为是唯一的
                continue;
            Stream<FieldWhere> mm = uniqueColumnsMap.stream().filter(r -> !r.name.equalsIgnoreCase(idColumn));
            context.disableDefaultConstraintViolation();//禁用默认的message的值
           //重新添加错误提示语句,通过MSG向调用方返回冲突的值，消息包装在外部进行: name 是检查的名称（多语言键值），一般可以认为是第一个列名
            String outParas = JSON.toJSONString(new String[]{
                    Strings.isNullOrEmpty(config.name) ? mm.map(r -> r.name).collect(Collectors.toList()).get(0) : L.t(getRequestLang(), config.name),
                    mm.map(r -> r.v + "").collect(Collectors.joining(","))});
            context.buildConstraintViolationWithTemplate(outParas).addConstraintViolation();
            return false;
        return true;
    static String getRequestLang() {
        String lang = CommonServletUtil.getRequest().getHeader("lang");
        if (Strings.isNullOrEmpty(lang))
            return "en";
            return lang.toLowerCase(Locale.ROOT).replace("_", "-");
    @Override
    public void initialize(Unique unique) {
        configs = new UniqueConfig[unique.columns().length];
        for (int i = 0; i < unique.columns().length; i++) {
            configs[i] = UniqueConfig.of(unique.names()[i], unique.columns()[i].split(","));
//        this.tableName = unique.tableName();
//        this.columns = unique.columns();
////        this.msg = unique.message();
//        this.name = unique.name();
     * 驼峰转下划线
     * @param str
     * @return
    public String camel2Underline(String str) {
        if (str == null || str.trim().isEmpty()) {
            return "";
        int len = str.length();
        StringBuilder sb = new StringBuilder(len);
        sb.append(str.substring(0, 1).toLowerCase());
        for (int i = 1; i < len; i++) {
            char c = str.charAt(i);
            if (Character.isUpperCase(c)) {
                sb.append("_");
                sb.append(Character.toLowerCase(c));
            } else {
                sb.append(c);
        return sb.toString();
    UniqueConfig[] configs;
//每个字段表示一个unique检查，如果unique由多个列组成，则使用逗号分隔，如{"no","name,time"}
@Unique(columns = {"CardNo","CardName"}, names=  { R.Common.TableColumn.CardNo,R.Common.TableColumn.CardName })
 @Override
    public boolean save(T entity) {
        if(validator.supports(this.entityClass)) {
            BindException errors = new BindException(entity, this.entityClass.getSimpleName());
            validator.validate(entity, errors);
            if(errors.hasErrors()){
                throw CommonException.of(R.Common.Global.WelComText);
        return super.save(entity);
        //https://www.cnblogs.com/niugang0920/p/12689224.html
# 生成的语句
 use patroldb_GH000F ; 
select 1 from card where cardNo='005FFEC2' and id<>'1686190791473471490'    AND delete_flag='0' limit 1;
# 出错提示
>>> 卡号已存在,005FFEC2，