Database development
in [Java Programming]
|
Prev: Null pointer issues
Next: Urgent
From: Tom Anderson on 27 Apr 2010 14:57 On Mon, 26 Apr 2010, Zlatko ?uri? wrote: > On 04/26/2010 11:03 PM, Martin Gregorie wrote: >> On Mon, 26 Apr 2010 20:41:11 +0100, Tom Anderson wrote: >> >>> I suspect documents may have more than one keyword, in which case your >>> query might look like: >> >> I suspect you're right, unless the keywords are pulled out to form a >> unique list in its own table with a M:M relationship to Document, which >> be useful in some circumstances. However, I didn't persue that since >> what I really wanted to illustrate was the benefits of indexing heavily >> searched non-key columns in the right circumstances and of generating >> result sets that don't contain irrelevant data. Both seemed to be >> points that hadn't occurred to the OP judging by his comment about >> searching out required rows from the result set. > > Hmm. My docs and folders are all "nodes". Stored in the node table. > Nodes have metadata - such as authors, keywords, document id's, > departments, this and that. Those are stored in the metadata table. That sounds like a pretty bad idea. It's not playing to an RDBMS's strengths at all. I take it you can't change this? > Now, to get all the info about one doc, you have to get it's node ID, > and the go to the metadata table, and find out the metadata that means > "parent node", get it's id, then get this parents' children (to get > other documents in this set), then get all those documents and search > for some keywords in those documents. You can still do this with joins in one big query: SELECT document.node_id FROM nodes AS document, metadata AS parentage, metadata AS folder_data, metadata AS content WHERE document.node_id = parentage.node_id AND parentage.key = 'parentNode' AND parentage.value = folder_data.node_id AND folder_data.key = 'name' AND folder_data.value = ? AND document.node_id = content.node_id AND content.key = 'content' AND content.value LIKE ? Remember to tip your query optimiser generously beforehand, though. tom -- Get my pies out of the oven!
From: Tom Anderson on 27 Apr 2010 15:10 On Mon, 26 Apr 2010, Arne Vajh?j wrote: > My preference is: if database need to be accessed by apps in different > technology, then it makes sense to put the business logic in SP's - > otherwise I would keep the business logic in the Java code, because that > makes it a lot cheaper to work with a different database - It's worth mentioning that the modern alternative approach here is to put hide the database completely behind the java, and expose the functionality through web services. Rather than having other apps talk to the database directly, they make calls to the java layer. That lets you raise the level of abstraction in the other apps, reuse functionality in the java, and maintain the invariants enforced by the business logic in the java. The downside of this is that whatever it is the other app wants to do has to be supported by the java app, which will invariably mean that developing any app will involve some work on the java app to add the needed capabilities. Mind you, if the DB-centric alternative involves putting logic in stored procedures, then this is no different - external apps will have to wrangle java code instead of PL/SQL or some other such monstrosity. Over time, the java app evolves into more of a service layer - at some point, it makes sense to formally split it into the original app and a service layer. That point might even be right at the start. Also worth mentioning that if you're not afflicted by XMLitis, you can also expose the services through CORBA or one of a myriad of other RPCish mechanisms. Thrift! Protocol buffers! JSON-REST! DCE RPC! CSV-UUCP! > I would keep basic integrity check in the database though. Same here. It should never be necessary, but it's nice to have a backstop. If you can generate the constraints in the database automatically from the code, then it's a no-brainer - would i be right in thinking that DDL generated by popular JPA implementations puts in constraints wherever it can? For example, FOREIGN KEY should be pretty easy. If you've got the new javax.validation annotations on your entities, more constraints could be generated from those too - is anyone doing that? tom -- Get my pies out of the oven!
From: Jim Janney on 27 Apr 2010 19:04 Zlatko Duric <zladuric(a)gmail.com> writes: > On 04/25/2010 06:14 PM, Tom Anderson wrote: > >> And as Arne said, when you're trying to do something unusual, you may be >> outside the limits of what ORM can comfortably do, and you'll be better >> off using straight JDBC. Or perhaps a combination of ORM for any CRUDdy >> / domain logicky bits, and JDBC for complex queries. >> > > I inherited something that uses Hibernate, and I'm thinking about > speeding up a few things. I was just thinking about how it would be > difficult to try to speed all the slow stuff up by replacing all the > hibernate stuff with all JDBC queries, and with my experience there's > no chance I'll be doing this. But this approach (combination of ORM > and JDBC) sounds very interesting to me. > > Now, my data is all objects - that suits me perfectly. But there is > some information about all those objects I'd like to store in a single > table or maybe two of them, that'd be super-fast to reach, without > having to look for all those parent/children/node/parameters/other > links and without having other issues to think about. I believe that > part of the features would benefit from it a lot in terms of > performance. > > Now, how common is this approach (combination)? Is there something > really important I should read about this, before starting with the > implementation? http://docs.jboss.org/hibernate/stable/core/reference/en/html/queryhql.html#queryhql-select You can query for just the columns (or expressions) you need, without pulling in complete objects. They used to call these projected queries, but I don't see that phrase in the docs now. You can also escape into native SQL. -- Jim Janney
From: Lew on 27 Apr 2010 19:24 Tom Anderson wrote: > You can still do this with joins in one big query: > > SELECT document.node_id > FROM nodes AS document, metadata AS parentage, metadata AS folder_data, > metadata AS content > WHERE > document.node_id = parentage.node_id > AND parentage.key = 'parentNode' > AND parentage.value = folder_data.node_id > AND folder_data.key = 'name' > AND folder_data.value = ? > AND document.node_id = content.node_id > AND content.key = 'content' > AND content.value LIKE ? > > Remember to tip your query optimiser generously beforehand, though. Not only was that a superb pun, it was demmed good advice. The better DBMSes have ways to gather statistics that help them plan queries and other operations. Their efficiency is strongly influenced by the tips these statistics provide. If you don't set these processes up correctly or keep up with the DBMS's usage, your queries tend to run slower. -- Lew
From: Arne Vajhøj on 27 Apr 2010 21:32
On 27-04-2010 04:54, Pitch wrote: > In article<hr3r1v$bvp$2(a)news.albasani.net>, noone(a)lewscanon.com says... >> junw2000(a)gmail.com says... >>>> When I work on database development projects, I use JDBC and SQL. Many >>>> people use hibernate/spring. Can somebody explain the pros and cons of >>>> using JDBC and SQL vs using hibernate/spring on database >>>> developments? >> >> Pitch wrote: >>> I always believed that ORM systems are forcing you to write your own >>> business-rules layer apart from the persistence layer. That way database >>> access is kept simple and easy mantainable. >> >> ORM doesn't force business rules into a separate layer and raw JDBC calls >> don't force them into the same layer as persistence. > > I disagree. Can you explain what prevents you from copying business logic and for that matter presentation logic into Hibernate/JPA data classes? Arne |