小确幸BBS论坛-2-首页

话说：
各位读者朋友，晚上好！这一篇展示首页。

难度系数：★★★☆☆
建议用时：1天 实际用时1天
目录

1. 工具类
2. 首页面index.jsp
3. 首页面样式
4. 后端代码

首页面复杂点：
1.SQL语句复杂点，要统计下回复次数；

2.搜索。单独搜索简单，但是要实现在不同栏目下的搜索，就相对复杂些，而且不论搜索还是分栏目，分页都要带上；

1. 工具类

DBUtil 连接数据库
GetFormatTimeUtil 格式化时间
GetPreparedStatementUtil 获取PS对象，遍历参数
GetPropertyUtil 获取静态资源
GetStrToInt 字符串转int

DBUtil 连接数据库

package com.hmc.util;
/** * *2017年12月30日 *User:Meice *下午8:35:12 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBUtil { 
   
    //封装连接数据库的方法


    //处理异常:java.sql.SQLNonTransientConnectionException: Could not create connection to database server.
    static  int retryCount = 5;  
   static  boolean transactionCompleted = false;  


    //1.加载驱动
    static {
        try {
            Class.forName(GetPropertyUtil.getVal("driver"));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

    }

    //2.获取连接
    public static Connection getConn() {

        do {

            //分开放 尽量不要合并写在一块
            String url = GetPropertyUtil.getVal("url");
            String user = GetPropertyUtil.getVal("user");
            String password = GetPropertyUtil.getVal("password");



            try {
                Connection conn =   DriverManager.getConnection(url, user, password);
                return conn;
            } catch (SQLException e) {
                e.printStackTrace();
                String sqlState = e.getSQLState();
                if("08S01".equals(sqlState) || "40001".equals(sqlState)) {
                    retryCount--;
                }else {
                    retryCount = 0;
                }
            }

            return null;

        }while(!transactionCompleted && retryCount>0);

    }


    //3.关闭资源
    public static void closeConn(Connection conn,PreparedStatement ps,ResultSet rs) {
        //Alt + Shift + Z 抛出语句块异常
        try {
            if(rs != null) rs.close();
            if(ps != null) ps.close();
            if(conn != null) conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }


    }


}

GetFormatTimeUtil 格式化时间

package com.hmc.util;
import java.text.SimpleDateFormat;
import java.util.Date;

/** * *2017年12月30日 *User:Meice *下午9:44:05 */
public class GetFormatTimeUtil { 
   

    public static String getFormatTime(int TwelveOrTwentyFour) {

        Date date = new Date();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd kk:mm:ss");
        SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");

        if(TwelveOrTwentyFour == 12) {
                return sdf2.format(date);
        }else if(TwelveOrTwentyFour == 24){

            return sdf.format(date);
        }else {
            return null;
        }

    }


}

GetPreparedStatementUtil 获取PS对象，遍历参数

package com.hmc.util;
/** * *2017年12月30日 *User:Meice *下午9:04:45 */

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class GetPreparedStatementUtil { 
   

    //定义获取PrepatedStatement对象
    public static PreparedStatement getPs(Connection conn,PreparedStatement ps,String sql,Object... params) {
        try {
             ps =       conn.prepareStatement(sql);
            //这样有无参数都可以搞定
            if(params!= null) {
                for(int i=0;i<params.length;i++) {
                    ps.setObject((i+1),params[i]);
                }
            }


            return ps;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }

}

GetPropertyUtil 获取静态资源

package com.hmc.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

/** * *2017年12月30日 *User:Meice *下午8:49:31 */
public class GetPropertyUtil { 
   

    //获取静态资源
    public static String getVal(String key) {

        Properties pro = new Properties();
        InputStream is;
        try {
            is = new FileInputStream("D:\\WorkSpace\\eclipse\\xiaomeibbs\\db.properties");
            pro.load(is);
            return pro.getProperty(key);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

        return null;

    }

}

GetStrToInt 字符串转int

package com.hmc.util;
/** * *2018年1月1日 *User:Meice *上午11:46:00 */
public class GetStrToInt { 
   

        public static int getInt(String str) {
            if(str!= null && !str.equals("")) {
                return Integer.parseInt(str);
            }
            return 0;

        }
}

这里要总结一下下：
1）连接数据库，读取配置文件的时候，如果直接把配置文件放到工程目录下，测试是没有问题的，但是一旦涉及Servlet，就会找不到，所以要写绝对路径。

2）封装的时候，不要仅仅考虑方便，比如获取PreparedStatement，要考虑到每次都要用一个新的对象，让外界提供Connection conn,PrepatedStatement
这样，每次调用，对象都不同，避免调用过程中，对象发生混乱！

3）获取时间的工具类可有可无。处理数据库时间方式多样，一般有这几种：

a.数据库时间设置为varchar()，实体类为String，用我写的工具类来添加数据；
b.数据库时间直接设置为Date，传值的时候直接new Date()即可；
c.数据库时间直接设置为Date或者datetime，直接在SQL语句中传值now()；
d.数据库时间不要设置为timestamp.因为这个默认为now(),虽然根本都不用传值，但是只要数据发生变化，比如修改、删除，那么这个时间都会变成修改、删除的时间，这并不是我们想要的。

2. 首页面index.jsp

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!--Author:Meice 2017年12月30日下午12:13:19 -->
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>小确幸论坛</title>
<!-- 引入外部样式-->
<link href="css/textList.css" rel="stylesheet" type="text/css">


<style type="text/css"> /* link visited hover active love hate */ a:visited{ color:gray; } a:hover{ color: orange; } </style>


<!-- 如果当前栏目被选择，给一个样式 -->
<script type="text/javascript"> function changeBgColor1() { 
     //alert("进来了！"); var a = document.getElementById("liOne"); //a.setAttribute("background-color","orange"); a.style.backgroundColor = "orange"; } </script>


</head>
<body>
<!--引入头部 -->
    <jsp:include page="head.jsp"></jsp:include>



    <!--搜索框的div -->
    <div id="search">
        <form action="textShow" method="get">

            <input type="text" name="search" style="width:300px;height:40px;">
            <input type="submit" value="搜索一下" style="width:60px;height:40px;">

            <input type="hidden" name="categoryId" value="${categoryId}">
        </form>

    </div>


    <!--登录后用户信息 -->
    <div id="userInfo">
        <span class="userInfo">欢迎【${user.userNick}】!</span><br/>
        <a href="userInfo" class="userInfo" style="color:orange;">个人中心</a>
        <a href="login.jsp" class="userInfo">退出</a>
    </div>




    <!--栏目div -->
    <div id="category">
        <ul>
        <!-- 前端不好，只能这样变化栏目背景色 -->
            <c:if test="${categoryId==0 }">
                <li style="background-color:rgb(104,189,69);"> 
                    <a href="textShow" onclick="changeBgColor1()">首页</a>
                </li>
            </c:if>
            <c:if test="${categoryId!=0 }">
                <li >   
                    <a href="textShow" onclick="changeBgColor1()">首页</a>
                </li>
            </c:if>




            <c:if test="${categoryId ==1 }">

                <li id="liTwo" style="background-color:rgb(104,189,69);" >  
                    <a href="textShow?categoryId=1">Java</a>
                </li>
            </c:if>

            <c:if test="${categoryId !=1 }">

                <li id="liTwo"> 
                    <a href="textShow?categoryId=1">Java</a>
                </li>
            </c:if>



            <c:if test="${categoryId ==2 }">

                <li style="background-color:rgb(104,189,69);">
                    <a href="textShow?categoryId=2">MySQL</a>
                </li>
            </c:if>
            <c:if test="${categoryId !=2 }">

                <li>
                    <a href="textShow?categoryId=2">MySQL</a>
                </li>
            </c:if>




            <c:if test="${categoryId ==3 }">

                <li style="background-color:rgb(104,189,69);">
                    <a href="textShow?categoryId=3">大数据</a>
                </li>

            </c:if>
            <c:if test="${categoryId !=3 }">

                <li>
                    <a href="textShow?categoryId=3">大数据</a>
                </li>

            </c:if>


            <c:if test="${categoryId == 4 }">

                <li style="background-color:rgb(104,189,69);">
                    <a href="textShow?categoryId=4">人工智能</a>
                </li>
            </c:if>
            <c:if test="${categoryId != 4 }">

                <li>
                    <a href="textShow?categoryId=4">人工智能</a>
                </li>
            </c:if>

            <c:if test="${categoryId == 5 }">

                <li style="background-color:rgb(104,189,69);">
                    <a href="textShow?categoryId=5">HTML5</a>
                </li>
            </c:if>
            <c:if test="${categoryId != 5 }">

                <li>
                    <a href="textShow?categoryId=5">HTML5</a>
                </li>
            </c:if>


            <c:if test="${categoryId == 6 }">

                <li style="background-color:rgb(104,189,69);background-color: rgb();">
                    <a href="textShow?categoryId=6">生活</a>
                </li>
            </c:if>
            <c:if test="${categoryId !=6 }">

                <li>
                    <a href="textShow?categoryId=6">生活</a>
                </li>
            </c:if>


            <c:if test="${categoryId == 7 }">

                <li style="background-color:rgb(104,189,69);">
                        <a href="textShow?categoryId=7">有趣有料</a>
                </li>
            </c:if>
            <c:if test="${categoryId != 7 }">

                <li>
                        <a href="textShow?categoryId=7">有趣有料</a>
                </li>
            </c:if>


        </ul>
    </div>



    <!--帖子列表div -->
    <div id="textList">
        <table border="1 solid green" width="90%" align="center">
            <thead>
                <tr style="background-color: rgb(233,248,230);">
                    <th>标题</th>
                    <th>作者</th>
                    <th>评论数量</th>
                    <th>发帖时间</th>
                </tr>
            </thead>


            <tbody>

                <!--遍历textList取值 -->

                    <c:if test="${ empty textList}">
                        <tr>
                            <td colspan="4" style="color:red;font-weight:bolder;" align="center">It's a pity,未查询到数据奥~~</td>
                        </tr>
                    </c:if>
                <c:forEach var="ListShow" items="${textList}">

                    <c:if test="${!empty textList}">

                        <tr>
                            <td>
                                <a href="textDetail?textId=${ListShow.textId}">${ListShow.title }</a>
                            </td>

                            <td align="center">
                                <a href="#">${ListShow.user.userNick}</a>
                            </td>

                            <td align="center">
                                ${ListShow.replyCount }
                            </td>

                            <td align="center">
                                ${ListShow.textTime}
                            </td>

                        </tr>
                    </c:if>

                </c:forEach>

                <tr>
                    <td colspan="4" align="center" >

                        <c:if test="${pageIndex>1 }">
                                <button onclick="window.location.href='textShow?pageIndex=1&categoryId=${categoryId }&search=${search}'" style="width:50px;height:25px;" >首页</button>
                                <button onclick="window.location.href='textShow?pageIndex=${pageIndex-1}&categoryId=${categoryId }&search=${search}'">上一页</button>
                        </c:if>


                        <!--总页数要>10 才按照每页10条分页 -->
                        <c:if test="${totalPage>=10}">

                                <!--处理前10页 -->
                            <c:if test="${pageIndex<=10 }">

                                <c:forEach var="i" begin="1" end="10">
                                    <c:if test="${pageIndex==i}">
                                        ${i}
                                    </c:if>
                                    <c:if test="${pageIndex!=i }">
                                        <button onclick="window.location.href='textShow?pageIndex=${i}&categoryId=${categoryId }&search=${search}'" style="width:25px;height:25px;">${i}</button>
                                    </c:if>

                                </c:forEach>
                            </c:if>


                            <!-- 处理大于10页 小于(totalPage-10)页 -->
                            <c:if test="${pageIndex>10 and pageIndex<(totalPage-4 )}">

                                <c:forEach var="i" begin="${pageIndex-4 }" end="${pageIndex+5}">
                                    <c:if test="${pageIndex==i}">
                                        ${i}
                                    </c:if>
                                    <c:if test="${pageIndex!=i }">
                                        <button onclick="window.location.href='textShow?pageIndex=${i}&categoryId=${categoryId }&search=${search}'" style="width:25px;height:25px;">${i}</button>
                                    </c:if>
                                </c:forEach>

                            </c:if>

                            <!-- 处理最后几页 -->
                            <c:if test="${pageIndex>=(totalPage-4) }">


                                    <c:forEach var="i" begin="${totalPage-9}" end="${totalPage}">
                                        <c:if test="${pageIndex==i}">
                                            ${i}
                                        </c:if>
                                        <c:if test="${pageIndex!=i }">
                                            <button onclick="window.location.href='textShow?pageIndex=${i}&categoryId=${categoryId }&search=${search}'" style="width:25px;height:25px;">${i}</button>
                                        </c:if>
                                    </c:forEach>
                            </c:if>
                        </c:if>


                        <!-- 总页数小于10 -->
                        <c:if test="${totalPage<10 }">
                            <c:forEach var="i" begin="1" end="${totalPage}">
                                        <c:if test="${pageIndex==i}">
                                            ${i}
                                        </c:if>
                                        <c:if test="${pageIndex!=i }">
                                            <button onclick="window.location.href='textShow?pageIndex=${i}&categoryId=${categoryId }&search=${search}'" style="width:25px;height:25px;">${i}</button>
                                        </c:if>
                            </c:forEach>

                        </c:if>





                        <c:if test="${pageIndex<totalPage }">

                            <button onclick="window.location.href='textShow?pageIndex=${pageIndex+1}&categoryId=${categoryId }&search=${search}'">下一页</button>
                            <button onclick="window.location.href='textShow?pageIndex=${totalPage}&categoryId=${categoryId }&search=${search}'" style="width:50px;height:25px;" >末页</button>
                        </c:if>
                        &nbsp;&nbsp;&nbsp;  <span style="color:orange;">${pageIndex }</span>/${totalPage}


                        <!-- <div style="float:right;">` <form action="#" mehtod="get"> <select id="goto" name="goto" > <option value="5">5</option> <option value="10">10</option> <option value="20">20</option> <input type="submit" value="goto"> </form> </div> -->

                    </td>

                </tr>

            </tbody>

        </table>
    </div>




    <!-- 引入尾部-->
    <jsp:include page="foot.jsp"></jsp:include>

</body>
</html>

首页面后台是重点，前端分页是重点。

3.首页面样式
textList.css

@CHARSET "UTF-8";
/*--Author:Meice 2018年1月1日 下午4:02:50*/

/* 搜索框样式 */
#search{ /* border:1px solid red; */ width:450px; height:80px; margin: -70px 0px 0px 600px; }


/* 登录信息样式*/
#userInfo{ /* border: 1px solid red; */ width: 200px; height: 60px; margin: -100px 0px 0px 1280px; }

/* 栏目样式 */
#category{ /* border:1px solid red; */ width:200px; height:300px; margin: 60px 0px 0px 70px; }


/* 帖子列表样式 */
#textList{ /* border:1px solid red; */ width:1200px; height:700px; margin: -300px 0px 0px 700px; float:right; }

/* 去除a 标签下划线 */
 a{ text-decoration:none; /* 因为是蓝色字体，所以 */ color:black; }

 /*设置li标签样式 */
 #category ul li{ list-style:none; margin:15px; background-color: rgb(233,248,230); height:35px; width:100px; font-weight:bolder; }



/* 登录信息字体样式 */
.userInfo{ color:white; font-weight:bolder; font-size:15px; }

4. 后端代码
因为搜索、分栏、及首页都是在同一个页面展示，而且有分页，尤其是分页的时候，点击要去调用一个Servlet，所以最好的处理方式是按照接收的参数（栏目Id，搜索内容）来确定调用不同的方法，但是用一个Servlet来写。

TextShowDao
TextShowDaoImpl

TextShowService
TextShowServiceImpl

TextShowServlet
TextSearchServlet

TextShowDao

package com.hmc.dao;

import java.util.List;

import com.hmc.pojo.Category;
import com.hmc.pojo.TextAll;
import com.hmc.pojo.TextShow;
import com.hmc.pojo.User;
import com.hmc.pojo.UserInfo;

/** * *2018年1月1日 *User:Meice *上午10:59:20 */
public interface TextShowDao { 
   


    //1.查询首页帖子方法
        List<TextShow> listText (String sql,Object... params);

        //2.根据帖子Id返回整个user对象
        User listUser(int textId);

        //3.根据帖子Id，首页显示个人中心
        List<UserInfo> listUserInfo(String sql,Object... params);

        //4.帖子详情显示方法
        List<TextAll> listAll(String sql,Object... params);
}

TextShowDaoImpl

package com.hmc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.hmc.pojo.Category;
import com.hmc.pojo.Reply;
import com.hmc.pojo.Text;
import com.hmc.pojo.TextAll;
import com.hmc.pojo.TextShow;
import com.hmc.pojo.User;
import com.hmc.pojo.UserInfo;
import com.hmc.util.DBUtil;
import com.hmc.util.GetPreparedStatementUtil;
import com.mysql.cj.api.xdevapi.Result;



/** * *2018年1月1日 *User:Meice *上午11:00:23 */
public class TextShowDaoImpl implements TextShowDao {

        //1.返回首页帖子列表
    @Override
    public List<TextShow> listText(String sql, Object... params) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;
        ps = GetPreparedStatementUtil.getPs(conn, ps, sql, params);

        List<TextShow> listText = new ArrayList<>();

        //执行查询
        try {
            rs =    ps.executeQuery();
            while(rs != null && rs.next()) {
                TextShow ts = new TextShow();
                int textId = rs.getInt("textId");
                String title = rs.getString("title");
                //根据textId获取整个用户对象
                //select userId from text where textId = ?
                int replyCount = rs.getInt("replyCount");
                String textTime = rs.getString("textTime");
                User user = listUser(textId);
                //System.out.println("第一个user对象 "+user);

                ts.setTextId(textId);
                ts.setTitle(title);
                ts.setUser(user);
                ts.setReplyCount(replyCount);
                ts.setTextTime(textTime);
                listText.add(ts);
            }
            return listText;

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            DBUtil.closeConn(conn, ps, rs);
        }

        return null;
    }



    //2.根据帖子Id返回整个user对象
    @Override
    public User listUser(int textId) {
        Connection conn = DBUtil.getConn();
        PreparedStatement ps = null;
        ResultSet rs2 = null;
        List<User> listUser = new ArrayList<>();
        String sql = "select userId from text where textId = ?";
        Object[] params = {textId};
        PreparedStatement   ps2 = GetPreparedStatementUtil.getPs(conn, ps, sql, params);
        try {
             rs2 =  ps2.executeQuery();
            int userId =0;
            if(rs2!= null && rs2.next()) {
                userId =    rs2.getInt("userId");
            }
            //根据userId，返回对象
            UserLoginDao uld = new UserLoginDaoImpl();
            String sql2 = "select * from user where userId = ?";
            Object[] params2 = {userId};
            List<User> listUser2 =  uld.listUser(sql2, params2);

            return listUser2.get(0);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeConn(conn, ps2, rs2);
        }

        return null;
    }







    //3.根据帖子Id，展示个人中心
    @Override
    public List<UserInfo> listUserInfo(String sql, Object... params) {
        Connection conn = DBUtil.getConn();
        List<UserInfo> list = new ArrayList<>();
        PreparedStatement ps = null;
        PreparedStatement ps3 = GetPreparedStatementUtil.getPs(conn, ps, sql, params);
        try {
            ResultSet rs3 = ps3.executeQuery();
            while(rs3!= null && rs3.next()) {
                UserInfo ui = new UserInfo();
                Text text = new Text();
                text.setTextId(rs3.getInt("textId"));
                text.setTitle(rs3.getString("title"));
                text.setContext(rs3.getString("context"));
                text.setTextTime(rs3.getString("textTime"));
                text.setUserId(rs3.getInt("userId"));

                Category category = new Category();
                category.setCategoryId(rs3.getInt("categoryId"));
                category.setCategoryName(rs3.getString("categoryName"));


                ui.setText(text);
                ui.setCategory(category);

                list.add(ui);


            }
            return list; 

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            DBUtil.closeConn(conn, ps3, null);
        }

        return null;
    }



    //4.显示帖子详情，四表联查

    @Override
    public List<TextAll> listAll(String sql, Object... params) {
        Connection conn = DBUtil.getConn();
        List<TextAll> listAll = new ArrayList<>();
        System.out.println("进来了...");
        PreparedStatement ps = null;
        PreparedStatement ps4 = GetPreparedStatementUtil.getPs(conn, ps, sql, params);
        try {
            ResultSet rs4 = ps4.executeQuery();
            while(rs4 != null && rs4.next()) {
                TextAll ta = new TextAll();

                Text text = new Text();
                text.setTextId(rs4.getInt("textId"));
                text.setTitle(rs4.getString("title"));
                text.setContext(rs4.getString("context"));
                text.setTextTime(rs4.getString("textTime"));
                text.setUserId(rs4.getInt("userId"));
                text.setCategoryId(rs4.getInt("categoryId"));


                User user = new User();
                user.setUserId(rs4.getInt("userId"));
                //text.setUserId(user.getUserId()); //据说，这样会导致hashCode()重复调用，导致内存溢出！
                user.setUserName(rs4.getString("userName"));
                user.setUserNick(rs4.getString("userNick"));
                user.setLoginTime(rs4.getString("loginTime"));
                user.setRegisterTime(rs4.getString("registerTime"));


                Category category = new Category();
                category.setCategoryId(rs4.getInt("categoryId"));
                //text.setCategoryId(category.getCategoryId());
                category.setCategoryName(rs4.getString("categoryName"));


                Reply reply = new Reply();
                reply.setReplyId(rs4.getInt("replyId"));
                reply.setReplyContext(rs4.getString("replyContext"));
                reply.setReplyTime(rs4.getString("replyTime"));
                reply.setReplyCount(rs4.getInt("replyCount"));
                //reply.setTextId(text.getTextId());
                //reply.setUserId(user.getUserId());
                reply.setTextId(rs4.getInt("textId"));
                reply.setReplyCount(rs4.getInt("replyCount"));


                ta.setText(text);
                ta.setUser(user);
                ta.setCategory(category);
                ta.setReply(reply);


                listAll.add(ta);

            }

            return listAll;

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

            DBUtil.closeConn(conn, ps4, null);
        }
        return null;
    }




    //5.返回帖子总条目数
    public int getCountAll(String sql,Object... params) {
        int countAll = 0;
        //String sql = "select count(textId) countAll from text";
        Connection conn = DBUtil.getConn();
        PreparedStatement ps = null;
        ps = GetPreparedStatementUtil.getPs(conn, ps, sql, params);
        ResultSet rs = null;
        try {
            rs = ps.executeQuery();
            if(rs != null && rs.next()) {
                countAll = rs.getInt("countAll");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.closeConn(conn, ps, rs);

        }


        return countAll;

    }



}

代码有注释，就不在赘述。

TextShowService

package com.hmc.service;
/** * *2018年1月1日 *User:Meice *上午10:58:08 */

import java.util.List;

import com.hmc.pojo.Category;
import com.hmc.pojo.TextAll;
import com.hmc.pojo.TextShow;
import com.hmc.pojo.User;
import com.hmc.pojo.UserInfo;

public interface TextShowService { 
   


    //1.查询首页帖子方法
    List<TextShow> listText (String sql,Object... params);


    //2.根据帖子Id返回整个user对象
    User listUser(int textId);


    //3.根据帖子Id，返回整个Category对象 因为查，只有反射可以优化，只有把对象写固定
    List<UserInfo> listUserInfo(String sql,Object... params);


    //4.帖子详情显示方法
    List<TextAll> listAll(String sql,Object... params);


}

TextShowServiceImpl

package com.hmc.service;

import java.util.List;

import com.hmc.dao.TextShowDaoImpl;
import com.hmc.pojo.Category;
import com.hmc.pojo.TextAll;
import com.hmc.pojo.TextShow;
import com.hmc.pojo.User;
import com.hmc.pojo.UserInfo;

/** * *2018年1月1日 *User:Meice *上午10:59:51 */
public class TextShowServiceImpl implements TextShowService { 
   

    //1.首页显示帖子
    @Override
    public List<TextShow> listText(String sql, Object... params) {
        return new TextShowDaoImpl().listText(sql, params);
    }


    //2.根据textId返回user对象
    @Override
    public User listUser(int textId) {
        return new TextShowDaoImpl().listUser(textId);
    }




    //3.根据帖子Id，个人中心显示内容
    @Override
    public List<UserInfo> listUserInfo(String sql, Object... params) {
        return new TextShowDaoImpl().listUserInfo(sql, params);
    }



    //4.显示帖子详情（四表联查）
    @Override
    public List<TextAll> listAll(String sql, Object... params) {
        return new TextShowDaoImpl().listAll(sql, params);
    }





}

TextShowServlet

package com.hmc.controller;
import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.hamcrest.core.Is;
import com.hmc.dao.TextShowDaoImpl;
import com.hmc.pojo.TextShow;
import com.hmc.service.TextShowService;
import com.hmc.service.TextShowServiceImpl;
import com.hmc.util.GetStrToInt;

/** * *2018年1月1日 *User:Meice *下午7:00:16 */
public class TextShowServlet extends HttpServlet { 
   
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req, resp);
    }

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

        TextShowService tss = new TextShowServiceImpl();
        TextShowDaoImpl tsd = new TextShowDaoImpl();
        //1.接收参数
        //传递2个参数search 、 categoryId 
        String search = req.getParameter("search");

        String categoryIdStr = req.getParameter("categoryId");
        int categoryId = GetStrToInt.getInt(categoryIdStr);
        System.out.println("search内容:"+search+"categoryId "+categoryId);

        //得到当前页pageIndex 
        String pageIndexStr = req.getParameter("pageIndex");
        int pageIndex = GetStrToInt.getInt(pageIndexStr);



        /** * 分页 * 分页五要素： * countAll 总条目数 * pageSize 每页显示数目 * totalPage 总页数 （countAll /pageSize） * pageIndex 当前页 * offset 偏移量，从第几条数据开始显示，也就是limit ? (pageindex-1)*pageSize */

        int countAll =0;
        //查首页帖子总条目数
        String sqlCountAll = "select count(textId) countAll from text";
        countAll = new TextShowDaoImpl().getCountAll(sqlCountAll, null);
        int pageSize = 20;
        int totalPage =(int)Math.ceil((double) countAll/pageSize);
        System.out.println("总页数："+totalPage);
        if(pageIndex ==0) {
            pageIndex = 1;
        }

        //页面传
        int offset  = (pageIndex-1)*pageSize;
        //limit offset,pageSize;





        //2.调用方法

        //定义全局的List<TextShow> textList 不论首页、搜索 、栏目都用同一个。分页只用做一次
        List<TextShow> textList = null;

        //执行首页查询方法

            if( search !=null && categoryId ==0) {

                //这是多余的判断
                /*if(search.equals("")) { search = ""; }*/
                //TODO 调用模糊查询方法
                System.out.println("调用模糊查询方法.....模糊查询进来了。。。。。。");
                String sqlSearch = " select a.textId,a.title,a.userId,b.replyCount,a.textTime from (select t.textId,t.title,t.userId,t.textTime from text t left join user u on t.textId = u.userId) a "
                        + "left join (select r.textId, count(r.replyId) replyCount from reply r group by textId) b "
                        + "on a.textId = b.textId where a.title like ? order by a.textId desc limit ?,?";
                System.out.println(sqlSearch);
                System.out.println("search内容："+search);
                Object[] paramsSearch = {
  
  "%"+search+"%",offset,pageSize};


                textList = tss.listText(sqlSearch, paramsSearch);
                System.out.println("模糊查询结果："+textList);
                System.out.println("模糊查询textList大小："+textList.size());
                //改变总页数
                String sqlSearchCountAll = "select count(textId) countAll from text where title like ?";
                Object[] paramsSearchCountAll = {
  
  "%"+search+"%"};
                if(textList != null) {
                    countAll = tsd.getCountAll(sqlSearchCountAll, paramsSearchCountAll);
                }
                totalPage =(int)Math.ceil((double) countAll/pageSize);
                System.out.println("模糊查询总页数:"+totalPage);
            }





            if(categoryId != 0) {
                //TODO 调用分栏目查询

                //如果在栏目下查询
                if(search != null &&!search.equals("")) {

                    String sqlSearchByCategoryId = "select a.textId,a.title,a.userId,a.categoryId,b.replyCount,a.textTime \r\n" + 
                            " from \r\n" + 
                            " (select t.textId,t.title,t.userId,t.textTime,t.categoryId from text t left join user u on t.textId = u.userId) a\r\n" + 
                            " left join \r\n" + 
                            " (select r.textId, count(r.replyId) replyCount from reply r group by textId) b\r\n" + 
                            " on \r\n" + 
                            " a.textId = b.textId \r\n" + 
                            " where a.categoryId = ? and a.title like ?\r\n" + 
                            " order by \r\n" + 
                            " a.textId desc \r\n" + 
                            " limit ?,?";
                    Object[] paramsSearchByCategoryId = {categoryId,"%"+search+"%",offset,pageSize};
                    textList = tss.listText(sqlSearchByCategoryId, paramsSearchByCategoryId);

                    //分栏目在查询总条目数
                    String sqlSearchByCategoryIdCountAll = "select count(textId) countAll from text where title like ? and categoryId = ?";
                    Object[] paramsSearchByCategoryIdCountAll = {
  
  "%"+search+"%",categoryId};
                    countAll = tsd.getCountAll(sqlSearchByCategoryIdCountAll, paramsSearchByCategoryIdCountAll);
                    totalPage = (int)Math.ceil((double) countAll/pageSize);



                }else {

                    System.out.println("分栏查询进来了。。。。。。。");
                    String sqlCategory = " select a.textId,a.title,a.userId,a.categoryId,b.replyCount,a.textTime \r\n" + 
                            " from \r\n" + 
                            " (select t.textId,t.title,t.userId,t.textTime,t.categoryId from text t left join user u on t.textId = u.userId) a\r\n" + 
                            " left join \r\n" + 
                            " (select r.textId, count(r.replyId) replyCount from reply r group by textId) b\r\n" + 
                            " on \r\n" + 
                            " a.textId = b.textId \r\n" + 
                            " where a.categoryId = ?\r\n" + 
                            " order by \r\n" + 
                            " a.textId desc \r\n" + 
                            " limit ?,?";
                    Object[] paramsCategory = {categoryId,offset,pageSize};
                    textList =  tss.listText(sqlCategory, paramsCategory);

                    //变化总页数
                    String sqlCategoryCountAll = "select count(textId) countAll from text where categoryId = ?";
                    Object[] paramsCategoryCountAll = {categoryId};
                    countAll = tsd.getCountAll(sqlCategoryCountAll, paramsCategoryCountAll);
                    totalPage =(int)Math.ceil((double) countAll/pageSize);
                }



            }





            //其他情况全部调用首页查询
            if(search == null  && categoryId ==0) {
                String sql = "select a.textId,a.title,a.userId,b.replyCount,a.textTime from (select t.textId,t.title,t.userId,t.textTime from text t left join user u on t.textId = u.userId) a left join (select r.textId, count(r.replyId) replyCount from reply r group by textId) b on a.textId = b.textId "
                        + "order by a.textId desc limit ?,?";
                Object[] params = {offset,pageSize};
                textList =  tss.listText(sql, params);
                System.out.println("textList大小："+textList.size());
                System.out.println(textList);

            }




            //当search categoryId 都有值的时候，怎么办？？
        /* if(categoryId != 0 && search != null &&! search.equals("")) { System.out.println("执行在分栏中搜索的方法。。。。"); }*/






        //3，传值及页面跳转
        System.out.println("实际传过去总条目数:"+countAll);
        req.setAttribute("textList", textList);
        req.setAttribute("countAll", countAll);
        req.setAttribute("pageSize", pageSize);
        req.setAttribute("totalPage", totalPage);
        req.setAttribute("pageIndex", pageIndex);

        //传过去search categoryId便于点击分页的时候，调用不同的方法 否则永远调用textShow
        req.setAttribute("search", search);
        req.setAttribute("categoryId", categoryId);




        req.getRequestDispatcher("index.jsp").forward(req, resp);


    }
}   

TextSearchServlet

package com.hmc.controller;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.hmc.pojo.TextShow;
import com.hmc.service.TextShowService;
import com.hmc.service.TextShowServiceImpl;

/** * *2018年1月7日 *User:Meice *下午5:10:12 */
public class TextSearchServlet extends HttpServlet { 
   
        @Override
        protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
            doPost(req, resp);
        }
        protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException ,IOException {
            //1.接收参数 search
            //传递1个参数search
            String search = req.getParameter("search");


            //2.调用方法
            if(search != null) {
                if(search.equals("")) {
                    search = "";
                }

                TextShowService tss = new TextShowServiceImpl();
                String sql = " select a.textId,a.title,a.userId,b.replyCount,a.textTime \r\n" + 
                        " from \r\n" + 
                        " (select t.textId,t.title,t.userId,t.textTime from text t left join user u on t.textId = u.userId) a\r\n" + 
                        " left join \r\n" + 
                        " (select r.textId, count(r.replyId) replyCount from reply r group by textId) b\r\n" + 
                        " on \r\n" + 
                        " a.textId = b.textId \r\n" + 
                        " where a.title like ？\r\n" + 
                        " order by \r\n" + 
                        " a.textId desc \r\n" + 
                        " limit ?,?";
                Object[] params = {};
                List<TextShow> textList =   tss.listText(sql, params);

            }



            //3.跳转页面





        };
}

哈哈，功能看起来不多，细节还是蛮多的。

呵呵呵，如果读者能坚持看到这里，那么我也就不用总结了。后面会专门发表一篇博客，来总结这个小项目。
下期预告：个人中心，再会！

今天的文章小确幸BBS论坛-2-首页分享到此就结束了，感谢您的阅读，如果确实帮到您，您可以动动手指转发给其他人。