package com.sun.j2ee.blueprints.catalog.dao;
import java.sql.*;
import java.util.*;
import javax.naming.*;
import javax.sql.*;
import com.sun.j2ee.blueprints.catalog.util.JNDINames;
import com.sun.j2ee.blueprints.catalog.model.Page;
import com.sun.j2ee.blueprints.catalog.model.Category;
import com.sun.j2ee.blueprints.catalog.model.Product;
import com.sun.j2ee.blueprints.catalog.model.Item;
import com.sun.j2ee.blueprints.catalog.util.DatabaseNames;
import com.sun.j2ee.blueprints.catalog.exceptions.CatalogDAOSysException;
import com.sun.j2ee.blueprints.servicelocator.ejb.ServiceLocator;
import com.sun.j2ee.blueprints.servicelocator.ServiceLocatorException;
public class CloudscapeCatalogDAO implements CatalogDAO {
public static String GET_CATEGORY_STATEMENT
= "select name, descn "
+ " from (category a join category_details b on a.catid=b.catid) "
+ " where locale = ? and a.catid = ?";
public static String GET_CATEGORIES_STATEMENT
= "select a.catid, name, descn "
+ " from (category a join category_details b on a.catid=b.catid) "
+ " where locale = ? order by name";
public static String GET_PRODUCT_STATEMENT
= "select name, descn "
+ " from (product a join product_details b on a.productid=b.productid) "
+ " where locale = ? and a.productid = ? ";
public static String GET_PRODUCTS_STATEMENT
= "select a.productid, name, descn "
+ " from (product a join product_details b on a.productid=b.productid) "
+ " where locale = ? and a.catid = ? order by name";
public static String GET_ITEM_STATEMENT
= "select catid, a.productid, name, b.image, b.descn, attr1, "
+ " attr2, attr3, attr4, attr5, listprice, unitcost "
+ " from (((item a join item_details b on a.itemid=b.itemid)"
+ " join product_details c on a.productid=c.productid)"
+ " join product d on d.productid = c.productid and b.locale = c.locale) "
+ " where b.locale = ? and a.itemid = ?";
public static String GET_ITEMS_STATEMENT
= "select catid, name, a.itemid, b.image, b.descn, attr1, "
+ " attr2, attr3, attr4, attr5, listprice, unitcost "
+ " from (((item a join item_details b on a.itemid=b.itemid)"
+ " join product_details c on a.productid=c.productid)"
+ " join product d on d.productid=c.productid and b.locale = c.locale)"
+ " where b.locale = ? and a.productid = ?";
public static String[] SEARCH_ITEMS_STATEMENT_FRAGMENTS
= { "select catid, a.productid, name, a.itemid, b.image, b.descn, attr1,"
+ " attr2, attr3, attr4, attr5, listprice, unitcost"
+ " from (((item a join item_details b on a.itemid=b.itemid)"
+ " join product_details c on a.productid=c.productid)"
+ " join product d on d.productid=c.productid and b.locale = c.locale)"
+ " where b.locale = ? ",
" and ((lower(name) like ? ",
" or lower(name) like ? ",
" ) or (lower(catid) like ? ",
" or lower(catid) like ? ",
" ) or (lower(b.descn) like ? ",
" or lower(b.descn) like ? ",
") )"
};
protected static DataSource getDataSource() throws CatalogDAOSysException {
try {
ServiceLocator sl = new ServiceLocator();
return (DataSource) sl.getDataSource(JNDINames.CATALOG_DATASOURCE);
} catch (ServiceLocatorException slx) {
throw new CatalogDAOSysException("NamingException while looking up DB context : " +
slx.getMessage());
}
}
public Category getCategory(String categoryID, Locale l)
throws CatalogDAOSysException {
Connection c = null;
PreparedStatement ps = null;
ResultSet rs = null;
Category ret = null;
try {
c = getDataSource().getConnection();
ps = c.prepareStatement(GET_CATEGORY_STATEMENT,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ps.setString(1, l.toString());
ps.setString(2, categoryID);
rs = ps.executeQuery();
if (rs.first()) {
ret = new Category(categoryID, rs.getString(1), rs.getString(2));
}
rs.close();
ps.close();
c.close();
return ret;
} catch (SQLException se) {
throw new CatalogDAOSysException("SQLException: " + se.getMessage());
}
}
public Page getCategories(int start, int count, Locale l)
throws CatalogDAOSysException {
Connection c = null;
PreparedStatement ps = null;
ResultSet rs = null;
Page ret = null;
try {
c = getDataSource().getConnection();
ps = c.prepareStatement(GET_CATEGORIES_STATEMENT,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ps.setString(1, l.toString());
rs = ps.executeQuery();
if (start >= 0 && rs.absolute(start+1)) {
boolean hasNext = false;
List items = new ArrayList();
do {
items.add(new Category(rs.getString(1).trim(),
rs.getString(2),
rs.getString(3)));
} while ((hasNext = rs.next()) && (--count > 0));
ret = new Page(items, start, hasNext);
} else {
ret = Page.EMPTY_PAGE;
}
rs.close();
ps.close();
c.close();
return ret;
} catch (SQLException se) {
se.printStackTrace(System.err);
throw new CatalogDAOSysException("SQLException: " + se.getMessage());
}
}
public Product getProduct(String productID, Locale l)
throws CatalogDAOSysException {
Connection c = null;
PreparedStatement ps = null;
ResultSet rs = null;
Product ret = null;
try {
c = getDataSource().getConnection();
ps = c.prepareStatement(GET_PRODUCT_STATEMENT,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ps.setString(1, l.toString());
ps.setString(2, productID);
rs = ps.executeQuery();
if (rs.first()) {
ret = new Product(productID, rs.getString(1), rs.getString(2));
}
rs.close();
ps.close();
c.close();
return ret;
} catch (SQLException se) {
throw new CatalogDAOSysException("SQLException: " + se.getMessage());
}
}
public Page getProducts(String categoryID, int start, int count, Locale l)
throws CatalogDAOSysException {
Connection c = null;
PreparedStatement ps = null;
ResultSet rs = null;
Page ret = null;
try {
c = getDataSource().getConnection();
ps = c.prepareStatement(GET_PRODUCTS_STATEMENT,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ps.setString(1, l.toString());
ps.setString(2, categoryID);
rs = ps.executeQuery();
if (start >= 0 && rs.absolute(start+1)) {
boolean hasNext = false;
List items = new ArrayList();
do {
items.add(new Product(rs.getString(1).trim(),
rs.getString(2).trim(),
rs.getString(3).trim()));
} while ((hasNext = rs.next()) && (--count > 0));
ret = new Page(items, start, hasNext);
} else {
ret = Page.EMPTY_PAGE;
}
rs.close();
ps.close();
c.close();
return ret;
} catch (SQLException se) {
throw new CatalogDAOSysException("SQLException: " + se.getMessage());
}
}
public Item getItem(String itemID, Locale l)
throws CatalogDAOSysException {
Connection c = null;
PreparedStatement ps = null;
ResultSet rs = null;
Item ret = null;
try {
c = getDataSource().getConnection();
ps = c.prepareStatement(GET_ITEM_STATEMENT,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ps.setString(1, l.toString());
ps.setString(2, itemID);
rs = ps.executeQuery();
if (rs.first()) {
int i = 1;
ret = new Item(rs.getString(i++).trim(),
rs.getString(i++).trim(),
rs.getString(i++),
itemID,
rs.getString(i++).trim(),
rs.getString(i++),
rs.getString(i++),
rs.getString(i++),
rs.getString(i++),
rs.getString(i++),
rs.getString(i++),
rs.getDouble(i++),
rs.getDouble(i++));
}
rs.close();
ps.close();
c.close();
return ret;
} catch (SQLException se) {
throw new CatalogDAOSysException("SQLException: " + se.getMessage());
}
}
public Page getItems(String productID, int start, int count, Locale l)
throws CatalogDAOSysException {
Connection c = null;
PreparedStatement ps = null;
ResultSet rs = null;
Page ret = null;
try {
c = getDataSource().getConnection();
ps = c.prepareStatement(GET_ITEMS_STATEMENT,
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ps.setString(1, l.toString());
ps.setString(2, productID);
rs = ps.executeQuery();
if (start >= 0 && rs.absolute(start+1)) {
boolean hasNext = false;
List items = new ArrayList();
do {
int i = 1;
items.add(new Item(productID,
rs.getString(i++).trim(),
rs.getString(i++),
rs.getString(i++).trim(),
rs.getString(i++).trim(),
rs.getString(i++),
rs.getString(i++),
rs.getString(i++),
rs.getString(i++),
rs.getString(i++),
rs.getString(i++),
rs.getDouble(i++),
rs.getDouble(i++)));
} while ((hasNext = rs.next()) && (--count > 0));
ret = new Page(items, start, hasNext);
} else {
ret = Page.EMPTY_PAGE;
}
rs.close();
ps.close();
c.close();
return ret;
} catch (SQLException se) {
throw new CatalogDAOSysException("SQLException: " + se.getMessage());
}
}
public Page searchItems(String searchQuery, int start, int count, Locale l)
throws CatalogDAOSysException {
Collection keywords = new HashSet();
StringTokenizer st = new StringTokenizer(searchQuery);
while (st.hasMoreTokens()) {
keywords.add(st.nextToken());
}
if (keywords.isEmpty()) {
return Page.EMPTY_PAGE;
}
Connection c = null;
PreparedStatement ps = null;
ResultSet rs = null;
Page ret = null;
try {
c = getDataSource().getConnection();
Iterator it;
int i;
StringBuffer sb = new StringBuffer();
sb.append(SEARCH_ITEMS_STATEMENT_FRAGMENTS[0]);
int keywordsSize = keywords.size();
if (keywordsSize > 0) {
sb.append(SEARCH_ITEMS_STATEMENT_FRAGMENTS[1]);
for (i = 1; i != keywordsSize; i++) {
sb.append(SEARCH_ITEMS_STATEMENT_FRAGMENTS[2]);
}
sb.append(SEARCH_ITEMS_STATEMENT_FRAGMENTS[3]);
for (i = 1; i != keywordsSize; i++) {
sb.append(SEARCH_ITEMS_STATEMENT_FRAGMENTS[4]);
}
sb.append(SEARCH_ITEMS_STATEMENT_FRAGMENTS[5]);
for (i = 1; i != keywordsSize; i++) {
sb.append(SEARCH_ITEMS_STATEMENT_FRAGMENTS[6]);
}
sb.append(SEARCH_ITEMS_STATEMENT_FRAGMENTS[7]);
}
ps = c.prepareStatement(sb.toString(),
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ps.setString(1, l.toString());
i = 2;
for (it = keywords.iterator(); it.hasNext(); i++) {
String keyword = ((String) it.next()).toLowerCase();
ps.setString(i, "%" + keyword + "%");
}
for (it = keywords.iterator(); it.hasNext(); i++) {
String keyword = ((String) it.next()).toLowerCase();
ps.setString(i, "%" + keyword + "%");
}
for (it = keywords.iterator(); it.hasNext(); i++) {
String keyword = ((String) it.next()).toLowerCase();
ps.setString(i, "%" + keyword + "%");
}
rs = ps.executeQuery();
if (start >= 0 && rs.absolute(start+1)) {
boolean hasNext = false;
List items = new ArrayList();
do {
i = 1;
items.add(new Item(rs.getString(i++).trim(),
rs.getString(i++).trim(),
rs.getString(i++),
rs.getString(i++).trim(),
rs.getString(i++).trim(),
rs.getString(i++),
rs.getString(i++),
rs.getString(i++),
rs.getString(i++),
rs.getString(i++),
rs.getString(i++),
rs.getDouble(i++),
rs.getDouble(i++)));
} while ((hasNext = rs.next()) && (--count > 0));
ret = new Page(items, start, hasNext);
} else {
ret = Page.EMPTY_PAGE;
}
rs.close();
ps.close();
c.close();
return ret;
} catch (SQLException se) {
throw new CatalogDAOSysException("SQLException: " + se.getMessage());
}
}
}