Migrate Sybase T-SQL to Java

The code samples below provide you with more details.

• Sybase T-SQL

  1. create procedure sp_demo_orders
  2.     @CustomerId int,
  3.     @TotalAmount numeric(12,2) output
  4. as
  5. begin
  6.     declare @OrderId int
  7.     declare @OrderAmount numeric(10,2)
  8.     declare @CurrentDate datetime
  9.     declare @Count int
  10.     declare @SumAmount numeric(12,2)
  11.  
  12.     select @CurrentDate = getdate()
  13.     select @TotalAmount = 0
  14.     select @SumAmount = 0
  15.     select @Count = 0
  16.  
  17.     print "Order processing started at " + convert(varchar(30), @CurrentDate)
  18.  
  19.     if @CustomerId is null or @CustomerId <= 0
  20.     begin
  21.         raiserror 50010 "Invalid Customer Id"
  22.         return
  23.     end
  24.  
  25.     -- Cursor for orders of this customer
  26.     declare order_cursor cursor for
  27.         select OrderId, Amount
  28.         from Orders
  29.         where CustomerId = @CustomerId
  30.  
  31.     open order_cursor
  32.     fetch order_cursor into @OrderId, @OrderAmount
  33.  
  34.     while @@FETCH_STATUS = 0
  35.     begin
  36.         select @Count = @Count + 1
  37.         select @SumAmount = @SumAmount + isnull(@OrderAmount, 0)
  38.  
  39.         -- Apply discount logic
  40.         if @OrderAmount > 1000
  41.         begin
  42.             print "Large order " + convert(varchar, @OrderId) +
  43.                   ": applying discount"
  44.             select @OrderAmount = @OrderAmount * 0.9
  45.         end
  46.  
  47.         -- Simulate error on negative amount
  48.         if @OrderAmount < 0
  49.         begin
  50.             raiserror 50011 "Negative order amount detected"
  51.             close order_cursor
  52.             deallocate cursor order_cursor
  53.             return
  54.         end
  55.  
  56.         select @TotalAmount = @TotalAmount + @OrderAmount
  57.  
  58.         fetch order_cursor into @OrderId, @OrderAmount
  59.     end
  60.  
  61.     close order_cursor
  62.     deallocate cursor order_cursor
  63.  
  64.     print "Customer " + convert(varchar, @CustomerId) +
  65.           " has " + convert(varchar, @Count) + " orders"
  66.     print "Total before discounts: " + convert(varchar, @SumAmount)
  67.     print "Final total amount: " + convert(varchar, @TotalAmount)
  68.     print "Average order: " +
  69.           convert(varchar, @TotalAmount / nullif(@Count,0))
  70.  
  71.     print "Order processing finished at " + convert(varchar(30), getdate())
  72. end

  → Java

  1. import com.sql4j.db.cursor.*;
  2. import com.sql4j.exception.ErrorUtil;
  3. import com.sql4j.transaction.aop.TransactionStatus;
  4. import com.sql4j.util.*;
  5. import java.math.BigDecimal;
  6. import java.time.LocalDateTime;
  7. import java.util.*;
  8. import org.springframework.beans.factory.annotation.Autowired;
  9. import org.springframework.stereotype.Service;
  10. import org.springframework.web.context.annotation.RequestScope;
  11. @Service
  12. @RequestScope
  13. public class SpDemoOrders implements ISpDemoOrders {
  14.   @Autowired
  15.   private ErrorUtil errorUtil;
  16.   @Autowired
  17.   private CursorStatusService cursorStatusService;
  18.   @TransactionStatus
  19.   @Override
  20.   public Map< String, Object > spSpDemoOrders(Integer customerid, BigDecimal totalamount) {
  21.     Map<String, Object> outData = new HashMap<>();
  22.     outData.put("retStatus", 0);
  23.  
  24.     try {
  25.       Integer orderid = null;
  26.       BigDecimal orderamount = null;
  27.       LocalDateTime currentdate = null;
  28.       Integer count = null;
  29.       BigDecimal sumamount = null;
  30.       currentdate = TimeUtil.getDate();
  31.       totalamount = new BigDecimal("0");
  32.       sumamount = new BigDecimal("0");
  33.       count = 0;
  34.       System.out.println(StringUtil.concat("Order processing started at ", Converter.toString(currentdate)));
  35.  
  36.       if (customerid == null || ComparisonUtil.isLessOrEqual(customerid, 0)) {
  37.         errorUtil.raisError(50010, "Invalid Customer Id");
  38.         return outData;
  39.       }
  40.  
  41.       CursorReader orderCursor = new CursorReader(cursorStatusService, true, "select OrderId, Amount " +
  42.               "from Orders " +
  43.               "where CustomerId = :CUSTOMERID ", "CUSTOMERID", customerid);
  44.       orderCursor.open();
  45.  
  46.       if (orderCursor.fetchNext()) {
  47.         orderid = orderCursor.getColumnValue(Integer.class);
  48.         orderamount = orderCursor.getColumnValue(BigDecimal.class);
  49.       }
  50.  
  51.       while (ComparisonUtil.areEqual(cursorStatusService.getFetchStatus(), 0)) {
  52.         count = Converter.toInteger(MathUtil.add(count, 1));
  53.         sumamount = MathUtil.add(sumamount, ComparisonUtil.isNull(orderamount, new BigDecimal("0")));
  54.  
  55.         // Apply discount logic
  56.         if (ComparisonUtil.isGreaterThan(orderamount, 1000)) {
  57.           System.out.println(StringUtil.concat("Large order ", Converter.toString(orderid), ": applying discount"));
  58.           orderamount = MathUtil.multiply(orderamount, new BigDecimal("0.9"));
  59.         }
  60.  
  61.         // Simulate error on negative amount
  62.         if (ComparisonUtil.isLessThan(orderamount, 0)) {
  63.           errorUtil.raisError(50011, "Negative order amount detected");
  64.           orderCursor.close();
  65.           orderCursor.deallocate();
  66.           return outData;
  67.         }
  68.  
  69.         totalamount = MathUtil.add(totalamount, orderamount);
  70.  
  71.         if (orderCursor.fetchNext()) {
  72.           orderid = orderCursor.getColumnValue(Integer.class);
  73.           orderamount = orderCursor.getColumnValue(BigDecimal.class);
  74.         }
  75.       }
  76.  
  77.       orderCursor.close();
  78.       orderCursor.deallocate();
  79.       System.out.println(StringUtil.concat("Customer ", Converter.toString(customerid), " has ", Converter.toString(count), " orders"));
  80.       System.out.println(StringUtil.concat("Total before discounts: ", Converter.toString(sumamount)));
  81.       System.out.println(StringUtil.concat("Final total amount: ", Converter.toString(totalamount)));
  82.       System.out.println(StringUtil.concat("Average order: ", Converter.toString(MathUtil.divide(totalamount, ComparisonUtil.nullIf(count, 0)))));
  83.       System.out.println(StringUtil.concat("Order processing finished at ", Converter.toString(TimeUtil.getDate())));
  84.       return outData;
  85.     }
  86.     finally {
  87.       outData.put("totalamount", totalamount);
  88.     }
  89.   }
  90. }