merchantcarddao.java

基于struts的网上商店源码

package com.mole.struts.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

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

import com.mole.struts.bean.MerchantCardViewBean;
import com.mole.struts.bean.MerchantCityInfoBean;

public class MerchantCardDAO {
	private Connection conn;
	private int pageSize;

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

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

	public void ExecuteQuery(String id, String state) {
		if (conn == null)
			conn = getConn();
		Statement ps = null;
		String sql = "update  card set state=" + state + " where id='" + id
				+ "'";
		try {
			ps = conn.createStatement();
			ps.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void deleteCard(String id) {
		if (conn == null)
			conn = getConn();
		Statement ps = null;
		String sql = "delete from card where id='" + id + "'";
		try {
			ps = conn.createStatement();
			ps.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	// 获取目前卡的最大号码
	public String[] getTopStoreId(String StoreId) throws Exception {
		PreparedStatement ps = null;
		String startId[] = new String[2];
		if (conn == null)
			conn = getConn();
		ResultSet rs = null;// (select right((select top 1 [ID] from card where
							// id like '002504001001%' order by id desc),4))
		String sql = "select top 1 [id],(select [LOGINNAME] from merchant where storeid='"
				+ StoreId
				+ "') as loginname from card where id like '"
				+ StoreId + "%' order by id desc";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				startId[0] = rs.getString(1);
				startId[1] = rs.getString(2);
			}
			return startId;
		} finally {
			if (ps != null)
				ps.close();
		}
	}

	// 通过调用数据库的存储过程，根据卡的数目，向数据库中加入相应的商家的卡。
	public String executeStoredProcedure(String spName, Object... args)
			throws Exception {
		ResultSet rs = null;
		Object[] ret = null;
		String endId = null;
		CallableStatement stmt = null;
		ArrayList<Object[]> al = new ArrayList<Object[]>();
		try {
			conn.setAutoCommit(true);
			stmt = conn.prepareCall("{call " + spName + "}");
			for (int i = 1; i <= args.length; i++)
				stmt.setObject(i, args[i - 1]);
			rs = stmt.executeQuery();
			while (rs.next()) {
				endId = rs.getString(1);
			}
			return endId;
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public int getPageInfo(int pageSize) {
		int count = 0;
		this.pageSize = pageSize;
		if (conn == null)
			conn = getConn();
		String sql = "SELECT COUNT(*) FROM CARD ";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if (rs.next())
				count = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;
	}

	public int getPageInfoByWhere(String w) {
		int count = 0;
		if (conn == null)
			conn = getConn();
		String sql = "select count(*) from v_customerCard " + w;
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if (rs.next())
				count = rs.getInt(1);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return count;

	}

	public ArrayList queryCardView(int currentPage, int pageSize)
			throws Exception {
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = "SELECT TOP "
				+ pageSize
				+ " a.[ID],a.[CustomerID],a.[OriginID],a.[State],CONVERT(char(20),a.[GrantDate],120) "
				+ "FROM [CARD] a WHERE a.[ID] NOT IN (SELECT TOP "
				+ (currentPage - 1) * pageSize + " a.[ID] FROM " + "[Card] a )";
		ArrayList mcvb = null;

		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			mcvb = new ArrayList();
			int i = 0;
			while (rs.next()) {
				MerchantCardViewBean MerchantCardView = new MerchantCardViewBean();
				MerchantCardView.setId(rs.getString(1));
				MerchantCardView.setCustomerId(rs.getString(2));
				MerchantCardView.setOriginId(rs.getString(3));
				MerchantCardView.setState(rs.getString(4));
				MerchantCardView.setGrantData(rs.getString(5));
				mcvb.add(MerchantCardView);
			}
		} finally {
			if (ps != null)
				ps.close();
		}
		return mcvb;
	}

	public ArrayList getArea() throws Exception {
		if (conn == null)
			conn = getConn();
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = "select distinct cid,c from v_storeaddress order by c";
		ArrayList mcvb = null;

		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			mcvb = new ArrayList();

			while (rs.next()) {
				MerchantCityInfoBean cityBean = new MerchantCityInfoBean();
				cityBean.setId(String.valueOf(rs.getString(1)));
				cityBean.setName(rs.getString(2));
				mcvb.add(cityBean);
			}
		} finally {
			if (ps != null)
				ps.close();
		}
		return mcvb;
	}

	public ArrayList getCardById(String where) throws Exception {
		if (conn == null)
			conn = getConn();
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = "SELECT  a.[ID],a.[CustomerID],a.[OriginID],a.[State],CONVERT(char(20),a.[GrantDate],120) "
				+ "FROM [CARD] a " + where;
		ArrayList mcvb = null;

		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			mcvb = new ArrayList();

			while (rs.next()) {
				MerchantCardViewBean MerchantCardView = new MerchantCardViewBean();
				MerchantCardView.setId(rs.getString(1));
				MerchantCardView.setCustomerId(rs.getString(2));
				MerchantCardView.setOriginId(rs.getString(3));
				MerchantCardView.setState(rs.getString(4));
				MerchantCardView.setGrantData(rs.getString(5));
				mcvb.add(MerchantCardView);
			}
		} finally {
			if (ps != null)
				ps.close();
		}
		return mcvb;
	}

	public ArrayList getCards(int pageSize, int currentPage, String where)
			throws Exception {
		if (conn == null)
			conn = getConn();
		ResultSet rs = null;
		PreparedStatement ps = null;
		String sql = "SELECT  a.[cardID],a.[ID],a.[OriginID],a.[CardState],CONVERT(char(20),a.[GrantDate],120) "
				+ "FROM [v_customerCard] a "
				+ where
				+ " and a.[id] not in (select top "
				+ (currentPage - 1)
				* pageSize + " a.[id] from v_customercard " + where + ")";
		ArrayList mcvb = null;
		try {
			conn.setAutoCommit(true);
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			mcvb = new ArrayList();

			while (rs.next()) {
				MerchantCardViewBean MerchantCardView = new MerchantCardViewBean();
				MerchantCardView.setId(rs.getString(1));
				MerchantCardView.setCustomerId(rs.getString(2));
				MerchantCardView.setOriginId(rs.getString(3));
				MerchantCardView.setState(rs.getString(4));
				MerchantCardView.setGrantData(rs.getString(5));
				mcvb.add(MerchantCardView);
			}
		} finally {
			if (ps != null)
				ps.close();
		}
		return mcvb;
	}

}