2.ibatis方式执行脚本
首先引入ibatis-common-2.jar包
import com.ibatis.common.jdbc.ScriptRunner;
import com.ibatis.common.resources.Resources;
jpetstore测试代码如下:
- 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"));
例子3:使用新创建的数据连接
ScriptRunner runner =new ScriptRunner(props);
runner.runScript(conn,new FileReader("/user/local/db/scripts/initialize-db.sql"));
3.ant执行SQL脚本
- package com.unmi;
- import java.io.*;
- import org.apache.tools.ant.*;
- import org.apache.tools.ant.taskdefs.*;
- import org.apache.tools.ant.types.*;
- /**
- * 调用 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();
- }
- }
- package com.unmi;
- import java.io.*;
- import org.apache.tools.ant.*;
- import org.apache.tools.ant.taskdefs.*;
- import org.apache.tools.ant.types.*;
- /**
- * 调用 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();
- }
- }