dbutil.java

全套网上书店系统代码

				if(rs.getString(1).equals("admin"))
					result = "admin";
				else
					result = "user";
			}
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
		return result;
	}
	
	/*
	 * 图书管理方面代码 
	 */
	
	public void addbook(String ISBN, String bookname, String bookpress, String category, double price,
			String pic, Date publishdate) {
		try {
			PreparedStatement ps = conn
				.prepareStatement("insert into book (IBSN, bookname, bookpress, category, " +
					"price, pic, publishdate) values (?,?,?,?,?,?,?)");
			ps.setString(1, ISBN);
			ps.setString(2, bookname);
			ps.setString(3, bookpress);
			ps.setString(4, category);
			ps.setDouble(5, price);
			ps.setString(6, pic);
			ps.setDate(7, publishdate);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
	}

	public int getAllBooks(){
		int sum = 0;
		try{
			String sql = "SELECT * FROM book";
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				sum++;
			}
			this.ISBN = new String[sum];
			this.bookname = new String[sum];
			this.bookpress = new String[sum];
			this.category = new String[sum];
			this.price = new double[sum];
			this.pic = new String[sum];
			this.publishdate = new Date[sum];
			rs.beforeFirst();
			int i = 0;
			while(rs.next()){
				ISBN[i] = rs.getString(2);
				bookname[i] = rs.getString(3);
				bookpress[i] = rs.getString(4);
				category[i] = rs.getString(5);
				price[i] = rs.getDouble(6);
				pic[i] = rs.getString(7);
				publishdate[i] = rs.getDate(8);
				i++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
		return sum;
	}
	
	public String getbookDetail(String ISBN){
		String result = "";
		try{
			String sql = "select * from book where IBSN=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, ISBN);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				result += (rs.getString(2) + "\n");
				result += (rs.getString(3) + "\n");
				if(!rs.getString(4).equals(""))
					result += (rs.getString(4) + "\n");
				else
					result += (" " + "\n");
				result += (rs.getString(5) + "\n");
				result += (rs.getDouble(6) + "\n");
				if(!rs.getString(7).equals(""))
					result += (rs.getString(7) + "\n");
				else
					result += (" " + "\n");
				Date date = rs.getDate(8);
				int year = date.getYear() + 1900;
				int month = date.getMonth() + 1;
				int day = date.getDate();
				result += (year + "\n" + month + "\n" + day);
				//result += (rs.getDate(8));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
		return result;
	}
	
	public String getBookname(String ISBN){
		try{
			PreparedStatement ps = conn.prepareStatement("select bookname from book where IBSN=?");
			ps.setString(1, ISBN);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				return rs.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
		return null;
	}
	
	public void updateBook(String ISBN, String bookname, String bookpress, String category, 
			double price, String pic, java.util.Date publishdate){
		try{
			String sql = "update book set bookname=?, bookpress=?, category=?, price=?, pic=?, publishdate=? where " +
					"IBSN=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, bookname);
			ps.setString(2, bookpress);
			ps.setString(3, category);
			ps.setDouble(4, price);
			ps.setString(5, pic);
			ps.setDate(6, (Date)publishdate);
			ps.setString(7, ISBN);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
	}
	
	public void searchBook(String category){
		int sum = 0;
		try{
			String sql = "SELECT * FROM book where category=?";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, category);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				sum++;
			}
			this.ISBN = new String[sum];
			this.bookname = new String[sum];
			this.bookpress = new String[sum];
			this.category = new String[sum];
			this.price = new double[sum];
			this.pic = new String[sum];
			this.publishdate = new Date[sum];
			rs.beforeFirst();
			int i = 0;
			while(rs.next()){
				ISBN[i] = rs.getString(2);
				bookname[i] = rs.getString(3);
				bookpress[i] = rs.getString(4);
				this.category[i] = rs.getString(5);
				price[i] = rs.getDouble(6);
				pic[i] = rs.getString(7);
				publishdate[i] = rs.getDate(8);
				i++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
	}
	
	public void likesearch(String searchString){
		int sum = 0;
		try{
			String sql = "SELECT * FROM book where bookname like ?";
			PreparedStatement ps = conn.prepareStatement(sql);
			String search = "%" + searchString + "%"; 
			ps.setString(1, search);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				sum++;
			}
			this.ISBN = new String[sum];
			this.bookname = new String[sum];
			this.bookpress = new String[sum];
			this.category = new String[sum];
			this.price = new double[sum];
			this.pic = new String[sum];
			this.publishdate = new Date[sum];
			rs.beforeFirst();
			int i = 0;
			while(rs.next()){
				ISBN[i] = rs.getString(2);
				bookname[i] = rs.getString(3);
				bookpress[i] = rs.getString(4);
				this.category[i] = rs.getString(5);
				price[i] = rs.getDouble(6);
				pic[i] = rs.getString(7);
				publishdate[i] = rs.getDate(8);
				i++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
	}
	
	/*
	 * 购物车管理方面
	 */
	
	public void addShoppingCart(String cartUserid, String cartISBN, int quantity,
			String status, Date ordertime){
		try {
			PreparedStatement ps = conn
				.prepareStatement("insert into shoppingcart (userid, ISBN, quantity, status, " +
					"ordertime) values (?,?,?,?,?)");
			ps.setString(1, cartUserid);
			ps.setString(2, cartISBN);
			ps.setInt(3, quantity);
			ps.setString(4, status);
			ps.setDate(5, ordertime);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
	}
	
	public void getFinishedCart(String userid){
		int sum = 0;
		try{
			String sql = "SELECT * FROM shoppingcart where status=? and userid=? order by ordertime ASC";
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, "finished");
			ps.setString(2, userid);
			ResultSet rs = ps.executeQuery();
			while(rs.next()){
				sum++;
			}
			this.cartUserid = new String[sum];
			this.cartISBN = new String[sum];
			this.quantity = new int[sum];
			this.status = new String[sum];
			this.ordertime = new Date[sum];
			rs.beforeFirst();
			int i = 0;
			while(rs.next()){
				cartUserid[i] = rs.getString(2);
				cartISBN[i] = rs.getString(3);
				quantity[i] = rs.getInt(4);
				status[i] = rs.getString(5);
				ordertime[i] = rs.getDate(6);
				i++;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (NullPointerException e) {
			throw e;
		}
	}
	
	public static void main(String args[]) {
		
		System.out.print(DBUtil.getInstance().connect2DB());
		String userid = DBUtil.getInstance().getUserID();
		DBUtil dbutil = DBUtil.getInstance();
		System.out.println(dbutil.getUseridFromUsername("flashstar"));
		java.util.Date date = new java.util.Date();
		System.out.println(date.getHours());
		
		/*
		String detail = dbutil.getUserDetail("PJ3SS0001");
		dbutil.likesearch("程序设计");
		for(int i = 0; i < dbutil.ISBN.length; i++){
			System.out.println(dbutil.ISBN[i]);
			System.out.println(dbutil.bookname[i]);
			System.out.println(dbutil.bookpress[i]);
			System.out.println(dbutil.category[i]);
			System.out.println(dbutil.price[i]);
			System.out.println(dbutil.pic[i]);
			System.out.println(dbutil.publishdate[i]);
		}
		
		StringTokenizer token = new StringTokenizer(detail, "\n");
		while(token.hasMoreTokens()){
			System.out.println(token.nextToken() + "\n");
		}
		dbutil.updateBook("000-001", "操作系统与网络应用(WindowsXP)", "清华大学出版社", "Computer", 50.0, "operate1.jpg", new Date(101, 1, 11));
		String detail = dbutil.getbookDetail("000-001");
		StringTokenizer token = new StringTokenizer(detail, ",");
		while(token.hasMoreTokens()){
			System.out.println(token.nextToken());
		}
		//System.out.println(new java.util.Date(2001, 3, 17).getDate());
		*/
		//DBUtil.getInstance().addbook("000-001", "操作系统与网络应用(Windows XP)", "清华大学出版社", "Computer", 45, "operate1.jpg", new Date(101, 1, 11));
		//dbutil.updateUser("GodBless", "GodBlessMe", "mygod", 30, "[e-mail]", "上海市宝山区宝山6村15号404室");
		//String user = dbutil.getUserMessage("GodBlessMe");
		//StringTokenizer token = new StringTokenizer(user, " ");
		//while(token.hasMoreTokens()){
			//System.out.println(token.nextToken());
		//}
		//DBUtil.getInstance().addUser(userid, "FlashStar1", "620615", 'M', 20, "admin", "[e-mail]", "abccc");
	}
}