获取数据库中字段的数据作为下拉框选项

本文涉及的产品
RDS MySQL DuckDB 分析主实例,集群系列 4核8GB
RDS AI 助手,专业版
简介: 获取数据库中字段的数据作为下拉框选项

 如题:当我们想把数据库中字段的数据作为对应的下拉框选项,并且数据库字段很多的时候。应该在后端获取这个表里有多少字段,遍历这个表,拿到所有的字段并放到一个map集合里,我们看看代码。

image.gif 编辑

image.gif 编辑

这个表里有大概四五十个字段,我们不能每一个字段都写一个对应获取的方法,所以我们在controller层直接使用这个方法。

@RequestMapping("/getDropdownItems")
    public ResultData getDropdownItems() {
        Map<String, List<String>> dropdownItems = new HashMap<>();
        String dbUrl = "jdbc:mysql://192.168.1.60:3306/qcfx_db?characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true&nullCatalogMeansCurrent=true&allowPublicKeyRetrieval=true";
        String user = "root";
        String password = "ME#EDC2WSx!qaZ";
        try (Connection conn = DriverManager.getConnection(dbUrl, user, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM report_changjingku_list WHERE 1=2")) { // 只查询字段,不查询数据
            ResultSetMetaData metaData = rs.getMetaData();
            int columnCount = metaData.getColumnCount();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                String query = "SELECT DISTINCT " + columnName + " FROM report_changjingku_list";
                try (ResultSet columnResultSet = stmt.executeQuery(query)) {
                    List<String> items = new ArrayList<>();
                    while (columnResultSet.next()) {
                        items.add(columnResultSet.getString(1));
                    }
                    dropdownItems.put(columnName, items);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ResultData.success("ok",dropdownItems);
    }

image.gif

连接到数据库后,遍历字段个数并且插入到map集合里。

前端:

页面展示:

image.gif 编辑

代码:

js:

export function getDropdownItems() {
    return request({
      url: '/mixTableAdd/getDropdownItems',
      method: 'post'
    })
  }

image.gif

选择框:

<template>
  <el-form
    ref="formRef"
    :model="reqUser"
    label-width="180px"
    v-loading="formLoading"
  >
  <el-row :gutter="24">
    <el-col :span="12">
    <el-form-item label="竖维度" prop="verticalDimension">
      <el-select v-model="reqUser.verticalDimension" class="filter-item" placeholder="请选择竖维度" clearable style="width: 230%;">
        <el-option
          v-for="item in statusList"
          :key="item.name"
          :label="item.comment"
          :value="item.name"
        />
      </el-select>
    </el-form-item>
  </el-col>
  <el-col :span="12">
    <el-form-item label="横维度" prop="horizontalDimension">
      <el-select v-model="reqUser.horizontalDimension" class="filter-item" placeholder="请选择横维度" clearable style="width: 230%;">
        <el-option
          v-for="item in statusList"
          :key="item.name"
          :label="item.comment"
          :value="item.name"
        />
      </el-select>
    </el-form-item>
  </el-col>
</el-row>
<el-row :gutter="24">
  <el-col :span="12">
    <el-form-item label="值" prop="price">
      <el-select v-model="reqUser.price" class="filter-item" placeholder="请选择值" clearable style="width: 230%;">
        <el-option
          v-for="item in statusList"
          :key="item.name"
          :label="item.comment"
          :value="item.name"
        />
      </el-select>
    </el-form-item>
  </el-col>
  <el-col :span="12">
    <el-form-item label="年龄" prop="age">
      <el-select v-model="reqUser.age" class="filter-item" placeholder="请选择年龄" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_nl"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
</el-row>
<el-row :gutter="24">
  <el-col :span="12">
    <el-form-item label="行业" prop="industry">
      <el-select v-model="reqUser.industry" class="filter-item" placeholder="请选择行业" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_hy"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
  <el-col :span="12">
    <el-form-item label="性别" prop="sex">
      <el-select v-model="reqUser.sex" class="filter-item" placeholder="请选择性别" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_xb"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
</el-row>
<el-row :gutter="24">
  <el-col :span="12">
    <el-form-item label="婚姻状况" prop="maritalStatus">
      <el-select v-model="reqUser.maritalStatus" class="filter-item" placeholder="请选择婚姻状况" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_hyzk"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
  <el-col :span="12">
    <el-form-item label="小孩数量" prop="childNum">
      <el-select v-model="reqUser.childNum" class="filter-item" placeholder="请选择小孩数量" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_xhsl"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
</el-row>
<el-row :gutter="24">
  <el-col :span="12">
    <el-form-item label="小孩年龄段" prop="childAgeScope">
      <el-select v-model="reqUser.childAgeScope" class="filter-item" placeholder="请选择小孩年龄段" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_xhnld"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
  <el-col :span="12">
    <el-form-item label="职业属性" prop="occupationAttribute">
      <el-select v-model="reqUser.occupationAttribute" class="filter-item" placeholder="请选择职业属性" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_zysx"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
</el-row>
<el-row :gutter="24">
  <el-col :span="12">
    <el-form-item label="家庭年收入情况(万)" prop="homeIncome">
      <el-select v-model="reqUser.homeIncome" class="filter-item" placeholder="请选择家庭年收入情况(万)" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_jtnsrqk"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
  <el-col :span="12">
    <el-form-item label="学历情况" prop="educationalBack">
      <el-select v-model="reqUser.educationalBack" class="filter-item" placeholder="请选择学历情况" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_xlqk"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
</el-row>
<el-row :gutter="24">
  <el-col :span="12">
    <el-form-item label="车系" prop="carSeries">
      <el-select v-model="reqUser.carSeries" class="filter-item" placeholder="请选择车系" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_cx"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
  <el-col :span="12">
    <el-form-item label="级别" prop="carLevel">
      <el-select v-model="reqUser.carLevel" class="filter-item" placeholder="请选择级别" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_jb"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
</el-row>
<el-row :gutter="24">
  <el-col :span="12">
    <el-form-item label="车身类别" prop="carBody">
      <el-select v-model="reqUser.carBody" class="filter-item" placeholder="请选择车身类别" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_cslb"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
  <el-col :span="12">
    <el-form-item label="TP价格段(万)" prop="tpPrice">
      <el-select v-model="reqUser.tpPrice" class="filter-item" placeholder="请选择TP价格段(万)" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_tpjgd"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
</el-row>
<el-row :gutter="24">
  <el-col :span="12">
    <el-form-item label="能源类型" prop="energyType">
      <el-select v-model="reqUser.energyType" class="filter-item" placeholder="请选择能源类型" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_nylx"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
  <el-col :span="12">
    <el-form-item label="一级场景" prop="oneScene">
      <el-select v-model="reqUser.oneScene" class="filter-item" placeholder="请选择一级场景" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_yjcj"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
</el-row>
<el-row :gutter="24">
  <el-col :span="12">
    <el-form-item label="二级场景描述" prop="twoSceneDescription">
      <el-select v-model="reqUser.twoSceneDescription" class="filter-item" placeholder="请选择二级场景描述" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_ejcjms"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
  <el-col :span="12">
    <el-form-item label="出行阶段" prop="travelStage">
      <el-select v-model="reqUser.travelStage" class="filter-item" placeholder="请选择出行阶段" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_cxjd"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
</el-row>
<el-row :gutter="24">
  <el-col :span="12">
    <el-form-item label="出行人数" prop="travelNum">
      <el-select v-model="reqUser.travelNum" class="filter-item" placeholder="请选择出行人数" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_cxrs"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
  <el-col :span="12">
    <el-form-item label="用户角色" prop="userRole">
      <el-select v-model="reqUser.userRole" class="filter-item" placeholder="请选择用户角色" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_yhjs"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
</el-row>
<el-row :gutter="24">
  <el-col :span="12">
    <el-form-item label="用户特征" prop="userFeature">
      <el-select v-model="reqUser.userFeature" class="filter-item" placeholder="请选择用户特征" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_yhtz"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
  <el-col :span="12">
    <el-form-item label="驾驶状态" prop="driveStatus">
      <el-select v-model="reqUser.driveStatus" class="filter-item" placeholder="请选择驾驶状态" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_jszt"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
</el-row>
<el-row :gutter="24">
  <el-col :span="12">
    <el-form-item label="驾驶道路" prop="driveRoad">
      <el-select v-model="reqUser.driveRoad" class="filter-item" placeholder="请选择驾驶道路" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_jsdl"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
  <el-col :span="12">
    <el-form-item label="驾驶区域" prop="driveArea">
      <el-select v-model="reqUser.driveArea" class="filter-item" placeholder="请选择驾驶区域" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_jsqy"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
</el-row>
<el-row :gutter="24">
  <el-col :span="12">
  </el-col>
  <el-col :span="12">
    <el-form-item label="天气环境" prop="weather">
      <el-select v-model="reqUser.weather" class="filter-item" placeholder="请选择天气环境" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_tqhj"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
  <el-col :span="12">
    <el-form-item label="时间" prop="time">
      <el-date-picker
      v-model.trim="reqUser.time"
       type="datetime"
      style="width: 230%;"
      placeholder="选择时间"/>
    </el-form-item>
  </el-col>
</el-row>
<el-row :gutter="24">
  <el-col :span="12">
    <el-form-item label="季节" prop="season">
      <el-select v-model="reqUser.season" class="filter-item" placeholder="请选择季节" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_jj"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
  <el-col :span="12">
    <el-form-item label="数据采集地点" prop="dataLocality">
      <el-select v-model="reqUser.dataLocality" class="filter-item" placeholder="请选择数据采集地点" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_sjcjdd"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
</el-row>
<el-row :gutter="24">
  <el-col :span="12">
    <el-form-item label="城市等级" prop="cityLevel">
      <el-select v-model="reqUser.cityLevel" class="filter-item" placeholder="请选择城市等级" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_csdj"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
  <el-col :span="12">
    <el-form-item label="隶属省份" prop="province">
      <el-select v-model="reqUser.province" class="filter-item" placeholder="请选择隶属省份" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_lssf"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
</el-row>
<el-row :gutter="24">
  <el-col :span="12">
    <el-form-item label="一级指标" prop="oneIndex">
      <el-select v-model="reqUser.oneIndex" class="filter-item" placeholder="请选择一级指标" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_yjzb"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
  <el-col :span="12">
    <el-form-item label="二级指标" prop="twoIndex">
      <el-select v-model="reqUser.twoIndex" class="filter-item" placeholder="请选择二级指标" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_ejzb"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
</el-row>
<el-row :gutter="24">
  <el-col :span="12">
    <el-form-item label="三级指标" prop="threeIndex">
      <el-select v-model="reqUser.threeIndex" class="filter-item" placeholder="请选择三级指标" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_sjzb"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
  <el-col :span="12">
    <el-form-item label="感知价值" prop="feelPrice">
      <el-select v-model="reqUser.feelPrice" class="filter-item" placeholder="请选择感知价值" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_gzjz"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
</el-row>
<el-row :gutter="24">
  <el-col :span="12">
    <el-form-item label="感知时刻" prop="feelTime">
      <el-date-picker
      v-model.trim="reqUser.feelTime"
       type="datetime"
      style="width: 230%;"
      placeholder="选择感知时刻"/>
    </el-form-item>
  </el-col>
  <el-col :span="12">
    <el-form-item label="当前解决方案" prop="workOutScheme">
      <el-select v-model="reqUser.workOutScheme" class="filter-item" placeholder="请选择当前解决方案" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_dqjjfa"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
</el-row>
<el-row :gutter="24">
  <el-col :span="12">
    <el-form-item label="数据来源" prop="dataResource">
      <el-select v-model="reqUser.dataResource" class="filter-item" placeholder="请选择数据来源" clearable style="width: 230%;">
        <el-option
          v-for="item in selectSource.r_sjly"
          :key="item"
          :label="item"
          :value="item"
        />
      </el-select>
    </el-form-item>
  </el-col>
</el-row>
<el-button type="primary" @click="save" style="margin-left: 47%;margin-top: 3%">确定</el-button>
  </el-form>
</template>

image.gif

获取方法:

getDropdownItems(){
      getDropdownItems().then(response => {
        console.log(response.r_nl,'asdsad')
        this.selectSource = response.data
      })
    }

image.gif

同时要定义一个selectSource数据来接受返回的字段。

返回的字段在postman中的样子:

image.gif 编辑

非常好用


目录
相关文章
|
7月前
|
存储 JSON 关系型数据库
【干货满满】解密 API 数据解析:从 JSON 到数据库存储的完整流程
本文详解电商API开发中JSON数据解析与数据库存储的全流程,涵盖数据提取、清洗、转换及优化策略,结合Python实战代码与主流数据库方案,助开发者构建高效、可靠的数据处理管道。
|
5月前
|
数据采集 关系型数据库 MySQL
python爬取数据存入数据库
Python爬虫结合Scrapy与SQLAlchemy,实现高效数据采集并存入MySQL/PostgreSQL/SQLite。通过ORM映射、连接池优化与批量提交,支持百万级数据高速写入,具备良好的可扩展性与稳定性。
|
10月前
|
存储 缓存 数据库
数据库数据删除策略:硬删除vs软删除的最佳实践指南
在项目开发中,“删除”操作常见但方式多样,主要分为硬删除与软删除。硬删除直接从数据库移除数据,操作简单、高效,但不可恢复;适用于临时或敏感数据。软删除通过标记字段保留数据,支持恢复和审计,但增加查询复杂度与数据量;适合需追踪历史或可恢复的场景。两者各有优劣,实际开发中常结合使用以满足不同需求。
977 4
|
6月前
|
存储 数据管理 数据库
数据字典是什么?和数据库、数据仓库有什么关系?
在数据处理中,你是否常困惑于字段含义、指标计算或数据来源?数据字典正是解答这些问题的关键工具,它清晰定义数据的名称、类型、来源、计算方式等,服务于开发者、分析师和数据管理者。本文详解数据字典的定义、组成及其与数据库、数据仓库的关系,助你夯实数据基础。
数据字典是什么?和数据库、数据仓库有什么关系?
|
5月前
|
人工智能 Java 关系型数据库
使用数据连接池进行数据库操作
使用数据连接池进行数据库操作
156 11
|
6月前
|
存储 关系型数据库 数据库
【赵渝强老师】PostgreSQL数据库的WAL日志与数据写入的过程
PostgreSQL中的WAL(预写日志)是保证数据完整性的关键技术。在数据修改前,系统会先将日志写入WAL,确保宕机时可通过日志恢复数据。它减少了磁盘I/O,提升了性能,并支持手动切换日志文件。WAL文件默认存储在pg_wal目录下,采用16进制命名规则。此外,PostgreSQL提供pg_waldump工具解析日志内容。
598 0
|
8月前
|
存储 SQL Java
数据存储使用文件还是数据库,哪个更合适?
数据库和文件系统各有优劣:数据库读写性能较低、结构 rigid,但具备计算能力和数据一致性保障;文件系统灵活易管理、读写高效,但缺乏计算能力且无法保证一致性。针对仅需高效存储与灵活管理的场景,文件系统更优,但其计算短板可通过开源工具 SPL(Structured Process Language)弥补。SPL 提供独立计算语法及高性能文件格式(如集文件、组表),支持复杂计算与多源混合查询,甚至可替代数据仓库。此外,SPL 易集成、支持热切换,大幅提升开发运维效率,是后数据库时代文件存储的理想补充方案。
|
11月前
|
数据库 Python
【YashanDB知识库】python驱动查询gbk字符集崖山数据库CLOB字段,数据被驱动截断
【YashanDB知识库】python驱动查询gbk字符集崖山数据库CLOB字段,数据被驱动截断
|
10月前
|
人工智能 关系型数据库 分布式数据库
让数据与AI贴得更近,阿里云瑶池数据库系列产品焕新升级
4月9日阿里云AI势能大会上,阿里云瑶池数据库发布重磅新品及一系列产品能力升级。「推理加速服务」Tair KVCache全新上线,实现KVCache动态分层存储,显著提高内存资源利用率,为大模型推理降本提速。