intercourseda.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.struts.form.IntercourseForm;
import com.captainli.util.GetConnection;

/**
 * intercourse表数据库操作类
 * @author CaptainLi
 *
 */
public class intercourseDA {
	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 showIntercourse(){
		ArrayList arry = new ArrayList();
		IntercourseForm form = null;
		String sql = "select * from intercourse order by i_id";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()){
				form = new IntercourseForm(rs.getInt("i_id"), rs.getString("i_name"), rs.getString("i_pinyin"), 
											rs.getInt("i_type"), rs.getString("i_tel"), rs.getString("i_addr"), 
											rs.getString("i_man"), rs.getString("i_zipcode"), rs.getString("i_tax"), 
											rs.getString("i_bank"), rs.getString("i_ent"), rs.getDouble("i_yingshou"), 
											rs.getDouble("i_yingfu"), rs.getString("i_note"));
				arry.add(form);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过往来单位类型查询
	 * @param i_type
	 * @return
	 */
	public ArrayList showIntercourseByType(int i_type){
		ArrayList arry = new ArrayList();
		IntercourseForm form = null;
		String sql = "select * from intercourse where i_type = ? order by i_id";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, i_type);
			rs = pstmt.executeQuery();
			while(rs.next()){
				form = new IntercourseForm(rs.getInt("i_id"), rs.getString("i_name"), rs.getString("i_pinyin"), 
											rs.getInt("i_type"), rs.getString("i_tel"), rs.getString("i_addr"), 
											rs.getString("i_man"), rs.getString("i_zipcode"), rs.getString("i_tax"), 
											rs.getString("i_bank"), rs.getString("i_ent"), rs.getDouble("i_yingshou"), 
											rs.getDouble("i_yingfu"), rs.getString("i_note"));
				arry.add(form);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过拼音返回单位集合
	 * @param i_pinyin
	 * @return
	 */
	public ArrayList showIntercourseByPinyin(String i_pinyin){
		ArrayList arry = new ArrayList();
		IntercourseForm form = null;
		String sql = "select * from intercourse where i_pinyin like ? order by i_id";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+i_pinyin+"%");
			rs = pstmt.executeQuery();
			while(rs.next()){
				form = new IntercourseForm(rs.getInt("i_id"), rs.getString("i_name"), rs.getString("i_pinyin"), 
											rs.getInt("i_type"), rs.getString("i_tel"), rs.getString("i_addr"), 
											rs.getString("i_man"), rs.getString("i_zipcode"), rs.getString("i_tax"), 
											rs.getString("i_bank"), rs.getString("i_ent"), rs.getDouble("i_yingshou"), 
											rs.getDouble("i_yingfu"), rs.getString("i_note"));
				arry.add(form);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过单位名称查询
	 * @param i_name
	 * @return
	 */
	public ArrayList showIntercourseByName(String i_name){
		ArrayList arry = new ArrayList();
		IntercourseForm form = null;
		String sql = "select * from intercourse where i_name like ? order by i_id";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+i_name+"%");
			rs = pstmt.executeQuery();
			while(rs.next()){
				form = new IntercourseForm(rs.getInt("i_id"), rs.getString("i_name"), rs.getString("i_pinyin"), 
											rs.getInt("i_type"), rs.getString("i_tel"), rs.getString("i_addr"), 
											rs.getString("i_man"), rs.getString("i_zipcode"), rs.getString("i_tax"), 
											rs.getString("i_bank"), rs.getString("i_ent"), rs.getDouble("i_yingshou"), 
											rs.getDouble("i_yingfu"), rs.getString("i_note"));
				arry.add(form);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过单位类型和单位名称返回单位集合
	 * @param i_name
	 * @param i_type
	 * @return
	 */
	public ArrayList showIntercourseByName(String i_name, int i_type){
		ArrayList arry = new ArrayList();
		IntercourseForm form = null;
		String sql = "select * from intercourse where i_name like ? and i_type = ? order by i_id";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+i_name+"%");
			pstmt.setInt(2, i_type);
			rs = pstmt.executeQuery();
			while(rs.next()){
				form = new IntercourseForm(rs.getInt("i_id"), rs.getString("i_name"), rs.getString("i_pinyin"), 
											rs.getInt("i_type"), rs.getString("i_tel"), rs.getString("i_addr"), 
											rs.getString("i_man"), rs.getString("i_zipcode"), rs.getString("i_tax"), 
											rs.getString("i_bank"), rs.getString("i_ent"), rs.getDouble("i_yingshou"), 
											rs.getDouble("i_yingfu"), rs.getString("i_note"));
				arry.add(form);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 通过拼音和单位类型返回单位集合
	 * @param i_pinyin
	 * @param i_type
	 * @return
	 */
	public ArrayList showIntercourseByPinyin(String i_pinyin, int i_type){
		ArrayList arry = new ArrayList();
		IntercourseForm form = null;
		String sql = "select * from intercourse where i_pinyin like ? and i_type = ? order by i_id";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, "%"+i_pinyin+"%");
			pstmt.setInt(2, i_type);
			rs = pstmt.executeQuery();
			while(rs.next()){
				form = new IntercourseForm(rs.getInt("i_id"), rs.getString("i_name"), rs.getString("i_pinyin"), 
											rs.getInt("i_type"), rs.getString("i_tel"), rs.getString("i_addr"), 
											rs.getString("i_man"), rs.getString("i_zipcode"), rs.getString("i_tax"), 
											rs.getString("i_bank"), rs.getString("i_ent"), rs.getDouble("i_yingshou"), 
											rs.getDouble("i_yingfu"), rs.getString("i_note"));
				arry.add(form);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return arry;
	}
	/**
	 * 往来单位添加
	 * @param form
	 */
	public void addIntercourse(IntercourseForm form){
		String sql = "insert into intercourse values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getI_name());
			pstmt.setString(2, form.getI_pinyin());
			pstmt.setInt(3, form.getI_type());
			pstmt.setString(4, form.getI_tel());
			pstmt.setString(5, form.getI_addr());
			pstmt.setString(6, form.getI_man());
			pstmt.setString(7, form.getI_zipcode());
			pstmt.setString(8, form.getI_tax());
			pstmt.setString(9, form.getI_bank());
			pstmt.setString(10, form.getI_ent());
			pstmt.setDouble(11, form.getI_yingshou());
			pstmt.setDouble(12, form.getI_yingfu());
			pstmt.setString(13, form.getI_note());
			pstmt.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 通过ID查询返回IntercourseForm对象
	 * @return
	 */
	public IntercourseForm showIntercourseById(int i_id){
		IntercourseForm form = null;
		String sql = "select * from intercourse where i_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, i_id);
			rs = pstmt.executeQuery();
			while(rs.next()){
				form = new IntercourseForm(rs.getInt("i_id"), rs.getString("i_name"), rs.getString("i_pinyin"), 
						rs.getInt("i_type"), rs.getString("i_tel"), rs.getString("i_addr"), 
						rs.getString("i_man"), rs.getString("i_zipcode"), rs.getString("i_tax"), 
						rs.getString("i_bank"), rs.getString("i_ent"), rs.getDouble("i_yingshou"), 
						rs.getDouble("i_yingfu"), rs.getString("i_note"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return form;
	}
	/**
	 * 修改往来单位信息
	 * @param form
	 */
	public void updateIntercourse(IntercourseForm form, int i_id){
		String sql = "update intercourse set i_name = ?, i_pinyin = ?, i_type = ?, i_tel = ?, i_addr = ?, i_man = ?, i_zipcode = ?, i_tax = ?, i_bank = ?, i_ent = ?, i_yingshou = ?, i_yingfu = ?, i_note = ? where i_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, form.getI_name());
			pstmt.setString(2, form.getI_pinyin());
			pstmt.setInt(3, form.getI_type());
			pstmt.setString(4, form.getI_tel());
			pstmt.setString(5, form.getI_addr());
			pstmt.setString(6, form.getI_man());
			pstmt.setString(7, form.getI_zipcode());
			pstmt.setString(8, form.getI_tax());
			pstmt.setString(9, form.getI_bank());
			pstmt.setString(10, form.getI_ent());
			pstmt.setDouble(11, form.getI_yingshou());
			pstmt.setDouble(12, form.getI_yingfu());
			pstmt.setString(13, form.getI_note());
			pstmt.setInt(14, i_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 往来单位应付变化
	 * @param i_id
	 * @param i_yingfu
	 */
	public void updateYingFu(int i_id, double i_yingfu){
		String sql = "update intercourse set i_yingfu = i_yingfu + ? where i_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, i_yingfu);
			pstmt.setInt(2, i_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 往来单位应收变化
	 * @param i_id
	 * @param i_yingshow
	 */
	public void updateYingShou(int i_id, double i_yingshou){
		String sql = "update intercourse set i_yingshou = i_yingshou + ? where i_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setDouble(1, i_yingshou);
			pstmt.setInt(2, i_id);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
	}
	/**
	 * 通过往来单位id返回往来单位名称
	 * @param i_id
	 * @return
	 */
	public String showI_name(int i_id){
		String tmp = "";
		String sql = "select i_name from intercourse where i_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, i_id);
			rs = pstmt.executeQuery();
			if(rs.next()){
				tmp = rs.getString("i_name");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			closeDB();
		}
		return tmp;
	}
}