java 执行sqlServer脚本

于 2021-04-13 14:47:45 发布
阅读量233 收藏 1
点赞数
文章标签： java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/zhouyun_121416/article/details/115667223
版权
  1. sqlServer的脚本 
    SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[Proc_HSPR_BalanceCutOffPointAdvanceSearch_InsInit]
(
@CommID int,
@UserCode nvarchar(20),	
@CutEndDate datetime	
)
AS
BEGIN	
	
	set @UserCode = isnull(@UserCode,'')
		
	DELETE FROM Tb_HSPR_BalanceCutOffPointAdvanceSearch
	WHERE
		CommID = @CommID
		and UserCode = @UserCode
	
	INSERT INTO Tb_HSPR_BalanceCutOffPointAdvanceSearch
	(CommID,UserCode,CutEndDate
	,PrecID,CustID,RoomID,PrecAmount,SourceType)
	select CommID,@UserCode,@CutEndDate
	,PrecID,0,0,0,isnull(SourceType,0)
	from Tb_HSPR_PreCostsDetail
	where CommID = @CommID
	and (isnull(SourceType,0) = 0 or SourceType = 1)
	and isnull(IsDelete,0) = 0
	group by CommID,PrecID,isnull(SourceType,0)
	order by CommID,PrecID

	
	--删除多余的
	delete from Tb_HSPR_BalanceCutOffPointAdvanceSearch
	from Tb_HSPR_BalanceCutOffPointAdvanceSearch as a
	inner join 
		(select CommID,PrecID from Tb_HSPR_BalanceCutOffPointAdvanceSearch
		where CommID = @CommID and UserCode = @UserCode
		group by CommID,PrecID
		having Count(*) > 1) as b
	on a.CommID = b.CommID and a.PrecID = b.PrecID 
	where a.CommID = @CommID and a.UserCode = @UserCode and a.SourceType = 0

END
    
SET QUOTED_IDENTIFIER OFF
GO

CREATE Proc [dbo].[Proc_HSPR_PreAdvanceBalanceSearch_PrecAmountUpdate1]
(	
	@CommID int,
	@UserCode nvarchar(20),	
	@CutEndDate datetime
)
as
BEGIN

	--预交余额		
	set @UserCode = isnull(@UserCode,'')

	--具有统计项
	update Tb_HSPR_BalanceCutOffPointAdvanceSearch
	set RoomID = b.RoomID
	,CustID = b.CustID
	,CostID = dbo.funGetPreCostsCanCost(b.PrecID)
	,PrecMemo = b.PrecMemo
	from Tb_HSPR_BalanceCutOffPointAdvanceSearch as a
	inner join Tb_HSPR_PreCosts as b
	on a.PrecID = b.PrecID
	where a.CommID = @CommID and a.UserCode = @UserCode


	--车位
	update Tb_HSPR_BalanceCutOffPointAdvanceSearch 
	set HandID = tt.HandID
	from Tb_HSPR_BalanceCutOffPointAdvanceSearch as a
	inner join (select CommID,PrecID			
			,Max(isnull(HandID,0)) as HandID
			from Tb_HSPR_PreCostsDetail
			where CommID = @CommID			
			and IsDelete = 0 
			group by CommID,PrecID) as tt
	on a.CommID = tt.CommID and a.PrecID = tt.PrecID
	where a.CommID = @CommID and a.UserCode = @UserCode

END

GO

     

  2. java代码 
    package com;


import java.sql.*;
import java.util.HashSet;
import java.util.Set;

/**
   工具类
 */
public class JdbcProHsprRepository4 {

    public static void jdbcConnect(JdbcConstants jdbcConstants,Integer CommID,String UserCode,Date date2) {

        Connection conn = null;

        Statement stmt = null;

        ResultSet rs = null;

        CallableStatement cs = null;//调用存储过程使用的接口

        Set<Integer> commidSet = new HashSet<Integer>();

        try {

            // 加载驱动类
            Class.forName(jdbcConstants.getDriver());

            //连接数据库通道
            conn = DriverManager.getConnection(jdbcConstants.getUrl(), jdbcConstants.getUsername(), jdbcConstants.getPassword());
            //设为手动提交
            conn.setAutoCommit(false);
            //建立连接
            stmt = conn.createStatement();

            // 调用的结果集
            System.out.println("conn"+conn);
            System.out.println(jdbcConstants.getUrl()+"" +jdbcConstants.getUsername()+""+jdbcConstants.getPassword());

            //执行第3个文件
            getPHB(conn,CommID,UserCode,date2);
            //执行第4个文件
            getPHBP1(conn,CommID,UserCode,date2);
//            //执行第5个文件
            getPHBP2(conn,CommID,UserCode,date2);
//            //执行第6个文件
            getPHBP3(conn,CommID,UserCode,date2);
//            //执行第7个文件
            getPHBP4(conn,CommID,UserCode,date2);
//            //执行第8个文件
            getPHBP5(conn,CommID,UserCode,date2);
            //执行第9个文件
            getPHBP6(conn,UserCode);

            // 提交事务
            conn.commit();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            try {
                throw new Exception("数据库异常：" + e.getMessage());
            } catch (Exception exception) {
                exception.printStackTrace();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                throw new Exception("数据库异常：" + e.getMessage());
            } catch (Exception exception) {
                exception.printStackTrace();
            }

        } finally {
            // 遵循：resultset-->statment-->connection这样的关闭顺序！一定要将三个trycatch块，分开写！
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

    }


    //执行第9个文件
    private static void getPHBP6(Connection conn, String userCode) {
        try {
            System.out.println(conn+"  "+userCode);
            CallableStatement cs = conn.prepareCall("{call Proc_HSPR_Report_PreCostBalanceCutOffPointAdvance(?)}");
            System.out.println(cs);
            cs.setString(1,userCode);
            System.out.println(9+"getPHBP6更新成功");
            cs.execute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    private static void getPHBP5(Connection conn, Integer commID, String userCode, Date date2) {
        try {
            CallableStatement cs = conn.prepareCall("{call Proc_HSPR_PreAdvanceBalanceSearch_PrecAmountUpdate5(?,?,?)}");
            cs.setInt(1, commID);
            cs.setString(2,userCode);
            cs.setDate(3,date2);
            System.out.println(8+"getPHBP5更新成功");
            cs.execute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    private static void getPHBP4(Connection conn, Integer commID, String userCode, Date date2) {
        try {
            CallableStatement cs = conn.prepareCall("{call Proc_HSPR_PreAdvanceBalanceSearch_PrecAmountUpdate4(?,?,?)}");
            cs.setInt(1, commID);
            cs.setString(2,userCode);
            cs.setDate(3,date2);
            System.out.println(7+"getPHBP4更新成功");
            cs.execute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    private static void getPHBP3(Connection conn, Integer commID, String userCode, Date date2) {
        try {
            CallableStatement cs = conn.prepareCall("{call Proc_HSPR_PreAdvanceBalanceSearch_PrecAmountUpdate3(?,?,?)}");
            cs.setInt(1, commID);
            cs.setString(2,userCode);
            cs.setDate(3,date2);
            System.out.println(6+"getPHBP3更新成功");
            cs.execute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    private static void getPHBP2(Connection conn, Integer commID, String userCode, Date date2) {
        try {
            CallableStatement cs = conn.prepareCall("{call Proc_HSPR_PreAdvanceBalanceSearch_PrecAmountUpdate2(?,?,?)}");
            cs.setInt(1, commID);
            cs.setString(2,userCode);
            cs.setDate(3,date2);
            System.out.println(5+"getPHBP2更新成功");
            cs.execute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    private static void getPHBP1(Connection conn, Integer commID, String userCode, Date date) {
        try {
            CallableStatement cs = conn.prepareCall("{call Proc_HSPR_PreAdvanceBalanceSearch_PrecAmountUpdate1(?,?,?)}");
            cs.setInt(1, commID);
            cs.setString(2,userCode);
            cs.setDate(3,date);
            System.out.println(4+"getPHBP1更新成功");
            cs.execute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    private static void getPHB(Connection conn, Integer commID, String userCode, Date date2) {
        try {
            CallableStatement cs = conn.prepareCall("{call Proc_HSPR_BalanceCutOffPointAdvanceSearch_InsInit(?,?,?)}");
            cs.setInt(1, commID);
            cs.setString(2,userCode);
            cs.setDate(3,date2);
            System.out.println(3+"getPHB添加成功");
            cs.execute();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }
}

     

  3. 测试类 
    import java.sql.Date;

public class Text {

    public static void main(String[] args) {
        JdbcConstants jdbcConnects=new JdbcConstants();
        jdbcConnects.setDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");//com.microsoft.sqlserver.jdbc.SQLServerDriver
        jdbcConnects.setUrl("");//数据库路径
        jdbcConnects.setUsername("");//名称
        jdbcConnects.setPassword("");//密码

        Integer CommID=200101;
        String UserCode="44444";
        Date date2=new Date(System.currentTimeMillis());
       JdbcProHsprRepository4.jdbcConnect(jdbcConnects,CommID,UserCode,date2);
//       JdbcProHsprRepository5.jdbcConnect(jdbcConnects,CommID,UserCode,date2);


    }
}

     

  4. 执行结果： 
    3getPHB添加成功
4getPHBP1更新成功
5getPHBP2更新成功
6getPHBP3更新成功
7getPHBP4更新成功
8getPHBP5更新成功

进程已结束，退出代码为 0

     

zhouyun_121416
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值