一文实现基于Mysql+Servlet+JSP的作业管理系统

深碍√TFBOYSˉ_ 2023-07-12 08:45 40阅读 0赞

### 文章目录 ###

*  介绍
 *  数据库设计
 *  系统设计
 *  实现
 *   *  1、创建Java EE 项目
     *  2、编写实体类
     *  3、在jdbc类中实现数据库连接，及增、查操作
     *  4、创建HttpServlet与浏览器进行交互
     *  5、jsp页面及css样式设计
     *  问题
     *   *  java的Long.getLong(str)的NullPointerException错误
         *  
         *  form表单提交数据到Servlet时，中文出现乱码问题
 *  优化：编写数据库连接池

# 介绍 #

*  环境  
    操作系统：Windows 10  
    技术环境：jdk13+tomcat7.0  
    工具：Intellij Idea
 *  功能  
    老师：添加作业，添加学生，查询学生作业  
    学生：根据老师添加的作业，进行作业提交
 *  [项目源码][Link 1]

# 数据库设计 #

ER图  
![在这里插入图片描述][20200313094334725.png]

*  `s_homework` ( `id` , `title` , `content` , `create_time` , `update_time` ) PRIMARY KEY (`id`)
 *  `school.s_student`(`id`,`name`,`create_time`,`update_time`) PRIMARY KEY (`id`)
 *  `s_student_homework` ( `id`, `student_id`, `homework_id` , `homework_title` , `homework_content` , `create_time` , `update_time`) PRIMARY KEY (`id`)

# 系统设计 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70]

# 实现 #

## 1、创建Java EE 项目 ##

[IDEA创建java EE项目详细步骤][IDEA_java EE]

## 2、编写实体类 ##

三个实体类，set和get方法可用快捷键（Alt+Insert）自动生成  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70 1]

## 3、在jdbc类中实现数据库连接，及增、查操作 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70 2]**主要步骤**  
（1）IDEA不需要加载驱动，直接创建连接

String url = "jdbc:mysql://127.0.0.1:3306/school";
    String allUrl = url+"?user=root&password=123456&useSSL=false";
    Connection connection = DriverManager.getConnection(allUrl)

（2）通过connection获取statement

Statement statement = connection.createStatement()

（3）通过statement执行sql语句（注意sql语句的写法，不要漏掉单引号）  
增：

String sqlString = "INSERT INTO s_homework (title,content,create_time) VALUES ('"
                    +h.getTitle()+"','"+h.getContent()+"',NOW())";
    int rows=statement.executeUpdate(sqlString);

查：

String sqlString = "SELECT * FROM s_homework";
    ResultSet resultSet = statement.executeQuery(sqlString);

(4) 获取执行结果

try(ResultSet resultSet = statement.executeQuery(sqlString)){
        
                        while (resultSet.next()){
        
                            Homework h = new Homework();
                            h.setId(resultSet.getLong("id"));
                            h.setTitle(resultSet.getString("title"));
                            h.setContent(resultSet.getString("content"));
                            h.setCreateTime(resultSet.getTimestamp("create_time"));
    
                            list.add(h);
                        }

**为了不浪费资源，把操作移到try括号里**  
以HomeworkJdbc.java为例

*  添加作业：

public static boolean addHomework(Homework h) {
        
            String url = "jdbc:mysql://127.0.0.1:3306/school";
            String allUrl = url+"?user=root&password=123456&useSSL=false";
            String sqlString = "INSERT INTO s_homework (title,content,create_time) VALUES ('"
                    +h.getTitle()+"','"+h.getContent()+"',NOW())";
    
    
            try(Connection connection = DriverManager.getConnection(allUrl)){
        
    
                try(Statement statement = connection.createStatement()){
        
                    int rows=statement.executeUpdate(sqlString);
    
                    }
    
            } catch (SQLException e){
        
                e.printStackTrace();
            }
            return true;
        }

*  获取所有作业

public static List<Homework> selectAll(){
        
            String url = "jdbc:mysql://127.0.0.1:3306/school";
            String allUrl = url+"?user=root&password=123456&useSSL=false";
            String sqlString = "SELECT * FROM s_homework";
    
            List<Homework> list = new ArrayList<>();
            try(Connection connection = DriverManager.getConnection(allUrl)){
        
    
                try(Statement statement = connection.createStatement()){
        
                    try(ResultSet resultSet = statement.executeQuery(sqlString)){
        
                        while (resultSet.next()){
        
                            Homework h = new Homework();
                            h.setId(resultSet.getLong("id"));
                            h.setTitle(resultSet.getString("title"));
                            h.setContent(resultSet.getString("content"));
                            h.setCreateTime(resultSet.getTimestamp("create_time"));
    
                            list.add(h);
                        }
                    }
                }
            } catch (SQLException e){
        
                e.printStackTrace();
            }
            return list;
        }

## 4、创建HttpServlet与浏览器进行交互 ##

**HttpServlet**  
HttpServlet首先必须读取Http请求的内容。Servlet容器负责创建HttpServlet对象，并把Http请求直接封装到HttpServlet对象中，大大简化了HttpServlet解析请求数据的工作量。

**HttpServlet容器响应Web客户请求流程如下：**  
1）Web客户向Servlet容器发出Http请求；  
2）Servlet容器解析Web客户的Http请求；  
3）Servlet容器创建一个HttpRequest对象，在这个对象中封装Http请求信息；  
4）Servlet容器创建一个HttpResponse对象；  
5）Servlet容器调用HttpServlet的service方法，把HttpRequest和HttpResponse对象作为service方法的参数传给HttpServlet对象；  
6）HttpServlet调用HttpRequest的有关方法，获取HTTP请求信息；  
7）HttpServlet调用HttpResponse的有关方法，生成响应数据；  
8）Servlet容器把HttpServlet的响应结果传给Web客户。  
**主要步骤**  
1）扩展HttpServlet抽象类  
2）@WebServlet注解  
3）覆盖HttpServlet的覆盖doGet()和doPost()方法  
4）获取HTTP请求信息。通过HttpServletRequest对象来检索HTML表单所提交的数据或URL上的查询字符串  
5）传递request或重定向

**这次项目设计了5个HttpServlet**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70 3]  
**以StudentHomeworkServlet.java为例**

@WebServlet("/studentHomework")
    public class StudentHomeworkServlet extends HttpServlet {
        
        @Override
        protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        
            Long homeworkId = Long.parseLong(req.getParameter("id"));
            List<StudentHomework> list;
            list = StudentHomeworkJdbc.select(homeworkId);
    
            req.setAttribute("list", list);
    
            req.getRequestDispatcher("studentHomework.jsp").forward(req, resp);
    
        }
    
        @Override
        protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        
            Long homeworkId = Long.parseLong(req.getParameter("homeworkId"));
            Long studentId = Long.parseLong(req.getParameter("studentId"));
            List<StudentHomework> list;
            list = StudentHomeworkJdbc.select(homeworkId,studentId);
    
            req.setAttribute("list", list);
    
            req.getRequestDispatcher("studentHomework.jsp").forward(req, resp);
    
        }
    }

## 5、jsp页面及css样式设计 ##

> JavaServer Pages（JSP）是一组技术，可帮助软件开发人员基于HTML，XML，SOAP或其他文档类型创建动态生成的网页。  
> 要部署和运行JSP，需要具有servlet容器的兼容Web服务器，例如Apache Tomcat或Jetty

**教师版**  
教师版首页 homework\_t.jsp![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70 4]  
添加学生 addstudent\_t.jsp  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70 5]  
发布作业 addHomework\_t.jsp  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70 6]  
查看/查询学生作业 studentHomework.jsp  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70 7]  
**学生版**  
学生版首页 homework\_s.jsp  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70 8]  
提交作业 addStudentHomework.jsp  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70 9]

*  css主要设计表单风格

body {
         padding:5px 100px; font:20px/150% Verdana, Tahoma, sans-serif; }
    
    /* tutorial */
    
    input, textarea {
        
        padding: 9px;
        border: solid 1px #E5E5E5;
        outline: 0;
        font: normal 13px/100% Verdana, Tahoma, sans-serif;
        width: 200px;
        background: #FFFFFF left top repeat-x;
        background: -webkit-gradient(linear, left top, left 25, from(#FFFFFF), color-stop(4%, #EEEEEE), to(#FFFFFF));
        background: -moz-linear-gradient(top, #FFFFFF, #EEEEEE 1px, #FFFFFF 25px);
        box-shadow: rgba(0,0,0, 0.1) 0px 0px 8px;
        -moz-box-shadow: rgba(0,0,0, 0.1) 0px 0px 8px;
        -webkit-box-shadow: rgba(0,0,0, 0.1) 0px 0px 8px;
    }
    
    textarea {
        
        width: 400px;
        max-width: 400px;
        height: 150px;
        line-height: 150%;
    }
    
    input:hover, textarea:hover,
    input:focus, textarea:focus {
        
        border-color: #C9C9C9;
        -webkit-box-shadow: rgba(0, 0, 0, 0.15) 0px 0px 8px;
    }
    
    .form label {
        
        margin-left: 10px;
        color: #999999;
    }
    
    .submit input {
        
        width: auto;
        padding: 9px 15px;
        background: #617798;
        border: 0;
        font-size: 14px;
        color: #FFFFFF;
        -moz-border-radius: 5px;
        -webkit-border-radius: 5px;
    }

## 问题 ##

### java的Long.getLong(str)的NullPointerException错误 ###

解决：替换成Long.parseLong(String str)

<table> 
 <thead> 
  <tr> 
   <th>getLong(String nm)</th> 
   <th>parselong(String s)</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Determines the long value of the system property with the specified name.</td> 
   <td>Parses the string argument as a signed decimal long</td> 
  </tr> 
 </tbody> 
</table>

###  ###

### form表单提交数据到Servlet时，中文出现乱码问题 ###

解决：添加语句

/*浏览器提交的数据在提交给服务器之前设置编码方式为UTF-8*/
            req.setCharacterEncoding("UTF-8");

<table> 
 <thead> 
  <tr> 
   <th>数据来源</th> 
   <th>默认编码格式</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>浏览器页面</td> 
   <td>GBK (可在浏览器页面右键切换)</td> 
  </tr> 
  <tr> 
   <td>request（get）</td> 
   <td>ISO-8859-1</td> 
  </tr> 
  <tr> 
   <td>request（post）</td> 
   <td>GBK(同浏览器)，但是如果是服务器来的页面一般已经设置了UTF-8(例如JSP提交的页面)</td> 
  </tr> 
  <tr> 
   <td>Servlet（response）</td> 
   <td>ISO-8859-1</td> 
  </tr> 
 </tbody> 
</table>

# 优化：编写数据库连接池 #

package com.java.code.jdbc;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.util.LinkedList;
    
    /**
     *优化数据库连接
     * 实现简单连接池
     * @author:Jingqi Wu
     * @date: 2020/3/15
     */
    public class ConPool {
        
    
        private static String url = "jdbc:mysql://127.0.0.1:3306/school";
        private static String allUrl = url+"?user=root&password=123456&useSSL=false";
        private static String driverName = "com.mysql.cj.jdbc.Driver";
    
        private static int initCount = 3;
        private static int maxCount = 10;
        private static int currentCount = 0;
    
        private LinkedList<Connection> connectionsPool =
                new LinkedList<Connection>();
    
        public ConPool()
    
        {
        
            for(; currentCount < initCount; ) {
        
                try {
        
                    // 连接池需要加载驱动？
                    Class.forName(driverName);
                    // 通过jdbc建立数据库连接
                    Connection connection = DriverManager.getConnection(allUrl);
                    this.connectionsPool.add(connection);
                    currentCount++;
                } catch (SQLException | ClassNotFoundException e) {
        
                    throw new ExceptionInInitializerError(e);
                }
            }
        }
    
        // 从连接池中拿走一个连接
        public Connection getConnection() throws SQLException{
        
            System.out.println("currentCount:"+currentCount+";list size:"+connectionsPool.size());
            synchronized (connectionsPool){
        
                if(connectionsPool.size()>0){
        
                    return connectionsPool.removeFirst();
                }
                if(connectionsPool.size()< maxCount){
        
                    Connection connection = DriverManager.getConnection(allUrl);
                    currentCount++;
                    return connection;
                }
            }
            throw new SQLException("超过最大连接");
        }
    
        // 释放连接，把连接放到连接池
        public void free(Connection connection){
        
    
            this.connectionsPool.add(connection);
    
        }
    
    }

[Link 1]: https://github.com/BeJane/homeworkMS
[20200313094334725.png]: https://img-blog.csdnimg.cn/20200313094334725.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200313123149720.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4,size_16,color_FFFFFF,t_70
[IDEA_java EE]: https://blog.csdn.net/qq_39753778/article/details/104618555
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200313122320570.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200313123541177.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200313132034340.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200313164609272.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200313171609198.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/2020031316561992.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200313170818709.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200313171137110.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20200313171707283.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5NzUzNzc4,size_16,color_FFFFFF,t_70