Digest for jooq-user@googlegroups.com - 5 updates in 3 topics

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

Digest for jooq-user@googlegroups.com - 5 updates in 3 topics

http://groups.google.com/group/jooq-user/topics mailing list
[hidden email]: Aug 23 08:05AM -0700

Hi, everyone
 
I have two tables: TABLE_A and TABLE_B, they have some columns with the
same names.
 
TABLE_A (ID, NAME, ADDRESS)
TABLE_B (ID, NAME)
 
I want to retrieve all columns from both tables by join:
 
 
 
 
 
*List<MyDto> results = query.select()
.from(TABLE_A)
.join(TABLE_B)
.on(TABLE_A.ID.equal(TABLE_B.ID))
.fetchInto(MyDto.class);*
 
And I use the @Column annotation to specify which column comes from which
table, like this:
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
*import javax.persistence.Column;public class MyDto { @Column(table =
"TABLE_A", name = "ID") private String idA; @Column(table =
"TABLE_A", name = "NAME") private String nameA; @Column(table =
"TABLE_A", name = "ADDRESS") private String addressA; @Column(table =
"TABLE_B", name = "ID") private String idB; @Column(table =
"TABLE_B", name = "NAME") private String nameB;}*
 
or this:
 
<code>
import javax.persistence.Column;
 
public class MyDto {
 
@Column(name = "*TABLE_A*.ID")
private String idA;
 
@Column(name = "*TABLE_A*.NAME")
private String nameA;
 
@Column(name = "*TABLE_A*.ADDRESS")
private String addressA;
 
@Column(name = "*TABLE_B*.ID")
private String idB;
 
@Column(name = "*TABLE_B*.NAME")
private String nameB;
 
}
</code>
 
But both cannot work as expected, does Jooq support this feature?
If not, how to implement this?
 
Thanks,
Extjs
Debapriya Patra <[hidden email]>: Aug 22 09:02PM -0700

#getDecksWithoutCardByIds
select "latest_deck_version"."id", "latest_deck_version"."deleted", "latest_deck_version"."foreign_id", "latest_deck_version"."deck_type", "latest_deck_version"."title", "latest_deck_version"."confidential", "latest_deck_version"."certified", "latest_deck_version"."deck_id", "latest_deck_version"."original_created", "latest_deck_version"."original_updated", "latest_deck_version"."created", "latest_deck_version"."updated", "latest_deck_version"."num_cards", "latest_deck_version"."num_images", "latest_deck_version"."edition" from "public"."latest_deck_version"(cast('{"efb2d576-f2d3-4a8e-9715-dc95fdd14701","816d681e-18ca-4b61-938c-401b22c714f5"}' as uuid[]));
> -------------------------------------- --------- ---------- ----------- ----------- ------------- ---------- -------------------------------------- --------------------- -------------------- --------------------- -------------------- ----------- ------------ ----------------------------------
> efb2d576-f2d3-4a8e-9715-dc95fdd14701 false null FINAL titleTest false true efb1d576-f2d3-4a8e-9715-dc95fdd14701 2018-10-19 17:44:50 2018-10-19 17:44:50 2018-10-19 17:44:50 2018-10-19 17:44:50 4 0 012a631c94215f2ad86cb66d0e7d3043
> 816d681e-18ca-4b61-938c-401b22c714f5 true null FINAL titleDemo true true efb6d576-f2d3-4a8e-9715-dc95fdd14701 2018-10-19 17:44:50 2018-10-19 17:44:50 2018-10-19 17:44:50 2018-10-19 17:44:50 7 0 03a3724203d4f758dc0a567cb58dab17
@ rows: 2
Knut Wannheden <[hidden email]>: Aug 23 11:30AM +0200

Hi Deba,
 
I tested your query using jOOQ 3.11.11 and could not find any problem. Can
you show your actual test code? Even better would be a MCVE (see
https://github.com/jOOQ/jOOQ-mcve).
 
Regards,
Knut
 
On Fri, Aug 23, 2019 at 6:02 AM Debapriya Patra <[hidden email]>
wrote:
 
Debapriya Patra <[hidden email]>: Aug 23 06:24AM -0700

Hi Kunt,
 
I am using JOOQ 3.11.8.
 
*DAO Method:*
 
public List<DeckWithoutCard> getDecksWithoutCardByIds(List<UUID> deckIds){
UUID[] ids = deckIds.stream().toArray(UUID[]::new);
LatestDeckVersion latestDeckVersion = Routines.latestDeckVersion(ids);
 
SelectWhereStep<LatestDeckVersionRecord> latestDeckVersionRecords = dsl.selectFrom(latestDeckVersion);
 
ResultSet rs = latestDeckVersionRecords.fetchResultSet();
return transformToDeckWithoutCardList(rs);
}
 
 
LatestDeckVersion is basically a function written in psql.
 
create or replace function public.latest_deck_version(deck_id_array uuid[])
returns table(
id uuid,
deleted boolean,
foreign_id text,
deck_type deck_type,
title text,
confidential boolean,
certified boolean,
deck_id uuid,
original_created timestamp without time zone,
original_updated timestamp without time zone,
created timestamp with time zone, updated
timestamp with time zone,
num_cards bigint,
num_images bigint,
edition text)
language sql
as $function$
with
cte0 as (
select
t.idx,
t.id
from unnest($1) with ordinality as t(id, idx)),
cte1 as (
select
lower(sys_period) as t,
false as deleted,
d.id,
foreign_id,
title,
deck_type,
confidential,
certified,
deck_id,
original_created,
original_updated,
coalesce((select min(lower(sys_period)) from core.deck_version where id = d.id), lower(sys_period)) as created,
lower(sys_period) as updated,
(select count (1) from core.card_deck where deck_id = d.id) as num_cards,
(select image_count from deck_image_count where deck_id = d.id) as num_images,
(select edition from deck_edition where deck_id = d.id)
from core.deck d
join cte0 on cte0.id::uuid = d.id
union all
select
lower(sys_period) as t,
true as deleted,
d.id,
foreign_id,
title,
deck_type,
confidential,
certified,
deck_id,
original_created,
original_updated,
(select min(lower(sys_period)) from core.deck_version where id = d.id) as created,
(select max(upper(sys_period)) from core.deck_version where id = d.id) as updated,
(select count (1) as count from core.card_deck where deck_id = d.id) num_cards,
(select image_count from deck_image_count where deck_id = d.id) num_images,
(select edition from deck_edition where deck_id = d.id)
from core.deck_version d
join cte0 on cte0.id::uuid = d.id),
cte2 as (
select *
from cte1
order by id, t desc),
cte3 as (
select distinct on (id)
cte2.id,
deleted,
foreign_id,
deck_type,
title,
confidential,
certified,
deck_id,
original_created,
original_updated,
created,
updated,
num_cards,
num_images,
edition
from cte2)
select
cte3.id,
deleted,
foreign_id,
deck_type,
title,
confidential,
certified,
deck_id,
original_created,
original_updated,
created,
updated,
num_cards,
num_images,
edition
from cte3
join cte0 on cte0.id::uuid = cte3.id
order by idx asc;
$function$;
 
 
 
 
*Test Method :*
 
import com.chegg.deck.service.model.DeckWithoutCard;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.mockito.InjectMocks;
import org.mockito.MockitoAnnotations;
 
import java.io.IOException;
import java.net.URISyntaxException;
import java.util.Arrays;
import java.util.List;
import java.util.UUID;
 
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertNotNull;
 
public class DeckWithoutCardsByIdsTest extends JooqBaseTest {
@InjectMocks
private DeckRepository deckRepository;
 
@Override
public String getResourcePath(){
return "/db/get_deck_history.txt";
}
 
@BeforeEach
public void init() throws IOException, URISyntaxException {
MockitoAnnotations.initMocks(this);
super.init();
deckRepository.setDsl(dslContext);
}
 
@Test
public void testGetDecksWithoutCardByIds(){
List<UUID> deckIds = Arrays.asList(UUID.fromString("efb2d576-f2d3-4a8e-9715-dc95fdd14701"), UUID.fromString("816d681e-18ca-4b61-938c-401b22c714f5"));
List<DeckWithoutCard> decksWithoutCardByIds = deckRepository.getDecksWithoutCardByIds(deckIds);
assertNotNull(decksWithoutCardByIds);
assertEquals(2, decksWithoutCardByIds.size());
}
}
 
 
Thanks,
Deba
 
On Friday, August 23, 2019 at 2:31:02 AM UTC-7, Knut Wannheden wrote:
Andrew Leung <[hidden email]>: Aug 22 05:45PM -0700

Thanks for the additional details, Lukas. I've mostly worked around the
issue by adjusting the workflow so the key should always exist prior to
this operation and a simple UPDATE is sufficient. I'll try and follow up
with more details when I have some time to put a reproducible example
together.
 
Andrew
 
On Wednesday, August 21, 2019 at 3:33:28 AM UTC-5, Lukas Eder wrote:
You received this digest because you're subscribed to updates for this group. You can change your settings on the group membership page.
To unsubscribe from this group and stop receiving emails from it send an email to [hidden email].