使用Servlet和JDBC编程来实现一个简单的博客系统（1）

一.分析需求

1. 新用户注册。
2. 已有用户登录。
3. 展示博客列表（每一项包含文章标题，作者），点击文章标题就会跳转到文章详情页（不考虑分页功能）。
4. 文章详情页中，可以看到文章标题，作者，内容。
5. 发布新的博客（不考虑富文本编辑，嵌入图片，视频代码片段等）。
6. 删除自己的博客。

二.创建项目 

            在Idea中创建maven项目，在maven仓库中下载mysql connector  和 Servlet API，注意版本对应。 

三.数据库设计 (一对多的关系)

     1.User（userId,name,password)

     2. Article(articleId,title,content,userId) 

  

四.写代码实现数据库的基本操作

1. 创建一个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();
           }
       }
   }
   

2. 创建实体类：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 +
                       '}';
           }
   }
   

3. 实现数据库的基本操作：增删改查 

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