p3110101dataaccessbean.java

使用myeclipse进行的项目设计实例

package cn.com.zzrd.easy.core;
import java.awt.List;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import cn.com.zzrd.easy.bass.*;
public class P3110101DataAccessBean {

	static ResultSet rs=null;
	static PreparedStatement pstmt=null;
	public static ArrayList executeSelect(P310101Data p){
		
		ArrayList a=new ArrayList();
		StringBuffer sb=new StringBuffer();
		sb.append("select distinct ");
		sb.append(" ID,MJ60P015.SELLERCODE,SELLER_NAME,NAMETO,END_NAME,MJ60P015.YC,YC_NAME,APPLYDATE,ENDDATE,MJ60P015.DEL_FLG");
		sb.append(" , MJ60P015.REG_CD, MJ60P015.REG_TS ,MJ60P015.UPD_CD , MJ60P015.UPD_TS");
		sb.append(" from MJ60P015 join MJ60Y019 on (MJ60P015.NAMETO=MJ60Y019.END_CD)");
		sb.append(" join MJ60Y010 on (MJ60P015.SELLERCODE=MJ60Y010.SELLERCODE)");
		sb.append(" join MJ60K001 on (MJ60P015.YC=MJ60K001.YC)");
		sb.append(" where MJ60P015.DEL_FLG=0 ");
		if(p!=null){
			if(p.getSELLERCODE()!=0){
			sb.append(" and MJ60P015.SELLERCODE="+p.getSELLERCODE());
			}
			if(p.getNAMETO()!=0){
			sb.append(" and NAMETO="+p.getNAMETO());
			}
			if(p.getAPPLYDATE()!=0){
			sb.append(" and APPLYDATE="+p.getAPPLYDATE());
			}
			if(p.getID()!=0){
				sb.append(" and ID="+p.getID());
				}
		}
		sb.append(" order by SELLERCODE, NAMETO, APPLYDATE DESC");
		
		try {
			ConnDb.dbOpen();
			pstmt=ConnDb.conn.prepareStatement(sb.toString());
			rs=pstmt.executeQuery();
			//System.out.println("��ݿ��ѯ");
			while(rs.next()){
				P310101Data d=new P310101Data(rs.getLong(1),rs.getLong(2),rs.getString(3),rs.getLong(4),rs.getString(5),
						                      rs.getLong(6),rs.getString(7),rs.getLong(8),rs.getLong(9),rs.getLong(10)
						                      ,rs.getString(11),rs.getLong(12),rs.getString(13),rs.getLong(14)
				);
				a.add(d);
				
				//System.out.println(d.getID()+d.getSELLER_NAME()+" ");
			}
		} catch (SQLException e) {
			// TODO �Զ���� catch ��
			e.printStackTrace();
		} catch (Exception e) {
			// TODO �Զ���� catch ��
			e.printStackTrace();
		}finally{
			try {
				ConnDb.dbClose();
				pstmt.close();
				rs.close();
				
			} catch (Exception e) {
				// TODO �Զ���� catch ��
				e.printStackTrace();
			}
		}
		return a;
	}
	
	public static long selectMaxId(){
		int i=0;
		String s="select max(ID)+1 from MJ60P015";
		
	    try {
			ConnDb.dbOpen();
			pstmt=ConnDb.conn.prepareStatement(s);
			rs=pstmt.executeQuery();
			while(rs.next()){
			     i=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO �Զ���� catch ��
			e.printStackTrace();
		} catch (Exception e) {
			// TODO �Զ���� catch ��
			e.printStackTrace();
		}finally{
			try {
				ConnDb.dbClose();
				pstmt.close();
				rs.close();
			} catch (SQLException e) {
				// TODO �Զ���� catch ��
				e.printStackTrace();
			} catch (Exception e) {
				// TODO �Զ���� catch ��
				e.printStackTrace();
			}
		}
		return i;
	}
	
	public static void executeInsert(P310101Data p){
		
		StringBuffer sb=new StringBuffer();
		sb.append("insert into MJ60P015 (SELLERCODE,NAMETO,YC,APPLYDATE,ENDDATE,ID,DEL_FLG");
		sb.append(" ,REG_CD, REG_TS ");
		sb.append(")values(");
		sb.append(" "+p.getSELLERCODE());
		sb.append(", "+p.getNAMETO());
		sb.append(", "+p.getYC());
		sb.append(", "+p.getAPPLYDATE());
		sb.append(", "+p.getENDDATE());
		sb.append(", "+selectMaxId());
		sb.append(", "+p.getDEL_FLG());
		sb.append(", ' "+p.getREG_CD()+" ' ");
		sb.append(", "+p.getREG_TS());
		sb.append(")");
		try {
			ConnDb.dbOpen();
			pstmt=ConnDb.conn.prepareStatement(sb.toString());
			pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO �Զ���� catch ��
			e.printStackTrace();
		} catch (Exception e) {
			// TODO �Զ���� catch ��
			e.printStackTrace();
		}finally{
			try {
				ConnDb.dbClose();
				pstmt.close();
			} catch (Exception e) {
				// TODO �Զ���� catch ��
				e.printStackTrace();
			}
		}
	}
	
	public static void executeUpdate(P310101Data p){
		StringBuffer sb=new StringBuffer();
		sb.append("update MJ60P015 ");
		sb.append("  set ");
		//sb.append(" SELLERCODE="+p.getSELLERCODE());
		//sb.append(", NAMETO="+p.getNAMETO());
		sb.append(" YC="+p.getYC());
		//sb.append(", APPLYDATE="+p.getAPPLYDATE());
		//sb.append(", ENDDATE="+p.getENDDATE());
		sb.append(", UPD_CD= '"+p.getUPD_CD()+"'");
		sb.append(", UPD_TS="+p.getUPD_TS());
		sb.append(" where ID="+p.getID());
		
		try {
			ConnDb.dbOpen();
			pstmt=ConnDb.conn.prepareStatement(sb.toString());
			pstmt.executeUpdate();
			
		} catch (SQLException e) {
			// TODO �Զ���� catch ��
			e.printStackTrace();
		} catch (Exception e) {
			// TODO �Զ���� catch ��
			e.printStackTrace();
		}finally{
			try {
				ConnDb.dbClose();
				pstmt.close();
			} catch (SQLException e) {
				// TODO �Զ���� catch ��
				e.printStackTrace();
			} catch (Exception e) {
				// TODO �Զ���� catch ��
				e.printStackTrace();
			}
		}
		
	}
}