JDBC+Servlet+JSP实现简单的数据遍历和查找功能

package com.liuyanzhao.bean;

/**

 * 与消息对应的实体类

 */

public class Message {

    private int id;

    private String command;

    private String description;

    private String content;

    public Message() {

    }

    public int getId() {

        return id;

    }

    public void setId(int id) {

        this.id = id;

    }

    public String getCommand() {

        return command;

    }

    public void setCommand(String command) {

        this.command = command;

    }

    public String getDescription() {

        return description;

    }

    public void setDescription(String description) {

        this.description = description;

    }

    public String getContent() {

        return content;

    }

    public void setContent(String content) {

        this.content = content;

    }

}

package com.liuyanzhao.servlet;

import com.liuyanzhao.service.ListService;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import java.io.IOException;

/**

 * 列表页面初始化控制

 */

public class ListServlet extends HttpServlet {

    @Override

    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

        //解决乱码

        request.setCharacterEncoding("utf-8");

        //接受表单内容

        String command = request.getParameter("command");

        String description = request.getParameter("description");

        //向页面传值

        request.setAttribute("command",command);

        request.setAttribute("description",description);

        //业务需要

        ListService listService = new ListService();

        //查询消息列表并传给页面

        request.setAttribute("messageList",listService.queryMessageList(command,description));

        //向页面跳转

        request.getRequestDispatcher("/WEB-INF/jsp/back/list.jsp").forward(request,response);

    }

    @Override

    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {

        this.doGet(req, resp);

    }

}

package com.liuyanzhao.dao;

import com.liuyanzhao.bean.Message;

import java.sql.*;

import java.util.ArrayList;

import java.util.List;

/**

 * 和message表相关的操作

 */

public class MessageDao {

    /**

     * 根据查询条件查询消息列表

     */

    public List<Message> queryMessageList(String command,String description) {

        List<Message> messageList = new ArrayList<Message>();

        //数据库连接

        try {

            Class.forName("com.mysql.jdbc.Driver");

            Connection conn = null;

            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/micro_message" +

                "?useUnicode=true&characterEncoding=utf8","root","");

            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 ptmt = conn.prepareStatement(sql.toString());

            for (int i=0;i<paramList.size();i++) {

                ptmt.setString(i+1,paramList.get(i));

            }

            ResultSet rs =  ptmt.executeQuery();

            while (rs.next()) {

                Message message = new Message();

                messageList.add(message);

                message.setId(rs.getInt("id"));

                message.setCommand(rs.getString("command"));

                message.setDescription(rs.getString("description"));

                message.setContent(rs.getString("content"));

            }

        } catch (ClassNotFoundException e) {

            e.printStackTrace();

        } catch (SQLException e) {

            e.printStackTrace();

        }

        return messageList;

    }

}

package com.liuyanzhao.service;

import com.liuyanzhao.bean.Message;

import com.liuyanzhao.dao.MessageDao;

import java.util.List;

/**

 *列表相关的业务功能

 */

public class ListService {

    public List<Message> queryMessageList(String command, String description) {

        MessageDao messageDao = new MessageDao();

        return messageDao.queryMessageList(command,description);

    }

}