📄 produitda.java

📁 医药供应链管理系统
💻 JAVA
字号:
package com.captainli.dboperation;

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 com.captainli.bean.ProduitBean;
import com.captainli.struts.form.ProduitForm;
import com.captainli.util.GetConnection;

/**
 * produit表数据库操作类
 * @author CaptainLi
 *
 */
public class ProduitDA {
	private Connection conn = GetConnection.getConn();
	private PreparedStatement pstmt = null;
	private Statement stmt = null;
	private ResultSet rs = null;
	/**
	 * 关闭数据库对象
	 *
	 */
	public void closeDB(){
		try {
			if(rs != null){
				rs.close();
			}
			if(stmt != null){
				stmt.close();
			}
			if(pstmt != null){
				pstmt.close();
			}
			if(conn != null){
				conn.close();
			}
		} catch (Exception e) {
			e.getStackTrace();
		}
	}
	/**
	 * 添加商品资料
	 * @param form
	 * @param p_sc_id
	 */
	public void addProduit(ProduitForm form, int p_sc_id){
		String sql = "insert into produit values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getP_name());
			pstmt.setString(2, form.getP_pinyin());
			pstmt.setInt(3, form.getP_bc_id());
			pstmt.setInt(4, p_sc_id);
			pstmt.setString(5, form.getP_chaname());
			pstmt.setString(6, form.getP_engname());
			pstmt.setString(7, form.getP_spe());
			pstmt.setString(8, form.getP_pro());
			pstmt.setString(9, form.getP_fac());
			pstmt.setString(10, form.getP_unit());
			pstmt.setString(11, form.getP_appnum());
			pstmt.setString(12, form.getP_validitym());
			pstmt.setString(13, form.getP_validityd());
			pstmt.setString(14, form.getP_storage());
			pstmt.setString(15, form.getP_note());
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 修改产品资料
	 * @param form
	 * @param p_sc_id
	 * @param p_id
	 */
	public void updateProduit(ProduitForm form, int p_sc_id, int p_id){
		String sql = "update produit set p_name = ?, p_pinyin = ?, p_bc_id = ?, p_sc_id = ?, p_chaname = ?, p_engname = ?, p_spe = ?, p_pro = ?, p_fac = ?, p_unit = ?, p_appnum = ?, p_validitym = ?, p_validityd = ?, p_storage = ?, p_note = ? where p_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getP_name());
			pstmt.setString(2, form.getP_pinyin());
			pstmt.setInt(3, form.getP_bc_id());
			pstmt.setInt(4, p_sc_id);
			pstmt.setString(5, form.getP_chaname());
			pstmt.setString(6, form.getP_engname());
			pstmt.setString(7, form.getP_spe());
			pstmt.setString(8, form.getP_pro());
			pstmt.setString(9, form.getP_fac());
			pstmt.setString(10, form.getP_unit());
			pstmt.setString(11, form.getP_appnum());
			pstmt.setString(12, form.getP_validitym());
			pstmt.setString(13, form.getP_validityd());
			pstmt.setString(14, form.getP_storage());
			pstmt.setString(15, form.getP_note());
			pstmt.setInt(16, p_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 返回所有产品
	 * @return
	 */
	public ArrayList showProduit(){
		ArrayList arry = new ArrayList();
		ProduitBean bean = null;
		String sql = "SELECT * FROM bigclass INNER JOIN produit ON bigclass.bc_id = produit.p_bc_id INNER JOIN smallclass ON produit.p_sc_id = smallclass.sc_id";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				bean = new ProduitBean(rs.getInt("p_id"), rs.getString("p_name"), rs.getString("p_pinyin"),
										rs.getInt("p_bc_id"), rs.getInt("p_sc_id"), rs.getString("p_chaname"), 
										rs.getString("p_engname"), rs.getString("p_spe"), rs.getString("p_pro"), 
										rs.getString("p_fac"), rs.getString("p_unit"), rs.getString("p_appnum"),
										rs.getString("p_validitym"), rs.getString("p_validityd"), rs.getString("p_storage"), rs.getString("p_note"),
										rs.getInt("bc_id"), rs.getString("bc_name"), rs.getInt("sc_id"), rs.getString("sc_name"), rs.getInt("sc_bc_id"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过大类ID返回所有产品
	 * @param p_bc_id
	 * @return
	 */
	public ArrayList showProduitBc(int p_bc_id){
		ArrayList arry = new ArrayList();
		ProduitBean bean = null;
		String sql = "SELECT * FROM bigclass INNER JOIN produit ON bigclass.bc_id = produit.p_bc_id INNER JOIN smallclass ON produit.p_sc_id = smallclass.sc_id WHERE p_bc_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, p_bc_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new ProduitBean(rs.getInt("p_id"), rs.getString("p_name"), rs.getString("p_pinyin"),
										rs.getInt("p_bc_id"), rs.getInt("p_sc_id"), rs.getString("p_chaname"), 
										rs.getString("p_engname"), rs.getString("p_spe"), rs.getString("p_pro"), 
										rs.getString("p_fac"), rs.getString("p_unit"), rs.getString("p_appnum"),
										rs.getString("p_validitym"), rs.getString("p_validityd"), rs.getString("p_storage"), rs.getString("p_note"),
										rs.getInt("bc_id"), rs.getString("bc_name"), rs.getInt("sc_id"), rs.getString("sc_name"), rs.getInt("sc_bc_id"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过小类ID返回产品集合
	 * @param p_sc_id
	 * @return
	 */
	public ArrayList showProduitSc(int p_sc_id){
		ArrayList arry = new ArrayList();
		ProduitBean bean = null;
		String sql = "SELECT * FROM bigclass INNER JOIN produit ON bigclass.bc_id = produit.p_bc_id INNER JOIN smallclass ON produit.p_sc_id = smallclass.sc_id WHERE p_sc_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, p_sc_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new ProduitBean(rs.getInt("p_id"), rs.getString("p_name"), rs.getString("p_pinyin"),
										rs.getInt("p_bc_id"), rs.getInt("p_sc_id"), rs.getString("p_chaname"), 
										rs.getString("p_engname"), rs.getString("p_spe"), rs.getString("p_pro"), 
										rs.getString("p_fac"), rs.getString("p_unit"), rs.getString("p_appnum"),
										rs.getString("p_validitym"), rs.getString("p_validityd"), rs.getString("p_storage"), rs.getString("p_note"),
										rs.getInt("bc_id"), rs.getString("bc_name"), rs.getInt("sc_id"), rs.getString("sc_name"), rs.getInt("sc_bc_id"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过产品ID返回产品集合
	 * @param p_id
	 * @return
	 */
	public ProduitForm showProduitById(int p_id){
		ProduitForm form = null;
		String sql = "SELECT * FROM produit WHERE p_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, p_id);
			rs = pstmt.executeQuery();
			if(rs.next()){
				form = new ProduitForm(rs.getInt("p_id"), rs.getString("p_name"), rs.getString("p_pinyin"),
										rs.getInt("p_bc_id"), rs.getInt("p_sc_id"), rs.getString("p_chaname"), 
										rs.getString("p_engname"), rs.getString("p_spe"), rs.getString("p_pro"), 
										rs.getString("p_fac"), rs.getString("p_unit"), rs.getString("p_appnum"), 
										rs.getString("p_validitym"), rs.getString("p_validityd"), 
										rs.getString("p_storage"), rs.getString("p_note"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return form;
	}
	/**
	 * 通过p_id返回产品详细资料显示到produit_show.jsp
	 * @param p_id
	 * @return
	 */
	public ProduitBean showProduit(int p_id){
		ProduitBean bean = null;
		String sql = "SELECT * FROM produit INNER JOIN bigclass ON produit.p_bc_id = bigclass.bc_id INNER JOIN smallclass ON produit.p_sc_id = smallclass.sc_id AND bigclass.bc_id = smallclass.sc_bc_id WHERE p_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, p_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new ProduitBean(rs.getInt("p_id"), rs.getString("p_name"), rs.getString("p_pinyin"), rs.getInt("p_bc_id"), rs.getInt("p_sc_id"), 
										rs.getString("p_chaname"), rs.getString("p_engname"), rs.getString("p_spe"), rs.getString("p_pro"), rs.getString("p_fac"), 
										rs.getString("p_unit"), rs.getString("p_appnum"), rs.getString("p_validitym"), rs.getString("p_validityd"), 
										rs.getString("p_storage"), rs.getString("p_note"), rs.getInt("bc_id"), rs.getString("bc_name"), rs.getInt("sc_id"), 
										rs.getString("sc_name"), rs.getInt("sc_bc_id"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return bean;
	}
	/**
	 * 通过药品名字返回药品ID
	 * @param p_name
	 * @return
	 */
	public int showProId(String p_name){
		int tmp = 0;
		String sql = "select p_id from produit where p_name = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, p_name);
			rs = pstmt.executeQuery();
			if(rs.next()){
				tmp = rs.getInt("p_id");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return tmp;
		
	}
}
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -