java 執行sql指令碼的3種方式 (ant,ibatis,ScriptRunner)

標籤：style   blog   http   color   java   使用   os   io   

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