1. 项目简介
项目地址：https://github.com/Madrid-7/LinuxProject/tree/main/MySQL-BOKE
该项目是为了练习JDBC的简单套路，在命令行实现简单的博客系统。
该系统实现用户注册，用户登录，发表文章（需要先登录），查看文章列表，查看指定文章内容，评论文章，点赞文章等功能。
2. 项目设计
数据库设计：
-- 建库
CREATE DATABASE boke CHARSET utf8mb4;
use boke;
-- 建表
-- 用户表
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增 id',
username VARCHAR(200) NOT NULL UNIQUE COMMENT '唯一的用户名',
nickname VARCHAR(200) NOT NULL COMMENT '显示名称',
password VARCHAR(200) NOT NULL COMMENT '登录密码'
);
-- 文章表
CREATE TABLE articles (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增 id',
author_id INT NOT NULL COMMENT '作者 id',
title VARCHAR(200) NOT NULL COMMENT '文章标题',
published_at DATETIME NOT NULL COMMENT '文章发表时间',
content TEXT NOT NULL COMMENT '文章正文'
);
-- 评论表
CREATE TABLE comments (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增 id',
user_id INT NOT NULL COMMENT '评论者id',
article_id INT NOT NULL COMMENT '文章id',
published_at DATETIME NOT NULL COMMENT '评论发表时间',
content VARCHAR(200) NOT NULL COMMENT '评论正文'
);
-- 点赞关系表
-- 使用复合主键的形式
CREATE TABLE like_relations (
user_id INT NOT NULL COMMENT '评论者 id',
article_id INT NOT NULL COMMENT '文章 id',
PRIMARY KEY (user_id, article_id)
);
通用类设计：
启动类Main设计：
该类主要负责程序的主要流程
功能菜单设计
各部分功能函数的调用
public class Main {
private static List<String> featureList = new ArrayList<>();
private static List<Action> actionList = new ArrayList<>();
private static void initFeatureList() {
featureList.add("用户注册");
featureList.add("用户登录");
featureList.add("查看文章列表-按照发表时间倒序排列");
featureList.add("发表文章-需要先登录");
featureList.add("查看指定文章内容");
featureList.add("评论文章-需要先登录");
featureList.add("点赞文章-需要先登录");
featureList.add("取消点赞指定文章-需要先登录");
featureList.add("查找标题中包含某些字符的文章列表");
}
private static void initActionList() {
actionList.add(new UserRegisterAction());
actionList.add(new UserLoginAction());
actionList.add(new ArticleListAction());
actionList.add(new ArticlePublishAction());
actionList.add(new ArticleDetailAction());
actionList.add(new ArticleCommentsAction());
actionList.add(new ArticleLikesAction());
actionList.add(new CancelLikeAction());
actionList.add(new ArticleFindListAction());
}
public static void main(String[] args) {
Scanner sc = new Scanner(System.in);
initFeatureList();
initActionList();
while (true) {
showMenu();
showPrompt();
int select = sc.nextInt();
doAction(select);
}
}
private static void doAction(int select) {
if (select == 0) {
System.out.println("退出。。。");
System.exit(0);
}
System.out.println("选择是：" + featureList.get(select - 1));
if (select - 1 < actionList.size()) {
Action action = actionList.get(select - 1);
action.run();
} else {
System.out.println("该功能未实现，敬请期待。。。");
}
}
private static void showPrompt() {
System.out.println("请输入>");
}
private static void showMenu() {
System.out.println("欢迎使用：>");
for (int i = 0; i < featureList.size(); i++) {
System.out.printf(" %d. %s%n", i+1, featureList.get(i));
}
System.out.println(" 0. 退出");
}
}
功能接口设计
主要负责规范功能类设计
public interface Action {
void run();
}
数据库工具类DBUtil
该类负责与数据库建立连接并返回Connection对象
使用DataSource与数据库进行连接
public class DBUtil {
// 静态属性，只有一份
private static DataSource dataSource = null;
private static void initDataSource() {
MysqlDataSource mysqlDataSource = new MysqlDataSource();
mysqlDataSource.setServerName("127.0.0.1");
mysqlDataSource.setPort(3306);
mysqlDataSource.setUser("root");
mysqlDataSource.setPassword("123456");
mysqlDataSource.setDatabaseName("boke");
dataSource = mysqlDataSource;
}
static {
initDataSource();
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
}
用户类设计User
规定基本用户属性id，username，nickname
设计静态属性currentUser，并初始化为null，该静态属性标识当前登录的用户信息，没有登录则为空
public class User {
int id;
String username;
String nickname;
public User() {
}
public User(int id, String username, String nickname) {
this.id = id;
this.username = username;
this.nickname = nickname;
}
private static User currentUser = null;
public static void login(User user) {
currentUser = user;
System.out.println("当前登陆的用户信息是: " + currentUser);
}
public static User getCurrentUser() {
return currentUser;
}
public static boolean isLogged() {
return currentUser != null;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", nickname='" + nickname + '\'' +
'}';
}
}
功能类设计：（统一继承Action接口）
用户注册类设计UserRegisterAction
调用DBUtil的函数得到Connection对象
使用try-with-resource结构管理资源调用
使用PreparedStatement方便查询参数的替换
public class UserRegisterAction implements Action{
@Override
public void run() {
System.out.println("用户注册。。。");
Scanner scanner = new Scanner(System.in);
System.out.print("请输入用户名称>");
String username = scanner.nextLine();
System.out.print("请输入用户昵称>");
String nickname = scanner.nextLine();
System.out.print("请输入用户密码>");
String password = scanner.nextLine();
try (Connection connection = DBUtil.getConnection()) {
String sql = "insert into users (username, nickname, password) values (?, ?, ?)";
try (PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
// Statement.RETURN_GENERATED_KEYS -> 返回刚才插入数据的自增 id
// 即返回生成的自增主键
statement.setString(1, username);
statement.setString(2, nickname);
statement.setString(3, password);
statement.executeUpdate();
int id;
try (ResultSet resultSet = statement.getGeneratedKeys()) {
resultSet.next();
id = resultSet.getInt(1);
}
System.out.println("注册成功。。。->" + nickname);
User user = new User(id, username, nickname);
User.login(user);
}
} catch (SQLException throwables) {
System.out.println("错误:" + throwables.getMessage());
}
}
}
用户登录类设计UserLoginAction
public class UserLoginAction implements Action{
@Override
public void run() {
System.out.println("用户登录。。。");
Scanner sc = new Scanner(System.in);
System.out.println("请输入用户名称>");
String username = sc.nextLine();
System.out.println("请输入用户密码>");
String password = sc.nextLine();
try (Connection connection = DBUtil.getConnection()) {
String sql = "select id, username, nickname from boke.users where username = ? and password = ?";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, username);
statement.setString(2, password);
try (ResultSet resultSet = statement.executeQuery()) {
if (resultSet.next()) {
int id = resultSet.getInt(1);
username = resultSet.getString(2);
String nickname = resultSet.getString(3);
User user = new User(id, username, nickname);
User.login(user);
} else {
System.out.println("Error->用户名或密码错误，请重新输入！！");
}
}
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
查看文章列表类设计ArticleListAction
public class ArticleListAction implements Action {
@Override
public void run() {
try (Connection connection = DBUtil.getConnection()) {
List<String[]> articleList = new ArrayList<>();
String sql = "select articles.id, nickname, title, published_at " +
"from boke.articles, boke.users " +
"where author_id = users.id " +
"order by published_at";
try (PreparedStatement s = connection.prepareStatement(sql)) {
try (ResultSet resultSet = s.executeQuery()) {
while (resultSet.next()) {
String[] article = new String[4];
String id = resultSet.getString("articles.id");
String nickname = resultSet.getString("nickname");
String title = resultSet.getString("title");
String publishedAt = resultSet.getString("published_at");
article[0] = id;
article[1] = nickname;
article[2] = title;
article[3] = publishedAt;
articleList.add(article);
}
}
}
System.out.println("#ID | 标题 | 作者 | 发表时间");
for (String[] article : articleList) {
System.out.printf("%-4s | %-40s | %-2s | %s%n", article[0], article[2], article[1], article[3]);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
发表文章类设计ArticlePublishAction
public class ArticlePublishAction implements Action {
@Override
public void run() {
if (!User.isLogged()) {
System.out.println("请先登录。。。");
return;
}
System.out.println("发表文章->...");
Scanner sc = new Scanner(System.in);
System.out.println("输入标题>");
String title = sc.nextLine();
System.out.println("输入正文>");
String content = sc.nextLine();
int authorId = User.getCurrentUser().id;
Date publishedAt = new Date(); // 对象本来就是当前时间
// publishedAt 现在是 Date 对象 format 成 String 格式
DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String publishedAtStr = format.format(publishedAt);
try (Connection connection = DBUtil.getConnection()) {
String sql = "insert into boke.articles (author_id, title, published_at, content) values (?, ?, ?, ?)";
try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
preparedStatement.setInt(1, authorId);
preparedStatement.setString(2, title);
preparedStatement.setString(3, publishedAtStr);
preparedStatement.setString(4, content);
preparedStatement.executeUpdate();
System.out.println("《" + title + "》发表成功。。。");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
查看指定文章内容类设计
public class ArticleDetailAction implements Action {
@Override
public void run() {
Scanner sc = new Scanner(System.in);
System.out.println("输入要查看的文章的id>");
String id = sc.nextLine();
String nickname, title, content, published_at;
int likeCount;
try (Connection connection = DBUtil.getConnection()) {
//查询文章信息
String articleSql = "select + articles.id, nickname, title, content, published_at " +
"from boke.articles, boke.users " +
"where users.id=author_id and articles.id = ?";
try (PreparedStatement preparedStatement = connection.prepareStatement(articleSql)) {
preparedStatement.setString(1, id);
try (ResultSet resultSet = preparedStatement.executeQuery()) {
if (!resultSet.next()) {
System.out.println("并未查到此文章。。。");
return;
}
nickname = resultSet.getString("nickname");
title = resultSet.getString("title");
content = resultSet.getString("content");
published_at = resultSet.getString("published_at");
}
}
//查询文章点赞数
String likeCountSql = "select count(*) from boke.like_relations where article_id = ?";
try (PreparedStatement preparedStatement = connection.prepareStatement(likeCountSql)) {
preparedStatement.setString(1, id);
try (ResultSet resultSet = preparedStatement.executeQuery()) {
resultSet.next();
likeCount = resultSet.getInt(1);
}
}
//查询评论信息
List<String[]> commentList = new ArrayList<>();
String queryCommentSql = "select nickname, content, published_at " +
"from boke.comments, boke.users " +
"where users.id=comments.user_id and article_id = ?";
try (PreparedStatement preparedStatement = connection.prepareStatement(queryCommentSql)) {
preparedStatement.setString(1, id);
try (ResultSet resultSet = preparedStatement.executeQuery()) {
while (resultSet.next()) {
String[] comment = new String[3];
comment[0] = resultSet.getString("nickname");
comment[1] = resultSet.getString("content");
comment[2] = resultSet.getString("published_at");
commentList.add(comment);
}
}
}
//显示所有信息
System.out.println(title);
System.out.println(nickname);
System.out.println(published_at);
System.out.println("点赞数—> " + likeCount);
System.out.println("-----------------------------------");
System.out.println(content);
System.out.println("-----------------------------------");
for (String[] comment: commentList) {
System.out.println(comment[0] + "|" + comment[1] + "|" + comment[2]);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
文章评论类设计ArticleCommentsAction
public class ArticleCommentsAction implements Action {
@Override
public void run() {
if (!User.isLogged()) {
System.out.println("请先登录。。。");
return;
}
Scanner sc = new Scanner(System.in);
System.out.println("输入要评论的文章id:>");
String id = sc.nextLine();
System.out.println("输入评论内容:>");
String content = sc.nextLine();
Date date = new Date();
DateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String publishedAt = format.format(date);
try (Connection connection = DBUtil.getConnection()) {
String sql = "insert into boke.comments (user_id, article_id, published_at, content) values (?, ?, ?, ?)";
try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
preparedStatement.setInt(1, User.getCurrentUser().id);
preparedStatement.setString(2, id);
preparedStatement.setString(3, publishedAt);
preparedStatement.setString(4, content);
preparedStatement.executeUpdate();
System.out.println("评论发表成功。。。");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
点赞类设计ArticleLikesAction
public class ArticleLikesAction implements Action {
@Override
public void run() {
if (!User.isLogged()) {
System.out.println("请先登录。。。");
return;
}
Scanner sc = new Scanner(System.in);
System.out.println("输入要点赞的文章id:>");
String id = sc.nextLine();
try (Connection connection = DBUtil.getConnection()) {
String sql = "insert into boke.like_relations (user_id, article_id) values (?, ?)";
try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
preparedStatement.setInt(1, User.getCurrentUser().id);
preparedStatement.setString(2, id);
preparedStatement.executeUpdate();
System.out.println("点赞成功。。。");
}
} catch (SQLIntegrityConstraintViolationException e) {
System.out.println("您以点赞过了。。。");
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
取消点赞类设计CancelLikeAction
public class CancelLikeAction implements Action {
@Override
public void run() {
if (!User.isLogged()) {
System.out.println("请先登录。。。");
return;
}
Scanner sc = new Scanner(System.in);
System.out.println("输入要取消点赞的文章id:>");
String id = sc.nextLine();
try (Connection connection = DBUtil.getConnection()) {
String sql = "delete from boke.like_relations where user_id = ? and article_id = ?";
try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
preparedStatement.setInt(1, User.getCurrentUser().id);
preparedStatement.setString(2, id);
preparedStatement.executeUpdate();
System.out.println("取消点赞成功。。。");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
查找标题中包含某些字符的文章列表类设计ArticleFindListAction
public class ArticleFindListAction implements Action {
@Override
public void run() {
Scanner sc = new Scanner(System.in);
System.out.println("输入标题相关内容:>");
String str = sc.nextLine();
str = "%" + str + "%";
try (Connection connection = DBUtil.getConnection()) {
List<String[]> articleList = new ArrayList<>();
String sql = "select articles.id, nickname, title, published_at " +
"from boke.articles, boke.users " +
"where author_id=users.id and title like ?";
try (PreparedStatement preparedStatement = connection.prepareStatement(sql)) {
preparedStatement.setString(1, str);
try (ResultSet resultSet = preparedStatement.executeQuery()) {
while (resultSet.next()) {
String[] article = new String[4];
article[0] = resultSet.getString("articles.id");
article[1] = resultSet.getString("nickname");
article[2] = resultSet.getString("title");
article[3] = resultSet.getString("published_at");
articleList.add(article);
}
}
}
System.out.println("#ID | 标题 | 作者 | 发表时间");
for (String[] article : articleList) {
System.out.printf("%-4s | %-40s | %-2s | %s%n", article[0], article[2], article[1], article[3]);
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
3. 项目测试
808
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
