本人新手，在个外站上看见的文章，感觉可能有用，请论坛大神帮忙翻译下 谢谢啦

克拉星空

以下是一段节选 全文在附件里 谢谢啦

I was positively surprised to see how popular my recent listing about 10 Common Mistakes Java Developers Make when WritingSQL was, both on my ownblog and on my syndication partner DZone. The popularity shows acouple of things:

·
How importantSQL is to the professional Java world.

·
How common itis to forget about some basic SQL things.

·
How wellSQL-centric libraries such as jOOQor MyBatis are responding tomarket needs, by embracing SQL. An amusing fact is that users have evenmentioned my blog post on SLICK’s mailing list. SLICK is a non-SQL-centric databaseaccess library in Scala. LikeLINQ (and LINQ-to-SQL) it focuses on language integration,not on SQL code generation.

Anyway, the common mistakes I listed previously are far from complete, soI will treat you to a sequel of 10 subtly less common, yet equally interestingmistakes Java developers make when writing SQL.

1. Not using PreparedStatements

Interestingly, this mistake or misbelief still surfaces blogs, forumsand mailing lists many years after the appearance of JDBC, even if it is abouta very simple thing to remember and to understand. It appears that somedevelopers refrain from using PreparedStatements for any of these reasons:

·
They don’tknow about PreparedStatements

·
They thinkthat PreparedStatements are slower

·
They thinkthat writing a PreparedStatement takes more effort

First off, let’s bust the above myths. In 96% of the cases, you’re betteroff writing a PreparedStatement rather than a static statement. Why? For simplereasons:

·
You can omitsyntax errors originating from bad string concatenation when inlining bindvalues.

·
You can omitSQL injection vulnerabilities from bad string concatenation when inlining bindvalues.

·
You can avoidedge-cases when inlining more “sophisticated” data types, such as TIMESTAMP,binary data, and others.

·
You can keepopen PreparedStatements around for a while, reusing them with new bind valuesinstead of closing them immediately (useful in Postgres, for instance).

·
You can makeuse of adaptive cursor sharing (Oracle-speak) in more sophisticateddatabases. This helps prevent hard-parsing SQL statements for every new set ofbind values.

Convinced? Yes. Note, there are some rare cases when you actually want toinline bind values in order to give your database’s cost-based optimiser someheads-up about what kind of data is really going to be affected by the query.Typically, this results in “constant” predicates such as:

·
DELETED = 1

·
STATUS = 42

But it shouldn’t result in “variable” predicates such as:

·
FIRST_NAMELIKE “Jon%”

·
AMOUNT >19.95

Note that modern databases implement bind-variable peeking. Hence, bydefault, you might as well use bind values for all your query parameters. Notealso that higher-level APIs such as JPACriteriaQuery or jOOQwill help you generate PreparedStatements and bind values very easily andtransparently when writing embedded JPQL or embedded SQL.

More background info:

·
Caveats ofbind value peeking: An interesting blog post by Oracle Guru Tanel Poder on the subject

·
Cursorsharing. An interesting Stack Overflow question.

The Cure:

By default, always use PreparedStatements instead of static statements. Bydefault, never inline bind values into your SQL.

lsekfe

以下是一段节选 全文在附件里 谢谢啦
I was positively surprised to see how popular my recent listing  ...
克拉星空 发表于 2013-8-15 16:03

    英文不好的，帮顶！

LorrieL

第一段内容是讲他写的十件TAV开发者使用SQL时常犯的十个错误。
SQL会影响JAVA程序的专业性，并且人人常常忘记关于SQL最基本要注意的事情。
下面他将列出这十个常见的错误。
其实就是讲SQL在JAVA开发过程中遇到的一些问题。你只需要看看标题，然后看一下他的列出的reason，然后分析一下他举得例子即可。不需要每句话都看，有些只是他自己的感想。

yuna4217

多写几个中文注释下嘛

克拉星空

回复 3# Geiler

这样呀，谢谢啦