How to Return a Select Query from JPA Repository With Foreign Key Type

How to return a select query from jpa repository with foreign key type

If, in a second time, you want to isolate a list of comarca_id, try to stream your request result.

    List<Comarca> comarca = debitoNegativacao.stream().map(dn -> dn.getComarca()).distinct().collect(Collectors.toList());

++

JPQL query by foreign key

Person entity should look something like this

@Entity
public class PersonEntity {
     private Long id;
     private String name;

     @OneToOne
     @JoinColumn(name = "city_id")
     private CityEntity city;
}

Than you can write your query like this

List<PersonEntity> findByNameAndCityOrNameIsNullOrCityIsNull(String name, CityEntity city);

Spring Data JPA is so smart to generate the query for you under the hood.

BTW, you attempted to write JPQL, not HQL.

EDIT (reaction on comment about long method names):

I would suggest to avoid creating JPQL query, because is is more error prone. If you don't like lengthy method name, you can wrap it into shorter default method within repository:

@Repository
public interface PersonEntityRepository extends  JpaRepository<PersonEntity, Long> {
     List<PersonEntity> findByNameAndCityOrNameIsNullOrCityIsNull(String name, CityEntity city);
     
     default List<PersonEntity> shortName(String name, CityEntity city) {
         return findByNameAndCityOrNameIsNullOrCityIsNull(name, city);
     }
}

Spring Data JPA @Query with foreign key: Parameter not matched

You can use Spring data to generate the underlying query like this :

public interface SignalRepository extends JpaRepository<Signal, Integer> {
    List<Signal> findSignalByObjectId(Integer objectId);
}

or you can write the query with this return type and parameter:

public interface SignalRepository extends JpaRepository<Signal, Integer> {
    @Query("select s from Signal s where s.object = :id")
    List<Signal> findSignalByObjectId(@Param("id") Integer objectId);
}

Spring data jpa search filter by foreign key and type

Just add a method in your Vehicle JPA repository interface as follow:

findAllByTypeAndFkFranchiseIdZipCode(String type, String zipCode);

And also you are welcome to check docs of Spring Data Jpa

Find all data using foreign key from referenced table in spring boot jpa

The problem that you face is that your entity Page does not have a field that relates with Book. So you have unidirectional mapping in JPA layer.

One solution would be to include the following custom query which will return you what you expect to be returned.

 @Query( "SELECT pg FROM Book bk join bk.pages pg WHERE bk.bookId = :bookId")
 List<Page> findPagesByBookId(@Param("bookId") String bookId);

How to perform a query in Spring Data /JPA with a foreign Key?

Use fields, not columns in @Query

public interface ExpensesRepository extends JpaRepository< Expenses, Long> {
    @Query("FROM Expenses g where g.user.id = :userId")
    GestaoGastos findAllByCurrentUser(@Param("userId") Long userId);
}

How to select row from table based on foreign key value using Hibernate (Spring)

It seems that Kotizacija's id is long and not BigInteger, but you may specify the Kotizacija directly instead of it id.
Controller:

@GetMapping("/kotizacije_cjenovnik/{id}")
@Timed
public List<KotizacijeCjenovnik> getKotizacijeByKotizacijaId(@PathVariable BigInteger id) {
    log.debug("REST request to get Kotizacija : {}", id);
    Kotizacija kotizacija = new Kotizacija();
    kotizacija.setId(id);
    List<KotizacijeCjenovnik> kotizacija_cjenovnik = kotizacijaCjenovnik.getKotizacijeByKotizacija(kotizacija);
    return kotizacija_cjenovnik;
}

Repository:

@SuppressWarnings("unused")
@Repository
public interface KotizacijeCjenovnikRepository extends 
    JpaRepository<KotizacijeCjenovnik, BigInteger> {
    @Query(value="select kotizacije_cjenovnik from KotizacijeCjenovnik kotizacije_cjenovnik where kotizacije_cjenovnik.kotizacija = :kotizacija")
    List<KotizacijeCjenovnik> getKotizacijeByKotizacija(@Param("kotizacija") Kotizacija kotizacija);
}

JPA find by foreign key

Assuming that primary key of Provincia is id:

public interface MunicipioRepo extends JpaRepository<Municipio, Integer> {
    ...
    List<Municipio> findAllByNombreProvinciaId(final String provinciaId);
}

And just pass provinciaId to this method:

// getMunicipiosEnProvincia method in controller
...
return municipioRepo.findAllByNombreProvinciaId(nombreProvincia); // here nombreProvincia is the id of Provincia

Unable to retrieve records using Spring Data JPA repository when foreign key is null

NULL does not compare equal with anything, you need to use "is NULL".
See here

In your case:

SELECT lr from LookupRequest lr where lr.account = :account and (lr.callback = :callback or lr.callback is null)

---

Related Topics