jsp+servlet+jdbc整合

项目基本流程
1、原型设计 画草图
授权码：https://blog.csdn.net/weixin_43697849/article/details/116159015
2、数据库设计
（1）设计数据库模型

（2）建表

3、软件设计
预留知识？
4、搭建框架
（1）创建项目

（2）项目结构介绍

（3）确定我们的 项目是否正确

（4）完整的项目结构

5、撸代码
（1） 编写我的util

druid.driverClassName=com.mysql.jdbc.Driver
druid.url=jdbc:mysql://localhost:3307/db_admin?useUnicode=true&characterEncpding=utf8
druid.username=root
druid.password=123456
druid.maxActive=8
druid.maxWait=5000

（2）封装我们jdbc

package com.qf.util;

import com.alibaba.druid.pool.DruidDataSource;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

public class JDBCUtil {

    private static Connection connection = null;
    private static ResultSet resultSet = null;
    private static  PreparedStatement preparedStatement = null;
    private static Properties properties = null;
    //jdbc 数据库操作对象
    private static DataSource dataSource = null;

    static{
        properties = new Properties();
        String path = JDBCUtil.class.getResource(".").getPath();
        path += "jdbc.properties";
        try {
            InputStream inputStream = new FileInputStream(path);
            properties.load(inputStream);
            DruidDataSource druidDataSource = new DruidDataSource();
            druidDataSource.configFromPropety(properties);
            dataSource = druidDataSource;
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     * 获取我们conn对象
     * @return
     */
    private static Connection getConn(){
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(true);
        } catch (Exception e) {
            e.printStackTrace();
        }
       return connection;
    }

    /**
     * 通用查询方法
     * @param sql mysql的查询语句
     * @param parmas mysql查询语句的参数
     * @return 结果集对象
     */
    public static Object getQuery(String sql,Class cls ,Object ... parmas){
        Connection conn = getConn();
        Object object = null;
        try {
             preparedStatement = conn.prepareStatement(sql);
             if(null != parmas){
                 int key = 1;
                 for(Object obj : parmas){
                     preparedStatement.setObject(key,obj);
                     key++;
                 }
             }
             resultSet = preparedStatement.executeQuery();

            object = getObj(resultSet,cls);
        } catch (Exception throwables) {
            throwables.printStackTrace();
        }finally {
            close(conn,preparedStatement,resultSet);
        }
        return object;
    }

    /**
     * 作为 ORM 映射 object 关系  mapping
     * @param resultSet
     * @param cls
     * @return
     * @throws Exception
     */
    private static Object getObj(ResultSet resultSet,Class<?> cls) throws Exception {
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        List<Object> list = new ArrayList<>();
        while (resultSet.next()){
            Object o = cls.newInstance();
            Field[] declaredFields = cls.getDeclaredFields();
            for(int i = 0;i < declaredFields.length;i++){
                for(int j = 0;j < columnCount; j++){
                   // String columnName = metaData.getColumnName(j + 1);
                    String columnName = metaData.getColumnLabel(j + 1);
                    if(columnName.equals(declaredFields[i].getName())){
                        declaredFields[i].setAccessible(true);

                        declaredFields[i].set(o,resultSet.getObject(columnName));
                    }
                }
            }
            list.add(o);
        }
        return list;
    }

    /**
     * 通用增删改
     * @param sql mysql的查询语句
     * @param parmas mysql参数列表
     * @return 返回int类型  成功为1 失败为0
     */
    public static Integer adit(String sql,Object ... parmas){
        Connection conn = getConn();
        int res = 0;
        try {
            preparedStatement = conn.prepareStatement(sql);
            int key = 1;
            for(Object obj : parmas){
                preparedStatement.setObject(key,obj);
                key++;
            }
            res = preparedStatement.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            close(connection,preparedStatement,resultSet);
        }

        return res;
    }

    private static void close(Connection conn,PreparedStatement ps,ResultSet rs){
        if(null != rs){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(null != ps){
            try {
                ps.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

        if(null != conn){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }


}

（3） 编写我的dao层

package com.qf.dao;

import com.qf.entity.UseEntity;

public interface IUserDao {
    /**
     * 登录接口
     * @param username 用户名
     * @param pwd 密码
     * @return 返回一个userentity实体
     * @throws Exception 统一异常处理
     */
    public UseEntity login(String username,String pwd) throws Exception;

    /**
     * 注册接口
     * @param useEntity 需要传入一个实体对象
     * @return
     * @throws Exception
     */
    public Integer insert(UseEntity useEntity) throws Exception;

}

package com.qf.dao.impl;

import com.qf.dao.IUserDao;
import com.qf.entity.UseEntity;
import com.qf.util.JDBCUtil;
import java.util.List;

public class UserDaoImpl implements IUserDao {
    @Override
    public UseEntity login(String username, String pwd) throws Exception {
        String sql = "select * from t_user where username=? and pwd=?";
        UseEntity useEntity = null;
        List<UseEntity> list = (List<UseEntity>)JDBCUtil.getQuery(sql, UseEntity.class, username, pwd);
        if(list != null && list.size() > 0){
            useEntity = list.get(0);
        }

        return useEntity;
    }

    @Override
    public Integer insert(UseEntity useEntity) throws Exception {
        String sql = "insert into t_user (username,pwd,sex,city) values(?,?,?,?)";
        Integer adit = JDBCUtil.adit(sql, useEntity.getUsername(),
                useEntity.getPwd(), useEntity.getSex(), useEntity.getCity());
        return adit;
    }
}

（4）编写我们的service层

package com.qf.service;

import com.qf.entity.UseEntity;

public interface IUserService {

    /**
     * 登录接口
     * @param username 用户名
     * @param pwd 密码
     * @return 返回一个userentity实体
     * @throws Exception 统一异常处理
     */
    public UseEntity login(String username, String pwd) throws Exception;

    /**
     * 注册接口
     * @param useEntity 需要传入一个实体对象
     * @return
     * @throws Exception
     */
    public Integer insert(UseEntity useEntity) throws Exception;
}

package com.qf.service.impl;

import com.qf.dao.IUserDao;
import com.qf.dao.impl.UserDaoImpl;
import com.qf.entity.UseEntity;
import com.qf.service.IUserService;

public class UserServiceImpl implements IUserService {
    private IUserDao userDao = new UserDaoImpl();

    @Override
    public UseEntity login(String username, String pwd) throws Exception {
        UseEntity useEntity = null;
        if(null != username && !"".equals(username)
                && null != pwd && !"".equals(pwd)){
            useEntity = userDao.login(username, pwd);
        }
        return useEntity;
    }

    @Override
    public Integer insert(UseEntity useEntity) throws Exception {
        Integer insert = 0;
        if(null != useEntity){
            if(null != useEntity.getUsername() && !"".equals(useEntity.getUsername())
                    && null != useEntity.getPwd() && !"".equals(useEntity.getPwd())){
                 insert = userDao.insert(useEntity);
            }
        }
        return insert;
    }
}

（5）编写我们的页面

<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html>
<head>
    <title>登录</title>
    <script src="https://code.jquery.com/jquery-3.6.0.js"></script>
</head>
<style>
    #login{
        margin: auto;
    }
</style>
<body>
    <div id="login">
        <h3>登录页面</h3>
        <form action="http://localhost:8039/LoginServlet" method="post">
          <table>
              <tr>
                  <td>账户</td>
                  <td colspan="1"><input name="username" type="text"></td>
              </tr>
              <tr>
                  <td>密码</td>
                  <td colspan="1"><input name="pwd" type="password"></td>
              </tr>
              <tr>
                  <td>验证码</td>
                  <td><input name="code" type="text"></td>
                  <td><span id="code">123456</span><span onclick="codeAjax()">刷新验证码</span></td>
              </tr>
              <tr>
                  <td colspan="2"><input type="submit" value="登录"><a href="http://localhost:8039/res.jsp">注册</a></td>
              </tr>
          </table>
        </form>
    </div>
</body>
</html>

<script>
    codeAjax();
    function codeAjax(){
        $.ajax({
            type:"get",
            url:"http://localhost:8039/CodeServlet",
            success:function (res){
                $("#code").text(res)
            }
        })
    }

</script>

<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html>
<head>
  <title>登录</title>

</head>
<style>
  #login{
    margin: auto;
  }
</style>
<body>
<div id="res">
  <h3>注册页面</h3>
  <form action="" method="post">
    <table>
      <tr>
        <td>账户</td>
        <td colspan="1"><input name="username" type="text"></td>
      </tr>
      <tr>
        <td>密码</td>
        <td colspan="1"><input name="pwd" type="password"></td>
      </tr>
      <tr>
        <td>确认密码</td>
        <td colspan="1"><input name="pwd2" type="password"></td>
      </tr>
      <tr>
        <td>性别</td>
        <td><input name="sex" type="radio" value="man" checked="checked"/> 男</td>
        <td><input name="sex" type="radio" value="female"/> 女</td>
      </tr>
      <tr>
        <td>
          城市
        </td>
        <td colspan="1">
          <select name="city">
            <option value="bj">北京</option>
            <option value="sh">上海</option>
            <option value="cq" selected="selected">重庆</option>
            <option value="cd">成都</option>
          </select>
        </td>
      </tr>

      <tr>
        <td>
          <input type="reset" value="重置"/>
        </td>
        <td colspan="1">
          <input type="submit" value="注册"/>
        </td>
      </tr>
    </table>
  </form>
</div>

</body>
</html>

编写验证码

package com.qf.controller;

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 javax.servlet.http.HttpSession;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.Random;

@WebServlet("/CodeServlet")
public class CodeServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        Random random = new Random();
        int i = random.nextInt();
        int j = random.nextInt();
        int k = random.nextInt();
        int m = random.nextInt();
        PrintWriter writer = resp.getWriter();
        String code = i+""+j+""+k+""+m;
        HttpSession session = req.getSession();
        session.setAttribute("code",code);
        writer.println(code);
        writer.close();
        writer.flush();
    }
}

package com.qf.controller;

import com.qf.entity.UseEntity;
import com.qf.service.IUserService;
import com.qf.service.impl.UserServiceImpl;

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;
@WebServlet("/LoginServlet")
public class LoginServlet extends HttpServlet {

    private IUserService userService = new UserServiceImpl();

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        String username = request.getParameter("username");
        String pwd = request.getParameter("pwd");
        String code = request.getParameter("code");

        if(null != code && !"".equals(code)
                && code.equals(request.getSession().getAttribute("code"))){

            UseEntity login = null;
            try {
                login = userService.login(username, pwd);
            } catch (Exception e) {
                e.printStackTrace();
            }
            if(null != login){
                request.getRequestDispatcher("home.jsp").forward(request,response);
            }else{
                request.getRequestDispatcher("index.jsp").forward(request,response);
            }

        }else{
            request.getRequestDispatcher("index.jsp").forward(request,response);
        }
    }
}

注册

<%@ page contentType="text/html; charset=UTF-8" pageEncoding="UTF-8" %>
<!DOCTYPE html>
<html>
<head>
  <title>登录</title>

</head>
<style>
  #login{
    margin: auto;
  }
</style>
<body>
<div id="res">
  <h3>注册页面</h3>
  ${msg}
  <form action="http://localhost:8039/ResServlet" method="post">
    <table>
      <tr>
        <td>账户</td>
        <td colspan="1"><input name="username" type="text"></td>
      </tr>
      <tr>
        <td>密码</td>
        <td colspan="1"><input name="pwd" type="password"></td>
      </tr>
      <tr>
        <td>确认密码</td>
        <td colspan="1"><input name="pwd2" type="password"></td>
      </tr>
      <tr>
        <td>性别</td>
        <td><input name="sex" type="radio" value="man" checked="checked"/> 男</td>
        <td><input name="sex" type="radio" value="female"/> 女</td>
      </tr>
      <tr>
        <td>
          城市
        </td>
        <td colspan="1">
          <select name="city">
            <option value="bj">北京</option>
            <option value="sh">上海</option>
            <option value="cq" selected="selected">重庆</option>
            <option value="cd">成都</option>
          </select>
        </td>
      </tr>

      <tr>
        <td>
          <input type="reset" value="重置"/>
        </td>
        <td colspan="1">
          <input type="submit" value="注册"/>
        </td>
      </tr>
    </table>
  </form>
</div>

</body>
</html>

package com.qf.controller;

import com.qf.entity.UseEntity;
import com.qf.service.IUserService;
import com.qf.service.impl.UserServiceImpl;
import com.qf.util.Md5Util;
import sun.security.provider.MD5;

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.nio.charset.StandardCharsets;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;

@WebServlet("/ResServlet")
public class ResServlet extends HttpServlet {
    private IUserService userService = new UserServiceImpl();
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String username = req.getParameter("username");
        String pwd = req.getParameter("pwd");
        String pwd2 = req.getParameter("pwd2");
        String sex = req.getParameter("sex");
        String city = req.getParameter("city");

        String msg = "新增失败";

        if(pwd.equals(pwd2)){
            UseEntity useEntity = new UseEntity();
            useEntity.setUsername(username);
            useEntity.setPwd(Md5Util.add(pwd));
            useEntity.setSex(sex);
            useEntity.setCity(city);

            try {
                Integer insert = userService.insert(useEntity);
                if(insert > 0){
                    msg = "添加成功！";
                    req.setAttribute("msg",msg);
                    req.getRequestDispatcher("index.jsp").forward(req,resp);
                }else{
                    req.setAttribute("msg",msg);
                    req.getRequestDispatcher("res.jsp").forward(req,resp);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }else{
            msg = "两次输入密码不一致！";
            req.setAttribute("msg",msg);
            req.getRequestDispatcher("res.jsp").forward(req,resp);
        }


    }
}

分页封装

package com.qf.util;

import javax.swing.*;
import java.io.Serializable;
import java.util.List;

/**
 * 分页工具
 * @param <T>
 */
public class PageUtil<T> implements Serializable {
    /********总数***********/
    private Integer total;
    /********总页数***********/
    private Integer totalPage;
    /*****每页显示的条数******/
    private Integer pageSize = 10;
    /********页数***********/
    private Integer pageNum = 1;
    /********上一页***********/
    private Integer upPage;
    /********下一页***********/
    private Integer downPage;
    /********页数集合********/
    private Integer[] pageNums;
    /********查询的结果集*****/
    private List<T> list;
    /********limt 偏移量****/
    private Integer current;

    public PageUtil(List<T> list, Integer total, Integer pageNum,Integer pageSize){
        this.list = list;
        this.total = total;
        if(pageNum > 1){
            this.pageNum = pageNum;
        }
        if(pageSize >= 1){
            this.pageSize = pageSize;
        }

        this.totalPage = this.total%this.pageSize == 0 ? this.total/this.pageSize : this.total/this.pageSize+1;
        this.upPage = this.pageNum == 1 ? this.pageNum : pageNum-1;
        this.downPage = this.pageNum == this.totalPage ? this.totalPage : this.pageNum+1;

        Integer[] pageNums = new Integer[this.totalPage];
        for(int i = 0; i< this.totalPage; i++){
            pageNums[i] = i+1;
        }
        this.pageNums = pageNums;
        this.current = (this.pageNum-1)*this.pageSize;
    }

    public Integer getTotal() {
        return total;
    }

    public Integer getTotalPage() {
        return totalPage;
    }

    public Integer getPageSize() {
        return pageSize;
    }

    public Integer getPageNum() {
        return pageNum;
    }

    public Integer getUpPage() {
        return upPage;
    }

    public Integer getDownPage() {
        return downPage;
    }

    public Integer[] getPageNums() {
        return pageNums;
    }

    public List<T> getList() {
        return list;
    }

    public Integer getCurrent() {
        return current;
    }

    public void setList(List<T> list) {
        this.list = list;
    }
}

分页使用

/**
     *
     * @param userEntity
     * @param pageNum 是多少页面 Limit ? ? 第一个 ？ 代表从第几条开始  ？取几条数据
     * @param pageSize
     * @return
     * @throws Exception
     */
    @Override
    public PageUtil  findAll(UserEntity userEntity, Integer pageNum, Integer pageSize) throws Exception {
        String sql = "select * from t_user where 1=1"; //不带条件
        List<Object> list = new ArrayList<>();
        if(null != userEntity){ // 带 where 条件
            if(null != userEntity.getUsername() && !("").equals(userEntity.getUsername())){
                sql += " and username like ?";
                list.add("%"+userEntity.getUsername()+"%");
            }
            if(null != userEntity.getStatus() && !("").equals(userEntity.getStatus())){
                sql += " and status = ?";
                list.add(userEntity.getStatus());
            }
        }

        System.out.println(sql);
        //求我們的数据总和
        List<UserEntity> all = userDao.findAll(sql, list);

        PageUtil pageUtil = new PageUtil(all,all.size(),pageNum,pageSize);

        //如果pageNum 和 pageSize 都是O 代表 我不分页
          if(pageNum > 0 && pageSize > 0){
              sql += " limit ? ?";
              list.add(pageUtil.getCurrent());
              list.add(pageUtil.getPageSize());
          }

        System.out.println(sql);
          //这条才是我真正需要分页的结果集
        List<UserEntity> pageList = userDao.findAll(sql, list);

        pageUtil.setList(pageList);

        return pageUtil;
    }

文件上传

<%--
  Created by IntelliJ IDEA.
  User: Zane
  Date: 2022/5/17
  Time: 19:47
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
</head>
<body>
    <form action="/FileServlet" method="post" enctype="multipart/form-data">
        <input type="file" name="file" />
        <input type="text" name="username"/>
        <input type="submit" value="上传"/>
    </form>
</body>
</html>

package com.qf.controller;

import javax.servlet.ServletException;
import javax.servlet.annotation.MultipartConfig;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.Part;
import java.io.IOException;
import java.io.PrintWriter;
@WebServlet("/FileServlet")
@MultipartConfig
public class FileServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        doPost(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        resp.setCharacterEncoding("utf-8");
        resp.setContentType("text/html;charset=utf-8");
        String username = req.getParameter("username");
        Part file = req.getPart("file");
        String filePath =  this.getClass().getResource("/").getPath().replaceFirst("/", "");
        filePath = filePath.replaceAll("WEB-INF/classes/", "");
        filePath = filePath+"upload/";

        String submittedFileName = file.getSubmittedFileName();

        file.write(filePath+submittedFileName);

        PrintWriter writer = resp.getWriter();
        writer.println("上传成功"+username);
        writer.close();
        writer.flush();
    }
}