SQL文件导入并执行

• • • 前端代码
    • Java代码
    • • • • RunSqlMapper.java
          • RunSqlMapper.xml
          • Controller文件
          • ServiceImpl文件

前端代码

略，比较简单的文件上传，这里就不写了

Java代码

由于要结合Mybatis使用所以就需要创建Mapper文件了

RunSqlMapper.java

和你的其他Mapper文件放一起就行了，要被扫描到

public interface RunSqlMapper extends BaseMapper<T> {
	//执行sql语句
    public void runSqlBySqlStr(String sqlStr);
}

RunSqlMapper.xml

我这里执行的sql语句是新增语句，如果是查询语句的话情况略微不同，需要加上resultMap返回查询的格式

<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="RunSqlMapper的位置">
	//执行sql语句
	<select id="runSqlBySqlStr" parameterType="java.lang.String">
		<![CDATA[${sqlStr}]]>
	</select>
</mapper>

Controller文件

	 /**
	  * 导入sql数据
	  *
	  * @param request
	  * @param response
	  * @return
	  */
	 @RequestMapping(value = "/importSql", method = RequestMethod.POST)
	 public Result<?> importSql(HttpServletRequest request, HttpServletResponse response) {
		 return 调用service的方法(request, response);
	 }

ServiceImpl文件

	@Resource
    private RunSqlMapper runSqlMapper;
    
   /**
     * 导入sql文件
     *
     * @param request
     * @param response
     * @return
     */
    protected Result<?> importSql(HttpServletRequest request, HttpServletResponse response) {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        for (Map.Entry<String, MultipartFile> entity : fileMap.entrySet()) {
            MultipartFile file = entity.getValue();// 获取上传文件对象
            List<String> sqlList = new ArrayList<String>();
            InputStream sqlFileIn = null;
            try {
                sqlFileIn = file.getInputStream();
                int available = sqlFileIn.available();
                if (available == 0){
                    throw new RuntimeException("文件内容为空，请检查后再进行导入");
                }
                StringBuffer sqlSb = new StringBuffer();
                byte[] buff = new byte[1024];
                int byteRead = 0;
                while ((byteRead = sqlFileIn.read(buff)) != -1) {
                    sqlSb.append(new String(buff, 0, byteRead));
                }
                // Windows 下换行是 \r\n, Linux 下是 \n
                String[] sqlArr = sqlSb.toString()
                        .split("(;\\s*\\r\\n)|(;\\s*\\n)");
                for (int i = 0; i < sqlArr.length; i++) {
                    String sql = sqlArr[i].replaceAll("--.*", "").trim();
                    if (!sql.equals("")) {
                        sqlList.add(sql);
                    }
                }
                for (String sql:sqlList){
                    Boolean b = sql != null && (sql.contains("insert") || sql.contains("INSERT"));
                    if (b){
                        runSqlMapper.runSqlBySqlStr(sql);
                    }else {
                        throw new JeecgBootException(sql + " sql语句错误");
                    }
                }
                return Result.ok("文件导入成功！数据行数：" + sqlList.size());
            } catch (Exception e) {
                log.error(e.getMessage(), e);
                return Result.error("文件导入失败:" + e.getMessage());
            } finally {
                try {
                    sqlFileIn.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return Result.error("文件导入失败！");
    }