Christine, I am unable to find finderMaxID in the EJB2.0 spec, so I am guessing that this is a Jboss method used in their Entity Beans. So I am unsure of the constraints that you might be working under. But, using a select max(id) is not thread safe. It is possible for one thread to start up, and run the select statement, then context switch to another thread, which will then run the same statement, and get the same answer. Then, second thread could complete an insert into the database, before the first thread regains control. Then when the first thread tries to insert, it will bomb out with a primary key constraint error.
I know it sounds far fetched, but under high load, it could become a real problem. If possible, you would want to use MSQL's native sequencing feature. The MySQL support site says: You can get the used AUTO_INCREMENT key with the LAST_INSERT_ID() SQL function or the mysql_insert_id() API function. http://www.mysql.com/doc/e/x/example-AUTO_INCREMENT.html This would guarantee a unique number. But I think that you would have to insert the row to get the unique id, which you may not be able to do, since you might not have all the necessary fields at the time you call finderMaxID. If this is the case, you could create a sequence table, which a table that has nothing but a auto-incrementing primary key in it. You could then do a insert into it of nothing, and then grab last inserted row from it using the LAST_INSERT_ID() function. Then you could use this unique number as the primary key for your new entity bean. This would mean that everyone using this table would use the same index. For example, if you had four tables, and you used the same sequence table for all of them, then the index would be spread across the four tables. Usually this is not a big deal. But if it is, you could just create an index table for each table. This is essentially how oracle deals with sequences. I hope that you find that helpful, Lucas McGregor, NovaLogic -----Original Message----- From: Todd Marshall [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 05, 2002 3:23 PM To: Christine; Burkhard Vogel Cc: [EMAIL PROTECTED] Subject: RE: [JBoss-user] Finder Method in JBoss if you select id from customer order by id DESC then get the first row returned by this query, it will be your max id.... -Todd -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Christine Sent: Tuesday, March 05, 2002 5:01 PM To: Burkhard Vogel Cc: [EMAIL PROTECTED] Subject: Re: [JBoss-user] Finder Method in JBoss Hi, The reason I have this issue is because I am actually implementing the auto_increment by myself. The whole store is: I have a table with an integer type unique ID. everytime, when I create a new EJB object, I call the finderMaxID method to get the MAX ID, then MAX ID + 1 to get the new max id and create a new EJB object. at the same time, insert a new record to the table. In the whereclause for the finderMaxID, i used " WHERE ID IN (SELECT MAX(ID) FROM Customer)". Now, MySQL doesn't support sub-select. I have to use other way to do so. I am not sure whether I understood what you mean, but how can ORDER BY ID help? Burkhard Vogel wrote: > Hi, > if you are in one table and you have only one line which will be MAX, you > could use ORDER BY ID desc . Which will be much faster as well, if in two > tables, join them and do the same... But if there are more than one value... > Wait for MySQL 4.0 which will support subqueries. > Regards > Burkhard > ----- Original Message ----- > From: "Christine" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Friday, March 01, 2002 1:12 PM > Subject: [JBoss-user] Finder Method in JBoss > > > Hi, > > > > I have a finder method which use sub-select as following: > > findMaxIDWhereClause : ID IN (SELECT MAX(ID) FROM Customer) > > it works fine when I used IBM WebSphere and DB2. Now I am switching to > > JBoss + Tomcat + MySQL. Cause MySQL doesn't support sub-select. Does > > anyone know whether there is some other way to get my > > findMaxIDWhereClause work without have to change code? Thanks in > > advance! > > > > regards, > > -- > > Jia (Christine) Li > > > > 524N ICT Building > > Department of Computer Science > > University of Calgary > > > > > > > > _______________________________________________ > > JBoss-user mailing list > > [EMAIL PROTECTED] > > https://lists.sourceforge.net/lists/listinfo/jboss-user -- Jia (Christine) Li 524N ICT Building Department of Computer Science University of Calgary _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user _______________________________________________ JBoss-user mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/jboss-user
