JDBC+Servlet+JSP 综合案例
1.搭建JDBC环境，连接数据库(测试，可以删除)
package com.myl.util;
import java.sql.*;
/**
* @ClassName: JDBCTools
* @Description: TODO
* @author: meyolo
* @date: 2020/10/4 10:51
*
* JDBC工具类 封装一些重复代码
*/
public class JDBCTools {
//这里将属性改为 static ，在调用时候直接通过类名调用，不用创建对象
private static Connection connection;
private static String url = "jdbc:mysql://localhost:3306/books?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
private static String user = "root";
private static String password = "root";
//静态代码块
static{
//将Driver加载到内存 只需要执行一次
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
connection = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public static void release(Connection connection, Statement statement, ResultSet resultSet){
try {
if(connection!=null){
connection.close();
}
if(statement!=null){
statement.close();
}
if(resultSet!=null){
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
2.实体类 包（entity）编写，对应数据库的字段
package com.myl.entity;
/**
* @ClassName: Student
* @Description: TODO
* @author: meyolo
* @date: 2020/10/3 21:11
*
* 实体类 学生
*/
public class Student {
private String Sno;
private String Sname;
private String Sscore;
public String getSno() {
return Sno;
}
public void setSno(String sno) {
Sno = sno;
}
public String getSname() {
return Sname;
}
public void setSname(String sname) {
Sname = sname;
}
public String getSscore() {
return Sscore;
}
public void setSscore(String sscore) {
Sscore = sscore;
}
public Student(String sno, String sname, String sscore) {
Sno = sno;
Sname = sname;
Sscore = sscore;
}
@Override
public String toString() {
return "Student{" +
"Sno='" + Sno + '\'' +
", Sname='" + Sname + '\'' +
", Sscore='" + Sscore + '\'' +
'}';
}
}
3.数据持久层 包（repository），负责与JDBC交互，实现增删改查功能。
package com.myl.repository;
import com.myl.entity.Student;
import com.myl.util.JDBCTools;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* @ClassName: StudentRepository
* @Description: TODO
* @author: meyolo
* @date: 2020/10/3 21:13
* <p>
* 数据持久层 负责与JDBC交互的代码
* 实现增删改查功能
*/
public class StudentRepository {
//查询方法
public List<Student> findAll() {
//需要返回多个对象（即一个学生一个对象），用ArrayList接收
List<Student> list = new ArrayList<>();
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCTools.getConnection();
//查询所有的信息
String sql = "select * from student";
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
//将student放在外部，则可以减少内存消耗，一个对象去赋值。
Student student = null;
while (resultSet.next()) {
//根据下表查询字段信息（以 1 开始）
String ssno = resultSet.getString(1);
String ssname = resultSet.getString(2);
String score = resultSet.getString(3);
//封装成一个Student对象
student = new Student(ssno, ssname, score);
//每次添加一个对象到arraylist中
list.add(student);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//用完之后关闭，释放资源
JDBCTools.release(connection, preparedStatement, resultSet);
}
return list;
}
//增加方法
public void add(String sno, String sname, String sscore) {
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCTools.getConnection();
//查询所有的信息
String sql = "insert into student(sno,sname,sscore) values (?,?,?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, sno);
preparedStatement.setString(2, sname);
preparedStatement.setString(3, sscore);
//返回值为int类型
preparedStatement.executeLargeUpdate();
//将student放在外部，则可以减少内存消耗，一个对象去赋值。
} catch (SQLException e) {
e.printStackTrace();
} finally {
//用完之后关闭，释放资源
JDBCTools.release(connection, preparedStatement, null);
}
}
//删除方法
public void deleteById(String id){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCTools.getConnection();
String sql ="delete from student where sno = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,id);
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCTools.release(connection,preparedStatement,null);
}
}
public Student findById(String sno){
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
Student student = null;
try {
connection = JDBCTools.getConnection();
String sql = "select * from student where sno = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1,sno);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()){
String ssno = resultSet.getString(1);
String ssname = resultSet.getString(2);
String score = resultSet.getString(3);
student = new Student(ssno, ssname, score);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCTools.release(connection,preparedStatement,resultSet);
}
return student;
}
public void update(String sno,String sname,String sscore){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = JDBCTools.getConnection();
String sql = "update student set sname=?,sscore=? where sno = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, sname);
preparedStatement.setString(2, sscore);
preparedStatement.setString(3, sno);
preparedStatement.executeLargeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCTools.release(connection, preparedStatement, null);
}
}
}
4.服务层 包（servlet），和浏览器 交互， 调用增删改查方法。
package com.myl.servlet;
import com.myl.entity.Student;
import com.myl.repository.StudentRepository;
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 java.io.IOException;
import java.util.List;
/**
* @ClassName: StudentServlet
* @Description: TODO
* @author: meyolo
* @date: 2020/10/3 21:15
*
* 和浏览器 交互 调用增删改查
*/
@WebServlet("/student") //请求studnet
public class StudentServlet extends HttpServlet {
private StudentRepository studentRepository = new StudentRepository();
@Override
//这里执行localhost:8080/student 是一个get请求 调用如下方法
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String method = req.getParameter("method");
if (method == null) {
method = "findAll";
}
switch (method) {
case "findAll":
//返回视图和数据
List<Student> list = studentRepository.findAll();
//将数据传送到 list中
//在本类中调用了setattribute（）方法然后才去跳转到index.jsp
req.setAttribute("list", list);
//返回到页面 index.jsp中 视图层
req.getRequestDispatcher("index.jsp").forward(req, resp);
break;
case "deleteById":
String did = req.getParameter("id");
studentRepository.deleteById(did);
resp.sendRedirect("/student");
break;
case "findById":
String fid = req.getParameter("id");
req.setAttribute("student", studentRepository.findById(fid));
req.getRequestDispatcher("update.jsp").forward(req, resp);
break;
}
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//req.setCharacterEncoding("UTF-8");
String method = req.getParameter("method");
switch (method) {
case "add":
String sno = req.getParameter("sno");
String sname = req.getParameter("sname");
String sscore = req.getParameter("sscore");
studentRepository.add(sno, sname, sscore);
break;
case "update":
String uso = req.getParameter("sno");
String uname = req.getParameter("sname");
String uscore = req.getParameter("sscore");
studentRepository.update(uso, uname, uscore);
break;
}
//重定向，相当于个给浏览器重新发送一个请求,刷新
//在这里就等于 重新执行一次 get请求
//无论执行哪个case 都要执行
resp.sendRedirect("/student");
}
}
4.工具层 包（util），JDBC连接的重复代码封装。
package com.myl.util;
import java.sql.*;
/**
* @ClassName: JDBCTools
* @Description: TODO
* @author: meyolo
* @date: 2020/10/4 10:51
*
* JDBC工具类 封装一些重复代码
*/
public class JDBCTools {
//这里将属性改为 static ，在调用时候直接通过类名调用，不用创建对象
private static Connection connection;
private static String url = "jdbc:mysql://localhost:3306/books?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC";
private static String user = "root";
private static String password = "root";
//静态代码块
static{
//将Driver加载到内存 只需要执行一次
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection(){
try {
connection = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
}
return connection;
}
public static void release(Connection connection, Statement statement, ResultSet resultSet){
try {
if(connection!=null){
connection.close();
}
if(statement!=null){
statement.close();
}
if(resultSet!=null){
resultSet.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
5.过滤器 包（filter），解决数据库添加数据特殊字符乱码问题（中文）。
package com.myl.filter;
import javax.servlet.*;
import javax.servlet.annotation.WebFilter;
import java.io.IOException;
/**
* @ClassName: CharacterFilter
* @Description: TODO
* @author: meyolo
* @date: 2020/10/4 10:17
*
* 过滤器 解决添加数据乱码问题(如果多个Servlet ，
* 可设置filter 单个不必要，直接在servlet设置编码
* req.setCharacterEncoding("UTF-8");
*/
@WebFilter("/student") //请求student 先进入到这里解决中文乱码问题
public class CharacterFilter implements Filter {
@Override
public void init(FilterConfig filterConfig) throws ServletException {
}
@Override
public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
servletRequest.setCharacterEncoding("UTF-8");
filterChain.doFilter(servletRequest,servletResponse);
}
@Override
public void destroy() {
}
}
