java restful web service, can't figure how to execute a specific Query

Question

Hello there and thanks in advance for any reply, I'm creating a restful web service and I would like to know:

How to execute a "table exporting query" from eclipse

the query works, it backs up the file, the only problem is it's not being executed for some reason from eclipse

Important to add, I'm using oracle's database express.

in sqlplus: $exp USERID=hr/password TABLES=(hr.students) FILE=exp_tab.dmp

from cmd: sqlplus /as sysdba $exp USERID=hr/password TABLES=(hr.students) FILE=exp_tab.dmp

The method, where I attempted to execute such query is combined with anotherinsert query

My objective is to backup the table (to a file on a local drive) after every insert

note: Yes, I know I can create a trigger for that.

public static boolean insertUser(int id, String name, String gender, int grade) throws SQLException, Exception {
    boolean insertStatus = false;
    Connection dbConn = null;
    try {
        try {
            dbConn = DBConnection.createConnection();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        Statement stmt_backup = dbConn.createStatement();
        Statement stmt = dbConn.createStatement();
        String query = "INSERT into students(id, name, gender, grade) " +
                "values('"+id+"',"+"'"+name+"','"+gender+"','"+grade+"')";

        String query_backup = "$exp USERID=hr/password TABLES=(hr.students) FILE=exp_tab.dmp";
        stmt_backup.executeQuery(query_backup);
        stmt_backup.close();
        //System.out.println(query);
        int records = stmt.executeUpdate(query);
        //System.out.println(records);
        //When record is successfully inserted
        if (records > 0) {
            insertStatus = true;
        }
    } catch (SQLException sqle) {
        //sqle.printStackTrace();
        throw sqle;
    } catch (Exception e) {
        //e.printStackTrace();
        // TODO Auto-generated catch block
        if (dbConn != null) {
            dbConn.close();
        }
        throw e;
    } finally {
        if (dbConn != null) {
            dbConn.close();
        }
    }
    return insertStatus;
}

PS: I've tried doing that with one statement , and got the same result. Also I tried replacing executeQuery(query_backup); with executeUpdate(Query_backup);

Answer 1

A remark : The Runtime.exec(String[]) doesn't take into account environment variables. If you want to set environment variables, you must use Runtime.exec(String[],String[]).

The second parameter corresponds to the list of environment variables you want to set. The syntax for these strings is "<VARIABLE_NAME>=<VALUE>". But it is easier to put the path of your sqlplus command in myCommandWithParams[0]. It is up to you to see what is best for you. I don't have the context.

Answer 2

You did well to ask for help. That will save you a lot of time.

• First point: You need to understand better what a Java DB connection is done for. In fact, I think, but maybe I am wrong, that your mistake shows a lack of architectural understanding about client / server architecture (DBMS in the present context).

  Quick answer: You can only execute SQL statement through a client connection (whatever the technology you use for establishing that connection). So everything is good for your INSERT request but you cannot execute your sqlplus command through your JDBC connection.

• Second point: I find a little bit strange for a web service to make a backup of a table. What do you really want to do?

• Third point : if you want to execute a command line statement from Java, here is the solution (but in your case I think it is a bad idea):

  public class NewClass {
      final String userName = "myuser";
      final String myPassword = "password";
      public NewClass () {
        String[] myCommandWithParams=new String[4];
        myCommandWithParams[0]="sqlplus";
        myCommandWithParams[1]="USERID="+userName+"/"+myPassword;
        myCommandWithParams[2]="TABLES=(hr.students)";
        myCommandWithParams[3]="FILE=exp_tab.dmp";
        Runtime runtime = Runtime.getRuntime();
        try {
            runtime.exec(myCommandWithParams);
        } catch (SecurityException | IOException ex) {
            System.err.println("There is a problem for executing the command");
        } catch (IndexOutOfBoundsException ex) {
             System.err.println("myCommandWithParams can't be empty");
        }
      }
  }