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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 Tair(兼容Redis),内存型 2GB
Redis 开源版,标准版 2GB
推荐场景:
搭建游戏排行榜
简介: 获取数据库中字段的数据作为下拉框选项

 如题:当我们想把数据库中字段的数据作为对应的下拉框选项,并且数据库字段很多的时候。应该在后端获取这个表里有多少字段,遍历这个表,拿到所有的字段并放到一个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 编辑

非常好用


目录
相关文章
|
3月前
|
存储 人工智能 Cloud Native
云栖重磅|从数据到智能:Data+AI驱动的云原生数据库
在9月20日2024云栖大会上,阿里云智能集团副总裁,数据库产品事业部负责人,ACM、CCF、IEEE会士(Fellow)李飞飞发表《从数据到智能:Data+AI驱动的云原生数据库》主题演讲。他表示,数据是生成式AI的核心资产,大模型时代的数据管理系统需具备多模处理和实时分析能力。阿里云瑶池将数据+AI全面融合,构建一站式多模数据管理平台,以数据驱动决策与创新,为用户提供像“搭积木”一样易用、好用、高可用的使用体验。
云栖重磅|从数据到智能:Data+AI驱动的云原生数据库
|
30天前
|
数据采集 数据库 Python
有哪些方法可以验证用户输入数据的格式是否符合数据库的要求?
有哪些方法可以验证用户输入数据的格式是否符合数据库的要求?
134 75
|
3月前
|
存储 监控 数据处理
flink 向doris 数据库写入数据时出现背压如何排查?
本文介绍了如何确定和解决Flink任务向Doris数据库写入数据时遇到的背压问题。首先通过Flink Web UI和性能指标监控识别背压,然后从Doris数据库性能、网络连接稳定性、Flink任务数据处理逻辑及资源配置等方面排查原因,并通过分析相关日志进一步定位问题。
272 61
|
2月前
|
SQL 存储 运维
从建模到运维:联犀如何完美融入时序数据库 TDengine 实现物联网数据流畅管理
本篇文章是“2024,我想和 TDengine 谈谈”征文活动的三等奖作品。文章从一个具体的业务场景出发,分析了企业在面对海量时序数据时的挑战,并提出了利用 TDengine 高效处理和存储数据的方法,帮助企业解决在数据采集、存储、分析等方面的痛点。通过这篇文章,作者不仅展示了自己对数据处理技术的理解,还进一步阐释了时序数据库在行业中的潜力与应用价值,为读者提供了很多实际的操作思路和技术选型的参考。
52 1
|
2月前
|
存储 Java easyexcel
招行面试:100万级别数据的Excel,如何秒级导入到数据库?
本文由40岁老架构师尼恩撰写,分享了应对招商银行Java后端面试绝命12题的经验。文章详细介绍了如何通过系统化准备,在面试中展示强大的技术实力。针对百万级数据的Excel导入难题,尼恩推荐使用阿里巴巴开源的EasyExcel框架,并结合高性能分片读取、Disruptor队列缓冲和高并发批量写入的架构方案,实现高效的数据处理。此外,文章还提供了完整的代码示例和配置说明,帮助读者快速掌握相关技能。建议读者参考《尼恩Java面试宝典PDF》进行系统化刷题,提升面试竞争力。关注公众号【技术自由圈】可获取更多技术资源和指导。
|
3月前
|
关系型数据库 MySQL 数据库
GBase 数据库如何像MYSQL一样存放多行数据
GBase 数据库如何像MYSQL一样存放多行数据
|
3月前
|
关系型数据库 分布式数据库 数据库
云栖大会|从数据到决策:AI时代数据库如何实现高效数据管理?
在2024云栖大会「海量数据的高效存储与管理」专场,阿里云瑶池讲师团携手AMD、FunPlus、太美医疗科技、中石化、平安科技以及小赢科技、迅雷集团的资深技术专家深入分享了阿里云在OLTP方向的最新技术进展和行业最佳实践。
|
20天前
|
关系型数据库 MySQL 数据库连接
数据库连接工具连接mysql提示:“Host ‘172.23.0.1‘ is not allowed to connect to this MySQL server“
docker-compose部署mysql8服务后,连接时提示不允许连接问题解决
|
6天前
|
关系型数据库 MySQL 数据库
Docker Compose V2 安装常用数据库MySQL+Mongo
以上内容涵盖了使用 Docker Compose 安装和管理 MySQL 和 MongoDB 的详细步骤,希望对您有所帮助。
73 42
|
24天前
|
缓存 关系型数据库 MySQL
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
179 0