0

I would like to do the following query using spring jpa. I am able to build the where clause for my statement with Predicate.toPredicate. However, I don't know how to join on more than one column. On a single column it can be done in the repository using @Query and @Param

SELECT a.name, a.column_x, b.column_y
FROM table_a a
INNER JOIN table_b b
ON b.name = a.name
AND b.pk_2 = a.pk_2
AND b.pk_3 = a.pk_3
WHERE ...;

Another question I have is, is an intermediate tableA_tableB association beneficial if I have something like this, oneToMany relations.

Table 1: thing

thing_name

type

tenant

other1

other2

Table 2: thing_sub_prop

prop_name

value

Association table: thing_thing_sub_prop

type

thing_name

tenant

prop_name

value

Or is it better to just have two tables, thing and thing_sub_prop with the primary key columns of thing repeated in thing_sub_prop as a foreign key?

user1738539
  • 874
  • 2
  • 11
  • 23
  • you can join table using @query . What is the information that you have so that you can pass that to the method to actually get the join working do you have the a.name or b.name. Also check this it might help you http://stackoverflow.com/questions/35989046/jpa-query-equals-between-two-properties/35998430 – Grinish Nepal Mar 22 '16 at 20:09
  • I tried something with like @query("SELECT .. FROM .. JOIN ..") public List myQuery() But inside the method definition I'm confused on how to add the parameters for my join to map to the columns in the query defined. Also I thought you can't 'AND' multiple predicates in @Query – user1738539 Mar 23 '16 at 02:39
  • inside the method you need to do something like this **public List myQuery(@Param("id") String id)** then you can actually use the id in the actual query you create. check the link that i have pasted above that should asnwer your question check the answer and the comment on the answer. It would be nice if you put some code here. – Grinish Nepal Mar 23 '16 at 18:44
  • I ended up doing something different but I'm still having a few issues. My queries are working perfect with specification toPredicate() Path> thingProps = root.get("thingProps"); current = builder.equal(thingProps.get("name"), key); result = (result == null) ? current : builder.and(result, current); result = builder.and(result, builder.equal(thingProps.get("value"), value)); The problem I'm having is persisting the entities into the database with pre existing ThingProps. – user1738539 Mar 25 '16 at 19:44
  • http://stackoverflow.com/questions/36225540/spring-data-jpa-persistence-could-not-commit-jpa-transaction-ora-00001-uniq This is the new problem I'm having. – user1738539 Mar 25 '16 at 19:47
  • This answer solved my problem: http://stackoverflow.com/a/28609074/1110253 – SparX Aug 19 '16 at 09:07

0 Answers0