今天做了一个用java导入sql脚本的功能，前台上传一个sql文件，后台先保存本地，然后进行导入。主要代码如下：String cmd = "cmd /c sqlplus usr/[email protected]{//服务器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就行了。
原文：http://biyusheng.blog.51cto.com/2626140/1438073

 import org.apache.tools.ant.Project;

        import org.apache.tools.ant.taskdefs.SQLExec;       

 

        String linkStr = "jdbc:"+dbaddress+":3306/"+dbname+"?useUnicode=true&characterEncoding=UTF-8";

        SQLExec sqlExec = new SQLExec();

        sqlExec.setEncoding("UTF-8");

        sqlExec.setDriver("com.mysql.jdbc.Driver");

        sqlExec.setUrl(linkStr);

        sqlExec.setUserid(dbusername);

        sqlExec.setPassword(dbpassword);

        sqlExec.setSrc(new File(sqlPath)); //sqlPath = SQL文件的路径

        sqlExec.setProject(new Project());

        sqlExec.execute();


注意：使用此方法进行sql导入实现时，需要将sqlPath指定的SQL文件中的注释全部去掉，否则导入时会报错。

最近在做一个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));
}
}