java scriptrunner_[转] java 执行sql脚本的3种方式 (ant,ibatis,ScriptRunner)

调用 ant.jar 的 SQLExec 执行 SQL 脚本文件

* @author Unmi

*/

public class AntExecSql {

/**

* @param args

*/

public static void main(String[] args) {

SQLExec sqlExec = new SQLExec();

//设置数据库参数

sqlExec.setDriver("oracle.jdbc.driver.OracleDriver");

sqlExec.setUrl("jdbc:oracle:thin:@10.128.x.x:1521:xxsid");

sqlExec.setUserid("xxuser");

sqlExec.setPassword("xxpass");

//要执行的脚本

sqlExec.setSrc(new File("src/data.sql"));

//有出错的语句该如何处理

sqlExec.setOnerror((SQLExec.OnError)(EnumeratedAttribute.getInstance(

SQLExec.OnError.class, "abort")));

sqlExec.setPrint(true); //设置是否输出

//输出到文件 sql.out 中；不设置该属性，默认输出到控制台

sqlExec.setOutput(new File("src/sql.out"));

sqlExec.setProject(new Project()); // 要指定这个属性，不然会出错

sqlExec.execute();

}

}

ibatis：

package com.ibatis.jpetstore.test;

import java.sql.DriverManager;

import java.util.Properties;

import com.ibatis.common.jdbc.ScriptRunner;

import com.ibatis.common.resources.Resources;

import com.mysql.jdbc.Connection;

public class DBTestSQL {

public static void main(String[] args) {

try {

Properties props = Resources.getResourceAsProperties("properties/database.properties");

String url = props.getProperty("url");

String driver = props.getProperty("driver");

String username = props.getProperty("username");

String password = props.getProperty("password");

System.out.println(url);

if(url.equals("jdbc:mysql://localhost:3306/jpetstore1")) {

Class.forName(driver).newInstance();

Connection conn = (Connection) DriverManager.getConnection(url, username, password);

ScriptRunner runner = new ScriptRunner(conn, false, false);

runner.setErrorLogWriter(null);

runner.setLogWriter(null);

runner.runScript(Resources.getResourceAsReader("ddl/mysql/jpetstore-mysql-schema.sql"));

runner.runScript(Resources.getResourceAsReader("ddl/mysql/jpetstore-mysql-dataload.sql"));

}

} catch (Exception e) {

e.printStackTrace();

}

}

}

ScriptRunner(com.ibatis.common.jdbc.*)用法

ScriptRunner类用于执行SQL语句,例如创建数据库schema，或传入缺省或测试数据库等等。

从下面的例子可以认识到它的易用性:

例子1：使用现成的数据库连接

Connection conn=getConnection();//some method to get a Connection

ScriptRunner runner=new ScriptRunner();

runner.runScript(conn,Resources.getResourceAsReader("com/some/resource/path/initialize.sql"));

conn.close();

例子2：使用新的数据库连接

ScriptRunner runner=new ScriptRunner("com.some.Driver","jdbc:url://db","login","password");

runner.runScript(conn,new FileReader("/user/local/db/scripts/initialize-db.sql"));

例子3：使用新创建的数据连接

Properties props= getProperties();//some properties form somewhere

ScriptRunner runner =new ScriptRunner(props);

runner.runScript(conn,new FileReader("/user/local/db/scripts/initialize-db.sql"));