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