Servlet+JSP+JDBC综合案例

层级关系：

一、Util包

包里面写一个JDBCTools.java文件

功能：实现数据库连接返回一个Connection对象，并且可以实现数据库相应资源的关闭！

注意事项：

1、定义成员变量

1 　　 private static  Connection connection ;
2     private static  String url="jdbc:mysql://localhost:3306/info?useUnicode=true&CharacterEncoding=utf-8";
3     private static  String user="root";
4     private static  String password="000429";

2、使用静态代码块包围加载驱动的部分，只执行一次！

1 static {
2         try {
3             Class.forName("com.mysql.jdbc.Driver");
4         } catch (ClassNotFoundException e) {
5             // TODO 自动生成的 catch 块
6             e.printStackTrace();
7         }
8     }

3、写一个静态方法（返回值是Connection）getConnection( )

 1 public static Connection getConnection() {
 2         
 3         try {
 4             connection=(Connection) DriverManager.getConnection(url,user,password);
 5         } catch (SQLException e) {
 6             // TODO 自动生成的 catch 块
 7             e.printStackTrace();
 8         }
 9         
10         return connection;
11     }

4、方法重载releas( )按连接对象 connection 表的工作空间 preparedStatement 和结果集 resultSet 的顺序依次关闭（注意判断null）

public static void release(Connection connection,PreparedStatement preparedStatement ,ResultSet resultSet)
    {
        if(connection!=null) {
            try {
                connection.close();
            } catch (SQLException e) {
                // TODO 自动生成的 catch 块
                e.printStackTrace();
            }
        }
        if(preparedStatement!= null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                // TODO 自动生成的 catch 块
                e.printStackTrace();
            }
        }
        if(resultSet != null) {
            try {
                resultSet.close();
            } catch (SQLException e) {
                // TODO 自动生成的 catch 块
                e.printStackTrace();
            }
        }
    }
    public static void release(Connection connection,PreparedStatement preparedStatement)
    {
        if(connection!=null) {
            try {
                connection.close();
            } catch (SQLException e) {
                // TODO 自动生成的 catch 块
                e.printStackTrace();
            }
        }
        if(preparedStatement!= null) {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                // TODO 自动生成的 catch 块
                e.printStackTrace();
            }
        }
    }

下面给出这个包（类）完整代码

 1 package Util;
 2 
 3 import java.sql.DriverManager;
 4 import java.sql.PreparedStatement;
 5 import java.sql.ResultSet;
 6 import java.sql.SQLException;
 7 
 8 import com.mysql.jdbc.Connection;
 9 
10 public class JBDCTools {
11     private static  Connection connection ;
12     private static  String url="jdbc:mysql://localhost:3306/info?useUnicode=true&CharacterEncoding=utf-8";
13     private static  String user="root";
14     private static  String password="000429";
15     static {
16         try {
17             Class.forName("com.mysql.jdbc.Driver");
18         } catch (ClassNotFoundException e) {
19             // TODO 自动生成的 catch 块
20             e.printStackTrace();
21         }
22     }
23     public static Connection getConnection() {
24         
25         try {
26             connection=(Connection) DriverManager.getConnection(url,user,password);
27         } catch (SQLException e) {
28             // TODO 自动生成的 catch 块
29             e.printStackTrace();
30         }
31         
32         return connection;
33     }
34     public static void release(Connection connection,PreparedStatement preparedStatement ,ResultSet resultSet)
35     {
36         if(connection!=null) {
37             try {
38                 connection.close();
39             } catch (SQLException e) {
40                 // TODO 自动生成的 catch 块
41                 e.printStackTrace();
42             }
43         }
44         if(preparedStatement!= null) {
45             try {
46                 preparedStatement.close();
47             } catch (SQLException e) {
48                 // TODO 自动生成的 catch 块
49                 e.printStackTrace();
50             }
51         }
52         if(resultSet != null) {
53             try {
54                 resultSet.close();
55             } catch (SQLException e) {
56                 // TODO 自动生成的 catch 块
57                 e.printStackTrace();
58             }
59         }
60     }
61     public static void release(Connection connection,PreparedStatement preparedStatement)
62     {
63         if(connection!=null) {
64             try {
65                 connection.close();
66             } catch (SQLException e) {
67                 // TODO 自动生成的 catch 块
68                 e.printStackTrace();
69             }
70         }
71         if(preparedStatement!= null) {
72             try {
73                 preparedStatement.close();
74             } catch (SQLException e) {
75                 // TODO 自动生成的 catch 块
76                 e.printStackTrace();
77             }
78         }
79     }
80 }

二、数据结构层（enity包定义Student类）

 1 package enity;
 2 
 3 import java.sql.Date;
 4 
 5 public class Student {
 6     private Integer id;
 7     private String studentname;
 8     private String score;
 9     private Date birthday;
10     public Integer getId() {
11         return id;
12     }
13     public Student(Integer id, String studentname, String score, Date birthday) {
14         super();
15         this.id = id;
16         this.studentname = studentname;
17         this.score = score;
18         this.birthday = birthday;
19     }
20     
21     @Override
22     
23     public String toString() {
24         return "Student [id=" + id + ", studentname=" + studentname + ", score=" + score + ", birthday=" + birthday + "]";
25     }
26     public void setId(Integer id) {
27         this.id = id;
28     }
29     public String getstudentname() {
30         return studentname;
31     }
32     public void setstudentname(String studentname) {
33         this.studentname = studentname;
34     }
35     public String getScore() {
36         return score;
37     }
38     public void setScore(String score) {
39         this.score = score;
40     }
41     public Date getBirthday() {
42         return birthday;
43     }
44     public void setBirthday(Date birthday) {
45         this.birthday = birthday;
46     }
47     
48 }

三、repository包（StudentRepository类）数据层，完成数据库的相关操作

注意事项：

1、注意方法的返回值是否需要封装数据结构用List集合，比如添加方法，需要返回一个数据结构集合给servlet，以便于servlet返回数据+视图

　方法内部的Connection 和 ResultSet 初始化要为 null 在最外部

1 public List<Student> findAll(){
2         //初始化null值的三个常用变量
3         Connection connection = null;
4         ResultSet resultSet=null;
5         PreparedStatement preparedStatement=null;
6　　　　 　List<Student> list=new ArrayList<>();

2、调用JDBCTools类的静态连接方法进行连接，

　　写SQL语句，执行SQL语句，

　　封装从数据库读取出来的数据结构类，

　　用List集合，最后释放资源。

  1 package repository;
  2 
  3 import java.sql.ResultSet;
  4 import java.sql.SQLException;
  5 import java.util.ArrayList;
  6 import java.util.List;
  7 
  8 import com.mysql.jdbc.Connection;
  9 import com.mysql.jdbc.PreparedStatement;
 10 
 11 import Util.JBDCTools;
 12 import enity.Student;
 13 
 14 public class StudentRepository {
 15     public StudentRepository() {};
 16     public List<Student> findAll(){
 17         //初始化null值的三个常用变量
 18         Connection connection = null;
 19         ResultSet resultSet=null;
 20         PreparedStatement preparedStatement=null;
 21         //初始化完毕，定义一个List集合存放学生类数据结构
 22         
 23         List<Student> list=new ArrayList<>();
 24         
 25         try {
 26             //获取连接
 27             connection=JBDCTools.getConnection();
 28             //SQL语句
 29             String sql="select * from student;";
 30             //定义工作表空间
 31             
 32             preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
 33             //执行SQL语句
 34             
 35             resultSet = preparedStatement.executeQuery();
 36             //初始化学生类
 37             
 38             Student student=null;
 39             //遍历结果集
 40             while(resultSet.next()){
 41                 Integer id=resultSet.getInt(1);
 42                 String name=resultSet.getString(2);
 43                 String score = resultSet.getString(3);
 44                 java.sql.Date date = resultSet.getDate(4);
 45                 student = new Student (id,name,score,(java.sql.Date) date);
 46                 list.add(student);
 47             }
 48         } catch (SQLException e) {
 49             // TODO 自动生成的 catch 块
 50             e.printStackTrace();
 51         }finally {
 52             //释放资源
 53             JBDCTools.release(connection,preparedStatement,resultSet);
 54         }
 55         return list;
 56     }
 57     //添加
 58     
 59     public List<Student> add(String studentname,String score) {
 60         Connection connection = null;
 61         PreparedStatement preparedStatement=null;
 62         List<Student> list=new ArrayList<>();
 63         try {
 64             connection=JBDCTools.getConnection();
 65             String SQL="INSERT INTO student(studentname,score,birthday) VALUE(?,?,?);";
 66             //
 67             preparedStatement = (PreparedStatement) connection.prepareStatement(SQL);
 68             connection.setAutoCommit(false);
 69             //
 70             preparedStatement.setString(1, studentname);
 71             preparedStatement.setString(2, score);
 72             preparedStatement.setDate(3, new java.sql.Date(5));
 73             int flag=preparedStatement.executeUpdate();
 74             //
 75             connection.commit();
 76             //
 77             if(flag>0) {
 78                 System.out.println("成功添加了"+flag+"条数据！");
 79             }
 80             else {
 81                 System.out.println("添加失败！");
 82             }
 83         } catch (SQLException e) {
 84             // TODO 自动生成的 catch 块
 85             e.printStackTrace();
 86         }finally {
 87             JBDCTools.release(connection,preparedStatement);
 88         }
 89         //返回List集合
 90         return list;
 91     }
 92     //根据ID删除数据
 93     
 94     public static void deleteByid(Integer id) {
 95         Connection connection=null;
 96         java.sql.PreparedStatement preparedStatement = null;
 97         connection = JBDCTools.getConnection();
 98         String sql = "DELETE FROM student where id = ?";
 99         try {
100             preparedStatement = connection.prepareStatement(sql);
101             preparedStatement.setInt(1, id);
102             preparedStatement.executeUpdate();
103         } catch (SQLException e) {
104             // TODO 自动生成的 catch 块
105             e.printStackTrace();
106         }
107         finally {
108             JBDCTools.release(connection,preparedStatement);
109         }
110     }
111     
112     
113     //根据学号查找
114     public Student findById(Integer id) {
115         Connection connection=null;
116         PreparedStatement preparedStatement=null;
117         ResultSet resultSet=null;
118         Student student=null;
119         
120         connection=JBDCTools.getConnection();
121         String sql="SELECT * FROM student where id = ?";
122         try {
123             preparedStatement=(PreparedStatement) connection.prepareStatement(sql);
124             preparedStatement.setInt(1,id);
125             resultSet = preparedStatement.executeQuery();
126             while(resultSet.next())
127             {
128                 Integer id1=resultSet.getInt(1);
129                 String name=resultSet.getString(2);
130                 String score = resultSet.getString(3);
131                 java.sql.Date date = resultSet.getDate(4);
132                 student = new Student (id1,name,score,(java.sql.Date) date);
133             }
134         } catch (SQLException e) {
135             // TODO 自动生成的 catch 块
136             e.printStackTrace();
137         }finally {
138             JBDCTools.release(connection,preparedStatement,resultSet);
139         }
140         return student;
141     }
142     
143     
144     
145     
146     //修改
147     public void update (Integer id,String studentname,String score) {
148         Connection connection = null;
149         PreparedStatement preparedStatement=null;
150         try {
151             connection=JBDCTools.getConnection();
152             String SQL="UPDATE student set studentname=?,score=?  where id=?";
153             preparedStatement = (PreparedStatement) connection.prepareStatement(SQL);
154             connection.setAutoCommit(false);
155             preparedStatement.setString(1, studentname);
156             preparedStatement.setString(2, score);
157             preparedStatement.setInt(3, id);
158             int flag=preparedStatement.executeUpdate();
159             connection.commit();
160             if(flag>0) {
161                 System.out.println("成功修改了"+flag+"条数据！");
162             }
163             else {
164                 System.out.println("修改失败！");
165             }
166         } catch (SQLException e) {
167             // TODO 自动生成的 catch 块
168             e.printStackTrace();
169         }finally {
170             JBDCTools.release(connection,preparedStatement);
171         }
172     }
173     
174     
175 }

四、servlet层

功能：返回数据+视图到用户界面，和用户直接交互！

注意事项：

1、表单的数据是post或者是get

　　href是get，对应doGet方法

2、doPost方法：

　　首先要处理中文乱码问题：

1 request.setCharacterEncoding("utf-8");
2     response.setCharacterEncoding("utf-8");
3         response.setContentType("application/json;charset=utf-8");

交互过程：

①获取前台jsp文件的数据：用request.getParameter()方法

　　调用数据处理层 studentRepository 类的相应方法！

②使用response.sendRedirect()方法重定向（一般是返回本页面servlet）

3、doGet方法：

功能：主要在处理前台 hre f链接请求。

注意事项：

①首先获取前台数据

②其次获取后台数据（也就是List集合中的数据，调用studentRepository类方法返回List对象值并保存！）

　　request转发！

　　也就是把数据一并转发到前台界面

1 List<Student> list= studentRepository.findAll();
2             request.setAttribute("list", list);
3             request.getRequestDispatcher("index.jsp").forward(request,response);

下面给出servlet层的完整代码：

 1 package com.southwind.servlet;
 2 
 3 import java.io.IOException;
 4 import java.util.List;
 5 
 6 import javax.servlet.ServletException;
 7 import javax.servlet.annotation.WebServlet;
 8 import javax.servlet.http.HttpServlet;
 9 import javax.servlet.http.HttpServletRequest;
10 import javax.servlet.http.HttpServletResponse;
11 
12 import enity.Student;
13 import repository.StudentRepository;
14 
15 /**
16  * Servlet implementation class studentservlet
17  */
18 @WebServlet("/studentservlet")
19 public class studentservlet extends HttpServlet {
20     private StudentRepository studentRepository=new StudentRepository();
21     private static final long serialVersionUID = 1L;
22        
23     /**
24      * @see HttpServlet#HttpServlet()
25      */
26     public studentservlet() {
27         super();
28         // TODO Auto-generated constructor stub
29     }
30 
31     /**
32      * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
33      */
34     protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
35         // TODO Auto-generated method stub
36         String method = request.getParameter("method");
37         if(method==null)
38         {
39             method="findAll";
40         }
41         switch(method) {
42         case "findAll":
43             List<Student> list= studentRepository.findAll();
44             request.setAttribute("list", list);
45             request.getRequestDispatcher("index.jsp").forward(request,response);
46             break;
47         case "delete":
48             String idStr= request.getParameter("id");
49             Integer id= Integer.parseInt(idStr);
50             StudentRepository.deleteByid(id);
51             list= studentRepository.findAll();
52             request.setAttribute("list", list);
53             request.getRequestDispatcher("index.jsp").forward(request,response);
54             break;
55         case "findById":
56             idStr=request.getParameter("id");
57             id=Integer.parseInt(idStr);
58             request.setAttribute("student",studentRepository.findById(id));
59             request.getRequestDispatcher("update.jsp").forward(request, response);
60             break;
61         }
62         //返回视图+数据
63     }
64     /**
65      * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
66      */
67     protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
68         // TODO Auto-generated method stub
69         //处理编码问题
70         request.setCharacterEncoding("utf-8");
71         response.setCharacterEncoding("utf-8");
72         response.setContentType("application/json;charset=utf-8");
73         //处理完毕
74         String method = request.getParameter("method");
75         switch (method) {
76         case "add":
77             String studentname=request.getParameter("studentname");
78             String score=request.getParameter("score");
79             response.getWriter().write(studentname+score);
80             studentRepository.add(studentname,score);
81             break;
82         case "update":
83             String idStr= request.getParameter("id");
84             Integer id= Integer.parseInt(idStr);
85             studentname=request.getParameter("studentname");
86             score=request.getParameter("score");
87             studentRepository.update(id, studentname, score);
88             break;
89         }
90         response.sendRedirect("http://localhost:8080/bilibili/studentservlet");
91     }
92 }

五、jsp层（画前台界面，直接接收用户的数据）

注意事项：

1、表单和servlet的关联

　　如果有不需要让用户选择的字段，可以使用隐藏的input标签：

 1 <%@ page language="java" contentType="text/html; charset=UTF-8"
 2     pageEncoding="UTF-8"%>
 3     
 4     <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
 5 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 6 <html>
 7 <head>
 8 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 9 <title>Insert title here</title>
10 </head>
11 <body>
12     <table>
13         <tr>
14             <th>编号</th>
15             <th>姓名</th>
16             <th>成绩</th>
17             <th>注册日期</th>
18         </tr>
19         <tr>
20                 <a href="add.jsp">添加</a>
21         </tr>        
22         <c:forEach items="${list}" var="student">
23             <tr>
24                 <td>${student.id}</td>
25                 <td>${student.studentname}</td>
26                 <td>${student.score}</td>
27                 <td>${student.birthday}</td>
28                 <td>
29                     <a href="${pageContext.request.contextPath}/studentservlet?method=delete&id=${student.id}">删除</a>
30                     <a href="${pageContext.request.contextPath}/studentservlet?method=findById&id=${student.id}">修改</a>
31                 </td>
32             </tr>
33         </c:forEach>
34     </table>
35 </body>
36 </html>

add.jsp

 1 <%@ page language="java" contentType="text/html; charset=UTF-8"
 2     pageEncoding="UTF-8"%>
 3 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 4 <html>
 5 <head>
 6 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 7 <title>插入</title>
 8 </head>
 9 <body>
10     <form action="${pageContext.request.contextPath}/studentservlet" method="post">
11         姓名：<input type="text" name="studentname"/><br/>
12         成绩：<input type="text"    name="score"/><br/>
13         <input type="hidden" name="method" value="add">
14         <input type="submit" value="提交"/>
15     </form>
16 </body>
17 </html>

update.jsp

 1 <%@ page language="java" contentType="text/html; charset=UTF-8"
 2     pageEncoding="UTF-8"%>
 3 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
 4 <html>
 5 <head>
 6 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
 7 <title>修改</title>
 8 </head>
 9 <body>
10     <form action="${pageContext.request.contextPath}/studentservlet" method="post">
11         编号：<input type="text" name="id" value="${student.id }" readonly/><br>
12         姓名：<input type="text" name="studentname" value="${student.studentname }"/><br/>
13         成绩：<input type="text"    name="score"value="${student.score }"/><br/>
14         <input type="hidden" name="method" value="update">
15         <input type="submit" value="修改"/>
16     </form>
17 </body>
18 </html>