1、Utils类
public class PageUtils {
/**
* 分页函数
* @param currentPage 当前页数
* @param pageSize 每一页的数据条数
* @param list 要进行分页的数据列表
* @return 当前页要展示的数据
*/
public static Page getPages(int currentPage, int pageSize, List list){
Page page = new Page();
int size = list.size();
if(pageSize > size) {
pageSize = size;
}
// 求出最大页数，防止currentPage越界
int maxPage = size % pageSize == 0 ? size / pageSize : size / pageSize + 1;
if(currentPage > maxPage) {
currentPage = maxPage;
}
// 当前页第一条数据的下标
int curIdx = currentPage > 1 ? (currentPage - 1) * pageSize : 0;
List pageList = new ArrayList();
// 将当前页的数据放进pageList
for(int i = 0; i < pageSize && curIdx + i < size; i++) {
pageList.add(list.get(curIdx + i));
}
page.setCurrent(currentPage).setSize(pageSize).setTotal(list.size()).setRecords(pageList);
return page;
}
/**
* 分页函数
* @param currentPage 当前页数
* @param pageSize 每一页的数据条数
* @param total 数据总数
* @param list 查询到的数据
* @return 当前页要展示的数据
*/
public static Page getPages(int currentPage, int pageSize,int total, List list){
Page page = new Page();
int size = total;
if(pageSize > size) {
pageSize = size;
}
// 求出最大页数，防止currentPage越界
int maxPage = size % pageSize == 0 ? size / pageSize : size / pageSize + 1;
if(currentPage > maxPage) {
currentPage = maxPage;
}
page.setCurrent(currentPage).setSize(pageSize).setTotal(total).setRecords(list);
return page;
}
}
2、业务层
/**
* ${comments}
* sql查询
*
* @author luoqianqian
* @email *****@mail.com
* @date 2021.3.9 9:27
*/
@Service
public class KsCorpSqlInfoExportServiceImpl implements KsCorpSqlInfoExprotService {
@Autowired
private KsCorpInfoExprotMapper infoExprotMapper;
@Autowired
private KsCorpSqlInfoExprotMapper sqlInfoExprotMapper;
@Autowired
private SysDictDetailMapper dictDetailMapper;
@Override
public Object findListByPage(KsCorpSqlInfoExportEntity sqlInfoExportEntity) {
/*QueryWrapper<KsCorpInfoExprotEntity> wrapper2 = new QueryWrapper<>();
wrapper2.apply(sqlInfoExportEntity.getSql());
List<KsCorpInfoExprotEntity> aa = infoExprotMapper.selectList(wrapper2);*/
//返回给前端的错误提示
String toFront = "查询有误，请重新输入查询";
//判断当前页和每页显示是否为null
if(sqlInfoExportEntity.getPage() == null){
sqlInfoExportEntity.setPage(1);
}
if(sqlInfoExportEntity.getLimit() == null){
sqlInfoExportEntity.setLimit(10);
}
//防止 sql注入风险：
//是否为查询语句
boolean isSelect = sqlInfoExportEntity.getSql().indexOf("s") == 0;
// “;” 是否存在，如果存在，是否在语句的末尾
boolean isExistSemicolon = (sqlInfoExportEntity.getSql().lastIndexOf(";") == (sqlInfoExportEntity.getSql().length() - 1))
|| (!sqlInfoExportEntity.getSql().contains(";"));
if (isSelect && isExistSemicolon){
/*//查询 sys_dict_detail 表的 机构状态
QueryWrapper<SysDictDetailEntity> wrapper = new QueryWrapper<>();
List<String> valueList = new ArrayList<>();*/
//以一个空格或多个空格分割SQL语句字符串
String[] sqlKeywordArray = sqlInfoExportEntity.getSql().split("\\s+");
String tableName = sqlKeywordArray[3];
//获取需要实例化的对象的类名
String entityName = tableName;
for(int i =0 ; i< tableName.length() ; i++) {
String temp = tableName.charAt(i) + "";
if (temp.equals("_")) {
entityName = entityName.substring(0,1).toUpperCase() + entityName.substring(1,i+1) + entityName.substring(i+1,i+2).toUpperCase() + entityName.substring(i+2);
}
}
entityName = entityName.replace("_","");
System.out.println(entityName);
//根据表名查询该表的所有字段的名称
List<String> columns = sqlInfoExprotMapper.selectAllTableColumn(tableName);
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
int count = 0;
//判断是否有数据查询到
Boolean isSelectData = false;
//查询数据的总量 和失去了语句
Integer total = null;
String totalSql = "";
//把实体类的添加到 List
List entityList = new ArrayList<>();
try{
//注册驱动
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
//设置链接地址
/*String url ="jdbc:sqlserver://223.112.127.3:8001;databaseName=farenkuj";
String user = "sa";
String password = "yg@2019";*/
String url ="jdbc:sqlserver://10.36.1.201:1433;databaseName=farenku";
String user = "frk";
String password = "yg@frk@2020";
//连接数据库
conn = DriverManager.getConnection(url,user,password);
//获取语句执行平台，并创建数据库查询
String sql = "";
//判断是否有 where
boolean isHaveWhere = false;
for(int i = 0;i<sqlKeywordArray.length;i++){
sql += sqlKeywordArray[i] + " ";
if (sqlKeywordArray[i].toLowerCase().equals("select")){
sql+=" top " + sqlInfoExportEntity.getLimit() + " ";
}
if (sqlKeywordArray[i].toLowerCase().equals("where")){
sql+=" " + sqlKeywordArray[3] + ".data_id not in (select top " + sqlInfoExportEntity.getLimit()*(sqlInfoExportEntity.getPage()-1)
+ " data_id " + " from " + sqlKeywordArray[3] + ") and ";
isHaveWhere = true;
}
//判断如果是最后一次循环并且没有 where 的话，拼接 where 上去
if (i == sqlKeywordArray.length -1){
if (!isHaveWhere){
sql += " where " + sqlKeywordArray[3] + ".data_id not in (select top " + sqlInfoExportEntity.getLimit()*(sqlInfoExportEntity.getPage()-1)
+ " data_id " + " from " + sqlKeywordArray[3] + ")";
}
}
}
//
totalSql = "select count(*) total from (" + sqlInfoExportEntity.getSql() + ") a;";
ps = conn.prepareStatement(sql);
//执行查询语句
rs = ps.executeQuery();
while (rs.next()){
//total = rs.getInt("total");
if (count==0){
//进入这里则代表查询到了数据
isSelectData = true;
count++;
}
//动态创建实例
Object entity = Class.forName("com.company.project.entity." + entityName + "Entity").newInstance();
Class infoExportEntityClass = entity.getClass();
Field[] fields = infoExportEntityClass.getDeclaredFields();
//利用循环
for (int i = 0;i < fields.length;i++){
//设置私有属性的访问权限
fields[i].setAccessible(true);
//获取字段的名称
String filedName = fields[i].getName();
//在大写字母前添加 “_” 符号
String name = filedName.replaceAll("[A-Z]", "_$0");
name = name.toLowerCase();
//获取字段类型
String type = fields[i].getGenericType().toString();
//判断实体类属性与表的字段名是否一致
for (int j = 0;j < columns.size();j++){
if(name.equals(columns.get(j))){
//再判断字段的类型
switch (type){
case "class java.lang.String":
fields[i].set(entity,rs.getString(name));
break;
case "class java.lang.Integer":
fields[i].set(entity,rs.getInt(name));
break;
case "class java.util.Date":
fields[i].set(entity,rs.getTimestamp(name));
break;
}
}
}
}
//添加到实体类集合
entityList.add(entity);
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
toFront = "不好意思，您输入的SQL语句在" + e.getMessage() +"请重新输入！";
return toFront;
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} finally {
if (null != conn){
try{
conn.close();
conn = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
if (null != ps){
try{
ps.close();
ps = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
if (null != rs){
try{
rs.close();
rs = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
total = sqlInfoExprotMapper.selectCount(totalSql);
//如果没查询到数据，则返回给前端 无数据
if (!isSelectData){
toFront = "无数据";
return toFront;
}
//数据分页设置
Page page = PageUtils.getPages(sqlInfoExportEntity.getPage(),sqlInfoExportEntity.getLimit(),total,entityList);
//批量查询 sys_dict_detail 1349172937746477058
QueryWrapper<SysDictDetailEntity> wrapper = new QueryWrapper<>();
wrapper.eq("dict_id","1349172937746477058");
List<SysDictDetailEntity> dictDetailEntities = dictDetailMapper.selectList(wrapper);
//设置机构状态
for (Object o : page.getRecords()) {
Class entityClass = o.getClass();
Field[] fields = entityClass.getDeclaredFields();
for(int i = 0;i < fields.length;i++){
//设置私有属性的访问权限
fields[i].setAccessible(true);
//获取字段的名称
String filedName = fields[i].getName();
if(filedName.equals("corpStatusName")){
//遍历 sys_dict_detail 表的数据
for (SysDictDetailEntity dictDetailEntity : dictDetailEntities) {
try {
if (fields[i-1].get(o) != null){
if (fields[i-1].get(o).equals(dictDetailEntity.getValue())) {
fields[i].set(o, dictDetailEntity.getLabel());
}
}
} catch (IllegalAccessException e) {
e.printStackTrace();
}
}
}
}
}
return page;
}
return toFront;
}
}