sql - SQL 不是由 JDBC 运行，但在数据库客户端中可以完美运行

Question

我有一个查询要从 SQL Server 2012 中选择树结构数据以从一个表中获取树结构结果集。

这是我使用的 SQL：

with cte_child (id, parentId, contractId, contractName, jfCompany, jfDepartment, yfDepartment, totalAmount, signDate, lvl)
as 
(
    select 
        id, parentId, contractId, contractName, 
        jfCompany, jfDepartment, yfDepartment, totalAmount, signDate, 0
    from 
        Contract_info_main
    where 
        contractId = 'c2012-0412'

    union all

    select 
        c.id, c.parentId, c.contractId, c.contractName, 
        c.jfCompany, c.jfDepartment, c.yfDepartment, c.totalAmount, 
        c.signDate, s.lvl+1
    from 
        Contract_info_main c
    inner join 
        cte_child s on s.parentId = c.id
)
select * from cte_child

此 SQL 语句在本地 SQL Server 2012 数据库以及我的带有 DBeaver 客户端的本地计算机上运行时结果正确。DBeaver 使用的驱动程序是“mssql-jdbc:7.4.1.jre8”。

奇怪的是，当我使用纯 Java 代码发送相同的 SQL 字符串来发送完全相同的查询时，它失败并出现错误

对象名称“Contract_inf_main”无效

由于我使用的是 Ubuntu 18，我想 DLL 和驱动程序应该不是问题？但这个问题的可能原因是什么？谢谢

这是我的 JDBC 代码：

public static void main(String[] args) throws ClassNotFoundException, SQLException {
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

    final Connection connection = DriverManager.getConnection("jdbc:sqlserver://192.168.5.158:1433", "general_reader", "sqlserver2017@reader");

    final String sql = "with cte_child(id, parentId, contractId, contractName, jfCompany, jfDepartment, yfDepartment, totalAmount, signDate, lvl) " +
                               "as (select id, parentId, contractId, contractName, jfCompany, jfDepartment, yfDepartment, totalAmount, signDate, 0 " +
                               "from Contract_info_main where contractId = 'jy2012-041' " +
                               "union all select c.id, c.parentId, c.contractId, c.contractName, c.jfCompany, c.jfDepartment, c.yfDepartment, c.totalAmount, c.signDate, s.lvl+1 " +
                               "from Contract_info_main c inner join cte_child s on s.parentId = c.id) select * from cte_child";

    final PreparedStatement statement = connection.prepareStatement(sql);

    System.out.println(sql);

    final ResultSet resultSet = statement.executeQuery();
    while(resultSet.next()) {
        System.out.println("id = " + resultSet.getString(1) + " and parentId = " + resultSet.getString(2));
    }

    resultSet.close();
    statement.close();
    connection.close();
}

标签： sqlsql-serverjdbc