小确幸BBS论坛-2-首页

2022-11-10 16:29:43 浏览数 (1)

大家好，又见面了，我是你们的朋友全栈君。

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

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

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

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

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

1. 工具类

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

DBUtil 连接数据库

代码语言：javascript复制
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 格式化时间

代码语言：javascript复制
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对象，遍历参数

代码语言：javascript复制
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 获取静态资源

代码语言：javascript复制
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

代码语言：javascript复制
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）获取时间的工具类可有可无。处理数据库时间方式多样，一般有这几种：

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

2. 首页面index.jsp

代码语言：javascript复制
<%@ 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

代码语言：javascript复制
@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

代码语言：javascript复制
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

代码语言：javascript复制
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

代码语言：javascript复制
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

代码语言：javascript复制
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

代码语言：javascript复制
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 rn"   
" from rn"   
" (select t.textId,t.title,t.userId,t.textTime,t.categoryId from text t left join user u on t.textId = u.userId) arn"   
" left join rn"   
" (select r.textId, count(r.replyId) replyCount from reply r group by textId) brn"   
" on rn"   
" a.textId = b.textId rn"   
" where a.categoryId = ? and a.title like ?rn"   
" order by rn"   
" a.textId desc rn"   
" 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 rn"   
" from rn"   
" (select t.textId,t.title,t.userId,t.textTime,t.categoryId from text t left join user u on t.textId = u.userId) arn"   
" left join rn"   
" (select r.textId, count(r.replyId) replyCount from reply r group by textId) brn"   
" on rn"   
" a.textId = b.textId rn"   
" where a.categoryId = ?rn"   
" order by rn"   
" a.textId desc rn"   
" 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

代码语言：javascript复制
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 rn"   
" from rn"   
" (select t.textId,t.title,t.userId,t.textTime from text t left join user u on t.textId = u.userId) arn"   
" left join rn"   
" (select r.textId, count(r.replyId) replyCount from reply r group by textId) brn"   
" on rn"   
" a.textId = b.textId rn"   
" where a.title like ？rn"   
" order by rn"   
" a.textId desc rn"   
" limit ?,?";
Object[] params = {};
List<TextShow> textList =   tss.listText(sql, params);
}
//3.跳转页面
};
}

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

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

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。

数据库 sql servlet jsp java

0 人点赞