一.分析需求
- 新用户注册。
- 已有用户登录。
- 展示博客列表(每一项包含文章标题,作者),点击文章标题就会跳转到文章详情页(不考虑分页功能)。
- 文章详情页中,可以看到文章标题,作者,内容。
- 发布新的博客(不考虑富文本编辑,嵌入图片,视频代码片段等)。
- 删除自己的博客。
二.创建项目
在Idea中创建maven项目,在maven仓库中下载mysql connector 和 Servlet API,注意版本对应。
三.数据库设计 (一对多的关系)
1.User(userId,name,password)
2. Article(articleId,title,content,userId)
四.写代码实现数据库的基本操作
- 创建一个DBUtil类管理连接
package model; import com.mysql.jdbc.jdbc2.optional.MysqlDataSource; import javax.sql.DataSource; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; // 管理数据库连接 // 1) 建立连接 // 2) 断开连接 // JDBC 中使用 DataSource 来管理连接. // DBUtil 相当于是对 DataSource 再稍微包装一层. // DataSource 每个应用程序只应该有一个实例~~ (单例) // DBUtil 本质上就是实现了一个单例模式, 管理了唯一的一个 DataSource 实例 // 单例模式的实现, 有两种风格: // 1. 饿汉模式 // 2. 懒汉模式. // 此处使用懒汉模式 public class DBUtil { private static volatile DataSource dataSource = null; private static final String URL = "jdbc:mysql://127.0.0.1:3306/java1109_blogdemo?characterEncoding=utf-8&useSSL=true"; private static final String USERNAME = "root"; private static final String PASSWORD = "123456"; public static DataSource getDataSource() { if (dataSource == null) { synchronized (DBUtil.class) { if (dataSource == null) { dataSource = new MysqlDataSource(); // 还需要给 DataSource 设置一些属性. ((MysqlDataSource)dataSource).setURL(URL); ((MysqlDataSource)dataSource).setUser(USERNAME); ((MysqlDataSource)dataSource).setPassword(PASSWORD); } } } return dataSource; } // 通过这个方法来获取连接 public static Connection getConnection() { try { return getDataSource().getConnection(); } catch (SQLException e) { e.printStackTrace(); } return null; } // 通过这个方法来断开连接 public static void close(Connection connection, PreparedStatement statement, ResultSet resultSet) { try { if (resultSet != null) { resultSet.close(); } if (statement != null) { statement.close(); } if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } }
- 创建实体类:User,Article
package model; public class User { private int userId; private String name; private String password; public int getUserId() { return userId; } public void setUserId(int userId) { this.userId = userId; } public String getName() { return name; } public void setName(String name) { this.name = name; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } @Override public String toString() { return "User{" + "userId=" + userId + ", name='" + name + '\'' + ", password='" + password + '\'' + '}'; } }
package model; public class Article { private int articleId; private String title; private String content; private int userId; public int getArticleId() { return articleId; } public void setArticleId(int articleId) { this.articleId = articleId; } public String getTitle() { return title; } public void setTitle(String title) { this.title = title; } public String getContent() { return content; } public void setContent(String content) { this.content = content; } public int getUserId() { return userId; } public void setUserId(int userId) { this.userId = userId; } @Override public String toString() { return "Article{" + "articleId=" + articleId + ", title='" + title + '\'' + ", content='" + content + '\'' + ", userId=" + userId + '}'; } }
- 实现数据库的基本操作:增删改查
package model;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class UserDao {
// 1. 新增用户(注册)
// 把一个 User 对象插入到数据库中.
public void add(User user) {
// (1). 获取到数据库连接.
Connection connection = DBUtil.getConnection();
// (2). 拼装 SQL 语句
String sql = "insert into user values (null, ?, ?)";
PreparedStatement statement = null;
try {
statement = connection.prepareStatement(sql);
statement.setString(1, user.getName());
statement.setString(2, user.getPassword());
// (3). 执行 SQL 语句
int ret = statement.executeUpdate();
if (ret != 1) {
System.out.println("插入新用户失败!");
return;
}
System.out.println("插入新用户成功");
} catch (SQLException e) {
e.printStackTrace();
} finally {
// (4). 释放数据库的连接.
DBUtil.close(connection, statement, null);
}
}
// 2. 按照名字查找用户(登陆)
public User selectByName(String name) {
// (1). 和数据库建立连接.
Connection connection = DBUtil.getConnection();
// (2). 拼装 SQL
String sql = "select * from user where name = ?";
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
statement = connection.prepareStatement(sql);
statement.setString(1, name);
// (3). 执行 SQL
resultSet = statement.executeQuery();
// (4). 遍历结果集. 预期 name 在数据库中不能重复.
// 此处查找最多只能查出一条记录来.
if (resultSet.next()) {
User user = new User();
user.setUserId(resultSet.getInt("userId"));
user.setName(resultSet.getString("name"));
user.setPassword(resultSet.getString("password"));
return user;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(connection, statement, resultSet);
}
return null;
}
// 3.根据用户 id 找到用户信息
public User selectById(int userId) {
// (1). 和数据库建立连接.
Connection connection = DBUtil.getConnection();
// (2). 拼装 SQL
String sql = "select * from user where userId = ?";
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
statement = connection.prepareStatement(sql);
statement.setInt(1, userId);
// (3). 执行 SQL
resultSet = statement.executeQuery();
// (4). 遍历结果集. 预期 name 在数据库中不能重复.
// 此处查找最多只能查出一条记录来.
if (resultSet.next()) {
User user = new User();
user.setUserId(resultSet.getInt("userId"));
user.setName(resultSet.getString("name"));
user.setPassword(resultSet.getString("password"));
return user;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil .close(connection, statement, resultSet);
}
return null;
}
public static void main(String[] args) {
UserDao userDao = new UserDao();
// 1. 先测试 add 方法
/*User user = new User();
user.setName("tmr");
user.setPassword("123");
userDao.add(user);
// 2. 测试 selectByName
User user = userDao.selectByName("tmr");
System.out.println(user);*/
}
}
package model;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class ArticleDao {
// 1. 新增文章(发布博客)
public void add(Article article) {
// 1. 获取数据库连接
Connection connection = DBUtil.getConnection();
// 2. 构造 SQL
String sql = "insert into article values (null, ?, ?, ?)";
PreparedStatement statement = null;
try {
statement = connection.prepareStatement(sql);
statement.setString(1, article.getTitle());
statement.setString(2, article.getContent());
statement.setInt(3, article.getUserId());
// 3. 执行 SQL
int ret = statement.executeUpdate();
if (ret != 1) {
System.out.println("执行插入文章操作失败");
return;
}
System.out.println("执行插入文章操作成功");
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 4. 释放连接.
DBUtil.close(connection, statement, null);
}
}
// 2. 查看文章列表(把所有的文章信息都查出来(没必要查正文))
public List<Article> selectAll() {
List<Article> articles = new ArrayList<>();
// 1. 建立连接
Connection connection = DBUtil.getConnection();
// 2. 拼装 SQL
String sql = "select articleId, title, userId from article";
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
if (connection != null) {
statement = connection.prepareStatement(sql);
}
// 3. 执行 SQL
resultSet = statement.executeQuery();
// 4. 遍历结果集
while (resultSet.next()) {
// 针对每个结果记录, 都构造一个对应的 Article 对象.
// 此时由于没有从数据库中读 content 字段, 这个字段暂时先不设置.
Article article = new Article();
article.setArticleId(resultSet.getInt("articleId"));
article.setTitle(resultSet.getString("title"));
article.setUserId(resultSet.getInt("userId"));
articles.add(article);
}
return articles;
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(connection, statement, resultSet);
}
return null;
}
// 3. 查看指定文章详情. (需要查正文)
public Article selectById(int articleId) {
// 1. 建立数据库连接
Connection connection = DBUtil.getConnection();
// 2. 构造 SQL
String sql = "select * from article where articleId = ?";
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
statement = connection.prepareStatement(sql);
statement.setInt(1, articleId);
// 3. 执行 SQL
resultSet = statement.executeQuery();
// 4. 遍历结果集合.
// 由于 id 是主键. 不会重复. 预期最多只能查出一条记录.
if (resultSet.next()) {
Article article = new Article();
article.setArticleId(resultSet.getInt("articleId"));
article.setTitle(resultSet.getString("title"));
article.setContent(resultSet.getString("content"));
article.setUserId(resultSet.getInt("userId"));
return article;
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 5. 释放连接.
DBUtil.close(connection, statement, resultSet);
}
return null;
}
// 4. 删除指定文章(给定文章 id 来删除)
public void delete(int articleId) {
// 1. 获取连接
Connection connection = DBUtil.getConnection();
// 2. 拼装 SQL
String sql = "delete from article where articleId = ?";
PreparedStatement statement = null;
try {
statement = connection.prepareStatement(sql);
statement.setInt(1, articleId);
// 3. 执行 SQL
int ret = statement.executeUpdate();
if (ret != 1) {
System.out.println("删除文章失败");
return;
}
System.out.println("删除文章成功");
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 4. 释放连接.
DBUtil.close(connection, statement, null);
}
}
public static void main(String[] args) {
ArticleDao articleDao = new ArticleDao();
// 1. 测试新增文章
Article article = new Article();
article.setTitle("woshibiaoti1");
article.setContent("zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1zhengwen1");
article.setUserId(1);
articleDao.add(article);
// 2. 测试查看文章列表
List<Article> articles = articleDao.selectAll();
System.out.println(articles);
// 3. 查看指定文章内容
//Article article = articleDao.selectById(1);
//System.out.println(article);
// 4. 删除文章
// articleDao.delete(2);
}
}