Hibenate访问数据库表的char字段有风险

数据库

• 5.5.59-MariaDB

JPA

• spring-data-jpa:2.3.4RELEASE

问题描述

• 返回前端的数据中， json对象的 email varchar(100) '电子邮箱 '，仅仅截取第一个字符
• 解决方案：可以在SQL语句中使用cast将char类型转成varchar即可。
• 特别说明----此次bug仅仅不支持char(100)

 @Query(value = "select  cast(email as varchar(255)) ,（其他字段）) from  sys_contact_view ", nativeQuery = true)
  List<Map<String, String>> findAllContact();

使用场景

建表语句

CREATE TABLE `sys_org_cntct_psn` (
  `seq` int(32) NOT NULL AUTO_INCREMENT COMMENT 'seq',
  `sys_org_seq` int(32) DEFAULT NULL COMMENT '组织结构seq',
  `cntct_psn_type` char(20) DEFAULT NULL COMMENT '联系人类型，主联系人，副联系人',
  `id` char(50) DEFAULT NULL COMMENT '身份证号码',
  `name` char(50) DEFAULT NULL COMMENT '姓名 姓名',
  `mobile` char(16) DEFAULT NULL COMMENT '移动电话号码 移动电话号码\r\n\r\n格式：国际区号-手机号\r\nXXXX-XXXXXXXXXXX',
  `email` char(100) DEFAULT NULL COMMENT '电子邮箱 电子邮箱',
  `rmak` text COMMENT '备注 注解、说明。',
  `created_by` varchar(50) DEFAULT NULL COMMENT '创建者',
  `created_at` datetime DEFAULT NULL COMMENT '创建时间',
  `updated_by` varchar(50) DEFAULT NULL COMMENT '更新者',
  `updated_at` datetime DEFAULT NULL COMMENT '更新时间',
  `deleted_by` varchar(50) DEFAULT NULL COMMENT '删除者',
  `deleted_at` datetime DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`seq`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=4927 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT COMMENT='私募基金管理人联系人表';

因为某些原因必须使用视图，举例比较简单（使用视图）

create or  REPLACE view  sys_contact_view as
select  * from sys_org_cntct_psn;

因为视图不能有主键，所以没有建实体类Entity接收ResultSet

  ~~ sysOrgCntctRepository~~
  @Query(value = "select * from  sys_contact_view ", nativeQuery = true)
  List<Map<String, String>> findAllContact();
~~ ServiceImpl
 @Override
  public GenericDto findAll() {
    try {
          List<Map<String,String>> list = sysOrgCntctRepository.findAllContact();
          return  GenericDto.ok("查询私募联系人成功",list);
    } catch (Exception e) {
      logger.error("查询信息查询私募联系人失败", e);
      return GenericDto.error("系统出错", e);
    }
  }
~~  service ~~
public interface PofContactViewService {

  GenericDto findAll();

} 
~~ controller ~~
@RestController
@Slf4j
public class PofController {


  @Autowired
  private PofContactViewService pofContactViewService;
  @GetMapping(value = "/rest/pof/contact")
  public GenericDto getAllPofContact() {
    return  pofContactViewService.findAll();
  }
}
~~  GenericDto ~~
/**
 * 前后端交互通用对象
 *
 */
@ToString
public class GenericDto<T> implements Serializable {
  private static final long serialVersionUID = -8188189217272286693L;

  public final static char OK = '1';
  public final static char ERROR = '0';
  public final static char FAIL = '2';
  public final static String OK_STR = "ok";
  public final static String FAIL_STR = "fail";
  public final static String ERROR_STR = "error";
  public final static String UNKNOWN = "unknown";

  private String result;
  private String msg;
  private T object;

  protected GenericDto() {
  }

  protected GenericDto(String result) {
    this.result = result;
  }

  public static <T> GenericDto<T> valueOf(char result) {
    switch (result) {
      case OK:
        return new GenericDto<>(OK_STR);
      case ERROR:
        return new GenericDto<>(ERROR_STR);
      case FAIL:
        return new GenericDto<>(FAIL_STR);
      default:
        return new GenericDto<>(UNKNOWN);
    }
  }

  public static <T> GenericDto<T> valueOf(char result, String msg) {
    GenericDto<T> r = valueOf(result);
    r.setMsg(msg);
    return r;
  }

  public static <T> GenericDto<T> valueOf(char result, T object) {
    GenericDto<T> r = valueOf(result);
    r.setObject(object);
    return r;
  }

  public static <T> GenericDto<T> valueOf(char result, String msg, T object) {
    GenericDto<T> r = valueOf(result, msg);
    r.setObject(object);
    return r;
  }

  public static <T> GenericDto<T> ok() {
    return valueOf(OK);
  }

  public static <T> GenericDto<T> ok(T object) {
    GenericDto<T> r = ok();
    r.setObject(object);
    return r;
  }

  public static <T> GenericDto<T> ok(String msg, T object) {
    GenericDto<T> r = ok(object);
    r.setMsg(msg);
    return r;
  }

  public static <T> GenericDto<T> error() {
    return valueOf(ERROR);
  }

  public static <T> GenericDto<T> error(T object) {
    GenericDto<T> r = error();
    r.setObject(object);
    return r;
  }

  public static <T> GenericDto<T> error(String msg, T object) {
    GenericDto<T> r = error(object);
    r.setMsg(msg);
    return r;
  }

  public String getResult() {
    return result;
  }

  public void setResult(String result) {
    this.result = result;
  }

  public String getMsg() {
    return msg;
  }

  public void setMsg(String msg) {
    this.msg = msg;
  }

  public T getObject() {
    return object;
  }

  public void setObject(T object) {
    this.object = object;
  }
}