mybatis 多表自定义查询实现

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;
}
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容

  • 面试题
    Swift1> Swift和OC的区别1.1> Swift没有地址/指针的概念1.2> 泛型1.3> 类型严谨 对...
    cosWriter阅读 11,144评论 1赞 32
  • MarkDown
    1. 简介 1.1 什么是 MyBatis ？ MyBatis 是支持定制化 SQL、存储过程以及高级映射的优秀的...
    笨鸟慢飞阅读 5,655评论 0赞 4
  • Android面试准备
    一、简历准备 1、个人技能 (1)自定义控件、UI设计、常用动画特效 自定义控件 ①为什么要自定义控件？ Andr...
    lucas777阅读 5,261评论 2赞 54
  • 无题
    我一胆小 就用诗的名义 鬼鬼祟祟 她说 你弄点看不懂的 我听懂了她的密码 看不懂 胆子就大了 ...
    DU杜默阅读 185评论 7赞 4