1 2 3 Previous Next 31 Replies Latest reply on Feb 11, 2004 3:05 PM by mingdong

    delay-database-insert-until and autogenerated PKs

    jimjonah

      Does JBoss support a deployment tag (option) similar to Weblogic's "delay-database-insert-until"? It appears that the weblogic tag allows you to define "ejbCreate|ejbPostCreate|commit" as options. By defining "ejbCreate" as the choice you're telling the app server to write the database record right after the ejbCreate statement. Which means that doing a ctx.getPrimaryKey() in the ejbPostCreate should return the actual value of an autogenerated database column (Key).

      It seems that this would be much simpler than the bleeding edge get-generated-keys command and using Unknown primary keys.

      Basically I just want my PK class populated with the key that was just generated by the database. I know what the DB field type is and the java variable type, so I'd prefer not to go down the Unknown PK path.

      thoughts?

      Jim

        • 1. Re: delay-database-insert-until and autogenerated PKs
          sebastien_petrucci

          I agree ... I'm not really pleased with the fact that we need to use unknown pk in order to get auto-generated keys... It implies some problems, especially for CMR (how to reference an EJB pk if it's not declared ?).

          Concerning the "delay-database-insert-until", I think it's more used to make it possible to have non-null foreign keys when you do CMR (great feature) and to provide bulk inserts.

          Regards,
          Sebastien.

          • 2. Re: delay-database-insert-until and autogenerated PKs
            jimjonah

            Thanks.

            After reading lots of messages about people struggling with the same thing, one of them struck a cord. While generally we use databases as the persistence engine, that may not always be the case, hence the lack of built-in support for autogenerating keys, claimed on poster.

            The book EJB Design Patterns has a couple of solutions for generating unique keys so I'm going down that path. i.e. don't use the autogenerate feature and instead use a Block Sequence design pattern.

            Jim

            • 3. Re: delay-database-insert-until and autogenerated PKs
              sebastien_petrucci

              Hi,

              After some testing, I just discovered that we can use the auto-generated key feature without having to use an unknown PK.
              I'm using JBoss 3.2 RC2.

              I can post an example if someone is interested.

              Regards,
              Sebastien.

              • 4. Re: delay-database-insert-until and autogenerated PKs
                ronr

                please do.

                • 5. Re: delay-database-insert-until and autogenerated PKs
                  sebastien_petrucci

                  Ok :-)

                  Here is an example using MySQL and it's JDBC 3.0 driver.

                  The CMP 2.0 Entity bean named 'Foo' has 2 fields. 1 field called 'pk' of type Integer (I use MySQL, which can only auto-increment on numbers ...) and 1 field 'name' of type String.
                  We want 'pk' to be the PK of the bean generated by the DB, and 'name' to be a normal field.

                  First, you create everything exactly like 'pk' was a normal field. Then, in the jbosscmp-jdbc descriptor :


                  <ejb-name>Foo</ejb-name>
                  <table-name>foo</table-name>
                  <cmp-field>
                  <field-name>pk</field-name>
                  <column-name>foo_pk</column-name>
                  <auto-increment/>
                  </cmp-field>
                  <cmp-field>
                  <field-name>name</field-name>
                  <column-name>foo_name</column-name>
                  </cmp-field>
                  <entity-command name="mysql-get-generated-keys"/>


                  That's it. Nothing more to do.

                  The entity-command declaration could also be done in the section, but then it would apply to all the EJBs you declare.
                  Last advice : MAKE SURE THAT YOUR ENTITY BEANS THAT DO NOT NEED AUTO-INCREMENTED KEYS USE <entity-command name="default"/>, OTHERWISE THE PK WILL NOT BE INSERTED AT BEAN CREATION !!

                  Good luck,
                  Sebastien.

                  • 6. Re: delay-database-insert-until and autogenerated PKs
                    dklehmann

                    Hi guys,

                    I have followed Sebastien's example, but am still having a problem.

                    It seems that JBoss (I'm using version 3.2.0RC3) recognizes the <auto-increment> tag within my primary-key cmp field in jbosscmp-jdbc.xml. However, I unsuccessfully tried a number of entity commands, including a custom one, to retrieve the generated key from my SQL 2000 database. The error I keep getting is:

                    Failure while creating an OrderEntity bean: Entity with primary key [.0.] already exists

                    I don't have a primary key with the value '0', but I'm also not sure my entity command is correct:

                    <entity-command name="pk-sql">
                    SELECT (IDENT_CURRENT('tOrder') + 1)
                    </entity-command>

                    I haven't been able to understand how entity commands are used during the create method. Are they used BEFORE the actual insert, or AFTER(the above SQL command will return the next pk before the insert). Futhermore, I am not sure which entity commands are used in what way?

                    Has someone gotten this to work? Any help is appreciated.

                    Thanks,
                    Daniel

                    • 7. Re: delay-database-insert-until and autogenerated PKs
                      jtbell

                      The problem here is that if you are using an "enterprise" database that can be accessed by non-java programs how do you enforce the key generation unless the database does it? There really must be a way to use a database generated primary key.

                      • 8. Re: delay-database-insert-until and autogenerated PKs
                        sebastien_petrucci

                        I quickly looked at the source code of JDBCPkSqlCreateCommand and it seems that :
                        - first the provided SQL (pk-sql) is executed to retrieve the next bean's PK
                        - a check is made to ensure that this PK is not already used
                        - the first CMP field of the bean marked as 'unknown-pk' is filled with the PK value (probably source of your problem)
                        - the insert is executed

                        If you look at JDBCGetGeneratedKeysCreateCommand or even JDBCMySQLCreateCommand, you will see that no check is done to ensure that a field is marked as unknown-pk. That's why it works immediatly by just using auto-increment with a normal PK definition.

                        You're very unlucky :-/

                        Anyway, I guess you could (quite easily) derive a specific command implementation from existing ones. Basically, you need to make an insert query (check wether the PK fields should be part of the query or not) and immediatly after execute 'select @@IDENTITY' to get the last generated key (the PK must be of IDENTITY type)... or something like that.

                        Another solution would be to buy a JDBC 3.0 compliant driver for M$ SQL SERVER and port your app to JDK 1.4 (if not already the case).

                        Good Luck !

                        Sebastien.

                        • 9. Re: delay-database-insert-until and autogenerated PKs
                          dklehmann

                          Sebastien,

                          Thanks a lot for the answer and the suggestions.

                          Acutally, I forgot to mention the following in my first post:

                          1. I am using a JDBC 4.0 driver for MS SQL
                          2. I am not using unknown-pk, but instead just the suggested approach of using only the 'auto-increment' tag in jbosscmp-jdbc.xml and the custom SQL command (I also left my getters and setters for my PK fields in the bean).

                          So, from your description it seems like it should be working, right? Could it be that in this approach JBoss attempts to insert the ENTIRE row including the PK? That would definitely not work since the PK field is declared as auto-increment... (By the way, I already unsuccessfully tried the unknown-pk approach, but it just seems that it should not be this complicated and that your suggested approach should do).

                          I will take a look at the source code in the indicated places, but am still curious what your thoughts are. Thanks a lot for the help.

                          Daniel

                          • 10. Re: delay-database-insert-until and autogenerated PKs
                            sysuser1

                            Hi Sebastien,

                            Where can I get information/docs about <auto-increment/> and <entity-command>. I searched around and could not find anu documentation regarding this - even the JBossCMP2.0 docs. Can you point me where this is documented>

                            Thanks,
                            Trev

                            • 11. Re: delay-database-insert-until and autogenerated PKs
                              sysuser1

                              Hi Sebastien,

                              Where can I get information/docs about <auto-increment/> and <entity-command>. I searched around and could not find anu documentation regarding this - even the JBossCMP2.0 docs. Can you point me where this is documented>

                              Thanks,
                              Trev

                              • 12. Re: delay-database-insert-until and autogenerated PKs
                                sebastien_petrucci

                                Hi,

                                Unfortunately, the paid docs do not seem to reflect all the changes of JBoss 3.2.x series yet.
                                I guess it will be fixed soon.

                                kr,
                                Sebastien.

                                • 13. Re: delay-database-insert-until and autogenerated PKs
                                  sebastien_petrucci

                                  Daniel,

                                  What driver do u exactly use ?

                                  I already noticed that when I use the MySQL-specific create command, the PK is not part of the insert query. I guess it's the same with the other create commands.

                                  If you continue having problems with that, I think you could :
                                  - use XDoclet (use CVS version) and let this nice tool generate all the XML descriptors for u. The CVS version supports all these auto-increment/create commands tags
                                  - Use the JDBCGetGeneratedKeysCreateCommand (JDK 1.4 needed) in conjonction with a JDBC 3.0 compliant driver like :

                                  http://www.j-netdirect.com/JSQLConnect/JSQLFeatures.html
                                  or
                                  http://www.idssoftware.com/jdbcdrv.html

                                  kr,
                                  Sebastien.

                                  • 14. Re: delay-database-insert-until and autogenerated PKs
                                    pup99

                                    Sebastien,
                                    Was wondering if you could put up a copy of a small entity bean using auto-increment. I have been trying to get this to work for some time. Though I can get the insert to work correctly I do get an error after the insert seems to happen. So it does show up in the database, but I get an error back here is the first part

                                    10:54:35,828 ERROR [LogInterceptor] TransactionRolledbackException, causedBy:
                                    java.lang.IllegalArgumentException
                                    at sun.reflect.UnsafeObjectFieldAccessorImpl.set(UnsafeObjectFieldAccess
                                    orImpl.java:63)
                                    at java.lang.reflect.Field.set(Field.java:519)
                                    at org.jboss.ejb.plugins.cmp.jdbc.bridge.JDBCAbstractCMPFieldBridge.setP
                                    rimaryKeyValue(JDBCAbstractCMPFieldBridge.java:229)

                                    Anyway, I could really use an example that works, so that I could understand where I am going wrong. I am sure I am not the only one who would find it usefull.

                                    Thanks a lot for the help so far :)

                                    Lyle

                                    1 2 3 Previous Next