- 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
- 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(); } } }
- 测试类
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); } }
- 执行结果:
3getPHB添加成功 4getPHBP1更新成功 5getPHBP2更新成功 6getPHBP3更新成功 7getPHBP4更新成功 8getPHBP5更新成功 进程已结束,退出代码为 0
java 执行sqlServer脚本
最新推荐文章于 2023-11-24 16:01:25 发布