package 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 bean.Message;
/**
* @author 570
*
*/
public class ListServlet extends HttpServlet {
/**
*
*/
private static final long serialVersionUID = 1L;
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
try {
String command = req.getParameter("command");//从页面拿到参数
String description =req.getParameter("description");
Class.forName("com.mysql.jdbc.Driver");
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb?characterEncoding=utf8&useSSL=true","root","root");
//String sql ="SELECT ID,COMMAND,DESCRIPTION,CONTENT FROM imooc_mybatis_list";
//where 1=1 用于语句拼接。。不明所以
StringBuilder sql = new StringBuilder("select ID,COMMAND,DESCRIPTION,CONTENT from imooc_mybatis_list where 1=1");
PreparedStatement ps = conn.prepareStatement(sql.toString());
//多条件查询时，利用List做缓冲，保存查询参数，然后再循环赋值
List<String> paramer = new ArrayList<String>();
//语句拼接如果查询的指令非空且非空字符串则执行语句拼接查询
if (command!=null&&!"".equals(command.trim())) {
sql.append("and COMMAND=?");
paramer.add(command);
}
if (description!=null&&!"".equals(description.trim())) {
sql.append("and DESCRIPTION like '%' ? '%'");
paramer.add(description);
}
//把页面上的语句拿给要送到数据库查询的ps中
for (int i = 0; i < paramer.size(); i++) {
ps.setString(i+1, paramer.get(i));
}
ResultSet rs = ps.executeQuery();
List<Message> messageList = new ArrayList<Message>();
while (rs.next()) {
Message message = new Message();
messageList.add(message);
message.setId(rs.getString("ID"));
message.setCommand(rs.getString("COMMADN"));
message.setDescription(rs.getString("DESCRIPTION"));
message.setContent(rs.getString("CONTENT"));
}
req.setAttribute("messageList", messageList);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
req.getRequestDispatcher("/WEB-INF/jsp/list.jsp").forward(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
this.doGet(req, resp);
}
}<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<%@ 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">
<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 %>resource/css/all.css" rel="stylesheet" type="text/css" />
</head>
<body style="background: #e1e9eb;">
<form action="<%=basePath %>list" 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=""/>
</td>
<td width="90" align="right">描述</td>
<td>
<input name="description" type="text" class="allInput" value=""/>
</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>
</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>
