How to Execute SQL Script File in Java

Question

I want to execute an SQL script file in Java without reading the entire file content into a big query and executing it   Is there any other standard way

User · Answer

For my simple project the user should be able to select SQL-files which get executed. As I was not happy with the other answers and I am using Flyway anyway I took a closer look at the Flyway code. DefaultSqlScriptExecutor is doing the actual execution, so I tried to figure out how to create an instance of DefaultSqlScriptExecutor.

Basically the following snippet loads a String splits it into the single statements and executes one by one. Flyway also provides other LoadableResources than StringResource e.g. FileSystemResource. But I have not taken a closer look at them.

As DefaultSqlScriptExecutor and the other classes are not officially documented by Flyway use the code-snippet with care.

public static void execSqlQueries(String sqlQueries, Configuration flyWayConf) throws SQLException {
  // create dependencies FlyWay needs to execute the SQL queries
  JdbcConnectionFactory jdbcConnectionFactory = new JdbcConnectionFactory(flyWayConf.getDataSource(),
      flyWayConf.getConnectRetries(),
      null);
  DatabaseType databaseType = jdbcConnectionFactory.getDatabaseType();
  ParsingContext parsingContext = new ParsingContext();
  SqlScriptFactory sqlScriptFactory = databaseType.createSqlScriptFactory(flyWayConf, parsingContext);
  Connection conn = flyWayConf.getDataSource().getConnection();
  JdbcTemplate jdbcTemp = new JdbcTemplate(conn);
  ResourceProvider resProv = flyWayConf.getResourceProvider();
  DefaultSqlScriptExecutor scriptExec = new DefaultSqlScriptExecutor(jdbcTemp, null, false, false, false, null);
  
  // Prepare and execute the actual queries
  StringResource sqlRes = new StringResource(sqlQueries);
  SqlScript sqlScript = sqlScriptFactory.createSqlScript(sqlRes, true, resProv);
  scriptExec.execute(sqlScript);
}

User · Answer

Since JDBC  doesn t support this option the best way to solve this question is executing command lines via the Java Program  Bellow is an example to postgresql   private void executeSqlFile          try            Runtime rt   Runtime getRuntime             String executeSqlCommand    psql -U  user  -h  domain  -f  script name   dbName             Process pr   rt exec             int exitVal   pr waitFor             System out println  Exited with error code     exitVal           catch  Exception e            System out println e toString

User · Answer

Try this code   String strProc             DECLARE  n                 sys date DATE                            BEGIN n                               SELECT SYSDATE INTO sys date FROM dual  n                            END  n    try      DriverManager registerDriver   new oracle jdbc driver OracleDriver           Connection connection   DriverManager getConnection   jdbc oracle thin  your db IP 1521 your db SID   user   password          PreparedStatement psProcToexecute   connection prepareStatement strProc       psProcToexecute execute     catch  Exception e        System out println e toString

User · Answer

The simplest external tool that I found that is also portable is jisql - https   www xigole com software jisql jisql jsp   You would run it as   java -classpath lib jisql jar             lib jopt-simple-3 2 jar             lib javacsv jar               home scott postgresql postgresql-8 4-701 jdbc4 jar      com xigole util sql Jisql -user scott -password blah           -driver postgresql                                             -cstring jdbc postgresql   localhost 5432 scott -c             -query  select   from test

User · Answer

JDBC does not support this option  although a specific DB driver may offer this   Anyway  there should not be a problem with loading all file contents into memory

User · Answer

If you use Spring you can use DataSourceInitializer    Bean public DataSourceInitializer dataSourceInitializer  Qualifier  dataSource   final DataSource dataSource        ResourceDatabasePopulator resourceDatabasePopulator   new ResourceDatabasePopulator        resourceDatabasePopulator addScript new ClassPathResource   data sql         DataSourceInitializer dataSourceInitializer   new DataSourceInitializer        dataSourceInitializer setDataSource dataSource       dataSourceInitializer setDatabasePopulator resourceDatabasePopulator       return dataSourceInitializer         Used to set up a database during initialization and clean up a   database during destruction    https   docs spring io spring-framework docs current javadoc-api org springframework jdbc datasource init DataSourceInitializer html

User · Answer

The Apache iBatis solution worked like a charm  The script example I used was exactly the script I was running from MySql workbench  There is an article with examples here  https   www tutorialspoint com how-to-run-sql-script-using-jdbc    text You 20can 20execute 20  to 20pass 20a 20connection 20object  amp text Register 20the 20MySQL 20JDBC 20Driver method 20of 20the 20DriverManager 20class  This is what I did  pom xml dependency  lt  -- IBATIS SQL Script runner from Apache  https   mvnrepository com artifact org apache ibatis ibatis-core  -- gt   lt dependency gt       lt groupId gt org apache ibatis lt  groupId gt       lt artifactId gt ibatis-core lt  artifactId gt       lt version gt 3 0 lt  version gt   lt  dependency gt   Code to execute script  import java io BufferedReader  import java io File  import java io FileReader  import java io Reader  import java sql Connection     import org apache ibatis jdbc ScriptRunner     import lombok extern slf4j Slf4j    Slf4j public class SqlScriptExecutor        public static void executeSqlScript File file  Connection conn  throws Exception           Reader reader   new BufferedReader new FileReader file            log info  quot Running script from file   quot    file getCanonicalPath             ScriptRunner sr   new ScriptRunner conn           sr setAutoCommit true           sr setStopOnError true           sr runScript reader           log info  quot Done  quot

User · Answer

There is great way of executing SQL scripts from Java without reading them yourself as long as you don t mind having a dependency on Ant  In my opinion such a dependency is very well justified in your case  Here is sample code  where SQLExec class lives in ant jar   private void executeSql String sqlFilePath        final class SqlExecuter extends SQLExec           public SqlExecuter                 Project project   new Project                project init                setProject project               setTaskType  sql                setTaskName  sql                         SqlExecuter executer   new SqlExecuter        executer setSrc new File sqlFilePath        executer setDriver args getDriver         executer setPassword args getPwd         executer setUserid args getUser         executer setUrl args getUrl         executer execute

User · Answer

No  you must read the file  split it into separate queries and then execute them individually  or using the batch API of JDBC    One of the reasons is that every database defines their own way to separate SQL statements  some use    others    some allow both or even to define your own separator

User · Answer

There is no portable way of doing that  You can execute a native client as an external program to do that though   import java io    public class CmdExec      public static void main String argv          try         String line        Process p   Runtime getRuntime   exec           psql -U username -d dbname -h serverhost -f scripfile sql          BufferedReader input           new BufferedReader            new InputStreamReader p getInputStream            while   line   input readLine       null            System out println line                 input close              catch  Exception err          err printStackTrace                   Code sample was extracted from here and modified to answer question assuming that the user wants to execute a PostgreSQL script file

User · Answer

Flyway library is really good for this       Flyway flyway   new Flyway        flyway setDataSource dbConfig getUrl    dbConfig getUsername    dbConfig getPassword         flyway setLocations  classpath db scripts        flyway clean        flyway migrate      This scans the locations for scripts and runs them in order  Scripts can be versioned with V01  name sql so if just the migrate is called then only those not already run will be run  Uses a table called  schema version  to keep track of things  But can do other things too  see the docs  flyway   The clean call isn t required  but useful to start from a clean DB  Also  be aware of the location  default is  classpath db migration    there is no space after the      that one caught me out

User · Answer

You cannot do using JDBC as it does not support    Work around would be  including iBatis iBATIS is a persistence framework and call the Scriptrunner constructor as shown in iBatis  documentation    Its not good to include a heavy weight persistence framework like ibatis in order to run a simple sql scripts any ways which you can do using command line      mysql -u root -p db name  lt  test sql

[java] How to Execute SQL Script File in Java?

Examples related to java

Examples related to sql-scripts