StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Always use "prepare" there will be an equivalent to prepareStatement but the exact function name will depend on your database and driver combination.</p> <p>The advantages of a prepared statement over an execute(String) are many:-</p> <p>The statement is parsed and an access plan determind only once when the "prepare" statement is executed. Depending on how many times you run the statement this can result in much better performance.</p> <p>You dont need to worry about special characters in string data when you pass it through setString(). In an execute(String) any single quotes or semicolons in the data will result in a parse error.</p> <p>Worse this is how "sql injection" attacks work. If a string something like "x' from cust_table; delete from cust_table; select " is entered as data it might well result in the delete statement being parsed and executed.</p> <p>Handling of numbers is much more efficient. A setInt call take an integer value as is the for the equvalent SQL string you must convert to characters then the DBMS has to convert it back to an integer.</p> <p>Readability. You code a single SQL statement with a few question marks where the variables go which is relatively easy to read, as opposed to mentally parsing and analysing a series of string concatinations will extra noise for escaped quotes etc.</p> <p>There are however a couple of cases where execute(String) is actually better. </p> <p>Where your keys are very unevenly distributed. E.G. If 95% of your customers live in the USA and you want to list the 4% who live in Canada then "where country = ?" would normally result in a table space scan while with "where country = 'CA'" you have some chance of using an index.</p> <p>The other case is where the user can enter or omit several search criteria. Its much better to build an SQL string for the criteria you are given than construct a complex query which copes with all possible permutaions of the input criteria.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload