【工作】Mybatis高频代码备忘录

Mybatis

# Mapper.xml使用

消除SQL语句where查询的第一个and

<where>
    <if test="attr1 != null and attr1 != ''">
        (LOWER(COLUMN1) LIKE CONCAT(CONCAT('%', LOWER(#{attr1})), '%'))
    </if>

    <if test="attr2 != null and attr2 != ''">
        AND (LOWER(COLUMN2) LIKE CONCAT(CONCAT('%', LOWER(#{attr2})), '%'))
    </if>
</where>

1
2
3
4
5
6
7
8
9

在mapper.xml中引用其他mapper.xml文件中代码片段
ShareMapper.xml

<mapper namespace="com.company.ShareMapper">
    <sql id="someSQL">
       id,name
    </sql>
</mapper>

1
2
3
4
5

CustomMapper.xml

<mapper namespace="com.company.CustomMapper">
    <select id="selectSome" >
        select
       <include refid="com.company.ShareMapper.someSQL"/>
        from t
    </select>
</mapper>

1
2
3
4
5
6
7

# 复杂操作

查询忽略关键字(Oracle)

public static String replaceUnderlineOrPercent(String content) {
  String SOURCE_UNDERLINE = "_";
  String SOURCE_PERCENT = "%";
  String SOURCE_SLASHLINE = "\\\\";
  String TARGET_UNDERLINE = "\\\\_";
  String TARGET_PERCENT = "\\\\%";
  String TARGET__SLASHLINE = "\\\\\\\\";
  if (StringUtils.hasText(content)) {
    String result = content.replaceAll("\\\\", "\\\\\\\\").replaceAll("_", "\\\\_").replaceAll("%", "\\\\%");
    return result;
  } else {
    return content;
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14

<if test="keyword != null and keyword != ''">
  AND (
  USER_ACCOUNT LIKE CONCAT(CONCAT('%', #{keyword, jdbcType=VARCHAR}), '%')ESCAPE '\' OR
  NICK_NAME LIKE CONCAT(CONCAT('%', #{keyword, jdbcType=VARCHAR}), '%')ESCAPE '\'
  )
</if>

1
2
3
4
5
6

数据加解密操作

/**
 * AES对称加密
 */
public final class AesUtil {

  private AesUtil() {}

  private static final String AES_TYPE = "AES/CBC/PKCS5Padding";
  private static final String AES = "AES";

  public static String encrypt(String message, String key, String iv) {
    try {
      // 偏移量
      IvParameterSpec ivParameterSpec = new IvParameterSpec(iv.getBytes());
      SecretKeySpec secretKeySpec = new SecretKeySpec(key.getBytes(), AES);
      Cipher cipher = Cipher.getInstance(AES_TYPE);
      // 加密
      cipher.init(Cipher.ENCRYPT_MODE, secretKeySpec, ivParameterSpec);
      byte[] encryptedData = cipher.doFinal(message.getBytes(StandardCharsets.UTF_8));
      String sencryptBase64Data = Base64.getEncoder().encodeToString(encryptedData);
      return sencryptBase64Data;
    } catch (Exception e) {
      throw new SystemException(e);
    }
  }

  public static String decrypt(String message, String key, String iv) {
    try {
      IvParameterSpec ivParameterSpec = new IvParameterSpec(iv.getBytes());
      SecretKeySpec secretKeySpec = new SecretKeySpec(key.getBytes(), AES);
      Cipher cipher = Cipher.getInstance(AES_TYPE);
      // 解密
      byte[] bytes = Base64.getDecoder().decode(message.getBytes());
      cipher.init(Cipher.DECRYPT_MODE, secretKeySpec, ivParameterSpec);
      byte[] decryptedData = cipher.doFinal(bytes);
      String decryptedBase64Data = new String(decryptedData, StandardCharsets.UTF_8);
      return decryptedBase64Data;
    } catch (Exception e) {
      throw new SystemException(e);
    }
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

@Slf4j
public class AesEncryptHandler extends BaseTypeHandler {

  public static final String KEY = "aaaaaaaa";
  public static final String IV = "bbbbbbbbb";

  @Override
  public void setNonNullParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException {
    ps.setString(i, AesUtil.encrypt((String) parameter, KEY, IV));
  }

  @Override
  public String getNullableResult(ResultSet rs, String columnName) throws SQLException {
    String columnValue = rs.getString(columnName);
    return decrypt(columnValue);
  }

  @Override
  public String getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
    String columnValue = rs.getString(columnIndex);
    return decrypt(columnValue);
  }

  @Override
  public String getNullableResult(CallableStatement cs, int columnIndex)
      throws SQLException {
    String columnValue = cs.getString(columnIndex);
    return decrypt(columnValue);
  }

  private String decrypt(String columnValue) {
    if (columnValue == null) {
      return null;
    }
    try {
      columnValue = AesUtil.decrypt(columnValue, KEY, IV);
    } catch (Exception e) {
      log.error("解密失败，返回原始数据");
    }
    return columnValue;
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

@Getter
@Setter
@ToString
@TableName(value = "USER", autoResultMap = true)
@KeySequence(value = "S_USER")
public class User {
  
    @TableField(typeHandler = AesEncryptHandler.class)
    private String userName;

    //...
}

1
2
3
4
5
6
7
8
9
10
11
12

  <!--自定义查询-->
  <resultMap id="userVo"
             type="com.brotherc.User">
    <result column="user_name" property="userName"
            typeHandler="com.brotherc.AesEncryptHandler" />
  </resultMap>

  <select id="select" resultMap="userVo">
    SELECT * FROM USER;
  </select>

1
2
3
4
5
6
7
8
9
10

使用Mybatis或MybtisPlug查询无需额外操作
如果查询条件包括加密字段，需要加密查询条件

Mybatis-plus max查询

// entity
Object obj = new Object();
// 查询max
QueryWrapper<Object> queryWrapper = new QueryWrapper<>();
queryWrapper.select("NVL(MAX(SEQ_NO), 0)");
int seqNo = getObj(queryWrapper, o -> ((BigDecimal) o).intValue());

1
2
3
4
5
6

Brotherc 博客

Choose mode