| 提交 | 用户 | age | ||
| 9e1c8d | 1 | package com.hx.other.service.controller; |
| G | 2 | |
| 3 | import com.alibaba.fastjson.JSONObject; | |
| 4 | import com.hx.common.BaseController; | |
| 5 | import com.hx.other.service.api.ai.SqlAiApi; | |
| 6 | import com.hx.other.service.model.BaseEntity; | |
| 7 | import com.hx.other.service.model.QueryRecord; | |
| 8 | import com.hx.other.service.service.QueryRecordService; | |
| 9 | import com.hx.other.service.service.SqlQueryTempService; | |
| 10 | import com.hx.other.service.util.AiQueryUtil; | |
| 11 | import com.hx.other.service.vo.BaseVo; | |
| 12 | import com.hx.other.service.vo.ai.SqlVo; | |
| 13 | import com.hx.resultTool.Result; | |
| 14 | import com.hx.util.StringUtils; | |
| 15 | import org.springframework.jdbc.core.JdbcTemplate; | |
| 16 | import org.springframework.web.bind.annotation.PostMapping; | |
| 17 | import org.springframework.web.bind.annotation.RequestBody; | |
| 18 | import org.springframework.web.bind.annotation.RequestMapping; | |
| 19 | import org.springframework.web.bind.annotation.RestController; | |
| 20 | ||
| 21 | import javax.annotation.Resource; | |
| 22 | import java.util.Calendar; | |
| 23 | import java.util.List; | |
| 24 | import java.util.Map; | |
| 25 | import java.util.Set; | |
| 26 | ||
| 27 | /** | |
| 28 | * ai查询控制器 | |
| 29 | * @Author: cmg | |
| 30 | * @Date: 2023/4/23 10:29 | |
| 31 | */ | |
| 32 | @RestController | |
| 33 | @RequestMapping("/ai") | |
| 34 | public class SqlQueryController extends BaseController { | |
| 35 | ||
| 36 | @Resource | |
| 37 | private SqlQueryTempService sqlQueryTempService; | |
| 38 | ||
| 39 | @Resource | |
| 40 | private JdbcTemplate jdbcTemplate; | |
| 41 | ||
| 42 | @Resource | |
| 43 | private SqlAiApi sqlAiApi; | |
| 44 | ||
| 45 | @Resource | |
| 46 | private QueryRecordService queryRecordService; | |
| 47 | ||
| 48 | @PostMapping("/query") | |
| 49 | public Result query(@RequestBody(required = false) BaseVo baseVo) | |
| 50 | { | |
| 51 | ||
| 6044ac | 52 | if(baseVo == null || StringUtils.isEmpty(baseVo.getKeyWord())) |
| 9e1c8d | 53 | { |
| G | 54 | throwParamException("请输入查询条件"); |
| 55 | } | |
| 56 | ||
| 57 | QueryRecord queryRecord = new QueryRecord(); | |
| 6044ac | 58 | queryRecord.setContent(baseVo.getKeyWord()); |
| 9e1c8d | 59 | |
| G | 60 | //先从模板里匹配 |
| 6044ac | 61 | String sql = sqlQueryTempService.selectByContent(baseVo.getKeyWord()); |
| 9e1c8d | 62 | if(StringUtils.isEmpty(sql)) |
| G | 63 | { |
| 64 | //记录向ai查询时的毫秒数 | |
| 65 | queryRecord.setQueryTime(Calendar.getInstance().getTimeInMillis()); | |
| 66 | ||
| 67 | //模板里没有，从ai里查询 | |
| 68 | JSONObject obj = new JSONObject(); | |
| 6044ac | 69 | obj.put("msg", baseVo.getKeyWord()); |
| 9e1c8d | 70 | obj = AiQueryUtil.aiQuery(sqlAiApi, obj); |
| G | 71 | |
| 72 | //记录ai返回的豪秒数 | |
| 73 | queryRecord.setAiResultTime(Calendar.getInstance().getTimeInMillis()); | |
| 74 | ||
| 75 | if(obj != null && 100 == obj.getIntValue("code")) | |
| 76 | { | |
| 77 | //判断返回是否sql | |
| 78 | if(obj.getBooleanValue("isSql")) | |
| 79 | { | |
| 80 | //是sql | |
| 81 | sql = obj.getString("now"); | |
| 82 | //记录ai返回的结果 | |
| 83 | queryRecord.setAiResult(obj.getString("origin")); | |
| 84 | ||
| 85 | } | |
| 86 | ||
| 87 | //记录ai查询的content | |
| 88 | queryRecord.setAiQueryContent(obj.getString("content")); | |
| 89 | } | |
| 90 | }else{ | |
| 91 | queryRecord.setIsFromQuery(BaseEntity.YES); | |
| 92 | } | |
| 93 | ||
| 94 | List<Map<String, Object>> list = null; | |
| 95 | if(!StringUtils.isEmpty(sql)) | |
| 96 | { | |
| 97 | //记录查询的sql | |
| 98 | queryRecord.setSqlStr(sql); | |
| 99 | ||
| 100 | list = AiQueryUtil.jdbcQuery(jdbcTemplate, sql); | |
| 101 | ||
| 102 | //记录sql查询返回的豪秒数 | |
| 103 | queryRecord.setSqlTime(Calendar.getInstance().getTimeInMillis()); | |
| 104 | } | |
| 105 | ||
| 106 | SqlVo sqlVo = new SqlVo(); | |
| 107 | sqlVo.setOrigin(queryRecord.getAiResult()); | |
| 108 | sqlVo.setQueryContent(queryRecord.getAiQueryContent()); | |
| 109 | sqlVo.setIsArr(1); | |
| 110 | sqlVo.setStatus(StringUtils.isEmpty(sql) ? 0 : 1); | |
| 111 | sqlVo.setMsg(list == null ? "查无信息" : list); | |
| 112 | sqlVo.setAiQueryTime(queryRecord.getQueryTime()); | |
| 113 | sqlVo.setAiResultTime(queryRecord.getAiResultTime()); | |
| 114 | sqlVo.setSqlResultTime(queryRecord.getSqlTime()); | |
| 115 | ||
| 116 | if(list != null) | |
| 117 | { | |
| 118 | //当返回是个统计数值时，直接返回数值 | |
| 119 | if(list.size() == 1) | |
| 120 | { | |
| 121 | Set<String> set = list.get(0).keySet(); | |
| 122 | if(set.size() == 1) { | |
| 123 | for (String key : set) { | |
| 124 | if (key.toUpperCase().indexOf("COUNT") != -1 || key.toUpperCase().indexOf("SUM") != -1) { | |
| 125 | sqlVo.setIsArr(0); | |
| 126 | sqlVo.setMsg(list.get(0).get(key)); | |
| 127 | } | |
| 128 | } | |
| 129 | } | |
| 130 | } | |
| 131 | } | |
| 132 | ||
| 133 | queryRecordService.insert(queryRecord); | |
| 134 | ||
| 135 | return Result.success(sqlVo); | |
| 136 | } | |
| 6044ac | 137 | |
| G | 138 | @PostMapping("/query/test") |
| 139 | public Result queryTest(@RequestBody(required = false) BaseVo baseVo) | |
| 140 | { | |
| 141 | ||
| 142 | if(baseVo == null || StringUtils.isEmpty(baseVo.getKeyWord())) | |
| 143 | { | |
| 144 | throwParamException("请输入查询条件"); | |
| 145 | } | |
| 146 | ||
| 147 | QueryRecord queryRecord = new QueryRecord(); | |
| 148 | queryRecord.setContent(baseVo.getKeyWord()); | |
| 149 | ||
| 150 | //先从模板里匹配 | |
| 151 | String sql = sqlQueryTempService.selectByContent(baseVo.getKeyWord()); | |
| 152 | if(StringUtils.isEmpty(sql)) | |
| 153 | { | |
| 154 | //记录向ai查询时的毫秒数 | |
| 155 | queryRecord.setQueryTime(Calendar.getInstance().getTimeInMillis()); | |
| 156 | ||
| 157 | //模板里没有，从ai里查询 | |
| 158 | JSONObject obj = new JSONObject(); | |
| 159 | obj.put("msg", baseVo.getKeyWord()); | |
| 160 | obj = AiQueryUtil.aiQuery(sqlAiApi, obj); | |
| 161 | ||
| 162 | //记录ai返回的豪秒数 | |
| 163 | queryRecord.setAiResultTime(Calendar.getInstance().getTimeInMillis()); | |
| 164 | ||
| 165 | if(obj != null && 100 == obj.getIntValue("code")) | |
| 166 | { | |
| 167 | //判断返回是否sql | |
| 168 | if(obj.getBooleanValue("isSql")) | |
| 169 | { | |
| 170 | //是sql | |
| 171 | sql = obj.getString("now"); | |
| 172 | //记录ai返回的结果 | |
| 173 | queryRecord.setAiResult(obj.getString("origin")); | |
| 174 | ||
| 175 | } | |
| 176 | ||
| 177 | //记录ai查询的content | |
| 178 | queryRecord.setAiQueryContent(obj.getString("content")); | |
| 179 | } | |
| 180 | }else{ | |
| 181 | queryRecord.setIsFromQuery(BaseEntity.YES); | |
| 182 | } | |
| 183 | ||
| 184 | List<Map<String, Object>> list = null; | |
| 185 | if(!StringUtils.isEmpty(sql)) | |
| 186 | { | |
| 187 | //记录查询的sql | |
| 188 | queryRecord.setSqlStr(sql); | |
| 189 | ||
| 190 | list = AiQueryUtil.jdbcQuery(jdbcTemplate, sql); | |
| 191 | ||
| 192 | //记录sql查询返回的豪秒数 | |
| 193 | queryRecord.setSqlTime(Calendar.getInstance().getTimeInMillis()); | |
| 194 | } | |
| 195 | ||
| 196 | SqlVo sqlVo = new SqlVo(); | |
| 197 | sqlVo.setIsArr(1); | |
| 198 | sqlVo.setStatus(StringUtils.isEmpty(sql) ? 0 : 1); | |
| 199 | sqlVo.setMsg(list == null ? "查无信息" : list); | |
| 200 | ||
| 201 | if(list != null) | |
| 202 | { | |
| 203 | //当返回是个统计数值时，直接返回数值 | |
| 204 | if(list.size() == 1) | |
| 205 | { | |
| 206 | Set<String> set = list.get(0).keySet(); | |
| 207 | if(set.size() == 1) { | |
| 208 | for (String key : set) { | |
| 209 | if (key.toUpperCase().indexOf("COUNT") != -1 || key.toUpperCase().indexOf("SUM") != -1) { | |
| 210 | sqlVo.setIsArr(0); | |
| 211 | sqlVo.setMsg(list.get(0).get(key)); | |
| 212 | } | |
| 213 | } | |
| 214 | } | |
| 215 | } | |
| 216 | } | |
| 217 | ||
| 218 | queryRecordService.insert(queryRecord); | |
| 219 | ||
| 220 | return Result.success(sqlVo); | |
| 221 | } | |
| 9e1c8d | 222 | } |
