running psql command in a java program

Question

I have a requirement where using java code I need to create/delete/alter postgres tables.

I have written a program as below:

public static void main(String[] args) throws IOException {
        System.out.println("Hello World!");
        Process p = Runtime.getRuntime().exec("psql -U postgres -d testdb -h localhost -p 5433 -f D:\test.sql");
    }

test.sql file looks like below,

Create TABLE MyTable1
(
    VersionNumber VARCHAR(32) NOT NUll
);

Create TABLE MyTable2
(
    VersionNumber VARCHAR(32) NOT NUll
);

Create TABLE MyTable3
(
    VersionNumber VARCHAR(32) NOT NUll
);

problem:

If I run the same psql command:

psql -U postgres -d testdb -h localhost -p 5433 -f D:\test.sql

in command line, it asks for password and the tables get created.

But in java program, there is not provision to provide password. Please let me know on how to achieve it.

score 2 · Accepted Answer · 2019-01-07 11:17:04Z

2

You can use a connection URL instead:

psql -f d:\test.sql postgresql://postgres:password@localhost:5433/testdb

edited Jan 7, 2019 at 11:17

answered Jan 7, 2019 at 10:13

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

vinod hy Over a year ago

i am using postgres 10.5 version. is that fine

Aleks G · Accepted Answer · 2019-01-04 10:13:31Z

0

First of all, it would be better to actually use JDBC to connect to the database and run your SQL statements. If you're set on using command-line psql, you can use PGPASSWORD environment variable to set the password:

String command = "psql -U postgres -d testdb -h localhost -p 5433 -f D:\test.sql";
String[] envVars = { "PGPASSWORD=yourpassword" };
Process p = Runtime.getRuntime().exec(command, envVars);

You can read the password from the stdin if necessary. But, again, it's better to do this over JDBC.

answered Jan 4, 2019 at 10:13

Aleks G

57.5k34 gold badges178 silver badges280 bronze badges

1 Comment

vinod hy Over a year ago

Sorry.. This doesnt work. if i set PGPASSWORD directly as system variable, then it works. If i remove the entry from system variables and try to use your approach, it doesnt work. Can you please check the same from your side also once.

Gen Eva · Accepted Answer · 2020-03-14 14:58:23Z

Regarding to a_horse_with_no_name answer and using Java-code launch of psql at Windows OS and using ProcessBuilder following slight modification works for me:

    ProcessBuilder builder = new ProcessBuilder();
    String connUrl = "postgresql://user:password@host:port/dbname";
    String sqlFileToProcess = "--file=Disk:\\path\\to\\file.sql";
    builder.command("psql.exe", sqlFileToProcess, connUrl);
    builder.directory(new File("Disk:\\Path\\to\\psql\\containing\\folder"));
    Process process = builder.start();
    int exitCode = 0;
    try {
         exitCode = process.waitFor();
         int len;
         if ((len = process.getErrorStream().available()) > 0) {
             byte[] buf = new byte[len];
             process.getErrorStream().read(buf);
             System.err.println("Command error:\t\""+new String(buf)+"\"");
         }
    } catch (InterruptedException e) {
         e.printStackTrace();
    }
   assert exitCode == 0;

Don`t know why, but if:

-f Disk:\\path\\to\\file.sql

as argument in Java-code throws:

Command error:  "unrecognized win32 error code: 123 psql: error:  Disk:/path/to/file.sql: Invalid argument
"

(pay attention to redirection of slashes and backslashes in otput and input)

Collectives™ on Stack Overflow

running psql command in a java program

3 Answers 3

1 Comment

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Related