Servlet+JSP+JDBC综合案例

 

 

层级关系：

一、Util包

包里面写一个JDBCTools.java文件

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

注意事项：

1、定义成员变量

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

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

static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
    }

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

public static Connection getConnection() {
        
        try {
            connection=(Connection) DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        
        return connection;
    }

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

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

package Util;

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

import com.mysql.jdbc.Connection;

public class JBDCTools {
    private static  Connection connection ;
    private static  String url="jdbc:mysql://localhost:3306/info?useUnicode=true&CharacterEncoding=utf-8";
    private static  String user="root";
    private static  String password="000429";
    static {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
    }
    public static Connection getConnection() {
        
        try {
            connection=(Connection) DriverManager.getConnection(url,user,password);
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        
        return connection;
    }
    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();
            }
        }
    }
}

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

package enity;

import java.sql.Date;

public class Student {
    private Integer id;
    private String studentname;
    private String score;
    private Date birthday;
    public Integer getId() {
        return id;
    }
    public Student(Integer id, String studentname, String score, Date birthday) {
        super();
        this.id = id;
        this.studentname = studentname;
        this.score = score;
        this.birthday = birthday;
    }
    
    @Override
    
    public String toString() {
        return "Student [id=" + id + ", studentname=" + studentname + ", score=" + score + ", birthday=" + birthday + "]";
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getstudentname() {
        return studentname;
    }
    public void setstudentname(String studentname) {
        this.studentname = studentname;
    }
    public String getScore() {
        return score;
    }
    public void setScore(String score) {
        this.score = score;
    }
    public Date getBirthday() {
        return birthday;
    }
    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }
    
}

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

注意事项：

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

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

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

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

　　写SQL语句，执行SQL语句，

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

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

package repository;

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

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

import Util.JBDCTools;
import enity.Student;

public class StudentRepository {
    public StudentRepository() {};
    public List<Student> findAll(){
        //初始化null值的三个常用变量
        Connection connection = null;
        ResultSet resultSet=null;
        PreparedStatement preparedStatement=null;
        //初始化完毕，定义一个List集合存放学生类数据结构
        
        List<Student> list=new ArrayList<>();
        
        try {
            //获取连接
            connection=JBDCTools.getConnection();
            //SQL语句
            String sql="select * from student;";
            //定义工作表空间
            
            preparedStatement = (PreparedStatement) connection.prepareStatement(sql);
            //执行SQL语句
            
            resultSet = preparedStatement.executeQuery();
            //初始化学生类
            
            Student student=null;
            //遍历结果集
            while(resultSet.next()){
                Integer id=resultSet.getInt(1);
                String name=resultSet.getString(2);
                String score = resultSet.getString(3);
                java.sql.Date date = resultSet.getDate(4);
                student = new Student (id,name,score,(java.sql.Date) date);
                list.add(student);
            }
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }finally {
            //释放资源
            JBDCTools.release(connection,preparedStatement,resultSet);
        }
        return list;
    }
    //添加
    
    public List<Student> add(String studentname,String score) {
        Connection connection = null;
        PreparedStatement preparedStatement=null;
        List<Student> list=new ArrayList<>();
        try {
            connection=JBDCTools.getConnection();
            String SQL="INSERT INTO student(studentname,score,birthday) VALUE(?,?,?);";
            //
            preparedStatement = (PreparedStatement) connection.prepareStatement(SQL);
            connection.setAutoCommit(false);
            //
            preparedStatement.setString(1, studentname);
            preparedStatement.setString(2, score);
            preparedStatement.setDate(3, new java.sql.Date(5));
            int flag=preparedStatement.executeUpdate();
            //
            connection.commit();
            //
            if(flag>0) {
                System.out.println("成功添加了"+flag+"条数据！");
            }
            else {
                System.out.println("添加失败！");
            }
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }finally {
            JBDCTools.release(connection,preparedStatement);
        }
        //返回List集合
        return list;
    }
    //根据ID删除数据
    
    public static void deleteByid(Integer id) {
        Connection connection=null;
        java.sql.PreparedStatement preparedStatement = null;
        connection = JBDCTools.getConnection();
        String sql = "DELETE FROM student where id = ?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }
        finally {
            JBDCTools.release(connection,preparedStatement);
        }
    }
    
    
    //根据学号查找
    public Student findById(Integer id) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        Student student=null;
        
        connection=JBDCTools.getConnection();
        String sql="SELECT * FROM student where id = ?";
        try {
            preparedStatement=(PreparedStatement) connection.prepareStatement(sql);
            preparedStatement.setInt(1,id);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next())
            {
                Integer id1=resultSet.getInt(1);
                String name=resultSet.getString(2);
                String score = resultSet.getString(3);
                java.sql.Date date = resultSet.getDate(4);
                student = new Student (id1,name,score,(java.sql.Date) date);
            }
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }finally {
            JBDCTools.release(connection,preparedStatement,resultSet);
        }
        return student;
    }
    
    
    
    
    //修改
    public void update (Integer id,String studentname,String score) {
        Connection connection = null;
        PreparedStatement preparedStatement=null;
        try {
            connection=JBDCTools.getConnection();
            String SQL="UPDATE student set studentname=?,score=?  where id=?";
            preparedStatement = (PreparedStatement) connection.prepareStatement(SQL);
            connection.setAutoCommit(false);
            preparedStatement.setString(1, studentname);
            preparedStatement.setString(2, score);
            preparedStatement.setInt(3, id);
            int flag=preparedStatement.executeUpdate();
            connection.commit();
            if(flag>0) {
                System.out.println("成功修改了"+flag+"条数据！");
            }
            else {
                System.out.println("修改失败！");
            }
        } catch (SQLException e) {
            // TODO 自动生成的 catch 块
            e.printStackTrace();
        }finally {
            JBDCTools.release(connection,preparedStatement);
        }
    }
    
    
}

四、servlet层

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

注意事项：

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

　　href是get，对应doGet方法

2、doPost方法：

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

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

交互过程：

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

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

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

3、doGet方法：

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

注意事项：

①首先获取前台数据

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

　　request转发！

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

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

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

package com.southwind.servlet;

import java.io.IOException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import enity.Student;
import repository.StudentRepository;

/**
 * Servlet implementation class studentservlet
 */
@WebServlet("/studentservlet")
public class studentservlet extends HttpServlet {
    private StudentRepository studentRepository=new StudentRepository();
    private static final long serialVersionUID = 1L;
       
    /**
     * @see HttpServlet#HttpServlet()
     */
    public studentservlet() {
        super();
        // TODO Auto-generated constructor stub
    }

    /**
     * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        String method = request.getParameter("method");
        if(method==null)
        {
            method="findAll";
        }
        switch(method) {
        case "findAll":
            List<Student> list= studentRepository.findAll();
            request.setAttribute("list", list);
            request.getRequestDispatcher("index.jsp").forward(request,response);
            break;
        case "delete":
            String idStr= request.getParameter("id");
            Integer id= Integer.parseInt(idStr);
            StudentRepository.deleteByid(id);
            list= studentRepository.findAll();
            request.setAttribute("list", list);
            request.getRequestDispatcher("index.jsp").forward(request,response);
            break;
        case "findById":
            idStr=request.getParameter("id");
            id=Integer.parseInt(idStr);
            request.setAttribute("student",studentRepository.findById(id));
            request.getRequestDispatcher("update.jsp").forward(request, response);
            break;
        }
        //返回视图+数据
    }
    /**
     * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
     */
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // TODO Auto-generated method stub
        //处理编码问题
        request.setCharacterEncoding("utf-8");
        response.setCharacterEncoding("utf-8");
        response.setContentType("application/json;charset=utf-8");
        //处理完毕
        String method = request.getParameter("method");
        switch (method) {
        case "add":
            String studentname=request.getParameter("studentname");
            String score=request.getParameter("score");
            response.getWriter().write(studentname+score);
            studentRepository.add(studentname,score);
            break;
        case "update":
            String idStr= request.getParameter("id");
            Integer id= Integer.parseInt(idStr);
            studentname=request.getParameter("studentname");
            score=request.getParameter("score");
            studentRepository.update(id, studentname, score);
            break;
        }
        response.sendRedirect("http://localhost:8080/bilibili/studentservlet");
    }
}

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

注意事项：

1、表单和servlet的关联

　　<form action="${pageContext.request.contextPath}/studentservlet" method="post">

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

　　<input type="hidden" name="method" value="add">

 

 

 

 

 

 

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
    
    <%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Insert title here</title>
</head>
<body>
    <table>
        <tr>
            <th>编号</th>
            <th>姓名</th>
            <th>成绩</th>
            <th>注册日期</th>
        </tr>
        <tr>
                <a href="add.jsp">添加</a>
        </tr>        
        <c:forEach items="${list}" var="student">
            <tr>
                <td>${student.id}</td>
                <td>${student.studentname}</td>
                <td>${student.score}</td>
                <td>${student.birthday}</td>
                <td>
                    <a href="${pageContext.request.contextPath}/studentservlet?method=delete&id=${student.id}">删除</a>
                    <a href="${pageContext.request.contextPath}/studentservlet?method=findById&id=${student.id}">修改</a>
                </td>
            </tr>
        </c:forEach>
    </table>
</body>
</html>

add.jsp

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

update.jsp

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