In Chapter 7 we developed a MongoDB database model for an e-commerce application. Now we’ll write the necessary queries for using the database and see how to perform common tasks for an e-commerce platform, including:

• Display categories of products.
• Display promotional products.
• Display products from a category (with pagination).
• Search for a product by name (or by the words in the name).
• Find a customer (for login, editing the profile, saving orders, and so on).
• Save an order for synchronizing the shopping cart with the database.
• Check the inventory for a certain product and quantity.
• Restore the quantity when products are removed from shopping cart.

Each of these tasks will be accomplished in a Hibernate Search/Apache Lucene query (since JP-QL is insufficiently developed, we need to use the full-text search engine provided by Apache). The Hibernate Search queries will be written in JPA style.

For testing the database, I developed an e-commerce web site inspired by the official e-shop of the tennis player Rafael Nadal (www.rafaelnadal-shop.com/en). The web site is based on:

• Java EE 6 (EJB 3.0, JSF 2.0)
• Hibernate OGM 4.0.0 Beta2
• MongoDB 2.2.2
• MongoDB Java Driver 2.8.0
• Hibernate Search 4.2.0 Beta 1
• Apache Lucene 3.6.0
• PrimeFaces 3.4.2

Don’t worry if you’re not familiar with JSF or PrimeFaces. You can implement the same functionality without them, using other approaches such as JSP and servlets. Moreover, you can drop EJB and implement the business layer as you wish. You can also use the Hibernate Native API instead of JPA. These technologies are not essential and, as long as you understand the e-commerce database model and the queries we’ll discuss, you can glue everything into an e-commerce application using the technologies you prefer.

You’ll find the complete source code for the application, named RafaEShop, in the Apress repository. The application was developed as a NetBeans 7.2.1 project and was tested under GlassFish v3 AS. Figure 8-1 shows the interaction of the classes.

For localhost testing purposes, follow these steps (assuming the application is deployed and the MongoDB server is running):

1. Ensure you don’t have a database named eshop_db in MongoDB.
2. Access the page http://localhost:8080/RafaEShop/faces/db.xhtml, as shown in Figure 8-2. (Obviously, you need to adjust the address and port to reflect your application server).

   

   Figure 8-2. The user interface for populating the eshop_db database

3. Press, ONLY ONCE, the button labeled “Populate Rafael Nadal E-Shop MongoDB Database;” pressing the button more than once will cause errors.
4. Navigate to the web site by pressing the button labeled, “Go To Website.” This button navigates to the web site start page.

Now you should see something like what’s shown in Figure 8-3.

If you need to restore the database (for whatever reason), follow these steps:

1. Drop the eshop_db database. You can do this from the MongoDB shell, like this:

   mongo eshop_db
   db.dropDatabase()

2. Navigate to the D root folder and delete the eshop folder (this is where Lucene indexes data).
3. Repeat the steps 1-4, from above.

Now let’s “dissect” Figure 8-2 in terms of Lucene queries.

Display the Categories of Products

The first query will extract the category names and ids from the categories_c collection (the Categories entity). The names are visible to users and the ids help identify a category in order to retrieve its products; we display the categories sorted by name. You can find this code in EshopBean.java, shown in Listing 8-1.

Listing 8-1.  EshopBean.java

package eshop.beans;
...
public class EShopBean {
...
public List<String> extractCategories() {

   FullTextEntityManager fullTextEntityManager =
                                        org.hibernate.search.jpa.Search.getFullTextEntityManager(em);

   QueryBuilder queryBuilder = fullTextEntityManager.getSearchFactory().buildQueryBuilder().
                                        forEntity(Categories.class).get();
          
        org.apache.lucene.search.Query query = queryBuilder.all().createQuery();

        FullTextQuery fullTextQuery = fullTextEntityManager
                                        .createFullTextQuery(query, Categories.class);
        fullTextQuery.setProjection(FullTextQuery.ID, "category");
        Sort sort = new Sort(new SortField("category", SortField.STRING));
        fullTextQuery.setSort(sort);

        fullTextQuery.initializeObjectsWith(ObjectLookupMethod.SKIP,
                                        DatabaseRetrievalMethod.FIND_BY_ID);

        List<String> results = fullTextQuery.getResultList();

        return results;
    }
}

The query is pretty simple. We extract all Categories instances (sorted by category names) by projecting the category names and ids. In Figure 8-4, you can see how categories are listed in the browser.

Display the Promotional Products

In addition to the category names, the first page of our web site contains a list of the promotional products; these products can belong to different categories. This is a common approach on many e-commerce web sites, but you can also display the newest products or the bestsellers. In this case, it’s easy to recognize the promotional products by checking the MongoDB field product_old_price (old_price in Products entity) of the documents in the products_c collection (the Products entity). All products with an old price bigger than 0 are assumed to be promotional products. Therefore, the query looks like the code in Listing 8-2.

Listing 8-2.  Query for Displaying Promotional Products

package eshop.beans;
...
public class EShopBean {
...
public List<Products> extractPromotionalProducts() {

        FullTextEntityManager fullTextEntityManager = org.hibernate.search.jpa.Search.getFullTextEntityManager(em);

        org.apache.lucene.search.Query query = NumericRangeQuery
                                          .newDoubleRange("old_price", 0.0d, 1000d, false, true);
        FullTextQuery fullTextQuery = fullTextEntityManager
                                          .createFullTextQuery(query, Products.class);
        Sort sort = new Sort(new SortField("price", SortField.DOUBLE));
        fullTextQuery.setSort(sort);

        fullTextQuery.initializeObjectsWith(ObjectLookupMethod.SKIP,                                           DatabaseRetrievalMethod.FIND_BY_ID);

        List results = fullTextQuery.getResultList();

        return results;
    }
}

Notice that the promotional products are displayed in ascending order by price. Obviously, you can present the products in a web browser in a number of different ways. In Figure 8-5, you can see our custom design. Notice that promotional products have the old price to the right of the current price.

As you can see, we haven’t yet provided pagination for the promotional products. Next we’ll look at how to provide pagination when displaying the products from a selected category, and you can adapt the same mechansim here.

Display the Products From a Category

When a user selects a category, we need to provide a list of the products grouped under that category. Since we have the category id, it’s very easy to extract the products, as shown in Listing 8-3.

Listing 8-3.  Extracting the Products

package eshop.beans;
...
public class EShopBean {
...
public Map<Integer, List<Products>> extractProducts(String id, int page) {
       
        FullTextEntityManager fullTextEntityManager =
                                     org.hibernate.search.jpa.Search.getFullTextEntityManager(em);

        QueryBuilder queryBuilder = fullTextEntityManager.getSearchFactory().
                                             buildQueryBuilder().forEntity(Products.class).get();
        org.apache.lucene.search.Query query = queryBuilder.keyword().
                                             onField("category.id").matching(id).createQuery();

        FullTextQuery fullTextQuery = fullTextEntityManager
                                             .createFullTextQuery(query, Products.class);
        Sort sort = new Sort(new SortField("price", SortField.DOUBLE));
        fullTextQuery.setSort(sort);

        fullTextQuery.initializeObjectsWith(ObjectLookupMethod.SKIP,
                                             DatabaseRetrievalMethod.FIND_BY_ID);

        fullTextQuery.setFirstResult(page * 3);
        fullTextQuery.setMaxResults(3);
        List<Products> results = fullTextQuery.getResultList();

        Map<Integer, List<Products>> results_and_total = new HashMap<Integer, List<Products>>();
        results_and_total.put(fullTextQuery.getResultSize(), results);

        return results_and_total;
    }
}

Returning type Map<Integer, List<Products>> may look strange, but it’s actually very simple to understand. Since a category may contain many products, we need to implement the pagination mechanism and load from the database only one page per query (the page size is set to three products). For calculating the number of pages, we need to know the number of products in the selected category, even if we extract only some of them. Lucene is able to return the total number of products even if you query only for some. The total number of products is stored as the key of the returned map, while the products list is the value of this map. Here’s what the code for this looks like:

• fullTextQuery.setFirstResult(int n); Sets the position of the first result of retrieving the data or, in other words, it skips the first "n"  elements from the result set. fullTextQuery.setMaxResults(int n);, which is used to set the number of results to retrieve starting from the first result.
• fullTextQuery.getResultSize(); Returns the number of all results that match the query, even if we retrieve only a subset of results.

In Figure 8-6, for example, you can see the last product from the Racquets category. Under the products list, you can see the navigation link to the previous page and the pagination status of type current_page of total_pages:

Search for a Product by Name

One task an e-commerce web site has to perform is providing an easy way to search for a specific product or a number of products without navigating through categories and pages of products. Usually, a user knows the product name or has an idea of what he’s looking for. For example, he may know that the product is named “Babolat AeroPro Drive GT Racquet,” or he may know only that he’s looking a “racquet.” The hard part is when the user knows only keywords that should appear in the name of the products(s).

Many query engines handle such problems with custom queries, but Lucene was especially designed to search in text, so searching for keywords in text is a piece of cake. The easiest way to accomplish this kind of search is to activate the default analyzer for the product field in the Products entity (set analyze = Analyze.YES). For complex searching, you can write your own analyzers, or mix analyzers, and so on. And you can use wildcards if you need more fine-grained control of keywords.

The code in Listing 8-4 locates a product (or group of products) that contains a keyword (or a list of keywords separated by spaces) within the name. (I arbitrarily chose not to sort the results.)

Listing 8-4.  Locating a Product by Keyword

package eshop.beans;
...
public class EShopBean {
...
public List<Products> searchProducts(String search) {

        FullTextEntityManager fullTextEntityManager =
                                       org.hibernate.search.jpa.Search.getFullTextEntityManager(em);

        QueryBuilder queryBuilder = fullTextEntityManager.getSearchFactory().
                                             buildQueryBuilder().forEntity(Products.class).get();
        org.apache.lucene.search.Query query = queryBuilder.keyword().
                                             onField("product").matching(search).createQuery();

        FullTextQuery fullTextQuery = fullTextEntityManager
                                             .createFullTextQuery(query, Products.class);

        fullTextQuery.initializeObjectsWith(ObjectLookupMethod.SKIP,
                                             DatabaseRetrievalMethod.FIND_BY_ID);
        fullTextQuery.setMaxResults(3);

        List results = fullTextQuery.getResultList();

        return results;
    }
}

A limitation of our search is that it returns at most three results (the first three). If you want to return more, or even all, you will need to implement the pagination mechanism to not return too much data in a single query.

For example, I tested the search for the keyword “t-shirts” and obtained the results shown in Figure 8-7.

Find a Customer By E-mail And Password

Each customer must have a unique account that contains his name, surname, e-mail address, password, and so on in the Customers entity (the customers_c collection). When the customer logs in to the web site, views or modifies his profile, places an order, or takes other actions, we need to be able to extract the customer details from the database. The query in Listing 8-5 locates a customer in the customers_c collection by the e-mail address and password.

Listing 8-5.  Locating a Customer

package eshop.beans;
...
public class EShopBean {
...
public Customers extractCustomer(String email, String password) {

        FullTextEntityManager fullTextEntityManager =
                                       org.hibernate.search.jpa.Search.getFullTextEntityManager(em);

        QueryBuilder queryBuilder = fullTextEntityManager.getSearchFactory().buildQueryBuilder().
                                             forEntity(Customers.class).get();
        org.apache.lucene.search.Query query = queryBuilder.bool().must(queryBuilder.keyword()
                                            .onField("email").matching(email).createQuery()).
                                            must(queryBuilder.keyword()
                                            .onField("password").matching(password).createQuery()).createQuery();

        FullTextQuery fullTextQuery = fullTextEntityManager
                                            .createFullTextQuery(query, Customers.class);

        fullTextQuery.initializeObjectsWith(ObjectLookupMethod.SKIP,
                                            DatabaseRetrievalMethod.FIND_BY_ID);

        List results = fullTextQuery.getResultList();

        if (results.isEmpty()) {
            return null;
        }

        return (Customers) results.get(0);
    }
}

Place an Order

This query does not need Lucene. When a customer places an order, the application should have the customer (because he or she is logged in); the shipping address (it’s provided by the customer); and the shopping cart (stored in the customer’s session). With these, it’s very easy to persist an order, like this:

package eshop.beans;
...
public class EShopBean {
...
private EntityManager em;
...
Orders new_order = new Orders();
...
//for each product
new_order.getCart().add( cart_product);
...
new_order.setShipping_address( shipping_address);
new_order.setCustomer( customer);

new_order.setOrderdate(Calendar.getInstance().getTime());
new_order.setSubtotal( payment);
new_order.setStatus("PURCHASED");

...
em.persist(new_order);
...
}

This query affects only a single document, providing atomicity.

Check the Inventory

A customer can add a product to his shopping cart only if the product is available in the warehouse inventory. Programmatically speaking, this means we need to know the product details and the required quantity; check if it’s available in the inventory; and, if it is, remove the quantity from the inventory.

However, removing from inventory can lead to inconsistent data, which is clearly undesirable. This can be avoided by using optimistic  locking (or even pessimistic locking), but there’s a price to pay when an optimistic locking exception is thrown. A simple solution is to provide a message such as, “The product was not added to your cart. Sorry for the inconvenience, please try again . . .”, or to wait a few seconds and repeat the query for a certain number of times or until the product is not available in the inventory anymore. The first solution gives the customer a quick response, while the second solution puts him in a waiting queue. I chose to return a message that urges the user to try again. The code is shown in Listing 8-6.

Listing 8-6.  Checking Inventory

package eshop.beans;
...
public class EShopBean {
...
public int checkInventory(String sku, String color, String size, int quantity) {
       
        InventoryPK pk = new InventoryPK(sku, color, size);

        Inventory inventory = em.find(Inventory.class, pk, LockModeType.OPTIMISTIC);
        int amount = inventory.getInventory();
        if (amount > 0) {
            if (amount >= quantity) {
                amount = amount - quantity;
                inventory.setInventory(amount);
                try {
                    em.merge(inventory);
                } catch (OptimisticLockException e) {
                    return -9999;
                }

                return quantity;
            } else {
                inventory.setInventory(0);
                try {
                    em.merge(inventory);
                } catch (OptimisticLockException e) {
                    return -9999;
                }

                return amount;
            }
        } else {
            return amount;
        }
    }
}

When the inventory contains fewer products than the required quantity, we add to the shopping cart only the quantity available and inform the user with a message. Figure 8-8 shows the messages that might appear when the user tries to add a product to his shopping cart.

Of course, there are many ways to improve this, such as displaying a message next to each product that says either "In Stock" or "Not in Stock," and deactivating the Add to Cart button for the latter.

Restore the Inventory

Customers can drop products from their shopping carts before placing the order, or the session might expire if the user get distracted and doesn’t complete the order in a timely fashion (our application doesn’t implement this case). When this happens, we need to restore the stock by adding the dropped product back to inventory. Practically, the process is the reverse of removing products from inventory, so the same problem of inconsistent data may arise. Optimistic locking (or pessimistic locking) can solve this, but, again, we have to deal with a possible optimistic locking exception. Obviously, you can’t return a message to the customer that says, “Sorry, we can’t remove the product from your cart . . .” because that would be very annoying. In our case, we just remove the product from the shopping cart (since it’s stored in the session) and try only once to restore the inventory. But you could repeat the query, storing the quantity somewhere else and try to restore it later; or you could use an in-memory secondary inventory; or find any other approach that fits your needs.

Here’s the code for restoring the inventory:

package eshop.beans;
...
public class EShopBean {
...
public int refreshInventory(String sku, String color, String size, int quantity) {

        InventoryPK pk = new InventoryPK(sku, color, size);

        Inventory inventory = em.find(Inventory.class, pk, LockModeType.OPTIMISTIC);
        int amount = inventory.getInventory();

        amount = amount + quantity;

        inventory.setInventory(amount);

        try {
            em.merge(inventory);
        } catch (OptimisticLockException e) {
            return -9999;
        }

        return quantity;
    }
}

When a product is removed from the shopping cart (even if the inventory could not actually be restored), the user should see a message like the one in Figure 8-9.

At this point, we have a set of queries that compare well with many e-commerce web sites. Obviously, there are many others that could be added, either using this database model or by modifying the model itself.

Considerations for Developing the Admin GUI

So far, we’ve talked about the e-commerce platform only from the perspective of a customer (user). But the administrative aspects are also important for e-commerce platforms. You can develop a powerful admin GUI based on our database model just by writing the proper queries. For example, our database model facilitates the most common tasks that an administrator must accomplish:

• You can easily create a new category, rename or delete existing ones, and so on.
• You can insert new products into a category, delete existing products, or modify products characteristics.
• You can view or modify customer profiles and orders.
• You can easily populate the inventory and tracking status.
• You can create several statistics regarding selling, bestsellers, and more.

All these tasks can be accomplished atomically (affecting only one document per query).

Summary

In this chapter, you learned how to query the MongoDB e-commerce database modeled in Chapter 7. You saw how easy it is to write Lucene queries to achieve the main features of an e-commerce platform and avoid transactions. Using MongoDB atomicity per document, embedded collections, nested documents, and some tricky queries, we were able to create an e-commerce site that provides most of the common facilities of a real e-commerce platform. At this point, you can easily write an admin side, add a powerful login mechanism, modify certain parameters such as the products page size, and much more.