背景
公司项目是老项目升级,非常老的项目,当时还没有mybatis框架,手写连接池与连接器。所有的sql语句都写在代码中,传进封装好的查询工具中,得到结果集。
现在老项目升级,需要采用mybatis框架,三层架构模式。但是一个接口中涉及几百个sql,全部都用三层架构的话,有心无力。所以写了一个工具类,用mybatis框架实现传入在代码中传入任意sql,得到查询结果。
工具类:
/**
* @Author: jiajunyu
* @CreateTime: 2022-09-15 14:08
* @Description:
*/
@Slf4j
public class SqlUtils {
private static CommonDao commonDao = ApplicationContextHelper.getBeanByClass(CommonDao.class);
/**
* @description: 传入查询sql、实体类对象的class,返回list实体类集合
**/
public static <T> List<T> getresult(String strSQL, Class<T> pojo) {
//非空校验
if (StringUtils.isBlank(strSQL) || pojo == null) {
return null;
}
//创建pojolist
List<T> result = new ArrayList<>();
//获得pojo所有字段名
Field[] fields = pojo.getDeclaredFields();
//查询数据库得到结果集
List<LinkedHashMap<String, Object>> linkedHashMapsList = commonDao.commonSelect(strSQL);
//如果传进来的pojo是基本类型,则走这个分支
try {
if ("java.lang.String".equals(pojo.getTypeName())) {
T pojoObj = pojo.newInstance();
for (int i = 0; i < linkedHashMapsList.size(); i++) {
LinkedHashMap<String, Object> stringObjectLinkedHashMap = linkedHashMapsList.get(i);
if ((null == stringObjectLinkedHashMap)) {
result.add(null);
continue;
}
Set<String> keySet = stringObjectLinkedHashMap.keySet();
Iterator<String> iterator = keySet.iterator();
while (iterator.hasNext()) {
String key = iterator.next();
String value = stringObjectLinkedHashMap.get(key).toString();
pojoObj = (T) value;
}
result.add(pojoObj);
}
return result;
}
//遍历结果集
for (int i = 0; i < linkedHashMapsList.size(); i++) {
LinkedHashMap<String, Object> linkedHashMap = linkedHashMapsList.get(i);
//创建pojo对象
T pojoObj = pojo.newInstance();
//将结果集取出来,并转换基本类型,再通过反射set到poji中
Set<String> keySet = linkedHashMap.keySet();
Iterator<String> iterator = keySet.iterator();
//遍历key
while (iterator.hasNext()) {
String next = iterator.next();
for (int j = 0; j < fields.length; j++) {
//当key的值等于pojo属性名时
if (next.equalsIgnoreCase(fields[j].getName())) {
//禁用安全检查以提升性能
fields[j].setAccessible(true);
//取出value,转换成string类型
String fieldValue = linkedHashMap.get(next).toString();
//获得pojo属性类型
String type = fields[j].getGenericType().getTypeName();
switch (type) {
case "java.lang.String": {
//给pojo赋值
fields[j].set(pojoObj, fieldValue);
break;
}
case "java.util.Date": {
fields[j].set(pojoObj, new SimpleDateFormat("yyyy-MM-dd").parse(fieldValue));
break;
}
case "java.math.BigDecimal": {
fields[j].set(pojoObj, BigDecimal.valueOf(Double.valueOf(fieldValue)));
break;
}
case "java.lang.Integer": {
fields[j].set(pojoObj, Integer.valueOf(fieldValue));
break;
}
case "java.lang.Double": {
fields[j].set(pojoObj, Double.valueOf(fieldValue));
break;
}
case "java.lang.Boolean": {
fields[j].set(pojoObj, Boolean.valueOf(fieldValue));
break;
}
case "java.lang.Long": {
fields[j].set(pojoObj, Long.valueOf(fieldValue));
break;
}
case "java.lang.Float": {
fields[j].set(pojoObj, Float.valueOf(fieldValue));
break;
}
default:
log.info("不支持该类型");
break;
}
}
}
}
result.add(pojoObj);
//将赋值完的实体类add到list中
}
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (ParseException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
}
return result;
//返回pojo的list
}
}
dao层:
@Mapper
public interface CommonDao {
@Select("${value}")
List<LinkedHashMap<String, Object>> commonSelect(String sql);
}
这个方法传入sql语句、结果类型,就可以直接得到返回结果,非常方便。
静态方法注入bean
@Component
public class ApplicationContextHelper implements ApplicationContextAware {
private static ApplicationContext applicationContext;
public ApplicationContextHelper() {
}
public static ApplicationContext getApplicationContext() {
return applicationContext;
}
public static Object getBeanById(String id) {
return applicationContext == null ? null : applicationContext.getBean(id);
}
public static <T> T getBeanByClass(Class<T> c) {
return applicationContext == null ? null : applicationContext.getBean(c);
}
public static Map getBeansByClass(Class c) {
return applicationContext == null ? null : applicationContext.getBeansOfType(c);
}
public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
ApplicationContextHelper.applicationContext = applicationContext;
}
}
具体为什么可以注入bean详见
https://blog.csdn.net/qq_31207917/article/details/108844219?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522166419815416782391820934%2522%252C%2522scm%2522%253A%252220140713.130102334…%2522%257D&request_id=166419815416782391820934&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2alltop_positive~default-1-108844219-null-null.142v50pc_rank_34_queryrelevant25,201v3control_2&utm_term=ApplicationContextAware&spm=1018.2226.3001.4187
测试代码
public class Test {
public static void main(String[] args) {
List<Student> studentlist = SqlUtils.getresult("select * from Users where userId = '001'", Student.class);
for (int i = 0; i < studentlist.size(); i++) {
System.out.println(studentlist[i]);
}
}
}
非常方便,不用写dao层以及xml配置文件,但是这个方法无法预防sql注入,不过这个接口的sql查询条件都是业务数据,并没有用户输入的地方,也没有暴露给前端。并且只用mybatis的查询方法,相对来说影响较小。