Spring data jpa

非数据库表字段映射查询

实体类中
import javax.persistence.Transient;

@ApiModelProperty(value = "年级组长id")
@Column(name = "teacher_id")
private Integer teacherId;

@Transient  //非数据库字段映射
@ApiModelProperty(value = "年级组长")
private String xm;

//添加有参构造  get,set方法
public BaseSchoolGrade(Integer teacherId,String xm) {
        this.teacherId = teacherId;
		this.xm = xm;
	}
	public String getXm() {
		return xm;
	}
	public void setXm(String xm) {
		this.xm = xm;
	}
	
//查询采用实体类的方式，内容顺序与有参构造一一对应
@Query(value = "SELECT new BaseSchoolGrade(s.teacherId,t.xm) FROM BaseSchoolGrade s LEFT JOIN BaseTeacherInfo t on s.teacherId = t.id AND t.ljsc = 0 where s.schoolId = ?1 and s.deleted = 0 and s.graduated= 0 ")
Page<BaseSchoolGrade> findGrades( Long schoolId, int deleted, int graduated,Pageable pageable);

JPA 分页查找

JPA 分页查找 pageable-1

方法1 
StringBuffer sql = new StringBuffer("SELECT * FROM `base_class_student` WHERE bjid =:bjid AND ljsc ='0'");

Map<String, Object> maps = new HashMap<String, Object>();
maps.put("bjid", bjid);
pageNumber = pageNumber -1;//前端从1计数，需减1
sql.append(" limit " + pageNumber * pageSize + ", " + pageSize);

List<BaseClassStudent> list = namedParameterJdbcTemplate.query(sql.toString(), maps,
				BeanPropertyRowMapper.newInstance(BaseClassStudent.class));

方法2
@ApiModel(value = "ApiSetDO")
@ApiModelProperty(value = "当前页", required = true)
private Integer pageNum;
@ApiModelProperty(value = "每页大小", required = true)
private Integer pageSize;

 // 前端从1计数，需减1
Pageable pageable = PageRequest.of(apiSetDO.getPageNum() - 1, apiSetDO.getPageSize());
// 查找出正常，未被删除的
return apiSetRepository.findByIsdel(0, pageable);

find in list 所查找的值在一个list中

find in list 所查找的值在一个list中
FIND_IN_SET

StringBuffer sqls = new StringBuffer(
				"SELECT s.* ,f.* FROM `base_studentinfo`  s " + 
				"left JOIN base_family f on f.student_id = s.id and f.deleted ='0' " + 
				"WHERE FIND_IN_SET(s.id,:studentId) and s.ljsc = '0' and s.move_type= '0' ORDER BY f.id asc LIMIT 1" );
Map<String, Object> mapss = new HashMap<String, Object>();

	//把list [1, 2, 3] 变成  1，2，3   放入sql中查找
mapss.put("studentId", studentId.toString().replace("[", "").replace("]", "").replace(" ", ""));
		
List<StudentFamilyMove> list2 = namedParameterJdbcTemplate.query(sqls.toString(), mapss,
				BeanPropertyRowMapper.newInstance(StudentFamilyMove.class));

Row_Number() OVER ()

低版本不支持Row_Number() OVER ()

SELECT s.*,f.name,f.mobile  FROM `base_studentinfo`  s 
LEFT JOIN 
	(
	SELECT * FROM
(
SELECT 
@rn:= CASE WHEN @student_id = student_id THEN @rn + 1 ELSE 1 END AS rn,
@student_id:= student_id as student_id,
name,mobile,id
FROM
(SELECT * from base_family WHERE deleted = 0 ORDER BY student_id, id asc) a
,(SELECT @rn=0, @student_id=0) b
)a WHERE rn <= 1
	)
	f on	f.student_id= s.id
WHERE FIND_IN_SET(s.id,'169,170,171,181') and s.ljsc = '0' and s.move_type= '0' 


高版本支持Row_Number() OVER ()
SELECT s.*,f.name,f.mobile  FROM `base_studentinfo`  s 
LEFT JOIN 
	(
	select * from (SELECT *, Row_Number() OVER (partition by studentId ORDER BY id desc) a FROM base_family )b WHERE b.a <= 1
	)
	f on	f.student_id= s.id
WHERE FIND_IN_SET(s.id,'169,170,171,181') and s.ljsc = '0' and s.move_type= '0' 

附表