operator.java

很好的东东 本人随便做的 仅供学习之用

package xiaoshou.javabeans;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import xiaoshou.classes.CClient;
import xiaoshou.classes.CGood;
import xiaoshou.classes.COrder;
import xiaoshou.classes.COrderDetail;
import xiaoshou.classes.CUser;

public class Operator {
	//private Connection conn;
	//private Statement cmd;
	
	
	
	public static Connection fatchConnection(){
		System.out.println("开始连接数据库");
		String url="jdbc:microsoft:sqlserver://10.10.8.184:1433;databaseName=jhyy";
		Connection conn=null;
		try{
		Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
		conn= DriverManager.getConnection(url,"jhyy","jhyy");
		}catch(Exception e){
			e.printStackTrace();
		}
		System.out.println("完成连接数据库");
		return conn;	
	}
	
	//模糊查询
	public static ArrayList queryGoods(String sql,String val){
		System.out.println("开始查询药品");
		System.out.println("***sql:" + sql + " ***val:" +val);
		Connection conn=fatchConnection();
		ArrayList list=new ArrayList();
		try{
		PreparedStatement pcmd=conn.prepareStatement(sql);
		if(! "null".equals(val))
			pcmd.setString(1,val);

		ResultSet rs=pcmd.executeQuery();
		while(rs.next()){
			CGood good=new CGood();
			good.setId(rs.getInt("ID"));
			System.out.print(good.getId()+"---");
			
			good.setName(rs.getString("name"));
			System.out.print(good.getName()+"---");
			
			good.setNotes(rs.getString("comment"));	
			System.out.print(good.getNotes()+"---");
			
			good.setPrice(rs.getFloat("price"));
			System.out.print(good.getPrice()+"---");
			
			good.setType(rs.getString("type"));
			System.out.print(good.getType() +"---");
			
			good.setSpan(rs.getInt("validity"));
			System.out.print(good.getSpan()+"---");
			
			System.out.println("");
			
			list.add(good);
		}
		rs.close();
		pcmd.close();
		conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}		
		System.out.println("完成查询药品");
		return list;
	}
	
	//插入新客户
	public static boolean newClient(String identify,String client,
	String tel,String addr,String type,float credit,String fax,String email,		
	float discount,String pswd){
		//boolean r=false;
		int n=0;
		System.out.println("开始注册");
		//System.out.println("sql:" + sql + " val:" +val);
		Connection conn=fatchConnection();
		String sql="insert into customerinfo(identifier,name,tel,address,type," +
				"credit,fax,e_mail,discount,password)";
		sql+=" values(?,?,?,?,?,?,?,?,?,?)";
		try{
			PreparedStatement pcmd=conn.prepareStatement(sql);
			pcmd.setString(1, identify);
			pcmd.setString(2, client);
			pcmd.setString(3, tel);
			pcmd.setString(4, addr);
			pcmd.setString(5, type);
			pcmd.setFloat(6, credit);
			pcmd.setString(7, fax);
			pcmd.setString(8, email);
			pcmd.setFloat(9, discount);
			pcmd.setString(10, pswd);
			n=pcmd.executeUpdate();
			pcmd.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}

		return n>0;
	}
	
	//根据药品ID查询药品
	public static CGood queryGood(String id){
		CGood good=new CGood();
		Connection conn=fatchConnection();
		Statement cmd=null;
		String sql="select * from druginfo where ID = " + id;
		try{
			cmd=conn.createStatement();
			ResultSet rs=cmd.executeQuery(sql);
			rs.next();
			good.setId(rs.getInt("ID"));;
			good.setSid(rs.getString("drugno"));
			good.setName(rs.getString("name"));
			good.setPrice(rs.getFloat("price"));
			good.setType(rs.getString("type"));
			good.setNotes(rs.getString("comment"));
			rs.close();
			cmd.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return good;
	}
	
	//插入新订单
	public static boolean newOrder(int customerID, String customername, int sellid,
			String sell, String ordertime, int principalid, String principal,
			String status, String type, String verifytime, String address){
		System.out.println("开始订货");
		int n=0;
		Connection conn=fatchConnection();
		String sql="insert into [order](customerID,customername,sellid,sell,ordertime," +
				"principalid,principal,status,type,verifytime,address)";
		sql+=" values(?,?,?,?,?,?,?,?,?,?,?)";
		try{
			PreparedStatement pcmd=conn.prepareStatement(sql);
			pcmd.setInt(1, customerID);
			pcmd.setString(2, customername);
			pcmd.setInt(3, sellid);
			pcmd.setString(4, sell);
			pcmd.setString(5, ordertime);
			pcmd.setInt(6, principalid);
			pcmd.setString(7, principal);
			pcmd.setString(8, status);
			pcmd.setString(9, type);
			pcmd.setString(10, verifytime);
			pcmd.setString(11, address);
			n=pcmd.executeUpdate();
			pcmd.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		System.out.println("完成订货");
		return n>0;
	}
	
//	插入新订单明细
	public static boolean newOrderDetail(int orderid,int drugid,int count,String drugname){
		int n=0;
		Connection conn=fatchConnection();
		String sql="insert into orderdetail(orderID,drugID,counts,drugname) values(?,?,?,?)";
		try{
			PreparedStatement pcmd=conn.prepareStatement(sql);
			pcmd.setInt(1, orderid);
			pcmd.setInt(2, drugid);
			pcmd.setInt(3, count);
			pcmd.setString(4, drugname);
			n=pcmd.executeUpdate();
			pcmd.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return n>0;
	}
	
	//根据客户姓名和订单时间查询该 订单号 ，用于订单明细的外键
	public static int queryOrderID(String clientName,String datetime){
		int id=-1;
		Connection conn=fatchConnection();
		String sql="select * from [order] where customername= ? and ordertime=?";
		try{
			PreparedStatement pcmd=conn.prepareStatement(sql);
			pcmd.setString(1, clientName);
			pcmd.setString(2, datetime);
			ResultSet rs=pcmd.executeQuery();
			if(rs.next())
				id=rs.getInt("ID");
			rs.close();
			pcmd.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return id;
	}
	
	//登陆检测，如果登陆成功，返回用户信息
	public static CUser checkLogin(String name,String pswd){
		System.out.println("****name:" + name + " password: " + pswd + "******");
		Connection conn=fatchConnection();
		String sql="select * from sellers where name= ? and password = ?";		
		CUser user=null;
		boolean r=false;
		try{
			PreparedStatement pcmd=conn.prepareStatement(sql);
			pcmd.setString(1, name);
			pcmd.setString(2, pswd);
			ResultSet rs=null;
			rs=pcmd.executeQuery();
			r=rs.next();
			System.out.println("find:" + r);
			if(r){
				user=new CUser();
				user.setId(rs.getInt("id"));
				user.setName(name);
				user.setPswd(pswd);
				user.setSell(rs.getString("sell"));
				user.setSellid(rs.getInt("sellID"));
				user.setPositions(rs.getString("positions"));
				System.out.println("***find user****");
			}				
			rs.close();
			pcmd.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		
		return user;
	}
	
	//根据客户姓名查询客户信息
	public static CClient queryClientByName(String name){
		CClient client=null;
		Connection conn=fatchConnection();
		String sql="select * from customerinfo where name= ? ";
		try{
			PreparedStatement pcmd=conn.prepareStatement(sql);
			pcmd.setString(1, name);
			ResultSet rs=null;
			rs=pcmd.executeQuery();
			if(rs.next()){
				client=new CClient();
				client.setId(rs.getInt("ID"));
				client.setName(rs.getString("name"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		return client;	
	}
	
	//模糊查询订单
	public static ArrayList queryOrders(String sql,String val){
		System.out.println("开始查询订单");
		System.out.println("***sql:" + sql + " ***val:" +val);
		Connection conn=fatchConnection();
		ArrayList list=new ArrayList();
		try{
			PreparedStatement pcmd=conn.prepareStatement(sql);
			if(! "null".equals(val))
				pcmd.setString(1,val);

			ResultSet rs=pcmd.executeQuery();
			while(rs.next()){
				COrder order=new COrder();
				order.setId(rs.getInt("id"));
				order.setCustomerID(rs.getInt("customerID"));
				order.setCustomerName(rs.getString("customerName"));
				order.setPrincipalID(rs.getInt("principalID"));
				order.setPrincipalName(rs.getString("principal"));
				order.setSellID(rs.getInt("sellID"));
				order.setSellName(rs.getString("sell"));
				order.setOrderTime(rs.getString("orderTime"));
				order.setVerifyTime(rs.getString("verifyTime"));
				order.setStatus(rs.getString("status"));
				order.setType(rs.getString("type"));
				order.setAddress(rs.getString("address"));
				list.add(order);
			}
			
			rs.close();
			pcmd.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}		
		System.out.println("完成查询订单");
		return list;
	}
	
	//根据订单号查询该定单对应的明细
	public static ArrayList queryOrderDetails(String orderID){
		//
		Connection conn=fatchConnection();
		ArrayList list=new ArrayList();
		String sql="select * from [orderDetail] as detail,druginfo where " +
				"orderID = ? and detail.drugID = druginfo.id";
		try{
			PreparedStatement pcmd=conn.prepareStatement(sql);
			pcmd.setString(1,orderID);
			ResultSet rs=pcmd.executeQuery();
			while(rs.next()){			
				COrderDetail detail=new COrderDetail();
				detail.setId(rs.getInt("id"));
				detail.setOrderID(rs.getInt("orderID"));
				detail.setDrugID(rs.getInt("drugID"));
				detail.setDrugNo(rs.getString("drugno"));
				detail.setDrugName(rs.getString("drugname"));
				detail.setCount(rs.getInt("counts"));
				detail.setPrice(rs.getFloat("price"));
				list.add(detail);
			}
			rs.close();
			pcmd.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}		
		return list;
	}
	
	//根据订单号查询订单
	public static COrder queryOrder(String orderid){
		System.out.println("****开始查找order*****");
		Connection conn=fatchConnection();
		String sql="select * from [order] where id = ?";
		COrder order=null;
		try{
			PreparedStatement pcmd=conn.prepareStatement(sql);
			pcmd.setString(1,orderid);
			ResultSet rs=pcmd.executeQuery();
			if(rs.next()){
				System.out.println("****找到order*****");
				order=new COrder();
				order.setId(rs.getInt("id"));
				order.setCustomerName(rs.getString("customername"));
				order.setPrincipalName(rs.getString("principal"));
				order.setOrderTime(rs.getString("ordertime"));
				order.setVerifyTime(rs.getString("verifytime"));
				order.setStatus(rs.getString("status"));
				order.setType(rs.getString("type"));
				order.setAddress(rs.getString("address"));
				System.out.println("****order："+order.getCustomerName());
			}
			rs.close();
			pcmd.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return order;
	}
	
	//特殊审核，更新负责人、状态、类型
	public static boolean updateOrderStatus(String orderid,String status,String user){
		Connection conn=fatchConnection();
		String sql="update [order] set type='特殊' , principal= ? , " +
				"status = ? where id = ?";
		int n=0;
		try{
			PreparedStatement pcmd=conn.prepareStatement(sql);
			pcmd.setString(1,user);
			pcmd.setString(2, status);
			pcmd.setString(3, orderid);
			n=pcmd.executeUpdate();
			pcmd.close();
			conn.close();
		}catch(Exception e){
			e.printStackTrace();
		}
		return n>0;
	}
}