Need Help executing postgresql query through java process

Question

I need to execute a bat file that executes a PostgreSQL query in commandline using psql.exe in windows 10 and Java-8 .I have to execute the query through bat file only for testing the bat file. Below is the contents of the bat file

BatFileContents:

postgresql\bin\psql.exe -U username -p dbport -h 127.0.0.1 -c "
insert into table1 values(value1,value2);
insert into table2 values(value1,value2);
insert into table3 values(value1,value2) database"

I tried using java ProcessBuilder to call the bat file but pgsql is asking for password.Below is the code i used to call the bat file and provide password but it doesn't work as expected.

Code:

Main class:

import java.io.BufferedWriter;
import java.io.File;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.io.OutputStreamWriter;
import java.util.Scanner;

public class ProcessTest {


    public static void main(String args[])
    {

        Process process=null;

        String batFile="\"c:\\batfilepath\\test.bat\"";

        ProcessBuilder processBuilder=new ProcessBuilder(batFile);

        try {

            //process=Runtime.getRuntime().exec(batFile);

            process=processBuilder.start();

            StreamEater inputStreamEater=new StreamEater(process.getInputStream());

            StreamEater errorStreamEater=new StreamEater(process.getErrorStream());

            inputStreamEater.start();

            errorStreamEater.start();

            BufferedWriter writer=new BufferedWriter(new OutputStreamWriter(process.getOutputStream()));

            writer.write("password");

            writer.newLine();

            writer.close();

            int errorcode=process.waitFor();

            System.out.println("errorcode:"+errorcode);

        }
        catch(Exception exception)
        {
            exception.printStackTrace();
        }

    }


}

StreamEater class

class StreamEater extends Thread
{


    private  InputStream inputStream=null;

    public StreamEater(InputStream stream)
    {
        this.inputStream=stream;
    }


    public void run()
    {
        System.out.println("Stream Eater thread started");

        Scanner scanner=new Scanner(new InputStreamReader(inputStream));

        String message="";

        StringBuilder sb=new StringBuilder("");

        try
        {
            System.out.println("before reading message");

            while(scanner.hasNextLine())
            {
                message=scanner.nextLine();

                sb.append(message);

                sb.append("\n");

            }

            scanner.close();

            System.out.println("after reading message  "+sb.toString());

        }
        catch(Exception exception)
        {
            exception.printStackTrace();
        }

        System.out.println("Stream Eater thread ended");

    }

}

Can anyone point out what mistake i have done.Is there any other way to send input to the bat file.

The bat file needs to be tested seperately without java in the tool i am working on .So i haven't used jdbc here. — tazz, Dec 05 '18 at 14:36
You have it all there, just 2 lines of code are missing. If you want I can do it, but take a closer look what's missing. Your code is fine, just security risks here and there regarding passing a password thru stream. — MS90, Dec 05 '18 at 14:44
@MS90 - If you have a solution post an answer. Based on edits and other comments OP seems committed to this approach regardless of other solutions or security concerns. — Freiheit, Dec 05 '18 at 18:13

Freiheit · Accepted Answer · 2018-12-05T15:34:18.210

2

There are several issues with this code. I will answer your direct questions first.

Passing Password Arguments (Direct Answer)

The pgsql command forces the user to enter a password interactively. This SO answer describes two techniques to handle this:

Use a .pgpass file. You can create this file independently of your program and alter the BAT file to reference it.
Set a PGPASSWORD environment variable. You can alter the BAT file to do this. You can also set this in ProcessBuilder from your application with code like processBuilder.environment().put("PGPASSWORD ", "PASSWORD");

Use JDBC (Improve your code)

Instead of having your Java app call a batch file, use JDBC. JDBC literally exists so that Java can connect to a database and run queries against that database.

This would avoid the challenges of calling a batch script and let your application directly interact with the database.

edited Dec 05 '18 at 15:34

answered Dec 05 '18 at 14:34

Freiheit

8,408
6
59
101

I am not allowed to alter the bat file though. – tazz Dec 05 '18 at 14:39
Very good explaination for passing password arguments, I am not quite sure why people go code-naked at all. – MS90 Dec 05 '18 at 14:39
@tazz see my edit. I hope this directly answers your question. If you set PGPASSWORD in ProcessBuilder then launch the BAT it should work. – Freiheit Dec 05 '18 at 15:07
u mean to add it like processBuilder.environment().put("PGPASSWORD ", "PASSWORD"); right – tazz Dec 05 '18 at 15:15
I added that in environment map but still the same stuck code. – tazz Dec 05 '18 at 15:51
Does setting PGPASSWORD and running the BAT manually allow it to work or not? IOW is it a problem with the environment variable or with the pg call or with your Java code? – Freiheit Dec 05 '18 at 18:12

Adam Ostrožlík · Answer 2 · 2018-12-05T14:56:43.577

0

I think that your thread will end really soon since the scanner has no lines to read. It could be blocked operation though but you should loop this thread. The answer above is also good but try this without affecting you code too much:

while (stillReading) {
    while(scanner.hasNextLine())
         {
           message=scanner.nextLine();
           sb.append(message);
           sb.append("\n");

         }
Thread.sleep(1000);
}

The main problem is that there is no input in InputStream when the thread starts.

edited Dec 05 '18 at 14:56

answered Dec 05 '18 at 14:37

Adam Ostrožlík

1,256
1
10
16

Is it possible to just pass the input password alone.I don't need the output of the command.So if i remove the inputstream reading would it work. – tazz Dec 05 '18 at 14:59

Need Help executing postgresql query through java process

BatFileContents:

Code:

2 Answers2