Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.java.programmer > #33197 > unrolled thread

Using GROUP By or @OrderColumn in JPA

Back to article view | Back to comp.lang.java.programmer

Contents

  Using GROUP By or @OrderColumn in JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-25 09:07 +0100
    Re: Using GROUP By or @OrderColumn in JPA Arne Vajhøj <arne@vajhoej.dk> - 2015-11-25 20:02 -0500
      Re: Using GROUP By or @OrderColumn in JPA Cecil Westerhof <Cecil@decebal.nl> - 2015-11-26 07:28 +0100
        Re: Using GROUP By or @OrderColumn in JPA Jerry Stuckle <jstucklex@attglobal.net> - 2015-11-26 08:25 -0500
        Re: Using GROUP By or @OrderColumn in JPA Arne Vajhøj <arne@vajhoej.dk> - 2015-11-26 20:22 -0500
          Re: Using GROUP By or @OrderColumn in JPA Silvio <silvio@internet.com> - 2015-11-27 12:28 +0100
            Re: Using GROUP By or @OrderColumn in JPA Jerry Stuckle <jstucklex@attglobal.net> - 2015-11-27 09:22 -0500
              Re: Using GROUP By or @OrderColumn in JPA Silvio <silvio@internet.com> - 2015-11-27 17:42 +0100
                Re: Using GROUP By or @OrderColumn in JPA Silvio <silvio@internet.com> - 2015-11-27 17:43 +0100
                  Re: Using GROUP By or @OrderColumn in JPA Martin Gregorie <martin@address-in-sig.invalid> - 2015-11-27 18:21 +0000
            Re: Using GROUP By or @OrderColumn in JPA Arne Vajhøj <arne@vajhoej.dk> - 2015-11-28 23:01 -0500
              Re: Using GROUP By or @OrderColumn in JPA Silvio <silvio@internet.com> - 2015-11-29 12:42 +0100
          Re: Using GROUP By or @OrderColumn in JPA Arne Vajhøj <arne@vajhoej.dk> - 2015-11-28 22:44 -0500
        Re: Using GROUP By or @OrderColumn in JPA Martin Gregorie <martin@address-in-sig.invalid> - 2015-11-27 11:07 +0000

#33197 — Using GROUP By or @OrderColumn in JPA

We are working on our first project with JPA. We had some bumps in the
road, but I find it a good tool.

We have an entity request which has a one to many relationship with
estimate which has a one to one relationship with decision. One of the
fields of decision is date.

We need request-estimate-decision's where the date field of decision
is not null and has the highest value. (We are only interested in the
latest decision pro request.)

I was thinking about a GROUP BY, but someone else thought about
@OrderColumn. But if I understand it correctly an @OrderColumn needs
an Integer and an index. We cannot change the database definition. So
I suppose that @OrderColumn is not an option and we need to use the
GROUP BY?

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [next] | [standalone]

#33205

On 11/25/2015 3:07 AM, Cecil Westerhof wrote:
> We are working on our first project with JPA. We had some bumps in the
> road, but I find it a good tool.
>
> We have an entity request which has a one to many relationship with
> estimate which has a one to one relationship with decision. One of the
> fields of decision is date.
>
> We need request-estimate-decision's where the date field of decision
> is not null and has the highest value. (We are only interested in the
> latest decision pro request.)
>
> I was thinking about a GROUP BY, but someone else thought about
> @OrderColumn. But if I understand it correctly an @OrderColumn needs
> an Integer and an index. We cannot change the database definition. So
> I suppose that @OrderColumn is not an option and we need to use the
> GROUP BY?

How did you do it before with SQL?

Arne

[toc] | [prev] | [next] | [standalone]

#33206

On Thursday 26 Nov 2015 02:02 CET, Arne Vajhøj wrote:

> On 11/25/2015 3:07 AM, Cecil Westerhof wrote:
>> We are working on our first project with JPA. We had some bumps in
>> the road, but I find it a good tool.
>>
>> We have an entity request which has a one to many relationship with
>> estimate which has a one to one relationship with decision. One of
>> the fields of decision is date.
>>
>> We need request-estimate-decision's where the date field of
>> decision is not null and has the highest value. (We are only
>> interested in the latest decision pro request.)
>>
>> I was thinking about a GROUP BY, but someone else thought about
>> @OrderColumn. But if I understand it correctly an @OrderColumn
>> needs an Integer and an index. We cannot change the database
>> definition. So I suppose that @OrderColumn is not an option and we
>> need to use the GROUP BY?
>
> How did you do it before with SQL?

This was never done with SQL. They made a JPQL query that was badly
written. I managed to help rewrite it, so it is a bit clearer what is
happening. But everything was done over instant message, which is not
ideal. Today we will meet face to face and I hope to get a better
understanding of what they want and have.

-- 
Cecil Westerhof
Senior Software Engineer
LinkedIn: http://www.linkedin.com/in/cecilwesterhof

[toc] | [prev] | [next] | [standalone]

#33207

On 11/26/2015 1:28 AM, Cecil Westerhof wrote:
> On Thursday 26 Nov 2015 02:02 CET, Arne Vajhøj wrote:
> 
>> On 11/25/2015 3:07 AM, Cecil Westerhof wrote:
>>> We are working on our first project with JPA. We had some bumps in
>>> the road, but I find it a good tool.
>>>
>>> We have an entity request which has a one to many relationship with
>>> estimate which has a one to one relationship with decision. One of
>>> the fields of decision is date.
>>>
>>> We need request-estimate-decision's where the date field of
>>> decision is not null and has the highest value. (We are only
>>> interested in the latest decision pro request.)
>>>
>>> I was thinking about a GROUP BY, but someone else thought about
>>> @OrderColumn. But if I understand it correctly an @OrderColumn
>>> needs an Integer and an index. We cannot change the database
>>> definition. So I suppose that @OrderColumn is not an option and we
>>> need to use the GROUP BY?
>>
>> How did you do it before with SQL?
> 
> This was never done with SQL. They made a JPQL query that was badly
> written. I managed to help rewrite it, so it is a bit clearer what is
> happening. But everything was done over instant message, which is not
> ideal. Today we will meet face to face and I hope to get a better
> understanding of what they want and have.
> 

Can you not use the ORDER BY clause in SQL?

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#33210

On 11/26/2015 1:28 AM, Cecil Westerhof wrote:
> On Thursday 26 Nov 2015 02:02 CET, Arne Vajhøj wrote:
>> On 11/25/2015 3:07 AM, Cecil Westerhof wrote:
>>> We are working on our first project with JPA. We had some bumps in
>>> the road, but I find it a good tool.
>>>
>>> We have an entity request which has a one to many relationship with
>>> estimate which has a one to one relationship with decision. One of
>>> the fields of decision is date.
>>>
>>> We need request-estimate-decision's where the date field of
>>> decision is not null and has the highest value. (We are only
>>> interested in the latest decision pro request.)
>>>
>>> I was thinking about a GROUP BY, but someone else thought about
>>> @OrderColumn. But if I understand it correctly an @OrderColumn
>>> needs an Integer and an index. We cannot change the database
>>> definition. So I suppose that @OrderColumn is not an option and we
>>> need to use the GROUP BY?
>>
>> How did you do it before with SQL?
>
> This was never done with SQL. They made a JPQL query that was badly
> written. I managed to help rewrite it, so it is a bit clearer what is
> happening. But everything was done over instant message, which is not
> ideal. Today we will meet face to face and I hope to get a better
> understanding of what they want and have.

I would probably do one of these:

1) Let JPA retrieve all decisions but add a getLastDecision
    method to the Estimate class. If the number of decisions
    per estimate is small then the overhead should be
    insignificant.

2) Don't let JPA map between estimates and decisions. Instead
    make a getLastDecision method in Estimate class that
    does a separate JPQL query to get the data. If the number
    of estimate for which you need last decision is small
    then the overhead should be insignificant.

Yes - I am arguing for a very simple solution. Unless you
really have a performance problem that requires a complex
solution then I strongly believe in keeping it simple.

Arne

[toc] | [prev] | [next] | [standalone]

#33213

On 11/27/2015 02:22 AM, Arne Vajhøj wrote:
> On 11/26/2015 1:28 AM, Cecil Westerhof wrote:
>> On Thursday 26 Nov 2015 02:02 CET, Arne Vajhøj wrote:
>>> On 11/25/2015 3:07 AM, Cecil Westerhof wrote:
>>>> We are working on our first project with JPA. We had some bumps in
>>>> the road, but I find it a good tool.
>>>>
>>>> We have an entity request which has a one to many relationship with
>>>> estimate which has a one to one relationship with decision. One of
>>>> the fields of decision is date.
>>>>
>>>> We need request-estimate-decision's where the date field of
>>>> decision is not null and has the highest value. (We are only
>>>> interested in the latest decision pro request.)
>>>>
>>>> I was thinking about a GROUP BY, but someone else thought about
>>>> @OrderColumn. But if I understand it correctly an @OrderColumn
>>>> needs an Integer and an index. We cannot change the database
>>>> definition. So I suppose that @OrderColumn is not an option and we
>>>> need to use the GROUP BY?
>>>
>>> How did you do it before with SQL?
>>
>> This was never done with SQL. They made a JPQL query that was badly
>> written. I managed to help rewrite it, so it is a bit clearer what is
>> happening. But everything was done over instant message, which is not
>> ideal. Today we will meet face to face and I hope to get a better
>> understanding of what they want and have.
>
> I would probably do one of these:
>
> 1) Let JPA retrieve all decisions but add a getLastDecision
>     method to the Estimate class. If the number of decisions
>     per estimate is small then the overhead should be
>     insignificant.
>
> 2) Don't let JPA map between estimates and decisions. Instead
>     make a getLastDecision method in Estimate class that
>     does a separate JPQL query to get the data. If the number
>     of estimate for which you need last decision is small
>     then the overhead should be insignificant.
>
> Yes - I am arguing for a very simple solution. Unless you
> really have a performance problem that requires a complex
> solution then I strongly believe in keeping it simple.
>
> Arne
>
>
>
>

I usually agree on keeping things as simple as possible given the 
circumstances. But I would be very hesitant to give this as general 
advice to someone who is in a situation I do not know.

I spoke of performance problems in ORM based applications here before. 
Most of these where caused by an accumulation of many "keep it simple" 
solutions.

Sometimes the data had just outgrown the simple solution and the sheer 
volume demanded a more optimal solution.

In other cases each little simple solution seemed to do just fine but a 
user action could result in tens of such simple actions being performed. 
A database action that takes 0.2 second seems plausible and hardly 
problematic. But use cases where consistently ~30 such actions will be 
performed result in a six second hick-up.

It's a very thin line between premature optimization and technical debt. 
Only if you can reliably predict both the volume of the data and the use 
case scenarios can one decide how many effort should be spent optimizing 
things.

Waiting for performance problems to happen in production before 
optimizing may be acceptable in some situations but you can not afford 
to take this approach system wide.

Only Cecil can decide here.

[toc] | [prev] | [next] | [standalone]

#33214

On 11/27/2015 6:28 AM, Silvio wrote:
> On 11/27/2015 02:22 AM, Arne Vajhøj wrote:
>> On 11/26/2015 1:28 AM, Cecil Westerhof wrote:
>>> On Thursday 26 Nov 2015 02:02 CET, Arne Vajhøj wrote:
>>>> On 11/25/2015 3:07 AM, Cecil Westerhof wrote:
>>>>> We are working on our first project with JPA. We had some bumps in
>>>>> the road, but I find it a good tool.
>>>>>
>>>>> We have an entity request which has a one to many relationship with
>>>>> estimate which has a one to one relationship with decision. One of
>>>>> the fields of decision is date.
>>>>>
>>>>> We need request-estimate-decision's where the date field of
>>>>> decision is not null and has the highest value. (We are only
>>>>> interested in the latest decision pro request.)
>>>>>
>>>>> I was thinking about a GROUP BY, but someone else thought about
>>>>> @OrderColumn. But if I understand it correctly an @OrderColumn
>>>>> needs an Integer and an index. We cannot change the database
>>>>> definition. So I suppose that @OrderColumn is not an option and we
>>>>> need to use the GROUP BY?
>>>>
>>>> How did you do it before with SQL?
>>>
>>> This was never done with SQL. They made a JPQL query that was badly
>>> written. I managed to help rewrite it, so it is a bit clearer what is
>>> happening. But everything was done over instant message, which is not
>>> ideal. Today we will meet face to face and I hope to get a better
>>> understanding of what they want and have.
>>
>> I would probably do one of these:
>>
>> 1) Let JPA retrieve all decisions but add a getLastDecision
>>     method to the Estimate class. If the number of decisions
>>     per estimate is small then the overhead should be
>>     insignificant.
>>
>> 2) Don't let JPA map between estimates and decisions. Instead
>>     make a getLastDecision method in Estimate class that
>>     does a separate JPQL query to get the data. If the number
>>     of estimate for which you need last decision is small
>>     then the overhead should be insignificant.
>>
>> Yes - I am arguing for a very simple solution. Unless you
>> really have a performance problem that requires a complex
>> solution then I strongly believe in keeping it simple.
>>
>> Arne
>>
>>
>>
>>
> 
> I usually agree on keeping things as simple as possible given the
> circumstances. But I would be very hesitant to give this as general
> advice to someone who is in a situation I do not know.
> 
> I spoke of performance problems in ORM based applications here before.
> Most of these where caused by an accumulation of many "keep it simple"
> solutions.
> 
> Sometimes the data had just outgrown the simple solution and the sheer
> volume demanded a more optimal solution.
> 
> In other cases each little simple solution seemed to do just fine but a
> user action could result in tens of such simple actions being performed.
> A database action that takes 0.2 second seems plausible and hardly
> problematic. But use cases where consistently ~30 such actions will be
> performed result in a six second hick-up.
> 
> It's a very thin line between premature optimization and technical debt.
> Only if you can reliably predict both the volume of the data and the use
> case scenarios can one decide how many effort should be spent optimizing
> things.
> 
> Waiting for performance problems to happen in production before
> optimizing may be acceptable in some situations but you can not afford
> to take this approach system wide.
> 
> Only Cecil can decide here.
> 

Part of good programming is performance testing - that is, testing under
a typical load you would see in real life.  This includes the number of
concurrent users, database size, typical operations, hardware being
used, etc.  Good testing will show performance problems.

Just testing with one user and a minimal database is like an automobile
manufacturer building a car and just testing it at 5mph in a large,
empty parking lot.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#33215

On 11/27/2015 03:22 PM, Jerry Stuckle wrote:
>
> Part of good programming is performance testing - that is, testing under
> a typical load you would see in real life.  This includes the number of
> concurrent users, database size, typical operations, hardware being
> used, etc.  Good testing will show performance problems.
>
> Just testing with one user and a minimal database is like an automobile
> manufacturer building a car and just testing it at 5mph in a large,
> empty parking lot.
>

I totally agree but good testing is difficult to organize. A good 
integration test before a major release as we used to do in the past 
would indeed uncover 99% of the performance bottlenecks.

But nowadays with all the agility going on testing is often equivalent 
to unit testing which is mostly in this area.

I uphold that the majority of performance problems can easily be 
prevented by the writer of the code given a competent coder.

[toc] | [prev] | [next] | [standalone]

#33216

On 11/27/2015 05:42 PM, Silvio wrote:
> On 11/27/2015 03:22 PM, Jerry Stuckle wrote:
>>
>> Part of good programming is performance testing - that is, testing under
>> a typical load you would see in real life.  This includes the number of
>> concurrent users, database size, typical operations, hardware being
>> used, etc.  Good testing will show performance problems.
>>
>> Just testing with one user and a minimal database is like an automobile
>> manufacturer building a car and just testing it at 5mph in a large,
>> empty parking lot.
>>
>
> I totally agree but good testing is difficult to organize. A good
> integration test before a major release as we used to do in the past
> would indeed uncover 99% of the performance bottlenecks.
>
> But nowadays with all the agility going on testing is often equivalent
> to unit testing which is mostly in this area.
>
> I uphold that the majority of performance problems can easily be
> prevented by the writer of the code given a competent coder.

Mostly USELESS

[toc] | [prev] | [next] | [standalone]

#33217

On Fri, 27 Nov 2015 17:43:27 +0100, Silvio wrote:

> On 11/27/2015 05:42 PM, Silvio wrote:
>> On 11/27/2015 03:22 PM, Jerry Stuckle wrote:
>>>
>>> Part of good programming is performance testing - that is, testing
>>> under a typical load you would see in real life.  This includes the
>>> number of concurrent users, database size, typical operations,
>>> hardware being used, etc.  Good testing will show performance
>>> problems.
>>>
>>> Just testing with one user and a minimal database is like an
>>> automobile manufacturer building a car and just testing it at 5mph in
>>> a large, empty parking lot.
>>>
>>>
>> I totally agree but good testing is difficult to organize. A good
>> integration test before a major release as we used to do in the past
>> would indeed uncover 99% of the performance bottlenecks.
>>
>> But nowadays with all the agility going on testing is often equivalent
>> to unit testing which is mostly in this area.
>>
>> I uphold that the majority of performance problems can easily be
>> prevented by the writer of the code given a competent coder.
> 
IME a major problem with sorting out performance during integration or 
system testing is getting anybody at all to say which transactions are 
the most used and/or important from a user POV, let alone getting usage 
volumes. On top of that, even after assuming the DB designer did a good 
job and added indexes etc to support all prime and alternate keys (this 
is often an unwarranted assumption but is usually obvious once you see 
the DB schema), it can be quite hard to find out what else bottlenecks 
the DB. I'm thinking of missing indexes that are needed for some joins or 
to obviate sorting to satisfy ORDER BY clauses. 

As a result, I've often had to instrument the application well enough to 
capture query frequencies and execution times just to find out which 
transactions to tackle first and, that decided, to use JDBC or ODBC 
tracing to see what the problem queries were doing.


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

[toc] | [prev] | [next] | [standalone]

#33222

On 11/27/2015 6:28 AM, Silvio wrote:
> On 11/27/2015 02:22 AM, Arne Vajhøj wrote:
>> Yes - I am arguing for a very simple solution. Unless you
>> really have a performance problem that requires a complex
>> solution then I strongly believe in keeping it simple.
>
> I usually agree on keeping things as simple as possible given the
> circumstances. But I would be very hesitant to give this as general
> advice to someone who is in a situation I do not know.
>
> I spoke of performance problems in ORM based applications here before.
> Most of these where caused by an accumulation of many "keep it simple"
> solutions.

In my experience serious performance problems is rarely the cumulative
effect of lots of non-optimized code but more a single or a few hot
spot.

Performance is known to be 20-80 but I actually think 5-95 is more
common.

> Sometimes the data had just outgrown the simple solution and the sheer
> volume demanded a more optimal solution.

For that to be a problem you will need something that is worse
than O(n). As long as the stuff is O(n) then volume increase and
hardware increase should work fine (volume increase without hardware
increase is obviously still a problem but I would not consider
that a software issue).

Lots of stuff is worse than O(n) but database access should not
be so.

> In other cases each little simple solution seemed to do just fine but a
> user action could result in tens of such simple actions being performed.
> A database action that takes 0.2 second seems plausible and hardly
> problematic. But use cases where consistently ~30 such actions will be
> performed result in a six second hick-up.

0.2 second for a database operation is an eternity.

> It's a very thin line between premature optimization and technical debt.

Premature optimization is technical debt.

> Only if you can reliably predict both the volume of the data and the use
> case scenarios can one decide how many effort should be spent optimizing
> things.

It can be very difficult to predict load profile. But one can try ones
best.

And the compromise has to be made. For any real world application an
attempt to make the application 100% optimal will guarantee that it
will not be complete before it becomes obsolete.

> Waiting for performance problems to happen in production before
> optimizing may be acceptable in some situations but you can not afford
> to take this approach system wide.

You really take that approach for all applications. You just
do your best to minimize risk based on experience and test.

Arne

[toc] | [prev] | [next] | [standalone]

#33223

On 11/29/2015 05:01 AM, Arne Vajhøj wrote:
>
> In my experience serious performance problems is rarely the cumulative
> effect of lots of non-optimized code but more a single or a few hot
> spot.
>

In my experience this is the exception case. YMMV.


> Performance is known to be 20-80 but I actually think 5-95 is more
> common.
>

20-80 or 5-95 in what respect?

>
> For that to be a problem you will need something that is worse
> than O(n). As long as the stuff is O(n) then volume increase and
> hardware increase should work fine (volume increase without hardware
> increase is obviously still a problem but I would not consider
> that a software issue).
>
> Lots of stuff is worse than O(n) but database access should not
> be so.

That totally depends on the application. If you have a simple CRUD 
application then that is mostly the case. I work a lot in the OLAP, data 
warehouse, ETL area and database access is worse than O(n) for all but 
the most trivial cases.

> 0.2 second for a database operation is an eternity.

Same here. That is a stupid thing to say in general.

> Premature optimization is technical debt.

No, its not. Technical debt accumulates when the volume of code that 
will need to be revisited in the future grows. Lazy or simply 
incompetent coding leads to technical debt.

Premature optimization is a waste of a priori effort but unless it is 
also done incompetently it results in the opposite of technical debt,

>
> It can be very difficult to predict load profile. But one can try ones
> best.
>
> And the compromise has to be made. For any real world application an
> attempt to make the application 100% optimal will guarantee that it
> will not be complete before it becomes obsolete.
>

Agree.

>> Waiting for performance problems to happen in production before
>> optimizing may be acceptable in some situations but you can not afford
>> to take this approach system wide.
>
> You really take that approach for all applications. You just
> do your best to minimize risk based on experience and test.
>

You contradict yourself here. In the end in means you don't.

[toc] | [prev] | [next] | [standalone]

#33221

On 11/26/2015 8:22 PM, Arne Vajhøj wrote:
> On 11/26/2015 1:28 AM, Cecil Westerhof wrote:
>> This was never done with SQL. They made a JPQL query that was badly
>> written. I managed to help rewrite it, so it is a bit clearer what is
>> happening. But everything was done over instant message, which is not
>> ideal. Today we will meet face to face and I hope to get a better
>> understanding of what they want and have.
>
> I would probably do one of these:
>
> 1) Let JPA retrieve all decisions but add a getLastDecision
>     method to the Estimate class. If the number of decisions
>     per estimate is small then the overhead should be
>     insignificant.
>
> 2) Don't let JPA map between estimates and decisions. Instead
>     make a getLastDecision method in Estimate class that
>     does a separate JPQL query to get the data. If the number
>     of estimate for which you need last decision is small
>     then the overhead should be insignificant.

To elaborate a bit on options and pros and cons.

Some generic data:

mysql> SELECT * FROM p;
+----+---------+
| id | descrip |
+----+---------+
|  1 | P#1     |
|  2 | P#2     |
+----+---------+
2 rows in set (0.03 sec)

mysql> SELECT * FROM c;
+----+-----+---------+
| id | pid | descrip |
+----+-----+---------+
|  1 |   1 | C#1.1   |
|  2 |   1 | C#1.2   |
|  3 |   2 | C#2.1   |
|  4 |   2 | C#2.2   |
+----+-----+---------+
4 rows in set (0.00 sec)

We want P join latest C:

P#1 : C#1.2
P#2 : C#2.2

Solution 1 - true object model:

Load all C and have a method in P to return the latest C.

	@OneToMany(mappedBy = "pid")
	public List<C1> getC() {
		return c;
	}
	public void setC(List<C1> c) {
		this.c = c;
	}
	@Transient
	public C1 getLastC() {
		C1 res = null;
		for(C1 o : c) {
			if((res == null) || (o.getId() > res.getId())) {
				res = o;
			}
		}
		return res;
	}

	List<P1> all = em.createQuery("SELECT p FROM P1 p").getResultList();
	for(P1 one : all) {
		System.out.printf("%s : %s\n", one.getDescrip(), 
one.getLastC().getDescrip());
	}

Pro's:
- this is a true in memory object/domain model
- classes are fully reusable for other queries
- nice JPA
- potentially data can be join fetched
Con's:
- load C's that are not needed for specific task

Solution 2 - manual lazy load of C's:

One to many is not mapped in JPA and P has a get that load
relevant C on demand.

	@Transient
	public C2 getC() {
		if(c == null) {
			EntityManagerFactory emf = Persistence.createEntityManagerFactory("PC");
			EntityManager em = emf.createEntityManager();
			@SuppressWarnings("unchecked")
			List<C2> onelst = em.createQuery("SELECT c FROM C2 c WHERE c.pid = 
:pid ORDER BY c.id DESC").setMaxResults(1).setParameter("pid", 
id).getResultList();
			c = onelst.get(0);
			em.close();
			emf.close();
		}
		return c;
	}

[ignore the EMF and EM parts plus the missing error handling - this
is just a SE demo app]

	@SuppressWarnings("unchecked")
	List<P2> all = em.createQuery("SELECT p FROM P2 p").getResultList();
	for(P2 one : all) {
		System.out.printf("%s : %s\n", one.getDescrip(), one.getC() != null ? 
one.getC().getDescrip() : "");
	}

Pros:
- only load C's needed
- nice JPA
Con's:
- classes are limited reusable
- data can not be join fetched

Solution 3 - non ORM style:

Normal POJO's, SQL and manual object
construction.

		@SuppressWarnings("unchecked")
		List<Object[]> allrow = em.createNativeQuery("SELECT p.id AS 
pid,p.descrip AS pdescrip,c.id AS cid,c.descrip AS cdescrip FROM p JOIN 
c ON p.id=c.pid WHERE c.id = (SELECT MAX(id) FROM c WHERE 
c.pid=p.id)").getResultList();
		List<P3> all = new ArrayList<P3>();
		for(Object[] row : allrow) {
			P3 p = new P3();
			p.setId((Integer)row[0]);
			p.setDescrip((String)row[1]);
			C3 c = new C3();
			c.setId((Integer)row[2]);
			c.setDescrip((String)row[3]);
			p.setC(c);
			all.add(p);
		}
		for(P3 one : all) {
			System.out.printf("%s : %s\n", one.getDescrip(), one.getC() != null ? 
one.getC().getDescrip() : "");
		}

[ignore whether the SQL is the optimal for the task - this
is just a SE demo app]

Pro's:
- only load C's needed
- join fetch
Con':
- classes are limited reusable (for true JPA)
- significant code
- really plain JDBC  not utilizing JPA

Solution 4 - SQL with result mapping:

As above but let JPA map query result to classes.

		@SuppressWarnings("unchecked")
		List<Object[]> allrow = em.createNativeQuery("SELECT p.id AS 
pid,p.descrip AS pdescrip,c.id AS cid,c.pid AS cpid,c.descrip AS 
cdescrip FROM p JOIN c ON p.id=c.pid WHERE c.id = (SELECT MAX(id) FROM c 
WHERE c.pid=p.id)", "PjoinCMapping").getResultList();
		List<P4> all = new ArrayList<P4>();
		for(Object[] row : allrow) {
			P4 p = (P4)row[0];
			C4 c = (C4) row[1];
			p.setC(c);
			all.add(p);
		}
		for(P4 one : all) {
			System.out.printf("%s : %s\n", one.getDescrip(), 
one.getC().getDescrip());
		}

       <sql-result-set-mapping name="PjoinCMapping">
          <entity-result entity-class="jpaproblem.sol4.P4">
              <field-result name="id" column="pid"/>
              <field-result name="descrip" column="pdescrip"/>
          </entity-result>
          <entity-result entity-class="jpaproblem.sol4.C4">
              <field-result name="id" column="cid"/>
              <field-result name="pid" column="cpid"/>
              <field-result name="descrip" column="cdescrip"/>
          </entity-result>
       </sql-result-set-mapping>

Pro's:
- only load C's needed
- join fetch
- mapping is reusable for all sorts of joins  of these two tables
Con':
- classes are limited reusable (for true JPA)
- significant configuration

Arne

[toc] | [prev] | [next] | [standalone]

#33212

On Thu, 26 Nov 2015 07:28:20 +0100, Cecil Westerhof wrote:

> On Thursday 26 Nov 2015 02:02 CET, Arne Vajhøj wrote:
> 
>> On 11/25/2015 3:07 AM, Cecil Westerhof wrote:
>>> We are working on our first project with JPA. We had some bumps in the
>>> road, but I find it a good tool.
>>>
>>> We have an entity request which has a one to many relationship with
>>> estimate which has a one to one relationship with decision. One of the
>>> fields of decision is date.
>>>
>>> We need request-estimate-decision's where the date field of decision
>>> is not null and has the highest value. (We are only interested in the
>>> latest decision pro request.)
>>>
>>> I was thinking about a GROUP BY, but someone else thought about
>>> @OrderColumn. But if I understand it correctly an @OrderColumn needs
>>> an Integer and an index. We cannot change the database definition. So
>>> I suppose that @OrderColumn is not an option and we need to use the
>>> GROUP BY?
>>
>> How did you do it before with SQL?
> 
> This was never done with SQL. They made a JPQL query that was badly
> written. I managed to help rewrite it, so it is a bit clearer what is
> happening. But everything was done over instant message, which is not
> ideal. Today we will meet face to face and I hope to get a better
> understanding of what they want and have.

I'd do it with an SQL query, but then I'm old fashioned. I'd join 
estimate and decision to form the result set ORDER it by date DESCENDING 
so the first entry in the result set is the latest decision. Adding an 
index on date to the database would speed up the sort, but if the result 
set is small the speed gain from adding an index should be minimal. 

From what others are saying, JPA should be able to achieve the same 
effect if its @OrderColumn annotation allows you to specify ascending or 
descending order.  



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

[toc] | [prev] | [standalone]

Back to top | Article view | comp.lang.java.programmer

csiph-web