0

EDIT After your advices, the code and the errors. I got an error "ORA-00955: name is already used by an existing object" now. The sequences creation create this error each time a DataStoreManager constructor is called.

Here is the code now : 

public class DataStoreManager {


    private Connection        connection;
    private PreparedStatement lookForAccount;
    private PreparedStatement addAccount;
    private PreparedStatement updaterBalance;

    private PreparedStatement reachOperation;

public DataStoreManager(String url, String user, String password) throws DataStoreException, SQLException {
      try {
          connection = DriverManager.getConnection(url,user,password);
          connection.setAutoCommit(false);

          this.createDB();

          lookForAccount = connection.prepareStatement("SELECT * FROM accounts WHERE account_id = ?");
          addAccount     = connection.prepareStatement("INSERT INTO accounts (account_id, balance) VALUES (? , 0.0)");
          updaterBalance = connection.prepareStatement("UPDATE accounts SET balance = ? WHERE account_id = ?"); 

          reachOperation = connection.prepareStatement("SELECT * FROM operations WHERE account_id = ? AND (date BETWEEN ? AND ?)");



      } catch (SQLException error) {
            error.printStackTrace();
            throw error;
      }
    }

public void createDB() throws DataStoreException {

      try {
              Statement statmnt = connection.createStatement();

            statmnt.executeUpdate("DROP TABLE operations");
            statmnt.executeUpdate("DROP TABLE accounts");

             statmnt.executeUpdate("CREATE TABLE accounts ( account_id INTEGER, balance DOUBLE PRECISION)");
             statmnt.executeUpdate("CREATE TABLE operations ( operation_id INTEGER, account_id INTEGER, amount DOUBLE PRECISION, mydate DATE NOT NULL)");



              //implement primary Key constraint.
              statmnt.executeUpdate("ALTER TABLE accounts ADD CONSTRAINT accounts_PK PRIMARY KEY ( account_id )");

              //implement foreign Key constraint
              statmnt.executeUpdate("ALTER TABLE operations ADD CONSTRAINT accountID FOREIGN KEY ( account_id )"
                    + "REFERENCES accounts ( account_id )");

              // implement sequence for auto implement.
              statmnt.executeUpdate("CREATE SEQUENCE operationID MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10");
              statmnt.executeUpdate("CREATE SEQUENCE logID MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10");

              // implement balance always positive constraint
              statmnt.executeUpdate("ALTER TABLE accounts ADD CONSTRAINT balance_must_be_positive CHECK (balance >= 0)");

              // Auto operation.
              statmnt.executeUpdate("CREATE TRIGGER addingOrder BEFORE UPDATE ON accounts FOR EACH ROW BEGIN"
                    + " INSERT INTO operations(operation_id, account_id, amount, mydate) "
                    + " VALUES (operationID.nextval, :OLD.account_id, :NEW.amount - :OLD.amount, SYSDATE) END");


          connection.commit();

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


  public boolean createAccount(int number) throws DataStoreException, SQLException {

      try {

         lookForAccount.setInt(1, number);
         Boolean result = lookForAccount.execute();

         if(result == true)
         {
                addAccount.setInt(1, number);
                addAccount.executeUpdate();
                connection.commit();
                return true;

         }
         else
         { return false;}

      } catch (SQLException error) {
            error.printStackTrace();
            throw error;
      }
  }

Here are my errors : 

   java.sql.SQLException: ORA-00955: name is already used by an existing object

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
    at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
    at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:891)
    at services.DataStoreManager.createDB(DataStoreManager.java:105)
    at services.DataStoreManager.<init>(DataStoreManager.java:55)
    at application.SimpleTest.main(SimpleTest.java:145)
Running single-user tests...
Running multi-users tests...
java.sql.SQLException: ORA-00955: name is already used by an existing object

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
    at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
    at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:891)
    at services.DataStoreManager.createDB(DataStoreManager.java:105)
    at services.DataStoreManager.<init>(DataStoreManager.java:55)
    at application.SimpleTest.main(SimpleTest.java:160)
user#0[services.DataStoreManager@e00c09]: starting
user#0[services.DataStoreManager@e00c09]: exiting
java.sql.SQLException: ORA-00955: name is already used by an existing object

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
    at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
    at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:891)
    at services.DataStoreManager.createDB(DataStoreManager.java:105)
    at services.DataStoreManager.<init>(DataStoreManager.java:55)
    at application.SimpleTest.main(SimpleTest.java:160)
user#1[services.DataStoreManager@111f9b]: starting
user#1[services.DataStoreManager@111f9b]: exiting
java.sql.SQLException: ORA-00955: name is already used by an existing object

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)user#2[services.DataStoreManager@1363271]: starting
user#2[services.DataStoreManager@1363271]: exiting

    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
    at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
    at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:891)
    at services.DataStoreManager.createDB(DataStoreManager.java:105)
    at services.DataStoreManager.<init>(DataStoreManager.java:55)
    at application.SimpleTest.main(SimpleTest.java:160)
java.sql.SQLException: ORA-00955: name is already used by an existing object

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
    at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
    at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:891)
    at services.DataStoreManager.createDB(DataStoreManager.java:105)
    at services.DataStoreManager.<init>(DataStoreManager.java:55)
    at application.SimpleTest.main(SimpleTest.java:160)
user#3[services.DataStoreManager@114f313]: starting
user#3[services.DataStoreManager@114f313]: exiting
java.sql.SQLException: ORA-00955: name is already used by an existing object

    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)user#4[services.DataStoreManager@d767dc]: starting
user#4[services.DataStoreManager@d767dc]: exiting

    at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:590)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1973)
    at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1119)
    at oracle.jdbc.driver.OracleStatement.executeNonQuery(OracleStatement.java:2191)
    at oracle.jdbc.driver.OracleStatement.doExecuteOther(OracleStatement.java:2064)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2989)
    at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:891)
    at services.DataStoreManager.createDB(DataStoreManager.java:105)
    at services.DataStoreManager.<init>(DataStoreManager.java:55)
    at application.SimpleTest.main(SimpleTest.java:160)
Csi
  • 526
  • 3
  • 22
  • 1
    When are you calling createDB()? Are you sure you're connected to the same datasource? Same user? You could be missing a synonym if your tables are not in the same schema. – Mark Leiber May 22 '15 at 12:42
  • Ah, you're right, i forgot to call createDB in the constructor. I added it but I got the same errors :S I don't really know how to see if i'm on the same datasource or schema (i'm used same user to login sqlplus and in the connection method) – Csi May 22 '15 at 12:56
  • Please edit your question to show the code as it is now. Also show the stack trace. – Mark Leiber May 22 '15 at 13:01
  • 2
    Probably unrelated, but nevertheless: don't use `executeQuery()` for DDL statements. Use `execute()` or `executeUpdate()` –  May 22 '15 at 13:04
  • @Mark Leiber : i edited. – Csi May 22 '15 at 13:13
  • Well, you're running "DROP TABLE logs" every time and not recreating it, so every time you run you'll get an error saying the table doesn't exist. – Mark Leiber May 22 '15 at 13:14
  • @Csi, please look at a_horse_with_no_name's comment. executeQuery should only be used for DML, not DDL. – Mark Leiber May 22 '15 at 13:24
  • @Mark Leiber I deleted table 'logs' (i figured out i don't need it), but i got the exactly same errors. Considering what you said, i thought the problem was me trying to remove a not yet created table (i want to drop it if it exists actually). Seems better now. Still got different errors though (ORA-00955 : name is already used by an existing object. each time i try to call the class constructor). – Csi May 22 '15 at 13:28
  • Leave the DROP statements in place (except for Logs, since you never create it again). But change executeQuery to execute or executeUpdate for your DML statements (your drops and creates). You also don't need to commit after those statements -- it happens automatically. – Mark Leiber May 22 '15 at 13:30
  • @Mark Leiber : I edited the original post as asked and deleted my answer. – Csi May 22 '15 at 13:41
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/78538/discussion-between-csi-and-mark-leiber). – Csi May 22 '15 at 13:46

1 Answers1

1

You will have problems in your createDB() method, if any of your SQL statements generate an error (for example if a table doesn't exist and you try to drop it). At that point your code will jump into the catch block and none of your database objects will be created. You need to wrap each DROP statement in a try/catch block so that any error can be caught and handled (or in your case it's probably safe to ignore) and then the CREATE statements will run.

You also are only running DROP statements for your tables. You need to do the same for your sequences and trigger.

Your trigger is also invalid. The accounts table only has columns for account_id and balance. You can't reference :old or :new values for the columns in the operations table. So :old.amount and :new.amount are invalid, and :old.account_id is the account_id from the accounts table (which turns out to be the same as the account_id in the operations table since it's a foreign key). You're also missing two semicolons. It should look like this:

statmnt.executeUpdate("CREATE TRIGGER addingOrder BEFORE UPDATE ON accounts FOR EACH ROW BEGIN"
                    + " INSERT INTO operations(... columns ...) "
                    + " VALUES (... values ...); END;");

The way you have things coded, you are trying to do some cleanup first by DROPPING everything, then you CREATE everything. When your test is done you will be left with all of your database objects in place. I would suggest that you reverse your order so that your create your objects when your test starts, and then do a tear down at the end in which you DROP everything so that your database stays clean.

Mark Leiber
  • 3,118
  • 2
  • 13
  • 22