I have a schema which consist of the following table. 1)TABLE category_type. Basically we have 4 category_type a)Groceries b)Pharma c)Baby Products d) Sports item
CREATE TABLE `category_type` (
`id` int(11) NOT NULL DEFAULT '0',
`name` varchar(45) NOT NULL,
`image` varchar(128) NOT NULL,
`creation_date` datetime NOT NULL,
`last_updated_date` datetime NOT NULL,
`created_by` int(11) DEFAULT NULL,
`last_updated_by` int(11) NOT NULL,
`object_version_number` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
2) TABLE category. Table's description as follows. The category_type and category are different. Category Type is the higher level as explain earlier. The category table consist of the next level of categorization. Ex) Staples,Personal Care,Biscuit and Beverages is the 2nd level of category which come under the main category_type Groceries. All the next level of the categorization is been done in the same category table itself. Suppose rice and pulses,flour and suji,spices,salt and sugar are the 3rd level of categorization with comes under staples category.So,all the 3rd level of the categorization refer to the 2nd level of categories using the "parent_category_id" field in the same table.If the staples have id=53 there sub categories will have there "parent_category_id"=53 in the same table.
CREATE TABLE `category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`creation_date` datetime NOT NULL,
`last_updated_date` datetime NOT NULL,
`created_by` int(11) NOT NULL,
`last_updated_by` int(11) NOT NULL,
`object_version_number` int(11) NOT NULL,
`image` varchar(128) DEFAULT NULL,
`category_type_id` int(11) NOT NULL,
`parent_category_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_category_categorytype_id` (`category_type_id`),
KEY `FK_parent_category_id` (`parent_category_id`),
CONSTRAINT `FK_category_categorytype_id` FOREIGN KEY (`category_type_id`) REFERENCES `category_type` (`id`),
CONSTRAINT `FK_parent_category_id` FOREIGN KEY (`parent_category_id`) REFERENCES `category` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1011 DEFAULT CHARSET=utf8
3)TABLE product.Now ultimately the product table come.It has category_id field which references to the id field in table category
CREATE TABLE `product` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`category_id` int(11) NOT NULL,
`name` varchar(45) NOT NULL,
`manufacturer` varchar(45) DEFAULT NULL,
`unique_name` varchar(45) DEFAULT NULL,
`barcode` varchar(45) DEFAULT NULL,
`search_code` varchar(45) DEFAULT NULL,
`active` int(1) NOT NULL DEFAULT '1',
`image` varchar(128) DEFAULT NULL,
`creation_date` datetime NOT NULL,
`last_updated_date` datetime NOT NULL,
`created_by` int(11) NOT NULL,
`last_updated_by` int(11) NOT NULL,
`object_version_number` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `UNQ_product_unique_name` (`manufacturer`,`name`,`category_id`),
KEY `FK_product_category_id` (`category_id`),
CONSTRAINT `FK_product_category_id` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1834 DEFAULT CHARSET=utf8
The POJO class of the able tables are as follows: 1)Table "category_type"
@Entity
public class CategoryType implements java.io.Serializable {
/**
*
*/
private static final long serialVersionUID = 1L;
private int id;
private String name;
private Date creationDate;
private Date lastUpdatedDate;
private int createdBy;
private int lastUpdatedBy;
private String image;
@Version
private int objectVersionNumber;
public CategoryType() {
}
public CategoryType(String name, Date creationDate, Date lastUpdatedDate,
int createdBy, int lastUpdatedBy, int objectVersionNumber) {
this.name = name;
this.creationDate = creationDate;
this.lastUpdatedDate = lastUpdatedDate;
this.createdBy = createdBy;
this.lastUpdatedBy = lastUpdatedBy;
this.objectVersionNumber = objectVersionNumber;
}
public int getId() {
return this.id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
public Date getCreationDate() {
return this.creationDate;
}
public void setCreationDate(Date creationDate) {
this.creationDate = creationDate;
}
public Date getLastUpdatedDate() {
return this.lastUpdatedDate;
}
public void setLastUpdatedDate(Date lastUpdatedDate) {
this.lastUpdatedDate = lastUpdatedDate;
}
public int getCreatedBy() {
return this.createdBy;
}
public void setCreatedBy(int createdBy) {
this.createdBy = createdBy;
}
public int getLastUpdatedBy() {
return this.lastUpdatedBy;
}
public void setLastUpdatedBy(int lastUpdatedBy) {
this.lastUpdatedBy = lastUpdatedBy;
}
public int getObjectVersionNumber() {
return this.objectVersionNumber;
}
public void setObjectVersionNumber(int objectVersionNumber) {
this.objectVersionNumber = objectVersionNumber;
}
public String getImage() {
return this.image;
}
public void setImage(String image) {
this.image = image;
}
public static void initializeXStream(XStream xstreamObj) {
xstreamObj.alias("", java.sql.Timestamp.class, java.util.Date.class);
}
}
2)Table "category"
@XStreamAlias("category")
@Entity
public class Category implements java.io.Serializable {
/**
*
*/
private static final long serialVersionUID = 1L;
private int id;
private String name;
private Date creationDate;
private Date lastUpdatedDate;
private int createdBy;
private int lastUpdatedBy;
private String image;
@XStreamOmitField
@JsonIgnore
private CategoryType categoryType;
@XStreamOmitField
@JsonIgnore
private Category parentCategory;
@Version
private int objectVersionNumber;
@XStreamOmitField
@JsonIgnore
private Set<Product> products = new HashSet<Product>(0);
@XStreamOmitField
@JsonIgnore
private Set<RetailerCategory> retailerCategories = new HashSet<RetailerCategory>(
0);
@XStreamOmitField
@JsonIgnore
private Set<CategoryType> categoryTypes = new HashSet<CategoryType>(0);
public Category() {
}
public Category(String name, Date creationDate, Date lastUpdatedDate,
int createdBy, int lastUpdatedBy, int objectVersionNumber) {
this.name = name;
this.creationDate = creationDate;
this.lastUpdatedDate = lastUpdatedDate;
this.createdBy = createdBy;
this.lastUpdatedBy = lastUpdatedBy;
this.objectVersionNumber = objectVersionNumber;
}
public Category(String name, Date creationDate, Date lastUpdatedDate,
int createdBy, int lastUpdatedBy, int objectVersionNumber, String image,
Set<Product> products, Set<RetailerCategory> retailerCategories) {
this.name = name;
this.creationDate = creationDate;
this.lastUpdatedDate = lastUpdatedDate;
this.createdBy = createdBy;
this.lastUpdatedBy = lastUpdatedBy;
this.objectVersionNumber = objectVersionNumber;
this.image = image;
this.products = products;
this.retailerCategories = retailerCategories;
}
public int getId() {
return this.id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
public Date getCreationDate() {
return this.creationDate;
}
public void setCreationDate(Date creationDate) {
this.creationDate = creationDate;
}
public Date getLastUpdatedDate() {
return this.lastUpdatedDate;
}
public void setLastUpdatedDate(Date lastUpdatedDate) {
this.lastUpdatedDate = lastUpdatedDate;
}
public int getCreatedBy() {
return this.createdBy;
}
public void setCreatedBy(int createdBy) {
this.createdBy = createdBy;
}
public int getLastUpdatedBy() {
return this.lastUpdatedBy;
}
public void setLastUpdatedBy(int lastUpdatedBy) {
this.lastUpdatedBy = lastUpdatedBy;
}
public int getObjectVersionNumber() {
return this.objectVersionNumber;
}
public void setObjectVersionNumber(int objectVersionNumber) {
this.objectVersionNumber = objectVersionNumber;
}
public String getImage() {
return this.image;
}
public void setImage(String image) {
this.image = image;
}
public Set<Product> getProducts() {
return this.products;
}
public void setProducts(Set<Product> products) {
this.products = products;
}
public Set<RetailerCategory> getRetailerCategories() {
return this.retailerCategories;
}
public void setRetailerCategories(Set<RetailerCategory> retailerCategories) {
this.retailerCategories = retailerCategories;
}
public Set<CategoryType> getCategoryTypes() {
return categoryTypes;
}
public void setCategoryTypes(Set<CategoryType> categoryTypes) {
this.categoryTypes = categoryTypes;
}
public CategoryType getCategoryType() {
return categoryType;
}
public void setCategoryType(CategoryType categoryType) {
this.categoryType = categoryType;
}
public Category getParentCategory() {
return parentCategory;
}
public void setParentCategory(Category parentCategory) {
this.parentCategory = parentCategory;
}
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
builder.append("Category [id=");
builder.append(id);
builder.append(", name=");
builder.append(name);
builder.append(", creationDate=");
builder.append(creationDate);
builder.append(", lastUpdatedDate=");
builder.append(lastUpdatedDate);
builder.append(", createdBy=");
builder.append(createdBy);
builder.append(", lastUpdatedBy=");
builder.append(lastUpdatedBy);
builder.append(", objectVersionNumber=");
builder.append(objectVersionNumber);
builder.append("]");
return builder.toString();
}
public static void initializeXStream(XStream xstreamObj) {
xstreamObj.alias("", java.sql.Timestamp.class, java.util.Date.class);
}
}
3) Table product
@XStreamAlias("product")
@Entity
public class Product implements java.io.Serializable,Comparable<Product> {
/**
*
*/
private static final long serialVersionUID = -1399060577555793322L;
private int id;
@XStreamOmitField
@JsonIgnore
private Category category;
private String name;
private String uniqueName;
private String barcode;
private String searchCode;
private int active = 1;
private String manufacturer;
private String image;
private Date creationDate;
private Date lastUpdatedDate;
private int createdBy;
private int lastUpdatedBy;
@Version
private int objectVersionNumber;
@XStreamOmitField
@JsonIgnore
private Set<OrderLines> orderLineses = new HashSet<OrderLines>(0);
@XStreamOmitField
//@JsonIgnore
private Set<RetailerSnapshot> retailerSnapshots = new HashSet<RetailerSnapshot>(0);
@XStreamOmitField
@JsonIgnore
private Set brandedProducts = new HashSet(0);
public Product() {
}
public Product(Category category, String name, String uniqueName,
int active, Date creationDate, Date lastUpdatedDate, int createdBy,
int lastUpdatedBy,int objectVersionNumber) {
this.category = category;
this.name = name;
this.uniqueName = uniqueName;
this.active = active;
this.creationDate = creationDate;
this.lastUpdatedDate = lastUpdatedDate;
this.createdBy = createdBy;
this.lastUpdatedBy = lastUpdatedBy;
this.objectVersionNumber = objectVersionNumber;
}
public Product(Category category, String name, String uniqueName,
String barcode, String searchCode, int active, String image,
Date creationDate, Date lastUpdatedDate, int createdBy,
int lastUpdatedBy, int objectVersionNumber, Set<OrderLines> orderLineses,
Set<RetailerSnapshot> retailerSnapshots) {
this.category = category;
this.name = name;
this.uniqueName = uniqueName;
this.barcode = barcode;
this.searchCode = searchCode;
this.active = active;
this.image = image;
this.creationDate = creationDate;
this.lastUpdatedDate = lastUpdatedDate;
this.createdBy = createdBy;
this.lastUpdatedBy = lastUpdatedBy;
this.objectVersionNumber = objectVersionNumber;
this.orderLineses = orderLineses;
this.retailerSnapshots = retailerSnapshots;
}
public Product( Category category, String name,
String uniqueName, String barcode, String searchCode, int active) {
this.category = category;
this.name = name;
this.uniqueName = uniqueName;
this.barcode = barcode;
this.searchCode = searchCode;
this.active = active;
}
public Product(String name, String manufacturer, Category category) {
this.name = name;
this.manufacturer = manufacturer;
this.category = category;
}
public int getId() {
return this.id;
}
public void setId(int id) {
this.id = id;
}
public int getObjectVersionNumber() {
return this.objectVersionNumber;
}
public void setObjectVersionNumber(int objectVersionNumber) {
this.objectVersionNumber = objectVersionNumber;
}
public Category getCategory() {
return this.category;
}
public void setCategory(Category category) {
this.category = category;
}
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
public String getUniqueName() {
return this.uniqueName;
}
public void setUniqueName(String uniqueName) {
this.uniqueName = uniqueName;
}
public String getBarcode() {
return this.barcode;
}
public void setBarcode(String barcode) {
this.barcode = barcode;
}
public String getSearchCode() {
return this.searchCode;
}
public void setSearchCode(String searchCode) {
this.searchCode = searchCode;
}
public int getActive() {
return this.active;
}
public void setActive(int active) {
this.active = active;
}
public String getImage() {
return this.image;
}
public void setImage(String image) {
this.image = image;
}
public Date getCreationDate() {
return this.creationDate;
}
public void setCreationDate(Date creationDate) {
this.creationDate = creationDate;
}
public Date getLastUpdatedDate() {
return this.lastUpdatedDate;
}
public void setLastUpdatedDate(Date lastUpdatedDate) {
this.lastUpdatedDate = lastUpdatedDate;
}
public int getCreatedBy() {
return this.createdBy;
}
public void setCreatedBy(int createdBy) {
this.createdBy = createdBy;
}
public int getLastUpdatedBy() {
return this.lastUpdatedBy;
}
public void setLastUpdatedBy(int lastUpdatedBy) {
this.lastUpdatedBy = lastUpdatedBy;
}
public Set<OrderLines> getOrderLineses() {
return this.orderLineses;
}
public void setOrderLineses(Set<OrderLines> orderLineses) {
this.orderLineses = orderLineses;
}
public Set getBrandedProducts() {
return this.brandedProducts;
}
public void setBrandedProducts(Set brandedProducts) {
this.brandedProducts = brandedProducts;
}
public Set<RetailerSnapshot> getRetailerSnapshots() {
return this.retailerSnapshots;
}
public void setRetailerSnapshots(Set<RetailerSnapshot> retailerSnapshots) {
this.retailerSnapshots = retailerSnapshots;
}
public String getManufacturer() {
return manufacturer;
}
public void setManufacturer(String manufacturer) {
this.manufacturer = manufacturer;
}
@Override
public String toString() {
StringBuilder builder = new StringBuilder();
builder.append("Product [id=");
builder.append(id);
builder.append(", category=");
builder.append(category);
builder.append(", name=");
builder.append(name);
builder.append(", uniqueName=");
builder.append(uniqueName);
builder.append(", barcode=");
builder.append(barcode);
builder.append(", searchCode=");
builder.append(searchCode);
builder.append(", active=");
builder.append(active);
builder.append(", creationDate=");
builder.append(creationDate);
builder.append(", lastUpdatedDate=");
builder.append(lastUpdatedDate);
builder.append(", createdBy=");
builder.append(createdBy);
builder.append(", lastUpdatedBy=");
builder.append(lastUpdatedBy);
builder.append(", objectVersionNumber=");
builder.append(objectVersionNumber);
builder.append("]");
return builder.toString();
}
public int compareTo(Product that) {
final int BEFORE = -1;
final int AFTER = 1;
if (that == null) {
return BEFORE;
}
Comparable<String> thisProductName = this.getCategory().getName();
Comparable<String> thatProductName = that.getCategory().getName();
if (thisProductName == null) {
return AFTER;
} else if (thatProductName == null) {
return BEFORE;
} else {
return thisProductName.compareTo(thatProductName.toString());
}
}
}
I am implementing a product filter based on the category been clicked. The code written at the front end are as follows:
<ul class="megamenu skyblue">
<!--1st level of categories -->
<li><a onclick="getCategorySearchAjaxResults('Staples','53')" class="color1" href="#">Staples</a></li>
<li><a onclick="getCategorySearchAjaxResults('HouseHold','54')" class="color1" href="#">HouseHold</a></li>
<li><a onclick="getCategorySearchAjaxResults('Wash nd Clean','55')" class="color1" href="#">Wash nd Clean</a></li>
<li><a onclick="getCategorySearchAjaxResults('Biscuit and Beverages','56')" class="color1" href="#">Biscuit and Beverages</a></li>
</ul>
When user click on any of the hyperlink in the list. The getCategorySearchAjaxResults(par1,par2) function is been called with the Category name and the Category id passed to it.
The implementation of the above function is shown below
function getCategorySearchAjaxResults(category) {
$
.ajax({
url : "${pageContext.request.contextPath}/customer/category_search.shx",
data : 'category=' + category,
success : function(data) {
var obj = JSON.parse( data);
$("#prod_list").html(buildProduct(obj.searchedProducts));
},
error : function(data, status, er) {
alert("Error occured while searching the category.");
}
});
}
The important thing to note here is the return searchedProducts is shoppingCart object is been replaced in the div with id=prod_list.
In the controller of the above the following code is been written
@RequestMapping(value = "/customer/category_search",method = RequestMethod.GET, headers="Accept=*/*")
public @ResponseBody String getcategorySearchResults(Model model,HttpSession session,
@ModelAttribute("shoppingCart") ShoppingCart shoppingCart,
@RequestParam("category") String category) throws IOException{
shoppingCart.setCategory(category);
List<Product> rs = productManager.findByCriteria(shoppingCart.getRetailer().getId(),category,shoppingCart);
model.addAttribute("shoppingCart", shoppingCart);
String json = JSONUtil.createJsonString(shoppingCart);//create the json of the shoppingCart object of ShoppingCart type
System.out.println(json);
return json;
}
BTW shoppingCart is simply the bean class used to the store the data to be displayed on the front end. It is converted to json and forward it to jsp page when ajax called is been made. The implementation of the findByCriteria is show below
public List<Product> findByCriteria(Integer orgaId, String categoryName,ShoppingCart shoppingCart) {
List<Product> rs = findByCriteria(orgaId, categoryName);
shoppingCart.setSearchedProducts(rs);
return rs;
}
The implementation of the findByCriteria(2parameters) is show below
public List<Product> findByCriteria(Integer orgaId, String categoryName){
return productDAO.findByCriteria(orgaId, categoryName);
}
The implementation of the findByCriteria(2parameters) in productDAO is shown below:
public List<Product> findByCriteria(Integer orgaId, String categoryName) {
List<Product> rs = null;
Query query = null;
Category category = null;
StringBuilder hql = new StringBuilder();
try{
String categoryHql=(categoryName!=null)?" and category.id=:category_id ":" ";
hql.append(" from Product product ");
hql.append(" left join fetch product.category category ");
hql.append(" left join fetch product.retailerSnapshots retailerSnapshot ");
hql.append(" where retailerSnapshot.organization.id=:org_id");
hql.append(categoryHql);
rs = getProducts("category_id",orgaId, categoryName, hql);
}catch(Exception e){
//if (sessionFactory.getCurrentSession().getTransaction() != null) sessionFactory.getCurrentSession().getTransaction().rollback();
e.printStackTrace();
}finally{
//sessionFactory.getCurrentSession().close();
}
return rs;
}
As we see above that when the user clicks on the particular link. It category id is been passed to the function. For Example if Staples with id=53 is been clicked then it's product with category_id=53 will be displayed. But the problem is that product doesn't refer to any category id of the 1st level of the categorization.It refers to the last level of the categorization. In short if staples is been been clicked it's last level of the category are Dates,cashews,edible oil etc. Product with category id of last level of category should be displayed. So when i click on staples no product is been displayed. How to i solve this problem? On the click the last level of the category_id should be searched and the respective product should be displayed. I hope the question is been understood. If not please leave a comment.
