Path: csiph.com!eternal-september.org!feeder.eternal-september.org!aioe.org!.POSTED!not-for-mail
From: =?UTF-8?Q?Arne_Vajh=c3=b8j?= <arne@vajhoej.dk>
Newsgroups: comp.lang.java.programmer
Subject: Re: Using GROUP By or @OrderColumn in JPA
Date: Sat, 28 Nov 2015 22:44:37 -0500
Organization: Aioe.org NNTP Server
Lines: 204
Message-ID: <n3dsba$2re$1@speranza.aioe.org>
References: <87bnai8pu7.fsf@Equus.decebal.nl> <n35lmq$mph$1@speranza.aioe.org> <87two9e0l7.fsf@Equus.decebal.nl> <n38b8u$br8$1@speranza.aioe.org>
NNTP-Posting-Host: 1lM8B9/qdtKBc/uIRK2Gjw.user.speranza.aioe.org
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
X-Complaints-To: abuse@aioe.org
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:38.0) Gecko/20100101 Thunderbird/38.4.0
X-Notice: Filtered by postfilter v. 0.8.2
Xref: csiph.com comp.lang.java.programmer: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