blocks|key|1668654|text|我倾向于使用Spring的命名JDBC参数，这样我就可以编写一个像"select+*+from+blah+where+colX=‘：someValue’“这样的标准字符串；我认为这是非常可读的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1668655|另一种方法是在单独的.sql文件中提供字符串，然后使用实用程序方法读取中的内容。|1668656|哦，同样值得一看的是Squill：https://squill.dev.java.net/docs/tutorial.html|offset|length|1668657|entityMap|0|LINK|mutability|MUTABLE|url|https://squill.dev.java.net/docs/tutorial.html^0|0|0|H|1A|0|0^^$0|@$1|2|3|4|5|6|7|P|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|Q|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@$F|S|G|T|1|U]]|A|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|A|$]]]|I|$J|$5|K|L|M|A|$N|O]]]]

I tend to use Spring's Named JDBC Parameters so I can write a standard string like "select * from blah where colX=':someValue'"; I think that's pretty readable.

An alternative would be to supply the string in a separate .sql file and read the contents in using a utility method.

Oh, also worth having a look at Squill: <a href="https://squill.dev.java.net/docs/tutorial.html" rel="noreferrer">https://squill.dev.java.net/docs/tutorial.html</a>

blocks|key|1453747|text|如果您将SQL字符串放在一个属性文件中，然后将其读入，则可以将SQL字符串保存在一个纯文本文件中。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1453748|这并没有解决SQL类型的问题，但至少它使得从TOAD或sqlplus进行复制和粘贴变得容易得多。|1453749|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

If you put the SQL strings in a properties file and then read that in you can keep the SQL strings in a plain text file.

That doesn't solve the SQL type issues, but at least it makes copying&amp;pasting from TOAD or sqlplus much easier.

blocks|key|1453810|text|为什么要手工生成所有的sql？你有没有看过像Hibernate这样的ORM，根据你的项目，它可能会至少完成你所需的95%25，用一种比原始SQL更干净的方式来做，如果你需要获得最后一点的性能，你可以创建需要手工调优的SQL查询。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1453811|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

Why do you want to generate all the sql by hand? Have you looked at an ORM like Hibernate Depending on your project it will probably do at least 95% of what you need, do it in a cleaner way then raw SQL, and if you need to get the last bit of performance you can create the SQL queries that need to be hand tuned.

blocks|key|1453867|text|首先，考虑在准备好的语句中使用查询参数：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1453868|PreparedStatement+stm+=+c.prepareStatement("UPDATE+user_table+SET+name=?+WHERE+id=?");
stm.setString(1,+"the+name");
stm.setInt(2,+345);
stm.executeUpdate();|code-block|syntax|javascript|1453869|可以做的另一件事是将所有查询保存在属性文件中。例如，在queries.properties文件中可以放置上面的查询：|1453870|update_query=UPDATE+user_table+SET+name=?+WHERE+id=?|1453871|然后在一个简单的实用程序类的帮助下：|1453872|public+class+Queries+{

++++private+static+final+String+propFileName+=+"queries.properties";
++++private+static+Properties+props;

++++public+static+Properties+getQueries()+throws+SQLException+{
++++++++InputStream+is+=+
++++++++++++Queries.class.getResourceAsStream("/"+%2B+propFileName);
++++++++if+(is+==+null){
++++++++++++throw+new+SQLException("Unable+to+load+property+file:+"+%2B+propFileName);
++++++++}
++++++++//singleton
++++++++if(props+==+null){
++++++++++++props+=+new+Properties();
++++++++++++try+{
++++++++++++++++props.load(is);
++++++++++++}+catch+(IOException+e)+{
++++++++++++++++throw+new+SQLException("Unable+to+load+property+file:+"+%2B+propFileName+%2B+"\n"+%2B+e.getMessage());
++++++++++++}+++++++++++
++++++++}
++++++++return+props;
++++}

++++public+static+String+getQuery(String+query)+throws+SQLException{
++++++++return+getQueries().getProperty(query);
++++}

}|1453873|您可以按如下方式使用查询：|1453874|PreparedStatement+stm+=+c.prepareStatement(Queries.getQuery("update_query"));|1453875|这是一个相当简单的解决方案，但效果很好。|1453876|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Y|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|11|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

First of all consider using query parameters in prepared statements:

<pre><code>PreparedStatement stm = c.prepareStatement("UPDATE user_table SET name=? WHERE id=?");
stm.setString(1, "the name");
stm.setInt(2, 345);
stm.executeUpdate();
</code></pre>

The other thing that can be done is to keep all queries in properties file. For example
in a queries.properties file can place the above query:

<pre><code>update_query=UPDATE user_table SET name=? WHERE id=?
</code></pre>

Then with the help of a simple utility class:

<pre><code>public class Queries {

 private static final String propFileName = "queries.properties";
 private static Properties props;

 public static Properties getQueries() throws SQLException {
 InputStream is = 
 Queries.class.getResourceAsStream("/" + propFileName);
 if (is == null){
 throw new SQLException("Unable to load property file: " + propFileName);
 }
 //singleton
 if(props == null){
 props = new Properties();
 try {
 props.load(is);
 } catch (IOException e) {
 throw new SQLException("Unable to load property file: " + propFileName + "\n" + e.getMessage());
 } 
 }
 return props;
 }

 public static String getQuery(String query) throws SQLException{
 return getQueries().getProperty(query);
 }

}
</code></pre>

you might use your queries as follows:

<pre><code>PreparedStatement stm = c.prepareStatement(Queries.getQuery("update_query"));
</code></pre>

This is a rather simple solution, but works well.

blocks|key|1668933|text|我想知道你是不是想要像Squiggle这样的东西。还有一个非常有用的东西是jDBI。不过，它不会对查询有所帮助。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1668934|entityMap|0|LINK|mutability|MUTABLE|url|http://code.google.com/p/squiggle-sql/|1|http://jdbi.codehaus.org/^0|B|8|0|11|4|1|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]|$A|R|B|S|1|T]]|C|$]]|$1|D|3|-4|5|6|7|U|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]|L|$5|G|H|I|C|$J|M]]]]

I am wondering if you are after something like <a href="http://code.google.com/p/squiggle-sql/" rel="noreferrer">Squiggle</a>. Also something very useful is <a href="http://jdbi.codehaus.org/" rel="noreferrer">jDBI</a>. It won't help you with the queries though.

blocks|key|1669014|text|我会去看看Spring+JDBC。每当我需要以编程方式执行SQL时，我都会使用它。示例：|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1669015|int+countOfActorsNamedJoe
++++=+jdbcTemplate.queryForInt("select+count(0)+from+t_actors+where+first_name+=+?",+new+Object[]{"Joe"});|code-block|syntax|javascript|1669016|它对于任何类型的sql执行都非常有用，尤其是查询；它将帮助您将结果集映射到对象，而不会增加完整ORM的复杂性。|1669017|entityMap|0|LINK|mutability|MUTABLE|url|http://static.springframework.org/spring/docs/2.0.x/reference/jdbc.html^0|5|B|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|D|3|E|5|F|7|W|8|@]|9|@]|C|$G|H]]|$1|I|3|J|5|6|7|X|8|@]|9|@]|C|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|C|$]]]|L|$M|$5|N|O|P|C|$Q|R]]]]

I would have a look at <a href="http://static.springframework.org/spring/docs/2.0.x/reference/jdbc.html" rel="noreferrer">Spring JDBC</a>. I use it whenever I need to execute SQLs programatically. Example:

<pre><code>int countOfActorsNamedJoe
 = jdbcTemplate.queryForInt("select count(0) from t_actors where first_name = ?", new Object[]{"Joe"});
</code></pre>

It's really great for any kind of sql execution, especially querying; it will help you map resultsets to objects, without adding the complexity of a complete ORM.

blocks|key|1669083|text|除了PreparedStatements中的长SQL字符串(可以很容易地在文本文件中提供并作为资源加载)之外，如何获得字符串连接？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1669084|您不会直接创建SQL字符串，对吧？这是编程中最大的禁忌。请使用PreparedStatements，并将数据作为参数提供。它极大地减少了SQL注入的机会。|1669085|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|F|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|G|8|@]|9|@]|A|$]]|$1|D|3|-4|5|6|7|H|8|@]|9|@]|A|$]]]|E|$]]

How do you get string concatenation, aside from long SQL strings in PreparedStatements (that you could easily provide in a text file and load as a resource anyway) that you break over several lines?

You aren't creating SQL strings directly are you? That's the biggest no-no in programming. Please use PreparedStatements, and supply the data as parameters. It reduces the chance of SQL Injection vastly.

blocks|key|1454179|text|我赞同使用像Hibernate这样的ORM的建议。但是，在某些情况下，这是不起作用的，所以我将利用这个机会吹嘘我帮助编写的一些东西：SqlBuilder是一个java库，用于使用“构建器”风格动态构建sql语句。它相当强大，相当灵活。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1454180|entityMap|0|LINK|mutability|MUTABLE|url|http://openhms.sourceforge.net/sqlbuilder/^0|1U|A|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

I second the recommendations for using an ORM like Hibernate. However, there are certainly situations where that doesn't work, so I'll take this opportunity to tout some stuff that i've helped to write: <a href="http://openhms.sourceforge.net/sqlbuilder/" rel="nofollow noreferrer">SqlBuilder</a> is a java library for dynamically building sql statements using the "builder" style. it's fairly powerful and fairly flexible.

blocks|key|1669223|text|对于任意SQL，请使用jOOQ。jOOQ目前支持SELECT、INSERT、UPDATE、DELETE、TRUNCATE和MERGE。您可以像这样创建SQL：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|1669224|String+sql1+=+DSL.using(SQLDialect.MYSQL)++
+++++++++++++++++.select(A,+B,+C)
+++++++++++++++++.from(MY_TABLE)
+++++++++++++++++.where(A.equal(5))
+++++++++++++++++.and(B.greaterThan(8))
+++++++++++++++++.getSQL();

String+sql2+=+DSL.using(SQLDialect.MYSQL)++
+++++++++++++++++.insertInto(MY_TABLE)
+++++++++++++++++.values(A,+1)
+++++++++++++++++.values(B,+2)
+++++++++++++++++.getSQL();

String+sql3+=+DSL.using(SQLDialect.MYSQL)++
+++++++++++++++++.update(MY_TABLE)
+++++++++++++++++.set(A,+1)
+++++++++++++++++.set(B,+2)
+++++++++++++++++.where(C.greaterThan(5))
+++++++++++++++++.getSQL();|code-block|syntax|javascript|1669225|您也可以使用jOOQ直接执行它，而不是获取SQL字符串。看见|1669226|http://www.jooq.org|1669227|(免责声明:我为jOOQ背后的公司工作)|1669228|entityMap|0|LINK|mutability|MUTABLE|url|http://www.jooq.org/|1^0|O|6|V|6|12|6|19|6|1G|8|1P|5|B|4|0|0|0|0|0|J|1|0|0^^$0|@$1|2|3|4|5|6|7|Z|8|@$9|10|A|11|B|C]|$9|12|A|13|B|C]|$9|14|A|15|B|C]|$9|16|A|17|B|C]|$9|18|A|19|B|C]|$9|1A|A|1B|B|C]]|D|@$9|1C|A|1D|1|1E]]|E|$]]|$1|F|3|G|5|H|7|1F|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|1G|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|1H|8|@]|D|@$9|1I|A|1J|1|1K]]|E|$]]|$1|O|3|P|5|6|7|1L|8|@]|D|@]|E|$]]|$1|Q|3|-4|5|6|7|1M|8|@]|D|@]|E|$]]]|R|$S|$5|T|U|V|E|$W|X]]|Y|$5|T|U|V|E|$W|X]]]]

For arbitrary SQL, use <a href="http://www.jooq.org" rel="noreferrer">jOOQ</a>. jOOQ currently supports <code>SELECT</code>, <code>INSERT</code>, <code>UPDATE</code>, <code>DELETE</code>, <code>TRUNCATE</code>, and <code>MERGE</code>. You can create SQL like this:

<pre><code>String sql1 = DSL.using(SQLDialect.MYSQL) 
 .select(A, B, C)
 .from(MY_TABLE)
 .where(A.equal(5))
 .and(B.greaterThan(8))
 .getSQL();

String sql2 = DSL.using(SQLDialect.MYSQL) 
 .insertInto(MY_TABLE)
 .values(A, 1)
 .values(B, 2)
 .getSQL();

String sql3 = DSL.using(SQLDialect.MYSQL) 
 .update(MY_TABLE)
 .set(A, 1)
 .set(B, 2)
 .where(C.greaterThan(5))
 .getSQL();
</code></pre>

Instead of obtaining the SQL string, you could also just execute it, using jOOQ. See

<a href="http://www.jooq.org" rel="noreferrer">http://www.jooq.org</a>

(Disclaimer: I work for the company behind jOOQ)

blocks|key|1454318|text|你也可以看看MyBatis+(www.mybatis.org)。它可以帮助您在java代码之外编写SQL语句，并将sql结果映射到java对象中。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1454319|entityMap|0|LINK|mutability|MUTABLE|url|http://www.mybatis.org/^0|F|F|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

You can also have a look at MyBatis (<a href="http://www.mybatis.org" rel="nofollow">www.mybatis.org</a>) . It helps you write SQL statements outside your java code and maps the sql results into your java objects among other things.

blocks|key|1669359|text|读取XML文件。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1669360|您可以从XML文件中读取它。它易于维护和使用。有一些标准的STaX、DOM、SAX解析器可以用来在java中编写几行代码。|1669361|使用属性做更多事情|1669362|您可以在标记上包含一些带有属性的语义信息，以帮助对SQL执行更多操作。这可以是方法名称或查询类型，也可以是任何有助于减少代码编写的内容。|1669363|维护|1669364|您可以将xml放在jar之外并轻松地对其进行维护。与属性文件具有相同的好处。|1669365|转换|1669366|XML是可扩展的，并且很容易转换为其他格式。|1669367|用例|1669368|Metamug+uses+xml+to+configure+REST+resource+files+with+sql.|offset|length|1669369|entityMap|0|LINK|mutability|MUTABLE|url|https://metamug.com/docs/resource-file.php^0|0|0|0|0|0|0|0|0|0|0|1N|0|0^^$0|@$1|2|3|4|5|6|7|13|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|14|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|15|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|16|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|17|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|18|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|19|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1A|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1B|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|1C|8|@]|9|@$T|1D|U|1E|1|1F]]|A|$]]|$1|V|3|-4|5|6|7|1G|8|@]|9|@]|A|$]]]|W|$X|$5|Y|Z|10|A|$11|12]]]]

<h3>Read an XML file.</h3>

You can read it from an XML file. Its easy to maintain and work with.
There are standard STaX, DOM, SAX parsers available out there to make it few lines of code in java.

<h3>Do more with attributes</h3>

You can have some semantic information with attributes on the tag to help do more with the SQL. This can be the method name or query type or anything that helps you code less.

<h3>Maintaince</h3>

You can put the xml outside the jar and easily maintain it. Same benefits as a properties file. 

<h3>Conversion</h3>

XML is extensible and easily convertible to other formats. 

<h3> Use Case </h3>

<a href="https://metamug.com/docs/resource-file.php" rel="nofollow noreferrer">Metamug uses xml to configure REST resource files with sql.</a>

I want to build an SQL string to do database manipulation (updates, deletes, inserts, selects, that sort of thing) - instead of the awful string concat method using millions of "+"'s and quotes which is unreadable at best - there must be a better way. 

I did think of using MessageFormat - but its supposed to be used for user messages, although I think it would do a reasonable job - but I guess there should be something more aligned to SQL type operations in the java sql libraries.

Would Groovy be any good?

Cleanest way to build an SQL string in Java

数据库

我想构建一个SQL字符串来执行数据库操作(更新、删除、插入、选择，诸如此类的事情)-而不是使用数百万个“+”和引号的糟糕的字符串连接方法，这充其量是不可读的-必须有更好的方法。我确实考虑过使用MessageFormat，但它应该用于用户消息，尽管我认为它可以做一些合理的工作，但我猜java SQL库中应该有一些更符合s...

问在Java中构建SQL字符串的最简洁方法
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Java中构建SQL字符串的最简洁方法EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在Java中构建SQL字符串的最简洁方法
EN