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("文件导入失败！");
    }

 
最近在做一个CMS系统，不免会在CMS安装阶段会有一个Install database阶段，需要用到安装数据库，之前已经用mysqldunp命令到处一个sql格式的文件，现在需要将它导入数据库。由于java里没有直接针对于数据库的文件操作。只能考虑其他办法：这里是采用读取文件拼接成字符串，并批量处理(addBatch)的方式：代码如下：
 
private static File file = new File("E:\\data\\tulufan.sql");
 
private static InputStreamReader fileReader;
 
static {
 
try {
 
if (!file.exists()) {
 
file.createNewFile();
 
}
 
fileReader = new InputStreamReader(new FileInputStream(file),
 
"UTF-8");
 
} catch (Exception e) {
 
e.printStackTrace();
 
}
 
}
 
public void readSQLFile() {
 
BufferedReader bufferedReader = new BufferedReader(fileReader);
 
try {
 
StringBuilder sBuilder = new StringBuilder("");
 
String str = bufferedReader.readLine();
 
while (str != null) {
 
// 去掉一些注释，和一些没用的字符
 
if (!str.startsWith("#") && !str.startsWith("/*")
 
&& !str.startsWith("–") && !str.startsWith("\n"))
 
sBuilder.append(str);
 
str = bufferedReader.readLine();
 
}
 
String[] strArr = sBuilder.toString().split(";");
 
List strList = new ArrayList();
 
for (String s : strArr) {
 
strList.add(s);
 
System.out.println(s);
 
}
 
// 创建数据连接对象，下面的DBConnection是我的一个JDBC类
 
DBConnection db = new DBConnection();
 
db.executeBatchByStatement(strList);
 
} catch (Exception e) {
 
e.printStackTrace();
 
} finally {
 
try {
 
bufferedReader.close();
 
fileReader.close();
 
} catch (Exception e) {
 
}
 
}
 
return true;
 
}/*******************************
 
** DBConnection类中批量处理方法
 
*/
 
public boolean executeBatchByStatement(List sqlList) {
 
boolean ret = false;
 
if (conn != null) {
 
Statement stmt = null;
 
try {
 
conn.setAutoCommit(false);
 
stmt = conn.createStatement();
 
for (String s : sqlList) {
 
stmt.addBatch(s);
 
}
 
stmt.executeBatch();
 
conn.commit();
 
ret = true;
 
} catch (BatchUpdateException e) {
 
e.printStackTrace();
 
try {
 
conn.rollback();
 
} catch (SQLException e1) {
 
e1.printStackTrace();
 
}
 
} catch (SQLException e) {
 
e.printStackTrace();
 
} finally {
 
try {
 
conn.setAutoCommit(true);
 
if (stmt != null) {
 
stmt.close();
 
stmt = null;
 
}
 
} catch (SQLException e) {
 
e.printStackTrace();
 
}
 
}
 
}
 
return ret;
 
}
 
当然，这只是我个人想出来的一个办法，愚陋之见还望大家批评指教，共同提高啊。。。代码有什么问题的可以QQ我，共同商讨啊。。

 
sql文件导入大字段,大字段超过4000则报错文本超过长度,
 
import java.io.BufferedReader;
 
import java.io.File;
 
import java.io.FileInputStream;
 
import java.io.InputStream;
 
import java.io.InputStreamReader;
 
import java.io.StringReader;
 
import java.sql.Connection;
 
import java.sql.DriverManager;
 
import java.sql.PreparedStatement;
 
import java.sql.SQLException;
 
import java.util.ArrayList;
 
import java.util.List;
 
import oracle.jdbc.OracleDriver;
 
public class ClobTest {
 
String url = "jdbc:oracle:thin:@192.168.1.102:1521:orcl";
 
String user = "YNZJ";
 
String pwd = "YNZJ";
 
//String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
 
//String user = "hczz";
 
//String pwd = "hczz";
 
private void clobImport(List list) throws SQLException {
 
DriverManager.registerDriver(new OracleDriver());
 
Connection conn = DriverManager.getConnection(url, user, pwd);// 得到连接对象
 
PreparedStatement stmt = null;
 
for (String sql : list) {
 
String content = "";
 
try {
 
sql = sql.trim();
 
int num = sql.lastIndexOf("', '");
 
content = sql.substring(num + 4, sql.length() - 3);
 
sql = sql.substring(0, num) + "', ?)";
 
sql = sql.replace("\"YNZJ\".\"ENET_WORKBOOK\"", "ENET_WORKBOOK_bak");
 
stmt = conn.prepareStatement(sql);// 加载SQL语句
 
stmt.setCharacterStream(1, new StringReader(content), content.length());
 
stmt.executeUpdate();
 
} catch (Exception e) {
 
System.out.println(e.getMessage());
 
System.out.println(content);
 
System.out.println(sql);
 
}
 
}
 
stmt.close();
 
conn.close();
 
}
 
private List duqu(String path) throws Exception {
 
String code = resolveCode(path);
 
File file = new File(path);
 
InputStream is = new FileInputStream(file);
 
InputStreamReader isr = new InputStreamReader(is, code);
 
BufferedReader bufferedReader = new BufferedReader(isr);
 
List strings = new ArrayList();
 
String str = null;
 
String sql_d = "";
 
int num = 0;
 
int ks = 0;
 
int dz = 200;
 
int jz = ks + dz;
 
while ((str = bufferedReader.readLine()) != null) {
 
sql_d = sql_d + str;
 
if (str.endsWith("');") && sql_d != "") {
 
if (num >= ks) {
 
strings.add(sql_d);
 
}
 
sql_d = "";
 
num++;
 
}
 
if (num == jz) {
 
break;
 
}
 
}
 
System.out.println(strings.size());
 
return strings;
 
}
 
private String resolveCode(String path) throws Exception {
 
InputStream inputStream = new FileInputStream(path);
 
byte[] head = new byte[3];
 
inputStream.read(head);
 
String code = "gb2312"; // 或GBK
 
if (head[0] == -1 && head[1] == -2)
 
code = "UTF-16";
 
else if (head[0] == -2 && head[1] == -1)
 
code = "Unicode";
 
else if (head[0] == -17 && head[1] == -69 && head[2] == -65)
 
code = "UTF-8";
 
inputStream.close();
 
return code;
 
}
 
public static void main(String[] args) throws Exception {
 
ClobTest a = new ClobTest();
 
String path = "D:\\WORD_BOOK超长字段错误数据(1).txt";
 
a.clobImport(a.duqu(path));
 
}
 
}

 
今天做了一个用java导入sql脚本的功能，前台上传一个sql文件，后台先保存本地，然后进行导入。主要代码如下：String cmd = "cmd /c sqlplus usr/pwd@{//服务器IP:1521/}sid @d:\\1.sql>d:\\t.log";
 
Process p = null;
 
try {
 
p = Runtime.getRuntime().exec(cmd);
 
} catch (IOException e) {
 
e.printStackTrace();
 
} finally {
 
if (p != null) {
 
try {
 
p.waitFor();
 
} catch (InterruptedException e) {
 
e.printStackTrace();
 
}
 
p.destroy();
 
}
 
}
 
注：1.sql文件中必须要有/
 
2.如果没有在sql最后一行加入exit则，执行完后，process实例仍然出于等待中，具体可以自行尝试cmd中执行。如需sql执行完程序关闭则最后一行加入exit。
 
3.上面的服务器IP和端口号是可选的，如果你已经配置了Net manager，则直接引用配置的sid就行了。