Java DAO Insert Layer in Oracle Database

Question

I have a DAO Layer to insert into DERBY db like this,

try {
    long id = dbAccess.insert(connection,
        "INSERT INTO BOOKS(NAME, AUTHORS, PUBLISHYEAR, AVAIL) VALUES (?, ?, ?, ?)",
        new ScalarHandler<BigDecimal>(),
        book.getName(),
        book.getAuthors(),
        book.getPublishedYear(),
        book.isAvailable()
           ).longValue();
    return id;
} catch (Exception e) {
    e.printStackTrace();
}

return -1L;

How do I translate this if i am using ORACLE DB as the data source?

Are you encountering some sort of problem when you try this against an Oracle database? — Bob Jarvis - Слава Україні, Dec 21 '15 at 16:09

score 2 · Accepted Answer · edited May 23 '17 at 12:30

I don't have any idea about your database but you need to define the method to insert your table id and you need to create connection class for your oracle database

Also you need to mange your table id Increment you should create an trigger to increment table id after each insert operation i will post an method that select the max id from table , you can find oracle trigger to increase sequence in the link below

How to create id with AUTO_INCREMENT on Oracle?

first we need to create method that return max id from your table

   public static int getMaxBookID(Connection connection){
   int id=0;
    String sql = "SELECT  NVL(MAX(ID),0)+1  FROM BOOK ";
    try{
    PreparedStatement statement = connection.prepareStatement(sql);
    if(statement!=null){
    try{
      ResultSet results = statement.executeQuery();
      if(results != null){
        try{
          if(results.next()){
            id = results.getInt(1);
          }
        }
        catch(Exception resultSetException) {resultSetException.printStackTrace();
        }
        results.close();
      }
    }
    catch(Exception statmentExcption){statmentExcption.printStackTrace();
    }
    statement.close();
  }
} catch (Exception generalException){generalException.printStackTrace();
}


return id;

}

this two methods are used to open and close your connection

  private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
  private static final String DB_CONNECTION = "jdbc:oracle:thin:@//host:1526/databasename";
private static final String DB_USER = "username";
private static final String DB_PASSWORD = "passowrd";

public static Connection lockConnection() {

    Connection dbConnection = null;

    try {

        Class.forName(DB_DRIVER);

    } catch (ClassNotFoundException e) {

        System.out.println(e.getMessage());

    }

    try {

        return DriverManager.getConnection(
                DB_CONNECTION, DB_USER, DB_PASSWORD);



    } catch (SQLException e) {

        System.out.println(e.getMessage());

    }

    return dbConnection;

}
public static void closeMyConnection(Connection connection) {

    try {

        connection.close();


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

}

now You can insert your information into your table

public int AddBook(String name,String auth,String year , String avail){
int id=0;

 Connection connection = lockConnection();
boolean ok = false;
String sql = "INSERT INTO BOOKS(ID,NAME, AUTHORS, PUBLISHYEAR, AVAIL)"


        + " VALUES(?,?,?,?,?)";


try{
  PreparedStatement statement = connection.prepareStatement(sql);
  if(statement!=null){
     statement.setInt(1,getMaxBookID(connection));
    statement.setString(2,name);
    statement.setString(3,auther);
    statement.setString(4,year);
    statement.setString(5,avail);




 try{
      int count = statement.executeUpdate();
      ok = count == 1;
      if(!ok)id=0;
    }
    catch(Exception statmentExcption){statmentExcption.printStackTrace();statmentExcption.printStackTrace(); return 0 ;
    }
    statement.close();
  }
} catch (Exception generalException){generalException.printStackTrace(); generalException.printStackTrace(); return 0;
}

closeMyConnection(connection);


return id;


}

That code doesn't return the ID of the newly inserted row, which I believe is the main point of the question (my interpretation anyway). — Andreas, Dec 21 '15 at 16:17
If two threads do that in parallel, they will try to add the same ID. Also, it's not very efficient. Using a SEQUENCE object to assign ID would be better, but makes SQL statement Oracle-specific, unless you do that in a trigger, but then how do you get value back? You use the version of `prepareStatement` that can return the value of a generated-value column. That way, you only have one turnaround to the database. — Andreas, Dec 21 '15 at 16:33
thats good , i have use prestatmnet in my post but because a don't have any idea about your table structure am advice you to make an trigger to avoid your ID duplication there is an example about how can i return sequence value from oracle data base you just need to change select query which i have post in getNextBookId after you have create an trigger and sequence "SELECT YOURSEQName.NEXTVAL FROM DUAL" and it will be worked correctly — Tareq Moh Qadommi, Dec 21 '15 at 16:37

score 1 · Answer 2 · answered Dec 21 '15 at 16:13

1

Since Oracle doesn't support IDENTITY columns, you have to do two things to make it work:

Create a TRIGGER to assign the ID value using a SEQUENCE.
Ask for the value by column name.

This means using the prepareStatement(String sql, String[] columnNames) version, not the prepareStatement(String sql, int autoGeneratedKeys) version, when building your PreparedStatement, because Oracle doesn't know which column is "auto-generated".

Using the name version works for all DBMS vendors, so using that makes your code vendor agnostic:

Oracle: Trigger
MySQL: AUTO_INCREMENT
MSSQL: IDENTITY
PostgreSQL: SERIAL
Derby: GENERATED ALWAYS AS IDENTITY
...

answered Dec 21 '15 at 16:13

Andreas

154,647
11
152
247

1

Oracle [12c does have support](https://oracle-base.com/articles/12c/identity-columns-in-oracle-12cr1) for identity columns. – Mick Mnemonic Dec 21 '15 at 17:18
@MickMnemonic Ahh yes, forgot that. Guess my brain was stuck on older versions. Since OP didn't specify version, I'll leave answer as a version-agnostic solution. – Andreas Dec 21 '15 at 17:50

Java DAO Insert Layer in Oracle Database

2 Answers2