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_po > >> 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_stmntcach > >> >> 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:[email protected]] > >> >>>>> 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.
