JavaEE-博客系统1（数据库和后端的交互）-CSDN博客

本部分内容包括网站设计总述数据库和后端的交互

---

数据库操作代码如下

-- 编写SQL完成建库建表操作
create database if not exists java_blog_system charset utf8;
use java_blog_system;
-- 建立两张表一个存储博客信息一个存储用户信息
drop table if exists user;
drop table if exists blog;

create table blog(
-- 主键必须包含唯一的值    主键列不能包含null值  设置主键进行自增长默认从1开始每次+1
blogId int primary key auto_increment,
title varchar(256),
content varchar(4096),
userId int,
postTime datetime
);

create table user(
userId int primary key auto_increment,
username varchar(64) unique,
password varchar(64)
);

-- 构造一些初始数据方便后续的测试
insert into user values(1,'zhansan','123'),(2,'lisi','123');

insert into blog values(1,'这是第一篇','这里是内容',1,'2023-06-07 18:00:00');
insert into blog values(2,'这是第一篇','这里是内容',1,'2023-06-07 18:00:00');
insert into blog values(3,'这是第一篇','这里是内容',1,'2023-06-07 18:00:00');

---

DBUtil.java

package Dao;

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;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: Home-pc
 * Date: 2023-10-28
 * Time: 11:07
 */
//通过这个类将数据库建立连接和断开连接的逻辑进行封装
public class DBUtil {
    //进行连接前的准备工作初始化数据库地址用户名密码
    //单例模式   只需要一个实例
    //volatile 禁止指令重排序
    private  static volatile DataSource dataSource=null;

    //提供一个方法获取datasource
    private static DataSource getDataSource(){
        if(dataSource==null){//此处if的作用在于避免频繁加锁如果dataSource已经有值再进行加锁他会很快的解锁但是这会导致频繁枷锁因此一旦发现ataSource已经有值就直接返回该值
            synchronized (DBUtil.class){//为了保证线程安全(多线程问题)加锁
                if(dataSource==null){//第二个if判断是否为空当a线程优先获得锁执行到此处b线程没竞争到锁会被阻塞在外面a线程判断实例是否为空为空则new实例a线程释放锁之后b线程拿到锁进来后先判断instance是否为null此时可能因上一个线程导致此处不为null则释放锁往下或者为null
                    dataSource=new MysqlDataSource();
                    ((MysqlDataSource)dataSource).setUrl("jdbc:mysql://127.0.0.1:3306/java_blog_system?useSSL=false&characterEncoding=utf8");
                    ((MysqlDataSource)dataSource).setUser("root");
                    ((MysqlDataSource)dataSource).setPassword("1111");
                }
            }
        }
        return dataSource;
    }

    //提供一个方法和数据库建立连接
    public static Connection getConnection(){
        try {
            return getDataSource().getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    //提供一个方法和数据库断开连接 PreparedStatement statement用来执行SQL语句   ResultSet resultSet接收执行SQL语句后返回的结果
    public static void close(Connection connection, PreparedStatement statement, ResultSet resultSet){
        //将3个close放置到3个try中即使前面的close出现问题也不会影响后续close的执行
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

blog.java

package Dao;

import java.sql.Timestamp;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: Home-pc
 * Date: 2023-10-28
 * Time: 12:13
 */
//这个类中的属性要和数据库中blog表中的属性相对应
//通过这个类的对象能够表示出一条blog表中的记录
public class Blog {
    private int blogId;
    private String title;
    private String content;
    private int UserId;
    // SQL 里有 timestamp 类型, 还有 datetime 类型.
    // 使用 SQL 时, 推荐使用 datetime, 因为 timestamp 只有 4 字节, 2038 年就不够用了.
    // 但是 Java 代码中的 Timestamp 是可以使用的.
    private Timestamp postTime;

    public int getBlogId() {
        return blogId;
    }

    public void setBlogId(int blogId) {
        this.blogId = blogId;
    }

    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;
    }

    public Timestamp getPostTime() {
        return postTime;
    }

    public void setPostTime(Timestamp postTime) {
        this.postTime = postTime;
    }

    @Override
    public String toString() {
        return "Blog{" +
                "blogId=" + blogId +
                ", title='" + title + '\'' +
                ", content='" + content + '\'' +
                ", userId=" + UserId +
                ", postTime=" + postTime +
                '}';
    }
}

user.java

package Dao;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: Home-pc
 * Date: 2023-10-28
 * Time: 13:32
 */
//这个类的属性要和user表里的一致
    //通过这个类的对象表示user表中的一条记录
public class User {
    private int userId;
    private String username;
    private String password;

    public int getUserId() {
        return userId;
    }

    public void setUserId(int userId) {
        this.userId = userId;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return "User{" +
                "userId=" + userId +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                '}';
    }
}

blogDao.java

package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: Home-pc
 * Date: 2023-10-28
 * Time: 13:36
 */
//通过这个类封装对blog表的增删改查
public class BlogDao {
    //1.新增一个博客,构造一个insert方法
    public void insert(Blog blog){
        Connection connection=null;
        PreparedStatement statement=null;

        try {
            //和数据库建立连接
            connection =DBUtil.getConnection();
            //构造SQL语句
            String sql="insert into blog values(null,?,?,?,now())";
            //准备好sql语句
            statement=connection.prepareStatement(sql);
            statement.setString(1, blog.getTitle());
            statement.setString(2, blog.getContent());
            statement.setInt(3,blog.getUserId());
            //执行SQL语句
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            //关闭连接释放资源
            DBUtil.close(connection,statement,null);
        }
    }

    //2.查询blog表中的所有博客
    public List<Blog> getblogs(){
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;
        List<Blog> blogs=new ArrayList<>();//此时list为空

        try {
            //和数据库建立连接
            connection=DBUtil.getConnection();
            //构造sql语句
            String sql="select * from blog order by postTime desc";//按时间顺序降序排序
            //准备语句
            statement=connection.prepareStatement(sql);
            //执行sql
            resultSet=statement.executeQuery();
            //遍历结果集合
            while(resultSet.next()){
                Blog blog=new Blog();
                //前面是java类中的属性后面则是从数据库的表中对应属性的值
                blog.setBlogId(resultSet.getInt("blogId"));
                blog.setTitle(resultSet.getString("title"));
                blog.setContent(resultSet.getString("content"));
                blog.setUserId(resultSet.getInt("userId"));
                blog.setPostTime(resultSet.getTimestamp("postTime"));
                blogs.add(blog);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return blogs;
    }

    //指定ID查询某一个博客
    public Blog getblog(int blogId){
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;

        try {
            connection=DBUtil.getConnection();
            String sql="select * from blog where blogId=?";
            statement.setInt(1,blogId);
            resultSet=statement.executeQuery();
            // 由于此处是按照 blogId 来查询, blogId 又是主键.
            // 查询到的结果要么是 1 条记录, 要么是 0 条记录. 不会有别的情况.
            // 因此这里就没必要循环了, 直接条件判定即可.
            if(resultSet.next()){//查询到了则进入否则不会进来
                Blog blog=new Blog();
                blog.setBlogId(resultSet.getInt("blogId"));
                blog.setTitle(resultSet.getString("title"));
                blog.setContent(resultSet.getString("content"));
                blog.setUserId(resultSet.getInt("userId"));
                blog.setPostTime(resultSet.getTimestamp("postTime"));
                return blog;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return null;
    }

    //指定博客进行删除
    public void delete(int blogId){
        Connection connection=null;
        PreparedStatement statement=null;

        try {
            connection=DBUtil.getConnection();
            String sql="delete from blog where blogId=?";
            statement=connection.prepareStatement(sql);
            statement.setInt(1,blogId);
            statement.executeQuery();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection, statement,null );
        }
    }

}

userDao.java

package Dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * User: Home-pc
 * Date: 2023-10-28
 * Time: 14:50
 */
//使用这个类封装对user表的增删改查
public class UserDao {
    //根据userId查询用户信息
    public User getUserById(int userId){
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;

        try {
            connection=DBUtil.getConnection();
            String sql="select * from user where userId = ?";
            statement=connection.prepareStatement(sql);
            statement.setInt(1,userId);
            resultSet=statement.executeQuery();
            if(resultSet.next()){
                User user=new User();
                user.setUserId(resultSet.getInt("userId"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return null;
    }

    //根据username来查询用户信息
    public User getUserByName(String username){
        Connection connection=null;
        PreparedStatement statement=null;
        ResultSet resultSet=null;

        try {
            connection=DBUtil.getConnection();
            String sql="select * from user where username = ?";
            statement=connection.prepareStatement(sql);
            statement.setString(1,username);
            resultSet=statement.executeQuery();
            if(resultSet.next()){
                User user=new User();
                user.setUserId(resultSet.getInt("userId"));
                user.setUsername(resultSet.getString("username"));
                user.setPassword(resultSet.getString("password"));
                return user;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            DBUtil.close(connection,statement,resultSet);
        }
        return null;
    }
}