前提
由于业务需要，前端传递已经配置好的sql，所以我需要实现一个通用的动态查询方案
实现
controller
java//contorller
@ApiOperation(value = "执行指定sql的脚本")
@PostMapping("getSqlScriptExcuteResult")
public Result<Object> getSqlScriptExcuteResult(@RequestBody @Validated RecSearchSolutionSqlScriptExcuteResultRequest request) {
List<Map<String, Object>> map = iRecSearchSolutionService.getSqlScriptExcuteResult(request);
return Result.success(map);
}
service层
java//Service
List<Map<String, Object>> getSqlScriptExcuteResult(RecSearchSolutionSqlScriptExcuteResultRequest request);
impl层
java//impl
@Override
public List<Map<String, Object>> getSqlScriptExcuteResult(RecSearchSolutionSqlScriptExcuteResultRequest request) {
if (request.getPage() != 0 && request.getRows() != 0) {
Integer begin= (request.getPage()-1)*request.getRows();
Integer end=request.getPage()*request.getRows();
String newSql = MessageFormat.format("SELECT *\n" +
" FROM (SELECT TMP.*, ROWNUM ROW_ID\n" +
" FROM (" +
"{0}" +
") TMP\n" +
" WHERE ROWNUM <= {1})\n" +
" WHERE ROW_ID > {2}\n", request.getSql(),end,begin);
request.setSql(newSql);
}
logger.info(request.getSql());
List<Map<String, Object>> map = commonService.queryForMapList(request.getSql());
for (Map<String,Object> m: map) {
for (Map.Entry<String, Object> set:m.entrySet()) {
if (set.getValue() instanceof Date){
set.setValue(DateUtil.format((Date)set.getValue(),"yyyy-MM-dd HH:mm:ss"));
}
}
}
return map;
}
commonService
javapackage com.lhw.service;
import com.lhw.mapper.SqlHelperMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.Date;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
@Service
public class CommonService {
@Resource
private JdbcTemplate jdbcTemplate;
@Resource
private SqlHelperMapper sqlHelperMapper;
public String queryForString(String sql) {
List<String> list = jdbcTemplate.queryForList(sql, String.class);
if (list.size() == 0) {
return "";
} else {
return list.get(0);
}
}
public List<String> queryForStringArray(String sql) {
List<String> list = jdbcTemplate.queryForList(sql, String.class);
return list;
}
public List<Date> queryForDateArray(String sql) {
List<Date> list = jdbcTemplate.queryForList(sql, Date.class);
return list;
}
public Integer queryForInt(String sql) {
List<Integer> list = jdbcTemplate.queryForList(sql, Integer.class);
if (list.size() == 0) {
return 0;
} else {
return list.get(0);
}
}
public Date queryForDate(String sql) {
List<Date> list = jdbcTemplate.queryForList(sql, Date.class);
if (list.size() == 0) {
return new Date();
} else {
return list.get(0);
}
}
public <T> List<T> queryForList(String sql,Class<T> elementType ){
List<T> list= jdbcTemplate.queryForList(sql, elementType);
return list;
}
public <T> List<T> query(String sql,RowMapper<T> rowMapper){
return jdbcTemplate.query(sql,rowMapper);
}
public List<LinkedHashMap<String, Object>> superSelect(String sql) {
return sqlHelperMapper.superSelect(sql);
}
public Integer superSelectForInt(String sql) {
return sqlHelperMapper.superSelectForInt(sql);
}
public List<Map<String, Object>> queryForMapList(String sql) {
return jdbcTemplate.queryForList(sql);
}
}
可能出现的错误
使用queryForList,会提示Incorrect column count: expected 1, actual 2，因为只支持返回一个list 所以我们就需要在commonService里调整，直接使用query
java public List<SolutionConditionDictResponse> queryForDictList(String sql) {
return jdbcTemplate.query(sql,BeanPropertyRowMapper.newInstance(SolutionConditionDictResponse.class));
}
前提
由于业务要求需要根据list里的时间排序来再次进行排序
实现
在Java中，你可以使用Collections.sort()方法对List中的元素进行排序。如果要按照时间排序，你需要确保每个元素都包含一个对应的时间戳或日期对象，并使用Comparator接口定义的比较方法来比较元素的时间戳或日期。
java//升序
Collections.sort(emrDocVEntityList, new Comparator<EmrDocVRes>() {
@Override
public int compare(EmrDocVRes o1, EmrDocVRes o2) {
return o1.getDocTime().compareTo(o2.getDocTime());
}
});
前提
这是因为mybatis默认开启驼峰命名法，按规则数据表中的lastName字段应对应实体类中的last_name属性，而实体类中的lastName属性应对应数据表中的last_name字段。
解决方式1
在application.yml中，关闭驼峰转换,可能会出现问题，影响以前的实体查不到内容
yml
mybatis-plus.configuration.map-underscore-to-camel-case=false
解决方式2
局部解决，写个将驼峰转回下划线命名方式的接口，优点不影响全局配置
java
public String camelToUnderline(String camelCase) {
StringBuilder sb = new StringBuilder();
for (int i = 0; i < camelCase.length(); i++) {
char c = camelCase.charAt(i);
if (Character.isUpperCase(c)) {
if (i != 0) {
sb.append("_");
}
sb.append(Character.toLowerCase(c));
} else {
sb.append(c);
}
}
if (sb.charAt(sb.length() - 1) == '_') {
sb.deleteCharAt(sb.length() - 1);
}
return sb.toString().toUpperCase();
}
原因
问题描述：
报错：Error: error:0308010C
envelope routines::unsupported报错原因：
因为 node.js V17版本中最近发布的OpenSSL3.0, 而OpenSSL3.0对允许算法和密钥大小增加了严格的限制
解决方法
- 卸载该nodejs，安装nodejs17以下版本
- idea终端输入
set NODE_OPTIONS=--openssl-legacy-provider
前提
由于业务需要返回一个树形结构，并且实现搜索功能，搜索子类时要显示父类信息
代码实现
请求
java
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
@Data
@ApiModel(value = "获取查询条件请求")
public class SearchConditionRequest {
@ApiModelProperty(value = "查询条件名称")
private String text;
}
实现
java
@ApiOperation(value = "获取查询条件树形列表")
@PostMapping("getSearchConditionListTree")
public Result<List<SearchConditionEntity>> getRecSearchConditionListTree(@RequestBody SearchConditionRequest request) {
List<SearchConditionEntity> list = iSearchConditionService.selectSearchConditionList(request);
List<SearchConditionEntity> SearchConditionEntityListTree = new ArrayList<>();
//第一步获取顶层节点
List<RecSearchConditionEntity> top = list.stream()
.filter(pa -> "0".equals(pa.getParentId()) || pa.getParentId()==null).collect(Collectors.toList());
if (top != null && top.size() > 0) {
top.forEach(p -> {
searchConditionEntity searchConditionEntity = p;
this.getChildrenList(list, searchConditionEntity);
searchConditionEntityListTree.add(SearchConditionEntity);
});
return Result.success(SearchConditionEntityListTree);
}
return Result.fail("500", "没有结果");
}
private void getChildrenList(List<SearchConditionEntity> list, SearchConditionEntity parent) {
List<searchConditionEntity> children = list.stream()
.filter(pa -> parent.getId().equals(pa.getParentId())).collect(Collectors.toList());
if (children != null && children.size() > 0) {
children.forEach(p -> {
this.getChildrenList(list, p);
});
parent.setChildren(children);
}
return;
}
使用递归的方式，给树形添加上下级
