java - JDBC 执行大型 T-SQL 查询

Question

我有一个大型 SQL 脚本，用于创建临时表并通过从另一个表中选择数据来插入数据，许多插入，例如

SET @EndDate = DATEADD(dd, -1, @EndDate);

DECLARE @EndDatePlusOneDay SMALLDATETIME
SET @EndDatePlusOneDay = DATEADD(dd, 1, @EndDate);

CREATE TABLE #cntrs
(
    ContractID             DSIDENTIFIER,
    ContractDateFrom       dsdatetime,
    ContractNumber         dsvarfullname40,
    ContractClientID       DSIDENTIFIER,
    ContractClientName     VARCHAR(500),
    CreditDateTo           dsdatetime,
    Amount                 dsmoney,
    LoanDept               dsmoney,
    PledgeRate             dsmoney,
    CollatVal              dsmoney,
    WarrantyType           dsfullname,
    WarrantyNumber         VARCHAR(20),
    WarrantyDate           dsoperday,
    WarrantyQty            dsmoney,
)

INSERT INTO #cntrs
SELECT c.ContractID,
       cc.CreditDateFrom,
       c.Number,
       ti.InstitutionID,
       (
           CASE 
                WHEN ti.PropDealPart = 0 THEN ti.Name + ' ' + ti.Name1 + ' ' +
                     ti.Name2 + ' '
                ELSE ti.Name
           END
       )              AS ContractClientName,
       cc.CreditDateTo,
       c.Amount,
       0              AS LoanDept,
       70             AS PledgeRate,
       0              AS CollatVal,
       '' AS             WarrantyType,
       '' AS             WarrantyNumber,
       '19000101' AS     WarrantyDate,
       0              AS WarrantyQty
FROM   dataTable1 c(NOLOCK)
       INNER JOIN dataTable2 cc(NOLOCK)
            ON  c.ContractID = cc.ContractCreditID
       INNER JOIN dataTable3 o(NOLOCK)
            ON  c.ContractID = o.ID
            AND o.ObjectTypeID = 105
       INNER JOIN dataTable4 p(NOLOCK)
            ON  o.CurrProtocolID = p.ProtocolID
       INNER JOIN dataTable5 t(NOLOCK)
            ON  p.TransitionID = t.TransitionID
       INNER JOIN dataTable6 n(NOLOCK)
            ON  t.TargetStateID = n.NodeID
       INNER JOIN dataTable7 ti WITH(NOLOCK)
            ON  ti.InstitutionID = c.InstitutionID
WHERE  1 = 1
       --  AND @BranchID IN (0, ISNULL(NULLIF(c.BranchExtId, 0), c.BranchID))
       AND n.Brief IN ('Предоставл', 'НеОплВовр', 'Завершен')
       AND cc.CreditDateFrom BETWEEN @StartDate AND @endDate
ORDER BY
       cc.CreditDateFrom


IF OBJECT_ID('tempdb..#AccInner') IS NOT NULL
    DROP TABLE #AccInner

CREATE TABLE #AccInner
(
    ContractID     NUMERIC(15, 0),
    ResourceID     NUMERIC(15, 0)
)

CREATE UNIQUE INDEX x1 ON #AccInner(ContractID)


DECLARE @DepParentID        DSIDENTIFIER,   -- Субконто КатегорияСредств
        @DepRepaymentID     DSIDENTIFIER,   -- Субконто ТипОперВУ - Упл/Погаш
        @DepAccrualID       DSIDENTIFIER   -- Субконто ТипОперВУ - Выд/Нач

SELECT @DepParentID = d.DepartmentID
FROM   tDepartment d(NOLOCK INDEX = XAK3tDepartment)
WHERE  d.Brief = 'КатСрдств'


UPDATE c
SET    c.CollatVal = c.LoanDept * (c.PledgeRate / 100)
FROM   #cntrs c 

SELECT *
FROM   #cntrs
ORDER BY
       ContractDateFrom

我需要在一批查询中执行此 T-SQL 代码。我计划从文件中读取 T-SQL 代码到 String 并立即执行这个 String。

如何使用 MS SQL Server 的 JDBC 来完成此操作？

Answer 1

您始终可以将此 T-SQL 代码保存为 MSSQL 存储过程并使用 JDBC 执行它 CallableStatement .