Running a .sql script using MySQL with JDBC
JavaSqlMysqlJdbcJava Problem Overview
I am starting to use MySQL with JDBC.
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql:///x", "x", "x");
stmt = conn.createStatement();
stmt.execute( "CREATE TABLE amigos" +
"("+
"id int AUTO_INCREMENT not null,"+
"nombre char(20) not null,"+
"primary key(id)" +
")");
I have 3-4 tables to create and this doesn't look good.
Is there a way to run a .sql script from MySQL JDBC?
Java Solutions
Solution 1 - Java
Ok. You can use this class here (posted on pastebin because of file length) in your project. But remember to keep the apache license info.
It's ripoff of the iBatis ScriptRunner with dependencies removed.
You can use it like this
Connection con = ....
ScriptRunner runner = new ScriptRunner(con, [booleanAutoCommit], [booleanStopOnerror]);
runner.runScript(new BufferedReader(new FileReader("test.sql")));
That's it!
Solution 2 - Java
I did a lot of research on this and found a good util from spring. I think using SimpleJdbcTestUtils.executeSqlScript(...)
is actually the best solution, as it is more maintained and tested.
Edit: SimpleJdbcTestUtils
is deprecated. You should use JdbcTestUtils
. Updated the link.
Solution 3 - Java
Spring Framework's ResourceDatabasePopulator
may help. As you said you're using MySQL and JDBC, let's assume you have a MySQL-backed DataSource
instance ready. Further, let's assume your MySQL script files are classpath-locatable. Let's assume you are using WAR layout and the script files are located in a directory src/main/webapp/resources/mysql-scripts/...
or src/test/resources/mysql-scripts/...
. Then you can use ResourceDatabasePopulator
to execute SQL scripts like this:
import org.springframework.jdbc.datasource.init.ResourceDatabasePopulator;
import javax.sql.DataSource;
DataSource dataSource = getYourMySQLDriverBackedDataSource();
ResourceDatabasePopulator rdp = new ResourceDatabasePopulator();
rdp.addScript(new ClassPathResource(
"mysql-scripts/firstScript.sql"));
rdp.addScript(new ClassPathResource(
"mysql-scripts/secondScript.sql"));
try {
Connection connection = dataSource.getConnection();
rdp.populate(connection); // this starts the script execution, in the order as added
} catch (SQLException e) {
e.printStackTrace();
}
Solution 4 - Java
For simple sql script splitted by ';' you can use this simple function. It remove comments and run statements one by one
static void executeScript(Connection conn, InputStream in)
throws SQLException
{
Scanner s = new Scanner(in);
s.useDelimiter("/\\*[\\s\\S]*?\\*/|--[^\\r\\n]*|;");
Statement st = null;
try
{
st = conn.createStatement();
while (s.hasNext())
{
String line = s.next().trim();
if (!line.isEmpty())
st.execute(line);
}
}
finally
{
if (st != null)
st.close();
}
}
Solution 5 - Java
@Pantelis Sopasakis
Slightly modified version on GitHub: https://gist.github.com/831762/
Its easier to track modifications there.
Solution 6 - Java
Regarding SQL script runner (which I'm also using), I noticed the following piece of code:
for (int i = 0; i < cols; i++) {
String value = rs.getString(i);
print(value + "\t");
}
However, in the API documentation for the method getString(int) it's mentioned that indexes start with 1, so this should become:
for (int i = 1; i <= cols; i++) {
String value = rs.getString(i);
print(value + "\t");
}
Second, this implementation of ScriptRunner does not provide support for DELIMITER statements in the SQL script which are important if you need to compile TRIGGERS or PROCEDURES. So I have created this modified version of ScriptRunner: http://pastebin.com/ZrUcDjSx which I hope you'll find useful.
Solution 7 - Java
Another interesting option would be to use Jisql to run the scripts. Since the source code is available, it should be possible to embed it into an application.
Edit: took a careful look at it; embedding it inside something else would require some modification to its source code.
Solution 8 - Java
Can you use this:
public static void executeSQL(File f, Connection c) throws Exception {
BufferedReader br = new BufferedReader(new FileReader(f));
String sql = "", line;
while ((line = br.readLine()) != null) sql += (line+"\n");
c.prepareCall(sql).execute(sql);
}
This function gets SQL file and DB connection.
Then it reads the file line-by-line using BufferedReader from java.io.
And, finally, executes the read statements.
Java 8+ version:
public static void executeSQL(Path p, Connection c) throws Exception {
List<String> lines = Files.readAllLines(p);
String s = String.join("\n", lines.toArray(new String[0]));
c.prepareCall(s).execute(s);
}
Solution 9 - Java
Write code to:
- Read in a file containing a number of SQL statements.
- Run each SQL statement.
Solution 10 - Java
For Oracle PL/SQL, the Oracle JDBC-driver indeed supports executing entire SQL-scripts including stored procedures and anonymous blocks (PL/SQL specific notation), see
Can the JDBC Drivers access PL/SQL Stored Procedures?
The Oracle JDBC driver FAQ has more info:
> Oracle JDBC drivers support execution > of PL/SQL stored procedures and > anonymous blocks. They support both > SQL92 escape syntax and Oracle PL/SQL > block syntax. The following PL/SQL > calls would work with any Oracle JDBC > driver:
// SQL92 syntax
CallableStatement cs1 = conn.prepareCall
( "{call proc (?,?)}" ) ; // stored proc
CallableStatement cs2 = conn.prepareCall
( "{? = call func (?,?)}" ) ; // stored func
// Oracle PL/SQL block syntax
CallableStatement cs3 = conn.prepareCall
( "begin proc (?,?); end;" ) ; // stored proc
CallableStatement cs4 = conn.prepareCall
( "begin ? := func(?,?); end;" ) ; // stored func
It should be possible to read in a file and feed the content to the prepareCall()-method.
Solution 11 - Java
Maven SQL Plugin Use this plugin to execute SQL statements a file or list of files through
- sqlCommand
- srcFiles 3.fileset configurations
Solution 12 - Java
There isn't really a way to do this.
You could either run the mysql command line client via Runtime.exec(String[]) and read this article when you decide for this option
Or try using the ScriptRunner (com.ibatis.common.jdbc.ScriptRunner) from ibatis. But it's a bit stupid to include a whole library just to run a script.