1 首先创建一个数据表实力user
他是一个复合对像(一般直接如下操作不推荐)
//需要配置
/*# mybatis
mybatis.configuration.map-underscore-to-camel-case: true
spring.jackson.date-format: yyyy-MM-dd HH:mm:ss
spring.jackson.time-zone: GMT+8*/
//学生类
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class MonitorPanelDO {
private Long id;
@NotBlank(message = "姓名不能为空")
@Length(max = 64, message = "名称太长")
private String name;
@NotNull(message = "性别不能为空")
private Integer age;
private Integer updateSeq; //防止重复覆盖
private Boolean removed; //是否离校
@NotBlank(message = "年级不能为空")
private String class;
@NotNull(message = "学科不能为空")
@Valid
private List<Subject> subjects;
private String description;
private Timestamp creationTime;
private String creationName;
private Timestamp updateTime;
private String updateName;
}
//学科类
@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class MonitorPanelDO {
private Long id;
@NotBlank(message = "学科名不能为空")
@Length(max = 64, message = "名称太长")
private String name;
@NotNull(message = "成绩不能为空")
private Double grade;
@NotBlank(message = "任课老师名不能为空")
private String teacherName;
private Timestamp creationTime;
private String creationName;
}
Dao
//实体类包含List<Class> 需要用BaseTypeHandler进行处理
public class SubjectHandler extends BaseTypeHandler<List<Subject>> {
@Override
public void setNonNullParameter(PreparedStatement ps, int i, List<Subject> parameter, JdbcType jdbcType) throws SQLException {
ps.setString(i, new Gson().toJson(parameter));
}
@Override
public List<Subject> getNullableResult(ResultSet rs, String columnName) throws SQLException {
String json = rs.getString(columnName);
if (!Strings.isNullOrEmpty(json)) {
return new Gson().fromJson(json, new TypeToken<List<Subject>>() {}.getType());
}
return null;
}
@Override
public List<Subject> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
String json = rs.getString(columnIndex);
if (!Strings.isNullOrEmpty(json)) {
return new Gson().fromJson(json, new TypeToken<List<Subject>>() {}.getType());
}
return null;
}
@Override
public List<Subject> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
String json = cs.getString(columnIndex);
if (!Strings.isNullOrEmpty(json)) {
return new Gson().fromJson(json,new TypeToken<List<Subject>>() {}.getType());
}
return null;
}
}
//
@Mapper
public interface IPanelDao {
@Select("<script> " +
"SELECT * FROM User WHERE id = #{id} " +
"</script>")
@Results({
@Result(property = "allSubject", column = "all_subject",
jdbcType = JdbcType.VARCHAR,
typeHandler = SubjectHandler.class)
})
User getById(Long id);
@Select("<script>" +
"SELECT * FROM User " +
"WHERE 1 = 1 " +
"<if test='ids != null'> " +
" AND id not in " +
" <foreach close=')' collection='ids' index='index' item='id' open='(' separator=','> " +
" #{id}" +
"</foreach> " +
"</if> " +
"<if test='keyword != null'> " +
"AND ( " +
"name LIKE concat('%',#{keyword},'%') " +
"OR id LIKE concat(#{keyword},'%') " +
"OR description LIKE concat('%',#{keyword},'%') " +
") " +
"</if> " +
"ORDER BY creation_time desc " +
"</script>")
@Results({
@Result(property = "allSubject", column = "all_subject",
jdbcType = JdbcType.VARCHAR,
typeHandler = SubjectHandler.class)
})
List<User> getBasePanelByKeyword(@Param("keyword") String keyword, @Param("ids") List<Long> ids);
@Insert({"INSERT INTO User (name, age,class,allSubject,description, creation_time, creation_name,update_time,update_name) " +
"VALUES (#{name},#{age}, #{class}, '+
"#{allSubject, javaType=com.ht.info.model.dto.Subject, typeHandler= com.ht.info.model.dao.handler.SubjectHandler}, " +
" #{description}, #{creationTime}, #{creationName}, #{updateTime},#{updateName})"})
@Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
@Results({
@Result(property = "allSubject", column = "all_subject",
jdbcType = JdbcType.VARCHAR,
typeHandler = SubjectHandler.class)
})
Long addUser(User user);
@Update("UPDATE User SET name = #{name},age= #{age},class = #{class},update_seq = #{updateSeq} + 1, " +
"allSubject = #{allSubject, javaType=com.ht.info.model.dto.Subject, typeHandler= com.ht.info.model.dao.handler.SubjectHandler}, " +
" description = #{description}, update_time = #{updateTime}, update_name = #{updateName} " +
"WHERE id = #{id} AND update_seq = #{updateSeq}")
@Results({
@Result(property = "allSubject", column = "all_subject",
jdbcType = JdbcType.VARCHAR,
typeHandler = SubjectHandler.class)
})
int updateUser(User user);
@Delete("DELETE FROM User WHERE id = #{id} and removed = true")
int deleteById(Long Id);
}
service层
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
public class PageQuery {
private String keyword;
private Integer pageNo;
private Integer pageSize;
private Long startTime;
private Long endTime;
private Timestamp startTimestamp;
private Timestamp endTimestamp;
public PageQuery(String keyword, Integer pageNo, Integer pageSize) {
this.keyword = keyword;
this.pageNo = pageNo;
this.pageSize = pageSize;
}
public void setStartTime(Long startTime) {
if (startTime != null) {
this.startTimestamp = new Timestamp(startTime);
}
}
public void setEndTime(Long endTime) {
if (endTime != null) {
this.endTimestamp = new Timestamp(endTime);
}
}
}
//需要配置分页插件# 分页配置
/*pagehelper:
helperDialect: mysql
offsetAsPageNum: true
rowBoundsWithCount: true
reasonable: true
returnPageInfo: true
*/
@Service
public class UserService implements IUserService {
@Autowired
private UserDao userDao;
@Override
public PageBean<User> findAdminByPage(PageQuery query) {
Integer currentPage = query.getPageNo();
Integer pageSize = query.getPageSize();
Page<User> page = PageHelper.startPage(currentPage, pageSize);
List<User> adminDOList =userDao.searchList(query.getKeyword());
PageBean<User> pageBean = new PageBean<>(currentPage, pageSize, page.getTotal());
pageBean.setItems(adminDOList);
return pageBean;
}