代码展示
Controller
@ApiOperation(value = "查询机构列表",notes = "查询机构列表")
@RequestMapping(value = "/getList",method = RequestMethod.POST)
public Map<String,Object> getSpeciqlistInfo(@RequestBody TrainingHospital trainingHospital){
return speciqlistLeagueSerivce.getSpeciqlistInfo(trainingHospital);
}
Service接口
public interface ISpeciqlistLeagueSerivce {
/**
* 获取机构列表
* @return
*/
Map<String,Object> getSpeciqlistInfo(TrainingHospital trainingHospital);
}
Service实现类
@Service//实现类加
public class SpeciqlistLeagueServiceImpl extends BaseService<Object> implements ISpeciqlistLeagueSerivce {
private Logger logger = LoggerFactory.getLogger(this.getClass());
/**
* 查询医院实现
*/
@Override
public Map<String,Object> getSpeciqlistInfo(TrainingHospital trainingHospital) {
Page<?> pages = null;
try {
StringBuilder sql = new StringBuilder();
sql.append("select t.*,count(u.id) - 1 nums from training_hospital t" +
" left JOIN platform_user u on t.id = u.trainingId " +
// " left join users u2 on t.id = u2.trainingId" +
" where 1=1 and t.hosType = 1 and t.isDel = 0 ");
if(trainingHospital.getTrainingType() != null){
sql.append(" and trainingType = ").append(trainingHospital.getTrainingType());
}
if(StringUtil.isNotBlank(trainingHospital.getHospitalName())){
sql.append(" and t.hospitalName like '%"+trainingHospital.getHospitalName()+"%' ");
}
if(StringUtil.isNotBlank(trainingHospital.getHospitalNo())){
sql.append(" and t.hospitalNo = "+trainingHospital.getHospitalNo());
}
sql.append(" group by t.id");
if(trainingHospital.getQueryType() == 0){
return ReturnUtils.returnOkMsg(jdbcTemplate.queryForList(sql.toString()));
}else{
pages = queryMapPage(sql.toString(), trainingHospital.getPageNumber(), trainingHospital.getPageSize());
return ReturnUtils.returnOkMsg(pages);
}
}catch (BusinessException e){
return ReturnUtils.returnFailMsg(e.getMessage());
}catch (Exception e){
logger.info(e.getMessage(),e);
return ReturnUtils.FAIL();
}
}
}
Service基类
/***
* JDBC 基类接口
* @author mxk
*
*/
@Component
public class BaseService<T>{
@Autowired
public JdbcTemplate jdbcTemplate;
/**
* 查询某一页数据
* @param sql 帶參數的SQL 必須包含排序
* @param PageNum
* @param pageSize
* @return
*/
public Page<?> queryMapPage2(String sql,int PageNum,int pageSize,Object... params) {
// TODO Auto-generated method stub
// TODO Auto-generated method stub
PageNum=(PageNum-1)*pageSize;
List<Map<String,Object>> objs = new ArrayList<>();
Page<?> page = null;
try {
page = getPageInfo(sql, pageSize,params);
if (page.getTotal() > 0) {
sql += " LIMIT "+PageNum+","+pageSize;
objs = queryForList(sql,params);
}
page.setMaps(objs);
} catch (Exception e) {
e.printStackTrace();
}
return page;
}
/***
* 翻页信息
* @param sql
* @param pageSize
* @return
*/
private Page<T> getPageInfo(String sql, int pageSize,Object... params) throws Exception {
Page<T> page = new Page<T>();
try {
int count = selectCount("select count(1) as count from (" + sql +") t",params);
if (count > 0) {
page.setTotal(count);
page.setSize(pageSize);
Double p = MathUtil.divide(count, pageSize);
int pages = 0;
if (p < 1 && p > 0) {
pages = 1;
} else {
//向上取整
pages = (int) Math.ceil(p);
}
page.setPages(pages);
}
} catch (Exception e) {
throw e;
}
return page;
}
/***
* 查询数据条目
* @param sql
* @return
*/
public int selectCount(String sql,Object... params) throws Exception {
// TODO Auto-generated method stub
int count = 0;
SqlRowSet set;
try {
set = jdbcTemplate.queryForRowSet(sql,params);
if (set.next()) {
count = set.getInt("count");
}
} catch (Exception e) {
e.printStackTrace();
}
return count;
}
/**
* 将查询字段映射到实体类
* @param sql 查询语句
* @param params 传入参数
* @return
*/
public List<Map<String,Object>> queryForList(String sql, Object... params) {
final List<Map<String,Object>> result = new ArrayList<>();
jdbcTemplate.query(sql, params, rs -> {
try {
// 字段名称
List<String> columnNames = new ArrayList<>();
ResultSetMetaData meta = rs.getMetaData();
int num = meta.getColumnCount();
for (int i = 0; i < num; i++) {
columnNames.add(meta.getColumnLabel(i + 1));
}
// 设置值
do {
Map<String,Object> resultMap = new HashMap<>();
for (int i = 0; i < num; i++) {
// 获取值
Object value = rs.getObject(i + 1);
// table.column形式的字段去掉前缀table.
String columnName = resolveColumn(columnNames.get(i));
// 下划线转驼峰
String property = CamelCaseUtils.toCamelCase(columnName);
// 复制值到属性，这是spring的工具类
resultMap.put(property,value);
}
result.add(resultMap);
} while (rs.next());
} catch (Exception e) {
throw new QueryException(e);
}
});
if (result.isEmpty()) {
return Collections.emptyList();
}
return result;
}
/**
* 用来去掉字段的表前缀，比如t.id替换成id
* @param column 查询的带有前缀的返回字段
* @return 不带前缀的字段
*/
private String resolveColumn(String column) {
final int notExistIndex = -1;
int index = column.indexOf(".");
if (index == notExistIndex) {
return column;
}
return column.substring(index + 1);
}
}
情况一：使用参数拼接传参
参数展示
{
"hospitalNo": "15139411539 or 1=1",
"pageNumber": 1,
"pageSize": 10
}
返回结果
{
"data": {
"records": [],
"maps": [
{
"id": 3,
"hospitalName": "东华社区",
"hospitalNo": "dhsq",
"hosType": 1,
"trainingType": 1,
"address": "222",
"tel": "123",
"level": "1",
"hosDesc": null,
"initTime": "2021-11-04T16:00:00.000Z",
"isDel": 1,
"hospitalOneLevelNo": "01",
"hospitalTwoLevelNo": null,
"isShow": 0,
"miniImage": null,
"lon": null,
"lat": null,
"contacts": null,
"sysUuid": "f400d7efb48b334ee2d77b1ef448d15a",
"province": null,
"city": null,
"district": null,
"message": null,
"title": null,
"nums": -1
},
{
"id": 4,
"hospitalName": "测试",
"hospitalNo": "00002",
"hosType": 1,
"trainingType": 1,
"address": "",
"tel": "13120898293",
"level": "",
"hosDesc": null,
"initTime": "",
"isDel": 1,
"hospitalOneLevelNo": "01",
"hospitalTwoLevelNo": null,
"isShow": 0,
"miniImage": null,
"lon": null,
"lat": null,
"contacts": null,
"sysUuid": "db7282901da891bea2fcd73840caad1c",
"province": null,
"city": null,
"district": null,
"message": null,
"title": null,
"nums": -1
},
{
"id": 5,
"hospitalName": "测试测试",
"hospitalNo": "00003",
"hosType": 1,
"trainingType": 1,
"address": "",
"tel": "13569508142",
"level": "1",
"hosDesc": null,
"initTime": "2021-11-09T16:00:00.000Z",
"isDel": 1,
"hospitalOneLevelNo": "01",
"hospitalTwoLevelNo": null,
"isShow": 0,
"miniImage": null,
"lon": null,
"lat": null,
"contacts": null,
"sysUuid": "e08599ba76fe6afa2b5786d857b0f56e",
"province": null,
"city": null,
"district": null,
"message": null,
"title": null,
"nums": -1
},
{
"id": 7,
"hospitalName": "测试机构1",
"hospitalNo": "10000",
"hosType": 1,
"trainingType": 1,
"address": "打发点",
"tel": "15890997318",
"level": "",
"hosDesc": null,
"initTime": "2021-10-31T16:00:00.000Z",
"isDel": 1,
"hospitalOneLevelNo": "01",
"hospitalTwoLevelNo": null,
"isShow": 0,
"miniImage": null,
"lon": null,
"lat": null,
"contacts": null,
"sysUuid": "b7a782741f667201b54880c925faec4b",
"province": null,
"city": null,
"district": null,
"message": null,
"title": null,
"nums": -1
},
{
"id": 8,
"hospitalName": "test",
"hospitalNo": "0000",
"hosType": 1,
"trainingType": 1,
"address": "",
"tel": "13120898293",
"level": "",
"hosDesc": null,
"initTime": "",
"isDel": 1,
"hospitalOneLevelNo": "01",
"hospitalTwoLevelNo": "12411700573582781E",
"isShow": 0,
"miniImage": null,
"lon": null,
"lat": null,
"contacts": null,
"sysUuid": "4a7d1ed414474e4033ac29ccb8653d9b",
"province": null,
"city": null,
"district": null,
"message": null,
"title": null,
"nums": -1
},
{
"id": 9,
"hospitalName": "人和社区卫生服务中心",
"hospitalNo": "15139411539",
"hosType": 1,
"trainingType": 1,
"address": "河南省周口市川汇区小桥街道汇聚路周口市第三人民医院",
"tel": "15139411539",
"level": "",
"hosDesc": null,
"initTime": "2021-11-07T16:00:00.000Z",
"isDel": 0,
"hospitalOneLevelNo": "01",
"hospitalTwoLevelNo": null,
"isShow": 0,
"miniImage": "http://qrcode-1308122820.cos.ap-chengdu.myqcloud.com/1640911660156-9.png",
"lon": "114.649887",
"lat": "33.650224",
"contacts": null,
"sysUuid": "dc33c3ab197bbaf6c410a271cc0113aa",
"province": "河南省",
"city": "周口市",
"district": "川汇区",
"message": null,
"title": null,
"nums": 0
},
{
"id": 10,
"hospitalName": "金海社区卫生服务中心",
"hospitalNo": "13592276989",
"hosType": 1,
"trainingType": 1,
"address": "河南省周口市川汇区建设大道文博苑东南侧约80米",
"tel": "13592276989",
"level": "",
"hosDesc": null,
"initTime": "2021-11-07T16:00:00.000Z",
"isDel": 0,
"hospitalOneLevelNo": "01",
"hospitalTwoLevelNo": null,
"isShow": 0,
"miniImage": "http://qrcode-1308122820.cos.ap-chengdu.myqcloud.com/1642240171086-10.png",
"lon": "114.63214",
"lat": "33.63708",
"contacts": null,
"sysUuid": "d4d37975a874ccb96450472cf594c275",
"province": "河南省",
"city": "周口市",
"district": "川汇区",
"message": "query ok",
"title": "金海办事处社区卫生中心",
"nums": 0
},
{
"id": 11,
"hospitalName": "陈州社区卫生服务中心",
"hospitalNo": "13703947929",
"hosType": 1,
"trainingType": 1,
"address": "河南省周口市川汇区人民路与中州大道交叉口东北",
"tel": "13703947929",
"level": "",
"hosDesc": null,
"initTime": "2021-11-07T16:00:00.000Z",
"isDel": 0,
"hospitalOneLevelNo": "01",
"hospitalTwoLevelNo": null,
"isShow": 0,
"miniImage": null,
"lon": "114.645461",
"lat": "33.621311",
"contacts": null,
"sysUuid": "d0185c531fb3c654636b44925376f98b",
"province": "河南省",
"city": "周口市",
"district": "川汇区",
"message": "query ok",
"title": "陈州社区卫生服务中心",
"nums": 0
},
{
"id": 12,
"hospitalName": "川汇区妇幼保健院",
"hospitalNo": "13033945353",
"hosType": 1,
"trainingType": 1,
"address": "河南省周口市川汇区中州大道",
"tel": "13033945353",
"level": "",
"hosDesc": null,
"initTime": "2021-11-07T16:00:00.000Z",
"isDel": 0,
"hospitalOneLevelNo": "01",
"hospitalTwoLevelNo": null,
"isShow": 0,
"miniImage": null,
"lon": "114.643442",
"lat": "33.624322",
"contacts": null,
"sysUuid": "479aea1593a8d392f1edfbd825616e85",
"province": "河南省",
"city": "周口市",
"district": "川汇区",
"message": "query ok",
"title": "周口市川汇区妇幼保健院",
"nums": 0
},
{
"id": 13,
"hospitalName": "小乔社区卫生服务中心",
"hospitalNo": "13592265853",
"hosType": 1,
"trainingType": 1,
"address": "河南省周口市川汇区八一大道希望小区兴业路西南侧约280米",
"tel": "13592265853",
"level": "",
"hosDesc": null,
"initTime": "2021-11-07T16:00:00.000Z",
"isDel": 0,
"hospitalOneLevelNo": "01",
"hospitalTwoLevelNo": null,
"isShow": 0,
"miniImage": null,
"lon": "114.656622",
"lat": "33.640067",
"contacts": null,
"sysUuid": "2cbab6389e1607371921af8f6347b69d",
"province": "河南省",
"city": "周口市",
"district": "川汇区",
"message": "query ok",
"title": "小桥社区卫生服务中心",
"nums": 0
}
],
"recordArray": null,
"statis": null,
"total": 547,
"pages": 55,
"size": 10,
"current": 1,
"msg": null,
"http": null,
"ok": false
},
"retCode": "0",
"timestamp": 1658641836675
}
情况二：使用可变数组传参
修改Service实现类
@Service//实现类加
public class SpeciqlistLeagueServiceImpl extends BaseService<Object> implements ISpeciqlistLeagueSerivce {
private Logger logger = LoggerFactory.getLogger(this.getClass());
/**
* 查询医院实现
*/
@Override
public Map<String,Object> getSpeciqlistInfo(TrainingHospital trainingHospital) {
Page<?> pages = null;
try {
StringBuilder sql = new StringBuilder();
sql.append("select t.*,count(u.id) - 1 nums from training_hospital t" +
" left JOIN platform_user u on t.id = u.trainingId " +
// " left join users u2 on t.id = u2.trainingId" +
" where 1=1 and t.hosType = 1 and t.isDel = 0 ");
List<Object> list = new ArrayList<>();
if(trainingHospital.getTrainingType() != null){
// sql.append(" and trainingType = ").append(trainingHospital.getTrainingType());
sql.append(" and trainingType = ").append("?");
list.add(trainingHospital.getHosType());
}
if(StringUtil.isNotBlank(trainingHospital.getHospitalName())){
// sql.append(" and t.hospitalName like '%"+trainingHospital.getHospitalName()+"%' ");
sql.append(" and t.hospitalName like ? ");
list.add("%"+trainingHospital.getHospitalName()+"%");
}
if(StringUtil.isNotBlank(trainingHospital.getHospitalNo())){
// sql.append(" and t.hospitalNo = "+trainingHospital.getHospitalNo()+" ");
sql.append(" and t.hospitalNo = ? ");
list.add(trainingHospital.getHospitalNo());
}
sql.append(" group by t.id");
if(trainingHospital.getQueryType() == 0){
return ReturnUtils.returnOkMsg(jdbcTemplate.queryForList(sql.toString()));
}else{
pages = queryMapPage2(sql.toString(), trainingHospital.getPageNumber(), trainingHospital.getPageSize(),list.toArray());
return ReturnUtils.returnOkMsg(pages);
}
}catch (BusinessException e){
return ReturnUtils.returnFailMsg(e.getMessage());
}catch (Exception e){
logger.info(e.getMessage(),e);
return ReturnUtils.FAIL();
}
}
}
参数展示
{
"hospitalNo": "15139411539 or 1=1",
"pageNumber": 1,
"pageSize": 10
}
返回结果
{
"data": {
"records": [],
"maps": [],
"recordArray": null,
"statis": null,
"total": 0,
"pages": 0,
"size": 10,
"current": 1,
"msg": null,
"http": null,
"ok": false
},
"retCode": "0",
"timestamp": 1658640578006
}
总结
使用可变数组传参可以防止sql注入，使用拼接传参不会防止sql注入！
原因分析详见：https://blog.csdn.net/Acx77/article/details/121856595
