package com.hx.other.service.controller;
|
|
import com.alibaba.fastjson.JSONObject;
|
import com.hx.common.BaseController;
|
import com.hx.other.service.api.ai.SqlAiApi;
|
import com.hx.other.service.model.BaseEntity;
|
import com.hx.other.service.model.QueryRecord;
|
import com.hx.other.service.service.QueryRecordService;
|
import com.hx.other.service.service.SqlQueryTempService;
|
import com.hx.other.service.util.AiQueryUtil;
|
import com.hx.other.service.vo.BaseVo;
|
import com.hx.other.service.vo.ai.SqlVo;
|
import com.hx.resultTool.Result;
|
import com.hx.util.StringUtils;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.beans.factory.annotation.Qualifier;
|
import org.springframework.jdbc.core.JdbcTemplate;
|
import org.springframework.web.bind.annotation.PostMapping;
|
import org.springframework.web.bind.annotation.RequestBody;
|
import org.springframework.web.bind.annotation.RequestMapping;
|
import org.springframework.web.bind.annotation.RestController;
|
|
import javax.annotation.Resource;
|
import java.util.Calendar;
|
import java.util.List;
|
import java.util.Map;
|
import java.util.Set;
|
|
/**
|
* ai查询控制器
|
* @Author: cmg
|
* @Date: 2023/4/23 10:29
|
*/
|
@RestController
|
@RequestMapping("/ai")
|
public class SqlQueryController extends BaseController {
|
|
@Resource
|
private SqlQueryTempService sqlQueryTempService;
|
|
@Autowired
|
@Qualifier("readJdbcTemplate")
|
private JdbcTemplate jdbcTemplate;
|
|
@Resource
|
private SqlAiApi sqlAiApi;
|
|
@Resource
|
private QueryRecordService queryRecordService;
|
|
@PostMapping("/query")
|
public Result query(@RequestBody(required = false) BaseVo baseVo)
|
{
|
|
if(baseVo == null || StringUtils.isEmpty(baseVo.getKeyWord()))
|
{
|
throwParamException("请输入查询条件");
|
}
|
|
QueryRecord queryRecord = new QueryRecord();
|
queryRecord.setContent(baseVo.getKeyWord());
|
|
//先从模板里匹配
|
String sql = sqlQueryTempService.selectByContent(baseVo.getKeyWord());
|
if(StringUtils.isEmpty(sql))
|
{
|
//记录向ai查询时的毫秒数
|
queryRecord.setQueryTime(Calendar.getInstance().getTimeInMillis());
|
|
//模板里没有，从ai里查询
|
JSONObject obj = new JSONObject();
|
obj.put("msg", baseVo.getKeyWord());
|
obj = AiQueryUtil.aiQuery(sqlAiApi, obj);
|
|
//记录ai返回的豪秒数
|
queryRecord.setAiResultTime(Calendar.getInstance().getTimeInMillis());
|
|
if(obj != null && 100 == obj.getIntValue("code"))
|
{
|
//判断返回是否sql
|
if(obj.getBooleanValue("isSql"))
|
{
|
//是sql
|
sql = obj.getString("now");
|
//记录ai返回的结果
|
queryRecord.setAiResult(obj.getString("origin"));
|
|
}
|
|
//记录ai查询的content
|
queryRecord.setAiQueryContent(obj.getString("content"));
|
}
|
}else{
|
queryRecord.setIsFromQuery(BaseEntity.YES);
|
}
|
|
List<Map<String, Object>> list = null;
|
if(!StringUtils.isEmpty(sql))
|
{
|
//记录查询的sql
|
queryRecord.setSqlStr(sql);
|
|
list = AiQueryUtil.jdbcQuery(jdbcTemplate, sql);
|
|
//记录sql查询返回的豪秒数
|
queryRecord.setSqlTime(Calendar.getInstance().getTimeInMillis());
|
}
|
|
SqlVo sqlVo = new SqlVo();
|
sqlVo.setNow(sql);
|
sqlVo.setOrigin(queryRecord.getAiResult());
|
sqlVo.setQueryContent(queryRecord.getAiQueryContent());
|
sqlVo.setIsArr(1);
|
sqlVo.setStatus(StringUtils.isEmpty(sql) ? 0 : 1);
|
sqlVo.setMsg(list == null ? "查无信息" : list);
|
sqlVo.setAiQueryTime(queryRecord.getQueryTime());
|
sqlVo.setAiResultTime(queryRecord.getAiResultTime());
|
sqlVo.setSqlResultTime(queryRecord.getSqlTime());
|
|
if(list != null)
|
{
|
//当返回是个统计数值时，直接返回数值
|
if(list.size() == 1)
|
{
|
Set<String> set = list.get(0).keySet();
|
if(set.size() == 1) {
|
for (String key : set) {
|
if (key.toUpperCase().indexOf("COUNT") != -1 || key.toUpperCase().indexOf("SUM") != -1) {
|
sqlVo.setIsArr(0);
|
sqlVo.setMsg(list.get(0).get(key));
|
}
|
}
|
}
|
}
|
}
|
|
queryRecordService.insert(queryRecord);
|
|
return Result.success(sqlVo);
|
}
|
|
@PostMapping("/query/test")
|
public Result queryTest(@RequestBody(required = false) BaseVo baseVo)
|
{
|
|
if(baseVo == null || StringUtils.isEmpty(baseVo.getKeyWord()))
|
{
|
throwParamException("请输入查询条件");
|
}
|
|
QueryRecord queryRecord = new QueryRecord();
|
queryRecord.setContent(baseVo.getKeyWord());
|
|
//先从模板里匹配
|
String sql = sqlQueryTempService.selectByContent(baseVo.getKeyWord());
|
if(StringUtils.isEmpty(sql))
|
{
|
//记录向ai查询时的毫秒数
|
queryRecord.setQueryTime(Calendar.getInstance().getTimeInMillis());
|
|
//模板里没有，从ai里查询
|
JSONObject obj = new JSONObject();
|
obj.put("msg", baseVo.getKeyWord());
|
obj = AiQueryUtil.aiQuery(sqlAiApi, obj);
|
|
//记录ai返回的豪秒数
|
queryRecord.setAiResultTime(Calendar.getInstance().getTimeInMillis());
|
|
if(obj != null && 100 == obj.getIntValue("code"))
|
{
|
//判断返回是否sql
|
if(obj.getBooleanValue("isSql"))
|
{
|
//是sql
|
sql = obj.getString("now");
|
//记录ai返回的结果
|
queryRecord.setAiResult(obj.getString("origin"));
|
|
}
|
|
//记录ai查询的content
|
queryRecord.setAiQueryContent(obj.getString("content"));
|
}
|
}else{
|
queryRecord.setIsFromQuery(BaseEntity.YES);
|
}
|
|
List<Map<String, Object>> list = null;
|
if(!StringUtils.isEmpty(sql))
|
{
|
//记录查询的sql
|
queryRecord.setSqlStr(sql);
|
|
list = AiQueryUtil.jdbcQuery(jdbcTemplate, sql);
|
|
//记录sql查询返回的豪秒数
|
queryRecord.setSqlTime(Calendar.getInstance().getTimeInMillis());
|
}
|
|
SqlVo sqlVo = new SqlVo();
|
sqlVo.setIsArr(1);
|
sqlVo.setStatus(StringUtils.isEmpty(sql) ? 0 : 1);
|
sqlVo.setMsg(list == null ? "查无信息" : list);
|
|
if(list != null)
|
{
|
//当返回是个统计数值时，直接返回数值
|
if(list.size() == 1)
|
{
|
Set<String> set = list.get(0).keySet();
|
if(set.size() == 1) {
|
for (String key : set) {
|
if (key.toUpperCase().indexOf("COUNT") != -1 || key.toUpperCase().indexOf("SUM") != -1) {
|
sqlVo.setIsArr(0);
|
sqlVo.setMsg(list.get(0).get(key));
|
}
|
}
|
}
|
}
|
}
|
|
queryRecordService.insert(queryRecord);
|
|
return Result.success(sqlVo);
|
}
|
}
|
