public String getXMLDataForItemId(String itemId) {
String xmlstore = "";
Connection conn = null;
// Create a connection to the database
try { // System.out.print("before connection");
// Connection to db manager
conn = DbManager.getConnection(true);
Statement statement = conn.createStatement();
// System.out.print("successfully connected to database");
// Geting the items
ResultSet result =
statement.executeQuery("SELECT * FROM Items " + "WHERE Items.Item_ID = " + itemId);
result.first();
// Somethings in it
if (result.getRow() != 0) {
// System.out.println("I found a item " );
DocumentBuilderFactory fac = DocumentBuilderFactory.newInstance();
DocumentBuilder b = fac.newDocumentBuilder();
org.w3c.dom.Document doc = b.newDocument();
// root element
Element root = doc.createElement("Item");
root.setAttribute("ItemID", itemId);
doc.appendChild(root);
Element element_name = doc.createElement("Name");
element_name.appendChild(doc.createTextNode(replacespecial(result.getString("Item_name"))));
root.appendChild(element_name);
/* // Build Category Elements
// Get the Categories
Statement catstatement = conn.createStatement();
ResultSet catresult = catstatement.executeQuery("SELECT Category "
+ "FROM Category,Item_Category "
+ "WHERE Item_Category.Item_ID = " + itemId + " "
+ "AND Item_Category.Category_ID = Category.Category_ID");
Element category_element;
while (catresult.next()) {
category_element = doc.createElement("Category");
category_element.appendChild(doc.createTextNode(replacespecial(catresult.getString("Category"))));
root.appendChild(category_element);
}
catresult.close();
catstatement.close();*/
// Build Item price elements
if (result.getString("Currently") != null) {
Element currently_element = doc.createElement("Currently");
currently_element.appendChild(doc.createTextNode("$" + result.getString("Currently")));
root.appendChild(currently_element);
}
if (result.getString("Buy_Price") != null) {
Element buyprice_element = doc.createElement("Buy_Price");
buyprice_element.appendChild(doc.createTextNode("$" + result.getString("Buy_Price")));
root.appendChild(buyprice_element);
}
if (result.getString("First_Bid") != null) {
Element start_element = doc.createElement("First_Bid");
start_element.appendChild(doc.createTextNode("$" + result.getString("First_Bid")));
root.appendChild(start_element);
}
// num bids
if (result.getString("Number_of_Bids") != null) {
Element numberbids_elements = doc.createElement("Number_of_Bids");
numberbids_elements.appendChild(doc.createTextNode(result.getString("Number_of_Bids")));
root.appendChild(numberbids_elements);
}
// description
if (result.getString("Description") != null) {
Element description_element = doc.createElement("Description");
description_element.appendChild(
doc.createTextNode(replacespecial(result.getString("Description"))));
root.appendChild(description_element);
}
/* // location
Element location_element = doc.createElement("Location");
location_element.appendChild(doc.createTextNode((replacespecial(result.getString("Location")))));
root.appendChild(location_element);
// country
Element country_element = doc.createElement("Country");
country_element.appendChild(doc.createTextNode((replacespecial(result.getString("Country")))));
root.appendChild(country_element);
// started
Element started_elem = doc.createElement("Started");
started_elem.appendChild(doc.createTextNode(convertDate(result.getString("Started"), "yyyy-MM-dd HH:mm:ss", "MMM-dd-yy HH:mm:ss")));
root.appendChild(started_elem);
// ends
Element ends_element = doc.createElement("Ends");
ends_element.appendChild(doc.createTextNode(convertDate(result.getString("Ends"), "yyyy-MM-dd HH:mm:ss", "MMM-dd-yy HH:mm:ss")));
root.appendChild(ends_element);*/
/* // Build Bid Elements
Statement bidstatement = conn.createStatement();
ResultSet bidresult = bidstatement.executeQuery("SELECT * "
+ "FROM Auctions, AuctionUser "
+ "WHERE Auctions.Item_Id = " + itemId + " "
+ "AND Auctions.User_ID = AuctionUser.User_ID");
Element bids_element = doc.createElement("Bids");
while (bidresult.next()) {
try {
Element bid_element = doc.createElement("Bid");
Element bidder_element = doc.createElement("Bidder");
bidder_element.setAttribute("UserID", replacespecial(bidresult.getString("User_ID")));
// bidder_element.setAttribute("Rating", bidresult.getString("Rating"));
// Add Location and Country elements if they aren't NULL
if (!bidresult.getString("Location").equals("")) {
Element location_element = doc.createElement("Location");
location_element.appendChild(doc.createTextNode((replacespecial(bidresult.getString("Location")))));
bidder_element.appendChild(location_element);
}
if (!bidresult.getString("Country").equals("")) {
Element country_element = doc.createElement("Country");
country_element.appendChild(doc.createTextNode((replacespecial(bidresult.getString("Country")))));
bidder_element.appendChild(country_element);
}
bid_element.appendChild(bidder_element);
// time
Element time_element = doc.createElement("Time");
time_element.appendChild(doc.createTextNode(convertDate(bidresult.getString("Bid_Time"), "yyyy-MM-dd HH:mm:ss", "MMM-dd-yy HH:mm:ss")));
bid_element.appendChild(time_element);
// amount
Element amount_element = doc.createElement("Amount");
amount_element.appendChild(doc.createTextNode(bidresult.getString("Bid_Time")));
bid_element.appendChild(amount_element);
bids_element.appendChild(bid_element);
} catch (SQLException e) {
System.out.println(e);
}
}
root.appendChild(bids_element);
bidresult.close();
bidstatement.close();*/
/* // Get the Seller data
Statement sellstatement = conn.createStatement();
ResultSet sellres = sellstatement.executeQuery("SELECT User_ID, Rating, Location, Country "
+ "FROM Items, AuctionUser "
+ "WHERE Items.Item_ID" + " = " + itemId + " "
+ "AND Items.Seller_Id = AuctionUser.User_ID");
sellres.first();
// seller
Element sellerElem = doc.createElement("Seller");
sellerElem.setAttribute("UserID", (replacespecial(sellres.getString("UserID"))));
sellerElem.setAttribute("Rating", sellres.getString("Rating"));
root.appendChild(sellerElem);
sellres.close();
sellstatement.close();*/
// Write the XML
TransformerFactory newfactory = TransformerFactory.newInstance();
Transformer transform = newfactory.newTransformer();
DOMSource source = new DOMSource(doc);
StringWriter writer = new StringWriter();
StreamResult res = new StreamResult(writer);
transform.setOutputProperty(OutputKeys.INDENT, "yes");
transform.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "2");
transform.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
transform.transform(source, res);
xmlstore = writer.toString();
xmlstore = replacespecial1(xmlstore);
}
result.close();
statement.close();
conn.close();
} catch (SQLException e) {
System.out.println(e);
} catch (ParserConfigurationException e) {
System.out.println("oops");
} catch (TransformerException e) {
System.out.println("oops");
}
return xmlstore;
}
public SearchResult[] advancedSearch(
SearchConstraint[] constraints, int numResultsToSkip, int numResultsToReturn) {
// Handle Lucene querying
Map<String, String> luceneResult = new HashMap<String, String>();
try {
String luceneQuery = buildLuceneQuery(constraints);
System.out.println("lucene Query is " + luceneQuery);
if (!luceneQuery.isEmpty()) {
// Access the lucene index
// Query Parser has no default field because
// query will specify fields.
String parent = new File("").getAbsolutePath();
File path = new File("C:\\Users\\Tony\\workspace\\ebayBidSearchService\\index-directory");
Directory index = FSDirectory.open(path);
IndexReader reader = DirectoryReader.open(index);
IndexSearcher searcher = new IndexSearcher(reader);
parser = new QueryParser("content", new StandardAnalyzer());
// Execute Query
Query q = parser.parse(luceneQuery);
TopDocs hits = searcher.search(q, numResultsToReturn + numResultsToSkip);
ScoreDoc[] sd = hits.scoreDocs;
// Iterators for the hits(matches found)
// And for resultsStore to return
System.out.println("advanced search found " + sd.length + " results");
for (int i = numResultsToSkip;
i < numResultsToReturn + numResultsToSkip && i < sd.length;
i++) {
int docId = sd[i].doc;
Document doc = searcher.doc(docId);
luceneResult.put(doc.get("id"), doc.get("name"));
System.out.println("I found a item ");
}
}
} catch (Exception e) {
System.out.println(e);
}
// Handle SQL querying
Map<String, String> sqlResult = new HashMap<String, String>();
Connection conn = null;
try {
String sqlQuery = buildSqlQuery(constraints);
conn = DbManager.getConnection(true);
Statement stmt = conn.createStatement();
// Execute query
if (sqlQuery.length() > 0) {
ResultSet items = stmt.executeQuery(sqlQuery);
while (items.next()) {
sqlResult.put(items.getString("Item_ID"), items.getString("Item_name"));
}
}
} catch (Exception e) {
System.out.println(e);
}
// Create the final SearchResult
// Determine if we must do an intersection
Set<String> itemIds;
Map<String, String> result;
if (sqlResult.isEmpty()) {
itemIds = luceneResult.keySet();
result = luceneResult;
} else if (luceneResult.isEmpty()) {
itemIds = sqlResult.keySet();
result = sqlResult;
} else {
// In this case do an intersection of the keys
itemIds = luceneResult.keySet();
itemIds.retainAll(sqlResult.keySet());
result = new HashMap<String, String>();
for (String item : luceneResult.keySet()) {
result.put(item, luceneResult.get(item));
}
for (String item : sqlResult.keySet()) {
result.put(item, sqlResult.get(item));
}
}
// Add the keys and itemIds to the SearchResult array
SearchResult[] resultstore = new SearchResult[itemIds.size()];
Iterator<String> i = itemIds.iterator();
int resultIndex = 0;
while (i.hasNext()) {
String itemId = i.next();
resultstore[resultIndex] = new SearchResult(itemId, result.get(itemId));
resultIndex++;
}
return resultstore;
}