MyBlog博客系统

一、项目简介

       MyBlog博客系统是一个集编辑文章、发表文章、查看文章于一体的平台，是一个私人的写作平台，相当于一个日记本，你可以在上面进行相关的创作，记录自己在学习生活中的点点滴滴。

二、系统流程

 

 三、前期准备

1、配置web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee
                      http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
  version="3.1"
  metadata-complete="false">
</web-app>

2、配置pom.xml（添加servlet，mysql等相关依赖）

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>blog</artifactId>
    <version>1.0-SNAPSHOT</version>

    <packaging>war</packaging>

    <properties>
            <maven.compiler.source>1.8</maven.compiler.source>
            <maven.compiler.target>1.8</maven.compiler.target>
    </properties>

    <dependencies>
        <dependency>
            <groupId>javax.servlet</groupId>
            <artifactId>javax.servlet-api</artifactId>
            <version>3.1.0</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.49</version>
        </dependency>

        <dependency>
            <groupId>com.fasterxml.jackson.core</groupId>
            <artifactId>jackson-databind</artifactId>
            <version>2.12.4</version>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.24</version>
            <scope>compile</scope>
        </dependency>
    </dependencies>

</project>

3、创建库表

       根据博客系统的结构进行分析，需要建立两张表：文章表和用户表。

       用户表使用字段：uid、username（用户名）、password（密码）、avatar（头像）、git_repo（git地址）；文章表使用字段：aid、uid、title（标题）、type（类型）、published_at（发布时间）、content（内容）。

CREATE DATABASE blog CHARSET utf8mb4;

CREATE TABLE `users` (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `password` varchar(45) NOT NULL,
  `avatar` varchar(600) NOT NULL,
  `git_repo` varchar(600) NOT NULL,
  PRIMARY KEY (`uid`),
  UNIQUE KEY `username_UNIQUE` (`username`)
) ;

CREATE TABLE `articles` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL COMMENT '作者id',
  `title` varchar(200) NOT NULL,
  `type` varchar(20) NOT NULL,
  `published_at` datetime NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`aid`)
) ;

4、连接数据库

public class DBUtil {
    private static final DataSource dataSource;
    static {
        MysqlDataSource mysqlDataSource = new MysqlDataSource();
        mysqlDataSource.setUrl("jdbc:mysql:///blog?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai");
        mysqlDataSource.setUser("root");
        mysqlDataSource.setPassword("输入自己的密码");
        dataSource = mysqlDataSource;
    }
    @SneakyThrows
    public static Connection connection(){
        return dataSource.getConnection();
    }
}

5、实现对数据库的操作

package blog.dao;

import blog.model.Article;
import blog.util.DBUtil;
import lombok.SneakyThrows;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ArticleDao {
    @SneakyThrows
    public int selectArticleCountByUid(int uid) {
        //通过用户ID来查询出该用户所写文章的数量
        String sql = "select count(*) from articles where uid = ?";
        try (Connection c = DBUtil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setInt(1, uid);
                System.out.println("DEBUG: " + ps);
                try (ResultSet rs = ps.executeQuery()) {
                    rs.next();
                    return rs.getInt(1);
                }
            }
        }
    }

    @SneakyThrows
    public int selectTypeCountByUid(int uid) {
        //通过用户ID来查询出该用户所写文章的类型数量
        String sql = "select count(distinct type) from articles where uid = ?";
        try (Connection c = DBUtil.connection()) {
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setInt(1, uid);
                System.out.println("DEBUG: " + ps);
                try (ResultSet rs = ps.executeQuery()) {
                    rs.next();
                    return rs.getInt(1);
                }
            }
        }
    }

    @SneakyThrows
    public List<Article> selectListByUid(int uid) {
        //通过用户ID来查询出该用户所写文章
        List<Article> list = new ArrayList<>();
        String sql = "select aid, uid, title, type, published_at, content from articles where uid = ? order by published_at desc";
        try (Connection c = DBUtil.connection()) {
            try (