dboperator.java

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

package kefu.javabeans;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class DBOperator {
	private Connection conn;

	private PreparedStatement pstmt;

//	public List excuteSQL(String sqlStr) {
//		try {
//			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
//			conn = DriverManager
//					.getConnection(
//							"jdbc:microsoft:sqlserver://10.10.8.184:1433;DataBaseName=JHYY",
//							"jhyy", "jhyy");
//			pstmt = conn.prepareStatement(sqlStr);
//			ResultSet rs = pstmt.executeQuery();
//			List ls = new ArrayList();
//			while (rs.next()) {
//				Send s = new Send();
//				s.setId(rs.getLong("ID"));
//				s.setArrivetime(rs.getDate("arrivetime"));
//				s.setConsignee(rs.getString("consignee"));
//				s.setHauler(rs.getString("hauler"));
//				s.setHaulerId(rs.getLong("haulerID"));
//				s.setOrderId(rs.getLong("orderID"));
//				s.setPrincipal(rs.getString("principal"));
//				s.setPrincipalId(rs.getLong("principalID"));
//				s.setSendtime(rs.getDate("sendtime"));
//				s.setStatus(rs.getString("status"));
//				s.setWarehouse(rs.getString("warehouse"));
//				s.setWarehouseId(rs.getLong("warehouseID"));
//				ls.add(s);
//			}
//			return ls;
//		} catch (ClassNotFoundException e) {
//			e.printStackTrace();
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		return null;
//	}

	public boolean confirm(String sqlStr, String id) {
		try {
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
			conn = DriverManager
					.getConnection(
							"jdbc:microsoft:sqlserver://10.10.8.184:1433;DataBaseName=JHYY",
							"jhyy", "jhyy");
			pstmt = conn.prepareStatement(sqlStr);
			pstmt.setString(1, id);
			if (pstmt.execute()) {
				System.out.println("确认成功");
				return true;
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	public boolean withDrawRequest(String sqlStr, Map map) {
		try {
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
			conn = DriverManager
					.getConnection(
							"jdbc:microsoft:sqlserver://10.10.8.184:1433;DataBaseName=JHYY",
							"jhyy", "jhyy");
//			conn.setAutoCommit(false);
			String orderID = (String) map.get("orderID");
			String[] drugName = (String[]) map.get("drugName");
			String[] counts = (String[]) map.get("counts");
			// String principal=(String)map.get("principal");
			String reason = (String) map.get("reason");

			java.util.Date date = new java.util.Date();

			pstmt = conn.prepareStatement(Sql.withDrawRequestStr);
			pstmt.setLong(1, Long.parseLong(orderID));
			pstmt.setDate(2, new java.sql.Date(date.getTime()));
			pstmt.setString(3, "待审查");
			pstmt.setString(4, reason);
			pstmt.execute();
			PreparedStatement pstmt2 = conn
					.prepareStatement("select ID from withdraw where withdrawtime=?");
			pstmt2.setDate(1, new java.sql.Date(date.getTime()));
			ResultSet rs = pstmt2.executeQuery();
			
			
			rs.next();
			long withID = rs.getLong("ID");
			System.out.println(withID);

			for (int i = 0; i < drugName.length; i++) {
				PreparedStatement pstmt3 = conn
						.prepareStatement(Sql.withDetailRequestStr);
				PreparedStatement pstmt4 = conn
						.prepareStatement("select ID from druginfo where name=?");
				pstmt4.setString(1, drugName[i]);
//				System.out.println(drugName[i]);
				rs = pstmt4.executeQuery();
				rs.next();
				long drugID = rs.getLong("ID");
				pstmt3.setLong(1, withID);
				pstmt3.setLong(2, drugID);
				pstmt3.setString(3, drugName[i]);
				pstmt3.setLong(4, Long.parseLong(counts[i]));
				pstmt3.execute();
			}
			conn.commit();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
			return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			return false;
		}
		return true;
	}

	public List orderDetail(String sqlStr, String orderId) {
		try {
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
			conn = DriverManager
					.getConnection(
							"jdbc:microsoft:sqlserver://10.10.8.184:1433;DataBaseName=JHYY",
							"jhyy", "jhyy");
			pstmt = conn.prepareStatement(sqlStr);
			pstmt.setString(1, orderId);
			ResultSet rs = pstmt.executeQuery();
			List ls = new ArrayList();
			while (rs.next()) {
				Orderdetail odt = new Orderdetail();
				odt.setCounts(rs.getLong("counts"));
				odt.setDrugId(rs.getLong("drugID"));
				odt.setDrugname(rs.getString("drugname"));
				odt.setId(rs.getLong("ID"));
				odt.setOrderId(rs.getLong("orderID"));
				ls.add(odt);
			}
			return ls;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public List order(String sqlStr, String name) {
		try {
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
			conn = DriverManager
					.getConnection(
							"jdbc:microsoft:sqlserver://10.10.8.184:1433;DataBaseName=JHYY",
							"jhyy", "jhyy");
			List ls=new ArrayList();
			pstmt = conn.prepareStatement(sqlStr);
			pstmt.setString(1, name);
			ResultSet rs = pstmt.executeQuery();
			
			while(rs.next()){
				Order order = new Order();
				order.setAddress(rs.getString("address"));
				order.setCustomerId(rs.getLong("customerID"));
				order.setCustomername(rs.getString("customername"));
				order.setId(rs.getLong("ID"));
				order.setOrdertime(rs.getDate("ordertime"));
				order.setPrincipal(rs.getString("principal"));
				order.setPrincipalId(rs.getLong("principalID"));
				order.setSell(rs.getString("sell"));
				order.setSellId(rs.getLong("sellID"));
				order.setStatus(rs.getString("status"));
				order.setType(rs.getString("type"));
				order.setVerifytime(rs.getDate("verifytime"));
				ls.add(order);
			}
			
			return ls;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	public List sendList(String sqlStr,String customername,String page) {
		try {
			Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
			conn = DriverManager
					.getConnection(
							"jdbc:microsoft:sqlserver://10.10.8.184:1433;DataBaseName=JHYY",
							"jhyy", "jhyy");
			pstmt = conn.prepareStatement(sqlStr);
			pstmt.setString(1,customername);
			
			ResultSet rs = pstmt.executeQuery();
			List ls = new ArrayList();
			while (rs.next()) {
				Send s = new Send();
				s.setId(rs.getLong("ID"));
				s.setArrivetime(rs.getDate("arrivetime"));
				s.setConsignee(rs.getString("consignee"));
				s.setHauler(rs.getString("hauler"));
				s.setHaulerId(rs.getLong("haulerID"));
				s.setOrderId(rs.getLong("orderID"));
				s.setPrincipal(rs.getString("principal"));
				s.setPrincipalId(rs.getLong("principalID"));
				s.setSendtime(rs.getDate("sendtime"));
				s.setStatus(rs.getString("status"));
				s.setWarehouse(rs.getString("warehouse"));
				s.setWarehouseId(rs.getLong("warehouseID"));
				ls.add(s);
			}
			return ls;
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
}