package cn.edu.ujn.servlet;import java.io.IOException;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import javax.servlet.ServletException;import javax.servlet.http.HttpServlet;import javax.servlet.http.HttpServletRequest;import javax.servlet.http.HttpServletResponse;import cn.edu.ujn.beans.Message;@SuppressWarnings("serial")public class ListServlet extends HttpServlet { private static String driver = "com.mysql.jdbc.Driver"; private static String url = "jdbc:mysql://localhost:3306/wechat"; private static String username = "root"; private static String password = "root"; @Override protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { try { String command = req.getParameter("command"); String description = req.getParameter("description"); req.setAttribute("command", command); req.setAttribute("description", description); Class.forName(driver); Connection conn = DriverManager.getConnection(url, username, password); StringBuilder sql = new StringBuilder("select id,command,description,content from message where 1=1"); List<String> paramList = new ArrayList<String>(); if (command!=null && !"".equals(command.trim())) { sql.append(" and command=?"); paramList.add(command); } if (description!=null && !"".equals(description.trim())) { sql.append(" and description like '%' ? '%'"); paramList.add(description); } PreparedStatement ps = conn.prepareStatement(sql.toString()); for(int i=0;i<paramList.size();i++) { ps.setString(i+1, paramList.get(i)); } ResultSet rs = ps.executeQuery(); List<Message> messageList = new ArrayList<Message>(); while(rs.next()) { Message message = new Message(); message.setId(rs.getInt("id")); message.setCommand(rs.getString("command")); message.setDescription(rs.getString("description")); message.setContent(rs.getString("content")); messageList.add(message); } req.setAttribute("messageList", messageList); } catch (ClassNotFoundException e) { // TODO Auto-generated catch block e.printStackTrace(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } req.getRequestDispatcher("WEB-INF/jsp/list.jsp").forward(req, resp); } //<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %> @Override protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { // TODO Auto-generated method stub this.doGet(req, resp); } }JSP<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%><%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><% String path = request.getContextPath(); String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%><html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> <meta http-equiv="X-UA-Compatible"content="IE=9; IE=8; IE=7; IE=EDGE" /> <title>内容列表页面</title> <link href="<%=basePath %>rescource/css/all.css" rel="stylesheet" type="text/css" /> </head> <body style="background: #e1e9eb;"> <form action="<%=basePath %>list.action" id="mainForm" method="post"> <div class="right"> <div class="current">当前位置：<a href="javascript:void(0)" style="color:#6E6E6E;">内容管理</a> > 内容列表</div> <div class="rightCont"> <p class="g_title fix">内容列表 <a class="btn03" href="#">新 增</a> <a class="btn03" href="#">删 除</a></p> <table class="tab1"> <tbody> <tr> <td width="90" align="right">指令语句：</td> <td> <input name="command" type="text" class="allInput" value="${command }"/> </td> <td width="90" align="right">描述：</td> <td> <input name="description" type="text" class="allInput" value="${description }"/> </td> <td width="85" align="right"><input type="submit" class="tabSub" value="查 询" /></td> </tr> </tbody> </table> <div class="zixun fix"> <table class="tab2" width="100%"> <tbody> <tr> <th><input type="checkbox" id="all" onclick="#"/></th> <th>序号</th> <th>指令</th> <th>描述</th> <th>内容</th> <th>操作</th> </tr> <c:forEach items="${messageList}" var="message" varStatus="status"> <tr <c:if test="${status.index%2!=0 }">style="background-color:#ECF6EE;"</c:if>> <td><input type="checkbox" /></td> <td>${status.index+1}</td> <td>${message.command }</td> <td>${message.description }</td> <td>${message.content}</td> <td> <a href="#">修改</a> <a href="#">删除</a> </td> </tr> </c:forEach> </tbody> </table> <div class='page fix'> 共 <b>4</b> 条 <a href='###' class='first'>首页</a> <a href='###' class='pre'>上一页</a> 当前第<span>1/1</span>页 <a href='###' class='next'>下一页</a> <a href='###' class='last'>末页</a> 跳至 <input type='text' value='1' class='allInput w28' /> 页 <a href='###' class='go'>GO</a> </div> </div> </div> </div> </form> </body></html>
1 回答
大咪
TA贡献785条经验 获得超332个赞
教你一个方法去验证sql语句对不对，你把没拼接的那个原语句放数据库里执行下，看看能不能查询出来结果，要是可以说明拼接之前的语句没错，那就检查拼接后的那个sql语句
添加回答
举报
0/150
提交
取消