CODEWITHSUNDEEP
javaterminalpsql
Edgar Peixoto
Edgar Peixoto

Reputation: 559

Importing sql file using psql in a Java aplication

I need to create a method that receives (databaseIp, databaseName, port, user, password, filePath). The idea is to import a sql file in database like terminal: 

psql -h databaseIp -p port -U user -d databaseName  
Password for user postgres: password
databaseName=# \\i filePath
databaseName=# \\q

I use the following method to send commands to the terminal

public static String execute(String command) {
    StringBuilder sb = new StringBuilder();
    String[] commands = new String[]{"/bin/sh", "-c", command};
    try {
        Process proc = new ProcessBuilder(commands).start();
        BufferedReader stdInput = new BufferedReader(new InputStreamReader(proc.getInputStream()));

        BufferedReader stdError = new BufferedReader(new InputStreamReader(proc.getErrorStream()));

        String s = null;
        while ((s = stdInput.readLine()) != null) {
            sb.append(s);
            sb.append("\n");
        }

        while ((s = stdError.readLine()) != null) {
            sb.append(s);
            sb.append("\n");
        }
    } catch (IOException e) {
        e.printStackTrace();
    }
    return sb.toString();
}

but It seens that it sends only one command at a time and when the psql asks for passwords the result given is incorrect. How can I send related commands to the terminal using java?

Upvotes: 0

Views: 505

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324731

Ugh, why would you want to do this?

Use PgJDBC to communicate with PostgreSQL from Java.

If you really must invoke psql, say if you're sourcing an SQL file that uses \ commands, build a command with ProcessBuilder and invoke psql non-interactively in batch mode. You can prompt the user for their password and then pass it to psql in the PGPASSWORD environment variable so you never need to interact with the process, you just launch it and wait for it to terminate.

Something like the (untested):

 cmd = new List<String>();
 cmd.add(path_to_psql);
 cmd.add("-q"); // Quiet mode
 cmd.add("-w"); // no password prompt
 cmd.add("-X"); // ignore any user psqlrc
 cmd.add("-1"); // Run in a single transaction
 cmd.add("-v"); // Set a variable as follows:
 cmd.add("ON_ERROR_STOP=1"); // If anything goes wrong, abort the whole job
 cmd.add("-f"); // input file is:
 cmd.add(input_file_path);
 if (username_arg != null) {
    cmd.add("-U");
    cmd.add(username_arg);
 }
 // and so on for database name, etc, then:

 ProcessBuilder pb = new ProcessBuilder(cmd);
 Map<String, String> env = pb.environment();
 if (psql_password != null) {
     // User supplied a password
     env.put("PGPASSWORD", psql_password);
 }
 // ... blah blah, the usual execute it, wait for it to finish,
 // check return value, etc.

If psql fails to connect the first time with error code 2 you can prompt the user to enter their password and re-try. Unfortunately error code 2 isn't very specific; it will also be returned for errors related to invalid host names, etc, so you might want to capture psql's stderr and display that to the user along with your prompt.

Upvotes: 1

Related Questions