老师,麻烦帮我解决下这段报错,我弄了半天找不到原因
### Error querying database. Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-3,3' at line 3 ### The error may exist in com/imooc/oa/dao/MessageDao.xml ### The error may involve com.imooc.oa.dao.MessageDao.selectAllByPage-Inline ### The error occurred while setting parameters ### SQL: SELECT * FROM message.message order by create_time desc limit ?,? ; ### Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-3,3' at line 3
<select id="selectAllByPage" parameterType="int" resultMap="message">
SELECT * FROM message.message
order by create_time desc
limit #{pager.firstParam},#{pager.pageSize} ;
</select>报错提示,页面刚开始加载时,由于 pager.firstParam 是-3 ,因此语法错误。
我的pager类是这么写的。(其实也是参考老师以前的代码抄下来的)
package com.imooc.oa.global;
public class Pager {
private int pageNum;
private int pageSize = 3;
private int totalCount;
private int totalPages;
private int firstParam;
public int getFirstParam() {
firstParam = (this.pageNum - 1) * this.pageSize;
return firstParam;
}
public int getPageSize() {
return pageSize;
}
public void setPageSize(int pageSize) {
this.pageSize = pageSize;
}
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
this.totalCount = totalCount;
}
public int getTotalPages() {
// totalPages=((totalCount%pageSize)==0)?(totalCount/pageSize):((totalCount/pageSize)+1);
return (this.totalCount - 1) / this.pageSize + 1;
}
public void setTotalPages(int totalPages) {
this.totalPages = totalPages;
}
public int getPageNum() {
pageNum = pageNum <= 0 ? 1 : pageNum;
pageNum = pageNum >= totalPages ? totalPages : pageNum;
return pageNum;
}
public void setPageNum(int pageNum) {
this.pageNum = pageNum;
}
}业务层:
package com.imooc.oa.biz.Impl;
import com.imooc.oa.biz.MessageBiz;
import com.imooc.oa.dao.MessageDao;
import com.imooc.oa.entity.Message;
import com.imooc.oa.entity.User;
import com.imooc.oa.global.Pager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.Date;
import java.util.List;
@Service("messageBiz")
public class MessageBizImpl implements MessageBiz {
@Autowired
private MessageDao messageDao;
public void add(User user, Message message) {
message.setUserId(user.getId());
message.setUsername(user.getName());
System.out.println(new Date());
message.setCreateTime(new Date());
messageDao.insert(message);
}
public void edit(Message message) {
message.setCreateTime(new Date());
messageDao.update(message);
}
public void remove(Long id) {
messageDao.delete(id);
}
public List<Message> getAllByUsername(String username) {
return messageDao.selectAllByUsername(username);
}
public Message getById(Long id) {
return messageDao.select(id);
}
public List<Message> getAll() {
return messageDao.selectAll();
}
public List<Message> getAllByPage(Pager pager) {
int count = messageDao.selectCountByPage();
System.out.println(count);
pager.setTotalCount(count);
return messageDao.selectAllByPage(pager);
}
public int getCountByPage() {
return messageDao.selectCountByPage();
}
}控制层:
package com.imooc.oa.controller;
import com.imooc.oa.biz.MessageBiz;
import com.imooc.oa.biz.UserBiz;
import com.imooc.oa.entity.Message;
import com.imooc.oa.entity.User;
import com.imooc.oa.global.Pager;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import java.util.Date;
import java.util.Map;
@Controller
@RequestMapping("/message")
public class MessageController {
@Autowired
private MessageBiz messageBiz;
@Autowired
private UserBiz userBiz;
@RequestMapping("/list")
public String list(HttpServletRequest request, HttpSession session, Map<String, Object> map) {
Pager pager = new Pager();
String page = request.getParameter("page");
if (!StringUtils.isEmpty(page)) {
System.out.println(page);
pager.setPageNum(Integer.parseInt(page));
}
// int page = (int) session.getAttribute("page");
// map.put("messages", messageBiz.getAll());
map.put("messages", messageBiz.getAllByPage(pager));
request.setAttribute("page", pager.getPageNum());
request.setAttribute("last", pager.getTotalPages());
return "message_list";
}
@RequestMapping("/addMessagePrompt")
public String addMessagePrompt(Map<String, Object> map) {
map.put("message", new Message());
return "add_message";
}
@RequestMapping("/addMessage")
public String addMessage(HttpSession session, Message message) {
User user = (User) session.getAttribute("user");
System.out.println(user.getId() + user.getName());
messageBiz.add(user, message);
return "redirect:/message/list";
}
@RequestMapping("/userInfo")
public String userInfo(HttpSession session, Map<String, Object> map) {
User user = (User) session.getAttribute("user");
map.put("user", userBiz.getByName(user.getName()));
return "user";
}
@RequestMapping("/list/my")
public String myList(HttpSession session, Map<String, Object> map) {
User user = (User) session.getAttribute("user");
map.put("messages", messageBiz.getAllByUsername(user.getName()));
return "message_list";
}
@RequestMapping(value = "/delete", params = "id")
public String delete(@RequestParam("id") Long id) {
messageBiz.remove(id);
return "redirect:/message/list";
}
@RequestMapping(value = "/to_editMessage", params = "id")
public String toEditMessage(Long id, Map<String, Object> map) {
Message message = messageBiz.getById(id);
map.put("message", message);
return "edit_message";
}
@RequestMapping(value = "/editMessage")
public String editMessage(Message message) {
System.out.println(new Date());
messageBiz.edit(message);
return "redirect:/message/list";
}
}持久层:
package com.imooc.oa.dao;
import com.imooc.oa.entity.Message;
import com.imooc.oa.global.Pager;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
@Repository("messageDao")
public interface MessageDao {
void insert(Message message);
void update(Message message);
void delete(Long id);
Message select(Long id);
List<Message> selectAll();
List<Message> selectAllByUsername(String username);
List<Message> selectAllByPage(@Param("pager") Pager pager);
int selectCountByPage();
}
<div id="pagefy">
<ul>
<form id="messageForm" action="/message/list.do" method="post" >
<input type="hidden" id="page" name="page" value="${page}">
<input type="hidden" id="last" name="last" value="${last}">
<li><a href="javascript:void(0)" onclick="submitMessageForm('first')">首页</a></li>
<li><a href="javascript:void(0)" onclick="submitMessageForm('pre')">上一页</a></li>
<li><a href="javascript:void(0)">当前第${page}页</a></li>
<li><a href="javascript:void(0)" onclick="submitMessageForm('next')">下一页</a></li>
<li><a href="javascript:void(0)" onclick="submitMessageForm('last')">尾页</a></li>
</form>
</ul>
</div>1
收起
正在回答
5回答

同学可以看一下老师的Pager里是做了判断的,如果pageIndex<=0 则让它等于1,如果不小于等于0,则让它等于原来的值。所以这里的pageIndex并不会是0。并且计算分页第一个参数的时候,调用的getPageIndex,所以分页的第一个参数不会是负数。
如果我的回答解决了你的疑惑,请采纳!祝学习愉快!
Java数据库开发与实战应用2018版
- 参与学习 人
- 提交作业 277 份
- 解答问题 4297 个
Java数据库开发的必备技能,从流行的MySQL数据库开始,到Java原生的数据库管理接口JDBC的使用,再到常用的数据持久化框架MyBatis,让你向Java工程师的目标又迈进了一步!
了解课程





恭喜解决一个难题,获得1积分~
来为老师/同学的回答评分吧
0 星