dbconnector.java

一个简单的网上订书系统 java的课程设计

	static ArrayList<Book> recommendedBooks() {
		String query = "select b.* "
				+ "from book b join invoice i on i.BID = b.BID "
				+ "where b.BID in ( select b.bid "
				+ "from book b , publisher p where b.PID = p.PID "
				+ "order by b.publicationDate desc) "
				+ "order by sum(i.qty) desc;";

		ArrayList<Book> books = new ArrayList<Book>();
		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			ResultSet result = stmt.executeQuery(query);
			ResultSetMetaData meta = result.getMetaData();
			String[] args = new String[meta.getColumnCount()];
			int j = 0, i = 0;
			while (result.next() && j < 3) {
				for (i = 1; i <= meta.getColumnCount(); i++)
					args[i - 1] = result.getString(i);
				books.add(new Book(args));
				j++;
			}
			stmt.close();
			con.close();
		} catch (SQLException se) {
			System.err.println("SQLException from top10ByDate():\n "
					+ se.getMessage());
		}
		return books;
	}

	static ArrayList<CustomerItem> cusInf(String name) {

		ArrayList<CustomerItem> customer = new ArrayList<CustomerItem>();

		if (Checker.checkNull(name) == Checker.nullStr) {
			System.err
					.println("Customer information retrievement failing:+\n Customer name is null");
			return customer;
		}

		String query = " select CID,Cname,RealName,MailingAddr, "
				+ " creditCardNum,ExpirationDate,EMailAddr "
				+ " from customer " + " where Cname =  '" + name.trim() + "' ;";

		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			ResultSet result = stmt.executeQuery(query);
			ResultSetMetaData meta = result.getMetaData();
			String[] args = new String[meta.getColumnCount()];
			if (result.wasNull())
				return null;
			while (result.next()) {
				for (int i = 1; i <= meta.getColumnCount(); i++)
					args[i - 1] = result.getString(i);
				customer.add(new CustomerItem(args));
			}
			stmt.close();
			con.close();
		} catch (SQLException se) {
			System.err
					.println("SQLException from Customer information retrieving :\n "
							+ se.getMessage());
		}
		return customer;

	}

	static ArrayList<OrderItem> getHisOrder(String cname) {

		ArrayList<OrderItem> orders = new ArrayList<OrderItem>();

		if (Checker.checkNull(cname) == Checker.nullStr) {
			System.err
					.println("History Orders retrievement failing:+\n Customer name is null");
			return orders;
		}

		String query = " select o.Ono, f.shipmentDate, b.title, i.qty, b.price, o.dateArrived "
				+ " from book b, fulfilledOrder f, invoice i, orders o ,customer c "
				+ " where c.cname ='"
				+ cname
				+ "' and o.CID= c.cid"
				+ " and o.Ono=f.Ono "
				+ " and i.ono=o.ono "
				+ " and b.bid=i.bid ;";
		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			ResultSet result = stmt.executeQuery(query);
			ResultSetMetaData meta = result.getMetaData();
			String[] args = new String[meta.getColumnCount()];
			if (result.wasNull())
				return null;
			while (result.next()) {
				for (int i = 1; i <= meta.getColumnCount(); i++)
					args[i - 1] = result.getString(i);
				orders.add(new OrderItem(args));
			}
			stmt.close();
			con.close();
		} catch (SQLException se) {
			System.err.println("SQLException from getHisOrder():\n "
					+ se.getMessage());
		}
		return orders;

	}

	static ArrayList<OrderItem> getPlaOrder(String cname) {

		ArrayList<OrderItem> orders = new ArrayList<OrderItem>();
		if (Checker.checkNull(cname) == Checker.nullStr) {
			System.err
					.println("Plan Orders retrievement failing:+\n Customer name is null");
			return orders;
		}

		String query = " select o.Ono, f.shipmentDate, b.title, i.qty, b.price, o.dateArrived "
				+ " from book b,fulfilledOrder f,invoice i,orders o "
				+ " where o.cid= '"
				+ cname
				+ "' "
				+ " and o.ono=i.ono "
				+ " and b.bid=i.bid "
				+ " and o.Ono not in (select Ono from fulfilledOrder ) ;";

		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			ResultSet result = stmt.executeQuery(query);
			ResultSetMetaData meta = result.getMetaData();
			String[] args = new String[meta.getColumnCount()];
			if (result.wasNull())
				return null;
			while (result.next()) {
				for (int i = 1; i <= meta.getColumnCount(); i++)
					args[i - 1] = result.getString(i);
				orders.add(new OrderItem(args));
			}
			stmt.close();
			con.close();
		} catch (SQLException se) {
			System.err.println("SQLException from getPlaOrder():\n "
					+ se.getMessage());
		}
		return orders;

	}

	static ArrayList<Book> getBookByType(String type) {
		ArrayList<Book> books = new ArrayList<Book>();

		if (Checker.checkNull(type) == Checker.nullStr) {
			System.err
					.println("get Book By Type retrievement failing:+\n Book's type is null");
			return books;
		}

		String query = "SELECT b.*" + " FROM book b,subcategory s"
				+ " where b.scno = s.scno and s.scname ='" + type.trim() + "';";

		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			ResultSet result = stmt.executeQuery(query);
			ResultSetMetaData meta = result.getMetaData();
			String[] args = new String[meta.getColumnCount()];
			while (result.next()) {
				for (int i = 1; i <= meta.getColumnCount(); i++)
					args[i - 1] = result.getString(i);
				books.add(new Book(args));
			}
			stmt.close();
			con.close();
		} catch (SQLException se) {
			System.err.println("SQLException from searchBooks: "
					+ se.getMessage());
		}
		if (books.size() == 0)
			System.err
					.println("Query Fail: No Book of this type in the database.");
		return books;
	}

	static boolean check(String Cname, String Cpassword) {
		if (Checker.checkNull(Cname) == Checker.nullStr) {
			System.err
					.println("Customer Information Checking fail:+\n Customer name is null");
			return false;
		}

		if (Checker.checkNull(Cpassword) == Checker.nullStr) {
			System.err
					.println("Customer Information Checking fail:+\n Customer password is null");
			return false;
		}

		String query = "SELECT * FROM customer c " + "where cname = '"
				+ Cname.trim() + "' and cpassword = '" + Cpassword.trim() + "'";

		boolean r = false;
		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			ResultSet result = stmt.executeQuery(query);
			r = result.next();
			stmt.close();
			con.close();
		} catch (SQLException se) {
			System.err
					.println("SQLException from Customer Information Checking: "
							+ se.getMessage());
		}
		return r;
	}

	static boolean updateBook(Book book) {
		return (BookManager.update(book.getBID(), book.getDescription(), book
				.getPrice()) != -1);
	}

	static boolean addBook(Book book) {
		return (BookManager.insert(book.getBID(), book.getAuthor(), book
				.getTitle(), book.getIsbn(), book.getPID(), book.getEdition(),
				book.getPublicationDate().toString(), book.getPrice(), book
						.getDescription(), book.getSCno()) != -1);
	}

	static boolean isAddable(Book book) {
		if (book.getBID() == null || book.getBID() == "") {
			return false;
		}

		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			ResultSet result = stmt
					.executeQuery("select * from book where bid='"
							+ book.getBID() + "';");

			if (!result.next()) {
				stmt.close();
				con.close();
				return true;
			} else {
				stmt.close();
				con.close();
				return false;
			}

		} catch (SQLException se) {
			System.err.println("SQLException from isAddable: "
					+ se.getMessage());
			return false;
		}

	}

	static ArrayList<Orders> getUnhandleOrders() {
		ArrayList<Orders> orders = new ArrayList<Orders>();
		String query = "";
		query += " select o.Ono,o.cid,o.shipment,o.shippingdate,o.datearrived "
				+ " from book b,invoice i,orders o " + " where b.bid=i.bid "
				+ " and o.Ono=i.Ono "
				+ " and o.Ono not in (select Ono from fulfilledorder) "
				+ " order by datearrived asc ;";
		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			ResultSet result = stmt.executeQuery(query);
			ResultSetMetaData meta = result.getMetaData();
			String[] args = new String[meta.getColumnCount()];
			if (result.wasNull())
				return null;
			int i;
			while (result.next()) {
				for (i = 1; i <= meta.getColumnCount(); i++) {
					args[i - 1] = result.getString(i);
				}

				orders.add(new Orders(args));

			}
			stmt.close();
			con.close();
		} catch (SQLException se) {
			System.err.println("SQLException from getUnhandleOrder():\n "
					+ se.getMessage());
		}
		return orders;

	}

	static ArrayList<OrderItem> getOrderItemByOrderID(String orderID) {
		ArrayList<OrderItem> orders = new ArrayList<OrderItem>();
		String query = "";
		query += " select o.Ono,datearrived,title,qty,price "
				+ " from book b,invoice i,orders o " + " where b.bid=i.bid "
				+ " and o.Ono=i.Ono " + " and o.Ono ='" + orderID + "' "
				+ " order by datearrived asc ;";

		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			ResultSet result = stmt.executeQuery(query);
			ResultSetMetaData meta = result.getMetaData();
			String[] args = new String[meta.getColumnCount()];
			if (result.wasNull())
				return null;
			while (result.next()) {
				for (int i = 1; i <= meta.getColumnCount(); i++)
					args[i - 1] = result.getString(i);
				orders.add(new OrderItem(args));
			}
			stmt.close();
			con.close();
		} catch (SQLException se) {
			System.err.println("SQLException from getOrderItemByOrderID():\n "
					+ se.getMessage());
		}
		return orders;

	}

	static CustomerItem getCustomerItemByOrderID(String orderID) {
		CustomerItem customer = null;
		String query = "";
		query += " select cid,cname,realname,mailingaddr,creditcardnum,expirationdate,emailaddr "
				+ " from customer "
				+ " where order.ono='"
				+ orderID
				+ " and order.cid=customer.cid ;";

		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			ResultSet result = stmt.executeQuery(query);
			ResultSetMetaData meta = result.getMetaData();
			String[] args = new String[meta.getColumnCount()];
			if (result.wasNull())
				return null;
			while (result.next()) {
				for (int i = 1; i <= meta.getColumnCount(); i++)
					args[i - 1] = result.getString(i);
				customer = new CustomerItem(args);
			}
			stmt.close();
			con.close();
		} catch (SQLException se) {
			System.err.println("SQLException from getOrderItemByOrderID():\n "
					+ se.getMessage());
		}
		return customer;

	}

	static boolean handleOrder(String orderID) {
		String query ="select * from orders where ono='" + orderID + "';";

		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			ResultSet result = stmt.executeQuery(query);
			if (result.wasNull()) {
				return false;
			}
			stmt.close();
			con.close();
		} catch (SQLException se) {
			System.err.println("SQLException from handleOrder():\n "
					+ se.getMessage());
		}

		String TNO = FulfilledOrderManager.getNewFulfilledOrderID();// tracking NO
		query = "";
		query += "insert into fulfilledorder values ('" + TNO + "','" + orderID
				+ "'," + "current_date,null);";
		return (execute(query) != -1);
	}

	static ArrayList<Orders> getHandledOrders() {
		ArrayList<Orders> orders = new ArrayList<Orders>();
		String query = " select o.Ono,o.cid,o.shipment,o.shippingdate,o.datearrived "
				+ " from book b,invoice i,orders o "
				+ " where b.bid=i.bid "
				+ " and o.Ono=i.Ono "
				+ " and o.Ono in (select Ono from fulfilledorder) "
				+ " order by datearrived asc ;";
		/*
		 * select o.Ono,datearrived,title,qty,price from book b,invoice i,orders
		 * o where b.bid=i.bid and o.Ono=i.Ono and o.Ono not in (select Ono from
		 * fulfilledorder);
		 */
		try {
			Connection con = getConnection();
			Statement stmt = con.createStatement();
			stmt.execute("USE Bookstore;");
			ResultSet result = stmt.executeQuery(query);
			ResultSetMetaData meta = result.getMetaData();
			String[] args = new String[meta.getColumnCount()];
			if (result.wasNull())
				return null;
			while (result.next()) {
				for (int i = 1; i <= meta.getColumnCount(); i++)
					args[i - 1] = result.getString(i);
				orders.add(new Orders(args));
			}
			stmt.close();
			con.close();
		} catch (SQLException se) {
			System.err.println("SQLException from getUnhandleOrder():\n "
					+ se.getMessage());
		}
		return orders;

	}

	public static void main(String[] args) {
		getSingletonInstance();
	}
}