java实现数据库前端可视化

上上签啊

于 2024-11-24 16:30:45 发布

阅读量418

点赞数 6

文章标签： java 数据库开发语言

本文链接：https://blog.youkuaiyun.com/qq_45828464/article/details/144005277

版权

由于研究要求，准备用javaweb实现实验室器件的数据库，方便查找器件在此记录一下学习进程

1.前端界面

前端界面采用vue的框架，为了使前端界面更美观引入element-ui组件包，vue项目创建网上有教程，这里不再详细说明，项目创建后进入项目用npm下载element-ui包，然后修改项目里面的HomeView.vue作为展示的界面。

<template>

  <el-container style="height: 100vh; border: 1px solid #eee">
    <el-header style="font-size: 40px; background-color: cadetblue">B110实验室</el-header>
    <el-container>
      <el-aside width="200px" style="border:1px solid #eee">
        <el-menu>
          <el-submenu index="1">
            <template slot="title">
              <i class="el-icon-menu"></i>
              <span>设备手册</span>
            </template>
            <el-menu-item-group>
              <el-menu-item index="1-1"><router-link to="/SALEAE16View">逻辑分析仪</router-link></el-menu-item>
              <el-menu-item index="1-2"><router-link to="/ESP32InstructionView">ESP32开发</router-link></el-menu-item>
              <el-menu-item index="1-3"><router-link to="/NetworkView">校园网文档</router-link></el-menu-item>
            </el-menu-item-group>
          </el-submenu>
          <el-menu-item index="2"  class="menu-item">
            <i class="el-icon-document"></i>
            <span slot="title"><router-link to="/">元器件库</router-link></span>
          </el-menu-item>
        </el-menu>
      </el-aside>
      <el-main style="flex:1;">
        <div class="table-container">
          <div class="footer-container">
            <div class="app-container">
              <!-- 搜索表单 -->
              <el-form :inline="true" :model="searchEmp" class="demo-form-inline">
                <el-form-item label=" 器件名">
                  <el-input v-model="searchEmp.name" placeholder="请输入要查询的器件名"></el-input>
                </el-form-item>
                <el-form-item>
                  <el-button type="primary" @click="onSubmit">查询</el-button>
                  <el-button type="info" @click="clear">清空</el-button>
                </el-form-item>
              </el-form>
            </div>
            <!-- 编辑和提交按钮 -->
            <div class="button_container">
              <el-button type="primary" class="button_style" @click="enableEditing">
                {{ isEditing ? '保存' : '编辑' }}
              </el-button>
              <el-button type="success" class="button_style" @click="openAddComponentDialog">添加组件</el-button>
            </div>
          </div>
          <el-table
              :data="tableData"
              stripe
              border
              class="centered-table"
              style="width: 100%; height : calc(100vh - 150px);margin: 20px auto;">

            <!-- id 列 -->
            <el-table-column
                label="序号"
                width="150"
                align="center">
              <template #default="scope">
                {{ calculateSerialNumber(scope.$index) }}
              </template>
            </el-table-column>

            <!-- componentName 列 -->
            <el-table-column
                prop="componentName"
                label="器件名称"
                width="180"
                align="center">
              <template #default="scope">
                {{ scope.row.componentName }}
              </template>
            </el-table-column>

            <!-- componentImage 列 -->
            <el-table-column
                label="器件图片"
                width="220"
                header-align="center"
                align="center">
              <template #default="scope">
                <el-image
                    :src="scope.row.componentImage"
                    class="centered-image"
                    style="width: 120px; height: 120px;">
                </el-image>
              </template>
            </el-table-column>

            <!-- componentAmount 列 -->
            <el-table-column
                label="器件数量"
                width="250"
                header-align="center"
                align="center">
              <template #default="scope">
                <el-input-number
                    v-model="scope.row.componentAmount"
                    :disabled="!isEditing"
                    controls-position="right"
                    :min="0">
                </el-input-number>
              </template>
            </el-table-column>

            <!-- componentData 列 -->
            <el-table-column
                prop="componentData"
                label="数据手册"
                width="500"
                header-align="center"
                align="center">
              <template #default="scope">
                <el-link
                    :href="scope.row.componentData"
                target="_blank"
                type="primary">
                {{ scope.row.componentData }}
                </el-link>
              </template>
            </el-table-column>

            <!-- updateTime 列 -->
            <el-table-column
                prop="updateTime"
                label="最后更新时间"
                min-width="150"
                header-align="center"
                align="center">
              <template #default="scope">
                {{ new Date(scope.row.updateTime).toLocaleString('zh-CN', { hour12: false }) }}
              </template>
            </el-table-column>

            <el-table-column
                label="操作"
                width="150"
                header-align="center"
                align="center">
              <template #default="scope">
                <el-button
                    type="danger"
                    size="small"
                    @click="confirmDelete(scope.row.id)">
                  删除
                </el-button>
              </template>
            </el-table-column>

          </el-table>
          <!-- 分页 -->
          <el-pagination
              @size-change="handleSizeChange"
              @current-change="handleCurrentChange"
              :current-page="currentPage"
              :page-size="pageSize"
              :page-sizes="[5, 10, 15, 20]"
              layout="total, sizes, prev, pager, next, jumper"
              :total="totalCount">
          </el-pagination>

         <!-- 添加组件的对话框 -->
          <el-dialog title="添加新组件" :visible.sync="isAddComponentDialogVisible">
            <el-form :model="newComponent" label-width="100px">
              <el-form-item label="组件名称">
                <el-input v-model="newComponent.componentName"></el-input>
              </el-form-item>

              <el-form-item label="组件数量">
                <el-input-number v-model="newComponent.componentAmount" :min="0"></el-input-number>
              </el-form-item>
              <el-form-item label="组件数据">
                <el-input v-model="newComponent.componentData"></el-input>
              </el-form-item>

              <el-form-item label="组件图片">
                <el-upload
                    class="upload-demo"
                    action="http://0.0.0.0:8081/api/components/upload"
                    list-type="picture-card"
                    :on-success="handleUploadSuccess"
                    :file-list="fileList"
                    :on-remove="handleRemove">
                  <i class="el-icon-plus"></i>
                </el-upload>
              </el-form-item>
            </el-form>

            <span slot="footer" class="dialog-footer">
        <el-button @click="isAddComponentDialogVisible = false">取消</el-button>
        <el-button type="primary" @click="submitNewComponent">提交</el-button>
      </span>
          </el-dialog>
        </div>

      </el-main>
    </el-container>
  </el-container>

</template>
<script>
import axios from 'axios';
import { fetchData } from '@/api/components.js';
export default {
  data() {
    return {
      components: [], // 组件数据列表
      isEditing: false, // 编辑模式开关
      isAddComponentDialogVisible: false, // 控制添加对话框的显示
      newComponent: {
        componentName: '', // 新组件名称
        componentAmount: 0, // 新组件数量
        componentData: '',  //新组件数据手册url
        componentImage: '' // 新组件图片路径
      },
      searchEmp: { name: ''},  // 搜索表单数据
      tableData: [],  // 表格数据
      currentPage: 1,  // 当前页码
      pageSize: 5,     // 每页显示的条数
      totalCount: 0,   // 总记录数
      fileList: [] // 图片文件列表
    };
  },
  mounted() {
    this.page();  // 当页面加载完成后获取数据
  },
  methods: {
    // 查询功能
    page() {
      fetchData(this.searchEmp.name, this.currentPage, this.pageSize).then((res) => {
        this.tableData = res.data.data.rows;  // 表格数据
        this.totalCount = res.data.data.total;  // 总记录数
        console.log('table',this.tableData);
      });
    },
    // 查询按钮
    onSubmit() {
      this.currentPage = 1;  // 重置当前页码
      this.page();  // 查询
    },
    // 清空查询条件
    clear() {
      this.searchEmp = { name: ''};  // 清空搜索条件
      this.page();  // 查询
    },
    handleSizeChange(val) {
      this.pageSize = val;
      console.log(`每页 ${val} 条`);
      this.page();
    },
    // 分页功能
    handleCurrentChange(val) {
      this.currentPage = val;  // 更新当前页码
      this.page();  // 查询
    },
    calculateSerialNumber(index) {
      return (this.currentPage - 1) * this.pageSize + index + 1;
    },

    // 开启或关闭编辑模式
    enableEditing() {
      this.isEditing = !this.isEditing;
      if (!this.isEditing) {
        this.submitChanges(); // 如果结束编辑模式，自动提交更改
      }
    },

    // 删除前的确认提示
    confirmDelete(id) {
      this.$confirm('此操作将永久删除该组件, 是否继续?', '提示', {
        confirmButtonText: '确定',
        cancelButtonText: '取消',
        type: 'warning'
      }).then(() => {
        this.deleteComponent(id);
      }).catch(() => {
        this.$message({
          type: 'info',
          message: '已取消删除'
        });
      });
    },

    // 删除组件
    deleteComponent(id) {
      axios.delete(`http://0.0.0.0:8081/api/components/delete/${id}`)
          .then(response => {
            if (response.data.success) {
              this.$message({
                type: 'success',
                message: '删除成功!'
              });
              this.page(); // 删除成功后，重新获取组件列表
            } else {
              this.$message({
                type: 'error',
                message: '删除失败!'
              });
            }
          })
          .catch(error => {
            console.error('Error deleting component:', error);
            this.$message({
              type: 'error',
              message: '删除失败，服务器错误!'
            });
          });
    },


    // 提交组件更改
    submitChanges() {
      const updates = this.tableData.map(component => ({
        id: component.id,
        componentAmount: component.componentAmount,  // 传递驼峰命名属性
        updateTime: this.getCurrentLocalTime()
      }));

      axios.post('http://0.0.0.0:8081/api/components/update', updates)
          .then(response => {
            if (response.data.success) {
              alert('更新成功');
              this.page(); // 成功后重新获取组件列表
            } else {
              alert('更新失败');
            }
          })
          .catch(error => {
            console.error('Error updating components:', error);
          });
    },

    // 打开添加组件对话框
    openAddComponentDialog() {
      this.isAddComponentDialogVisible = true;
    },

    // 图片上传成功后的处理
    handleUploadSuccess(response, file) {
      console.log('Upload success response:', response);// 打印上传响应
      console.log('file',file.name);
      this.newComponent.componentImage = response.filePath; // 确保后端返回的路径正确
    },

    // 移除已上传的图片
    handleRemove(file) {
      this.fileList = this.fileList.filter(item => item.uid !== file.uid);
    },

    // 提交新组件
    submitNewComponent() {
      axios.post('http://0.0.0.0:8081/api/components/add', {
        componentName: this.newComponent.componentName,  // 驼峰命名属性
        componentAmount: this.newComponent.componentAmount, // 驼峰命名属性
        componentImage: this.newComponent.componentImage, // 图片路径
        componentData: this.newComponent.componentData

      })
          .then(response => {
            if (response.data.success) {
              alert('组件添加成功');
              this.page(); // 重新获取组件列表
              this.isAddComponentDialogVisible = false;
              this.resetNewComponentForm(); // 重置表单
            } else {
              alert('组件添加失败');
            }
          })
          .catch(error => {
            console.error('Error adding component:', error.response.data);
          });
    },

    // 重置添加组件表单
    resetNewComponentForm() {
      this.newComponent = {
        componentName: '', // 重置组件名称
        componentAmount: 0, // 重置组件数量
        componentImage: '' // 重置组件图片
      };
      this.fileList = []; // 清空文件列表
    },

    // 获取当前时间
    getCurrentLocalTime() {
      const now = new Date();
      const year = now.getFullYear();
      const month = String(now.getMonth() + 1).padStart(2, '0');
      const day = String(now.getDate()).padStart(2, '0');
      const hours = String(now.getHours()).padStart(2, '0');
      const minutes = String(now.getMinutes()).padStart(2, '0');
      const seconds = String(now.getSeconds()).padStart(2, '0');
      return `${year}-${month}-${day} ${hours}:${minutes}:${seconds}`;
    }
  }
};
</script>

<style scoped>

/* 容器宽度和居中 */
.table-container {
  width: 100%;
  display: flex;
  flex-direction: column;
  justify-content: center;
  align-items: center;
  margin: 0 auto;
  padding: 0;
}

a{
  text-decoration: none;
  color: black;
}

.menu-item{
  background-color: lightblue;
}
.footer-container {
  display: flex;
  justify-content: space-between;
  align-items: center;
  margin: 5px auto;
  width: 100%;
}
html, body {
  height: 100%;
}
.main-content {
  min-height: 100%;
}

.button-container {
  display: flex;
  gap: 10px;
}

.button-style {
  margin-left: 10px;
}


</style>

2.后端

后端依然采用最熟悉的Spring-boot和mybatis框架，controller用来处理相关访问。

package com.b110.component_databases.controller;

import com.b110.component_databases.pojo.Component;
import com.b110.component_databases.pojo.PageBean;
import com.b110.component_databases.pojo.Result;
import com.b110.component_databases.service.ComponentService;
import com.b110.component_databases.utils.AliOSSUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.Resource;
import org.springframework.core.io.ResourceLoader;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.sql.Timestamp;
import java.util.List;
import java.util.Map;
import java.util.HashMap;

@Slf4j
@RestController
@RequestMapping("/api/components")
public class ComponentController {

    @Autowired
    private ComponentService componentService;

    // 获取所有组件
   /* @GetMapping
    public List<Component> getAllComponents() {
        return componentService.getAllComponents();
    }
*/
    // 更新组件数量
    @PostMapping("/update")
    public Map<String, Object> updateComponentAmount(@RequestBody List<Map<String, Object>> updates) {
        Map<String, Object> response = new HashMap<>();
        try {
            componentService.updateComponentAmount(updates);
            response.put("success", true);
        } catch (IllegalArgumentException e) {
            response.put("success", false);
            response.put("message", e.getMessage());
        } catch (Exception e) {
            response.put("success", false);
            response.put("message", "Error: " + e.getMessage());
            e.printStackTrace();
        }
        return response;
    }

    @Autowired
    private AliOSSUtils aliOSSUtils;

    @PostMapping("/upload")
    /*public Map<String, Object> uploadImage(@RequestParam("file") MultipartFile file) {
        Map<String, Object> response = new HashMap<>();
        try {
            // 使用绝对路径指向项目的 resources 目录
            String resourcePath = System.getProperty("user.dir") + "/src/main/resources/static/images/";

            // 检查目录是否存在，如果不存在则创建
            File uploadDir = new File(resourcePath);
            if (!uploadDir.exists()) {
                uploadDir.mkdirs();
            }

            // 生成新文件名并保存文件
            String fileName = System.currentTimeMillis() + "_" + file.getOriginalFilename();
            file.transferTo(new File(uploadDir, fileName));

            response.put("success", true);
            response.put("filePath", "/images/" + fileName);
        } catch (Exception e) {
            response.put("success", false);
            response.put("message", e.getMessage());
        }
        return response;
    }*/
    public Map<String, Object> uploadImage(@RequestParam("file") MultipartFile file) {

        Map<String, Object> response = new HashMap<>();
        try {
            System.out.println("文件上传文件名：" + file.getOriginalFilename());
            String url = aliOSSUtils.upload(file);
            response.put("success", true);
            System.out.println("文件url：" + url);
            response.put("filePath", url);
        } catch (Exception e) {
            response.put("success", false);
            response.put("message", e.getMessage());
        }
        return response;
    }


    // 添加组件接口
    @PostMapping("/add")
    public Map<String, Object> addComponent(@RequestBody Component component) {
        Map<String, Object> response = new HashMap<>();
        try {
            System.out.println("Received component data: "+component);
            if (component.getComponentName() == null || component.getComponentName().isEmpty()) {
                throw new IllegalArgumentException("Component name is required");
            }
            if (component.getComponentAmount() == 0 || component.getComponentAmount() < 0) {
                throw new IllegalArgumentException("Component amount must be non-negative");
            }
            component.setUpdateTime(new Timestamp(System.currentTimeMillis()));
            componentService.addComponent(component);
            response.put("success", true);
        } catch (Exception e) {
            response.put("success", false);
            response.put("message", e.getMessage());
            e.printStackTrace();
        }
        return response;
    }

    @DeleteMapping("/delete/{id}")
    public Map<String, Object> deleteComponent(@PathVariable("id") int id) {
        Map<String, Object> response = new HashMap<>();
        try {
            componentService.deleteComponentById(id);
            response.put("success", true);
        } catch (Exception e) {
            response.put("success", false);
            response.put("message", e.getMessage());
        }
        return response;
    }

    @GetMapping
    public Result page(@RequestParam (defaultValue = "1") Integer page ,
                       @RequestParam (defaultValue = "4") Integer pageSize,
                       String componentName)

    {
        log.info("分页查询，参数,器件名：{},{},{}",page,pageSize,componentName);
        PageBean pageBean = componentService.page(page,pageSize,componentName);
        return Result.success(pageBean);
    }


}

Service层

package com.b110.component_databases.service;

import com.b110.component_databases.mapper.ComponentMapper;
import com.b110.component_databases.pojo.Component;
import com.b110.component_databases.pojo.PageBean;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.transaction.Transactional;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;
@Slf4j
@Service
public class ComponentService {

    @Autowired
    private ComponentMapper componentMapper;

    // 获取所有组件
    public List<Component> getAllComponents() {
        return componentMapper.findAll();
    }

    // 更新组件数量
    public void updateComponentAmount(List<Map<String, Object>> updates) {
        for (Map<String, Object> update : updates) {
            if (update.get("id") == null || update.get("componentAmount") == null) {
                throw new IllegalArgumentException("id or componentAmount is null");
            }

            Integer id = Integer.parseInt(update.get("id").toString());
            Integer newComponentAmount = Integer.parseInt(update.get("componentAmount").toString());

            // 获取当前的 component_amount
            Integer currentComponentAmount = componentMapper.findComponentAmountById(id);

            // 只有当数量改变时，才更新数量和更新时间
            if (currentComponentAmount != null && !newComponentAmount.equals(currentComponentAmount)) {
                String updateTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
                componentMapper.updateComponent(id, newComponentAmount, updateTime);
            }
        }
    }
    @Transactional
    public void addComponent(Component component) {
        componentMapper.insertComponent(component);
    }

    public void deleteComponentById(int id) {
        componentMapper.delete(id);
    }


    public PageBean page(Integer page, Integer pageSize,String componentName) {
        Long count = componentMapper.count();
        Integer start = (page - 1) * pageSize;
        log.info("参数：{}",start);
        List<Component> componentList = componentMapper.page(componentName,start, pageSize);
        PageBean pageBean = new PageBean(count,componentList);
        log.info("返回数据：\n{} ",pageBean);
        return pageBean;
    }
}

Mapper层

package com.b110.component_databases.mapper;

import com.b110.component_databases.pojo.Component;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface ComponentMapper {

    // 查询所有组件
    @Select("SELECT * FROM electronic_component")
    List<Component> findAll();

    // 更新组件的数量和更新时间
    @Update("UPDATE electronic_component SET componentAmount = #{componentAmount}, updateTime = #{updateTime} WHERE id = #{id}")
    void updateComponent(@Param("id") int id, @Param("componentAmount") int componentAmount, @Param("updateTime") String updateTime);

    // 查询组件的当前数量
    @Select("SELECT componentAmount FROM electronic_component WHERE id = #{id}")
    int findComponentAmountById(@Param("id") int id);

    // 插入元器件
    @Insert("INSERT INTO electronic_component (componentName, componentAmount, componentData, componentImage, updateTime) " +
            "VALUES (#{componentName}, #{componentAmount}, #{componentData}, #{componentImage}, #{updateTime})")
    void insertComponent(Component component);
    //删除指定id数据
    @Delete("DELETE FROM electronic_component WHERE id = #{id}")
    void delete(int id);

    @Select("select count(*) from electronic_component")
    public Long count();

    @Select("select * from electronic_component where componentName like '%${componentName}%' limit #{start},#{pageSize}")
    public List<Component> page(@Param("componentName") String componentName, @Param("start") Integer start, @Param("pageSize") Integer pageSize);

    @Select("select * from electronic_component where componentName like '%${componentName}%'")
    public List<Component> list(@Param("componentName") String componentName);
}

创建器件的实体类

package com.b110.component_databases.pojo;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import java.sql.Timestamp;

@Entity
public class Component {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;

    private String componentName;
    private String componentImage;
    private String componentData;
    private int componentAmount;
    private Timestamp updateTime;

    // 默认构造函数
    public Component() {
    }

    // 全参构造函数
    public Component(Integer id, String componentName, String componentImage, String componentData, int componentAmount, Timestamp updateTime) {
        this.id = id;
        this.componentName = componentName;
        this.componentImage = componentImage;
        this.componentData = componentData;
        this.componentAmount = componentAmount;
        this.updateTime = updateTime;
    }

    // Getter 和 Setter 方法
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getComponentName() {
        return componentName;
    }

    public void setComponentName(String componentName) {
        this.componentName = componentName;
    }

    public String getComponentImage() {
        return componentImage;
    }

    public void setComponentImage(String componentImage) {
        this.componentImage = componentImage;
    }

    public String getComponentData() {
        return componentData;
    }

    public void setComponentData(String componentData) {
        this.componentData = componentData;
    }

    public int getComponentAmount() {
        return componentAmount;
    }

    public void setComponentAmount(int componentAmount) {
        this.componentAmount = componentAmount;
    }

    public Timestamp getUpdateTime() {
        return updateTime;
    }

    public void setUpdateTime(Timestamp updateTime) {
        this.updateTime = updateTime;
    }

    @Override
    public String toString() {
        return "Component{" +
                "id=" + id +
                ", componentName='" + componentName + '\'' +
                ", componentImage='" + componentImage + '\'' +
                ", componentData='" + componentData + '\'' +
                ", componentAmount=" + componentAmount +
                ", updateTime=" + updateTime +
                '}';
    }
}

在同一个主机运行前后端的所以不需要配置跨域如果前后端在不同的主机运行需要配置跨域

package com.b110.component_databases.config;


import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.CorsRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;


@Configuration
public class WebConfig implements WebMvcConfigurer {

    @Override
    public void addCorsMappings(CorsRegistry registry) {
        registry.addMapping("/**") // 允许所有路径
                .allowedOriginPatterns("http://localhost:8082") // 允许来自这个地址的请求
                .allowedMethods("GET", "POST", "PUT", "DELETE") // 允许的请求方法
                .allowedHeaders("*") // 允许的请求头
                .allowCredentials(true); // 允许携带凭据
    }
    public void addResourceHandlers(ResourceHandlerRegistry registry) {
        registry.addResourceHandler("/images/**")
                .addResourceLocations("file:src/main/resources/static/images/");
    }

}

数据库设计如下，图片存储在阿里云在数据库中存储的是图片的url