java导出sql脚本 java 执行sql脚本

有需求就要有行动，有行动就会有收获！

一、使用ant提供的工具类执行sql脚本(测试ok)

导入ant.jar文件后，创建测试类如下：

import java.io.File;
import org.apache.tools.ant.Project;
 import org.apache.tools.ant.taskdefs.SQLExec;
 import org.apache.tools.ant.types.EnumeratedAttribute;public class ExecuteSqlFile {
  public static void main(String[] args) {  SQLExec sqlExec = new SQLExec();

// 设置数据库参数
   sqlExec.setDriver("org.gjt.mm.mysql.Driver");
   sqlExec.setUrl("jdbc:mysql://127.0.0.1:3306/seal_lite?useUnicode=true&characterEncoding=UTF-8");
   sqlExec.setUserid("hugy");
   sqlExec.setPassword("123456");
   // 要执行的脚本
   sqlExec.setSrc(new File("src/seal_lite.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执行sql脚本(未测试)

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的用法

参考：http://czjxdm.iteye.com/blog/27930

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"));