Re: SINSERT Key Generator
anthony berglas <[email protected]> Wed, 4 May 2011 13:01:53 +1000
| Newsgroups | gmane.comp.java.orm.simpleorm |
|---|---|
| Message-ID | <[email protected]> |
--90e6ba4fc420f041fa04a26a7c92 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable Hello Abel, Sorry for the late reply, but your code looks basically OK. However, it would probably be better to use the new standard JDBC getGeneratedKeys() method. If that works for MySql and HSQL then it could be pushed up into SDriver for all databases that supportsInsertKeyGeneration(). If you would like to do that that woul= d be great. See http://dev.mysql.com/doc/refman/4.1/en/connector-j-usagenotes-basic.html#co= nnector-j-examples-autoincrement-getgeneratedkeys I am suspicious of the thread safety of LAST_INSERT_ID() (which getGeneratedKeys probably uses beneath the covers). To test, just set up two manual command lines sessions to a MySql database, Check AutoCommit is Off, insert a row in one session, then in the second session, then go back to the first session and Select Last_Insert_Id. It is important that it be the first record, not the second. This would be a valuable contribution to SimpleOrm. If you get it working just with MySql, we can push it up for you. You make a good point about the references. Children assume parents alread= y have their keys. I suppose there should be a lazy way to get them. But fo= r now just flush. Regards, Anthony On Wed, Apr 27, 2011 at 12:55 PM, Anthony & Melissa Berglas < [email protected]> wrote: > > > ---------- Forwarded message ---------- > From: Abel Birya <[email protected]> > Date: Mon, Apr 18, 2011 at 3:50 PM > Subject: [SimpleORM] SINSERT Key Generator [1 Attachment] > To: [email protected] > > > > [Attachment(s) <#12f94e2668a4f8ce_12f67290b26e68e3_TopText> from Abel > Birya included below] > > Hi, > > I was wondering whether anyone has been able to do an implementation of > this key generator mode for MySQL. I implemented the methods > supportsInsertKeyGeneration() & retrieveInsertKey() of the SDriverMySQL.j= ava > class and so far I am able to retrieve my inserted key from the db when a > commit() is done. > > One thing that I have noticed though is that in the event that an entity > represents a foreign key in another table, I have to explicitly call the > session.flush() method in order for the entity to bear its primary key fi= eld > value before I call the setReference() method in the child record. > > I was wondering whether there is anyone who has time to assist me in > testing this. I am currently working on a project that needs to work in a > multi-threaded environment where there will be many connections to the DB= at > once and I wanted to find out how effective the retrieveInsertKey() metho= d > is going to be in such an environment and whether I will be able to maint= ain > data integrity all across the different threads that are running. > > I have attached the SDriverMySQL.java class. > > Kind regards. > > Abel > >=20=20 > > > > -- > > *Spreadsheet Detective, > *Southern Cross Software Queensland Pty Limited > 54 Gerler Street, Bardon, Queensland 4065, Australia. > www.SpreadsheetDetective.com > <http://www.spreadsheetdetective.com/>*"If the model seems correct only > because the numbers look right, > then why build the model in the first place?" > * > > --=20 Dr Anthony Berglas, [email protected] Mobile: +61 4 4838 8874 Just because it is possible to push twigs along the ground with ones nose does not necessarily mean that that is the best way to collect firewood. --90e6ba4fc420f041fa04a26a7c92 Content-Type: text/html; charset=windows-1252 Content-Transfer-Encoding: quoted-printable <head> <style type=3D"text/css"> <!-- /* start of attachment style */ .ygrp-photo-title{ clear: both; font-size: smaller; height: 15px; overflow: hidden; text-align: center; width: 75px; } div.ygrp-photo{ background-position: center; background-repeat: no-repeat; background-color: white; border: 1px solid black; height: 62px; width: 62px; } div.photo-title=20 a, div.photo-title a:active, div.photo-title a:hover, div.photo-title a:visited { text-decoration: none;=20 } div.attach-table div.attach-row { clear: both; } div.attach-table div.attach-row div { float: left; /* margin: 2px;*/ } p { clear: both; padding: 15px 0 3px 0; overflow: hidden; } div.ygrp-file { width: 30px; valign: middle; } div.attach-table div.attach-row div div a { text-decoration: none; } div.attach-table div.attach-row div div span { font-weight: normal; } div.ygrp-file-title { font-weight: bold; } /* end of attachment style */ --> </style> </head> <html> <head> <style type=3D"text/css"> <!-- #ygrp-mkp { border: 1px solid #d8d8d8; font-family: Arial; margin: 10px 0; padding: 0 10px; } #ygrp-mkp hr { border: 1px solid #d8d8d8; } #ygrp-mkp #hd { color: #628c2a; font-size: 85%; font-weight: 700; line-height: 122%; margin: 10px 0; } #ygrp-mkp #ads { margin-bottom: 10px; } #ygrp-mkp .ad { padding: 0 0; } #ygrp-mkp .ad p { margin: 0; } #ygrp-mkp .ad a { color: #0000ff; text-decoration: none; } --> </style> </head> <body> <div>Hello Abel,</div><div><br></div><div>Sorry for the late reply, but you= r code looks basically OK.</div><div><br></div><div>However, it would proba= bly be better to use the new standard JDBC=A0getGeneratedKeys() method. =A0= If that works for MySql and HSQL then it could be pushed up into SDriver fo= r all databases that=A0supportsInsertKeyGeneration(). =A0If you would like = to do that that would be great. =A0See</div> <div><br></div><a href=3D"http://dev.mysql.com/doc/refman/4.1/en/connector-= j-usagenotes-basic.html#connector-j-examples-autoincrement-getgeneratedkeys= ">http://dev.mysql.com/doc/refman/4.1/en/connector-j-usagenotes-basic.html#= connector-j-examples-autoincrement-getgeneratedkeys</a><div> <br></div><div>I am suspicious of =A0the thread safety of LAST_INSERT_ID() = (which getGeneratedKeys probably uses beneath the covers). =A0To test, just= set up two manual =A0command lines sessions to a MySql database, Check Aut= oCommit is Off, insert a row in one session, then in the second session, th= en go back to the first session and Select Last_Insert_Id. =A0It is importa= nt that it be the first record, not the second.</div> <div><br></div><div>This would be a valuable contribution to SimpleOrm. =A0= If you get it working just with MySql, we can push it up for you.</div><div= ><br></div><div>You make a good point about the references. =A0Children ass= ume parents already have their keys. =A0I suppose there should be a lazy wa= y to get them. =A0But for now just flush.</div> <div><br></div><div>Regards,</div><div><br></div><div>Anthony</div><div><br= ></div><div><div class=3D"gmail_quote">On Wed, Apr 27, 2011 at 12:55 PM, An= thony & Melissa Berglas <span dir=3D"ltr"><<a href=3D"mailto:berglas= @spreadsheetdetective.com">[email protected]</a>></span> = wrote:<br> <blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p= x #ccc solid;padding-left:1ex;"><br><br><div class=3D"gmail_quote">--------= -- Forwarded message ----------<br>From: <b class=3D"gmail_sendername">Abel= Birya</b> <span dir=3D"ltr"><<a href=3D"mailto:[email protected]" ta= rget=3D"_blank">[email protected]</a>></span><br> Date: Mon, Apr 18, 2011 at 3:50 PM<br>Subject: [SimpleORM] SINSERT Key Gene= rator [1 Attachment]<br>To: <a href=3D"mailto:[email protected]" ta= rget=3D"_blank">[email protected]</a><br><br><br> <div style=3D"background-color:#fff"> <span>=A0</span> <div> <div> <div> =20=20=20=20=20=20 =20=20=20=20=20=20=20=20=20=20=20=20=20=20 <span style=3D"font-weight:700;font-size:12px;display:block;margin-= bottom:20px">[<a style=3D"text-decoration:none" href=3D"#12f94e2668a4f8ce_1= 2f67290b26e68e3_TopText">Attachment(s)</a> from Abel Birya included below]<= /span> =20=20=20=20=20=20=20=20 =20=20=20=20=20=20 <p>Hi,</p><div><br></div><div>I was wondering whether anyone has been= able to do an implementation of this key generator mode for MySQL. I=A0imp= lemented the methods supportsInsertKeyGeneration() & retrieveInsertKey(= ) of the SDriverMySQL.java class and so far I am able to retrieve my insert= ed key from the db when a commit() is done.</div> <div><br></div><div>One thing that I have noticed though is that in the eve= nt that an entity represents a foreign key in another table, I have to expl= icitly call the session.flush() method in order for the entity to bear its = primary key field value before I call the setReference() method in the chil= d record.</div> <div><br></div><div>I was wondering whether there is anyone who has time to= assist me in testing this. I am currently working on a project that needs = to work in a multi-threaded environment where there will be many connection= s to the DB at once and I wanted to find out how effective the=A0retrieveIn= sertKey() method is going to be in such an environment and whether I will b= e able to maintain data integrity all across the different threads that are= running.</div> <div><br></div><div>I have attached the SDriverMySQL.java class.</div><div>= <br></div><div>Kind regards.</div><div><br></div><div>Abel</div><div><br></= div> <p></p> </div> =20=20=20=20=20 =20=20=20=20 <div style=3D"color:#fff;min-height:0"></div> </div> =20=20 </div><br><font color=3D"#888888"><br clear=3D"all"><br>-- <br><p> <b>Spreadsheet Detective,<br> </b>Southern Cross Software Queensland Pty Limited<br> 54 Gerler Street, Bardon, Queensland 4065, Australia.<br> <a href=3D"http://www.spreadsheetdetective.com/" target=3D"_blank">www.Spre= adsheetDetective.com<br> </a><i>"If the model seems correct only because the numbers look right= , <br> then why build the model in the first place?"<br></i></p><br> </font></blockquote></div><br><br clear=3D"all"><br>-- <br><p> </p><p>Dr Anthony Berglas, <a href=3D"mailto:[email protected]" target=3D= "_blank">[email protected]</a>=A0=A0=A0=A0=A0=A0 Mobile: +61 4 4838 8874<= br> Just because it is possible to push twigs along the ground with ones nose<b= r> does not necessarily mean that that is the best way to collect firewood.</p= ><br> </div> <!-- |**|begin egp html banner|**| --> <br> =20=20=20=20 =20=20=20=20 <br> <!-- |**|end egp html banner|**| --> <div width=3D"1" style=3D"color: white; clear: both;"/>__._,_.___</div> <!-- Start Recommendations --> <!-- End Recommendations --> <!-- |**|begin egp html banner|**| --> <img src=3D"http://geo.yahoo.com/serv?s=3D97476590/grpId=3D7360226/grpspI= d=3D1705006905/msgId=3D2027/stime=3D1304478161" width=3D"1" height=3D"1"> <= br> <!-- |**|end egp html banner|**| --> =20=20 <!-- |**|begin egp html banner|**| --> <br> <div style=3D"font-family: verdana; font-size: 77%; border-top: 1px s= olid #666; padding: 5px 0;" > Your email settings: Individual Email|Traditional <br> <a href=3D"http://groups.yahoo.com/group/SimpleORM/join;_ylc=3DX3oDMT= Jmb2FvaXRjBF9TAzk3NDc2NTkwBGdycElkAzczNjAyMjYEZ3Jwc3BJZAMxNzA1MDA2OTA1BHNlY= wNmdHIEc2xrA3N0bmdzBHN0aW1lAzEzMDQ0NzgxNjE-">Change settings via the Web</a= > (Yahoo! ID required) <br> Change settings via email: <a href=3D"mailto:SimpleORM-digest@yahoogr= oups.com?subject=3DEmail Delivery: Digest">Switch delivery to Daily Digest<= /a> | <a href =3D "mailto:[email protected]?subject=3D= Change Delivery Format: Fully Featured">Switch to Fully Featured</a> <br> <a href=3D"http://groups.yahoo.com/group/SimpleORM;_ylc=3DX3oDMT= JkdmYzZTQzBF9TAzk3NDc2NTkwBGdycElkAzczNjAyMjYEZ3Jwc3BJZAMxNzA1MDA2OTA1BHNlY= wNmdHIEc2xrA2hwZgRzdGltZQMxMzA0NDc4MTYx"> Visit Your Group=20 </a> | <a href=3D"http://docs.yahoo.com/info/terms/"> Yahoo! Groups Terms of Use </a> | <a href=3D"mailto:[email protected]?subject=3DUns= ubscribe"> Unsubscribe=20 </a>=20 <br> </div> <br> <!-- |**|end egp html banner|**| --> <div style=3D"color: white; clear: both;"/>__,_._,___</div> </body> </html> --90e6ba4fc420f041fa04a26a7c92--