smallclassda.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.SmallclassBean;
import com.captainli.struts.form.SmallclassForm;
import com.captainli.util.GetConnection;

/**
 * smallclass表 数据库操作类
 * @author CaptainLi
 *
 */
public class SmallclassDA {
	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();
		}
	}
	/**
	 * 小类界面返回所有数据
	 * @return
	 */
	public ArrayList selectSmallClass(){
		ArrayList arry = new ArrayList();
		SmallclassBean bean = null;
		String sql = "SELECT smallclass.sc_id, smallclass.sc_name, bigclass.bc_name FROM bigclass INNER JOIN smallclass ON bigclass.bc_id = smallclass.sc_bc_id";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				bean = new SmallclassBean(rs.getInt("sc_id"), rs.getString("bc_name"), rs.getString("sc_name"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 产品小类界面 通过大类ID查询
	 * @param bc_id
	 * @return
	 */
	public ArrayList selectSmallClassByID(int bc_id){
		ArrayList arry = new ArrayList();
		SmallclassBean bean = null;
		String sql = "SELECT smallclass.sc_id, smallclass.sc_name, bigclass.bc_name FROM bigclass INNER JOIN smallclass ON bigclass.bc_id = smallclass.sc_bc_id WHERE sc_bc_id = ?";
		//System.out.println(bc_id+"");
		//System.out.println(sql);
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, bc_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				bean = new SmallclassBean(rs.getInt("sc_id"), rs.getString("bc_name"), rs.getString("sc_name"));
				arry.add(bean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过sc_id得到产品小类的信息
	 * @param sc_id
	 * @return
	 */
	public SmallclassForm selectSmallClassBySCID(int sc_id){
		SmallclassForm form = null;
		String sql = "select sc_id, sc_name, sc_bc_id from smallclass where sc_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, sc_id);
			rs = pstmt.executeQuery();
			if(rs.next()){
				form = new SmallclassForm(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 form;
	}
	/**
	 * 添加产品小类
	 * @param form
	 */
	public void addSmallclass(SmallclassForm form){
		String sql = "insert into smallclass values (?, ?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getSc_name());
			pstmt.setInt(2, form.getSc_bc_id());
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 修改产品小类信息
	 * @param form
	 * @param sc_id
	 */
	public void updateSmallclass(SmallclassForm form, int sc_id){
		String sql = "update smallclass set sc_name = ?, sc_bc_id = ? where sc_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getSc_name());
			pstmt.setInt(2, form.getSc_bc_id());
			pstmt.setInt(3, sc_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
}