SpringBoot 整合 Spring-Data-JPA 的时候出现此问题:
前端 js 代码
$(function () {
var searchText = $('.search').find('input').val()
var columns = [];
columns.push({
title: '分类',
field: 'category',
align: 'center',
valign: 'middle',
formatter: function (value, row, index) {
return value
}
}, {
title: '美图',
field: 'url',
align: 'center',
valign: 'middle',
formatter: function (value, row, index) {
return "![](" + value + ")"
}
})
$.extend($.fn.bootstrapTable.defaults, $.fn.bootstrapTable.locales['zh-CN']);
$('#meituFavoriteTable').bootstrapTable({
url: 'meituSearchFavoriteJson',
sidePagination: "server",
queryParamsType: 'page,size',
contentType: "application/x-www-form-urlencoded",
method: 'get',
striped: false, //是否显示行间隔色
cache: false, //是否使用缓存,默认为true,所以一般情况下需要设置一下这个属性(*)
pagination: true, //是否显示分页(*)
paginationLoop: true,
paginationHAlign: 'right', //right, left
paginationVAlign: 'bottom', //bottom, top, both
paginationDetailHAlign: 'left', //right, left
paginationPreText: ' 上一页',
paginationNextText: '下一页',
search: true,
searchText: searchText,
searchTimeOut: 500,
searchAlign: 'right',
searchOnEnterKey: false,
trimOnSearch: true,
sortable: true, //是否启用排序
sortOrder: "desc", //排序方式
sortName: "id",
pageNumber: 1, //初始化加载第一页,默认第一页
pageSize: 10, //每页的记录行数(*)
pageList: [5, 10, 20, 50, 100], // 可选的每页数据
totalField: 'totalPages',
dataField: 'content', //后端 json 对应的表格数据 key
columns: columns,
queryParams: function (params) {
return {
size: params.pageSize,
page: params.pageNumber,
sortName: params.sortName,
sortOrder: params.sortOrder,
searchText: params.searchText
}
},
classes: 'table table-responsive full-width',
})
$(document).on('keydown', function (event) {
// 键盘翻页事件
var e = event || window.event || arguments.callee.caller.arguments[0];
if (e && e.keyCode == 38 || e && e.keyCode == 37) {//上,左
// 上一页
$('.page-pre').click()
}
if (e && e.keyCode == 40 || e && e.keyCode == 39) {//下,右
// 下一页
$('.page-next').click()
}
})
})
后端接口
package com.easy.kotlin.controller
import com.easy.kotlin.chapter11_kotlin_springboot.dao.ImageRepository
import com.easy.kotlin.chapter11_kotlin_springboot.entity.Image
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.data.domain.Page
import org.springframework.data.domain.PageRequest
import org.springframework.data.domain.Sort
import org.springframework.stereotype.Controller
import org.springframework.web.bind.annotation.RequestMapping
import org.springframework.web.bind.annotation.RequestMethod
import org.springframework.web.bind.annotation.RequestParam
import org.springframework.web.bind.annotation.ResponseBody
import org.springframework.web.servlet.ModelAndView
/**
* Created by jack on 2017/7/22.
*/
@Controller
class MeituController {
@Autowired
var imageRepository: ImageRepository? = null
@RequestMapping(value = "meituJson", method = arrayOf(RequestMethod.GET))
@ResponseBody
fun wotuJson(@RequestParam(value = "page", defaultValue = "0") page: Int,
@RequestParam(value = "size", defaultValue = "10") size: Int): Page<Image>? {
return getPageResult(page, size)
}
@RequestMapping(value = "meituSearchJson", method = arrayOf(RequestMethod.GET))
@ResponseBody
fun wotuSearchJson(@RequestParam(value = "page", defaultValue = "0") page: Int,
@RequestParam(value = "size", defaultValue = "10") size: Int,
@RequestParam(value = "searchText", defaultValue = "") searchText: String): Page<Image>? {
return getPageResult(page, size, searchText)
}
@RequestMapping(value = "meituSearchFavoriteJson", method = arrayOf(RequestMethod.GET))
@ResponseBody
fun wotuSearchFavoriteJson(@RequestParam(value = "page", defaultValue = "0") page: Int,
@RequestParam(value = "size", defaultValue = "10") size: Int,
@RequestParam(value = "searchText", defaultValue = "") searchText: String): Page<Image>? {
return getFavoritePageResult(page, size, searchText)
}
@RequestMapping(value = "meituView", method = arrayOf(RequestMethod.GET))
fun meituView(): ModelAndView {
return ModelAndView("meituView")
}
@RequestMapping(value = "meituFavoriteView", method = arrayOf(RequestMethod.GET))
fun meituFavoriteView(): ModelAndView {
return ModelAndView("meituFavoriteView")
}
private fun getPageResult(page: Int, size: Int): Page<Image>? {
val sort = Sort(Sort.Direction.DESC, "id")
val pageable = PageRequest(page, size, sort)
return imageRepository?.findAll(pageable)
}
private fun getPageResult(page: Int, size: Int, searchText: String): Page<Image>? {
val sort = Sort(Sort.Direction.DESC, "id")
val pageable = PageRequest(page, size, sort)
if (searchText == "") {
return imageRepository?.findAll(pageable)
} else {
return imageRepository?.search(searchText, pageable)
}
}
private fun getFavoritePageResult(page: Int, size: Int, searchText: String): Page<Image>? {
val sort = Sort(Sort.Direction.DESC, "id")
val pageable = PageRequest(page, size, sort)
if (searchText == "") {
val allFavorite = imageRepository?.findAllFavorite(pageable)
return allFavorite
} else {
val searchFavorite = imageRepository?.searchFavorite(searchText, pageable)
return searchFavorite
}
}
}
package com.easy.kotlin.chapter11_kotlin_springboot.dao
import com.easy.kotlin.chapter11_kotlin_springboot.entity.Image
import org.springframework.data.domain.Page
import org.springframework.data.domain.Pageable
import org.springframework.data.jpa.repository.Query
import org.springframework.data.repository.PagingAndSortingRepository
import org.springframework.data.repository.query.Param
/** Created by jack on 2017/7/17.
@Query注解里面的value和nativeQuery=true,意思是使用原生的sql查询语句.
sql模糊查询like语法,我们在写sql的时候是这样写的
like '%?%'
但是在@Query的value字符串中, 这样写
like %?1%
*/
interface ImageRepository : PagingAndSortingRepository<Image, Long> {
@Query("SELECT a from #{#entityName} a where a.isDeleted=0 and a.category like %?1%")
fun findByCategory(category: String): MutableList<Image>
@Query("select count(*) from #{#entityName} a where a.isDeleted=0 and a.url = ?1")
fun countByUrl(url: String): Int
@Query("SELECT a from #{#entityName} a where a.isDeleted=0 and a.category like %:searchText%")
fun search(@Param("searchText") searchText: String, pageable: Pageable): Page<Image>
@Query("SELECT a from #{#entityName} a where a.isDeleted=0 and a.isFavorite=1")
fun findAllFavorite(pageable: Pageable): Page<Image>
@Query("SELECT a from #{#entityName} a where a.isDeleted=0 and a.isFavorite=1 and a.category like %:searchText%")
fun searchFavorite(@Param("searchText") searchText: String, pageable: Pageable): Page<Image>
}
前台请求第一页的数据, 参数 &page=1&start=0&limit=50
后台得不到数据, 并提示 Page 1 of 1 containing UNKNOWN instances
原因page从0开始不是从1开始
解决方式把page修改为0
...
pageNumber: 0, //初始化加载第一页,默认第一页
pageSize: 10, //每页的记录行数(*)
...
https://github.com/EasyKotlin/chatper15_net_io_img_crawler
参考连接:
Page 1 of 1 containing UNKNOWN instances when findByUserNameLike(String userName,Pageable pageable)