 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Java Forum / General / February 2007Statement and Resultset
Hi all, I've a java method (included in a java class) like this above: public static ResultSet eseguiSelect(Connection conn,String query) throws SQLException { LogTrace log = new LogTrace(); log.trace("query : "+query); Statement stmt = null; ResultSet rs=null; try { stmt=conn.createStatement(); rs=stmt.executeQuery(query); } catch(SQLException sqle) { log.trace("SQLException : "+sqle.getMessage()); log.trace("SQLState : "+sqle.getSQLState()); log.trace("VendorError : "+sqle.getErrorCode()); try { rs.close(); stmt.close(); } catch(Exception exception) { } throw sqle; } return rs; } In this execution I don't close "stmt" and "rs" 'cause "rs" must be returned to caller of method; but this can be dangerous? i.e. this statement and resultset unclosed could remain active with consequent memory occupation? Thanks in advance JFM > Hi all, > I've a java method (included in a java class) like this above: [quoted text clipped - 33 lines] > Thanks in advance > JFM My understanding of how Java garbage collection is supposed to work is that once the last reference to an object is abandoned, its memory is flagged to be reclaimed. So, by rights, there should be no problem. In this case, I suspect that the database connection object is the one that has the most system wide effects, but those are often left active throughout program execution... (yes, I will check that sticky . key). "Andrea" <andre...@jumpy.it> wrote: >> Hi all, >> I've a java method (included in a java class) like this above: [quoted text clipped - 23 lines] >> catch(Exception exception) >> { } You should log this error, not ignore it. >> throw sqle; >> } [quoted text clipped - 4 lines] >> to caller of method; but this can be dangerous? i.e. this statement and >> resultset unclosed could remain active with consequent memory occupation? > My understanding of how Java garbage collection is supposed to work is > that once the last reference to an object is abandoned, its memory is > flagged to be reclaimed. So, by rights, there should be no problem. In > this case, I suspect that the database connection object is the one > that has the most system wide effects, but those are often left active > throughout program execution... (yes, I will check that sticky . key). Not a good idea to leave database resources open for long times. The issue here is not garbage collection but the release of database resources. Andrea, look into the "try { ... } finally { .... }" idiom. Personally, I transfer my results into value object lists (or sets, or ...) and close the result set before passing the results up. This frees database resources (or returns them to the pool) right away. Side note: Consider using PreparedStatement instead of Statement. The bottom line is that you must release external resources after you have used them. If you are not sure if they are released, you haven't written your code correctly. You must release external resources after you have used them. There are a number of ways to ensure the guarantee of resource release, most of which use the finally block. Use your favorite idiom, but remember, you must release your external resources. - Lew Lew wrote : > Side note: Consider using PreparedStatement instead of Statement. Hmm, I use a Statement for a single call to the database, but a PreparedStatement within a loop. Why would I want to incur the overhead of a PreparedStatment for a single call?  SignatureTest Sig > Why would I want to incur the overhead of a PreparedStatment for a > single call? To avoid the tempation to assemble SQL queries by concatenating unsafe strings. (Just a general point, nothing to do with the original question.) -- chris >> Why would I want to incur the overhead of a PreparedStatment for a >> single call? > > To avoid the tempation to assemble SQL queries by concatenating unsafe strings. > (Just a general point, nothing to do with the original question.) That is relevant to Wojtek's question. It is the first reason, or more generally, the precise control that a PreparedStatement provides. The protection against SQL injection security hacks. Also, the Statement gets compiled anyway, or the db engine won't be able to run it. So how does one avoid the overhead of a PreparedStatement when Statement incurs it anyway? Or are you referring to the fact that it takes two lines of code instead of one? - Lew Lew wrote : >>> Why would I want to incur the overhead of a PreparedStatment for a >>> single call? [quoted text clipped - 6 lines] > generally, the precise control that a PreparedStatement provides. The > protection against SQL injection security hacks. I do not understand what you mean by precise control? > Also, the Statement gets compiled anyway, or the db engine won't be able to > run it. [quoted text clipped - 4 lines] > Or are you referring to the fact that it takes two lines of code instead of > one? I always thought that a Statement was passed to the DB engine, where it is compiled then run. A PreparedStatement is passed to the DB engine where it is compiled. The compiled code is held until the connection is closed, thus using up a resource. Moreover it is a more complex resource, as a PreparedStatement must be able to replace the ?'s with passed parameters. As to the SQL injections, I build up SQL statements using a custom class (SQLBuffer) which escapes imbeded quotes in String parameters. SignatureWojtek :-) > I do not understand what you mean by precise control? The types of the set parameters that get passed to the ? parameters can be checked at compile time, for example. Lew wrote: >> Also, the Statement gets compiled anyway, or the db engine won't be >> able to run it. > I always thought that a Statement was passed to the DB engine, where it > is compiled then run. Correct. > A PreparedStatement is passed to the DB engine where it is compiled. Same overhead so far. > The compiled code is held until the connection is closed, thus using up a > resource. As to what the db engine holds or how long, I do not know any place that Java makes promises about that. PreparedStatement is an interface. The only sure thing is that the compilation is separate from the execution, not how long the compiled version hangs around. For all we know, the actual driver may hang on to Statements' compiled forms for just as long. In fact, I would not be surprised if the Statement in a driver were implemented by the same code as the PreparedStatement. The PreparedStatement, like the Statement from which it inherits, releases its resources on close(). > Moreover it is a more complex resource, as a PreparedStatement > must be able to replace the ?'s with passed parameters. That is true. The price of power. Notice that you thus get compile-time checking of the types of arguments to the ? parameters, a pretty strong benefit. > As to the SQL injections, I build up SQL statements using a custom class > (SQLBuffer) which escapes imbeded quotes in String parameters. Good. You really don't have to go through as much protection with PreparedStatements, so perhaps that helps offset the complexity. Plus that pruning step adds overhead, so the overhead argument by itself might tilt in favor of PreparedStatement because of this. There really isn't any overhead of PreparedStatement compared to Statement that I know of, other than the additional complexity of use - but that complexity can be offset in other parts of the program. - Lew Lew wrote : >> I always thought that a Statement was passed to the DB engine, where it is >> compiled then run. [quoted text clipped - 15 lines] > be surprised if the Statement in a driver were implemented by the same code > as the PreparedStatement. No, it would have to be different. A Statement contains all, whereas a PreparedStatement has place holders for the information, which would require index pointers to where the information is to be placed. > The PreparedStatement, like the Statement from which it inherits, releases > its resources on close(). [quoted text clipped - 5 lines] > checking of the types of arguments to the ? parameters, a pretty strong > benefit. Not at DB compile time, but when the ? parameters are filled. You need to use the correct parameter type for the numbered index. Which also creates a potential for mis-placing which parameter goes where. I use a LOT of comments between where the PreparedStatement is built up, to where the parameters are filled in. >> As to the SQL injections, I build up SQL statements using a custom class >> (SQLBuffer) which escapes imbeded quotes in String parameters. [quoted text clipped - 3 lines] > pruning step adds overhead, so the overhead argument by itself might tilt in > favor of PreparedStatement because of this. Well, the class does more than that. I have abstracted the tables and their columns, which I also pass into SQLBuffer. It then extracts the actual table name and column name from the passed Table and Column classes (I *like* the compiler to catch errors for me :-)) > There really isn't any overhead of PreparedStatement compared to Statement > that I know of, other than the additional complexity of use - but that > complexity can be offset in other parts of the program. Ah, trade-offs, trade-offs :-) SignatureWojtek :-) Lew wrote: >> In fact, I would not be surprised if the >> Statement in a driver were implemented by the same code as the >> PreparedStatement. > No, it would have to be different. A Statement contains all, whereas a > PreparedStatement has place holders for the information, which would > require index pointers to where the information is to be placed. What I meant was substantially the same. If I were writing a DB driver, I'd make my Statement implementor a thin layer over my PreparedStatement implementor, so literally the real work is done by PreparedStatement in either case. Lew wrote: >> Notice that you thus get >> compile-time checking of the types of arguments to the ? parameters, a >> pretty strong benefit. > Not at DB compile time, but when the ? parameters are filled. That is what I was talking about. > You need to use the correct parameter type for the numbered index. I do not see that as an exceptional burden. I have to know the data types to fill in the Statement, too, only a Statement doesn't provide /any/ assistance to the matchup. Yes, there are more lines of code in the PreparedStatement usage - the payback is that extra little boost of type checking. > Which also creates a potential for mis-placing which parameter goes > where. I use a LOT of comments between where the PreparedStatement is > built up, to where the parameters are filled in. Don't you have to be careful about which parameter goes where in the concatenation phase of your SQL String for a Statement? This validates Chris's paranoia about how careful programmers are. > As to the SQL injections, I build up SQL statements using a custom > class (SQLBuffer) which escapes imbeded quotes in String parameters. ... > Well, the class does more than that. I have abstracted the tables and > their columns, which I also pass into SQLBuffer. It then extracts the > actual table name and column name from the passed Table and Column > classes (I *like* the compiler to catch errors for me :-)) Chris Uppal spoke to why, at least going forward, using PreparedStatement can help ease the programming burden here. No one is arguing to eliminate surface edits. At the very least, PreparedStatement provides extra security on data types and values for a defense in depth against data anomalies. Chris also pointed to how a clever DB driver might even leverage PreparedStatement to perform better than a "regular" Statement. However, it is really impossible to generalize an implementation characteristic from an interface. PreparedStatement is an interface subtype of the Statement interface. In particular, one can not make any averrals about their relative performance. So the only meaningful comparison between the two is in terms of how they aid program design and construction. The question was why one /would/ use PreparedStatements. It might not be that you always should use them, but at least one can be aware of the advantages they confer to offset their complexity, and that the performance burden is likely an urban legend. - Lew Lew wrote : > So the only meaningful comparison between the two is in terms of how they aid > program design and construction. [quoted text clipped - 3 lines] > they confer to offset their complexity, and that the performance burden is > likely an urban legend. Well, any performance hit would probably be within the DB engine, rather than Java. But yes, I get the thrust of your argument. SignatureWojtek :-) Lew wrote : >> So the only meaningful comparison between the two is in terms of how >> they aid program design and construction. [quoted text clipped - 6 lines] > Well, any performance hit would probably be within the DB engine, rather > than Java. In fact, the DB side could be completely unable to tell the difference, since the JDBC layer could hide it. - Lew > > Well, any performance hit would probably be within the DB engine, rather > > than Java. > > In fact, the DB side could be completely unable to tell the difference, since > the JDBC layer could hide it. In fact, I believe that MySQL 4 did not support prepared statements, so the JDBC driver for MySQL faked them. It's a completely valid approach, so long as there's no way to tell from the application that there's no real "prepare" step. > Lew wrote : >>> So the only meaningful comparison between the two is in terms of how [quoted text clipped - 10 lines] > In fact, the DB side could be completely unable to tell the difference, > since the JDBC layer could hide it. Do a Google search on: sql prepared statement -java -jdbc The first return was an IBM DB/2 manual http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2. udb.doc/admin/r0000975.htm About 2/3rds way down the page is this statement: "A prepared statement can be executed many times. Indeed, if a prepared statement is not executed more than once and does not contain parameter markers, it is more efficient to use the EXECUTE IMMEDIATE statement rather than the PREPARE and EXECUTE statements." Another entry was the PostrgeSQL at address http://www.postgresql.org/docs/current/static/sql-prepare.html where it states in the Notes: "In some situations, the query plan produced for a prepared statement will be inferior to the query plan that would have been chosen if the statement had been submitted and executed normally. This is because when the statement is planned and the planner attempts to determine the optimal query plan, the actual values of any parameters specified in the statement are unavailable. PostgreSQL collects statistics on the distribution of data in the table, and can use constant values in a statement to make guesses about the likely result of executing the statement. Since this data is unavailable when planning prepared statements with parameters, the chosen plan may be suboptimal." For Microsoft at address http://support.microsoft.com/kb/224587 it states "Ideally, an application must prepare a SQL statement one time and run it several times. This saves the Optimizer the cost of compiling a new plan each time the statement is executed. Therefore, the number of Exec Prepared SQL events should be much larger than the number of Prepare SQL events. If the number of Prepare SQL events is roughly equivalent to the number of Exec Prepared SQLevents, this may indicate that the application is not making good use of the prepare/execute model. It is best not to prepare a statement that is only going to be executed a single time." So it seems that the DB engine does more work, rather than JDBC. And that the PreparedStatement may be less efficient than a Statement. If the underlying DB does not support PreparedStatement, then of course the JDBC driver should implement it. Of course it must then also handle any special characters as per Chris post. > What I meant was substantially the same. If I were writing a DB driver, > I'd make my Statement implementor a thin layer over my PreparedStatement > implementor, so literally the real work is done by PreparedStatement in > either case. I do not think that would be a good decision. That would add the overhead of PreparedStatement (and it does exist with some databases) to the usage of Statement where the programmer did not expect it. > However, it is really impossible to generalize an implementation > characteristic from an interface. PreparedStatement is an interface [quoted text clipped - 3 lines] > So the only meaningful comparison between the two is in terms of how > they aid program design and construction. The choice of name gives a clear indication of what JDBC driver implementators are supposed to do with it. And that do have performance implications. Not necessarily a simple obvious conclusion, but none the less a conclusion. Arne > The choice of name gives a clear indication of what JDBC driver > implementators are supposed to do with it. [quoted text clipped - 3 lines] > Not necessarily a simple obvious conclusion, but none the > less a conclusion. I see your point, but I just do not see how a programmer can make *any* performance decisions about code to which the only access is an interface. Furthermore, others on this thread have offered anecdotal evidence that the naive expectation is, in fact, violated in the real world. Furthermore, the "clear indication" says absolutely nothing about performance, only usage structure. - Lew >> The choice of name gives a clear indication of what JDBC driver >> implementators are supposed to do with it. [quoted text clipped - 7 lines] > performance decisions about code to which the only access is an > interface. I can repeat it once more: THE NAME. > Furthermore, others on this thread have offered anecdotal > evidence that the naive expectation is, in fact, violated in the real > world. Well - I scanned through the thread - and I did not see such. > Furthermore, the "clear indication" says absolutely nothing about > performance, only usage structure. Wrong. PreparedStatement gives a clear indication of that it is expected to use a SQL prepared statement if the database supports such and that gives an indication of performance - and that is both with and without that support. Arne > As to the SQL injections, I build up SQL statements using a custom > class (SQLBuffer) which escapes imbeded quotes in String parameters. That is an approach that I'd like to see stamped-out. I don't know how competent you may be -- I'm willing to believe that you've got it exactly right -- but I don't trust /most/ programmers to get it right. In this case I don't trust /myself/ to get it right, and I'm at the careful end of the spectrum as a programmer. You have to know /exactly/ how the real DBMS's parser will work, including its responses to malformed input, to non-ASCII characters in the input, and whatever bugs it may have. You also have to take account of what bugs it may develop in later releases. If you think it's worthwhile then you have to make your code work with all possible databases (I wouldn't think it's worth it myself); but if you don't then you should include some way to ensure that using it with a "wrong" DB will cause runtime errors (exceptions, say) rather than opening an invisible security hole. Of course, having such a module is /far/ better than concatenating unsafe strings without thought. It's good, but I just don't think it's good /enough/ -- and it is actually quite a bit more effort than using prepared statements. -- chris Chris Uppal wrote : >> As to the SQL injections, I build up SQL statements using a custom >> class (SQLBuffer) which escapes imbeded quotes in String parameters. [quoted text clipped - 4 lines] > I don't trust /myself/ to get it right, and I'm at the careful end of the > spectrum as a programmer. I fully understand that paranoia :-) I abhor "magic numbers" and values. I use lots of constants and let the compiler catch as many mistakes as I can make it catch. > You have to know /exactly/ how the real DBMS's parser will work, including > its responses to malformed input, to non-ASCII characters in the input, and > whatever bugs it may have. Malformed input by the user is checked way above the SQL layer. All user input is conditioned (leading/training spaces, double spaces, numeric values, dates, etc) then checked for validity before the business logic is even evaluated. > You also have to take account of what bugs it may > develop in later releases. If you think it's worthwhile then you have to > make your code work with all possible databases (I wouldn't think it's worth > it myself); but if you don't then you should include some way to ensure that > using it with a "wrong" DB will cause runtime errors (exceptions, say) rather > than opening an invisible security hole. The actual code for a particular DB is held in a class specific to that DB (for each use case). The command class accesses the DB method through an abstract class, which the DB class extends. The only interaction is through a data class wich is passed around. > Of course, having such a module is /far/ better than concatenating unsafe > strings without thought. It's good, but I just don't think it's good > /enough/ -- and it is actually quite a bit more effort than using prepared > statements. And I have learned something. I was only ever concerned with text delimiters, ie the single quote used in most DB's, but I had never considered problems with non-ASCII characters. Need to do some research here. I will need to re-think on how I use Statement vs PreparedStatement, though at this point in the application we are talking about many thousands of places. It would mean major re-factoring and subsequent regression testing. SignatureWojtek :-) > I will need to re-think on how I use Statement vs PreparedStatement, > though at this point in the application we are talking about many > thousands of places. It would mean major re-factoring and subsequent > regression testing. I wouldn't dream of suggesting that you change your current system (which, in fact, sounds very well thought out). -- chris > So how does one avoid the overhead of a PreparedStatement when Statement > incurs it anyway? If the JDBC implementation provides PreparedStatement pooling, then the overhead may even be negative. (I don't know how many implementation /do/ provide PreparedStatement pooling -- I onlly heard of it for the first time yesterday.) -- chris > Lew wrote : >> Side note: Consider using PreparedStatement instead of Statement. [quoted text clipped - 4 lines] > Why would I want to incur the overhead of a PreparedStatment for a > single call? 1) Easy handling of quotes. 2) Easy handling of date formats. 3) The special case of #1 = protection against SQL injection. Arne > In this execution I don't close "stmt" and "rs" 'cause "rs" must be > returned to caller of method; but this can be dangerous? i.e. this > statement and resultset unclosed could remain active with consequent > memory occupation? Thanks in advance I can't find any information on what's guaranteed and what's not (not even in the JDBC spec). From a cautious point of view (which I would normally take) it seems that relying on automatic cleanup is not a good idea, if it is not guaranteed to work. And so, from that point of view, it seems that you design is flawed. On the other hand, finalisation and related techniques are there to be used for this kind of thing, so there's a good chance that it will work perfectly in practise (I suppose that depends on the driver vendor too). Can't help much, I'm afraid. I just wanted to say that there doesn't seem to be a well-defined answer. -- chris > Hi all, > I've a java method (included in a java class) like this above: [quoted text clipped - 33 lines] > Thanks in advance > JFM Hello all, Why wouldn't you use CachedRowSet instead ? here is a tutorial http://www.onjava.com/pub/a/onjava/2004/06/23/cachedrowset.html DNas Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.