goodsdaoimpl.java

本程序主要实现对管理系统的初步管理

package org.ads123.goodsmanagers.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;

import org.JavaChina.myjdbc.ConnectPool;
import org.ads123.goodsmanagers.dao.GoodsDao;
import org.ads123.goodsmanagers.dto.Goods;

public class GoodsDaoImpl implements GoodsDao {

	public void goods_add(Goods goods) {
		// if(this.isExist(goods.getG_no())){
		// this.goods_update_add(goods);
		// }
		// else{
		// this.goods_new_add(goods);
		// }
		goods_update_add(goods);
	}

	// public boolean isExist(String G_no){
	// boolean result = false;
	// ArrayList<String> G_no_list = new ArrayList<String>();
	// Connection conn = ConnectPool.getInstance().getConnection();
	// PreparedStatement pstm = null;
	// ResultSet rst = null;
	// String sql = "select G_no from goods";
	// try {
	// pstm = conn.prepareStatement(sql);
	// rst = pstm.executeQuery();
	// while(rst.next()){
	// String g_no = rst.getString("G_no");
	// G_no_list.add(g_no);
	// }
	// } catch (SQLException e) {
	// e.printStackTrace();
	// }finally{
	// try {
	// if(rst != null){
	// rst.close();
	// }
	// if(pstm != null){
	// pstm.close();
	// }
	// if(conn != null && !conn.isClosed()){
	// ConnectPool.getInstance().destroyConnection(conn);
	// }
	// } catch (SQLException e) {
	// e.printStackTrace();
	// }
	// }
	// //判断是否存在相对应的商品Goods
	// for(String list:G_no_list){
	// if(list.equals(G_no)){
	// result = true;
	// }
	// }
	// return result;
	// }
	//	
	// public void goods_new_add(Goods goods){
	// Connection conn = ConnectPool.getInstance().getConnection();
	// PreparedStatement pstm = null;
	// String sql = "insert into goods values(?, ?, ?, ?, ?, ?, ?, ?)";
	// try {
	// pstm = conn.prepareStatement(sql);
	// pstm.setString(1, goods.getG_no());
	// pstm.setString(2, goods.getG_name());
	// pstm.setString(3, goods.getC_no());
	// pstm.setString(4, goods.getT_no());
	// pstm.setInt(5, goods.getG_count());
	// pstm.setString(6, goods.getM_no());
	// pstm.setTimestamp(7, new Timestamp(System.currentTimeMillis()));
	// pstm.setTimestamp(8, new Timestamp(System.currentTimeMillis() +
	// 2073600000));
	//			
	// pstm.executeUpdate();
	// } catch (SQLException e) {
	// e.printStackTrace();
	// }finally{
	// try {
	// if(pstm != null){
	// pstm.close();
	// }
	// if(conn != null && !conn.isClosed()){
	// ConnectPool.getInstance().destroyConnection(conn);
	// }
	// } catch (SQLException e) {
	// e.printStackTrace();
	// }
	// }
	// }

	public void goods_update_add(Goods goods) {
		Connection conn = ConnectPool.getInstance().getConnection();
		PreparedStatement pstm = null;
		ResultSet rst = null;
		String sql_0 = "select G_count from goods where G_name = ?";
		String sql = "update goods set G_count = ?, IN_time = ? where G_name = ?";
		// String sql_1 = "update rent from rent where G_name = ?";
		String sql_1 = "update rent set r_use = ?, r_nouse = ? where G_name = ?";
		try {
			// 获取数据库中对应产品的数量
			int g_count = 0;
			pstm = conn.prepareStatement(sql_0);
			pstm.setString(1, goods.getG_name());
			rst = pstm.executeQuery();
			if (rst.next()) {
				g_count = rst.getInt("G_count");
			}
			// 更新数据库中对应产品的数量
			int new_G_count = g_count + goods.getG_count();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, new_G_count);
			pstm.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
			pstm.setString(3, goods.getG_name());
			pstm.executeUpdate();

			// 修改仓库容量（已完成）..........................
			pstm = conn.prepareStatement(sql_1);
			pstm.setInt(1, new_G_count);
			pstm.setInt(2, 1000 - new_G_count);
			pstm.setString(3, goods.getG_name());
			pstm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstm != null) {
					pstm.close();
				}
				if (conn != null && !conn.isClosed()) {
					ConnectPool.getInstance().destroyConnection(conn);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public void goods_min(Goods goods) {
		Connection conn = ConnectPool.getInstance().getConnection();
		PreparedStatement pstm = null;
		ResultSet rst = null;
		String sql_0 = "select G_count from goods where G_name = ?";
		String sql = "update goods set G_count = ?, OUT_time = ? where G_name = ?";
		String sql_1 = "update rent set r_use = ?, r_nouse = ? where G_name = ?";
		try {
			int g_count = 0;
			pstm = conn.prepareStatement(sql_0);
			pstm.setString(1, goods.getG_name());
			rst = pstm.executeQuery();
			if (rst.next()) {
				g_count = rst.getInt("G_count");
			}
			// 更新数据库中对应产品的数量
			int new_G_count = g_count - goods.getG_count();
			pstm = conn.prepareStatement(sql);
			pstm.setInt(1, new_G_count);
			pstm.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
			pstm.setString(3, goods.getG_name());
			pstm.executeUpdate();

			// 修改仓库容量（已完成）..........................
			pstm = conn.prepareStatement(sql_1);
			pstm.setInt(1, new_G_count);
			pstm.setInt(2, 1000 - new_G_count);
			pstm.setString(3, goods.getG_name());
			pstm.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (pstm != null) {
					pstm.close();
				}
				if (conn != null && !conn.isClosed()) {
					ConnectPool.getInstance().destroyConnection(conn);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public ArrayList<Goods> findAllGoods() {
		ArrayList<Goods> goodslist = new ArrayList<Goods>();
		Goods goods = null;

		Connection conn = ConnectPool.getInstance().getConnection();
		PreparedStatement pstm = null;
		ResultSet rst = null;
		String sql = "select G_no, G_name, C_name, T_no, G_count, M_name, IN_time from goods";

		try {
			pstm = conn.prepareStatement(sql);
			rst = pstm.executeQuery();
			while (rst.next()) {
				String g_no = rst.getString("G_no");
				String g_name = rst.getString("G_name");
				String c_name = rst.getString("C_name");
				String t_no = rst.getString("T_no");
				int g_count = rst.getInt("G_count");
				String m_name = rst.getString("M_name");
				Date in_time = rst.getDate("IN_time");

				goods = new Goods();
				goods.setG_no(g_no);
				goods.setG_name(g_name);
				goods.setC_name(c_name);
				goods.setT_no(t_no);
				goods.setG_count(g_count);
				goods.setM_name(m_name);
				goods.setIN_time(in_time);

				goodslist.add(goods);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rst != null) {
					rst.close();
				}
				if (pstm != null) {
					pstm.close();
				}
				if (conn != null && !conn.isClosed()) {
					ConnectPool.getInstance().destroyConnection(conn);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return goodslist;
	}

	public static void main(String[] args) {
		Goods goods = new Goods();
		goods.setG_count(200);
		goods.setG_name("索尼笔记本");
		ArrayList<Goods> list = new GoodsDaoImpl().findAllGoods();
		for(Goods s : list){
			System.out.println(s.getG_name());
		}
	}

	public Goods findGoodsByG_name(String G_name) {
		Goods goods = null;
		
		Connection conn = ConnectPool.getInstance().getConnection();
		PreparedStatement pstm = null;
		ResultSet rst = null;
		String sql = "select G_no, G_name, C_name, T_no, G_count, M_name, IN_time from goods where G_name = ?";
		
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, G_name);
			rst = pstm.executeQuery();
			if(rst.next()){
				String g_no = rst.getString("G_no");
				String g_name = rst.getString("G_name");
				String c_name = rst.getString("C_name");
				String t_no = rst.getString("T_no");
				int g_count = rst.getInt("G_count");
				String m_name = rst.getString("M_name");
				Date in_time = rst.getDate("IN_time");
				
				goods = new Goods();
				goods.setG_no(g_no);
				goods.setG_name(g_name);
				goods.setC_name(c_name);
				goods.setT_no(t_no);
				goods.setG_count(g_count);
				goods.setM_name(m_name);
				goods.setIN_time(in_time);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return goods;
	}

	public ArrayList<Goods> findGoodsListByC_name(String C_name) {
		ArrayList<Goods> goods_list = new ArrayList<Goods>();
		Goods goods = null;
		
		Connection conn = ConnectPool.getInstance().getConnection();
		PreparedStatement pstm = null;
		ResultSet rst = null;
		String sql = "select G_no, G_name, C_name, T_no, G_count, M_name, IN_time from goods where C_name = ?";
		
		try {
			pstm = conn.prepareStatement(sql);
			pstm.setString(1, C_name);
			rst = pstm.executeQuery();
			while(rst.next()){
				String g_no = rst.getString("G_no");
				String g_name = rst.getString("G_name");
				String c_name = rst.getString("C_name");
				String t_no = rst.getString("T_no");
				int g_count = rst.getInt("G_count");
				String m_name = rst.getString("M_name");
				Date in_time = rst.getDate("IN_time");
				
				goods = new Goods();
				goods.setG_no(g_no);
				goods.setG_name(g_name);
				goods.setC_name(c_name);
				goods.setT_no(t_no);
				goods.setG_count(g_count);
				goods.setM_name(m_name);
				goods.setIN_time(in_time);
				
				goods_list.add(goods);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return goods_list;
	}

}