Running .sql script using MySQL with JDBC

Question

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 that doesn't look good.

Is there a way to run a .sql script from MySQL JDBC?

Answer 1

Ok You can use this class here (hosted on pastebin due to file length) in your project. But do not forget to save apache license information.

JDBC ScriptRunner

This is ripoff from 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"))); 

What is it!

Answer 2

I did a lot of research on this and found a good util from spring . I think using SimpleJdbcTestUtils.executeSqlScript(...) is actually a better solution, as it is more supported and verified.

Edit: SimpleJdbcTestUtils deprecated. You must use JdbcTestUtils . Updated link.

Answer 3

Spring Framework ResourceDatabasePopulator can help. As you said, you are using MySQL and JDBC, suppose you have a ready-made MySQL DataSource instance. Also, suppose your MySQL script files are classpath-locatable. Suppose you use the WAR layout and the script files are in the src/main/webapp/resources/mysql-scripts/... directory or src/test/resources/mysql-scripts/... Then you can use ResourceDatabasePopulator to execute SQL scripts as follows:

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

Answer 4

For a simple sql script, separated by ';' You can use this simple function. It removes comments and runs 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(); } } 

Answer 5

@ Pantelis Sopasakis

A slightly modified version on GitHub: https://gist.github.com/831762/

It’s easier to track changes there.

Answer 6

Regarding the SQL script runner (which I also use), I noticed the following code snippet:

 for (int i = 0; i < cols; i++) { String value = rs.getString(i); print(value + "\t"); } 

However, the API documentation for the getString (int) method mentioned that indexes start at 1 , so this should become:

 for (int i = 1; i <= cols; i++) { String value = rs.getString(i); print(value + "\t"); } 

Secondly, this ScriptRunner implementation does not provide support for DELIMITER statements in SQL script, which are important if you need to compile TRIGGERS or PROCEDURES. So I created this modified version of ScriptRunner: http://pastebin.com/ZrUcDjSx , which I hope you find useful.

Answer 7

Another interesting option is to use Jisql to run scripts. Since the source code is available, it can be included in the application.

---

Edit: carefully looked at him; embedding it in something else will require some modification of the source code.

Answer 8

Enter a code:

• Read in a file containing several SQL statements.
• Run each SQL statement.

Answer 9

For Oracle PL / SQL, the Oracle JDBC driver really supports the execution of all SQL scripts, including stored procedures and anonymous blocks (specific to PL / SQL records), see

Can JDBC drivers access PL / SQL stored procedures?

The Oracle JDBC driver FAQ contains additional information:

Oracle JDBC drivers support PL / SQL stored procedure execution and anonymous blocks. They support both SQL92 escape code syntax and Oracle PL / SQL block syntax. The following PL / SQL calls will 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 the file and pass the contents to the prepareCall () method.

Answer 10

Maven SQL Plugin Use this plugin to execute SQL file instructions or a list of files through

• SQLCommand
• srcFiles Configurations 3.fileset

Answer 11

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

Answer 12

There really is no way to do this.

You can start the mysql command-line client through Runtime.exec (String []) and read this article when you decide to do this.

Or try using ScriptRunner (com.ibatis.common.jdbc.ScriptRunner) from ibatis . But it's a little silly to include an entire library just to run a script.