August 4, 2009 at 4:13 am
Hi there,
We're building a new part cataloguing database and app. The catalogue will probably only go to a max of 7 levels - so no need to worry on the 768 (or whatever it is) level limit. We've have a few hundred catalogues, then all the sub levels, then the mapping to parts. It seems a nice idea to use the hierarchyId in SQL 2008. Having had this mapped previousily as a parent / child relationship, it gets very ugly with reporting, as you have to join on and join on and join on.... to get the data you need.
The app guys here use Java for development, they're using the JDBC driver. Does anyone know how the hierarchyId data type can work with Java? I've been hunting for ages for info. To me it seems that the datatype isn't yet supported in the latest Microsoft JDBC driver. I would like to implement using the datatype but I am a bit worried that they won't be able to do anything with it. Would having an added computed column as VARBINARY type be a good or bad thing? I am thinking exposing it as a string, is a bit wasteful. I'd love to make them use view and stored procs, but it's a long heated debate and they tend to go table direct for reading... they say it fits better in the world of Java as the column types can automatically be exposed and played with, they can build classes faster....
Have people here had to deal with this as yet? Just wandered what thoughts and practices if any are out there...
Many thanks
Kevin
January 20, 2010 at 8:48 am
Doh... having a google for the same topic, came accross SQL Server Central and thought ah now there's the money shot...
Except it's my oringal unanswered question.. deep doh!
January 21, 2010 at 8:23 pm
Ohhh... your developers are lazy. Just kidding... sort of.
FYI, I'm a java developer who deals with SQL Server most often on the back-end.
To be polite, I'll say that MS did not put their best people on the JDBC driver project. If you want something that works, try JTDS. This is an open-source project that is in active development, so you might want to put a feature request for hierarchyid support into their bug-tracker. You might actually get it!
If your developers like how easy it is to map columns to properties, then I can only assume they're using something like Hibernate to do the heavy lifting. Any persistence framework worth its salt will allow you to provide your own select, insert, update and delete statements, including working through stored procedures.
Yes, that requires extra effort.
Further, for many intensive queries, I implement them as stored procedures and allow Hibernate to map the results onto my entities.
Also, so long as you can get to something "standard" like a byte[] (varbinary) or String, then your developers can create a custom Java datatype (say, HierachyId), and a converter that allows the framework to map back and forth.
You might also want to just see what happens when you run either the MS or JTDS driver against a table with a HierarchyId. Does it return something sensible already?
I've seen the SQL traffic generated by my own applications navigating hierarchies. The thought of slashing the number or roundtrips by being able to ask for everything in a tree and have it returned in one fetch... glorious.
Good luck - Jonathan
January 22, 2010 at 2:59 am
Hi Jonathan,
Thanks for the info. Think they managed to hook up the MS driver and could get back strings. They were using the JTDS back in August, but have since dumped it for the MS one as there were some bugs or features it couldn't handle at the time.
Have had a poke around the JTDS site, guess they're still working on some things, but as you say are in active dev. Datetime2 seems to be an issue, which should be simpler than the hierarchyid.
Wow a Java and MS SQL Man, don't get many of you guys around! I completely concur about getting a one shot query rather than multiple hits, or having the app build out the tree.. used to be a web guy..
They were using Hibernate as well... think they've switched to something else now. I did get told all about it, my sorry to say my eyes glazed over a bit. :Whistling:
I've implemented both the id/parentId format as well as the hierarchyid... well just didn't know what would work with the app...
What we've agreed for now is for them to ignore the hierarchyid, and I'll deal with the consequences through an insert/update trigger and an sp.
Admittedly MS haven't quite hit the nail on the head with the hierarchyid - it needs more work by MS with the GetReparentedValue for example... I can fully understand why it's so confusing and thus little documentation out there. Once I've got my code correct I'll put up an article to see if it can help others or be greatly improved.
Thank you very much for your feedback Jonathan
Cheers
Kevin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply