spring MVC +freemarker + easyui 实现sql查询和执行小工具总结

    项目中,有时候线下不能方便的连接项目中的数据源时刻,大部分的问题定位和处理都会存在难度,有时候,一个小工具就能实时的查询和执行当前对应的数据源的库.下面,就本人在项目中实际开发使用的小工具,实时的介绍开发使用过程.首先看图:大概的操作界面,基本使用easyui组件实现,欢迎大家吐槽:

界面包含了基本的sql查询 和 sql执行的小功能,把查询和执行分开,也是为了后台实现的需要,以及权限控制的考虑,毕竟执行的操作,会影响到系统的数据问题.查询和执行的菜单,是用easyui的手风琴式的菜单处理的.两个菜单的界面都包含了执行按钮和重置按钮,输入sql的文本区域,和数据源<哪些db需要操作>的tab分开展示,以及文本下方的执行结果展示信息.

首先看下查询结果展示:

确定查询,可以展示当前sql的查询结果展示:结果采用分页展示,表头采用的是当前数据库的表的字段名称.

执行案例,就不展示图片信息了.会在执行的下方提示:当前的sql执行成功的条数.  下面重点分享开发实现过程:

1,采用spring MVC的架构处理,首先来看下controller的处理类,基本的spring mvc的配置信息,在这就不多描述了. 本类主要包含了四个控制方法,

　　a,main方法,主要跳转到工具管理主页面.

　　b,query方法,主要是sql查询的主方法,接收传输过来的sql语句,解析给交给数据库执行

　　c,queryDetail方法,主要是查询页面的分页查询方法,点击每一页的查询sql方法

　　d,excute方法,接收前台传输的执行sql,解析给数据库执行

/**
 * 〈一句话功能简述〉<br>
 * 〈功能详细描述〉
 * 
 * @author lilin
 * @see [相关类/方法]（可选）
 * @since [产品/模块版本] （可选）
 */
@RequestMapping(value = "/tool")
@Controller
public class PmpTools {

    @Resource
    private IPmpToolService pmpToolService;

    /**
     * 
     * 功能描述: <br>
     * 〈功能详细描述〉工具管理主页面
     * 
     * @return
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    @RequestMapping(value = "/main")
    public String main() {
        return "/system/tools/main.ftl";
    }

    /**
     * 
     * 功能描述: <br>
     * 〈功能详细描述〉sql的查询主方法
     * 
     * @return
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    @RequestMapping(value = "/query")
    public String query(HttpServletRequest request) {
        String findSql = request.getParameter("findSql");
        String dataSource = request.getParameter("dataSource");
        if (StringUtils.isNotEmpty(findSql) && StringUtils.isNotEmpty(dataSource)) {
            String pageSql = pmpToolService.getQuerySqlByPage(findSql, 1, 1);
            List<Map<String, Object>> list = pmpToolService.queryMapBySql(pageSql, dataSource);
            if (list != null && !list.isEmpty()) {
                request.setAttribute("column", list.get(0));
                request.setAttribute("findSql", findSql);
                request.setAttribute("dataSource", dataSource);
            } else {
                request.setAttribute("errorMessage", "未查到数据!");
            }
        }
        return "/system/tools/queryDetail.ftl";
    }

    /**
     * 
     * 功能描述: <br>
     * 〈功能详细描述〉
     * 
     * @return
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    @ResponseBody
    @RequestMapping(value = "queryDetail")
    public Object queryDetail(HttpServletRequest request) {
        Map<String, Object> map = new HashMap<String, Object>();
        String page = request.getParameter("page");
        String rows = request.getParameter("rows");
        int intPage = Integer.parseInt((page == null || page.equals("0")) ? "1" : page);
        int number = Integer.parseInt((rows == null || rows.equals("0")) ? "10" : rows);
        int start = (intPage - 1) * number + 1;
        String findSql = request.getParameter("findSql");
        String dataSource = request.getParameter("dataSource");
        String totalSql = null;
        List<Map<String, Object>> lists = null;
        int totals = 0;
        if (StringUtils.isNotEmpty(findSql)) {
            try {
                findSql = URLDecoder.decode(findSql, "UTF-8");
            } catch (UnsupportedEncodingException e) {
                throw new RuntimeException("解析SQL报错!");
            }
            totalSql = pmpToolService.getTotalSql(findSql);
            totals = pmpToolService.queryTotalNumBySql(totalSql, dataSource);
            String pageSql = pmpToolService.getQuerySqlByPage(findSql, start, number);
            lists = pmpToolService.queryMapBySql(pageSql, dataSource);
        }
        map.put("rows", lists);
        map.put("total", totals);
        return map;
    }
    
    
    /**
     * 
     * 功能描述: <br>
     * 〈功能详细描述〉
     *
     * @param request
     * @return
     * @see [相关类/方法](可选)
     * @since [产品/模块版本](可选)
     */
    @ResponseBody
    @RequestMapping("excute")
    public Object excute(HttpServletRequest request) {
        String excuteSql = request.getParameter("excuteSql");
        String dataSource = request.getParameter("dataSource");
        String message = "";
        Map<String, String> map = new HashMap<String, String>();
        if (StringUtils.isNotEmpty(excuteSql)) {
            try {
                int flag = pmpToolService.exctueSql(excuteSql,dataSource);
                message = "执行成功: " + flag + "行。";
            } catch (RuntimeException e) {
                message = "执行失败:" + e.getMessage();
            }
        }
        map.put("message", message);
        return map;
    }

}

2,页面主要是使用了freemaker+easyui的组件实现.主要页面包含如下:

下面分享每个页面的详细设计实现:

　　a,main页面:

<html>
<head>
    <meta charset="UTF-8">
    <title>工具页面</title>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <meta http-equiv="X-UA-Compatible" content="IE=edge"/>
    <link rel="shortcut icon" href="http://www.suning.com/favicon.ico" type="image/x-icon"/>
    <script type="text/javascript" src="${resRoot}/js/tool/tool.js"></script>
    <script type="text/javascript">
        $(document).ready(function(){
            $('#sqlSelect').tabs({
                tools:'#tab-tools'
            });
            $('#sqlExcute').tabs({
                onSelect:tool.excuteTabSelect
            });
        });
    </script>
</head>
<body>
<div id="p" class="easyui-panel" title="SQL小工具" style="1420px;height:620px;">
    <div class="easyui-accordion" style="100%;height:560px;">
         <div title="SQL<>查询"  style="overflow:auto;padding:10px;">
             <div id="sqlSelect" style="99%;height:70px;">
                <div title="WUPDB" style="text-align: center;">
                     <a href="javascript:void(0);" class="btn l mt10" onclick="tool.query('uwpdb');"><span>执行</span></a>
                     <a href="javascript:void(0);" class="btn l mt10" onclick="tool.clear('findSql');"><span>重置</span></a>
                </div>
                <div title="PMPDB" style="text-align: center;">
                     <a href="javascript:void(0);" class="btn l mt10" onclick="tool.query('pmpdb');"><span>执行</span></a>
                     <a href="javascript:void(0);" class="btn l mt10" onclick="tool.clear('findSql');"><span>重置</span></a>
                </div>
            </div>
            <textarea rows="5" name="findSql" id="findSql" style="1380px;" placeholder="[输入查询sql]" class="commInput"></textarea>
            <div id="detail" style="margin-top:4px;">暂无查询结果</div>
         </div>
         <div title="SQL<>执行"  style="overflow:auto;padding:10px;">
             <div id="sqlExcute" style="99%;height:70px;">
                <div title="WUPDB" style="text-align: center;">
                     <div>
                         <a href="javascript:void(0);" class="btn l mt10" onclick="tool.excute('uwpdb');"><span>执行</span></a>
                         <a href="javascript:void(0);" class="btn l mt10" onclick="tool.clear('excuteSql');"><span>重置</span></a>
                     </div>
                </div>
                <div title="PMPDB" style="text-align: center;">
                     <div>
                         <a href="javascript:void(0);" class="btn l mt10" onclick="tool.excute('pmpdb');"><span>执行</span></a>
                         <a href="javascript:void(0);" class="btn l mt10" onclick="tool.clear('excuteSql');"><span>重置</span></a>
                     </div>
                </div>
            </div>
            <textarea rows="5" name="excuteSql" id="excuteSql" style="1380px;" placeholder="[输入执行sql]" class="commInput"></textarea>
            <div id="excuteDetail" style="margin-top:4px;">暂无执行结果</div>
         </div>
    </div>
</div>

</body>
</html>

　　b,queryDetail页面如下: 主要采用了easyui的datagrid的组件:用于分页查询嵌入

<script type="text/javascript">
    $(document).ready(function(){
        var findSql = "${findSql}";
        var dataSource = "${dataSource}";
        findSql = encodeURI(findSql);
        $('#dataGrid').datagrid({
            url: '${base}/tool/queryDetail.htm',
            method: 'post',
            title: '',
             1382,
            height: 330,
            fitColumns: true,
            singleSelect: true,
            columns:[[
                {field:'RN',title:'序号'},
                <#if column??>
                    <#list column?keys as col>
                        {field:'${col}',title:'${col}'},
                    </#list>
                </#if>
                {field:'',title:'',hidden:true}
            ]],
            queryParams:{
                findSql:findSql,
                dataSource : dataSource
            },
            pagination:true,
            onLoadError:function(){
                $.messager.alert('警告','查询出错!','error');
            },
            onClickRow: function(rowIndex, rowData){
                $('#dataGrid').datagrid('unselectRow', rowIndex);
            } 
        });
    });

</script>
<#if column??>
    <table id="dataGrid"></table>
<#else>
    <p style="color: red;padding:10px">无数据</p>
</#if>

3,页面的js操作,全部提取出来了到tool.js中:主要是查询和执行的方法:

Tool = function() {

    this.query = function(dataSource) {
        var findSql = $.trim($('#findSql').val());
        if (!findSql) {
            $.messager.alert('警告', '请输入查询语句!', 'error');
            return;
        } else if (findSql.substring(0, 6) != 'select') {
            $.messager.alert('警告', '查询语句请以select开头！', 'error');
            return;
        }
        $.messager.confirm("操作提示", "您确定要执行操作吗？", function(data) {
            if (data) {
                $.ajax({
                    type : "post",
                    url : "${applicationName}/tool/query.htm",
                    dataType : "html",
                    async : true,
                    data : {
                        findSql : findSql,
                        dataSource : dataSource
                    },
                    success : function(html) {
                        $("#detail").empty();
                        $("#detail").html(html);
                    },
                    error : function() {
                        $("#detail").html("查询明细信息数据错误,请检查sql语句!");
                    }
                });
            }
        });
    };

    this.excute = function(dataSource) {
        var excuteSql = $.trim($('#excuteSql').val());
        if (!excuteSql) {
            $.messager.alert('警告', '请输入执行语句!', 'error');
            return;
        }
        $.messager.confirm("操作提示", "您确定要执行操作吗？", function(data) {
            if (data) {
                $.ajax({
                    type : "post",
                    url : "${applicationName}/tool/excute.htm",
                    dataType : "json",
                    async : true,
                    data : {
                        excuteSql : excuteSql,
                        dataSource : dataSource
                    },
                    success : function(data) {
                        $("#excuteDetail").empty();
                        $("#excuteDetail").html(data.message);
                    },
                    error : function(e) {
                        $("#excuteDetail").html("执行sql语句错误,请检查sql语句!");
                    }
                });
            }
        });
    };

    this.clear = function(textareaId) {
        $('#' + textareaId).val('');
    };
    
    this.queryTabSelect = function(title,index){
        $("#detail").html('暂时么有查询结果');
    };
    
    this.excuteTabSelect = function(title,index){
        $("#excuteDetail").html('暂时么有执行结果');
    };
};
var tool = new Tool();

4,service的类设计如下,接口就不做展示了,主要的方法展示:

/**
 * 〈一句话功能简述〉<br>
 * 〈功能详细描述〉
 * 
 * @author lilin
 * @see [相关类/方法]（可选）
 * @since [产品/模块版本] （可选）
 */
@Service
public class PmpToolService implements IPmpToolService {

    @Resource
    private IPmpToolDao pmpToolDao;

    @Override
    public List<Map<String, Object>> queryMapBySql(String pageSql, String datasoure) {
        return pmpToolDao.findMapBySql(pageSql, datasoure);
    }

    @Override
    public int queryTotalNumBySql(String totalSql, String datasoure) {
        return pmpToolDao.queryTotalNumBySql(totalSql, datasoure);
    }

    @Override
    public int exctueSql(String sqlString, String datasource) {
        return pmpToolDao.exctueSql(sqlString, datasource);
    }

    @Override
    public String getQuerySqlByPage(String findSql, int start, int maxRows) {
        StringBuilder temp = new StringBuilder();
        temp.append("SELECT * FROM ( SELECT ST.*, ROWNUMBER() OVER() AS RN FROM (");
        temp.append(findSql);
        temp.append(") AS ST)AS PT WHERE PT.RN BETWEEN ");
        temp.append(start);
        temp.append(" AND ");
        temp.append(start + maxRows - 1);
        return temp.toString();
    }

    @Override
    public String getTotalSql(String findSql) {
        String temp = "";
        if (findSql.indexOf("order") == -1) {
            temp = findSql;
        } else {
            temp = findSql.substring(0, findSql.indexOf("order"));
        }
        return "select count(1) from (" + temp + ") as total";
    }
}

5,dao层的方法设计和service的类似,主要是连接数据源的操作:

/**
 * 〈一句话功能简述〉<br>
 * 〈功能详细描述〉
 * 
 * @author lilin
 * @see [相关类/方法]（可选）
 * @since [产品/模块版本] （可选）
 */
@Repository
public class PmpToolDao extends CommonDao implements IPmpToolDao {

    private static final String TOOL_SQL = "tool.sql";

    @Override
    public List<Map<String, Object>> findMapBySql(String pageSql, String datasource) {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("sql", pageSql);
        if ("uwpdb".equals(datasource)) {
            return getSoaDalClient().queryForList(TOOL_SQL, paramMap);
        }
        return getDalClient().queryForList(TOOL_SQL, paramMap);
    }

    @Override
    public int queryTotalNumBySql(String totalSql, String datasource) {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("sql", totalSql);
        if ("uwpdb".equals(datasource)) {
            return getSoaDalClient().queryForObject(TOOL_SQL, paramMap, Integer.class);
        }
        return getDalClient().queryForObject(TOOL_SQL, paramMap, Integer.class);
    }

    @Override
    public int exctueSql(String sqlString, String datasource) {
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("sql", sqlString);
        if ("uwpdb".equals(datasource)) {
            return getSoaDalClient().execute(TOOL_SQL, paramMap);
        }
        return getDalClient().execute(TOOL_SQL, paramMap);
    }

}

6,最后就是sqlMap文件的相关sql,本组件主要是前台接收sql执行查询和执行操作,所以,sqlmap中的文件比较简单:

<?xml version="1.0" encoding="UTF-8" ?>
<sqlMap namespace="tool">
    <sql id="sql">
        <![CDATA[
           ${sql}
        ]]>
    </sql>
</sqlMap>

到此为止,一个简单的sql查询和执行小工具就完成了,可以方便的进行当前的db的简单增删改查操作了,只要数据源用户的权限够,也能执行相关的DDL操作.

有个比较棘手的问题就是:当前的库的表中,有的字段信息存放的是xml格式的数据的时候,当前的组件展示会存在问题,不能正常的展示xml格式的文本数据,这个还在进一步的排查和解决中,希望有朋友能够指点,一起交流.这个暂时没有找寻到好的方法,之前打算采用字符替换的,也不能完全解决问题