Hibernate ‘SELECT DISTINCT’ Question

Here’s an interesting problem for you Hibernate freaks out there. I have two classes: Order and OrderItem where OrderItem has a property ‘orderid’. I need to write a query that returns all the distinct orders that match criteria ‘b’, where ‘b’ could be anything from the date time created, to the order amount to the SKU on the OrderItem AND I need to be able to order the results by any of the columns on the Order class. So I’ve ended up with a Hibernate HQL query that looks something like this:

Query q = null;
Session session = HibernateFactory.currentSession();
String sql = "SELECT DISTINCT o " +
  "FROM com.mycompany.Order AS o, com.mycompany.OrderItem as i " +
  "WHERE   o.id = i.orderid AND " +
  "o.datetimecreated >= :date1 AND " +
  "o.datetimecreated
The problem is that it appears that Hibernate takes my query and turns it into this:

SELECT distinct o.id
FROM myordertable as o, myitemstable as i
WHERE ...
ORDER by o.datetimecreated

but this isn't valid SQL, or at least it's not valid SQL for SQL Server, which returns the message:

Order by items must appear in the select list if SELECT DISTINCT is specified

The workaround is semi-trivial; I put all the columns that might show up in the order by clause in the select list and then instead of iterating over the returned Order objects, I iterate using the row of objects that is created, retrieve the order ID, fetch the order object using the ID and then add the resulting order to my collection:

List list = q.list();
for (int i=0; i
Is there is another way that I'm missing?

Pretty URL’s and Tapestry

User friendly URL’s are a personal pet peeve. The last couple consumer focused web applications I’ve written using Struts used a servlet filter to effective rewrite the URL from something like /articles/pets to /do/articles/pets or /articles/pets.do. This same pet peeve kept from me taking any more than a cursory glance at Tapestry, which (at least in the examples I’ve seen) produce some pretty ugly URL strings. Until today that is… Matt Raible pointed out that some guys just converted a 300 page JSP site to Tapestry and the resulting URL’s are pretty nice looking. You can read about how they did it on the Tapestry user list.

Also of note, in Matt’s comments, Dave Keller pointed out an URL rewriting tool on java.net called urlrewrite that looks like it might come in handy sometime.

SD Times on Hibernate

Allen Holub from SD Times wrote a flattering article on Hibernate in this month’s magazine. He thinks that the “.. main strength of Hibernate is its ease of use.” I’m not sure I want to work on a project involving databases where Hibernate isn’t used and everytime someone whispers that I might have to work on a .NET web application at work, I wonder what the .NET equivalent of Hibernate is. Are .NET people satisfied with this:

String sql = "update newsletter set title = 'New Article'";
String connstring = "...";
SqlConnection sqlcon = new SqlConnection(connstring);
SqlCommand sqlCmd = new SqlCommand (sql, sqlcon);
sqlcon.Open();
sqlCmd.ExecuteNonQuery();
sqlcon.Close();

when you could be doing this:
...
newsletter.setTitle("New Article");
...
getDAO().save(newsletter);

SQL Server 2000 Driver for JDBC: Connection reset by peer

If you’re using the SQL Server 2000 Driver for JDBC and Commons DBCP and you either you see this error in your logs:

java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC]Connection reset by peer: socket write error

and / or your application stops functioning after your SQL Server is restarted or the connection between the your application and the SQL Server is terminated, then you need to add this to your JDNI setup:

<parameter>
  <name>validationQuery</name>
  <value>select $somecolumn FROM $sometable</value>
</parameter>

where the value is a query that returns at least one row from your database. According to the documentation, the validation query is:

“… invoked in an implementation-specific fashion to determine if an instance is still valid to be returned by the pool. It will only be invoked on an “activated” instance.”

In my testing, if I add the validationQuery parameter to my JNDI setup, restart the servlet container (in this case Tomcat), request a page and then stop SQL Server, an error is returned (as expected). After starting SQL Server and requesting another page, the application returns to normal usage (which doesn’t happen if the validationQuery parameter is not present).

ebay web services talk by Jeffrey McManus

A couple weeks ago I attended the Boston .NET User Group to hear Jeffrey McManus give a talk on how ebay is using web services. I took a bunch of notes during his presentation, which I’m putting up here semi-edited. The first part of the talk was mainly about ebay the company; how much business they do per quarter, how many users they have,etc.. The second part was about the ebay API, which I was interested in because the company I’m working for now is exploring a similar program, so you’ll see more detailed notes there:

developer.ebay.com

45000 product categories
collectibles was the first product category on ebay, it is category id = 1
ebay did approximately $5.6 billion in cars last year

105 million registered users as of 3/1/2004
rate of growth is accelerating quarter over quarter
28 countries with a physical presence and localized website
20 million items for sale at any one time…
1 billion items were listed last year
7 gigabits per second outgoing traffic
10 billion api hits in 2004

in december 2003, 1 of every 3 people on the internet came to ebay

1 in 3 used golf clubs bought in the US are purchased on ebay
valueguide.pga.com uses ebay data to help determine the price of used golf equipment

Ebay isn’t just for knick knacks: more than 125,000 keyword searches for Louis Vuitton, Coach and Prada on ebay every day

superpawn.com
— they build a proprietary POS with ebay and significantly reduced costs
— used to cost $23 per item to list & describe each item on ebay (person costs), now approx $.25

highline auctions
— custom system integrator with a business around ebay integration, specifically in cars
— reduced the time it takes to create a professional listing from 30 minutes to under 3 minutes

auctionworks
— solution provider on ebay

use cases
— integration w/ business tools (quickbooks, excel, sap, etc..) (ie: a donation can be estimated for taxes using a data licensing application)
— integration w/ back ends for medium & large retailers, manufacturers

‘we make inefficient markets efficient’
scarce –> in-season reatail –> end of life–> used/vintage
– end of life scenario for retailers
– products at the end of life can be put on ebay programmatically saving companies the most of having to get rid of excess product

top level meta categories
— sports, tickets, etc…
— ‘browseability’ is a one of the challenges
— most likely use cases are sellers listing items programatically

data licensing services
— you can purchase data feeds from ebay so that you can find out what the given price for a given car in a given location is
andale.com: provides ebay pricing and selling recommendations

API

jSearch 1.1

Spent some time this last week making some minor updates to the jSearch codebase. Along the way I decided to get creative and rename it. So jSearch is now called karakoram, after the mountain range that saddles Pakistan and China. The updates include:

  • modified JSP templates to use Struts <html:img /> and <html:image /> tags instead of hardcoding the context into the templates
  • added Hibernate xdoclet tags to automate DDL export and Hibernate mapping and config document creation. Basically I updated the four core objects in karakoram, embedding the Hibernate xdoclet tags so that Ant can automatically create an installation.sql file, the four Hibernate mapping files, and the master Hibernate configuration file (hibernate.cfg.xml).
  • updated hibernate.cfg.xml to run off of a JNDI configured datasource instead of a datasource configured in hibernate.cfg.xml (personal preference: I find that it’s easier to configure the application once in server.xml and then deploy the application without worrying about the datasource configuration)

I’ll post updated installation instructions when I have a chance tomorrow, in the meantime you can download the latest war file by visiting the karakoram homepage.

Rendezvous and Java example

Lots of talk right now about Apple and it’s releasing of Rendezvous (Matt #1, Matt #2, Ted, Marc, Sam) into the wild. I downloaded the Windows Preview and extracted the SDK and was able to get minimally interesting things up and running very easily using Java. Below is a simple class that implements the BrowseListener interface and has utility methods for finding iTunes (protocol = DAAP), HTTP Servers (protocol = HTTP) and printers (protocol = LPR).

// class that implements the BrowseListenr interface
public class TestBrowseListener implements BrowseListener {
 
 DNSSDService myBrowser = null;
 
 public void serviceFound(DNSSDService browser, int flags, int ifIndex,
  String serviceName, String regType, String domain) {
  
  // do stuff when a service is found
  System.out.println("Service Found\n");
  System.out.println("