3 ways to execute SQL scripts in Java (Ant,ibatis,scriptrunner)

package com.unmi;
import java.io.*;
import org.apache.tools.ant.*;
import org.apache.tools.ant.taskdefs.*;
import org.apache.tools.ant.types.*;
* *
*Call sqlexec of ant.jar to execute SQL script file
* @author Unmi
* /
public class AntExecSql {
* *
* @param args
* /
public static void main(String[] args) {
SQLExec sqlExec = new SQLExec();
//Set database parameters
sqlExec.setDriver("oracle.jdbc.driver.OracleDriver");
sqlExec.setUrl("jdbc:oracle:thin:@10.128.x.x:1521:xxsid");
sqlExec.setUserid("xxuser");
sqlExec.setPassword("xxpass");
//Script to execute
sqlExec.setSrc(new File("src/data.sql"));
//How to deal with statements with errors
sqlExec.setOnerror((SQLExec.OnError)(EnumeratedAttribute.getInstance(
SQLExec.OnError.class, "abort")));
Sqlexec. Setprint (true); / / set whether to output
//Output to the file sql.out; do not set this property, default output to the console
sqlExec.setOutput(new File("src/sql.out"));
Sqlexec. Setproject (New project()); / / you need to specify this property, otherwise an error will occur
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.*;
* *
*Call sqlexec of ant.jar to execute SQL script file
* @author Unmi
* /
public class AntExecSql {
* *
* @param args
* /
public static void main(String[] args) {
SQLExec sqlExec = new SQLExec();
//Set database parameters
sqlExec.setDriver("oracle.jdbc.driver.OracleDriver");
sqlExec.setUrl("jdbc:oracle:thin:@10.128.x.x:1521:xxsid");
sqlExec.setUserid("xxuser");
sqlExec.setPassword("xxpass");
//Script to execute
sqlExec.setSrc(new File("src/data.sql"));
//How to deal with statements with errors
sqlExec.setOnerror((SQLExec.OnError)(EnumeratedAttribute.getInstance(
SQLExec.OnError.class, "abort")));
Sqlexec. Setprint (true); / / set whether to output
//Output to the file sql.out; do not set this property, default output to the console
sqlExec.setOutput(new File("src/sql.out"));
Sqlexec. Setproject (New project()); / / you need to specify this property, otherwise an error will occur
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. *) usage
The ScriptRunner class is used to execute SQL statements, such as creating a database schema, passing in a default or test database, and so on. Its ease of use can be recognized by the following examples:
Example 1: using an off the shelf database connection
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();
Example 2: using a new database connection
ScriptRunner runner=new ScriptRunner("com.some.Driver","jdbc:url://db","login","password");
runner.runScript(conn,new FileReader("/user/local/db/scripts/initialize-db.sql"));
Example 3: using the newly created data connection
Properties props= getProperties();//some properties form somewhere
ScriptRunner runner =new ScriptRunner(props);
runner.runScript(conn,new FileReader("/user/local/db/scripts/initialize-db.sql"));