目录
整体设计流程:
1.分析需求
a)注册
b)登录
c)查看博客列表
d)查看博客详情
e)新增博客
f)删除博客
2.maven 项目
导入依赖:
mysql connector
Servlet API
3.数据库设计:
两张表:
User:userId name password
Article:articleId,title,content,userId
4.写代码
a)创建一个DBUtil 类,来管理连接
b)创建实体类 User类 和 Article类
c)实现数据库的基本的增删改查
d)进行单元测试
0.分析需求
1)注册新用户
2)登录已有账户
3)展示博客列表(每一项包含了文章的标题,作者)
4)文章详情中,可以看到文章标题,作者,文章内容
5)发布新的博客
6)删除自己的博客
1.创建一个maven项目
创建maven的过程可以参考:编写一个简单的客户端服务器
2.在pom.xml中加入依赖
- Servlet API
- MySQL connector
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>3.1-b06</version>
<scope>provided</scope>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
3.数据库的设计
设计当前的代码中用到的数据库有几张表,每个表有哪些字段,每个表之间是什么关系
1)提取出需求中的“实体”
实体:关键性的名词,设计的数据库的表,就是需要保存这些实体的
以上需要设计的实体(用户 文章/博客)
每个实体先分配一张表
2)理清实体需要关注的属性(确定两张表中,都需要哪些字段)
用户:名字 密码
文章:标题 内容
3)理清实体和实体之间的关系(一对一,一对多,多对多,没关系)
由于一个用户,可能有多个文章
a)user 表中加入一个userId 字段,身份标识
b)Article 表中加入一个userId字段,标识该文章是哪个用户发的
drop database if exists blogDemo;
create database blogDemo;
use blogDemo;
drop table if exists user;
create table user(
userId int primary key auto_increment,
name varchar(50) unique,
password varchar(50)
);
drop table if exists article;
create table article(
articleId int primary key auto_increment,
title varchar(50),
context text,
userId int,
foreign key(userId) references user(userId)
);
4.写代码
先实现数据库的基本操作
把两张表所涉及到的增删改查操作,进行封装
(a)创建一个类,管理数据库的连接
为了使后面的代码用起来方便
懒汉实现的单例模式有一个重要的问题:
线程不安全~~
首次使用getDataSource,如果是多线程调用,就可能会产生线程不安全的问题
如果保证线程安全?
1.加锁
2.双重if判断
3.volatile
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;
/**
* @program: test_maven
* @description
* @author: wenwen
* @create: 2021-08-15 10:45
**/
//用于管理数据库连接
//1)建立连接
//2)断开连接
//3)JDBC 使用DataSource 来管理连接
//4)DBUtil 相当于是对 DataSource 再少位包装一层
//DataSource 每个应用程序只应该有一个实例~~(单例)
// DBUtil 本质上是实现了一个单例模式,管理了唯一一个 DataSource 实例
//单例模式有两种风格:
//1.饿汉模式
//2.懒汉模式
//此处使用懒汉模式即可
public class DBUtil {
private static DataSource dataSource = null;
private static final String URL ="jdbc:mysql://127.0.0.1:3306/java16_blogDemo?characterEncoding=utf-8&useSSL=true";
private static final String USERNAME= "root";
private static final String PASSWORD= "root";
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() throws SQLException {
try {
return getDataSource().getConnection();
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//通过这个方法来关闭连接
public static void close(Connection connection, PreparedStatement statement, ResultSet resultSet) throws SQLException {
try {
if(resultSet != null){
resultSet.close();
}
if(statement != null){
statement.close();
}
if(connection != null){
connection.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
b)创建实体类
创建一个 User 类和 Article 类~~
这两个类的属性,要和数据库表结构关联
Article类:
package model;
/**
* @program: test_maven
* @description
* @author: wenwen
* @create: 2021-08-15 11:13
**/
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 +
'}';
}
}
User类:
package model;
/**
* @program: test_maven
* @description
* @author: wenwen
* @create: 2021-08-15 11:11
**/
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 + '\'' +
'}';
}
}
c)实现数据库的基本的增删改查
UserDao:
package model;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* @program: test_maven
* @description
* @author: wenwen
* @create: 2021-08-15 11:38
**/
public class UserDao {
//1.新增用户(注册)
//把一个User 对象插入到数据库中
void add(User user) throws SQLException {
//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) throws SQLException {
//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.遍历结果集
// 此处只能查找出一条记录
while(resultSet.next()){
User user = new User();
user.setUserId(resultSet.getInt("userId"));
user.setPassword(resultSet.getString("password"));
user.setName(resultSet.getString("name"));
return user;
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
DBUtil.close(connection,statement,resultSet);
}
return null;
}
//进行单元测试
public static void main(String[] args) throws SQLException {
//1.先测试 add 方法
UserDao userDao = new UserDao();
User user = new User();
user.setName("tx");
user.setPassword("1234");
userDao.add(user);
}
}
ArticleDao:
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;
/**
* @program: test_maven
* @description
* @author: wenwen
* @create: 2021-08-15 11:38
**/
public class ArticleDao {
//1.新增文章(发布博客)
public void add(Article article) throws SQLException {
//1.获取数据库连接
Connection connection = DBUtil.getConnection();
//2.构造sql
String sql = "insert into article values(null,?,?,?)";
// PreparedStatement statement = null;
PreparedStatement statement = connection.prepareStatement(sql);
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() throws SQLException {
List<Article> list = new ArrayList<Article>();
//1.建立连接
Connection connection = DBUtil.getConnection();
//2.拼装sql
String sql = "select articleId,title,userId from article";
PreparedStatement statement = null;
ResultSet resultSet = null;
try {
//3.执行sql
statement = connection.prepareStatement(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"));
list.add(article);
}
return list;
} catch (SQLException e) {
e.printStackTrace();
}finally {
//5.释放连接
DBUtil.close(connection,statement,null);
}
return null;
}
//3.查看指定文章详情(需要查正文)
public Article selectById(int articleId) throws SQLException {
//1.建立数据库连接
Connection connection = DBUtil.getConnection();
PreparedStatement statement = null;
ResultSet resultSet = null;
//2.构造sql
String sql = "select * from article where articleId = ?";
try {
statement = connection.prepareStatement(sql);
statement.setInt(1,articleId);
//3.执行sql
resultSet = statement.executeQuery();
//4.遍历结果集
//由于 articleid 是主键,不会重复,顶多只能查出一条记录
if(resultSet.next()){
Article article = new Article();
article.setArticleId(resultSet.getInt("articleId"));
article.setTitle(resultSet.getString("title"));
article.setTitle(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) throws SQLException {
//1.建立数据库连接
Connection connection = DBUtil.getConnection();
//2.拼装sql
String sql = "delete from article where articleId = ?";
PreparedStatement statement = null;
try {
//3.执行sql语句
statement = connection.prepareStatement(sql);
statement.setInt(1,articleId);
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) throws SQLException {
ArticleDao articleDao = new ArticleDao();
//1.测试新增文章
// Article article = new Article();
// article.setTitle("我是标题");
// article.setUserId(1);
// article.setContent("我是正文正文是正文正文是正文正文是正文正文是正文正文是正文正文是正文正文是正文正文是正文正文是正文正文是正文正文是正文正文是正文正文是正文正文");
// 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(1);
articleDao.selectById(1);
}
}
5.进行单元测试
单元测试:把一些类/方法(拥有相对完整的功能),视为一个单元,针对这个单元进行测试。
上述代码的测试过成比较简单粗暴,还有不少不合理的地方
例如:
1.测试过的代码需要注释掉
2.有些测试需要结合数据库来查看
3.想要进行一些更复杂的操作(重复执行N词,打乱顺序执行用例等。。。)
6.使用junit框架进行单元测试
使用junit框架就能解决上述问题。。。
(1)首先安装junit插件
(2) 导入junit的依赖
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<scope>compile</scope>
</dependency>
(3)找到要进行测试的类,选中类名,ctrl + shift + T,创建单元测试的类,进行测试
package model;
import org.junit.Test;
import java.sql.SQLException;
import static org.junit.Assert.*;
/**
* @program: test_maven
* @description
* @author: wenwen
* @create: 2021-08-15 18:36
**/
public class UserDaoTest {
User user = new User();
UserDao userDao = new UserDao();
@Test
public void add() throws SQLException {
user.setPassword("1212");
user.setName("liying");
userDao.add(user);
}
@Test
public void selectByName() throws SQLException {
System.out.println(userDao.selectByName("liying"));
}
@Test
public void main() {
}
}
package model;
import org.junit.Test;
import java.sql.SQLException;
/**
* @program: test_maven
* @description
* @author: wenwen
* @create: 2021-08-15 18:39
**/
public class ArticleDaoTest {
ArticleDao articleDao = new ArticleDao();
Article article = new Article();
@Test
public void add() throws SQLException {
article.setTitle("标题一");
article.setContent("正文一");
article.setUserId(1);
articleDao.add(article);
}
@Test
public void selectAll() throws SQLException {
System.out.println(articleDao.selectAll());
}
@Test
public void selectById() throws SQLException {
System.out.println(articleDao.selectById(3));
}
@Test
public void delete() throws SQLException {
articleDao.delete(1);
}
@Test
public void main() {
}
}