代码之家 › 专栏 › 技术社区 › Gennadiy

Java类内部的SQL代码

integration sql java

Gennadiy · 技术社区 · 17 年前

我们当前的项目不使用Hibernate(出于各种原因),我们使用Spring的SimpleJDBC支持来执行所有的DB操作。我们有一个实用程序类,它抽象了所有CRUD操作,但复杂的操作是使用自定义SQL查询执行的。

目前,我们的查询存储在服务类本身的字符串常量中,并被送入一个实用程序,由SimpleJDBCTemplate执行。我们正处于一个僵局中,可读性必须与可维护性相平衡。类本身内部的SQL代码更易于维护,因为它与使用它的代码共存。另一方面,如果我们将这些查询存储在一个外部文件(平面或XML)中,SQL本身将比转义Java字符串语法更可读。

有人遇到过类似的问题吗?什么是良好的平衡?在项目中,您将自定义SQL保存在哪里?

示例查询如下:

private static final String FIND_ALL_BY_CHEAPEST_AND_PRODUCT_IDS = 
"    FROM PRODUCT_SKU T \n" +
"    JOIN \n" +
"    ( \n" +
"        SELECT S.PRODUCT_ID, \n" +
"               MIN(S.ID) as minimum_id_for_price \n" +
"          FROM PRODUCT_SKU S \n" +
"         WHERE S.PRODUCT_ID IN (:productIds) \n" +
"      GROUP BY S.PRODUCT_ID, S.SALE_PRICE \n" +
"    ) FI ON (FI.PRODUCT_ID = T.PRODUCT_ID AND FI.minimum_id_for_price = T.ID) \n" +
"    JOIN \n" +
"    ( \n" +
"        SELECT S.PRODUCT_ID, \n" +
"               MIN(S.SALE_PRICE) as minimum_price_for_product \n" +
"          FROM PRODUCT_SKU S \n" +
"         WHERE S.PRODUCT_ID IN (:productIds) \n" +
"      GROUP BY S.PRODUCT_ID \n" +
"    ) FP ON (FP.PRODUCT_ID = T.PRODUCT_ID AND FP.minimum_price_for_product = T.sale_price) \n" +
"WHERE T.PRODUCT_ID IN (:productIds)";

这是在平面SQL文件中的外观:

--namedQuery: FIND_ALL_BY_CHEAPEST_AND_PRODUCT_IDS
FROM PRODUCT_SKU T 
JOIN 
( 
    SELECT S.PRODUCT_ID, 
           MIN(S.ID) as minimum_id_for_price 
      FROM PRODUCT_SKU S 
     WHERE S.PRODUCT_ID IN (:productIds) 
  GROUP BY S.PRODUCT_ID, S.SALE_PRICE 
) FI ON (FI.PRODUCT_ID = T.PRODUCT_ID AND FI.minimum_id_for_price = T.ID) 
JOIN 
( 
    SELECT S.PRODUCT_ID, 
           MIN(S.SALE_PRICE) as minimum_price_for_product 
      FROM PRODUCT_SKU S 
     WHERE S.PRODUCT_ID IN (:productIds) 
  GROUP BY S.PRODUCT_ID 
) FP ON (FP.PRODUCT_ID = T.PRODUCT_ID AND FP.minimum_price_for_product = T.sale_price) 
WHERE T.PRODUCT_ID IN (:productIds)

16 回复 | 直到 10 年前

Bill the Lizard 17 年前

我将SQL存储为Java类中的两个字符串,以及在运行时加载的单独文件。我非常喜欢后者有两个原因。首先,代码的可读性更高。第二,如果将SQL存储在单独的文件中,那么隔离地测试它就更容易了。除此之外,当我的查询位于不同的文件中时,在SQL中找一个比我更好的人来帮助我处理查询更容易。

Steve B. 17 年前

我也遇到过这个问题,目前也是出于同样的原因——一个基于SpringJDBC的项目。我的经验是,虽然在SQL本身中使用逻辑并不好,但实际上没有比使用DB更合适的地方了,而且插入应用程序代码比使用DB要慢,而且不一定更清楚。

我所看到的最大的缺陷是,在这个项目中,SQL开始以多种变体在整个项目中激增。”从foo获取a、b、c”。从foo中获取a、b、c、e等。当项目达到某个临界质量时,这种扩散尤其可能——它可能看起来不像10个查询的问题,但当项目中分散了500个查询时,就很难确定是否已经做了些什么。抽象出基本的CRUD操作可以让您在游戏中遥遥领先。

最好的解决方案,afaik,是严格地与编码的SQL一致——注释、测试,并且在一致的地方。我们的项目有50行未注释的SQL查询。它们是什么意思?谁知道?

至于外部文件中的查询,我看不出这能带来什么好处——您仍然像依赖SQL一样依赖于SQL,除了将SQL从类中排除在外的(有问题的)美学改进之外,您的类仍然像依赖SQL一样依赖于SQL——例如,您通常会分离资源以获得插件替换资源的灵活性。但是你不能插入替换的SQL查询,因为这会改变类的语义,或者根本不起作用。所以这是一个虚幻的代码清洁。

Dónal 17 年前

一个相当激进的解决方案是使用groovy来指定查询。groovy支持多行字符串和字符串插值(有趣的是,称为gstrings)。

例如,使用groovy,您在上面指定的查询只是:

class Queries
    private static final String PRODUCT_IDS_PARAM = ":productIds"

    public static final String FIND_ALL_BY_CHEAPEST_AND_PRODUCT_IDS = 
    """    FROM PRODUCT_SKU T 
        JOIN 
        ( 
            SELECT S.PRODUCT_ID, 
                   MIN(S.ID) as minimum_id_for_price 
              FROM PRODUCT_SKU S 
             WHERE S.PRODUCT_ID IN ($PRODUCT_IDS_PARAM) 
          GROUP BY S.PRODUCT_ID, S.SALE_PRICE 
        ) FI ON (FI.PRODUCT_ID = T.PRODUCT_ID AND FI.minimum_id_for_price = T.ID) 
        JOIN 
        ( 
            SELECT S.PRODUCT_ID, 
                   MIN(S.SALE_PRICE) as minimum_price_for_product 
              FROM PRODUCT_SKU S 
             WHERE S.PRODUCT_ID IN ($PRODUCT_IDS_PARAM) 
          GROUP BY S.PRODUCT_ID 
        ) FP ON (FP.PRODUCT_ID = T.PRODUCT_ID AND FP.minimum_price_for_product = T.sale_price) 
    WHERE T.PRODUCT_ID IN ($PRODUCT_IDS_PARAM) """

你可以从Java代码中访问这个类,就像你在Java中定义的一样。

String query = QueryFactory.FIND_ALL_BY_CHEAPEST_AND_PRODUCT_IDS;

我承认,将groovy添加到类路径中只是为了使SQL查询看起来更好,这有点像是一个“攻克难题”的解决方案,但是如果您使用的是Spring,那么很有可能您已经在类路径上实现了groovy。

此外,在您的项目中可能有很多其他地方可以使用Groovy(而不是Java)来改进代码(特别是现在Groovy由Spring拥有)。例子包括编写测试用例,或者用Groovy bean替换Java bean。

Mike 17 年前

我们使用存储过程。这对我们有好处,因为我们使用Oracle细粒度访问。这允许我们通过限制用户对相关过程的访问来限制用户查看特定报告或搜索结果。这也给了我们一点性能提升。

Scott Lance 17 年前

为什么不使用存储过程而不是硬编码查询?存储过程将提高可维护性,并为SQL插入攻击等攻击提供更高的安全性。

Tom Clarkson 17 年前

在类中可能是最好的——如果查询足够长,以至于转义是一个问题,那么您可能希望查看存储过程或简化查询。

Dónal 17 年前

一种选择是使用 iBatis . 与成熟的ORM类Hibernate相比,它相当轻量级,但提供了一种在Java文件之外存储SQL查询的方法。

neesh 17 年前

我们将所有SQL存储在一个类中,作为一组静态最终字符串。为了可读性,我们将它分布在使用+连接的几行上。另外,我不确定您是否需要转义任何东西——“字符串”在SQL中用单引号括起来。

Edward Q. Bridges 17 年前

我们有一个项目,在那里我们使用了您的确切方法,除了我们将每个查询外部化为一个单独的文本文件。使用Spring的ResourceLoader框架读取每个文件(一次),应用程序通过如下接口工作:

public interface SqlResourceLoader {
    String loadSql(String resourcePath);
}

这样做的一个明显的优点是,允许使用未转义的SQL格式进行更容易的调试——只需将文件读取到查询工具中即可。一旦您有了几个中等复杂度的查询,在测试和调试(尤其是调优)时处理代码的非/转义,这是非常宝贵的。

我们还必须支持几个不同的数据库,这样就可以更容易地交换平台。

Robin 17 年前

根据您所解释的问题,这里没有真正的选择,除了将其保存在代码中,并消除 /n 到处都是人物。这是您提到的唯一影响可读性的事情,它们是绝对不必要的。

除非代码中存在其他问题,否则问题很容易解决。

Tom 17 年前

我可以想象,将查询存储在一个外部文件中,然后让应用程序在需要时读取它,这会造成一个巨大的安全漏洞。

如果一个邪恶的主脑可以访问该文件并更改您的查询,会发生什么?

例如更改

 select a from A_TABLE;

到

 drop table A_TABLE;

或

 update T_ACCOUNT set amount = 1000000

此外,它还增加了必须掌握两件事情的复杂性:Java代码和SQL查询文件。

编辑:是的,您可以更改您的查询,而无需重新编译您的应用程序。我看不出有什么大不了的。如果项目太大,您可以重新编译只保存/创建sqlqueries的类。此外,如果文档不好,可能最终会更改不正确的文件,这将变成一个巨大的静默的bug。不会抛出任何异常或错误代码,当您意识到您所做的事情时,可能为时已晚。

另一种方法是使用某种有良好文档记录的sqlqueryfactory,并使用返回要使用的SQL查询的方法。

例如

public String findCheapest (String tableName){

      //returns query.
}

Pablo Santa Cruz 17 年前

你需要的是 SQLJ 这是一个SQL Java预处理器。可悲的是,虽然我见过一些 IBM 和 Oracle 实施。但它们已经过时了。

如果我是你,在系统上有很多的查询,我会将它们存储在一个单独的文件中,并加载到 运行时 .

Tal 17 年前

根据我的经验,最好将SQL语句留在代码中,而不是将它们分开,这样可以使事情更易于维护(如注释和配置文件),但现在我与团队成员就这一点进行了讨论。

OscarRyz 17 年前

我有一个小程序,用Java字符串文字访问剪贴板和逃逸/ unSCAPE纯文本。

我把它作为“快速启动”工具栏上的快捷方式,所以我唯一需要做的就是

Ctrl+C, Click jar, Ctrl+V

当我想将“代码”运行到SQL工具中时,或者相反。

所以我通常有这样的东西:

String query = 
    "SELECT a.fieldOne, b.fieldTwo \n"+
    "FROM  TABLE_A a, TABLE b \n"+ 
    "... etc. etc. etc";


logger.info("Executing  " + query  );

PreparedStatement pstmt = connection.prepareStatement( query );
....etc.

转化为:

    SELECT a.fieldOne, b.fieldTwo 
    FROM  TABLE_A a, TABLE b
    ... etc. etc. etc

要么是因为在某些项目中我无法创建单独的文件,要么是因为我偏执,我觉得从外部文件读取时会插入/删除一些位(通常是一个不可见的

select a,b,c 
from

进入之内

select a,b,cfrom

intellij的思想自动地为您做同样的事情,但只是从简单到代码。

这是我恢复的旧版本。它有点坏,不能处理吗?.

如果有人改进了请告诉我。

import java.awt.Toolkit;
import java.awt.datatransfer.Clipboard;
import java.awt.datatransfer.DataFlavor;
import java.awt.datatransfer.ClipboardOwner;
import java.awt.datatransfer.Transferable;
import java.awt.datatransfer.StringSelection;
import java.awt.datatransfer.UnsupportedFlavorException;
import java.io.IOException;

/**
 * Transforms a plain string from the clipboard into a Java 
 * String literal and viceversa.
 * @author <a href="http://stackoverflow.com/users/20654/oscar-reyes">Oscar Reyes</a>
 */
public class ClipUtil{

    public static void main( String [] args ) 
                                throws UnsupportedFlavorException,
                                                     IOException {

        // Get clipboard
        Toolkit toolkit = Toolkit.getDefaultToolkit();
        Clipboard clipboard = toolkit.getSystemClipboard();

        // get current content.
        Transferable transferable = clipboard.getContents( new Object() ); 
        String s = ( String ) transferable.getTransferData( 
                                                DataFlavor.stringFlavor );

        // process the content
        String result = process( s );

        // set the result
        StringSelection ss = new StringSelection( result );
        clipboard.setContents( ss, ss );

    }
    /**
     * Transforms the given string into a Java string literal 
     * if it represents plain text and viceversa. 
     */
    private static String process( String  s ){
        if( s.matches( "(?s)^\\s*\\\".*\\\"\\s*;$" ) ) {
            return    s.replaceAll("\\\\n\\\"\\s*[+]\n\\s*\\\"","\n")
                       .replaceAll("^\\s*\\\"","")
                       .replaceAll("\\\"\\s*;$","");
        }else{
            return     s.replaceAll("\n","\\\\n\\\" +\n \\\" ")
                        .replaceAll("^"," \\\"")
                        .replaceAll("$"," \\\";");
        }
    }
}

Tom 17 年前

既然您已经使用了Spring,为什么不将SQL放到Spring配置文件中并将其DI到DAO类中呢?这是外部化SQL字符串的简单方法。

高温高压汤姆

Aodhan Hoffman 16 年前

我更喜欢外部的选择。我支持多个项目,并且发现更难支持内部SQL,因为每次您想要对SQL做一个细微的更改时,都必须编译和重新部署。将SQL放在外部文件中可以方便地进行大的和小的更改,并且风险较小。如果您只是在编辑SQL,那么就不可能输入会破坏类的拼写错误。

对于Java,我使用属性类来表示.Actudio文件中的SQL,如果您想重复使用查询,而不是多次读取该文件,则允许您绕过SQL查询。