goodsconsumeddao.java

一个KTV管理系统

package dao.consumeddao;

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

import vo.GoodsConsumedInfo;
import vo.Room;

import common.dbconnection.DbConnection;

public class GoodsConsumedDao {
	// 通过消费流水号判断是否存在相应的商品消费信息记录
	public Vector getGoodsConsumedInfo(String value) {
		Vector v = null;
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		String sql = "select * from KTV_goods_consumed_INFO where consumed_id = '"
				+ value + "'";
		System.out.println(sql);
		try {
			con = new DbConnection().getConnection();
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			v = new Vector();
			while (set.next()) {

				long consumedId = set.getLong(1);
				String goodsName = set.getString(2);
				int consumedNumber = set.getInt(3);
				double consumedMoney = set.getDouble(4);
				String consumedTime = set.getString(5);
				v.addElement(new GoodsConsumedInfo(consumedId, goodsName,
						consumedNumber, consumedMoney, consumedTime));
			}
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return v;
	}
	//通过商品名来获取商品的单价
	public double getGoodsConsumedSimpleprice(String value) {
		double simpleprice = 0;
		Vector v = null;
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		String sql = "select * from KTV_goods_consumed_INFO where GOODS_NAME  = '"
				+ value + "'";
		System.out.println(sql);
		try {
			con = new DbConnection().getConnection();
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			v = new Vector();
			while (set.next()) {
				double consumedMoney = set.getDouble(4);
				simpleprice=consumedMoney;
				
			}
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return simpleprice;
	}

	// 通过消费流水号判断是否存在相应的商品消费信息记录
	public boolean hasGoodsConsumedInfo(String value) {
		boolean flag = false;
		Vector v = null;
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		String sql = "select * from KTV_goods_consumed_INFO where consumed_id = '"
				+ value + "'";
		System.out.println(sql);
		try {
			con = new DbConnection().getConnection();
			stmt = con.createStatement();
			set = stmt.executeQuery(sql);
			v = new Vector();
			while (set.next()) {
				flag = true;
				System.out.println("存在");
			}
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return flag;
	}

	public boolean insertGoodsInfo(GoodsConsumedInfo value) {

		boolean flag = false;
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		String sql1 = "insert into ktv_goods_consumed_info(consumed_Id, goods_Name, consumed_Number,"
				+ "consumed_Money,consumed_Time)"
				+ " values('"
				+ value.getConsumedId()
				+ "','"
				+ value.getGoodsName()
				+ "','"
				+ value.getConsumedNumber()
				+ "','"
				+ value.getConsumedMoney()
				+ "',to_date('" + value.getConsumedTime().trim()

				+ "','yyyy-MM-dd HH24:mi:ss'))";

		System.out.println(sql1);

		try {
			con = new DbConnection().getConnection();
			stmt = con.createStatement();
			set = stmt.executeQuery(sql1);
			if (set.next()) {
				flag = true;
			}
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return flag;
	}

	public boolean insertGoodsInfo2(String consumedid,String goodsname,int consumednumber,double consumedmoney,String consumedtime) {

		boolean flag = false;
		Connection con = null;
		Statement stmt = null;
		ResultSet set = null;
		String sql1 = "insert into ktv_goods_consumed_info(consumed_Id, goods_Name, consumed_Number,"
				+ "consumed_Money,consumed_Time)"
				+ " values('"
				+consumedid
				+ "','"
				+goodsname
				+ "','"
				+ consumednumber
				+ "','"
				+ consumedmoney
				+ "',to_date('" + consumedtime

				+ "','yyyy-MM-dd HH24:mi:ss'))";

		System.out.println(sql1);

		try {
			con = new DbConnection().getConnection();
			stmt = con.createStatement();
			set = stmt.executeQuery(sql1);
			if (set.next()) {
				flag = true;
			}
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return flag;
	}

	
	public boolean updateGoodsInfo(String value, int number) {
		boolean flag = false;
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet set = null;

		try {
			con = new DbConnection().getConnection();
			String sql = "update ktv_goods_info set goods_stock=goods_stock-'"
					+ number + "' where goods_name = '" + value + "'";
			System.out.println(sql);
			stmt = con.prepareStatement(sql);
			set = stmt.executeQuery(sql);
			flag = true;
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return flag;
	}

	public boolean deleteGoodsInfo(String date) {
		boolean flag = false;
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet set = null;

		String sql = "delete from ktv_goods_consumed_info where consumed_time =to_date('"
				+ date.substring(0, 19) + "','yyyy-MM-dd hh24:mi:ss')";
		System.out.println(sql);
		try {
			con = new DbConnection().getConnection();
			System.out.println(sql);
			stmt = con.prepareStatement(sql);
			set = stmt.executeQuery(sql);
			flag = true;
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return flag;
	}

	public boolean updateConsumedInfo(String value, int number) {
		boolean flag = false;
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet set = null;

		try {
			con = new DbConnection().getConnection();
			String sql = "update ktv_goods_info set goods_stock=goods_stock + '"
					+ number + "' where goods_name = '" + value + "'";
			System.out.println(sql);
			stmt = con.prepareStatement(sql);
			set = stmt.executeQuery(sql);
			flag = true;
		} catch (Exception e) {
			System.out.println("异常信息: " + e.getMessage());
		}
		return flag;
	}

}