Auto Increment ID Field Table in the Oracle Database

[JiaPei Jen]

I have been using the MySQL. And the ID field of the database table is AUTO INCREMENT. When I insert a new record into a database table, I can have a statement like:

   public void createThread( String receiver, String sender, String title, 
                             String lastPostMemberName, String threadTopic, 
                             String threadBody, Timestamp threadCreationDate, 
                             Timestamp threadLastPostDate, int threadType, 
                             int threadOption, int threadStatus, int threadViewCount, 
                             int threadReplyCount, int threadDuration )
                             throws MessageDAOSysException

and I do not have to put the ID variable in the above method. The table will give the new record an ID number that is equivalent to the ID number of the last record plus one automatically.

Now, I am inserting a new record into an Oracle database table. I am told that I cannot do what I am used to doing with the MySQL database.

How do I revise the createThread method while I have no idea about what the next sequence number shall be?

[JiaPei Jen]

OKay, the table into which I am trying to insert new records has a sequence: dhsinfo_content-grp_seq

The DBA has done that part. I am worrying about the Java part. To give you an example the origin of my confusion, I have:

   public void storeContent( UpdateBean updateBean ) throws ContentDAOSysException
   {
      int groupID = updateBean.getGroupID();
      String pageName = updateBean.getPageName();
      int componentID = updateBean.getComponentID();
      String content = updateBean.getContent();
      Timestamp contentCreationDate = updateBean.getContentCreationDate();
 
      pd.updateContent( groupID, pageName, componentID, content, contentCreationDate );
   } 

All the parameters of the updateContent() methods have known values. Where in the code we use that sequence key for INSERT? Do I insert new record this way:

      String insertSQL = "INSERT INTO DHSInfoContentGroup( ID, GroupID, Name, ComponentID, Content, CreationDate ) 
VALUES (dhsinfo_content-grp_seq.NEXTVAL FROM DUAL, ?, ?, ?, ?, ?)";
 
      try
      {
         conn = DBConnection.getDBConnection();
         stmt = conn.prepareStatement( insertSQL );
 
         stmt.setInt( 1, id );
         stmt.setInt( 2, groupID );
         stmt.setString( 3, pageName );
         stmt.setInt( 4, componentID );
         stmt.setString( 5, content );
         stmt.setTimestamp( 6, contentCreationDate );
 
         stmt.executeUpdate();
       }

[edited to fix page allignment]
[ September 30, 2004: Message edited by: Jeanne Boyarsky ]

[Colin Fletcher]

 String insertSQL = "INSERT INTO DHSInfoContentGroup( ID, GroupID, Name, ComponentID, Content, CreationDate ) VALUES (dhsinfo_content-grp_seq.NEXTVAL FROM DUAL, ?, ?, ?, ?, ?)";
 
      try
      {
         conn = DBConnection.getDBConnection();
         stmt = conn.prepareStatement( insertSQL );
 
         stmt.setInt( 1, id );
         stmt.setInt( 2, groupID );
         stmt.setString( 3, pageName );
         stmt.setInt( 4, componentID );
         stmt.setString( 5, content );
         stmt.setTimestamp( 6, contentCreationDate );
 
         stmt.executeUpdate();
       }

From your code section above, you try to get the sequence value from dual in the insert statement.

You don't need the "FROM DUAL" above...

If you want to see the next value of the sequence you can
SELECT <someseq>.NEXTVAL FROM DUAL

If you want to use the sequence in a query, you can.. if you have a table called customers... this would work as well....

SELECT cust.name_first, cust.name_last, <someseq>.NEXTVAL
FROM customers cust

[JiaPei Jen]

Do you mean this is what I should do:

String insertSQL = "INSERT INTO DHSInfoContentGroup( ID, GroupID, Name, ComponentID, Content, CreationDate ) VALUES (dhsinfo_content-grp_seq.NEXTVAL, ?, ?, ?, ?, ?)";
      try
      {
         conn = DBConnection.getDBConnection();
         stmt = conn.prepareStatement( insertSQL );
         stmt.setInt( 1, id );
         stmt.setInt( 2, groupID );
         stmt.setString( 3, pageName );
         stmt.setInt( 4, componentID );
         stmt.setString( 5, content );
         stmt.setTimestamp( 6, contentCreationDate );
         stmt.executeUpdate();
      }

[JiaPei Jen]

Let me make sure that I am doing it right.

In my case,

1. the table name in the Oracle database is DHSInfoContentGroup
2. ID is the column name of that table for auto increment
3. dhsinfo_content_grp_seq has been taken care of by DBA

Therefore, the code should look like:

   public void updateContent( int groupID, String pageName, int componentID,
                              String content, Timestamp contentCreationDate )  
                             throws contentDAOSysException
   {
      Connection conn = null;
      PreparedStatement stmt = null;
 
      FOR EACH ROW WHEN (new.ID IS NULL) 
      // how does the computer know it is the ID for which table in the 
      // database at this step?  I have a number of tables in the database.
      // The ID is the column name for all those tables.
      BEGIN
        SELECT dhsinfo_content_grp_seq.NEXTVAL
        INTO :ID
        FROM DUAL;
      END;
 
      String insertSQL = "INSERT INTO DHSInfoContentGroup( ID, GroupID, Name, ComponentID, Content, CreationDate ) VALUES (?, ?, ?, ?, ?, ?)";
      try
      {
         conn = DBConnection.getDBConnection();
         stmt = conn.prepareStatement( insertSQL );
         stmt.setInt( 1, id );
         stmt.setInt( 2, groupID );
         stmt.setString( 3, pageName );
         stmt.setInt( 4, componentID );
         stmt.setString( 5, content );
         stmt.setTimestamp( 6, contentCreationDate );
         stmt.executeUpdate();
       }
       catch 
       {
         ................
       }
       finally
       {
         ................
       }

[JiaPei Jen]

I am very confused. The source of the confusion is switching from the MySQL database to the Oracle database. If I use the Oracle database:

// This part is for the databast table creation
 
-- Component primary key sequence
CREATE SEQUENCE dhsinfo_page_content_seq
    START WITH 0;
-- Trigger for updating the Component primary key
CREATE OR REPLACE TRIGGER DHSInfoPageContent_INSERT_TRIGGER
    BEFORE INSERT ON DHSInfoPageContent //DHSInfoPageContent is the table name
    FOR EACH ROW WHEN (new.ID IS NULL) // ID is the column name for auto increment
    BEGIN
        SELECT dhsinfo_page_content_seq.Nextval
        INTO :ID
        FROM DUAL;
    END;/

What should I do with my Java code in database operation?

   public void updateContent( int groupID, String pageName, int componentID,
                              String content, Timestamp contentCreationDate )
                               throws contentDAOSysException
   // The above Java statement does not have a value to insert into the ID column 
   // in the DHSInfoPageContent table
   {
      Connection conn = null;
      PreparedStatement stmt = null;
 
      // what to do with the INSERT INTO below.  Note the paramether ID.
      String insertSQL = "INSERT INTO DHSInfoPageContent( ID, GroupID, Name, ComponentID, Content, CreationDate ) VALUES (?, ?, ?, ?, ?, ?)";
      try
      {
         conn = DBConnection.getDBConnection();
         stmt = conn.prepareStatement( insertSQL );
         stmt.setInt( 1, id ); // Is this Java statement redundant?
         stmt.setInt( 2, groupID );
         stmt.setString( 3, pageName );
         stmt.setInt( 4, componentID );
         stmt.setString( 5, content );
         stmt.setTimestamp( 6, contentCreationDate );
         stmt.executeUpdate();
       }
       catch
       {
         ................
       }
       finally
       {
         ................
       }

[Colin Fletcher]

[B]
Do you mean this is what I should do:

code:

 
String insertSQL = "INSERT INTO DHSInfoContentGroup( ID, GroupID, Name, ComponentID, Content, CreationDate ) VALUES (dhsinfo_content-grp_seq.NEXTVAL, ?, ?, ?, ?, ?)";
      try
      {
         conn = DBConnection.getDBConnection();
         stmt = conn.prepareStatement( insertSQL );
         stmt.setInt( 1, id );
         stmt.setInt( 2, groupID );
         stmt.setString( 3, pageName );
         stmt.setInt( 4, componentID );
         stmt.setString( 5, content );
         stmt.setTimestamp( 6, contentCreationDate );
         stmt.executeUpdate();
      }
 

[/B]

Exactly - for Oracle

[Colin Fletcher]

For MySQL you would have to

1. remove the field from the query
2. change the field in the table to auto increment

-C

[JiaPei Jen]

What about the statement:

        stmt = setInt( 1, id ); // There is no 'id' (unrecognized symbol)

or this is the correct way to do:

      String insertSQL = "INSERT INTO DHSInfoPageContent( ID, GroupID, Name, ComponentID, Content, CreationDate ) VALUES (dhsinfo_page_content_seq.NEXTVAL, ?, ?, ?, ?, ?)";
      try
      {
         conn = DBConnection.getDBConnection();
         stmt = conn.prepareStatement( insertSQL );
         stmt.setInt( 1, groupID );
         stmt.setString( 2, pageName );
         stmt.setInt( 3, componentID );
         stmt.setString( 4, content );
         stmt.setTimestamp( 5, contentCreationDate );
         stmt.executeUpdate();
       }

[Colin Fletcher]

The second one :-)

[JiaPei Jen]

Thank you. It works.