如何在MyBatis中使用动态SQL比较MySQL的二进制UUID

• 自定义MyBatis类型处理器
  实现BaseTypeHandler<UUID>接口，手动处理UUID与binary(16)的双向转换，确保参数和结果集的类型适配。示例代码：

  import org.apache.ibatis.type.BaseTypeHandler;
  import org.apache.ibatis.type.JdbcType;
  import java.sql.*;
  import java.util.UUID;
  
  public class UuidBinaryTypeHandler extends BaseTypeHandler<UUID> {
      @Override
      public void setNonNullParameter(PreparedStatement ps, int i, UUID parameter, JdbcType jdbcType) throws SQLException {
          String uuidStr = parameter.toString().replace("-", "");
          ps.setBytes(i, hexStringToByteArray(uuidStr));
      }
  
      @Override
      public UUID getNullableResult(ResultSet rs, String columnName) throws SQLException {
          byte[] bytes = rs.getBytes(columnName);
          return bytes == null ? null : byteArrayToUuid(bytes);
      }
  
      @Override
      public UUID getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
          byte[] bytes = rs.getBytes(columnIndex);
          return bytes == null ? null : byteArrayToUuid(bytes);
      }
  
      @Override
      public UUID getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
          byte[] bytes = cs.getBytes(columnIndex);
          return bytes == null ? null : byteArrayToUuid(bytes);
      }
  
      private byte[] hexStringToByteArray(String s) {
          int len = s.length();
          byte[] data = new byte[len / 2];
          for (int i = 0; i < len; i += 2) {
              data[i / 2] = (byte) ((Character.digit(s.charAt(i), 16) << 4)
                      + Character.digit(s.charAt(i+1), 16));
          }
          return data;
      }
  
      private UUID byteArrayToUuid(byte[] bytes) {
          StringBuilder sb = new StringBuilder();
          for (byte b : bytes) {
              sb.append(String.format("%02x", b));
          }
          String uuidStr = sb.insert(8, "-").insert(13, "-").insert(18, "-").insert(23, "-").toString();
          return UUID.fromString(uuidStr);
      }
  }
  

  注册该类型处理器后，MyBatis会自动处理UUID参数与binary(16)字段的转换，无需修改查询逻辑。

• 手动转换UUID为字节数组
  在查询前将UUID转换为符合存储格式的byte数组，直接以字节数组作为查询参数：

  // 工具方法：UUID转byte数组
  private byte[] uuidToBytes(UUID uuid) {
      String uuidStr = uuid.toString().replace("-", "");
      int len = uuidStr.length();
      byte[] data = new byte[len / 2];
      for (int i = 0; i < len; i += 2) {
          data[i / 2] = (byte) ((Character.digit(uuidStr.charAt(i), 16) << 4)
                  + Character.digit(uuidStr.charAt(i+1), 16));
      }
      return data;
  }
  
  // 构建查询
  UUID targetUuid = UUID.fromString("e59bf2fd-e742-4314-ab02-db195e0168c8");
  select(myTable.id)
      .from(myTable)
      .where(myTable.myUuidColumn, isEqualTo(uuidToBytes(targetUuid)))
      .build()
      .render(RenderingStrategies.MYBATIS3);
  

  注意确保转换逻辑与UUID存储时的逻辑一致（比如存储时用UNHEX(REPLACE(uuid_val, '-', ''))，则此转换有效）。

• 扩展动态SQL框架的类型转换器
  如果你使用的是MyBatis Dynamic SQL框架，可以自定义TypeConverter实现UUID到byte数组的转换，并绑定到对应的字段：

  import org.mybatis.dynamic.sql.type.TypeConverter;
  import java.util.UUID;
  
  TypeConverter<UUID, byte[]> uuidConverter = new TypeConverter<>() {
      @Override
      public byte[] convert(UUID uuid) {
          if (uuid == null) return null;
          String uuidStr = uuid.toString().replace("-", "");
          int len = uuidStr.length();
          byte[] data = new byte[len / 2];
          for (int i = 0; i < len; i += 2) {
              data[i / 2] = (byte) ((Character.digit(uuidStr.charAt(i), 16) << 4)
                      + Character.digit(uuidStr.charAt(i+1), 16));
          }
          return data;
      }
  };
  
  // 定义字段时绑定转换器
  SqlColumn<UUID> myUuidColumn = column("my_uuid", uuidConverter);
  

  此后框架会自动将UUID参数转换为byte数组，适配binary(16)字段的查询需求。

内容的提问来源于stack exchange，提问作者aravindaM