Trying to run sql commands from java

Question

I am trying to run sql commands from java using ProcessBuilder. My problem si that i cant use the same cmd from different functions. For example, i want to run an sql file when i press a button. After that, i want to maintain the connection to sql plus and commit or rollback the transaction by pressing other button. How to keep the process from a button to other?

My first button code:

public class MyExec {

    public static ProcessBuilder builder;
    public static Process p;
    public static BufferedReader bri;
    public static BufferedReader bre;
    public static BufferedWriter p_stdin;

     public static void executeScript(String scriptName, String alias, String path, TextArea txtArea) throws IOException {

     //obtaining sql script from a share folder
      NtlmPasswordAuthentication userCred = new NtlmPasswordAuthentication("domain",
                    "user", "pass");
      SmbFile smbFile=new SmbFile("path"  + scriptName, userCred);
      File file = new File("D://" + scriptName);
      try (InputStream in = smbFile.getInputStream()) {
        Files.copy(smbFile.getInputStream(), file.toPath());
      }

      //init shell
      builder = new ProcessBuilder( "cmd" );
      builder.redirectErrorStream(true);
      try {
        p = builder.start();
      } catch (IOException e) {
        System.out.println(e);
      }
      //get stdin of shell
      p_stdin =new BufferedWriter(new OutputStreamWriter(p.getOutputStream()));

      //executing commands in cmd
      try {
        p_stdin.write("sqlplus sys/sys@" + alias +" as sysdba");
        p_stdin.newLine();
        p_stdin.flush();
        p_stdin.write("@"+file.toPath());
        p_stdin.newLine();
        p_stdin.flush();
      } catch (IOException e) {
        System.out.println(e);
      }

      // write stdout of shell (=output of all commands)
      String line;
      bri = new BufferedReader(new InputStreamReader(p.getInputStream()));
                   bre = new BufferedReader(new InputStreamReader(p.getErrorStream()));
      while ((line = bri.readLine()) != null) {
        txtArea.appendText(line + "\n");
        System.out.println(line + "\n");
      }                
      while ((line = bre.readLine()) != null) {
        txtArea.appendText(line + "\n");
        System.out.println(line + "\n");
      }

      System.out.println("Done.");
    }

}

Second button code:

Thread t1 = new Thread(new Runnable() {
   public void run() {
     //commit the sql script which was run with the first button
     try {
       MyExec.p_stdin.write("commit");
       MyExec.p_stdin.newLine();
       MyExec.p_stdin.flush();
       MyExec.p_stdin.write("exit");
       MyExec.p_stdin.newLine();
       MyExec.p_stdin.flush();

       //output the result of commiting sql script
       String line;
       BufferedReader bri = new BufferedReader(new InputStreamReader(MyExec.p.getInputStream()));
       BufferedReader bre = new BufferedReader (new InputStreamReader(MyExec.p.getErrorStream()));
        while ((line = MyExec.bri.readLine()) != null) {
          txtArea.appendText(line + "\n");
          System.out.println(line + "\n");
        }
        MyExec.bri.close();
        while ((line = MyExec.bre.readLine()) != null) {
          txtArea.appendText(line + "\n");
          System.out.println(line + "\n");
        }
        MyExec.bri.close();
        System.out.println("Done.");
     } catch (IOException e1) {
        e1.printStackTrace();
     }

    }
    });
      //event on Commit button, running the thread above
    public void commit(ActionEvent e) {   
      t1.start();   
    }

Hint: you never ever have empty catch blocks. Then: read about java naming conventions. you dont use "_" in variable names; variable names go camelCase. And really important: you want us to spend our time to help you. So you take the time it takes to properly format/indent all of your input. — GhostCat
– GhostCat, Commented Sep 12, 2016 at 8:03
And then: avoid code duplication. The code for updating your txtArea is almost identical. Don't do that. — GhostCat
– GhostCat, Commented Sep 12, 2016 at 8:05
Please provide some more explanations of your code, what you are trying to do and what issue you are experiencing. It is hard to find an error in dozens or even hundreds lines of code. Create a Minimal, Complete, and Verifiable example and please read How do I ask a good question?. — Martin Nyolt
– Martin Nyolt, Commented Sep 12, 2016 at 8:07
@GhostCat, i dont use " " in my variables name, where u see that? Whatever, can u answer my question? — Aleksandr8894
– Aleksandr8894, Commented Sep 12, 2016 at 8:08
Have a class say QueryRunner with methods like Connect, BeginTran, Commit/RollbackTran, Execute. Ensure your button listeners use the same instance of the above class. — Serg
– Serg, Commented Sep 12, 2016 at 8:25

GhostCat · Accepted Answer · 2016-09-12 09:05:00Z

0

The comment from Serg is giving you the right direction: you need a clear separation of concerns here. You don't write one application that somehow does everything in one place.

You start by defining an interface such as

interface SqlProvider {
  public void connectConnection() ... 
  public void beginTransaction() ...
  public void commit() ..
  public void rollback()
  public void endTransaction()
  public voide closeConnection()

each of them with reasonable parameters and throw lists.

Then you start implementing that interfaces; similar to what you have done within MyExec so far; but: you hide all that ProcessBuilder things from the users of your interface.

Meaning: in your current code, you have public fields; and you expect your "button" code to use those fields. And that is wrong. Instead, your button code should be calling (non-static!) methods on an instance of that interface. And the implementation of that interface makes sure that the correct things happen.

You absolutely do not continue your current approach (where one class opens streams, and other classes just "append" to those streams; or read from them).

answered Sep 12, 2016 at 9:05

GhostCat

141k28 gold badges191 silver badges262 bronze badges

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

8 Comments

Aleksandr8894 Over a year ago

what u say is not for a JDBC connection? yes, u are right. I am trying to open cmd from a function and append commands from other function

GhostCat Over a year ago

I am saying that no matter what you do; you should abstract from that. A user of that interface should not need to know. All he knows is that there are several methods that need to be called in a certain order; and that do meaningful things.

Aleksandr8894 Over a year ago

i know abot public fields, but i use them to win time, this is a test code, after o find a solution i will make it...

GhostCat Over a year ago

Seriously, that is what "abstractions" are about: I just made a proposal how things could look like. But one final thing: what exactly is your problem with your current code? I guess it is doing what you want it to do? Or are there errors/bugs?

Aleksandr8894 Over a year ago

So...from the first button, i open a cmd session, i connect to sql plus and i run a script. Until now, all is working. BUT, i am trying to get the same cmd session which i opened and append commands from the second button commit or rollback. HERE is my problem, the second button is not doing anything, it is not getting the cmd session..This is my main problem, the rest of the code, it doesn't matter, i will improve it, but tell me how to solve the problem of using the same cmd session from different functions...

|

Collectives™ on Stack Overflow

Trying to run sql commands from java

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related