话说：
各位读者朋友，晚上好！这一篇展示首页。
难度系数：★★★☆☆
建议用时：1天 实际用时1天
目录
- 工具类
- 首页面index.jsp
- 首页面样式
- 后端代码
首页面复杂点：
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>
<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.跳转页面
};
}
哈哈，功能看起来不多，细节还是蛮多的。
呵呵呵，如果读者能坚持看到这里，那么我也就不用总结了。后面会专门发表一篇博客，来总结这个小项目。
下期预告：个人中心，再会！
