If you are using Spring MyBatis, you can achieve pagination manually using 2 MyBatis queries and the useful Spring Page and Pageable interfaces.
You create a higher level DAO interface e.g. UploadDao
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
public interface UploadDao {
Page<Upload> search(UploadSearch uploadSearch, Pageable pageable);
}
... where Upload maps to an upload table and UploadSearch is a parameter POJO e.g.
@Data // lombok
public class UploadSearch {
private Long userId;
private Long projectId;
...
}
An implementation of UploadDao (which injects a MyBatis UploadMapper mapper) is as follows:
public class DefaultUploadDao implements UploadDao {
@Autowired
private UploadMapper uploadMapper;
public Page<Upload> searchUploads(UploadSearch uploadSearch, Pageable pageable) {
List<Upload> content = uploadMapper.searchUploads(uploadSearch, pageable);
Long total = uploadMapper.countUploads(uploadSearch);
return new PageImpl<>(content, pageable, total);
}
}
The DAO implementation calls 2 methods of UploadMapper. These are:
UploadMapper.searchUploads - returns a page of results based on search param (UploadSearch) and Pageable param (contains offset / limit etc).UploadMapper.countUploads - returns total count, again based on search param UploadSearch. NOTE - Pageable param is not required here as we're simply determining the total rows the search parameter filters to and don't care about page number / offset etc.
The injected UploadMapper interface looks like ...
@Mapper
public interface UploadMapper {
List<Upload> searchUploads(
@Param("search") UploadSearch search,
@Param("pageable") Pageable pageable);
long countUploads(
@Param("search") UploadSearch search);
}
... and the mapper XML file containing the dynamic SQL e.g. upload_mapper.xml contains ...
<mapper namespace="com.yourproduct.UploadMapper">
<select id="searchUploads" resultType="com.yourproduct.Upload">
select u.*
from upload u
<include refid="queryAndCountWhereStatement"/>
<if test="pageable.sort.sorted">
<trim prefix="order by">
<foreach item="order" index="i" collection="pageable.sort" separator=", ">
<if test="order.property == 'id'">id ${order.direction}</if>
<if test="order.property == 'projectId'">project_id ${order.direction}</if>
</foreach>
</trim>
</if>
<if test="pageable.paged">
limit #{pageable.offset}, #{pageable.pageSize}
</if>
<!-- NOTE: PostgreSQL has a slightly different syntax to MySQL i.e.
limit #{pageable.pageSize} offset #{pageable.offset}
-->
</select>
<select id="countUploads" resultType="long">
select count(1)
from upload u
<include refid="queryAndCountWhereStatement"/>
</select>
<sql id="queryAndCountWhereStatement">
<where>
<if test="search != null">
<if test="search.userId != null"> and u.user_id = #{search.userId}</if>
<if test="search.productId != null"> and u.product_id = #{search.productId}</if>
...
</if>
</where>
</sql>
</mapper>
NOTE - <sql> blocks (along with <include refid=" ... " >) are very useful here to ensure your count and select queries are aligned. Also, when sorting we are using conditions e.g. <if test="order.property == 'projectId'">project_id ${order.direction}</if> to map to a column (and stop SQL injection). The ${order.direction} is safe as the Spring Direction class is an enum.
The UploadDao could then be injected and used from e.g. a Spring controller:
@RestController("/upload")
public UploadController {
@Autowired
private UploadDao uploadDao; // Likely you'll have a service instead (which injects DAO) - here for brevity
@GetMapping
public Page<Upload>search (@RequestBody UploadSearch search, Pageable pageable) {
return uploadDao.search(search, pageable);
}
}
