package Servlet;
import java.io.IOException;
import java.io.PrintWriter;
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;
@SuppressWarnings({ "serial", "unused" })
public class ListServlet extends HttpServlet {
private static String url = "jdbc:MYSQL://127.0.0.1:3306/micro_message";
private static String user = "root";
private static String password = "123456";
/**
* Constructor of the object.
*/
public ListServlet() {
super();
}
public void destroy() {
super.destroy(); // Just puts "destroy" string in log
// Put your code here
}
public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
try {
Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;
try {
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
String command=request.getParameter("command");
String description=request.getParameter("description");
request.setAttribute("command", command);
request.setAttribute("description", description);
conn = DriverManager.getConnection(url,user,password);
StringBuilder sql=new StringBuilder("select * from message where 1=1");
List<String> paramList=new ArrayList<String>();
if(command!=null&&!"".equals(command.trim())){
sql.append(" and command='"+command+"' ");
paramList.add(command);
}
if(description!=null&&!"".equals(description.trim())){
sql.append(" and DESCRIPTION like '%' ? '%'");
paramList.add(description);
}
PreparedStatement statement=conn.prepareStatement(sql.toString());
for(int i = 1;i<paramList.size();i++){
statement.setString(i, paramList.get(i));
}
ResultSet rs=statement.executeQuery(sql.toString());
List<Message> messageList=new ArrayList<Message>();
while(rs.next()){
Message message=new Message();
message.setId(rs.getInt("id"));
message.setCommand(rs.getString("command"));
message.setContent(rs.getString("content"));
message.setDescription(rs.getString("description"));
messageList.add(message);
}
request.setAttribute("messageList", messageList);
} catch (SQLException e) {
e.printStackTrace();
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
request.getRequestDispatcher("/WEB-INF/jsp/back/List.jsp").forward(request, response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
this.doGet(request, response);
}
public void init() throws ServletException {
// Put your code here
}
}报错是 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 '? '%'' at line 1
sql.append(" and command='"+command+"' ");这一句写错了;
改成sql.append(" and command= ? "); 或者sql.append(" and command="+command+" ");
控制for循环
1,如果存在description,不存在command,就循环一次,给参数赛值;
2,如果description,command都存在,循环一次,给第二个参数赛值;
3,如果command存在,description不存在,就不要循环,不要进行赛值;
3,如果command,description都不存在,也不要进行参数赛值;
备注:这里可以不用循环,可以直接取第几个参数,得到,并进行赛值;
如果command为空,description为不为空,然后是不会进入for循环,这时,就不会statement.setString(i, paramList.get(i)); ->然后就会报sql错误