4 Replies Latest reply on Jun 28, 2002 12:03 PM by John Smith

    Autonumber'ed primary keys

    Scott Newbie

      Hi.

      I've got JBoss connected to MS SQL server and all of my tables use autonumbered primary keys. I'd very much like to stick with CMP, but I'm worried that this type of functionality gets into BMP territory, and it's my understanding that mixing CMP and BMP is bad mojo. Does anyone know of a way to do this? Maybe with jaws custom insert statements? Perhaps some sort of ejbql statement in the ejbPostCreate() method? I'm still familiarizing myself with jaws and CMP, so any information you can provide would be greatly appreciated.

      Scott

        • 1. Re: Autonumber'ed primary keys
          Jason Gerard Newbie

          Nope, it's pure BMP. If you want to use CMP, you have to remove the identities and generate the ID yourself.

          • 2. Re: Autonumber'ed primary keys
            Roland Berger Newbie

            Bill Burke and Sacha Labourey write in the JBoss 3.0 Workbook to EJB:

            "At the time of writing of this workbook Jboss 3.0 was in beta and automatic primary key generation had not been implemented yet."

            Now the final releas 3.0.0 is out know. From your post I understand that automatic primary key generation is also in the release version 3.0.0 not implemented.
            Please clarify.

            Thank's
            Rolanb

            • 3. Re: Autonumber'ed primary keys
              Stanislav Nazarenko Newbie

              Hi, illerd.
              I've got the same problem as you.
              As far as I know, it cannot be resolved using standard JBoss 3.0.0 CMP methods. I've spent some time to think about different solutions and my solution is:
              First of all, I'm using custom generator of EJB code, so I'm not confused about mixing CMPs and little bit of JDBC.
              CMP entity beans are created as if there's no probs with auto-keys and are wrapped by session bean layer which hooks create() calls and does very simple thing: it runs JDBC INSERT statement (with the minimal set of fields: unique ones, just one ideally. I tried to preserve SQL statement as simple as possible, again: it's all generated by the source code generator, so it looks ugly but really easy to implement: just to fill out the xml generation script), retrieves the generated key value, calls findByPrimaryKey using this value to retrieve the reference to the newly created entity, and calls a bundle of setXXXX methods for CMP bean to complete the creation. So I never call create() method of CMPs directly but substitute it by JDBC insert + CMP updates.
              BUT: I would suggest NOT to use this solution if you don't use the intermediate session layer.
              P.S. If somebody have a better way to resolve this problem, please let us know.

              • 4. Re: Autonumber'ed primary keys
                John Smith Newbie

                I got a good idea from an O'Reilly book that I read. It
                pretty much sums up dropping the Auto Numbering from the
                tables and using one table to manage the next value in sequence.

                You could then use a local session bean to get the next value
                from that table.

                The algorithm would be:
                Lock the table.
                Get the value for that table name.
                update the value by one.
                unlock the table.

                Your table would look like
                CREATE TABLE Sequences AS
                (
                tablename nvarchar(30),
                seqId int(10)
                );

                Anyhow... I thought it was a pretty good solution if you're
                guaranteed that the EJB Application will be the only thing writing to the DB. If you have other applications you would have to change the way it insert rows to use the Sequences table.

                $0.02