Java Spring Boot, Join tables in type safe way without plain strings

Question

Is there a way to join tables in Spring Boot without using plain SQL strings which are not type safe? Currently using JBDC .

These methods here are not string safe: Joining two table entities in Spring Data JPA

In .NET, Entity Framework has a method in C#,

from u in db.Users
join ad in db.Address on u.Addressid equals ad.AddressId
select ..

Java Example:

@Entity
@Table(name = "users")
public class User {
    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Long id;
    //... 

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "address_id", referencedColumnName = "id")
    private Address address;

    // ... getters and setters
}

@Entity
@Table(name = "address")
public class Address {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Long id;
    //...

    @OneToOne(mappedBy = "address")
    private User user;

    //... getters and setters
}

What exactly is your question? You seem to already have a solution in Java. If it does not work, what is not working? What do you expect? — Matt, Jul 10 '21 at 06:45
I have a solution in reference, however this is not type safe https://stackoverflow.com/questions/19977130/joining-two-table-entities-in-spring-data-jpa , someone can write bad code in the string quote, and it will still compile, I am trying to do it similar to Entity Framework code above @Matt — mattsmith5, Jul 10 '21 at 07:05
I don't know of a solution at compile-time. However, you could validate the schema with hibernate using a test-database as a reference. — Matt, Jul 10 '21 at 07:12
would this join work? @Matt https://stackoverflow.com/questions/31120461/how-to-do-join-on-query-using-criteria-api https://stackoverflow.com/questions/3424696/jpa-criteria-api-how-to-add-join-clause-as-general-sentence-as-possible — mattsmith5, Jul 10 '21 at 07:14
If you are using Spring Data JPA, you could declare custom query methods like: ``` Optional findUserByAddress_Id(Long id); ``` See https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.named-queries.declaring-interfaces — Tsing, Jul 16 '21 at 12:29

score 1 · Accepted Answer · edited Sep 09 '21 at 05:35

It is "type-safe" - because they all are strings.

Your problem is: If someone use the "wrong" referenced field, then it will not checked at compile-time.
This is true and cannot be changed, but you can check it at runtime.
Or even better: Spring/Hibernate can do that for you.
Just enable the hibernate validation by adding spring.jpa.hibernate.ddl-auto=validate to your application.yml or application.properties.

Now on the startup of spring you will get this exception (if you have a typo for address/adress):

Caused by: org.hibernate.AnnotationException: Unknown mappedBy in: Address.user, referenced property unknown: User.adress

You can also use the other modes. Have a look to the documentation.
All modes (expect none) will validating the schema.
That's the best solution, since there are no compile-time-checks.

You can try using JOOQ also.

Java Spring Boot, Join tables in type safe way without plain strings

1 Answers1