老师,麻烦帮我解决下这段报错,我弄了半天找不到原因
### 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 星