I have this sql script that I would like to execute in a java program. It takes on 3 parameters: dropper_id, to_char(begin_dt), to_char(end_dt). How would I do this?
The program is held on a Unix server.
The sql script is also located on the Unix server.
I think i would most likely want to execute the program by command line, but how do I execute it with parameters?
Process p = Runtime.getRuntime().exec ("psql sql_script.sql");
You can do that using JDBC and PreparedStatement, see these tutorials:
Here are some examples:
If both your Java application and the SQL are located on the same server, then you just need to load it and execute as given above. See examples here:
If it's just running the SQL (i.e. the whole purpose of your application is to run that SQL and do nothing else), you may want to look at the alternative solutions such as using Ant for the task, see this:
or using whatever is your database command line utility, see some examples here:
This very much depends on your circumstances, so you would have to see what is important in your case to decide which way to go.
In general, the correct way to execute database commands in a Java application is to use a Java-based database connection using JDBC or an even higher-level ORM such as Hibernate. These are preferred for a number of reasons, most importantly because they can report and deal with error paths far more easily than an external command.
However, if you have a reason not to use JDBC, you can use ProcessBuilder as described in this question How To Execute Native Commands In Java?
