查看 | 历史 | 提交 | 提交对比
guang
2023-04-23 9e1c8d19bbca288b74464e90b75c9c1fe710ec1e
提交 | 用户 | 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
52         if(baseVo == null || StringUtils.isEmpty(baseVo.getContent()))
53         {
54             throwParamException("请输入查询条件");
55         }
56
57         QueryRecord queryRecord = new QueryRecord();
58         queryRecord.setContent(baseVo.getContent());
59
60         //先从模板里匹配
61         String sql = sqlQueryTempService.selectByContent(baseVo.getContent());
62         if(StringUtils.isEmpty(sql))
63         {
64             //记录向ai查询时的毫秒数
65             queryRecord.setQueryTime(Calendar.getInstance().getTimeInMillis());
66
67             //模板里没有，从ai里查询
68             JSONObject obj = new JSONObject();
69             obj.put("msg", baseVo.getContent());
70             obj = AiQueryUtil.aiQuery(sqlAiApi, obj);
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     }
137 }