Re: SINSERT Key Generator
anthony berglas <[email protected]> Wed, 4 May 2011 13:38:28 +1000
| Newsgroups | gmane.comp.java.orm.simpleorm |
|---|---|
| Message-ID | <[email protected]> |
--001636d352c2c41a1704a26aff8e Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable Actually, http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_= last-insert-id Says it is threadsafe. Plus has a few caveats. Thanks Tim. Anthony On Wed, May 4, 2011 at 1:01 PM, anthony berglas <[email protected]> wrote= : > 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 wo= uld > be great. See > > > http://dev.mysql.com/doc/refman/4.1/en/connector-j-usagenotes-basic.html#= connector-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 bac= k > 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 workin= g > just with MySql, we can push it up for you. > > You make a good point about the references. Children assume parents > already have their keys. I suppose there should be a lazy way to get the= m. > But for 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)<#12fb8f45faf831e3_12f94e2668a4f8ce_12f67290b26e68e3_TopT= ext>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.= java >> 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 f= ield >> 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 D= B at >> once and I wanted to find out how effective the retrieveInsertKey() meth= od >> is going to be in such an environment and whether I will be able to main= tain >> 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?" >> * >> >> > > > -- > > 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. > > --=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. --001636d352c2c41a1704a26aff8e 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>Actually,=A0</div><div><br></div><span class=3D"Apple-style-span" styl= e=3D"border-collapse: collapse; font-family: arial, sans-serif; font-size: = 13px; "><a href=3D"http://dev.mysql.com/doc/refman/5.0/en/information-funct= ions.html#function_last-insert-id" target=3D"_blank" style=3D"color: rgb(0,= 0, 204); ">http://dev.mysql.com/doc/refman/5.0/en/information-functions.ht= ml#function_last-insert-id</a></span><br> <br><div>Says it is threadsafe. =A0Plus has a few caveats. =A0Thanks Tim.</= div><div><br></div><div>Anthony</div><div><br><div class=3D"gmail_quote">On= Wed, May 4, 2011 at 1:01 PM, anthony berglas <span dir=3D"ltr"><<a href= =3D"mailto:[email protected]">[email protected]</a>></span> wrote:<b= r> <blockquote class=3D"gmail_quote" style=3D"margin:0 0 0 .8ex;border-left:1p= x #ccc solid;padding-left:1ex;"><div>Hello Abel,</div><div><br></div><div>S= orry for the late reply, but your code looks basically OK.</div><div><br></= div> <div>However, it would probably be better to use the new standard JDBC=A0ge= tGeneratedKeys() method. =A0If that works for MySql and HSQL then it could = be pushed up into SDriver for all databases that=A0supportsInsertKeyGenerat= ion(). =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= " target=3D"_blank">http://dev.mysql.com/doc/refman/4.1/en/connector-j-usag= enotes-basic.html#connector-j-examples-autoincrement-getgeneratedkeys</a><d= iv> <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><div></div><div class=3D"h5"><div class=3D"gmail_quote">On= Wed, Apr 27, 2011 at 12:55 PM, Anthony & Melissa Berglas <span dir=3D"= ltr"><<a href=3D"mailto:[email protected]" target=3D"_bla= nk">[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]" tar= get=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"#12fb8f45faf831e3_1= 2f94e2668a4f8ce_12f67290b26e68e3_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></div></div>-- <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: <a href=3D"tel:%= 2B61%204%204838%208874" value=3D"+61448388874" target=3D"_blank">+61 4 4838= 8874</a><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> </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=3D2028/stime=3D1304480349" 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= JmbHJkNnZ2BF9TAzk3NDc2NTkwBGdycElkAzczNjAyMjYEZ3Jwc3BJZAMxNzA1MDA2OTA1BHNlY= wNmdHIEc2xrA3N0bmdzBHN0aW1lAzEzMDQ0ODAzNDk-">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= JkOW5ndXVsBF9TAzk3NDc2NTkwBGdycElkAzczNjAyMjYEZ3Jwc3BJZAMxNzA1MDA2OTA1BHNlY= wNmdHIEc2xrA2hwZgRzdGltZQMxMzA0NDgwMzQ5"> 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> --001636d352c2c41a1704a26aff8e--