实训18

一.完善模糊查询

因为打的代码越来越多，为了后期看起来不会很乱，所以今天我们把seach方法删除了
其他改动的比较多
文件创建如下

我把代码都贴在下面
IConsumerDao中代码

public interface IConsumerDao {
    Consumer findConsumerByname(String name);
    List<Consumer> findAll();
    void delete(int id);
    void add(String name,String password);
    Consumer findConsumerById(int id);
    void update(int id,String name,String password);
    List<Consumer> findByPage(String name,int start,int size);
    int selectCount();
}

ConsumerDaoImpl中代码

ublic class ConsumerDaoImpl implements IConsumerDao {
    public Consumer findConsumerByname(String name) {
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        Connection connection = null;
        Consumer consumer = null;
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from consumer where name=?";
            statement = connection.prepareStatement(sql);
            statement.setString(1, name);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                consumer = new Consumer();
                consumer.setId(resultSet.getInt(1));
                consumer.setName(name);
                consumer.setPassword(resultSet.getString(3));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(resultSet, statement, connection);
        }
        return consumer;
    }

    @Override
    public List<Consumer> findAll() {
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        Connection connection = null;
        List<Consumer> consumers = new ArrayList<>();
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from consumer";
            statement = connection.prepareStatement(sql);

            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                Consumer consumer = new Consumer();
                consumer.setId(resultSet.getInt(1));
                consumer.setName(resultSet.getString(2));
                consumer.setPassword(resultSet.getString(3));
                consumers.add(consumer);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(resultSet, statement, connection);
        }
        return consumers;
    }

    @Override
    public void delete(int id) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DBUtil.getConnection();
            statement = connection.prepareStatement("delete from consumer where id=?");
            statement.setInt(1, id);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(null, statement, connection);
        }
    }

    @Override
    public void add(String name, String password) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = DBUtil.getConnection();
            statement = connection.prepareStatement("insert into consumer(name,password) values (?,?)");
            statement.setString(1, name);
            statement.setString(2, password);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(null, statement, connection);
        }
    }

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
@Override
    public Consumer findConsumerById(int id) {
        Consumer consumer = null;
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        Connection connection = null;

        try {
            connection = DBUtil.getConnection();
            String sql = "select * from consumer where id=?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                consumer = new Consumer();
                consumer.setId(resultSet.getInt(1));
                consumer.setName(resultSet.getString(2));
                consumer.setPassword(resultSet.getString(3));
               }
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                DBUtil.closeAll(resultSet, statement, connection);
            }
            return consumer;
    }


    @Override
    public void update(int id, String name, String password) {
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        try {
            connection=DBUtil.getConnection();
            String sql = "update consumer set name=?,password=? where id=?";
            statement=connection.prepareStatement(sql);
            statement.setInt(3,id);
            statement.setString(1,name);
            statement.setString(2,password);
            statement.executeUpdate();

        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(null,statement,connection);
        }
    }

    @Override
    public List<Consumer> findByPage(String name,int start, int size) {
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        Connection connection = null;
        List<Consumer> consumers = new ArrayList<>();
        try {
            connection = DBUtil.getConnection();
            if (name==null){
                String sql = "select * from consumer limit ?,?";
                statement = connection.prepareStatement(sql);
                statement.setInt(1,start);
                statement.setInt(2,size);
            }else {
                String sql = "select * from consumer where name like ? limit ?,?";
                statement = connection.prepareStatement(sql);
                statement.setString(1,"%"+name+"%");
                statement.setInt(2,start);
                statement.setInt(3,size);
            }
            resultSet=statement.executeQuery();
            while (resultSet.next()) {
                Consumer consumer = new Consumer();
                consumer.setId(resultSet.getInt(1));
                consumer.setName(resultSet.getString(2));
                consumer.setPassword(resultSet.getString(3));
                consumers.add(consumer);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(resultSet, statement, connection);
        }
        return consumers;
    }

    @Override
    public int selectCount() {
        ResultSet resultSet = null;
        PreparedStatement statement = null;
        Connection connection = null;
        List<Consumer> consumers = new ArrayList<>();
        int count = 0;
        try {
            connection = DBUtil.getConnection();
            String sql = "select count(*) from consumer";
            statement = connection.prepareStatement(sql);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                count = resultSet.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(resultSet, statement, connection);
        }
        return count;
    }
}

PageInfo中代码

public class PageInfo{
    private int currentPage;
    private int size;
    private List<Consumer> list;
    private int totalPage;
    private int totalCount;

    public int getCurrentPage() {
        return currentPage;
    }

    public void setCurrentPage(int currentPage) {
        this.currentPage = currentPage;
    }

    public int getSize() {
        return size;
    }

    public void setSize(int size) {
        this.size = size;
    }

    public List<Consumer> getList() {
        return list;
    }

    public void setList(List<Consumer> list) {
        this.list = list;
    }

    public int getTotalPage() {
        return totalPage;
    }

    public void setTotalPage(int totalPage) {
        this.totalPage = totalPage;
    }

    public int getTotalCount() {
        return totalCount;
    }

    public void setTotalCount(int totalCount) {
        this.totalCount = totalCount;
    }

    @Override
    public String toString() {
        return "PageInfo{" +
                "currentPage=" + currentPage +
                ", size=" + size +
                ", list=" + list +
                ", totalPage=" + totalPage +
                ", totalCount=" + totalCount +
                '}';
    }
}

IUserService中代码

public interface IUserService {
    Boolean login(String name, String password);
    List<Consumer> findAll();
    void delete(int id);
    void add(String name,String password);
    Consumer findConsumerById(int id);
    void update(int id,String name,String password);
    PageInfo findByPage(String name,int currentPage,int size);
    }

UserServiceImpl中代码

public class UserServiceImpl implements IUserService {
    IConsumerDao iConsumerDao=new ConsumerDaoImpl();
    @Override
    public Boolean login(String name, String password) {
        Consumer consumer=iConsumerDao.findConsumerByname(name);
        if (consumer!=null&&consumer.getPassword().equals(password)){
            return true;
        }else {
            return false;
        }
    }

    @Override
    public List<Consumer> findAll() {
        List<Consumer>consumers = new ArrayList<>();
        return consumers;
    }

    @Override
    public void delete(int id) {
       iConsumerDao.delete(id);
    }

    @Override
    public void add(String name, String password) {
        iConsumerDao.add(name, password);
    }

    @Override
    public Consumer findConsumerById(int id) {
        return iConsumerDao.findConsumerById(id);
    }

    @Override
    public void update(int id, String name, String password) {
        iConsumerDao.update(id,name,password);
    }

    @Override
    public PageInfo findByPage(String searchname,int currentPage, int size) {
        PageInfo pageInfo = new PageInfo();
        pageInfo.setCurrentPage(currentPage);
        pageInfo.setSize(size);
        int start=(currentPage-1)*size;
        List<Consumer> consumers=iConsumerDao.findByPage(searchname,start,size);
        pageInfo.setList(consumers);
        int count=iConsumerDao.selectCount();
        pageInfo.setTotalCount(count);
        Double c=Double.valueOf(count);
        Double ceil=Math.ceil(c/size);
        pageInfo.setTotalPage(ceil.intValue());
        return pageInfo;
    }
}

AddServlet中代码

public class AddServlet extends HttpServlet {
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String name=req.getParameter("username");
        String password=req.getParameter("password");
        IUserService userService=new UserServiceImpl();
        userService.add(name,password);
        req.getRequestDispatcher("/findByPage").forward(req,resp);
    }
}

DeleteServlet中代码

public class DeleteServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String id1=req.getParameter("id");
        int id2=Integer.parseInt(id1);
        IUserService userService=new UserServiceImpl();
        userService.delete(id2);
        String searchname=req.getParameter("searchname");
        PageInfo pageInfo=userService.findByPage(searchname,1,5);
        HttpSession session=req.getSession();
        session.setAttribute("pageInfo",pageInfo);
        req.getRequestDispatcher("/allUser.jsp").forward(req, resp);
    }
}

FindByPageServlet中代码

public class FindByPageServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        HttpSession session=req.getSession();
        String searchname=(String) session.getAttribute("searchname");
        String c=req.getParameter("currentPage");
        int currentPage=Integer.parseInt(c);
        String s=req.getParameter("size");
        int size=Integer.parseInt(s);
        IUserService userService=new UserServiceImpl();
        PageInfo pageInfo=null;
        pageInfo=userService.findByPage(searchname,currentPage,size);
        session.setAttribute("pageInfo",pageInfo);
        req.getRequestDispatcher("allUser.jsp").forward(req,resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String searchname=req.getParameter("searchname");
        int currentPage=1;
        int size=5;
        IUserService userService=new UserServiceImpl();
        PageInfo pageInfo=null;
        pageInfo=userService.findByPage(searchname,currentPage,size);
        HttpSession session=req.getSession();
        session.setAttribute("searchname",searchname);
        session.setAttribute("pageInfo",pageInfo);
        req.getRequestDispatcher("allUser.jsp").forward(req,resp);
    }
}

LoginServlet中代码

public class LoginServlet extends HttpServlet {

@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
    String name = req.getParameter("username");
    String password = req.getParameter("password");
    IUserService userService=new UserServiceImpl();
    Boolean flag=userService.login(name,password);
    if (flag) {
        req.getRequestDispatcher("/findByPage").forward(req, resp);
    } else {
        req.getRequestDispatcher("fail.jsp").forward(req,resp);
    }

}
}

UpdateServlet中代码

public class UpdateServlet extends HttpServlet {
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
IUserService userService = new UserServiceImpl();
int id = Integer.parseInt(req.getParameter(“id”));
String name=req.getParameter(“username”);
String password=req.getParameter(“password”);
userService.update(id,name,password);
req.getRequestDispatcher("/findByPage").forward(req, resp);
}

}

Test中代码

public class Test {
public static void main(String[] args){
int count=1;
int size=5;
Double c=Double.valueOf(count);
System.out.println©;
double a=c/size;
System.out.println(a);
double ceil=Math.ceil(a);
System.out.println(ceil);
}
}

web中代码

<servlet>
        <servlet-name>LoginServlet</servlet-name>
        <servlet-class>com.zhongruan.servlet.LoginServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>LoginServlet</servlet-name>
        <url-pattern>/login</url-pattern>
    </servlet-mapping>

    <servlet>
        <servlet-name>DeleteServlet</servlet-name>
        <servlet-class>com.zhongruan.servlet.DeleteServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>DeleteServlet</servlet-name>
        <url-pattern>/delete</url-pattern>
    </servlet-mapping>
    
    <servlet>
        <servlet-name>AddServlet</servlet-name>
        <servlet-class>com.zhongruan.servlet.AddServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>AddServlet</servlet-name>
        <url-pattern>/add</url-pattern>
    </servlet-mapping>

    
    <servlet>
        <servlet-name>ToUpdateServlet</servlet-name>
        <servlet-class>com.zhongruan.servlet.ToUpdateServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>ToUpdateServlet</servlet-name>
        <url-pattern>/toUpdate</url-pattern>
    </servlet-mapping>
    
    <servlet>
        <servlet-name>UpDateServlet</servlet-name>
        <servlet-class>com.zhongruan.servlet.UpdateServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>UpDateServlet</servlet-name>
        <url-pattern>/update</url-pattern>
    </servlet-mapping>

    <servlet>
        <servlet-name>FindPage</servlet-name>
        <servlet-class>com.zhongruan.servlet.FindByPageServlet</servlet-class>
    </servlet>
    <servlet-mapping>
        <servlet-name>FindPage</servlet-name>
        <url-pattern>/findByPage</url-pattern>
    </servlet-mapping>

allUser中代码

<body style="background-image: url(images/p2.jpg);background-size: 100%">
<div class="container">
    <div class="row clearfix">
        <div class="col-md-12 column">
            <div class="page-header">
                <h1>
                    基于servlet+jsp框架的管理系统：简单实现增、删、改、查。
                </h1>
            </div>
        </div>
    </div>

    <div class="row clearfix">
        <div class="col-md-12 column">
            <div class="page-header">
                <h1>
                    <small>用户列表 —— 显示所有用户</small>
                </h1>
            </div>
        </div>
    </div>
    <div class="row">
        <div class="col-md-4 column">
            <a class="btn btn-primary" href="addUser.jsp">新增</a>
        </div>

        <form action="/findByPage" method="post">
            <input name="searchname" type="text">
            <input type="submit" value="搜索">
        </form>

    </div>
    <br/>

    <br/>
    <div class="row clearfix">
        <div class="col-md-12 column">
            <table class="table table-hover table-striped">
                <thead>
                <tr>
                    <th>id</th>
                    <th>用户名</th>
                    <th>密码</th>
                    <th>操作</th>
                </tr>
                </thead>
                <tbody>
                <c:forEach var="consumer" items="${pageInfo.list}">
                    <tr>
                    <td>${consumer.id}</td>
                    <td>${consumer.name}</td>
                    <td>${consumer.password}</td>
                    <td><a href="/delete?id=${consumer.id}">删除</a>|
                        <a href="/toUpdate?id=${consumer.id}">修改</a></td>
                    </tr>
                </c:forEach>
                </tbody>
            </table>
        </div>
    </div>
    <div>
        <a href="/findByPage?currentPage=1&size=5">首页</a>

        <c:if test="${pageInfo.currentPage==1}">
            <a href="/findByPage?currentPage=1&size=5">上一页</a>
        </c:if>
        <c:if test="${pageInfo.currentPage>1}">
            <a href="/findByPage?currentPage=${pageInfo.currentPage-1}&size=5">上一页</a>
        </c:if>

        <c:forEach begin="1" end="${pageInfo.totalPage}" var="i">
            <a href="/findByPage?currentPage=${i}&size=5">${i}</a>
        </c:forEach>

        <c:if test="${pageInfo.currentPage==pageInfo.totalPage}">
            <a href="/findByPage?currentPage=${pageInfo.totalPage}&size=5">下一页</a>
        </c:if>
        <c:if test="${pageInfo.currentPage<pageInfo.totalPage}">
            <a href="/findByPage?currentPage=${pageInfo.currentPage+1}&size=5">下一页</a>
        </c:if>

        <a href="/findByPage?currentPage=${pageInfo.totalPage}&size=5">尾页</a>
    </div>
</div>
</body>