(一)准备工作
建立java Web项目基础的四个package
将JSP相关库和MySQL数据驱动包放到相应的目录
下面列出com.java.util中的三个类的代码,在项目中经常用到,基本是参照java1234.com小锋老师的项目代码稍作写出来的。
public classDbUtil {//定义获取数据库连接的方法
private String dburl="jdbc:mysql://127.0.0.1:3306/db_blog?useSSL=false&serverTimezone=GMT";private String username="root";private String password="root";private String driver="com.mysql.cj.jdbc.Driver"; //mysql-connector-java-8.0.12.jar包需要放在WEB/INF/lib下
public Connection getConnection() throwsException{
Class.forName(driver);
Connection conn=DriverManager.getConnection(dburl,username,password);returnconn;
}//定义断开连接的方法
public void closeConnection(Connection conn) throwsSQLException{if(conn!=null){
conn.close();
}
}
}
public classStringUtil {public static booleanisEmpty(String str){if(str==null||str.equals("")){return true;
}else{return false;
}
}public static booleanisNotEmpty(String str){if(str!=null&&!str.equals("")){return true;
}else{return false;
}
}
}
public classDateUtil {public staticString Date2String(Date date,String format){
String result="";
SimpleDateFormat sdf=newSimpleDateFormat(format);if(date!=null){
result=sdf.format(date);
}returnresult;
}//日期转换成字符串
public static Date String2Date(String str,String format) throwsException{if(StringUtil.isEmpty(str)){return null;
}
SimpleDateFormat sdf=newSimpleDateFormat(format);returnsdf.parse(str);
}//字符串转换成日期
}
(二)实现思路——玩的是SQL游戏,以blog博客信息的展示为例;
1.Blog.java类
私有域基本和数据表字段对应,在加上get和set方法。数据设计参看之前的博客:
https://www.cnblogs.com/sanyun/p/9944249.html
public classBlog {private intblogId;private intuserId;privateString userName;privateString title;privateString content;privateString blogType;privateDate createTime;private int views=0;private int likes=0;private int comments=0;/***@returnthe userName*/
publicString getUserName() {returnuserName;
}/***@paramuserName the userName to set*/
public voidsetUserName(String userName) {this.userName =userName;
}/**下面省略了很多get,set方法,以上的私有域都应该有get,set方法*/}
2.BlogDao类
因为首页以各种现实现实博客内容,以时间排序的,4小时内的,10天内的,所以DAO方法最好以SQL语句作为入参之一。
在这个类中,按照传进来的SQL查询数据库,遍历返回的结果集,结果集中每条记录信息保存在一个Blog对象中(这就是前面Blog.java私有域和数据表字段一致的原因),将所有查询出的结果初始化出一堆Blog对象,放入List中返回。
public classBlogDao {public List queryBlog(Connection conn,String sql) throwsException{
ArrayList blogList=new ArrayList<>();
PreparedStatement stat=conn.prepareStatement(sql);
ResultSet rs=stat.executeQuery();while(rs.next()){
Blog blog=newBlog();
blog.setBlogId(rs.getInt("blogId")); //根据列名获取列数据
blog.setBlogType(rs.getString("blogType"));
blog.setContent(rs.getString("content"));
blog.setLikes(rs.getInt("likes"));
blog.setTitle(rs.getString("title"));
blog.setUserId(rs.getInt("userId"));
blog.setUserName(rs.getString("userName"));
blog.setViews(rs.getInt("views"));
blog.setCreateTime(DateUtil.String2Date(rs.getString("createTime"),"yyyy-MM-dd HH:mm:ss")); //将数据库日期类型转换成java日期类型
blogList.add(blog);
}returnblogList;
}
}
3.HomeServlet类
从前面静态页面的设计可以知道:
(1)链接面板:
查询48小时内阅读量最多的前10条博客标题:
SELECT * FROM blog WHERE createTime BETWEEN SUBDATE(LOCALTIME(),2) AND LOCALTIME() ORDER BY views DESC LIMIT 0,10;
查询2天内评论数最多的前10条博客标题:
SELECT * FROM blog WHERE createTime BETWEEN SUBDATE(LOCALTIME(),10) AND LOCALTIME() ORDER BY likes DESC LIMIT 0,10;
查询积分数最多的前20名博客姓名:
SELECT * FROM USER ORDER BY points DESC LIMIT 0,20;
(2)页面主体
按时间顺序逆序查询出博客:
SELECT * FROM blog ORDER BY createTime DESC;
因此,在Servlet的doPost(HttpServletRequest request, HttpServletResponse response)方法中,主要做的工作是:
(1)查询数据库获得博客列表,不同的SQL语句博客列表不一样。
(2)重点是:将查询出来的信息列表作为请求的参数,以便被主页(home.jsp)获取。
(3)forward跳转到主页home.jsp:request.getRequestDispatcher("home.jsp").forward(request, response);
步骤(1),(2)代码如下
private voidshowNavContent(HttpServletRequest request, HttpServletResponse response) {//TODO Auto-generated method stub//左边面板显示的列表信息
Connection conn=null;try{
conn=dbutil.getConnection();
String sql="SELECT * FROM blog WHERE createTime BETWEEN SUBDATE(LOCALTIME(),2) AND LOCALTIME() ORDER BY views DESC LIMIT 0,10; ";
List viewsMoreblogList=blogDao.queryBlog(conn,sql);
request.setAttribute("viewsMoreblogList", viewsMoreblogList);
sql="SELECT * FROM blog WHERE createTime BETWEEN SUBDATE(LOCALTIME(),10) AND LOCALTIME() ORDER BY likes DESC LIMIT 0,10; ";
List likesMoreblogList=blogDao.queryBlog(conn,sql);
request.setAttribute("likesMoreblogList", likesMoreblogList);
sql="SELECT * FROM USER ORDER BY points DESC LIMIT 0,20;";
List userList=userDao.queryUser(conn,sql);
request.setAttribute("userList", userList);
}catch(Exception e) {//TODO Auto-generated catch block
e.printStackTrace();
}
}
private voidshowBlogsByTime(HttpServletRequest request,HttpServletResponse response){
Connection conn=null;try{
conn=dbutil.getConnection();
String sql="SELECT * FROM blog ORDER BY createTime DESC;"; //mySQL不支持top语法
List latestBlogList=blogDao.queryBlog(conn, sql);for(Blog blog:latestBlogList){int num=commentDao.getCommentNumByBlogId(conn,blog.getBlogId());
blog.setComments(num);//评论数
}
request.setAttribute("blogList", latestBlogList);
}catch(Exception e) {//TODO Auto-generated catch block
e.printStackTrace();
}
}
4.主页home.jsp显示数据表内容
(1)导入JSP标签库
(2)获取请求属性中的内容,主要使用JSP的c:forEach标签将博客列表信息显示出来
例如链接面板显示的“48小时阅读排行榜””
静态
48小时阅读排行榜