Using new MySQL unknown-pk features

[kylev]

I'm trying to get a simple User CMP Entity working with a mysql backend. It works for read access, but I keep getting Exceptions on create(String username, String password). I think it has to do with they way I'm attempting to use the new auto_increment-related features. From my ejb-jar.xml:


<ejb-name>User</ejb-name>

Core user bean, CMP edition

com.kylev.little.UserHomeRemote
com.kylev.little.UserRemote
<ejb-class>com.kylev.little.UserBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.Integer</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>User</abstract-schema-name>
<cmp-field>
<field-name>id</field-name>
<column-name>id</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>INT(11)</sql-type>
<auto-increment />
</cmp-field>
<entity-command name="mysql-get-generated-keys" />
<cmp-field><field-name>username</field-name></cmp-field>
<cmp-field><field-name>password</field-name></cmp-field>
<cmp-field><field-name>firstName</field-name></cmp-field>
<cmp-field><field-name>lastName</field-name></cmp-field>
<primkey-field>id</primkey-field>

<unknown-pk>
<unknown-pk-class>java.lang.Integer</unknown-pk-class>
<column-name>id</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>INT(11)</sql-type>
<auto-increment/>
</unknown-pk>
<entity-command name="mysql-get-generated-keys" class="org.jboss.cmp.jdbc.mysql.MySQLCreateEntityCommand"/>


<query-method>
<method-name>findByUsername</method-name>
<method-params>
<method-param>java.lang.String</method-param>
</method-params>
</query-method>
<ejb-ql><![CDATA[
SELECT DISTINCT OBJECT(u)
FROM User u
WHERE u.username = ?1
]]></ejb-ql>



It's not clear to me that I'm using the unknown-pk stuff correctly, but the threads I've seen on the topic are less than clear how/where to use this stuff.

I'm running the 3.2.0beta2 bundle.

[kylev]

Doh, specifically:

java.lang.IllegalArgumentException: Attempt to get lock ref with a null
object

[hvenstrom]

Reposting:

-------------------------------------------------------------

JBossCMP becomes cooler. Now it supports Unknown primary keys (Refer to EJB2.0 spec 10.8.3 for details).

To make use of this feature, entity beans must have prim-key-class declared as java.lang.Object and no primkey-field in ejb-jar.xml.
To set up unknown primary key, the new optional unknown-pk element is used in jbosscmp-jdbc.xml. (See jbosscmp-jdbc_3_0.dtd for details).
If unknown-pk element is omitted in jbosscmp-jdbc.xml, the default one from standardjbosscmp-jdbc.xml will be used.
The default (and the only for now) key generator factory is UUIDKeyGeneratorFactory. It's deployed as a service and bound to the global JNDI context.
Other key generator factory implementations are welcome!

The feature is available in CVS HEAD version.

--
Alex Loubyansky

[hvenstrom]

Hmmm...right that was not your problem

[hvenstrom]

Reposting again, note that in this post are the

<unknown-pk>
<unknown-pk-class>java.lang.Integer</unknown-pk-class>
<column-name>id</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>INT(11)</sql-type>
<auto-increment/>
</unknown-pk>
<entity-command name="mysql-get-generated-keys" class="org.jboss.cmp.jdbc.mysql.MySQLCreateEntityCommand"/>


defenition part of the jbosscmp-jdbc file

-------------------------------------------------------------

Alex Loubyansky

JBossCMP now provides some new ways to setup and create entity beans.
It is based on so called entity commands.
First some working examples (snippet from jbosscmp-jdbc.xml):

<ejb-name>MyCMPBean</ejb-name>
<table-name>mycmp_table</table-name>

<!-- auto-incremented primary key (jdk1.3/1.4) for now supported only for MySQL. -->
<cmp-field>
<field-name>id</field-name>
<column-name>knownid</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>INTEGER(11)</sql-type>
<auto-increment/>
</cmp-field>
<!-- entity command to create the entity -->
<entity-command name="mysql-get-generated-keys"/>

<!-- unknown primary key generated with SQL statement -->
<unknown-pk>
<unknown-pk-class>java.lang.Integer</unknown-pk-class>
<column-name>genid</column-name>
<jdbc-type>TIMESTAMP</jdbc-type>
<sql-type>DATETIME</sql-type>
</unknown-pk>
<entity-command name="pk-sql"/>
-->
<!-- pk-sql generated unknown-pk; overriding default sql
<unknown-pk>
<unknown-pk-class>java.lang.Integer</unknown-pk-class>
<column-name>genid</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>INTEGER</sql-type>
</unknown-pk>
<entity-command name="pk-sql">
SELECT SEQ_2279_1.nextval FROM DUAL
</entity-command>
-->
<!-- mysql generated unknown-pk
<unknown-pk>
<unknown-pk-class>java.lang.Integer</unknown-pk-class>
<column-name>genid</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>INTEGER(11)</sql-type>
<auto-increment/>
</unknown-pk>
<entity-command name="mysql-get-generated-keys"
class="org.jboss.ejb.plugins.cmp.jdbc.mysql.MySQLCreateEntityCommand"/>
-->
<!-- the use of key generator for unknown-pk
<unknown-pk>
<unknown-pk-class>java.lang.String</unknown-pk-class>
<column-name>genid</column-name>
<jdbc-type>VARCHAR</jdbc-type>
<sql-type>VARCHAR(32)</sql-type>
</unknown-pk>
<entity-command name="key-generator"
class="org.jboss.ejb.plugins.cmp.jdbc.JDBCKeyGeneratorCreateCommand">
UUIDKeyGeneratorFactory
</entity-command>
-->


The general syntax of entity-command is:
<entity-command name="CommandName"
class="ComandClass">
value1
value2
</entity-command>

Only the 'name' is required.
You can check the list of available commands in
standardjbosscmp-jdbc.xml in entity-commands element.
The default entity command is specified in 'defaults' element and can
be overriden in each entity element as above.
If entity-command in the entity provides 'class' or 'attribute' it
overrides the corresponding element of in the known command.

For now the feature is available in HEAD version.

PS: templates for non-MySQL auto-increment SQL statements are welcome!!!

alex

[kylev]

Making notes as I go so other people don't get annoyed like me.

First, any of this behaviour is moot unless you've made your CMP bean follow the spec:

http://java.sun.com/j2ee/tutorial/1_3-fcs/doc/CMP8.html

Specifically, the stuff about findByPrimaryKey() and create() and java.lang.Object .

Now at least I'm getting SQL errors about a User_upk field not being found.

[kylev]

It was previously unclear (from other postings) that the <unknown-pk> entries go in jbosscmp-jdbc.xml . Now I think I understand that.

Now, I just need to figure out how my id field needs to be defined. Should I specify it as a <cmp-field> in the deployment descriptor? Or just lean on the jbosscmp-jdbc.xml stuff to know of the implied id field since there isn't a <primkey-field> specified?

Also, where does the <entity-command> actually go?

Previous posts, which have been reposted in this thread, were poorly written to indicate the what-goes-where that has me confused.

[hvenstrom]

If you used local it would look at bit different, but I think it should be something like this in the ejb-jar file (but I am trying out this my self so I might be wrong):



<ejb-name>User</ejb-name>
com.kylev.little.UserHomeRemote
com.kylev.little.UserRemote
<ejb-class>com.kylev.little.UserBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.Integer</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>User</abstract-schema-name>
<cmp-field>
<field-name>id</field-name>
</cmp-field>
x
x
x


As to the <entity-command> it gose with the <unknown-pk> defenition.

[kylev]

Aha! I got things working, but the finaly key was that you must be running the Connector/J 3 beta MySQL JDBC drivers. The required getGeneratedKeys() method doesn't exist int the version 2 drivers. The old mm.mysql drivers won't work at all since the JDBCMySQLCreateCommand class does casts from com.mysql.jdbc... stuff, not the old org.gjt heirarchy.

I'll post more about the struggles of related .xml entries after I eat food.

[hvenstrom]

Hmmm...I see, ok then I will stay away from the MySQL generated keys (don't like that solution in any case, it is not generic).
BW...lol..do not forget the jboss.xml file.....

[kylev]

Ok, I finally have things working right, at least to external appearances. Perhaps this should become a FAQ at some point.

First, you have to consider how you want the JBoss CMP implementation to act, and put that in jbosscmp-jdbc.xml. In my case, I was planning to have id auto_increment columns in the tables for my EntBeans. You have to tell CMP that thusly (The important part is the <unknown-pk>):

<jbosscmp-jdbc>

java:/LittleDS
<create-table>false</create-table>
<remove-table>false</remove-table>
<unknown-pk>
<unknown-pk-class>java.lang.Integer</unknown-pk-class>
<field-name>id</field-name>
<column-name>id</column-name>
<jdbc-type>INTEGER</jdbc-type>
<sql-type>INT(11)</sql-type>
<auto-increment/>
</unknown-pk>
<entity-command name="mysql-get-generated-keys"/>

</jbosscmp-jdbc>

I've done this in a section. Note that according to the DTD, you can specify elements here if you want to, overriding this default behaviour. That local definition should override the larger defaults. Notice the <entity-command> I used: it tells CMP what plugin to use when creating one of these Entities (this command is defined to reference a plugin in standardjbosscmp-jdbc.xml).

In writing your bean, be sure to follow the Sun spec regarding unknown keys. The amounts mostly to replacing references to an actual key type to java.lang.Object. Most notably, ejbCreate() should return one, findByPrimaryKey() should take one.

The next piece of the puzzle is in your deployment descriptor for the Bean. Snippet:


<ejb-name>User</ejb-name>

Core user bean, CMP edition

com.kylev.little.UserHomeRemote
com.kylev.little.UserRemote
<ejb-class>com.kylev.little.UserBean</ejb-class>
<persistence-type>Container</persistence-type>
<prim-key-class>java.lang.Object</prim-key-class>
False
<cmp-version>2.x</cmp-version>
<abstract-schema-name>User</abstract-schema-name>
<cmp-field><field-name>username</field-name></cmp-field>
<cmp-field><field-name>password</field-name></cmp-field>
...


(I left out some since I have a query that might confuse the example).

First, as mandated by the Sun spec, our key is of java.lang.Object.

Next, note that I didn't snip things about the primary key or an id cmp-field. Those are actually left out on purpose. With no <primkey-field> specified, it falls through to the CMP behaviour which we specified above. In this case, you end up with a field named "id", in column named "id" of type Integer, populated via an entity-command that knows how to get the value from the backend database.

Hope this helps someone!

[kylev]

I'm documenting this all in a more coherent form:

http://www.kylev.com/projects/jboss/cmpauto.html

[hvenstrom]

Gret stuff..I will book mark. Good luck with the rest