Hi Kenji, you're welcome, but actually I am glad you came up with it since we just had neglected this very important issue a bit. And as I said: Most of it was already there and was waiting for getting completed.
I am thinking of tiding up a bit and I might rename a few functions (not sure yet) - just to make it simpler and more consistent. It won't be anything serious or a change in the behavior so it can easily be applied to existing code. If I do I let you know here. BTW: I'd be happy to hear if everything works as desired (or not). Regards Rainer Kenji Nakamura wrote: > from: Kenji Nakamura [mailto:[email protected]] > to: [email protected] > re: Re: All "prepared" now! > > Rainer, > > Wow, it's fantastic! > I'll give a try with the latest code. > > I feel like I got a bit early Christmas present! > > Thanks for all your effort, > > Kenji Nakamura > > On Dec 3, 2010, at 22:26, "Rainer Döbele" <[email protected]> wrote: > > > > > But then we should not wait too long with our 2.1. release. > > > > At least it will show, that we are a very active community :-) > > > > Regards > > Rainer > > > > > > Francis De Brabandere wrote: > >> from: Francis De Brabandere [mailto:[email protected]] > >> to: [email protected] > >> re: Re: All "prepared" now! > >> > >> I would just continue with the release. That will be the last 2.0.x > >> Afterwards we can directly cut the 2.1.0 > >> > >> Cheers, > >> Francis > >> > >> On Fri, Dec 3, 2010 at 11:25 AM, Rainer Döbele <[email protected]> > >> wrote: > >>> Hi Francis, > >>> > >>> Yes indeed a hard night. > >>> I had a problem with the parameter order that just took me some > time > >> to fix. > >>> > >>> I have seen that I missed a few places that sill generate simple > >> statements. > >>> Will submit another patch today after a few more tests. > >>> > >>> Yes it's a shame that this isn't part of the release - I think it > is > >> an important major feature. > >>> > >>> How about stopping the current rc and going for the next one? > >>> I would probably safe us some unnecessary double work. > >>> > >>> Since I had to change a few things in the interface of DBCommand I > >> would like to call the next release 2.1.0. > >>> > >>> What do you think? > >>> > >>> Regards > >>> Rainer > >>> > >>> > >>> Francis De Brabandere wrote: > >>>> from: Francis De Brabandere [mailto:[email protected]] > >>>> to: [email protected] > >>>> re: Re: All "prepared" now! > >>>> > >>>> Busy night Rainer? Great to hear this is implemented :-) Too bad > we > >>>> just cut a release :-s > >>>> > >>>> On Fri, Dec 3, 2010 at 9:30 AM, Rainer Döbele <[email protected]> > >> wrote: > >>>>> Hi Kenji, > >>>>> > >>>>> good news for you: it's all "prepared" now :-) > >>>>> Most of it was already there, but a few bits and pieces were > >> missing. > >>>>> However you have to take the latest sources directly from our SVN > >>>> repository. > >>>>> > >>>>> In DBDatabase you now have a property called > >>>> "preparedStatementsEnabled" which you can enable or disable. > >>>>> If enabled Empire-db will use prepared statements for all > database > >>>> operations on DBRecord. > >>>>> > >>>>> If you create your own commands using DBCommand you will have to > >>>> explicitly declare the parameters using DBCommand.addCmdParam() > which > >>>> will return a DBCommandParam (I have renamed this inner class from > >>>> DBCmdParameter!) except for set() operations. > >>>>> > >>>>> I have added an example to the empire-db-example-advanced > >>>> (SampleAdvApp.java). The commandParamsSample() method shows how to > >> use > >>>> command params. > >>>>> By additionally setting preparedStatementsEnabled to enabled, > also > >> all > >>>> operations performed by Empire-db itself will use prepared > >> statements. > >>>>> You can easily see it in the log. > >>>>> And the good thing is, you can easily switch between prepared and > >> non- > >>>> prepared (non-prepared statements are still better for debugging). > >>>>> > >>>>> Let me know, if you have problems or need any more help. > >>>>> Best regards, > >>>>> > >>>>> Rainer > >>>>> > >>>>> > >>>>> Kenji Nakamura wrote: > >>>>>> from: Kenji Nakamura [mailto:[email protected]] > >>>>>> to: [email protected] > >>>>>> re: Re: Prepared statement support? > >>>>>> > >>>>>> Hi Rainer, > >>>>>> > >>>>>> Yes, this is exactly what I was looking for. > >>>>>> Regarding statement pooling, it is a part of JDBC 3.0 spec and I > >>>> think > >>>>>> it is a job of connection pool utility. > >>>>>> We use c3p0 and it has statement pooling capability. It is > highly > >>>>>> configurable and has lots of features. > >>>>>> > >>>>>> > >>>> > >> > http://www.mchange.com/projects/c3p0/index.html#configuring_statement_p > o > >>>>>> oling > >>>>>> > >>>>>> I really appreciate if you can include the bug fix of DBReader > in > >> the > >>>>>> next release as this is crucial feature to persuade DBAs and > >> security > >>>>>> auditors. > >>>>>> > >>>>>> Thanks a lot! > >>>>>> > >>>>>> Kenji Nakamura > >>>>>> > >>>>>> On Dec 2, 2010, at 19:29, Rainer Döbele <[email protected]> > wrote: > >>>>>> > >>>>>>> Hi everyone, > >>>>>>> > >>>>>>> thanks for your comment Matt. > >>>>>>> > >>>>>>> To my own surprise I have overlooked that there is already > >>>> substantial > >>>>>> support for prepared statement generation in Empire-db now, but > >> you > >>>> have > >>>>>> to explicitly declare the parameters. > >>>>>>> Here is an example of how to generate a prepared statement > >> phrase > >>>> and > >>>>>> execute it with the corresponding parameters: > >>>>>>> > >>>>>>> // Define the query > >>>>>>> DBCommand cmd = db.createCommand(); > >>>>>>> > >>>>>>> // Create parameters > >>>>>>> DBCmdParameter depIdParam = cmd.addCmdParam(1); > >>>>>>> DBCmdParameter genderParam = cmd.addCmdParam('F'); > >>>>>>> > >>>>>>> // create statement > >>>>>>> cmd.select(EMP.getColumns()); > >>>>>>> cmd.where(EMP.DEPARTMENT_ID.is(depIdParam)); > >>>>>>> cmd.where(EMP.GENDER.is(genderParam)); > >>>>>>> > >>>>>>> // First execution > >>>>>>> String sql = cmd.getSelect(); > >>>>>>> ResultSet r = db.executeQuery(sql, cmd.getCmdParams(), > false, > >>>>>> conn); > >>>>>>> // do something > >>>>>>> r.close(); > >>>>>>> > >>>>>>> // Modify command parameters > >>>>>>> depIdParam.setValue(2); > >>>>>>> genderParam.setValue('M'); > >>>>>>> > >>>>>>> // Second execution > >>>>>>> r = db.executeQuery(sql, cmd.getCmdParams(), false, conn); > >>>>>>> // do something > >>>>>>> r.close(); > >>>>>>> > >>>>>>> This will result in the following SQL: > >>>>>>> > >>>>>>> SELECT t2.EMPLOYEE_ID, t2... > >>>>>>> FROM EMPLOYEES t2 > >>>>>>> WHERE t2.DEPARTMENT_ID=? AND t2.GENDER=? > >>>>>>> > >>>>>>> And set the parameter to 1 and 'F' for the first query and to 2 > >> and > >>>>>> 'M' for the second. > >>>>>>> > >>>>>>> Unfortunately there is a bug in DBReader so that cmd params are > >> not > >>>>>> properly set. > >>>>>>> This is the reason why I used db.executeQuery(..) instead of a > >>>>>> DBReader in the example above. > >>>>>>> I will fix this bug as soon as possible. > >>>>>>> > >>>>>>> Another thing we should do is to use the prepared statements > for > >>>>>> DBRecord.read (which in turn uses DBRowSet.readRecord(...)). > >>>>>>> > >>>>>>> As far as the pooling of prepared statements is concerned, if > >> it's > >>>> not > >>>>>> done by the data source already it can also be done by > subclassing > >>>> the > >>>>>> DBDatabaseDriver and overriding executeQuery() and / or > >> executeSQL() > >>>> and > >>>>>> do it yourself. But it is not necessary for Empire-db to provide > >>>> this. > >>>>>>> > >>>>>>> Kenji will this satisfy your needs? > >>>>>>> > >>>>>>> Regards, > >>>>>>> Rainer > >>>>>>> > >>>>>>> > >>>>>>> > >>>>>>> Matthew Bond wrote: > >>>>>>>> from: Matthew Bond [mailto:[email protected]] > >>>>>>>> to: [email protected]; empire-db- > >>>>>>>> re: AW: Prepared statement support? > >>>>>>>> > >>>>>>>> Hi Rainer, Hi Kenji, > >>>>>>>> > >>>>>>>> Rainer's comments are true in a Web Application scenario where > >> the > >>>>>>>> connection if got for a short time and then released again. > >> Empire > >>>> DB > >>>>>>>> can also be used in other scenarios, like a Fat Clients or > >> Command > >>>>>> Line > >>>>>>>> Utility tools, where a connection will probably be held for > the > >>>> whole > >>>>>>>> duration of the application lifetime and PooledStatements > >> could > >>>>>> bring > >>>>>>>> more performance. So it really depends on what you application > >>>> type > >>>>>> you > >>>>>>>> are programming. > >>>>>>>> > >>>>>>>> FYI: WebSphere too pools prepared statements (see page 2 of > >>>>>> http://www- > >>>>>>>> > >>>>>> > >>>> > >> > 03.ibm.com/systems/resources/systems_i_advantages_perfmgmt_pdf_stmntcac > h > >>>>>>>> e.pdf "WebSphere, however, will do the caching automatically. > >>>> When > >>>>>> you > >>>>>>>> execute a query, WebSphere determines if the SQL text is > >> already > >>>> in > >>>>>> the > >>>>>>>> cache and if so, it will use that cached statement instead of > >>>>>> preparing > >>>>>>>> a new one." ). So if EmpireDB was extended to make more use of > >>>>>> Prepared > >>>>>>>> Statements it would be advantageous. > >>>>>>>> > >>>>>>>> However as Rainer describes, the big benefit of using > EmpireDB > >> is > >>>>>> that > >>>>>>>> the selects are going to be way better than other ORM's as the > >>>>>> developer > >>>>>>>> hand crafts the "SQL" statement. > >>>>>>>> > >>>>>>>> The great thing is that it is Open Source so if you feel > >> strongly > >>>>>> about > >>>>>>>> the use of PreparedStatements, you could submit a Patch adding > >>>> this > >>>>>>>> functionality. > >>>>>>>> > >>>>>>>> Cheers > >>>>>>>> Matt > >>>>>>>> > >>>>>>>> -----Ursprüngliche Nachricht----- > >>>>>>>> Von: Rainer Döbele [mailto:[email protected]] > >>>>>>>> Gesendet: Donnerstag, 2. Dezember 2010 00:11 > >>>>>>>> An: [email protected]; empire-db- > >>>>>>>> [email protected] > >>>>>>>> Betreff: re: Prepared statement support? > >>>>>>>> > >>>>>>>> Dear Kenji, > >>>>>>>> > >>>>>>>> I have reviewed our code and thought about this subject again. > >>>>>>>> As you mentioned there is both a performance and a security > >> issue > >>>> to > >>>>>>>> consider. > >>>>>>>> For the moment I would like to focus on the performance issue > >> as > >>>>>>>> security can as well be established by other measures. > >>>>>>>> > >>>>>>>> It's pretty obvious to understand that creating a prepared > >>>> statement > >>>>>> and > >>>>>>>> executing it multiple times with varying parameters is > superior > >>>> over > >>>>>>>> creating a normal statement each time. But as far as I > >> understand > >>>> it, > >>>>>>>> the advantage of a ps exists only as long as the statement > >> lives, > >>>> and > >>>>>>>> ends when you close it. > >>>>>>>> > >>>>>>>> The problem is, that a prepared statement is created for a > >>>> particular > >>>>>>>> connection. In a web-application we usually use a connection > >> pool > >>>> and > >>>>>>>> the connection is fetched for a particular request. It is > >>>> extremely > >>>>>>>> rare, that the same statement is executed multiple times > within > >> a > >>>>>> single > >>>>>>>> request - whereas it is very likely that the same statement > >> needs > >>>> to > >>>>>> be > >>>>>>>> executed by other users' requests. As those other users have > >>>>>> different > >>>>>>>> connections they cannot share the same prepared statement. > >>>>>>>> > >>>>>>>> Here is a thread discussing this issue: > >>>>>>>> http://www.velocityreviews.com/forums/t644638-jdbc- > >>>> preparedstatement- > >>>>>> in- > >>>>>>>> a-multi-threaded-environment.html > >>>>>>>> > >>>>>>>> As Empire-db does not store or maintain a connection, it is > not > >>>>>> sensible > >>>>>>>> for us to store the actual JDBC prepared statement object. But > >>>> this > >>>>>>>> might not be necessary as it could be done on another level. > >>>> Possibly > >>>>>>>> the solution lies just in another Apache Project: Apache > >> Commons > >>>>>> DBCP. > >>>>>>>> http://commons.apache.org/dbcp/index.html > >>>>>>>> > >>>>>>>> From my understanding it should be possible to use a commons- > >> dbcp > >>>>>>>> connection pool that will also pool prepared statements. The > >>>>>> connections > >>>>>>>> returned by the pool can be used with Empire db just like a > >> normal > >>>>>> JDBC > >>>>>>>> connection. > >>>>>>>> Of course we still need to enforce and extend the generation > of > >>>>>> prepared > >>>>>>>> statement phrases beyond the CUD operations. > >>>>>>>> > >>>>>>>> Still we must keep in mind, that probably for most real world > >>>>>>>> applications the performance benefit of prepared statements > >> over > >>>>>> simple > >>>>>>>> statements is negligible, and it is our primary goal to > >> maintain > >>>>>>>> simplicity and transparency. > >>>>>>>> It is IMO far more important to be able to create efficient > >>>>>> statements - > >>>>>>>> and avoid the problem of OR-Mappers that usually work with > lots > >> of > >>>>>>>> simple operations. After all, one clever statement with server > >>>> side > >>>>>> db > >>>>>>>> logic will still execute a lot faster than 10 prepared > >> statements > >>>>>> with > >>>>>>>> trailed Java logic. > >>>>>>>> (Still the gloal is to have it all of course) > >>>>>>>> > >>>>>>>> Any more suggestions or remarks on this topic? > >>>>>>>> > >>>>>>>> Regards > >>>>>>>> Rainer > >>>>>>>> > >>>>>>>> > >>>>>>>> Kenji Nakamura wrote: > >>>>>>>>> from: Kenji Nakamura [mailto:[email protected]] > >>>>>>>>> to: [email protected] > >>>>>>>>> re Re: Prepared statement support? > >>>>>>>>> > >>>>>>>>> Rainer, > >>>>>>>>> > >>>>>>>>> Thank you for your reply. My comment are inline. > >>>>>>>>> > >>>>>>>>> On Wed, Dec 1, 2010 at 2:14 AM, Rainer Döbele > >> <[email protected]> > >>>>>>>>> wrote: > >>>>>>>>>> Hi Kenji, > >>>>>>>>>> > >>>>>>>>>> thanks for your interesting links about this subject. > >>>>>>>>>> > >>>>>>>>>> It is certainly true, that the performance of a prepared > >>>> statements > >>>>>>>>> is better when you execute it multiple times with varying > >>>> parameter > >>>>>>>>> values. > >>>>>>>>>> This is not always possible when varying statements with > >>>>>> conditional > >>>>>>>>> joins are created at runtime. > >>>>>>>>>> For a one-time statement using a prepared statement does not > >>>>>> execute > >>>>>>>>> faster than a normal statement. > >>>>>>>>> > >>>>>>>>> I understand the issue that the use of PreparedStatement > seems > >> to > >>>>>> have > >>>>>>>>> overhead and actually it may take longer if we measure it > with > >> a > >>>>>>>>> single execution from application developer's point of view, > >> but > >>>> the > >>>>>>>>> compiled result of the statement is kept added to Oracle's > >> cache > >>>> and > >>>>>>>>> it flushes the compiled results of the PreparedStatement > >> invoked > >>>>>> from > >>>>>>>>> different applications as the cache is managed per SID in > >> Oracle. > >>>> So > >>>>>>>>> it has negative impact from the DBA's point of view. It is > >> not > >>>> an > >>>>>>>>> issue as long as the DB is used as the data storage of a web > >>>>>>>>> application server and the performance of the app is only > >>>> concern, > >>>>>> but > >>>>>>>>> the assumption is not true when the DB is also used in data > >>>>>>>>> processing. > >>>>>>>>> > >>>>>>>>>> The inclusion of parameter values in the SQL text when > >>>> assembling > >>>>>>>>> statements is an advantage when it comes to logging (logging > >> of > >>>>>>>>> parameterized statements is not sufficient to track errors) > or > >>>> for > >>>>>> the > >>>>>>>>> creation of SQL scripts that are saved and executed later. > >>>>>>>>> > >>>>>>>>> I see your point. > >>>>>>>>> > >>>>>>>>>> > >>>>>>>>>> Currently Empire-db uses prepared statements by default only > >> for > >>>>>>>>> statements with BLOB and CLOB fields. > >>>>>>>>>> > >>>>>>>>>> However at least as far as update and insert statements are > >>>>>>>>>> concerned > >>>>>>>>> you can override the method useCmdParam() in DBCommandOracle, > >> but > >>>>>> you > >>>>>>>>> need to subclass the DBDatabaseDriverOracle and override > >>>>>> createCommand > >>>>>>>>> first. If you return true in useCmdParam(), then Empire-DB > >> will > >>>> use > >>>>>> a > >>>>>>>>> prepared statement and supply this value as a prepared > >> statement > >>>>>>>>> parameter. > >>>>>>>>> > >>>>>>>>> From the point of view of Oracle administrator, the primary > >>>> interest > >>>>>>>>> is how to reduce the # of hard parse and increase the hit > rate > >> of > >>>>>> the > >>>>>>>>> cache, and using PreparedStatement only for CUD operation is > >> not > >>>>>>>>> sufficient if the ratio of Select outweigh CUD operations. > >> From > >>>>>>>>> security point of view, Select statement with parameters > >>>> embedding > >>>>>>>>> user's input is as vulnerable as other DMLs, so the option to > >> use > >>>>>>>>> PreparedStatement for CUD operation doesn't address those > >>>> concerns, > >>>>>>>>> while it may be useful to improve the performance on > iterative > >>>>>>>>> operations. > >>>>>>>>> > >>>>>>>>>> > >>>>>>>>>> Personally I have used Empire-DB in many projects and > >>>> performance > >>>>>> or > >>>>>>>>> security have never been a problem. However, if you except to > >>>>>> execute > >>>>>>>>> 10.000 sql statements a minute then certainly this needs to > be > >>>>>>>>> thoroughly checked. > >>>>>>>>> > >>>>>>>>> It is nice to know the framework has been proven in > production > >>>>>>>>> environments. Our current performance test also doesn't show > >> the > >>>>>> hard > >>>>>>>>> parse is the primary culprit of the performance bottleneck, > so > >> it > >>>> is > >>>>>>>>> not an urgent problem, but I'd like prepare to answer the > >>>> questions > >>>>>>>>> from our DB engineers. > >>>>>>>>> > >>>>>>>>>> > >>>>>>>>>> I have created a new Jira (EMPIREDB-91) issue for us to > >> check, > >>>> how > >>>>>>>>> and where we can increase and optimize the use of prepared > >>>>>> statements. > >>>>>>>>> > >>>>>>>>> Thank you for the reaction. I registered myself to the watch > >>>> list. > >>>>>> Let > >>>>>>>>> me know if I can do something to make this forward. > >>>>>>>>> > >>>>>>>>> Lastly, I really thank you to share the framework in public. > I > >>>> have > >>>>>>>>> used Toplink, Hibernate, and iBatis, but I favor empire-db a > >> lot > >>>>>>>>> because of the simplicity and type-safe coding. It is very > >>>>>>>>> straightforward to customize to fulfill our specific needs > >> such > >>>> as > >>>>>> the > >>>>>>>>> support of TableFunction in Oracle. > >>>>>>>>> > >>>>>>>>> Regards, > >>>>>>>>> > >>>>>>>>> Kenji > >>>>>>>>> > >>>>>>>>>> > >>>>>>>>>> Regards > >>>>>>>>>> Rainer > >>>>>>>>>> > >>>>>>>>>> > >>>>>>>>>> Kenji Nakamura wrote: > >>>>>>>>>>> from: Kenji Nakamura [mailto:kenji_nakam...@diva- > >> america.com] > >>>>>>>>>>> to: [email protected] > >>>>>>>>>>> re: Prepared statement support? > >>>>>>>>>>> > >>>>>>>>>>> Hi, > >>>>>>>>>>> > >>>>>>>>>>> I got a question from one of our DB engineer about the use > >> of > >>>>>>>>> prepared > >>>>>>>>>>> statements. > >>>>>>>>>>> According to him, or a thread in AskTom, it is always > >> preferred > >>>> to > >>>>>>>>> use > >>>>>>>>>>> PreparedStatement instead of Statement whenever possible. > >>>>>>>>>>> > >>>>>>>>> > >>>>>> > >>>> > >> > http://asktom.oracle.com/pls/asktom/f?p=100:11:7607696421577136::::P11 > >>>>>>>>> _ > >>>>>>>>> Q > >>>>>>>>>>> UESTION_ID:1993620575194 > >>>>>>>>>>> > >>>>>>>>>>> As far as I looked at the code, PreparedStatement is not > >> used > >>>>>> other > >>>>>>>>>>> than DBDatabaseDriver class and the method is not used from > >>>> other > >>>>>>>>>>> code. > >>>>>>>>>>> > >>>>>>>>>>> My understanding is that creation of PreparedStatement has > >>>> certain > >>>>>>>>>>> overhead, but statement pooling introduced in JDBC 3.0 > >>>> mitigates > >>>>>>>>>>> the impact especially from application server point of > view. > >>>>>>>>>>> We use Oracle, and the DB engineer explained that the use > of > >>>>>>>>> statement > >>>>>>>>>>> floods the library cache in SGA and reduce the hit rate of > >>>>>>>>>>> pre-compiled statements so it has negative impact on entire > >> db, > >>>>>> and > >>>>>>>>>>> using PreparedStatement simply reduces the cost of hard > >> parse. > >>>>>>>>>>> > >>>>>>>>>>> Another aspect is about SQL injection prevention. I noticed > >>>> single > >>>>>>>>>>> quotes are escaped at DBDatabaseDriver#getValueString() > >> method, > >>>>>> but > >>>>>>>>>>> the preferred way to prevent SQL injection is to use > >>>>>>>>> PreparedStatement > >>>>>>>>>>> according to OWASP website. > >>>>>>>>>>> > >>>>>> > >> http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet > >>>>>>>>>>> > >>>>>>>>>>> Would you tell me the design philosophy or reasons not to > >> use > >>>> or > >>>>>>>>>>> provide the option to use prepared statement? Is it > >> possible, > >>>> or > >>>>>>>>> have > >>>>>>>>>>> a plan to support PreparedStatement? > >>>>>>>>>>> > >>>>>>>>>>> Thanks, > >>>>>>>>>>> > >>>>>>>>>>> Kenji Nakamura > >>>>>>>>>> > >>>>> > >>>> > >>>> > >>>> > >>>> -- > >>>> http://www.somatik.be > >>>> Microsoft gives you windows, Linux gives you the whole house. > >>> > >> > >> > >> > >> -- > >> http://www.somatik.be > >> Microsoft gives you windows, Linux gives you the whole house.
