java中执行sql脚本

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

参考：http://licg1234.blog.163.com/blog/static/139082333201222154919809/

一、使用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"));