1.pom.xml
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.0.1</version>
</dependency>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>fastjson</artifactId>
<version>1.2.47</version>
</dependency>
2.工具类
2.1.自定义查询类
package com.yudu.lyj.util;
import com.yudu.lyj.config.DbConfig;
import org.apache.ibatis.datasource.pooled.PooledDataSource;
import org.apache.ibatis.jdbc.SqlRunner;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.Environment;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.SqlSource;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.scripting.xmltags.XMLLanguageDriver;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.ibatis.transaction.TransactionFactory;
import org.apache.ibatis.transaction.jdbc.JdbcTransactionFactory;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.DateFormat;
import java.util.*;
@Component
public class SelfSearch {
@Autowired
private DbConfig dbConfig;
public Map<String, Object> execSql(String prepareSql, Map<String, Object> condition) throws Exception {
Map<String, Object> page = new HashMap<>();
SqlSession sqlSession = null;
try {
SqlSessionFactory sqlSessionFactory = this.createSqlSessionFactory();
sqlSession = sqlSessionFactory.openSession();
Connection connection = sqlSession.getConnection();
SqlRunner sqlRunner = new SqlRunner(connection);
String realSql = this.toSql(prepareSql, condition);
page.put("results", sqlRunner.selectAll(realSql));
return page;
} catch (SQLException e) {
e.printStackTrace();
throw new Exception("");
} finally {
if (sqlSession != null) {
sqlSession.close();
}
}
}
/**
* 配置数据库信息，获取SqlSessionFactory
* @return
*/
private SqlSessionFactory createSqlSessionFactory() {
String driver = dbConfig.dbDriverClassName;
String url = dbConfig.dbUrl;
String username = dbConfig.dbUserName;
String password = dbConfig.dbPassword;
//创建连接池
DataSource dataSource = new PooledDataSource(driver, url, username, password);
//事务
TransactionFactory transactionFactory = new JdbcTransactionFactory();
//创建环境
Environment environment = new Environment("development", transactionFactory, dataSource);
//创建配置
Configuration configuration = new Configuration(environment);
//开启驼峰规则
configuration.setMapUnderscoreToCamelCase(true);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(configuration);
return sqlSessionFactory;
}
private String toSql(String prepareSql, Map<String, Object> condition) throws Exception {
XMLLanguageDriver driver = new XMLLanguageDriver();
String script = "<script>" + prepareSql + "</script>";
SqlSource sqlSource;
BoundSql boundSql;
SqlSessionFactory sqlSessionFactory = this.createSqlSessionFactory();
try {
sqlSource = driver.createSqlSource(sqlSessionFactory.getConfiguration(), script, condition.getClass());
boundSql = sqlSource.getBoundSql(condition);
} catch (Exception e) {
e.printStackTrace();
throw new Exception("");
}
Configuration configuration = sqlSessionFactory.getConfiguration();
Object parameterObject = boundSql.getParameterObject();
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
if (parameterMappings.size() == 0 || parameterObject == null) {
return sql;
}
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
sql = sql.replaceFirst("\\?", getParameterValue(parameterObject));
} else {
MetaObject metaObject = configuration.newMetaObject(parameterObject);
for (ParameterMapping parameterMapping : parameterMappings) {
String propertyName = parameterMapping.getProperty();
if (metaObject.hasGetter(propertyName)) {
Object obj = metaObject.getValue(propertyName);
sql = sql.replaceFirst("\\?", getParameterValue(obj));
} else if (boundSql.hasAdditionalParameter(propertyName)) {
Object obj = boundSql.getAdditionalParameter(propertyName);
sql = sql.replaceFirst("\\?", getParameterValue(obj));
}
}
}
return sql;
}
private static String getParameterValue(Object obj) {
String value;
if (obj instanceof String) {
value = "'" + obj.toString() + "'";
} else if (obj instanceof Date) {
DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
value = "'" + formatter.format(obj) + "'";
} else if (obj instanceof String[]){
String str = "";
for(String s :(String[]) obj){
str += "'" + s.trim() + "',";
}
value = str.substring(0,str.length()-1);
}else {
if (obj != null) {
value = obj.toString();
} else {
value = "";
}
}
return value;
}
}
2.2.解析查询结果集为JSONArray字符串
package com.yudu.lyj.util;
public class JsonStringUtils {
public static String stringConvert(String oldString){
String newResult = "[";
oldString = oldString.replaceAll(" ", "");
oldString = trimStart(oldString,"[");
oldString = trimEnd(oldString,"]");
oldString = oldString.replace("},{","}=={");
String[] r1 = oldString.split("==");
for (int i = 0; i <r1.length ; i++) {
newResult+="{";
r1[i] = trimStart(r1[i],"{");
r1[i] = trimEnd(r1[i],"}");
String[] r2 =r1[i].split(",");
for (int j = 0; j < r2.length; j++) {
if(r2[j].split("=").length == 2){
String s1 = r2[j].split("=")[0];
String s2 = r2[j].split("=")[1];
newResult+="\""+s1+"\""+":"+ "\""+s2+"\""+",";
}else{
String s1 = r2[j].split("=")[0];
newResult+="\""+s1+"\""+":"+ "\""+""+"\""+",";
}
}
newResult = newResult.substring(0,newResult.length()-1);
newResult+="},";
}
newResult = newResult.substring(0,newResult.length()-1);
newResult += "]";
return newResult;
}
/*
* 删除开头字符串
*/
public static String trimStart(String inStr, String prefix) {
if (inStr.startsWith(prefix)) {
return (inStr.substring(prefix.length()));
}
return inStr;
}
/*
* 删除末尾字符串
*/
public static String trimEnd(String inStr, String suffix) {
if (inStr.endsWith(suffix)) {
return (inStr.substring(0,inStr.length()-suffix.length()));
}
return inStr;
}
}
3.单元测试
package com.yudu.lyj;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.yudu.lyj.util.JsonStringUtils;
import com.yudu.lyj.util.SelfSearch;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
@RunWith(SpringRunner.class)
@SpringBootTest
public class SqlTest {
@Autowired
private SelfSearch selfSearch;
@Test
public void test(){
String userId = "25";
String sql = "SELECT u.name as username, d.name as departmentname, c.mobilePhone\n" +
"FROM USERINFO u\n" +
"\tINNER JOIN DEPARTMENT d ON u.departmentID = d.departmentID\n" +
"\tINNER JOIN CONTACTLIST c ON c.contactID = u.contactListID\n" +
"WHERE u.departmentID = (\n" +
"\t\tSELECT departmentID\n" +
"\t\tFROM USERINFO\n" +
"\t\tWHERE userInfoId = '"+userId+"'\n" +
"\t)\n" +
"\tAND u.orderNum = 0";
Map<String, Object> condition = new HashMap<>();
String result = null;
Map<String,Object> map = null;
try {
map = selfSearch.execSql(sql,condition);
for (String k:map.keySet()) {
result = map.get(k).toString();
}
result = JsonStringUtils.stringConvert(result);
JSONArray jsonArray = JSONObject.parseArray(result);
} catch (Exception e) {
e.printStackTrace();
}
}
}
4.补充数据库配置类
package com.yudu.lyj.config;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
@Component
public class DbConfig {
@Value("${spring.datasource.url}")
public String dbUrl;
@Value("${spring.datasource.username}")
public String dbUserName;
@Value("${spring.datasource.password}")
public String dbPassword;
@Value("${spring.datasource.driver-class-name}")
public String dbDriverClassName;
}
网友评论