如题:当我们想把数据库中字段的数据作为对应的下拉框选项,并且数据库字段很多的时候。应该在后端获取这个表里有多少字段,遍历这个表,拿到所有的字段并放到一个map集合里,我们看看代码。
编辑
编辑
这个表里有大概四五十个字段,我们不能每一个字段都写一个对应获取的方法,所以我们在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); }
连接到数据库后,遍历字段个数并且插入到map集合里。
前端:
页面展示:
编辑
代码:
js:
export function getDropdownItems() { return request({ url: '/mixTableAdd/getDropdownItems', method: 'post' }) }
选择框:
<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>
获取方法:
getDropdownItems(){ getDropdownItems().then(response => { console.log(response.r_nl,'asdsad') this.selectSource = response.data }) }
同时要定义一个selectSource数据来接受返回的字段。
返回的字段在postman中的样子:
编辑
非常好用