mybatis 显示自定义多个统计_mybatis 多表自定义查询实现

1.pom.xml

org.springframework.boot

spring-boot-starter-test

test

org.mybatis.spring.boot

mybatis-spring-boot-starter

2.0.1

com.microsoft.sqlserver

mssql-jdbc

runtime

com.alibaba

fastjson

1.2.47

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 execSql(String prepareSql, Map condition) throws Exception {

Map 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 condition) throws Exception {

XMLLanguageDriver driver = new XMLLanguageDriver();

String 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 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

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 condition = new HashMap<>();

String result = null;

Map 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;

}