cardjdbcdao.java

本套系统采用了业界当前最为流行的beanAction组件

package teleReport.persistence.jdbcdao;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.ibatis.dao.client.DaoManager;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import commons.persistence.BaseJdbcDAO;

import teleReport.domain.CardInfo;
import teleReport.domain.City;
import teleReport.domain.OrderInfo;
import teleReport.domain.Product;
import teleReport.persistence.iface.CardDAO;

public class CardJdbcDAO extends BaseJdbcDAO implements CardDAO {

	public CardJdbcDAO(DaoManager daoManager) {
		super(daoManager);
	}

	// base table query```
	public List<City> getCity() {
		String sql = "select cityId, cityName from city";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<City> resultList = new ArrayList<City>();

		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			while (rs.next()) {
				City findCity = new City();
				findCity.setCityId(rs.getString("cityId"));
				findCity.setCityName(rs.getString("cityName"));
				resultList.add(findCity);
			}
		} catch (SQLException e) {
			dealException(e);
		} finally {
			close(rs, pstm);
		}

		return resultList;
	}

	public List<Product> getProd() {
		String sql = "select prodId,prodName from product where isused = 1 and isCard = 1";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		List<Product> resultList = new ArrayList<Product>();

		try {
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			while (rs.next()) {
				Product findProduct = new Product();
				findProduct.setProdId(rs.getString("prodId"));
				findProduct.setProdName(rs.getString("prodName"));
				resultList.add(findProduct);
			}
		} catch (SQLException e) {
			dealException(e);
		} finally {
			close(rs, pstm);
		}

		return resultList;
	}

	// 插入卡销售信息
	public Long insertCard(CardInfo cardInfo) {
		String seqSql = "select seq_cSaleId.nextval as  cardSaleId from dual";
		String sql = "insert into cardSale(cSaleId, proId, cityId, singleV, proNumber, discordV, saleDate, state, checkerId,checkTime)"
				+ "values(?,?,?,?,?,?,?,?,?,?)";

		PreparedStatement pstm = null;
		ResultSet rs = null;
		Long cardSaleId = null; // 第一条语句的查询结果——卡销售编号

		int insertNum = 0; // 测试操作是否成功

		try {
			pstm = getConnection().prepareStatement(seqSql);
			rs = pstm.executeQuery();
			while (rs.next()) {
				cardSaleId = rs.getLong(1);
			}

			pstm = getConnection().prepareStatement(sql);
			pstm.setLong(1, cardSaleId);
			pstm.setLong(2, Long.parseLong(cardInfo.getProId()));
			pstm.setLong(3, Long.parseLong(cardInfo.getCityId()));
			pstm.setDouble(4, Double.parseDouble(cardInfo.getSingleV()));
			pstm.setLong(5, Long.parseLong(cardInfo.getProNumber()));
			pstm.setDouble(6, Double.parseDouble(cardInfo.getDiscordV()));
			pstm.setDate(7, Date.valueOf(cardInfo.getSaleDate()));
			pstm.setString(8, "2");
			pstm.setString(9, null);
			pstm.setDate(10, null);

			insertNum = pstm.executeUpdate();
		} catch (SQLException e) {
			dealException(e);
		} finally {
			close(rs, pstm);
		}
		return insertNum == 0 ? null : cardSaleId;
	}

	
	public CardInfo getCardById(String cardSaleId){
		String sql = "select * from cardSale where cSaleId = " + cardSaleId;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		CardInfo findCard = new CardInfo();
		try{
			pstm = getConnection().prepareStatement(sql);
			rs = pstm.executeQuery();
			while (rs.next()) {
				findCard.setCardSaleId(Long.toString(rs
						.getLong("cSaleId")));						
				findCard.setProId(rs.getString("proId"));	
				findCard.setCityId(rs.getString("cityId"));
				findCard.setSingleV(Double.toString(rs
						.getDouble("singleV")));					
				findCard.setProNumber(Long.toString(rs
						.getLong("proNumber")));					
				findCard.setDiscordV(Double.toString(rs
						.getDouble("discordV")));					
				findCard.setSaleDate(rs.getString("saleDate"));		
				findCard.setState(rs.getString("state"));			
				findCard.setCheckId(Long.toString(rs
						.getLong("checkerId")));				
				findCard.setCheckTime(rs.getString("checkTime"));
			}
		}catch(SQLException e){
			dealException(e);
		}finally{
			close(rs,pstm);
		}
		return findCard;
	}
	// 删除卡销售信息
	public void deleteCard(String cSaleId) {
		String sql = "delete from cardSale where cSaleId= ?";
		PreparedStatement pstm = null;

		try {
			
			pstm = getConnection().prepareStatement(sql);
		
			pstm.setLong(1, Long.parseLong(cSaleId)); // 设置要删除的卡销售编号
		
			pstm.executeUpdate();
		
		} catch (SQLException e) {
			dealException(e);
		} finally {
			close(pstm);
		}
		return;
	}

	// 更新卡销售记录信息
	public CardInfo updateCard(CardInfo cardInfo) {
		String sql = "update cardSale set proId = ?, cityId = ?, singleV = ?, proNumber = ?, discordV = ?, saleDate = ? where cSaleId = ?";
		String sqlString = " SELECT  DISTINCT cSaleId,product.prodname AS cProdName,city.cityname AS cCityName, singleV,"
			+ "proNumber,discordV,saleDate,state,checkerId,checkTime "
			+ " FROM cardSale, city, product, personInfo "
			+ " WHERE cardSale.cSaleId = ? AND cardSale.Proid = product.prodid AND cardsale.cityid = city.cityid";
		PreparedStatement pstm = null;
		ResultSet rs = null;
		CardInfo findCardInfo  = new CardInfo();

		try {
			pstm = getConnection().prepareStatement(sql);
			pstm.setLong(1, Long.parseLong(cardInfo.getProId()));
			pstm.setLong(2, Long.parseLong(cardInfo.getCityId()));
			pstm.setDouble(3, Double.parseDouble(cardInfo.getSingleV()));
			pstm.setLong(4, Long.parseLong(cardInfo.getProNumber()));
			pstm.setDouble(5, Double.parseDouble(cardInfo.getDiscordV()));			
			pstm.setDate(6, Date.valueOf(cardInfo.getSaleDate()));				
			pstm.setLong(7, Long.parseLong(cardInfo.getCardSaleId()));
			
			
			
			pstm.executeUpdate();
			pstm = getConnection().prepareStatement(sqlString);
			pstm.setLong(1, Long.parseLong(cardInfo.getCardSaleId()));
		
			rs = pstm.executeQuery();
		
			
			while (rs.next()) {
				findCardInfo.setCardSaleId(Long.toString(rs
						.getLong("cSaleId")));
				findCardInfo.setProName(rs.getString("cProdName"));
				findCardInfo.setCityName(rs.getString("cCityName"));
				findCardInfo.setSingleV(Double.toString(rs
						.getDouble("singleV")));
				findCardInfo.setProNumber(Long.toString(rs
						.getLong("proNumber")));
				findCardInfo.setDiscordV(Double.toString(rs
						.getDouble("discordV")));
				findCardInfo.setSaleDate(rs.getString("saleDate"));
				findCardInfo.setState(rs.getString("state"));
				findCardInfo.setCheckId(Long.toString(rs
						.getLong("checkerId")));
				findCardInfo.setCheckTime(rs.getString("checkTime"));
			}