vipdaoimpl.java

一个优秀的干洗店管理系统

/**
 * 
 */
package dao.vip.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import javax.swing.JOptionPane;

import vo.VipCardVo;
import vo.VipChargeVo;
import vo.VipVo;
import dao.common.DBConnectionManager;
import dao.common.DbException;
import dao.common.sql.VipSql;
import dao.vip.VipDao;

/**
 * @author lulicheng
 * 
 */
public class VipDaoImpl implements VipDao {

	private DBConnectionManager manager = DBConnectionManager.getInstance();

	/**
	 * 从数据库表Consumer中获取操作员的部分信息，并且显示出来以便于操作员管理
	 */
	public Vector<VipVo> searchVipAll(String sql) throws DbException {
		Vector<VipVo> vector = null;
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		try {
			con = manager.getConnection("oracle");
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			vector = new Vector<VipVo>();

			while (set.next()) {
				int id = set.getInt("VIP_ID");
				int cardId = set.getInt("CARD_ID");
				String name = set.getString("VIP_NAME");
				String sex = set.getString("VIP_SEX");
				String level = set.getString("VIP_LEVEL");
				String state = set.getString("CARD_STATE");
				double rstMoney = set.getDouble("REST_MONEY");
				String phone = set.getString("VIP_PHONE");
				int point = set.getInt("VIP_POINT");
				String birthday = set.getString("VIP_BIRTHDAY");
				String regDate = set.getString("REGIST_DATE");

				vector.add(new VipVo(new VipCardVo(id, cardId, "", regDate,
						level, rstMoney, point, state), name, sex, birthday,
						phone, ""/* String vipAddress */));

			}
			if (vector.size() == 0) {
				throw new DbException("searchConsumerAll::vector.size() == 0");
			}
		} catch (SQLException e) {
			System.out.println("用户查找dao异常:= " + e.getMessage());
		} finally {
			manager.freeConnection("oracle", con);
		}
		return vector;
	}

	/**
	 * 按照关键字进行查询，返回一个Vector<VipVo>保存查询的结果
	 */
	public Vector<VipVo> searchVipByKey(String sql, String key)
			throws DbException {
		Vector<VipVo> vector = null;
		PreparedStatement pstmt = null;
		Connection con = null;
		ResultSet set = null;

		try {
			con = manager.getConnection("oracle");
			pstmt = con.prepareStatement(sql);
			if (sql.equals(VipSql.SEARCH_UNCLEAR_BY_VIP_NAME)) {
				pstmt.setString(1, "%" + key + "%");
			} else if (sql.equals(VipSql.SEARCH_BY_VIP_ID)) {
				pstmt.setInt(1, Integer.parseInt(key));
			}
			set = pstmt.executeQuery();

			vector = new Vector<VipVo>();
			while (set.next()) {
				int id = set.getInt("VIP_ID");
				int cardId = set.getInt("CARD_ID");
				String name = set.getString("VIP_NAME");
				String sex = set.getString("VIP_SEX");
				String level = set.getString("VIP_LEVEL");
				String state = set.getString("CARD_STATE");
				double rstMoney = set.getDouble("REST_MONEY");
				String phone = set.getString("VIP_PHONE");
				int point = set.getInt("VIP_POINT");
				String birthday = set.getString("VIP_BIRTHDAY");
				String regDate = set.getString("REGIST_DATE");
				String psw = set.getString("VIP_PWD");
				String vipAddress = set.getString("VIP_ADDRESS");

				vector.add(new VipVo(new VipCardVo(id, cardId, psw, regDate,
						level, rstMoney, point, state), name, sex, birthday,
						phone, vipAddress));
			}
		} catch (NumberFormatException e) {
			JOptionPane.showMessageDialog(
					null,"会员搜素数据格式异常:= " + e.getMessage(),"提示",JOptionPane.YES_OPTION);
		} catch (SQLException e) {
			JOptionPane.showMessageDialog(
					null,"会员搜素异常:= " + e.getMessage(),"提示",JOptionPane.YES_OPTION);
		} finally {
			manager.freeConnection("oracle", con);
		}
		return vector;
	}

	/**
	 * 根据操作员的Id来删除对应的记录
	 */
	public boolean deleteVip(int vipId) {
		Connection con = null;
		PreparedStatement pstmt1 = null;
		PreparedStatement pstmt2 = null;
		boolean flag = false;
		try {
			con = manager.getConnection("oracle");
			con.setAutoCommit(false);

			pstmt1 = con.prepareStatement(VipSql.DELETE_VIP_BY_ID);
			pstmt1.setInt(1, vipId);
			int count1 = pstmt1.executeUpdate();

			pstmt2 = con.prepareStatement(VipSql.DELETE_CARD_BY_ID);
			pstmt2.setInt(1, vipId);
			int count2 = pstmt2.executeUpdate();

			if ((count1 != 0) && (count2 != 0)) {
				flag = true;
				con.commit(); // 当两个更新的结果都不为零时，说明整个事物进行顺利，此时提交结果
			} else {
				con.rollback(); // 如果两个过程没有同时成功，则回滚
			}
			con.setAutoCommit(true); // 将自动提交设置回来

		} catch (SQLException e) {

			e.printStackTrace();
		} finally {
			manager.freeConnection("oracle", con);
		}

		return flag;
	}

	/**
	 * 获取输入的值对象并将其插入到数据库
	 */
	public boolean insertVipInfo(VipVo value) throws DbException {
		boolean flag = false;
		Connection con = null;

		PreparedStatement pstmt1 = null;
		PreparedStatement pstmt2 = null;

		try {
			con = manager.getConnection("oracle");
			con.setAutoCommit(false); // 设置为非自动提交
			if (this.VipIdExists(con, value.getVipCard().getVipId())) {
				throw new DbException("不能插入，数据库中存在相同编号的雇员");
			}
			pstmt1 = con.prepareStatement(VipSql.INSERTINTO_VIP_BY_ID);
			pstmt1.setInt(1, value.getVipCard().getVipId());
			pstmt1.setString(2, value.getVipName());
			pstmt1.setString(3, value.getVipSex());
			pstmt1.setString(4, value.getVipBirthday());
			pstmt1.setString(5, value.getVipPhone());
			pstmt1.setString(6, value.getVipAddress());
			int count1 = pstmt1.executeUpdate(); // 执行更新语句
			/*
			 * "insert into vip " +
			 * "values( ?, ?, ?, to_date(?, 'yyyy-MM-dd'), ?, ?)"
			 */
			/*
			 * card_id number(5),--卡号 vip_id number(8),--会员号 vip_pwd
			 * varchar(10),--会员卡密码 regist_date date,--注册时间 vip_level
			 * varchar(4),--会员级别 rest_money number(5,1),--余额 vip_point
			 * number(5),--会员卡积分 card_state varchar(4),--会员卡状态
			 */
			pstmt2 = con.prepareStatement(VipSql.INSERTINTO_CARD_BY_ID);
			pstmt2.setInt(1, value.getVipCard().getCardId());
			pstmt2.setInt(2, getNewCardId());
			System.out.println("添加会员失败：" + getNewCardId());

			pstmt2.setString(3, value.getVipCard().getVipPsw());
			pstmt2.setString(4, value.getVipCard().getRegistDate());
			pstmt2.setString(5, value.getVipCard().getVipLevel());
			pstmt2.setDouble(6, value.getVipCard().getRestMoney());
			pstmt2.setInt(7, value.getVipCard().getPoints());
			pstmt2.setString(8, value.getVipCard().getCardState());
			int count2 = pstmt2.executeUpdate(); // 执行更新语句
			/*
			 * "insert into vip_card " +
			 * "values(?, ?, to_date(?, 'yyyy-MM-dd'), ?, ?, ?, ?)"
			 */
			if ((count1 != 0) && (count2 != 0)) {
				flag = true;
				con.commit(); // 当两个更新的结果都不为零时，说明整个事物进行顺利，此时提交结果
			} else {
				con.rollback(); // 如果两个过程没有同时成功，则回滚
			}
			con.setAutoCommit(true); // 将自动提交设置回来
		} catch (SQLException e) {
			System.out.println("添加会员失败：" + e.getMessage());
		} finally {
			manager.freeConnection("oracle", con);
		}
		return flag;
	}

	public void insertIntoCardLevel() {
		Connection con = null;
		PreparedStatement pstmt = null;
		try {
			con = manager.getConnection("oracle");
			pstmt = con.prepareStatement(VipSql.INSERT_CARD_LEVEL_ONE);
			pstmt.executeUpdate();
			pstmt = con.prepareStatement(VipSql.INSERT_CARD_LEVEL_TWO);
			pstmt.executeUpdate();
			pstmt = con.prepareStatement(VipSql.INSERT_CARD_LEVEL_THREE);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			System.out.println(e.getMessage());
		} finally {
			manager.freeConnection("oracle", con);
		}