merchantsalepassagedao.java

基于struts的网上商店源码

package com.mole.struts.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

public class MerchantSalePassageDAO {
	private DataSource ds;
	private Connection conn;

	public MerchantSalePassageDAO() {
		try {
			Context ctx = new InitialContext();
			if (ctx == null)
				throw new Exception("Failed to initial context!");
			ds = (DataSource) ctx.lookup("java:comp/env/jdbc/crmdata");
			conn = ds.getConnection();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 批量添加购买物品信息
	public void updateTransaction(String storeID, String userID,
			String[] discount, String[] goods, String[] amounts,
			String[] prices, String[] voucher) throws Exception {
		PreparedStatement ps = null;
		ResultSet rs = null;
		String SwiftID = "";
		String sqlUpdateAmount = "UPDATE [GoodsStorage] SET [Amount]=[Amount]-? WHERE [StoreID]=? AND [GoodsID]=?";
		String sqlUpdateScore = "UPDATE [Score] SET [Score]=[Score]+(SELECT [Scale]*? FROM [Store] WHERE [ID]=?) WHERE [StoreID]=? AND [CustomerID]=?";
		String sqlSwift = "INSERT INTO [SwiftNumber]([StoreId],[CustomerId]) VALUES(?,?)";
		String sqlVoucher = "INSERT INTO [VoucherRecord]([SwiftID],[VoucherID],[Amount]) VALUES(?,?,?)";
		String sqlInsert = "INSERT INTO [BusinessRecord]([SwiftID],[GoodsID],[Amount],[Discount],[Price]) VALUES(?,?,?,?,?)";
		try {
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sqlUpdateAmount);
			for (int i = 0; i < goods.length; i++) {
				ps.setString(1, amounts[i]);
				ps.setString(2, storeID);
				ps.setString(3, goods[i]);
				ps.addBatch();
			}
			ps.executeBatch();
			ps.close();
			ps = conn.prepareStatement(sqlUpdateScore);
			for (int i = 0; i < goods.length; i++) {
				ps.setString(1, prices[i]);
				ps.setString(2, storeID);
				ps.setString(3, storeID);
				ps.setString(4, userID);
				ps.addBatch();
			}
			ps.executeBatch();
			ps.close();
			ps = conn.prepareStatement(sqlSwift);
			ps.setString(1, storeID);
			ps.setString(2, userID);
			ps.execute();
			ps.close();
			ps = conn
					.prepareStatement("SELECT TOP 1 [ID] FROM [SwiftNumber] WHERE [StoreId]='"
							+ storeID
							+ "' AND [CustomerId]="
							+ userID
							+ " ORDER BY [DealTime] DESC");
			rs = ps.executeQuery();
			if (rs.next())
				SwiftID = rs.getString(1);
			ps.close();
			ps = conn.prepareStatement(sqlVoucher);
			for (int i = 0; i < voucher.length; i += 2) {
				if (voucher[i + 1].equals("0"))
					continue;
				ps.setString(1, SwiftID);
				ps.setString(2, voucher[i]);
				ps.setString(3, voucher[i + 1]);
				ps.addBatch();
			}
			ps.executeBatch();
			ps.close();
			ps = conn.prepareStatement(sqlInsert);
			for (int i = 0; i < goods.length; i++) {
				ps.setString(1, SwiftID);
				ps.setString(2, goods[i]);
				ps.setString(3, amounts[i]);
				ps.setString(4, discount[i]);
				ps.setString(5, prices[i]);
				ps.addBatch();
			}
			ps.executeBatch();
			ps.close();
			conn.commit();
		} catch (Exception e) {
			conn.rollback();
			throw e;
		} finally {
			conn.setAutoCommit(true);
			conn.close();
		}
	}
}