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?
Write code to:
Spring Framework's
ResourceDatabasePopulator
may help. As you said you're using MySQL and JDBC, let's assume you have a MySQL-backedDataSource
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 directorysrc/main/webapp/resources/mysql-scripts/...
orsrc/test/resources/mysql-scripts/...
. Then you can useResourceDatabasePopulator
to execute SQL scripts like this: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:
It should be possible to read in a file and feed the content to the prepareCall()-method.
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.
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.
For simple sql script splitted by ';' you can use this simple function. It remove comments and run statements one by one