热门图书排名
效果:
代码:
jQuery(grid_selector).jqGrid({
url: "book/rank",
datatype: "json",
mtype: "post",
height: 250,
colNames: ['编号', '书名', '类别', '作者', '价格', '借阅次数'],
colModel: [
/*{
name: 'myac', index: '', align: 'center', width: 80, fixed: true, sortable: false, resize: false,
formatter: 'actions',
formatoptions: {
keys: true,
delbutton: false,//disable delete button
editOptions: {recreateForm: true, beforeShowForm: beforeEditCallback}
//editformbutton:true, editOptions:{recreateForm: true, beforeShowForm:beforeEditCallback}
}
},*/
{name: 'bookId', index: '编号', width: 60, sorttype: "int", editable: true},
{
name: 'bookName',
index: '书名',
width: 180,
editable: true
},
{name: 'bookClass', index: '类别', width: 100, editable: true, editoptions: {size: "20"}},
{name: 'bookAuthor', index: '作者', width: 70, editable: true, editoptions: {size: "20"}},
{name: 'bookPrice', index: '价格', width: 60, editable: true, editoptions: {size: "20"}},
{name: 'times', index: '借阅次数', width: 150, editable: true}
],
pgtext: "第 {0} 页 {1}",
viewrecords: true,
recordtext: "当前 {0} - {1} 共 {2}",
jsonReader: {//分页的关键
id: "id",
root: "rows",
total: "total",
page: "page",
records: "records",
repeatitems: false
},
postData:{
'code':0
},
rowNum: 10,
rowList: [10, 20, 30],
pager: pager_selector,
altRows: true,
//toppager: true,
multiselect: true,
//multikey: "ctrlKey",
multiboxonly: true,
loadonce: false,
loadComplete: function () {
var table = this;
setTimeout(function () {
styleCheckbox(table);
updateActionIcons(table);
updatePagerIcons(table);
enableTooltips(table);
}, 0);
},
editurl: "book/editAndDel",//nothing is saved
// editData:[],
editData:{
bookId:1
},
/*serializeGridData:function(id){
var rowData = $(grid_selector).jqGrid('getRowData', id);
return JSON.stringify(rowData);
},*/
caption: "图书信息管理"
});
后端:
@RequestMapping("rank")
@ResponseBody
public JSONObject getBookRank(BookRankPage bookRankPage) {
JSONObject jsonData = new JSONObject();
if (bookRankPage.getCode() == 0){
List<BookRank> bookRanks = bookService.selectRank();
// 当前页
int pageStr = Integer.parseInt(bookRankPage.getPage());
// 页大小
int rowsStr = Integer.parseInt(bookRankPage.getRows());
// 记录总数
int records = bookRanks.size();
// 总页数
int total = (records%rowsStr==0) ? records/rowsStr : records/rowsStr+1;
// 返回内容
int start = (pageStr-1)*rowsStr;
int end = start + rowsStr;
JSONArray jsonArray = new JSONArray();
for(int i=start;i<=end && i<records;i++){
jsonArray.add(bookRanks.get(i));
}
// JSONArray array= JSONArray.parseArray(JSON.toJSONString(books));
jsonData.put("total", total);
jsonData.put("page", pageStr);
jsonData.put("records", records);
jsonData.put("rows", jsonArray);
}else {
}
return jsonData;
}
public class BookRankPage {
private Integer bookId;
private String bookName;
private Integer times;
private String rows;
private String page;
private int code;
…………
}
public class BookRank {
private Integer bookId;
private String bookName;
private String bookClass;
private String bookAuthor;
private String bookPrice;
private Integer times;
…………
}
BookMapper.xml
<resultMap id="BaseResultMapRank" type="com.usts.vo.BookRank">
<id column="book_id" property="bookId" />
<result column="book_name" property="bookName" />
<result column="book_class" property="bookClass" />
<result column="book_author" property="bookAuthor" />
<result column="book_price" property="bookPrice" />
<result column="times" property="times" />
</resultMap>
<select id="selectRank" parameterType="com.usts.vo.BookRank" resultMap="BaseResultMapRank">
SELECT book.book_id,book.book_name,book.book_class,book.book_author,book.book_price,count(borrow.book_id) as times
FROM borrow,book
WHERE borrow.book_id = book.book_id
GROUP BY book_id
ORDER BY times desc
</select>