jeecgboot整合JdbcTemplate方便多表联合查询

　　感觉jeecgboot处理复杂的多表联合查询有点费劲，就自己实现了JdbcTemplate的整合，其实也不是整合吧，因为jeecgboot已经把JdbcTemplate整合进来了。

　　我查了下项目的依赖关系，发现jeecg-boot-base-core模块依赖了mybatis-plus-boot-starter，而mybatis-plus-boot-starter依赖了spring-boot-starter-jdbc，这样JdbcTemplate就自动整合进来了，哪里需要直接注入它的bean就可以。

@Autowired
private JdbcTemplate jdbcTemplate;

　　

　　如果直接这样用，用的多了还是挺麻烦的，就把常用的方法提取出来，哪里想使用复杂查询直接注入BaseService就可以了，推荐service层注入。

　　直接上代码：

　　

　　IBaseService

package org.jeecg.config.jdbctemplate;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.springframework.lang.Nullable;

import java.util.List;
import java.util.Map;

public interface IBaseService {

    /**
     * Query given SQL to create a prepared statement from SQL and a list of
     * arguments to bind to the query, expecting a result object.
     * <p>The query is expected to be a single row/single column query; the returned
     * result will be directly mapped to the corresponding object type.
     * @param sql the SQL query to execute
     * @param requiredType the type that the result object is expected to match
     * (for example, {@code Integer.class})
     * @param args arguments to bind to the query
     * @return the result object of the required type, or {@code null} in case of SQL NULL
     */
    @Nullable
    <T> T queryFirst(String sql, Class<T> requiredType, @Nullable Object... args);

    /**
     * Query given SQL to create a prepared statement from SQL and a list of
     * arguments to bind to the query, expecting a result list.
     * <p>The results will be mapped to a List (one entry for each row) of
     * result objects, each of them matching the specified element type.
     * @param sql the SQL query to execute
     * @param elementType the required type of element in the result list
     * (for example, {@code Integer.class})
     * @param args arguments to bind to the query
     * @return a List of objects that match the specified element type
     */
    <T> List<T> query(String sql, Class<T> elementType, @Nullable Object... args);

    @Nullable
    Map<String, Object> findById(String tableName, Object idValue);

    List<Map<String, Object>> find(String sql, @Nullable Object... args);

    @Nullable
    Map<String, Object> findFirst(String sql, @Nullable Object... args);

    boolean save(String tableName, Map<String, Object> record);

    int update(String sql, @Nullable Object... args);

    boolean update(String tableName, Map<String, Object> record);

    int[] batchUpdate(final String... sqls);

    boolean deleteById(String tableName, Object idValue);

    Page<Map<String, Object>> findPage(int pageNumber, int pageSize, String sql, @Nullable Object... args);

    Page<Map<String, Object>> findPage(int pageNumber, int pageSize, String select, String sqlExceptSelect, @Nullable Object... args);

}

 　　BaseService

package org.jeecg.config.jdbctemplate;

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.lang.Nullable;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Service
@Slf4j
public class BaseService implements IBaseService {
    
    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public BaseService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public <T> T queryFirst(String sql, Class<T> requiredType, @Nullable Object... args){
        List<T> list = jdbcTemplate.queryForList(sql, requiredType, args);
        if(list.size() > 0){
            return list.get(0);
        }
        return null;
    }

    @Override
    public <T> List<T> query(String sql, Class<T> elementType, @Nullable Object... args){
        return jdbcTemplate.queryForList(sql, elementType, args);
    }

    @Override
    public Map<String, Object> findById(String tableName, Object idValue) {
        if (StringUtils.isBlank(tableName) || idValue == null) {
            return null;
        }
        String sql = MysqlDialect.forBsFindById(tableName);

        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, idValue);
        Map<String, Object> record = null;
        if(list.size() > 0){
            record = list.get(0);
        }
        return record;
    }

    @Override
    public List<Map<String, Object>> find(String sql, Object... args) {
        return jdbcTemplate.queryForList(sql, args);
    }

    @Override
    public Map<String, Object> findFirst(String sql, Object... args) {
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql, args);
        if(list.size() > 0){
            return list.get(0);
        }
        return null;
    }

    @Override
    public boolean save(String tableName, Map<String, Object> record) {
        StringBuilder sql = new StringBuilder();
        int length = record.entrySet().size();
        Object[] args = new Object[length];
        
        MysqlDialect.forBsSave(sql, tableName, record, args);
        int result = jdbcTemplate.update(sql.toString(), args);
        return result >= 1;
    }

    @Override
    public int update(String sql, Object... args) {
        return jdbcTemplate.update(sql, args);
    }

    @Override
    public boolean update(String tableName, Map<String, Object> record) {
        if(record.get(MysqlDialect.ID) == null || StringUtils.isBlank(record.get(MysqlDialect.ID).toString())) {
            throw new RuntimeException("You can't update record without Primary Key ," + MysqlDialect.ID + " can not be null.");
        }
        Object idValue = record.get(MysqlDialect.ID);
        
        StringBuilder sql = new StringBuilder();
        int length = record.entrySet().size();
        Object[] args = new Object[length];
        
        MysqlDialect.forBsUpdate(sql, tableName, idValue, record, args);
        int result = jdbcTemplate.update(sql.toString(), args);
        return result >= 1;
    }

    @Override
    public int[] batchUpdate(final String... sql) {
        return jdbcTemplate.batchUpdate(sql);
    }

    @Override
    public boolean deleteById(String tableName, Object idValue) {
        String sql = MysqlDialect.forBsDeleteById(tableName);
        int result = jdbcTemplate.update(sql, idValue);
        return result >= 1;
    }

    @Override
    public Page<Map<String, Object>> findPage(int pageNumber, int pageSize, String sql, Object... args) {
        return findPage(pageNumber, pageSize, "SELECT * ", "FROM (" + sql + ") r_", args);
    }

    @Override
    public Page<Map<String, Object>> findPage(int pageNumber, int pageSize, String select, String sqlExceptSelect, Object... args) {
        Page<Map<String, Object>> page = new Page<>(pageNumber, pageSize);
        if (pageNumber == -1 || pageSize == -1) {
            List<Map<String, Object>> records = find(select + " " + sqlExceptSelect, args);
            page.setRecords(records);
            page.setTotal(records.size());
            return page;
        }
        if (pageNumber < 1 || pageSize < 1) {
            List<Map<String, Object>> records = find(select + " " + sqlExceptSelect, args);
            page.setRecords(records);
            page.setTotal(records.size());
            return page;
        }

        long totalRow;
        int totalPage;

        totalRow = jdbcTemplate.queryForObject("SELECT COUNT(*) " + replaceFormatSqlOrderBy(sqlExceptSelect), Long.class, args);
        
        totalPage = (int) (totalRow / pageSize);
        if (totalRow % pageSize != 0) {
            totalPage++;
        }
        
        if (pageNumber > totalPage) {
            page.setRecords(new ArrayList<>(0));
            page.setTotal(totalRow);
            return page;
        }
        
        StringBuilder sql = new StringBuilder();
        MysqlDialect.forPaginate(sql, pageNumber, pageSize, select, sqlExceptSelect);
        
        List<Map<String, Object>> list = find(sql.toString(), args);

        page.setRecords(list);
        page.setTotal(totalRow);
        
        return page;
    }
    
    /**
     * 去掉order by查询条件（优化查询效率）
     */
    private String replaceFormatSqlOrderBy(String sql) {
        sql = sql.replaceAll("(\\s)+", " ");
        int index = sql.toLowerCase().lastIndexOf("order by");
        if (index > sql.toLowerCase().lastIndexOf(")")) {
            String sql1 = sql.substring(0, index);
            String sql2 = sql.substring(index);
            sql2 = sql2.replaceAll("[oO][rR][dD][eE][rR] [bB][yY] [\u4e00-\u9fa5a-zA-Z0-9_.]+((\\s)+(([dD][eE][sS][cC])|([aA][sS][cC])))?(( )*,( )*[\u4e00-\u9fa5a-zA-Z0-9_.]+(( )+(([dD][eE][sS][cC])|([aA][sS][cC])))?)*", "");
            return sql1 + sql2;
        }
        return sql;
    }

}

　　MysqlDialect

package org.jeecg.config.jdbctemplate;

import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

public class MysqlDialect {
    
    public static final String ID = "id";
    
    public static String forBsFindById(String tableName) {
        tableName = tableName.trim();
        StringBuilder sql = new StringBuilder("SELECT * FROM ");
        sql.append(tableName).append(" WHERE ").append(ID).append(" = ?");
        return sql.toString();
    }
    
    public static void forBsSave(StringBuilder sql, String tableName, Object record, Object... args) {
        tableName = tableName.trim();
        
        sql.append("INSERT INTO ");
        sql.append(tableName).append(" (");
        StringBuilder temp = new StringBuilder();
        temp.append(") VALUES (");
        
        int i = 0;

        Set<Entry<String, Object>> entrySet = ((Map<String, Object>) record).entrySet();

        for (Entry<String, Object> e : entrySet) {
            if (i > 0) {
                sql.append(", ");
                temp.append(", ");
            }
            sql.append(e.getKey());
            temp.append("?");
            args[i] = e.getValue();
            
            i++;
        }
        sql.append(temp.toString()).append(")");
    }
    
    public static void forBsUpdate(StringBuilder sql, String tableName, Object idValue, Object record, Object... args) {
        tableName = tableName.trim();
        
        sql.append("UPDATE ");
        sql.append(tableName).append(" SET ");
        
        int i = 0;

        Set<Entry<String, Object>> entrySet = ((Map<String, Object>) record).entrySet();
        
        for (Entry<String, Object> e : entrySet) {
            String colName = e.getKey();
            if(!colName.equalsIgnoreCase(ID)) {
                if (i > 0) {
                    sql.append(", ");
                }
                sql.append(colName).append(" = ? ");
                args[i] = e.getValue();
                
                i++;
            }
        }
        sql.append(" WHERE ").append(ID).append(" = ?");
        args[i] = idValue;
    }

    public static String forBsDeleteById(String tableName) {
        tableName = tableName.trim();
        StringBuilder sql = new StringBuilder("DELETE FROM ").append(tableName).append(" WHERE ");
        sql.append(ID).append(" = ?");
        return sql.toString();
    }
    
    public static void forPaginate(StringBuilder sql, int pageNumber, int pageSize, String select, String sqlExceptSelect) {
        int offset = pageSize * (pageNumber - 1);
        sql.append(select).append(" ");
        sql.append(sqlExceptSelect);
        sql.append(" LIMIT ").append(offset).append(", ").append(pageSize);
    }
    
}

　　SQL

package org.jeecg.config.jdbctemplate;

import lombok.extern.slf4j.Slf4j;

@Slf4j
public class SQL {

    private StringBuilder sql;

    private SQL() {
        this.sql = new StringBuilder();
    }

    private SQL(final String sqlStr) {
        (this.sql = new StringBuilder()).append(sqlStr);
    }

    public static SQL me() {
        return new SQL();
    }

    public static SQL me(final String sqlStr) {
        return new SQL(sqlStr);
    }

    public SQL build(final String statment) {
        this.sql.append(statment).append(" ");
        return this;
    }

    public String create() {
        log.debug("SQL = " + this.sql.toString());
        return this.sql.toString();
    }

    @Override
    public String toString() {
        return this.sql.toString();
    }
}

　　使用例子：

SQL sql = SQL.me();
sql.build("SELECT id,username,realname,`password`,salt,org_code,create_time");
sql.build("FROM sys_user");
sql.build("WHERE `status` = ?");
sql.build("AND del_flag = ?");
sql.build("ORDER BY create_time DESC");
Page<Map<String, Object>> pageNew = baseService.findPage(pageNo, pageSize, sql.create(), 2, 0);