数据库
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;
}
}