前言

本篇其实写的不够好,具体可以看我后来的改进:
JavaWeb servlet jsp 使用七牛云API上传图片
本篇是图片直接存到数据库里面,改进是用对象存储来存图片,然后把url存到数据库中,这样就速度会快很多

通过javaBean创建表结构,通过jsp界面录入数据,具备上传图片的功能,并提供一个列表界面,用分页及表格形式显示这些数据,在列表界面点击某条记录,跳到详细信息界面,能把上传的照片显示出来

效果:

首先,代码仅供参考,本人水平有限,没有用jsp写过太多代码,很多地方我自己都觉得很不好,好在功能算是勉强完成了,如果有想交流的可以在评论留言,欢迎交流

简单说,这次作业就是一个巨型缝合怪,东凑凑西凑凑,页面用了layui的表格,强行从后台传json到页面,以及layui的bug,分页操作,前端的数据无法传到后端,导致徒有其表无法分页…
(除了分页其他都做了,分页其实也是可以的,只要它能将limit和page两个参数放到url上就行,我在sql那里有注释的一个版本就是)

因为作业是放在一块的,很难弄…
里面存留了一些方法,可能是调试或者没有用的,但是我没删掉

如果有些不太懂的,可以看最底下的参考,我很多代码都是参考里面的

DBUtil

数据库工具类,里面只有两个有用的方法Connection和closeConn

package top.sehnsucht.Util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

/**
 * @Description:
 * @Author: Cai
 * @CreateTime: 2021/10/29
 */

public class DBUtil {
    // 定义数据库连接参数
    public static final String DRIVER_CLASS_NAME = "com.mysql.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost:3306/xxxx";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "xxxxxx";


    // 注册数据库驱动
    static {
        try {
            Class.forName(DRIVER_CLASS_NAME);
        } catch (ClassNotFoundException e) {
            System.out.println("注册失败！");
            e.printStackTrace();
        }
    }

    // 获取连接
    public static Connection getConn() throws SQLException {
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }

    // 关闭连接
    public static void closeConn(Connection conn) {
        if (null != conn) {
            try {
                conn.close();
            } catch (SQLException e) {
                System.out.println("关闭连接失败！");
                e.printStackTrace();
            }
        }
    }
    //测试
    public static void main(String[] args) throws SQLException {
        System.out.println(DBUtil.getConn());
    }
}

ImageUtil

特殊变量it,在ImageDBUtil.readImage2DB中数据库表进行id自增

里面2个方法,最主要的是readBin2Image,用来读取表中图片获取输出流,然后存入本地

package top.sehnsucht.Util;


import java.io.*;
import java.sql.Blob;

/**
 * @Description:
 * @Author: Cai
 * @CreateTime: 2021/10/29
 */

public class ImageUtil {
    public static Integer it = 1;
    /**
     * 读取本地图片获取输入流
     * @param path
     * @return
     * @throws IOException
     */
    public static FileInputStream readImage(String path) throws IOException {
        return new FileInputStream(new File(path));
    }

    /**
     * 读取表中图片直接存到本地
     * @param in
     * @param targetPath
     */
    public static void readBin2Image(Blob in, String targetPath) throws Exception{
        File file = new File(targetPath);
        OutputStream outputStream = new FileOutputStream(file);
        outputStream.write(in.getBytes(1,(int)in.length()));
    }
}

UrlUtil

url工具类,不过没有用上,因为参数根本没传过来…

package top.sehnsucht.Util;

import java.util.HashMap;
import java.util.Map;

/**
 * @Description:
 * @Author: Cai
 * @CreateTime: 2021/10/31
 */

public class UrlUtil {
    /**
     * 获取请求地址中的某个参数
     *
     * @param url
     * @param name
     * @return
     */
    public static String getParam(String url, String name) {
        return urlSplit(url).get(name);
    }

    /**
     * 去掉url中的路径，留下请求参数部分
     *
     * @param url url地址
     * @return url请求参数部分
     */
    private static String truncateUrlPage(String url) {
        String strAllParam = null;
        String[] arrSplit = null;
        url = url.trim().toLowerCase();
        arrSplit = url.split("[?]");
        if (url.length() > 1) {
            if (arrSplit.length > 1) {
                for (int i = 1; i < arrSplit.length; i++) {
                    strAllParam = arrSplit[i];
                }
            }
        }
        return strAllParam;
    }

    /**
     * 将参数存入map集合
     *
     * @param url url地址
     * @return url请求参数部分存入map集合
     */
    public static Map<String, String> urlSplit(String url) {
        Map<String, String> mapRequest = new HashMap<String, String>();
        String[] arrSplit = null;
        String strUrlParam = truncateUrlPage(url);
        if (strUrlParam == null) {
            return mapRequest;
        }
        arrSplit = strUrlParam.split("[&]");
        for (String strSplit : arrSplit) {
            String[] arrSplitEqual = null;
            arrSplitEqual = strSplit.split("[=]");
            //解析出键值
            if (arrSplitEqual.length > 1) {
                //正确解析
                mapRequest.put(arrSplitEqual[0], arrSplitEqual[1]);
            } else {
                if (arrSplitEqual[0] != "") {
                    //只有参数没有值，不加入
                    mapRequest.put(arrSplitEqual[0], "");
                }
            }
        }
        return mapRequest;
    }
}

ImageDBUtil

两个方法readImage2DB和readDB2Image,2就是to,即数据库和图片的相互转换

package top.sehnsucht.Util;


import top.sehnsucht.vo.Page;

import java.io.FileInputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

/**
 * @Description:
 * @Author: Cai
 * @CreateTime: 2021/10/29
 */

public class ImageDBUtil {

    /**
     * 将图片插入数据库
     * @param path filename
     */
    public static void readImage2DB(String path, String filename) {
        Connection conn = null;
        PreparedStatement ps = null;
        FileInputStream in = null;
        try {
            in = ImageUtil.readImage(path + "\\" + filename);
            conn = DBUtil.getConn();
            String sql = "insert into photo (id,name,photo)values(?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, ImageUtil.it++);//数据库表我弄成自增的了
            ps.setString(2, filename);
            ps.setBinaryStream(3, in, in.available());
            int count = ps.executeUpdate();
            if (count > 0) {
                System.out.println("插入成功！");
            } else {
                System.out.println("插入失败！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(conn);
            if (null != ps) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 读取数据库中图片
     * @param targetPath
     */
    public static void readDB2Image(String targetPath) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtil.getConn();
            String sql = "select * from photo";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Blob in = rs.getBlob("photo");
                System.out.println("看我在哪,文件名为: " + targetPath);
                ImageUtil.readBin2Image(in, targetPath);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(conn);
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 将数据库里面的id和name取出,并放到ArrayList里面,以便用fastjson转化
     * @param page
     * @return
     */
    public static ArrayList<Map<String,String>> selectImg(Page page) {
        Connection conn = null;
        PreparedStatement ps = null;
        FileInputStream in = null;
        ResultSet rs = null;
        ArrayList<Map<String,String>> arrs = new ArrayList<>();
        try {

            conn = DBUtil.getConn();
            String sql = "select id,name from photo order by id" ;
//            String sql = "select id,name from photo order by id limit " + page.getStart() + "," + page.getLimit() ;
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery(sql);
            while (rs.next()) {
                Map<String,String> map = new HashMap();
                Integer id = rs.getInt(1);
                String name = rs.getString(2);
                System.out.println("查询: " + id + " " + name);
                map.put("id",id.toString());
                map.put("name", name);
                arrs.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(conn);
            if (null != ps) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return arrs;
    }

    /**
     * 查询数据库有多少条信息,最后返回前端
     * @return
     */
    public static int selectCount() {
        Connection conn = null;
        PreparedStatement ps = null;
        FileInputStream in = null;
        ResultSet rs = null;
        int ans = 0;
        try {
            conn = DBUtil.getConn();
            String sql = "select id,name from photo ";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery(sql);
            while (rs.next()) {
               ans = rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConn(conn);
            if (null != ps) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return ans;
    }
}

ForwardServlet /data

查看是不是有jsp后缀的bug,可以忽略

package top.sehnsucht.pic;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

/**
 * @Description:
 * @Author: Cai
 * @CreateTime: 2021/10/31
 */

public class ForwardServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        System.out.println(req.getRequestURL());
        RequestDispatcher rs = req.getRequestDispatcher("pic/data.jsp");
        rs.forward(req, resp);
    }

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

UploadServlet /upload

文件上传功能

package top.sehnsucht.pic;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import top.sehnsucht.Util.ImageDBUtil;

import javax.servlet.ServletContext;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.IOException;
import java.util.Iterator;
import java.util.List;

/**
 * @Description:
 * @Author: Cai
 * @CreateTime: 2021/10/29
 */
public class UploadServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        resp.setCharacterEncoding("utf-8");
        resp.setContentType("text/html; charset=UTF-8");
        boolean isMultipart = ServletFileUpload.isMultipartContent(req);//判断文件是否可以上传
        if (isMultipart) {//可以上传,有问题就报异常
            ServletFileUpload upload = new ServletFileUpload(new DiskFileItemFactory());//创建一个FileItem工厂，通过工厂创建文件上传核心组件ServletFileUpload对象
            try {
                List<FileItem> items = upload.parseRequest(req);//通过核心上传组件解析request请求，获取表单的所有表单项，表单的每一个表单项对应一个FileItem
                Iterator<FileItem> it = items.iterator();
                for (FileItem item : items) {
                    if (item.isFormField()) {
                        System.out.println("其他类型文件");
                    } else {
                        String filename = item.getName();
                        String path = req.getSession().getServletContext().getRealPath("/") + "\\1";
                        File file = new File(path, filename);
                        item.write(file);
                        System.out.println(filename + "上传成功");
                        ImageDBUtil.readImage2DB(path, filename);//持久化

                        ServletContext servletContext = getServletContext();
                        servletContext.setAttribute("filename", filename);

                        req.getRequestDispatcher("/pic/list.jsp").forward(req, resp);
                        return;
                    }
                }
            } catch (FileUploadException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }

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

add.jsp

添加文件

<%--
  Created by IntelliJ IDEA.
  User: windows
  Date: 2021/10/29
  Time: 10:08
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>上传</title>
</head>
<body>
<form method="post" action="${pageContext.request.contextPath}/upload" enctype="multipart/form-data">
    选择一个文件:
    <br/><br/>
    <input type="file" name="uploadFile" />
    <br/><br/>
    <input type="submit" value="上传" />
</form>
</body>
</html>

data.jsp

手动强转json…因为没有像@ResponseBody这么方便的东西,这里需要导fastjson的包

<%@ page import="com.alibaba.fastjson.JSON" %>
<%@ page import="top.sehnsucht.Util.ImageDBUtil" %>
<%@ page import="top.sehnsucht.vo.Result" %>
<%@ page import="top.sehnsucht.vo.Page" %>
<%@ page import="top.sehnsucht.Util.UrlUtil" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%
    response.setHeader("Content-Type", "application/json;charset=UTF-8");//注意加上这一句

    System.out.println("现在路径是: " + request.getRequestURL());
    System.out.println(UrlUtil.getParam(String.valueOf(request.getRequestURL()),"page"));
    System.out.println(UrlUtil.getParam(String.valueOf(request.getRequestURL()),"limit  "));

    Result result = new Result();
    result.setCode(0);
    result.setCount((long) ImageDBUtil.selectCount());
    Page p = new Page();
    System.out.println("limit: " + request.getAttribute("limit") + "\n" + "page: " + request.getAttribute("page"));
    p.setLimit((Integer) request.getAttribute("limit"));
    p.setPage((Integer) request.getAttribute("page"));


    String json = JSON.toJSONString(ImageDBUtil.selectImg(p));

    out.println("{\"code\":" + result.getCode() + ",\"msg\":\"\",\"count\":" + result.getCount() + ",\"data\":" + json + "}");
%>

detail.jsp

用了JavaScript的DOM操作,在上面的类里面可以看到,我把图片存到了webapp/2里面,所以从数据库里面获取名字然后直接取就行了,这里原本不是在webapp下的,但是由于谷歌浏览器禁止在如何地方随意IO(Not allowed to load local resource),不然还要改tomcat的配置,我就直接改到了webapp底下

<%@ page import="top.sehnsucht.Util.UrlUtil" %>
<%@ page import="top.sehnsucht.Util.ImageDBUtil" %><%--
  Created by IntelliJ IDEA.
  User: windows
  Date: 2021/10/29
  Time: 10:08
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>Title</title>
    <style>
        img {
            width: 100%;
            height: 500px;
        }
    </style>
</head>
<body>

<%
    String pathpath = request.getSession().getServletContext().getRealPath("/") + "\\2";
    System.out.println("路径  " + pathpath);
    ImageDBUtil.readDB2Image(pathpath + "\\" + application.getAttribute("filename"));
%>

<p id="name"></p>
<img id="img"  alt="" src="">

<script>

    let data = sessionStorage.getItem("name");
    // req.getSession().getServletContext().getRealPath("/") + "\\1"

    document.getElementById("img").src = "../2/" + data;
    document.getElementById("name").innerText = "图片名:" + data + "\n图片正常显示";
</script>
</body>
</html>

list.jsp

用了layui的模板,看起来至少比自己写的好看很多了,只是有个分页的bug,就离谱,之前都没遇到这个错误,可能真的是不兼容jsp吧

<%--
  Created by IntelliJ IDEA.
  User: windows
  Date: 2021/10/29
  Time: 10:08
  To change this template use File | Settings | File Templates.
--%>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
    <title>列表</title>
    <link rel="stylesheet" href="${pageContext.request.contextPath}/layui/css/layui.css">
    <script src="${pageContext.request.contextPath}/layui/layui.js"></script>
    <script src="${pageContext.request.contextPath}/js/jquery-3.6.0.js"></script>
</head>
<body>

<div style="border-radius: 5px;margin-top: 100px;margin-left: 600px;margin-right: 600px">
    <table id="demo" lay-filter="table-pic-filter"></table>
</div>

<script type="text/html" id="col-pic-toolbar">
    <a class="layui-btn layui-btn-xs" lay-event="look">查看</a>
</script>
<script>
    layui.use('table', function () {
        var table = layui.table;

        table.render({
            elem: '#demo'
            , height: 312
            , url: '/data' //数据接口
            , page: true //开启分页
            , limits: [2, 3, 5, 10, 20, 50, 100, 200, 500]
            , cols: [[ //表头
                {field: 'id', title: 'id', width: 100, sort: true, fixed: 'left'}
                , {field: 'name', title: 'name', width: 350}
                , {title: '操作', align: 'center', toolbar: '#col-pic-toolbar'}
            ]]
            //下面是网上解决layui bug的方法,但是我试过了无效,可能是jsp不兼容的缘故?
            // ,parseData: function(res){ //将原始数据解析成 table 组件所规定的数据，res为从url中get到的数据
            //     var result;
            //     console.log(this);
            //     console.log(JSON.stringify(res));
            //     if(this.page.curr){
            //         result = res.data.slice(this.limit*(this.page.curr-1),this.limit*this.page.curr);
            //     }
            //     else{
            //         result=res.data.slice(0,this.limit);
            //     }
            //     return {
            //         "code": res.code, //解析接口状态
            //         "msg": res.msg, //解析提示文本
            //         "count": res.count, //解析数据长度
            //         "data": result //解析数据列表
            //     };
            // }
        });

        table.on('tool(table-pic-filter)', function (obj) {
            let data = obj.data;
            // alert(obj.data.name)
            let layEvent = obj.event;
            if (layEvent === 'look') {
                console.log(data.name)
                let url = '/pic/detail.jsp'
                layer.open({
                    type: 2,
                    title: "查看图片",
                    area: ['80%', '80%'],
                    shadeClose: true,
                    content: url
                });
                sessionStorage.setItem("name", data.name)
            }
        });
    });
</script>
</body>
</html>

pom.xml

有些可能没有用上

<dependency>
    <groupId>commons-fileupload</groupId>
    <artifactId>commons-fileupload</artifactId>
    <version>1.2.1</version>
</dependency>
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.6</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.26</version>
</dependency>
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13.2</version>
    <scope>test</scope>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.20</version>
</dependency>
<dependency>
    <groupId>org.webjars</groupId>
    <artifactId>jquery</artifactId>
    <version>3.5.1</version>
</dependency>
<dependency>
    <groupId>org.thymeleaf</groupId>
    <artifactId>thymeleaf</artifactId>
    <version>3.0.12.RELEASE</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.76</version>
</dependency>

中间还有几个问题,传参数嘛,本来是layui自己传的,但是中间有个地方也是不行,传参数使用了sessionStorage解决,但是框架是真的没法改…

sql

create table photo (
    id int not null auto_increment primary key ,
    name varchar(100) comment '图片名称',
    photo blob comment '照片'
)
default charset = utf8;

参考

Java使用Commons-FileUpload组件实现文件上传最佳方案

java+mysql实现保存图片到数据库，以及读取数据库存储的图片

Java 连接 MySQL读取 Blob 生成图片

layui镜像文档网站

更改后綴

layui数据表格分页无法正常显示

js 使用 sessionStorage

用jsp返回json

等…