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?

Thanks.

Jack

On 25-04-2010 01:03, Jack wrote:
> 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?

That is a rather big discussion.

The ultra short version is:
- ORM (Hibernate or other) is best when the problem to
   be solved is CRUD of objects
- pure SQL (JDBC) is best when you want to do something
   more unusual

Arne

Jack wrote:
>> When I work on database development projects, I use JDBC and SQL. Many
>> people use hibernate [sic]/spring [sic]. Can somebody explain the pros and cons of
>> using JDBC and SQL vs using hibernate [sic]/spring [sic] on database
>> developments?

Arne Vajhøj wrote:
> That is a rather big discussion.
> 
> The ultra short version is:
> - ORM (Hibernate or other) is best when the problem to
>   be solved is CRUD of objects
> - pure SQL (JDBC) is best when you want to do something
>   more unusual

Hibernate, as Arne pointed out, is not the only ORM (Object-Relational 
Mapping) tool.  The biggies include also OpenJPA and EclipseLink.

They all support and implement the Java Persistence API (JPA), the Java 
standard for ORM.

JPA is useful for more than mere CRUD, but that is the most common use case.

JPA does not require Spring at all.  "Hibernate/Spring" is not one thing nor 
one word.

Strictly speaking, JPA is not for database development.  It is for persistent 
object development.  The point of view of JPA is object orientation, quite 
different from the set logic of SQL.

SQL is best when you need a relational view of data.  JPA is best when you 
need an object view of objects.

Obviously JPA sits atop JDBC, so there is no real dichotomy between the two. 
The question is roughly equivalent to the decision between a low-level library 
and a higher-level one.

-- 
Lew

On Sun, 25 Apr 2010, Arne Vajh?j wrote:

> On 25-04-2010 01:03, Jack wrote:
>
>> 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?
>
> That is a rather big discussion.
>
> The ultra short version is:
> - ORM (Hibernate or other) is best when the problem to
>  be solved is CRUD of objects
> - pure SQL (JDBC) is best when you want to do something
>  more unusual

I'd rephrase that slightly to say that ORM is best when you want to deal 
with your data as objects - when you need to be able to call methods, 
traverse object graphs, and generally think of your data as objects.

If your data is something that isn't usefully thought of as objects 
(perhaps a big boring spew of temperature measurements over time or 
something) then there isn't much benefit to ORM. There's probably no real 
harm either, so if you prefer ORM, you can still use it.

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.

tom

-- 
The literature is filled with bizarre occurrances for which we have
no explanation

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?

TIA

-- 
Zlatko

On 25-04-2010 12:44, Zlatko Duric wrote:
> 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?

If you are accessing the data as objects, then I don't think
that switching from Hibernate to raw JDBC is the right direction
to optimize the code.

Instead you should focus on tuning Hibernate and the databases
itself.

Hibernate can be slow and Hibernate can be fast. It all depends
on the guy writing the code.

Arne

On 25-04-2010 12:14, Tom Anderson wrote:
> On Sun, 25 Apr 2010, Arne Vajh?j wrote:
>
>> On 25-04-2010 01:03, Jack wrote:
>>
>>> 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?
>>
>> That is a rather big discussion.
>>
>> The ultra short version is:
>> - ORM (Hibernate or other) is best when the problem to
>> be solved is CRUD of objects
>> - pure SQL (JDBC) is best when you want to do something
>> more unusual
>
> I'd rephrase that slightly to say that ORM is best when you want to deal
> with your data as objects - when you need to be able to call methods,
> traverse object graphs, and generally think of your data as objects.
>
> If your data is something that isn't usefully thought of as objects
> (perhaps a big boring spew of temperature measurements over time or
> something) then there isn't much benefit to ORM. There's probably no
> real harm either, so if you prefer ORM, you can still use it.
>
> 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.

It is possible to mix different persistence technologies, but it raises
lots of potential consistency issues. I would avoid it if possible.

Arne

Zlatko Duric wrote:

> 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 ...

> Now, how common is this approach (combination)? Is there something 
> really important I should read about this, before starting with the 
> implementation?


As far as I know, de-normalizing a database for faster access is very 
common, as long as you started with a good normalized design, and you 
document carefully what you denormalize, and you measure carefully the 
performance boost and can justify the extra maintenance.

I don' have any links handy, but if you Google for "database 
denormalization optimization" there seems to be plenty of info.  I'd try 
some standard techniques for denormalization first, rather than try to 
improvise something.

On 25-04-2010 13:35, markspace wrote:
> Zlatko Duric wrote:
>> 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 ...
>
>> Now, how common is this approach (combination)? Is there something
>> really important I should read about this, before starting with the
>> implementation?
>
> As far as I know, de-normalizing a database for faster access is very
> common, as long as you started with a good normalized design, and you
> document carefully what you denormalize, and you measure carefully the
> performance boost and can justify the extra maintenance.
>
> I don' have any links handy, but if you Google for "database
> denormalization optimization" there seems to be plenty of info. I'd try
> some standard techniques for denormalization first, rather than try to
> improvise something.

It is very common to denormalise databases for "performance".

I have a strong suspicion that in more than 90% of cases it
is unwarranted.

Databases are extremely optimized to do joins efficiently.

If the logical and physical design is good then joins is
usually not the problem.

Even if it is a problem, then the specific databases may
offer the possibility of materialized views to solve the
problem.

Arne

Zlatko Duric wrote:
>>> 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 ...

Wrong approach.

>>> Now, how common is this approach (combination)? Is there something

Common doesn't mean correct.

>>> really important I should read about this, before starting with the
>>> implementation?

Yes.  Read about why normalization is important in the first place.  Read 
about the things others have mentioned, like (materialized) views.  Read about 
why "premature optimization is the root of all evil."

Whatever you do to "optimize" won't, at least not unless you actually 
*measure* performance before and after your so-called "optimizations" under 
realistic loads and field conditions.

Don't forget to take into account the cost of the increased code complexity 
for denormalized structures, and compare that to the cost of keeping data 
normalized.  Don't forget to take into account the actuarial cost of the risk 
to your data from the denormalization.

Better yet, stick with best practices.

markspace wrote:
>> As far as I know, de-normalizing a database for faster access is very
>> common, as long as you started with a good normalized design, and you
>> document carefully what you denormalize, and you measure carefully the
>> performance boost and can justify the extra maintenance.
>>
>> I don't have any links handy, but if you Google for "database
>> denormalization optimization" there seems to be plenty of info. I'd try
>> some standard techniques for denormalization first, rather than try to
>> improvise something.

Arne Vajhøj wrote:
> It is very common to denormalise databases for "performance".

OP:  Notice how some of us put "performance" in quotation marks?  There's a 
good reason for that.

> I have a strong suspicion that in more than 90% of cases it
> is unwarranted.

You are being kind.

> Databases are extremely optimized to do joins efficiently.
> 
> If the logical and physical design is good then joins is
> usually not the problem.
> 
> Even if it is a problem, then the specific databases may
> offer the possibility of materialized views to solve the
> problem.

When one denormalizes a database for "performance", one usually winds up with 
none of the expected performance gains and all of the expected increase in 
risk to the data.

At least, one ought to expect that risk.  The purpose of normalizing a 
database is to prevent data anomalies and enforce data constraints. 
Denormalize and you screw that up.

As for ORM efficiency, as Arne pointed out:
> Hibernate [or any other ORM framework] can be slow 
> and Hibernate can be fast.
> It all depends on the guy writing the code. 

Properly written, JPA code is no slower than raw SQL coding, takes less time 
to develop and maintain (part of the cost-benefit equation, folks!), and is a 
much more natural fit to the object model of the application.

Furthermore, JPA frameworks offload much of the management effort for 
persistent storage connections and O-R mapping.  This is similar to how 
managed-memory environments like the JVM and .Net offload the effort, expense 
and risk of memory management from the programmer.  Don't give that up lightly.

Beyond that, Hibernate and other JPA frameworks lend themselves well to 
inbuilt and outboard cache approaches.  Out of the box, JPA gives you a "level 
one" cache (a.k.a. a "session") that will help optimize interaction with the 
database.

If you're looking to optimize database access, it is by far much more 
productive to pay attention to things like client- and server-side statement 
preparation, scope and lifecycle of 'EntityManager' instances, database tuning 
parameters (such as work memory or whatever the DBMS calls it), connection 
pooling, disk speed (use high-rotational-speed drives in a RAID array with a 
battery-backed RAID controller), scalability, concurrency, indexes, 
partitioning (database, not disk), and other adjustments that will improve 
performance WITHOUT TOTALLY HOSING YOUR DATA MODEL.

A good object model that caches well without concurrency bottlenecks will 
scale well to additional hardware and provide much more performance than a 
messed-up data model, without the risks of the latter.

I've been part of performance optimization efforts for databases a number of 
times.  Denormalization usually has hurt, not helped.  In one case that I 
recall fondly, the denormalized structure caused a quadratic increase in 
processing time with data quantity, rather than the linear increase a 
normalized database would have provided (and did, when they finally accepted 
my recommendation to normalize, but not before causing a major problem with 
their customer that got the project manager replaced and nearly cost the 
contract).

Program run time is rarely the dominant cost in a project.

Code correctly and well first.  That will almost always give sufficient 
performance.  If not, MEASURE and optimize and MEASURE again, focusing first 
and foremost on things that don't mess you up by harming data integrity, 
maintainability or scalability.

-- 
Lew

On 04/25/2010 04:37 PM, Arne Vajh=F8j wrote:
> On 25-04-2010 01:03, Jack wrote:
>> 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?
>=20
> That is a rather big discussion.
>=20
> The ultra short version is:
> - ORM (Hibernate or other) is best when the problem to
>   be solved is CRUD of objects
> - pure SQL (JDBC) is best when you want to do something
>   more unusual

Notably manipulating large volumes of data.  While there are some=20
features in JPA and likes working efficiently with large volumes of data =

often requires exploiting features of the particular RDBMS at hand.=20
That soon becomes awkward if you want to do it through a ORM.

And one downside of using Hibernate, JPA or any other ORM tool: these=20
tools hide the often quoted impedance mismatch between the object world=20
and the database - which is good because that is precisely what they=20
were invented for.  The danger here is to use a persistent store=20
mindlessly just like objects in memory which has a good chance of=20
leading to awful performance in certain situations.  In a way you could=20
say the hiding works but not for every use case and sometimes you're=20
crossing from "works" to "major nuisance" without noticing it.  Bottom=20
line, with Hibernate as well as with any other tool, you should know it=20
and its weaknesses (which to a certain extent makes the "hiding" moot).

Kind regards

	robert

--=20
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

On 25-04-2010 14:30, Robert Klemme wrote:
> On 04/25/2010 04:37 PM, Arne Vajh�j wrote:
>> On 25-04-2010 01:03, Jack wrote:
>>> 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?
>>
>> That is a rather big discussion.
>>
>> The ultra short version is:
>> - ORM (Hibernate or other) is best when the problem to
>> be solved is CRUD of objects
>> - pure SQL (JDBC) is best when you want to do something
>> more unusual
>
> Notably manipulating large volumes of data. While there are some
> features in JPA and likes working efficiently with large volumes of data
> often requires exploiting features of the particular RDBMS at hand. That
> soon becomes awkward if you want to do it through a ORM.
>
> And one downside of using Hibernate, JPA or any other ORM tool: these
> tools hide the often quoted impedance mismatch between the object world
> and the database - which is good because that is precisely what they
> were invented for. The danger here is to use a persistent store
> mindlessly just like objects in memory which has a good chance of
> leading to awful performance in certain situations. In a way you could
> say the hiding works but not for every use case and sometimes you're
> crossing from "works" to "major nuisance" without noticing it. Bottom
> line, with Hibernate as well as with any other tool, you should know it
> and its weaknesses (which to a certain extent makes the "hiding" moot).

I don't think the big benefits of ORM (Hibernate or JPA or one of the
alternatives) are in the writing of the code. It still requires
somebody that knows both the ORM framework and the database well
to write really efficient code.

The big benefits are for reading the code. Everyone can read the
the code using ORM and immediately understand what it does without
looking at tons of code that uses JDBC and SQL. It is maintenance
friendly.

Arne

On Apr 25, 9:58=A0am, Arne Vajh=F8j <a...@vajhoej.dk> wrote:
> On 25-04-2010 12:44, Zlatko Duric wrote:
>
>
>
> > 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 bette=
r
> >> off using straight JDBC. Or perhaps a combination of ORM for any CRUDd=
y
> >> / 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 n=
o
> > 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 som=
e
> > 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?
>
> If you are accessing the data as objects, then I don't think
> that switching from Hibernate to raw JDBC is the right direction
> to optimize the code.
>
> Instead you should focus on tuning Hibernate and the databases
> itself.
>
> Hibernate can be slow and Hibernate can be fast. It all depends
> on the guy writing the code.
>
> Arne

To use JDBC and SQL, I need to handle trial things, like commit,
rollback etc. How about hibernate/spring?

Jack wrote:
> To use JDBC and SQL, I need to handle trial things, like commit,
> rollback etc. How about hibernate [sic]/spring [sic]?

Hibernate and Spring are two completely different products.

In Hibernate and other JPA products you do have to handle transactions.  I 
don't know about Spring.

-- 
Lew

On 04/25/2010 08:09 PM, Lew wrote:
> Zlatko Duric wrote:
>>>> 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 ...
>
> Wrong approach.

Well, I don't mean to change the model.

I want to keep and use all this stuff that's already in there.
But there are few things I think can benefit from an approach like this:

- reports
	there are a few "reports" being produced from the system. For some 
10000 objects, there are 200k mysql queries - I am not much into big 
systems (yet) but this seems waaaaaaaaaaay too much to me. If I had the 
data from this in an additional table (I know this means duplicating 
some data), I could filter out what I need in one single query.
- lists
	when accessing the data, users only fetch a certain small amount of 
objects - 15, 20, maybe 100 at a time. So to avoid most of the node 
links and stuff, I could get a quick filter of the data needed, without 
having to join this and that.

Now, I know there are some disadvantages too, like having to worry 
whether I update the object and the table at the same time or so.
I also have to make sure that all the data is in sync, since it's now 
duplicated. But I still can't resist wondering if this would be a good 
option.

Luckily I still have a couple of months work already planned, so I get 
to think about it :P

Thanks anyway
-- 
Zlatko

In article <3d3dbd92-01d3-4ad7-bb94-3855c7e2b221
@y6g2000prk.googlegroups.com>, junw2000@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?

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.

Also, this multi-tier architecture allows for easier load-balancing, 
architecture changes, integration with other systems, development..



-- 
stirr your cofee properly

Zlatko Duric wrote:
>>>>> 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 ...

Lew wrote:
>> Wrong approach.

Zlatko Duric wrote:
> Well, I don't mean to change the model.
> 
> I want to keep and use all this stuff that's already in there.
> But there are few things I think can benefit from an approach like this:
> 
> - reports
>     there are a few "reports" being produced from the system. For some 
> 10000 objects, there are 200k mysql queries - I am not much into big 
> systems (yet) but this seems waaaaaaaaaaay too much to me. If I had the 
> data from this in an additional table (I know this means duplicating 
> some data), I could filter out what I need in one single query.

You can do that with views or join queries without duplicating the data.

> - lists
>     when accessing the data, users only fetch a certain small amount of 
> objects - 15, 20, maybe 100 at a time. So to avoid most of the node 
> links and stuff, I could get a quick filter of the data needed, without 
> having to join this and that.

What do you mean by "most of the node links and stuff"?

Duplicated data will require more accesses, code complication and risk than 
"to join this and that".

> Now, I know there are some disadvantages too, like having to worry 
> whether I update the object and the table at the same time or so.
> I also have to make sure that all the data is in sync, since it's now 
> duplicated. But I still can't resist wondering if this would be a good 
> option.

No.

> Luckily I still have a couple of months work already planned, so I get 
> to think about it :P

Time isn't necessary, correct thinking is.

-- 
Lew

junw2000@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.

> Also, this multi-tier architecture allows for easier load-balancing, 
> architecture changes, integration with other systems, development..

-- 
Lew

On 04/26/2010 12:45 PM, Lew wrote:

>> there are a few "reports" being produced from the system. For some
>> 10000 objects, there are 200k mysql queries - I am not much into big
>> systems (yet) but this seems waaaaaaaaaaay too much to me. If I had
>> the data from this in an additional table (I know this means
>> duplicating some data), I could filter out what I need in one single
>> query.
>
> You can do that with views or join queries without duplicating the data.

Maybe I could, if I knew what am I looking for in the first place :)

>> - lists
>> when accessing the data, users only fetch a certain small amount of
>> objects - 15, 20, maybe 100 at a time. So to avoid most of the node
>> links and stuff, I could get a quick filter of the data needed,
>> without having to join this and that.
>
> What do you mean by "most of the node links and stuff"?

Well, my objects are documents - those are "nodes". But so are the 
"folders" holding the documents. And so are their parents. And I want, 
for example, all the docs that have the keyword FOO and their parent is 
"Reports".

That's messy - iterate through all the folders to find the stuff I need.


>
> Duplicated data will require more accesses, code complication and risk
> than "to join this and that".
>
>> Now, I know there are some disadvantages too, like having to worry
>> whether I update the object and the table at the same time or so.
>> I also have to make sure that all the data is in sync, since it's now
>> duplicated. But I still can't resist wondering if this would be a good
>> option.
>
> No.
>
>> Luckily I still have a couple of months work already planned, so I get
>> to think about it :P
>
> Time isn't necessary, correct thinking is.
>

That's why I'm posting here, so you can do my thinking :)


-- 
Zlatko

On Mon, 26 Apr 2010 13:37:46 +0200, Zlatko Duric wrote:

> 
> Well, my objects are documents - those are "nodes". But so are the
> "folders" holding the documents. And so are their parents. And I want,
> for example, all the docs that have the keyword FOO and their parent is
> "Reports".
>
Thats what additional indexes are for. For example, to support the 
following example you'd want indexes on doc.keyword and folder.name, 
since they'll probably be used often for data selection and sorting. 
We'll assume that the DB designer was sensible and put indexes on both 
table's prime keys. Your example requirement could be satisfied with a 
single query, which would involve a prime key join and use the additional 
indexes to select the rows to be included in the dataset. Something like 
this:

SELECT required fields
from folder f, document d 
where f.key = d.folderkey
  and d.keyword = 'FOO'
  and f.name = 'Reports';

Any decent RDBMS should be able to optimise that type of query and would 
return just the required result set.
 
> That's messy - iterate through all the folders to find the stuff I need.
> 
Why would you need to do that? A correctly written query will only return 
the data you need, present it in the order you want and not slow database 
updates down by requiring additional reference tables which must be 
maintained.


-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

On Mon, 26 Apr 2010, Martin Gregorie wrote:

> On Mon, 26 Apr 2010 13:37:46 +0200, Zlatko Duric wrote:
>
>> Well, my objects are documents - those are "nodes". But so are the
>> "folders" holding the documents. And so are their parents. And I want,
>> for example, all the docs that have the keyword FOO and their parent is
>> "Reports".
>
> Thats what additional indexes are for. For example, to support the
> following example you'd want indexes on doc.keyword and folder.name,
> since they'll probably be used often for data selection and sorting.
> We'll assume that the DB designer was sensible and put indexes on both
> table's prime keys. Your example requirement could be satisfied with a
> single query, which would involve a prime key join and use the additional
> indexes to select the rows to be included in the dataset. Something like
> this:
>
> SELECT required fields
> from folder f, document d
> where f.key = d.folderkey
>  and d.keyword = 'FOO'
>  and f.name = 'Reports';
>
> Any decent RDBMS should be able to optimise that type of query and would
> return just the required result set.

I suspect documents may have more than one keyword, in which case your 
query might look like:

SELECT required fields
from folder f, document d
where f.key = d.folderkey
  and d.keyword LIKE '%FOO%' -- if keywords are packed space-separated into one column (bad idea)
  and f.name = 'Reports';

Or:

SELECT required fields
from folder f, document d, keyword k
where f.key = d.folderkey and d.key = k.documentkey
  and k.word = 'FOO' -- if keyword is a relation (documentkey, word)
  and f.name = 'Reports';

Or even:

SELECT required fields
from folder f, document d, document_keywords dk, keyword k
where f.key = d.folderkey and d.key = dk.documentkey and dk.keywordkey = k.key
  and k.word = 'FOO' -- if keywords are first-class and there is a join table (fully normalised but overcomplicated)
  and f.name = 'Reports';

>> That's messy - iterate through all the folders to find the stuff I need.
>
> Why would you need to do that? A correctly written query will only 
> return the data you need, present it in the order you want and not slow 
> database updates down by requiring additional reference tables which 
> must be maintained.

I think the OP is saying he would have to navigate in that way if he was 
using ORM rather than SQL. That still isn't true, though, because in JPA 
you have JP-QL, which is more or less isomorphic to SQL, and in fact 
simpler, because the mapping of relationships to properties makes joins 
easier to express:

select doc from Document doc
where
  'FOO' in doc.keywords
  and doc.folder.name = 'Reports'

(i think)

tom

-- 
Everybody with a heart votes love

Tom Anderson  wrote:
> I think the OP is saying he would have to navigate in that way if he was
> using ORM rather than SQL. That still isn't true, though, because in JPA
> you have JP-QL, which is more or less isomorphic to SQL, and in fact
> simpler, because the mapping of relationships to properties makes joins
> easier to express:
>
> select doc from Document doc
> where
> =A0 'FOO' in doc.keywords
> =A0 and doc.folder.name =3D 'Reports'
>
> (i [sic] think)
>

What you wrote looks correct, but I am fairly certain it requires
'keywords' to be expressed as a collection (probably a 'Set'), which
is best done if the keywords are in their own table, which they should
be anyway.  I don't think it works with the space-separated list of
keywords as in your first of three examples.

Your second example, as you hint, is probably optimal and the third
overkill.

--
Lew

On 04/26/2010 02:14 AM, Arne Vajh=F8j wrote:

> I don't think the big benefits of ORM (Hibernate or JPA or one of the
> alternatives) are in the writing of the code. It still requires
> somebody that knows both the ORM framework and the database well
> to write really efficient code.
>=20
> The big benefits are for reading the code. Everyone can read the
> the code using ORM and immediately understand what it does without
> looking at tons of code that uses JDBC and SQL. It is maintenance
> friendly.

So you are saying that I need skilled people to write the initial code=20
and can give maintenance to less skilled people because the ORM using=20
code is easy to read?  I am not sure that is a good strategy.  Over time =

software tends to decay because more and more bug fixes are applied and=20
features added.  If only the people knew internals of ORM that wrote the =

initial code I see a good chance that maintainers wreck havoc on the=20
performance and potentially the whole application if they change /=20
extend the easy readable code without knowing the tool they are using.=20
Even a change as seemingly simple as that of a field type from "int" to=20
"String" might have dramatic consequences.  And just think of the woes=20
of schema migration: if you have an installed base you urgently need=20
someone who understands the DB underneath and the ORM tool to come up=20
with a feasible migration strategy that.

Btw, did I mention that I believe database independence is a myth? :-)

Kind regards

	robert

--=20
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

  This message is in MIME format.  The first part should be readable text,
  while the remaining parts are likely unreadable without MIME-aware tools.

---910079544-1410690323-1272313580=:14878
Content-Type: TEXT/PLAIN; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8BIT

On Mon, 26 Apr 2010, Lew wrote:

> Tom Anderson  wrote:
>
>> I think the OP is saying he would have to navigate in that way if he 
>> was using ORM rather than SQL. That still isn't true, though, because 
>> in JPA you have JP-QL, which is more or less isomorphic to SQL, and in 
>> fact simpler, because the mapping of relationships to properties makes 
>> joins easier to express:
>>
>> select doc from Document doc
>> where
>> � 'FOO' in doc.keywords
>> � and doc.folder.name = 'Reports'
>>
>> (i [sic] think)
>
> What you wrote looks correct, but I am fairly certain it requires 
> 'keywords' to be expressed as a collection (probably a 'Set'), which is 
> best done if the keywords are in their own table, which they should be 
> anyway.  I don't think it works with the space-separated list of 
> keywords as in your first of three examples.

I believe you're absolutely right - sorry, i should have been clearer on 
what context that query required.

> Your second example, as you hint, is probably optimal and the third 
> overkill.

Probably. The advantage of the fully normal design is that queries like:

"find me all the documents with keywords with 'brew' in them"

are fast, because doing that in any schema involves a LIKE over the 
keyword text, which more or less means a table scan, and the keyword table 
in that design is smaller than in the second one (proportional to the 
number of distinct keywords, not the sum of the number of keywords over 
all documents), and much smaller than in the first one, where it's the 
whole table.

But then, if you have full-text indexing, you can search the packed string 
without doing a table scan. Although that's not necessarily fast enough:

http://www.pui.ch/phred/archives/2005/06/tagsystems-performance-tests.html

But then, you can full-text index the more normal schemas too:

http://www.opensymphony.com/compass/content/about.html

Er, so, yeah, anyway, glad to have entirely eliminated the OP's confusion, 
i'm sure.

tom

-- 
Mass motoring effects an absolute triumph of bourgeois ideology on the
level of daily life. It gives and supports in everyone the illusion
that each individual can seek his or her own benefit at the expense of
everyone else. -- Andre Gorz
---910079544-1410690323-1272313580=:14878--

On 04/26/2010 12:48 PM, Lew wrote:
> 
> junw2000@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.

Exactly.  I believe there is a fundamental dilemma that I haven't seen a 
satisfying solution to: with a relational database and an object 
oriented (or not) application which implements the business logic you 
automatically have a distribution of business rules between several tiers.

If you manage to place all business consistency rules into the database 
(which is often impossible because either of limitations of the DB or 
complexity of the model) you leave very little for the application layer 
(mostly presentation) so you might wonder why not directly implement all 
the business logic in PL/SQL or T-SQL (just to name two well known 
brands).  Advantage is that you cannot break the model if you need to do 
changes in the DB (this can happen for migration, repair or other one 
off tasks).

If you place all the rules in the application consequently you would 
have to even get rid of foreign keys.  Downside is of course that you 
now have zero consistency enforcement for the data model in the DB (e.g. 
during all those tasks mentioned above) and you are only using 10% of a 
potentially expensive installation (in the case of a commercial RDBMS).

In reality I have often seen a mix between the two approaches: some 
consistency checking (FK, PK, CHECK constraints, triggers) is done in 
the database and the "rest" of the business logic lives in the 
application tier.  This may actually be the worst approach: it's not 
only that you don't have a single place where the business model is 
consistently defined and enforced - that is merely a violation of some 
form of purity rule (which _does_ have its advantages).  But this might 
also make people feel safe when they change the database while breaking 
business rules that live elsewhere...

We probably should get rid of persistence altogether - maybe Alzheimer 
DB or WORN is the future. :-)

>> Also, this multi-tier architecture allows for easier load-balancing, 
>> architecture changes, integration with other systems, development..

Pitch, you can have multi tier with JDBC and ORM - you can even have 
multi tier without persistence altogether.

Kind regards

	robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

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.
    

-- 
martin@   | Martin Gregorie
gregorie. | Essex, UK
org       |

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.

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.



-- 
Zlatko

Martin Gregorie wrote:
>> 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.

Zlatko Đurić wrote:
> Hmm. My docs and folders are all "nodes". Stored in the node table. 
> Nodes have metadata - such as authors, keywords, document id's [sic], 
> departments, this and that.  Those are stored in the metadata table.
> 
> Now, to get all the info about one doc, you have to get it's [sic] node ID, 
> and the go to the metadata table, and find out the metadata that means 
> "parent node", get it's [sic] 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 don't have to do it that way.  You could do it with a correctly set up 
query involving JOINs, which is the point Martin and others are making.

Think set intersection instead of procedure.

-- 
Lew

On 04/27/2010 12:21 AM, Lew wrote:

> You don't have to do it that way. You could do it with a correctly set
> up query involving JOINs, which is the point Martin and others are making.
>

I'm not, the application is.

> Think set intersection instead of procedure.
>

I will, thanks.

-- 
Zlatko

On 26-04-2010 05:14, Pitch wrote:
> In article<3d3dbd92-01d3-4ad7-bb94-3855c7e2b221
> @y6g2000prk.googlegroups.com>, junw2000@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?
>
> I always believed that ORM systems are forcing you to write your own
> business-rules layer apart from the persistence layer.

It is not enforcing anything. Hibernate or any other ORM does not
refuse to persist classes with business logic in.

There is a certain correlation, because when people start to
use ORM then they have also learned about PL-BLL-DAL (let us
ignore the fact that they probably should have learned about
PL-CL-BLL-DAL).

>                                                That way database
> access is kept simple and easy mantainable.
>
> Also, this multi-tier architecture allows for easier load-balancing,
> architecture changes, integration with other systems, development..

layers != tiers

Arne

On 26-04-2010 16:31, Robert Klemme wrote:
> Exactly. I believe there is a fundamental dilemma that I haven't seen a
> satisfying solution to: with a relational database and an object
> oriented (or not) application which implements the business logic you
> automatically have a distribution of business rules between several tiers.
>
> If you manage to place all business consistency rules into the database
> (which is often impossible because either of limitations of the DB or
> complexity of the model) you leave very little for the application layer
> (mostly presentation) so you might wonder why not directly implement all
> the business logic in PL/SQL or T-SQL (just to name two well known
> brands). Advantage is that you cannot break the model if you need to do
> changes in the DB (this can happen for migration, repair or other one
> off tasks).
>
> If you place all the rules in the application consequently you would
> have to even get rid of foreign keys. Downside is of course that you now
> have zero consistency enforcement for the data model in the DB (e.g.
> during all those tasks mentioned above) and you are only using 10% of a
> potentially expensive installation (in the case of a commercial RDBMS).
>
> In reality I have often seen a mix between the two approaches: some
> consistency checking (FK, PK, CHECK constraints, triggers) is done in
> the database and the "rest" of the business logic lives in the
> application tier. This may actually be the worst approach: it's not only
> that you don't have a single place where the business model is
> consistently defined and enforced - that is merely a violation of some
> form of purity rule (which _does_ have its advantages). But this might
> also make people feel safe when they change the database while breaking
> business rules that live elsewhere...

That is a classic dilemma.

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 - I would
keep basic integrity check in the database though.

Arne

On 25-04-2010 22:57, Jack wrote:
> On Apr 25, 9:58 am, Arne Vajh�j<a...@vajhoej.dk>  wrote:
>> On 25-04-2010 12:44, Zlatko Duric wrote:
>>> 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?
>>
>> If you are accessing the data as objects, then I don't think
>> that switching from Hibernate to raw JDBC is the right direction
>> to optimize the code.
>>
>> Instead you should focus on tuning Hibernate and the databases
>> itself.
>>
>> Hibernate can be slow and Hibernate can be fast. It all depends
>> on the guy writing the code.
>
> To use JDBC and SQL, I need to handle trial things, like commit,
> rollback etc. How about hibernate/spring?

You still need to handle your own transactions. That is a very
common feature of persistence frameworks - they don't try to
guess how you want your transactions.

Arne

On 26-04-2010 16:14, Robert Klemme wrote:
> On 04/26/2010 02:14 AM, Arne Vajh�j wrote:
>
>> I don't think the big benefits of ORM (Hibernate or JPA or one of the
>> alternatives) are in the writing of the code. It still requires
>> somebody that knows both the ORM framework and the database well
>> to write really efficient code.
>>
>> The big benefits are for reading the code. Everyone can read the
>> the code using ORM and immediately understand what it does without
>> looking at tons of code that uses JDBC and SQL. It is maintenance
>> friendly.
>
> So you are saying that I need skilled people to write the initial code
> and can give maintenance to less skilled people because the ORM using
> code is easy to read? I am not sure that is a good strategy. Over time
> software tends to decay because more and more bug fixes are applied and
> features added. If only the people knew internals of ORM that wrote the
> initial code I see a good chance that maintainers wreck havoc on the
> performance and potentially the whole application if they change /
> extend the easy readable code without knowing the tool they are using.
> Even a change as seemingly simple as that of a field type from "int" to
> "String" might have dramatic consequences. And just think of the woes of
> schema migration: if you have an installed base you urgently need
> someone who understands the DB underneath and the ORM tool to come up
> with a feasible migration strategy that.

Not quite. I am saying that you may want people that have a clue
about persistence to write and modify the persistence code, but
that developers that does not know about the used ORM framework
or the database will be able to easily read and understand the code
(while working on something else - like the business logic).

> Btw, did I mention that I believe database independence is a myth? :-)

It is a myth that is seen working everyday in the Java world, that
many Java apps using a good ORM (like Hibernate or one
of the JPA implementation) use the same Java code with different
databases. It is not always that easy. But for all the simple
stuff it works well.

Arne

In article <4bd62dfc$0$286$14726298@news.sunsite.dk>, arne@vajhoej.dk 
says...

> My preference is: if database need to be accessed by apps in different
> technology, 

If you use ORM you don't want this, so it's not relevant. Right?



-- 
stirr your cofee properly

Lew <lew@lewscanon.com> writes:

> What you wrote looks correct, but I am fairly certain it requires
> 'keywords' to be expressed as a collection (probably a 'Set'), which
> is best done if the keywords are in their own table, which they should
> be anyway.  I don't think it works with the space-separated list of
> keywords as in your first of three examples.
>
Arguably the keywords should actually be in a full text index in the
RDBMS, and let the proper full text indexing support handle it
instead. That will be more efficient than scanning a table of keywords
joined back to the table of documents, and - potentially - gives support
for near matches as well...

On the other hand, I've no idea if JPA supports full text indexes and if
it does how you'd go about using them...
-- 
Graham Cox

In article <83mbghFjnU1@mid.individual.net>, shortcutter@googlemail.com 
says...

> >> Also, this multi-tier architecture allows for easier load-
balancing, 
> >> architecture changes, integration with other systems, development..
> 
> Pitch, you can have multi tier with JDBC and ORM - you can even have 
> multi tier without persistence altogether.

Yes, but I sad _this_ multi-tier architecture. If you use only JDBC you 
have one tier less.

-- 
stirr your cofee properly

In article <hr3r1v$bvp$2@news.albasani.net>, noone@lewscanon.com says...
> 
> junw2000@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.


-- 
stirr your cofee properly

Pitch wrote:
> In article <hr3r1v$bvp$2@news.albasani.net>, noone@lewscanon.com says...
>> junw2000@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.

The evidence speaks for itself.

-- 
Lew

On 27 Apr., 02:31, Arne Vajh=F8j <a...@vajhoej.dk> wrote:
> On 26-04-2010 16:14, Robert Klemme wrote:
>
>
>
> > On 04/26/2010 02:14 AM, Arne Vajh=F8j wrote:
>
> >> I don't think the big benefits of ORM (Hibernate or JPA or one of the
> >> alternatives) are in the writing of the code. It still requires
> >> somebody that knows both the ORM framework and the database well
> >> to write really efficient code.
>
> >> The big benefits are for reading the code. Everyone can read the
> >> the code using ORM and immediately understand what it does without
> >> looking at tons of code that uses JDBC and SQL. It is maintenance
> >> friendly.
>
> > So you are saying that I need skilled people to write the initial code
> > and can give maintenance to less skilled people because the ORM using
> > code is easy to read? I am not sure that is a good strategy. Over time
> > software tends to decay because more and more bug fixes are applied and
> > features added. If only the people knew internals of ORM that wrote the
> > initial code I see a good chance that maintainers wreck havoc on the
> > performance and potentially the whole application if they change /
> > extend the easy readable code without knowing the tool they are using.
> > Even a change as seemingly simple as that of a field type from "int" to
> > "String" might have dramatic consequences. And just think of the woes o=
f
> > schema migration: if you have an installed base you urgently need
> > someone who understands the DB underneath and the ORM tool to come up
> > with a feasible migration strategy that.
>
> Not quite. I am saying that you may want people that have a clue
> about persistence to write and modify the persistence code, but
> that developers that does not know about the used ORM framework
> or the database will be able to easily read and understand the code
> (while working on something else - like the business logic).

Thank you for the clarification!

> > Btw, did I mention that I believe database independence is a myth? :-)
>
> It is a myth that is seen working everyday in the Java world, that
> many Java apps using a good ORM (like Hibernate or one
> of the JPA implementation) use the same Java code with different
> databases. It is not always that easy. But for all the simple
> stuff it works well.

Hm...  The question is: how simple is "simple" and where does
"complicated" begin?  Just an example, which I would rather place in
the "simple" bucket: assume you want to query for items that have a
field set to null.  Considering that Oracle does not index NULL values
and another RDBMS that you want to use does so, you'll likely end up
with a clutch: either you use a different value for NULL which will
allow for uniform ORM code at the cost of a bad design; or you need to
make the ORM tool create different queries (and I do not mean the
difference between "VARCHAR2" and "VARCHAR") for Oracle and the other
RDBMS.  You could, of course, also live with the FTS in Oracle but I
doubt you'll have much fun doing this on any reasonably large
database. :-)

In any case I believe the complicated stuff is where the fun begins -
so for _me_ DB independence is definitively a myth. ;-)

Kind regards

robert

On 27 Apr., 10:45, Pitch <m...@fake.info> wrote:
> In article <4bd62dfc$0$286$14726...@news.sunsite.dk>, a...@vajhoej.dk
> says...
>
> > My preference is: if database need to be accessed by apps in different
> > technology,
>
> If you use ORM you don't want this, so it's not relevant. Right?

Maybe you do not want it - but you still may need to be able to do it
(DB repair, large volume changes etc.).  Unfortunately reality is
rarely in line with what we like or want - and we still have to deal
with it.

Kind regards

robert

On Tue, 27 Apr 2010, Graham Cox wrote:

> Lew <lew@lewscanon.com> writes:
>
>> What you wrote looks correct, but I am fairly certain it requires
>> 'keywords' to be expressed as a collection (probably a 'Set'), which
>> is best done if the keywords are in their own table, which they should
>> be anyway.  I don't think it works with the space-separated list of
>> keywords as in your first of three examples.
>
> Arguably the keywords should actually be in a full text index in the
> RDBMS, and let the proper full text indexing support handle it
> instead. That will be more efficient than scanning a table of keywords
> joined back to the table of documents

Maybe, maybe not:

http://www.pui.ch/phred/archives/2005/06/tagsystems-performance-tests.html

Depends on the data.

> On the other hand, I've no idea if JPA supports full text indexes and if 
> it does how you'd go about using them...

JPA itself doesn't, AFAIK. However, you can take a JPA-mapped database, 
add a fulltext index on the side, and then access it using native queries. 
It's a leak in the abstraction, and a point of nonportability, but it's 
easily confined to the data access layer, and probably not too big a deal 
in practice.

There are also fulltext search options for JPA that don't use the 
database's own indexing - notably Compass, which indexes a JPA repository 
with Lucene:

http://www.compass-project.org/overview.html

Hibernate also has Hibernate Search, which does much the same thing. I 
understand that its existence is pushing towards the next major version of 
JPA including fulltext search. Whether there will be implementations on 
top of database fulltext search as well as Lucene and similar, i don't 
know.

tom

-- 
Get my pies out of the oven!

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!

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!

Zlatko Duric <zladuric@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

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

On 27-04-2010 04:54, Pitch wrote:
> In article<hr3r1v$bvp$2@news.albasani.net>, noone@lewscanon.com says...
>> junw2000@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

On 27-04-2010 04:45, Pitch wrote:
> In article<4bd62dfc$0$286$14726298@news.sunsite.dk>, arne@vajhoej.dk
> says...
>> My preference is: if database need to be accessed by apps in different
>> technology,
>
> If you use ORM you don't want this, so it's not relevant. Right?

In the unlikely case that it is a decision that can be made, then
it is still a restriction and therefore relevant.

In the more likely case that it is a given, then it is obviously
relevant.

Arne

On 27-04-2010 12:09, Robert Klemme wrote:
> On 27 Apr., 10:45, Pitch<m...@fake.info>  wrote:
>> In article<4bd62dfc$0$286$14726...@news.sunsite.dk>, a...@vajhoej.dk
>> says...
>>
>>> My preference is: if database need to be accessed by apps in different
>>> technology,
>>
>> If you use ORM you don't want this, so it's not relevant. Right?
>
> Maybe you do not want it - but you still may need to be able to do it
> (DB repair, large volume changes etc.).  Unfortunately reality is
> rarely in line with what we like or want - and we still have to deal
> with it.

DBA operations is one thing.

But it is not that uncommon that databases used by Java is
also used by non-Java apps: .NET, C, Cobol, PL/I etc..

Arne

On 27-04-2010 15:10, Tom Anderson wrote:
> 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.

Modern alternative????

I thought it was a classic anti-pattern to expose DAL as
services instead of BLL as services.

After all - this is entity beans remote interface just with a
10 times more inefficient transport format.

Arne

On 27-04-2010 04:48, Pitch wrote:
> In article<83mbghFjnU1@mid.individual.net>, shortcutter@googlemail.com
> says...
>>>> Also, this multi-tier architecture allows for easier load-balancing,
>>>> architecture changes, integration with other systems, development..
>>
>> Pitch, you can have multi tier with JDBC and ORM - you can even have
>> multi tier without persistence altogether.
>
> Yes, but I sad _this_ multi-tier architecture. If you use only JDBC you
> have one tier less.

No.

You have the same tiers with ORM and JDBC and in 99% of cases
the same layers as well.

Arne

On 27-04-2010 12:06, Robert Klemme wrote:
> On 27 Apr., 02:31, Arne Vajh�j<a...@vajhoej.dk>  wrote:
>> On 26-04-2010 16:14, Robert Klemme wrote:
>>> Btw, did I mention that I believe database independence is a myth? :-)
>>
>> It is a myth that is seen working everyday in the Java world, that
>> many Java apps using a good ORM (like Hibernate or one
>> of the JPA implementation) use the same Java code with different
>> databases. It is not always that easy. But for all the simple
>> stuff it works well.
>
> Hm...  The question is: how simple is "simple" and where does
> "complicated" begin?  Just an example, which I would rather place in
> the "simple" bucket: assume you want to query for items that have a
> field set to null.  Considering that Oracle does not index NULL values
> and another RDBMS that you want to use does so, you'll likely end up
> with a clutch: either you use a different value for NULL which will
> allow for uniform ORM code at the cost of a bad design; or you need to
> make the ORM tool create different queries (and I do not mean the
> difference between "VARCHAR2" and "VARCHAR") for Oracle and the other
> RDBMS.  You could, of course, also live with the FTS in Oracle but I
> doubt you'll have much fun doing this on any reasonably large
> database. :-)

How does you solve it by using JDBC?

> In any case I believe the complicated stuff is where the fun begins -
> so for _me_ DB independence is definitively a myth. ;-)

Well - out goal is to make everything as simple as possible.

Arne

On 28 Apr., 03:43, Arne Vajh=F8j <a...@vajhoej.dk> wrote:
> On 27-04-2010 12:06, Robert Klemme wrote:
>
>
>
> > On 27 Apr., 02:31, Arne Vajh=F8j<a...@vajhoej.dk> =A0wrote:
> >> On 26-04-2010 16:14, Robert Klemme wrote:
> >>> Btw, did I mention that I believe database independence is a myth? :-=
)
>
> >> It is a myth that is seen working everyday in the Java world, that
> >> many Java apps using a good ORM (like Hibernate or one
> >> of the JPA implementation) use the same Java code with different
> >> databases. It is not always that easy. But for all the simple
> >> stuff it works well.
>
> > Hm... =A0The question is: how simple is "simple" and where does
> > "complicated" begin? =A0Just an example, which I would rather place in
> > the "simple" bucket: assume you want to query for items that have a
> > field set to null. =A0Considering that Oracle does not index NULL value=
s
> > and another RDBMS that you want to use does so, you'll likely end up
> > with a clutch: either you use a different value for NULL which will
> > allow for uniform ORM code at the cost of a bad design; or you need to
> > make the ORM tool create different queries (and I do not mean the
> > difference between "VARCHAR2" and "VARCHAR") for Oracle and the other
> > RDBMS. =A0You could, of course, also live with the FTS in Oracle but I
> > doubt you'll have much fun doing this on any reasonably large
> > database. :-)
>
> How does you solve it by using JDBC?

Well, you would either wrap the query in a stored procedure or use
custom SQL per database engine type.  For example, with Oracle you
could create a FBI and query with its functional expression.

> > In any case I believe the complicated stuff is where the fun begins -
> > so for _me_ DB independence is definitively a myth. ;-)
>
> Well - out goal is to make everything as simple as possible.

.... but not simpler. :-)

Cheers

robert

In article <4bd79011$0$284$14726298@news.sunsite.dk>, arne@vajhoej.dk 
says...
> 
> On 27-04-2010 04:54, Pitch wrote:
> > In article<hr3r1v$bvp$2@news.albasani.net>, noone@lewscanon.com says...
> >> junw2000@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

experience



-- 
stirr your cofee properly

Hi!

Robert Klemme wrote:

> Btw, did I mention that I believe database independence is a myth? :-)

If you want to write code that runs on "all" databases, you cannot use
any of the special features of any database.
Your code will run (rather crawl) on every database.

If you want a fast application let the database do what it can do best,
and create an API on top of it.
In case your back end runs on Oracle, use packaged stored procedures,
table functions, pipelined functions.
This way you make sure that different programs (written in different
programming languages) are all controlled  by the same database code.
If you did this from the start you can easily have
- Oracle forms
- Cold fusion
- a Java desktop application
- an interface for processing remote requests (written in C)
- another interface for processing remote requests (written in Pascal)
- ...
do the same thing without messing up the data.

You can encapsulate the tables behind this by only giving execute privs
on the API procedures to all the clients. No insert/update/delete privs
for any client software.

This way you can easily change the client without rewriting the logic.

brgds

Gunter, Orlando, Fla.

On Tue, 27 Apr 2010, Arne Vajh?j wrote:

> On 27-04-2010 15:10, Tom Anderson wrote:
>> 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.
>
> Modern alternative????
>
> I thought it was a classic anti-pattern to expose DAL as services 
> instead of BLL as services.

Yes. Sorry if i didn't make it clear enough, but i was talking about 
exposing domain-model operations, not raw database operations. Hence the 
bit about "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.". And the paragraph discussing the fact 
that developing other apps will involve adding business logic to the java.

tom

-- 
IMPORTANCE MEMO: >>> WHEN YOU BUY AN N-GAGE QD <<< PLEASE, please CONTINUE
TO TALK ON THE SIDE!!$ Note: the other party will not be able to hear you,
BUT WHO REALLY CRAPS A THING, SIDETALKIN' 2009++!!!

Lew wrote:
>>>> ORM doesn't force business rules into a separate layer and raw JDBC calls
>>>> don't force them into the same layer as persistence.

Pitch wrote:
>>> I disagree.

Arne Vajhøj wrote:
>> Can you explain what prevents you from copying business logic and
>> for that matter presentation logic into Hibernate/JPA data classes?

Pitch wrote:
> experience

You just proved that the ORM is not what forces business rules into a separate 
layer, confirming my claim.

-- 
Lew

On 28-04-2010 04:52, Pitch wrote:
> In article<4bd79011$0$284$14726298@news.sunsite.dk>, arne@vajhoej.dk
> says...
>> On 27-04-2010 04:54, Pitch wrote:
>>> In article<hr3r1v$bvp$2@news.albasani.net>, noone@lewscanon.com says...
>>>> junw2000@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?
>
> experience

Well - ORM implementations does not carry experience AI into the
app.

And your experience is not part of the ORM.

Arne

In article <4bd8e72a$0$277$14726298@news.sunsite.dk>, arne@vajhoej.dk 
says...
> 
> On 28-04-2010 04:52, Pitch wrote:
> > In article<4bd79011$0$284$14726298@news.sunsite.dk>, arne@vajhoej.dk
> > says...
> >> On 27-04-2010 04:54, Pitch wrote:
> >>> In article<hr3r1v$bvp$2@news.albasani.net>, noone@lewscanon.com says...
> >>>> junw2000@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?
> >
> > experience
> 
> Well - ORM implementations does not carry experience AI into the
> app.
> 
> And your experience is not part of the ORM.

 :)

Well, arent' you a nitpicker. Are you and Lew the same person?


-- 
stirr your cofee properly

a...@vajhoej.dk says...
>> Well - ORM implementations does not carry experience AI into the
>> app.
>>
>> And your experience is not part of the ORM.
>

Pitch <m...@fake.info> wrote:
> =A0:)
>
> Well, arent' you a nitpicker. Are you and Lew the same person?
>

We aren't, but the honor would be mine were it so.

It isn't really nitpicking to point out that you completely and
utterly contradicted your earlier point.

--
Lew

In article <4451fd91-74f4-4840-83a9-ae155893aa64
@o14g2000yqb.googlegroups.com>, lew@lewscanon.com says...
>=20
> a...@vajhoej.dk says...
> >> Well - ORM implementations does not carry experience AI into the
> >> app.
> >>
> >> And your experience is not part of the ORM.
> >
>=20
> Pitch <m...@fake.info> wrote:
> > =A0:)
> >
> > Well, arent' you a nitpicker. Are you and Lew the same person?
> >
>=20
> We aren't, but the honor would be mine were it so.
>=20
> It isn't really nitpicking to point out that you completely and
> utterly contradicted your earlier point.

Aparently, you are not able to follow my thoughts so I won't take up any=20
more of your time.

--=20
stirr your cofee properly