一、基础实体
@MappedSuperclass
public abstract class AbsIdEntity implements Serializable { private static final long serialVersionUID = 7988377299341530426L; public final static int IS_DELETE_YES = 1;// 标记删除
public final static int IS_DELETE_NO = 0;// 未删除,保留的 @Id
@GenericGenerator(name="uuid", strategy="uuid")
@GeneratedValue(generator="uuid")
protected String id; @Column(name = "creationtime")
protected Timestamp creationTimestamp = new Timestamp(System.currentTimeMillis()); @Column(name = "lastmodifiedtime")
protected Timestamp modificationTimestamp; @Column(name = "dr")
protected int dr;// 是否删除。0:未删除;1:已删除 /**
* 主键，对应id字段
*/
public String getId() { return id; }
public void setId(String id) { this.id = id; } /**
* 创建日期，对应ts_insert字段
*/
public Timestamp getCreationTimestamp() { return creationTimestamp; }
public void setCreationTimestamp(Timestamp creationTimestamp) { this.creationTimestamp = creationTimestamp; } /**
* 修改日期，对应ts_update字段
*/
public Timestamp getModificationTimestamp() { return modificationTimestamp; }
public void setModificationTimestamp(Timestamp modificationTimestamp) { this.modificationTimestamp = modificationTimestamp; } /**
* 是否删除，对应dr字段
* @return
*/
public int getDr() {
return dr;
}
public void setDr(int dr) {
this.dr = dr;
} }
二、扩展VO
import java.io.Serializable;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException; import org.springframework.jdbc.core.RowMapper; import xxx.entity.AbsIdEntity; public class StaffUnionVO extends AbsIdEntity implements Serializable, RowMapper<StaffUnionVO>{
/**
*
*/
private static final long serialVersionUID = -5213225066818183479L; private String code;// 员工编码 private String name;// 员工姓名 private int sex; // 性别 private Date birthday;// 出生年月日 private String deptId;// 人员所属部门 private String companyId;//人员所属公司表 private EnumStatus status;// 员工状态，在职，离职 private String nativePlace;// 籍贯 private String education;// 学历 private EnumPoliticalStatus politicalStatus;// 政治面貌 private String major;// 所学专业 private String school;// 毕业学校 private Date graduationTime;// 毕业时间 private Date workTime;// 参加工作时间 private String credentialCode;// 证件号码 private String mobile;// 移动电话 private String signPic;// 签名图片 private String landlineNum;// 座机号 private String age;// 年龄 private String post;// 职位 private String positionId;// 岗位Id private String dept;// 部门 private String email;// 邮箱 private String userId;// 用户关联人员 private int isUser;// 是否关联人员 private int credentialType;//证件类型 private String address;//地址 private String staffTypeId;//人员id private String property;//直属或隶属 public String getProperty() {
return property;
} public void setProperty(String property) {
this.property = property;
} public String getPositionId() {
return positionId;
} public void setPositionId(String positionId) {
this.positionId = positionId;
} public String getAddress() {
return address;
} public void setAddress(String address) {
this.address = address;
} public String getStaffTypeId() {
return staffTypeId;
} public void setStaffTypeId(String staffTypeId) {
this.staffTypeId = staffTypeId;
} public int getCredentialType() {
return credentialType;
} public void setCredentialType(int credentialType) {
this.credentialType = credentialType;
} public int getIsUser() {
return isUser;
} public void setIsUser(int isUser) {
this.isUser = isUser;
} public String getUserId() {
return userId;
} public void setUserId(String userId) {
this.userId = userId;
} public String getLandlineNum() {
return landlineNum;
} public void setLandlineNum(String landlineNum) {
this.landlineNum = landlineNum;
} public String getDeptId() {
return deptId;
} public void setDeptId(String deptId) {
this.deptId = deptId;
} public String getCompanyId() {
return companyId;
} public void setCompanyId(String companyId) {
this.companyId = companyId;
} public String getAge() {
return age;
} public void setAge(String age) {
this.age = age;
} public String getPost() {
return post;
} public void setPost(String post) {
this.post = post;
} public String getPosition() {
return positionId;
} public void setPosition(String position) {
this.positionId = position;
} public String getDept() {
return dept;
} public void setDept(String dept) {
this.dept = dept;
} public String getEmail() {
return email;
} public void setEmail(String email) {
this.email = email;
} public String getCode() {
return code;
} public void setCode(String code) {
this.code = code;
} public String getName() {
return name;
} public void setName(String name) {
this.name = name;
} public Date getBirthday() {
return birthday;
} public int getSex() {
return sex;
} public void setSex(int sex) {
this.sex = sex;
} public void setBirthday(Date birthday) {
this.birthday = birthday;
} public EnumStatus getStatus() {
return status;
} public void setStatus(EnumStatus status) {
this.status = status;
} public String getNativePlace() {
return nativePlace;
} public void setNativePlace(String nativePlace) {
this.nativePlace = nativePlace;
} public String getEducation() {
return education;
} public void setEducation(String education) {
this.education = education;
} public EnumPoliticalStatus getPoliticalStatus() {
return politicalStatus;
} public void setPoliticalStatus(EnumPoliticalStatus politicalStatus) {
this.politicalStatus = politicalStatus;
} public String getMajor() {
return major;
} public void setMajor(String major) {
this.major = major;
} public String getSchool() {
return school;
} public void setSchool(String school) {
this.school = school;
} public Date getGraduationTime() {
return graduationTime;
} public void setGraduationTime(Date graduationTime) {
this.graduationTime = graduationTime;
} public Date getWorkTime() {
return workTime;
} public void setWorkTime(Date workTime) {
this.workTime = workTime;
} public String getCredentialCode() {
return credentialCode;
} public void setCredentialCode(String credentialCode) {
this.credentialCode = credentialCode;
} public String getMobile() {
return mobile;
} public void setMobile(String mobile) {
this.mobile = mobile;
} public String getSignPic() {
return signPic;
} public void setSignPic(String signPic) {
this.signPic = signPic;
} @Override
public StaffUnionVO mapRow(ResultSet rs, int row) throws SQLException {
StaffUnionVO vo = new StaffUnionVO();
vo.setAddress(rs.getString("address"));
vo.setAge(rs.getString("age"));
vo.setBirthday(rs.getDate("birthday"));
vo.setCode(rs.getString("code"));
vo.setCompanyId(rs.getString("companyId"));
vo.setCreationTimestamp(rs.getTimestamp("creationtime"));
vo.setCredentialCode(rs.getString("credentialCode"));
vo.setCredentialType(rs.getInt("credentialType"));
vo.setDept(rs.getString("dept"));
vo.setDeptId(rs.getString("deptId"));
vo.setDr(rs.getInt("dr"));
vo.setEducation(rs.getString("education"));
vo.setEmail(rs.getString("email"));
vo.setGraduationTime(rs.getDate("graduationTime"));
vo.setId(rs.getString("id"));
vo.setIsUser(rs.getInt("isUser"));
vo.setLandlineNum(rs.getString("landlineNum"));
vo.setMajor(rs.getString("major"));
vo.setMobile(rs.getString("mobile"));
vo.setModificationTimestamp(rs.getTimestamp("lastmodifiedtime"));
vo.setName(rs.getString("name"));
vo.setNativePlace(rs.getString("nativePlace"));
vo.setPoliticalStatus(rs.getString("politicalStatus") == null || "".equals(rs.getString("politicalStatus").trim()) ? null : EnumPoliticalStatus.valueOf(rs.getString("politicalStatus")));
vo.setPositionId(rs.getString("positionId"));
vo.setPost(rs.getString("post"));
vo.setProperty(rs.getString("property"));
vo.setSchool(rs.getString("school"));
vo.setSex(rs.getInt("sex"));
vo.setSignPic(rs.getString("signPic"));
vo.setStaffTypeId(rs.getString("staffTypeId"));
vo.setStatus(rs.getString("status") == null || "".equals(rs.getString("status").trim()) ? null : EnumStatus.valueOf(rs.getString("status")));
vo.setUserId(rs.getString("userId"));
vo.setWorkTime(rs.getDate("workTime")); return vo;
}
}
三、使用JdbcTemplate 进行联表查询操作
@Repository
public class StaffUnionDao {
@Autowired
private JdbcTemplate jdbcTemplate; public List<StaffUnionVO> getStaffUnion(String deptId, String param) {
//sql中联表查询，自定义列名
String sql = "select bs.id id, bs.creationtime creationtime, bs.lastmodifiedtime lastmodifiedtime, bs.dr dr, bs.code code, bs.name name, bs.sex sex, bs.birthday birthday, bsp.dept_id as deptId, bsp.company_id companyId,"
+ "bs.status status, bs.native_place nativePlace, bs.education education, bs.political_status politicalStatus, bs.major major, bs.school school, bs.graduation_time graduationTime, bs.work_time workTime,"
+ "bs.credential_code credentialCode, bs.mobilephone_num mobile, bs.sign_pic signPic, bs.landline_num landlineNum, bs.age age, bs.post post, bsp.position positionId, bs.dept dept, bs.is_user isUser,"
+ "bs.email email, bs.userid userId, bs.credential_type credentialType, bs.address address, bs.staff_type_id staffTypeId, bsp.property property from bd_staff bs, bd_staff_partjob bsp where bs.id = bsp.staff_id and bsp.dr = 0"; //String sqlx = "select bs.id id from bd_staff bs, bd_staff_partjob bsp where bs.id = bsp.staff_id and bsp.dr = 0"; if(deptId != null) {
sql += "and bsp.dept_id = " + deptId;
}
if(param != null) {
sql += " and (bs.code like %" + param + "% or bs.name like %" + param + "%)";
}
(1)
//该方法无法实现类中变量是对象时的转换，例如存在枚举对象。
//List<StaffUnionVO> result = jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(StaffUnionVO.class));
(2)
// 可以自定义转化
// @SuppressWarnings("unchecked")
// List<StaffUnionVO> result = jdbcTemplate.query(sqlx, new RowMapper() {
// @Override
// public Object mapRow(ResultSet rs, int n) throws SQLException {
// StaffUnionVO vo = new StaffUnionVO();
// vo.setId(rs.getString("id"));
// return vo;
// }
// });
(3)
//自定义映射方式的实现写在了 StaffUnionVO类中
List<StaffUnionVO> result = jdbcTemplate.query(sql, new StaffUnionVO()); return result;
}
}
四、使用PageImpl进行分页
@Autowired
private StaffUnionDao newPageDao; @RequestMapping(path = "newpage", method = RequestMethod.GET)
@ResponseBody
public PageImpl<StaffUnionVO> getStaffInfoNew(@RequestParam(value = "deptId", required = false) String deptId,
@RequestParam(value = "searchText", required = false) String param,
@RequestParam("pageNumber") int pageNumber, @RequestParam("pageSize") int pageSize) {
List<StaffUnionVO> content = newPageDao.getStaffUnion(deptId, param);
PageRequest pageRequest = new PageRequest(pageNumber - 1, pageSize);
PageImpl<StaffUnionVO> ans = new PageImpl<StaffUnionVO>(content, pageRequest, content == null ? 0 : content.size());
return ans;
}
五、补充一种场景
进行分页查询的时候一般多是针对一个实体（对应一个表），通常使用的spring data jpa 的接口 JpaSpecificationExecutor<T>, 接口内容如下。
JpaSpecificationExecutor
public interface JpaSpecificationExecutor<T> {
/**
* Returns a single entity matching the given {@link Specification}.
*
* @param spec
* @return
*/
T findOne(Specification<T> spec);
/**
* Returns all entities matching the given {@link Specification}.
*
* @param spec
* @return
*/
List<T> findAll(Specification<T> spec);
/**
* Returns a {@link Page} of entities matching the given {@link Specification}.
*
* @param spec
* @param pageable
* @return
*/
Page<T> findAll(Specification<T> spec, Pageable pageable);
/**
* Returns all entities matching the given {@link Specification} and {@link Sort}.
*
* @param spec
* @param sort
* @return
*/
List<T> findAll(Specification<T> spec, Sort sort);
/**
* Returns the number of instances that the given {@link Specification} will return.
*
* @param spec the {@link Specification} to count instances for
* @return the number of instances
*/
long count(Specification<T> spec);
}
分页查询的结果是实体类的集合，通常在服务层完成。但是我们一般不让业务层直接接触我们的实体类，所以就是产生了VO类，也就是服务层不再返回实体类的集合，而是返回VO类的集合，业务层操作的其实是VO类。这样就显得层次清晰，方便控制。例如分页查询，具体实现如下。
请参考： 后台json格式设计 PagableResponse（分页json格式） ，生成查询Specification QueryTool工具
Controller
@Autowired
private RoleService roleService; @RequestMapping(path = "page")
@ResponseBody
public PagableResponse<RoleVO> getPageableRole(@RequestParam(value = "roleId", required = false) String roleId,
@RequestParam(value = "searchText", required = false) String param,
@RequestParam("pageNumber") int pageNumber, @RequestParam("pageSize") int pageSize) {
PagableResponse<RoleVO> response = new PagableResponse<RoleVO>();
response.setPageNumber(pageNumber);
response.setPageSize(pageSize);
PageRequest pageRequest = new PageRequest(pageNumber, pageSize);
try {
List<RoleVO> data = roleService.findPageableRoles(param, roleId, pageRequest);
long count = roleService.count(param, roleId);
response.setList(data);
response.setCount(count);
response.setCode(ReturnCode.SUCCESS);
response.setMsg("获取角色信息成功！");
} catch(Exception e) {
e.printStackTrace();
response.setCode(ReturnCode.FAILURE);
response.setMsg("获取角色信息失败！");
}
return response;
}
Service
@Override
public List<RoleVO> findPageableRoles(String param, String relyCondition, PageRequest pageRequest) {
List<RoleVO> list = new ArrayList<RoleVO>();
Map<String, Object> map = QueryTool.parseCondition(relyCondition);
//添加搜索添加
if(StringUtils.isNotBlank(param)){
map.put(Operator.LIKE+"_roleName", param);
map.put(Operator.LIKE+"_roleCode", relyCondition);
}
if(StringUtils.isNotBlank(relyCondition)) {
map.put(Operator.LIKE+"_id", relyCondition);
}
map.put(Operator.EQ+"_dr", "0");
Specification<RoleEntity> spec = QueryTool.buildSpecification(map, RoleEntity.class);
Page<RoleEntity> page = roleDao.findAll(spec, pageRequest);
for (RoleEntity entity : page) {
RoleVO roleVO = new RoleVO();
BeanUtils.copyProperties(entity, roleVO);
list.add(roleVO);
}
return list;
}
六、自定义规范
实体类
@Entity
@Table(name="sm_role")
public class RoleEntity extends AbsIdEntity implements Serializable{ private static final long serialVersionUID = 3717832885014711347L; @Column(name="role_name")
private String roleName; @Column(name="role_code")
private String roleCode; @Column(name="role_type")
private String roleType; @Column(name="isactive")
private String isactive; @Column(name="tenant_id")
private String tenantId; @Column(name="dtype")
private String dtype; @Column(name="remark")
private String remark; @Column(name="creator")
private String creator; @Column(name="reviser")
private String reviser; @Column(name="role_property")
private String roleProperty; @Column(name="related_post_id")
private String relatedPostId; @Column(name = "instruction")
private String instruction; public String getInstruction() {
return instruction;
} public void setInstruction(String instruction) {
this.instruction = instruction;
} public String getRoleName() {
return roleName;
} public void setRoleName(String roleName) {
this.roleName = roleName;
} public String getRoleCode() {
return roleCode;
} public void setRoleCode(String roleCode) {
this.roleCode = roleCode;
} public String getRoleType() {
return roleType;
} public void setRoleType(String roleType) {
this.roleType = roleType;
} public String getIsactive() {
return isactive;
} public void setIsactive(String isactive) {
this.isactive = isactive;
} public String getTenantId() {
return tenantId;
} public void setTenantId(String tenantId) {
this.tenantId = tenantId;
} public String getDtype() {
return dtype;
} public void setDtype(String dtype) {
this.dtype = dtype;
} public String getRemark() {
return remark;
} public void setRemark(String remark) {
this.remark = remark;
} public String getCreator() {
return creator;
} public void setCreator(String creator) {
this.creator = creator;
} public String getReviser() {
return reviser;
} public void setReviser(String reviser) {
this.reviser = reviser;
} public String getRoleProperty() {
return roleProperty;
} public void setRoleProperty(String roleProperty) {
this.roleProperty = roleProperty;
} public String getRelatedPostId() {
return relatedPostId;
} public void setRelatedPostId(String relatedPostId) {
this.relatedPostId = relatedPostId;
}
}
自定义Specification
public class RoleSpecification implements Specification<RoleEntity> {
private Integer delete = new Integer(0);// 默认查询未删除的数据
private String searchParam;
private String roleId;
public RoleSpecification(String searchParam, String roleId) {
super();
this.searchParam = searchParam;
this.roleId = roleId;
}
@Override
public Predicate toPredicate(Root<RoleEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
List<Predicate> list = new ArrayList<Predicate>();
list.add(cb.equal(root.get("dr").as(Integer.class), delete));
if (StringUtils.isNotBlank(searchParam)) {
list.add(
cb.or(
cb.like(root.get("roleName").as(String.class), cb.literal("%" + searchParam + "%")),
cb.like(root.get("roleCode").as(String.class), cb.literal("%" + searchParam + "%")),
cb.like(root.get("roleType").as(String.class), cb.literal("%" + searchParam + "%"))
)
);
}
//角色id
if(roleId != null && !("".equals(roleId.trim()))) {
list.add(cb.equal(root.get("roleId").as(String.class), roleId));
}
Predicate[] predicates = new Predicate[list.size()];
return cb.and(list.toArray(predicates));
}
public Integer getDelete() {
return delete;
}
public void setDelete(Integer delete) {
this.delete = delete;
}
public String getSearchParam() {
return searchParam;
}
public void setSearchParam(String searchParam) {
this.searchParam = searchParam;
}
public String getRoleId() {
return roleId;
}
public void setRoleId(String roleId) {
this.roleId = roleId;
}
}
注：规范中操作的 字段名称 都是 实体类的字段名称，不要写成数据库字段名称。
自定义规范可以自定义条件查询，比如 or， and等等，使用QueryTool工具建立的规范默认条件都是and，所以很多情况下不能满足我们的需求。总的来说，现在刚刚接触，Spring Data JPA 的 Specification 还要进一步学习。