How to make a custom query with Spring CrudRepository employ two or more entities with a repository

Question

I have three entities and one repository extends CrudRepository and I want make next query, but I can't. I have syntax error in or near upper. I think the problem is using multiple tables in the query and/or I'm not using any tag or declaration in the entities.

import java.util.List;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;
import org.springframework.data.repository.query.Param;
import com.companyname.springapp.business.entities.TableA;

public interface TableARepository extends CrudRepository<TableA, Integer>{

@Query(value = "SELECT DISTINCT a.*"
            +"FROM TableB AS b, TableA_TableB AS ab, TableA AS a"
            +"WHERE upper(b.nick_name) like upper(concat('%',:name,'%'))"
            +"AND b.nick_name_id=ab.nick_name_id"
            +"AND ab.id=a.id", 
              nativeQuery = true)
            List<tableA> test(
              @Param("name") String name);

My entities:

TableA

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

import com.companyname.springapp.web.Views;
import com.fasterxml.jackson.annotation.JsonView;


@Entity
@Table(name="TableA")
public class TableA{

    @Id
    @JsonView(Views.Public.class)
    private Integer id;

    //more columns, constructs and getters and setters

TableB

    //... imports
    @Entity
    @Table(name="TableB")
    public class TableB{

    @Id
    @JsonView(Views.Public.class)
    private Integer nick_name_id;

    @JsonView(Views.Public.class)
    private String nick_name;

    //more columns, constructs and getters and setters

TableA_TableB

    //... imports
    @Entity
    @Table(name="TableA_TableB")
    public class TableA_TableB{

    @Id
    @JsonView(Views.Public.class)
    private Integer id;

    @JsonView(Views.Public.class)
    private Integer nick_name_id;

    //more columns, constructs and getters and setters

I have TableAManager

import java.util.List;

import com.companyname.springapp.business.entities.TableA;

public interface TableAManager {

    public List<TableA> test(String name);
}

implementation JPATableAManager

import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.companyname.springapp.business.entities.TableA;
import com.companyname.springapp.business.repositories.TableARepository;

@Service
public class JPATableAManager implements TableAManager {

    @Autowired
    private TableARepository tableARepository;

    public List<TableA> getTableA() {
        return (List<TableA>) tableARepository.test();
    }

PD: excuse my english

Answer 1

Your SQL statement is missing spaces.

"SELECT DISTINCT a.*"
+"FROM TableB AS b, TableA_TableB AS ab, TableA AS a"
+"WHERE upper(b.nick_name) like upper(concat('%',:name,'%'))"
+"AND b.nick_name_id=ab.nick_name_id"
+"AND ab.id=a.id"

when concatenated will be "SELECT DISTINCT a.*FROM TableB AS b, TableA_TableB AS ab, TableA AS aWHERE upper(b.nick_name) like upper(concat('%',:name,'%'))AND b.nick_name_id=ab.nick_name_idAND ab.id=a.id"

This results in aWhere becoming the alias for TableA and the where clause not being parsed as such.

When constructing SQL statements in this fashion end each line with a space:

"SELECT DISTINCT a.* "
+"FROM TableB AS b, TableA_TableB AS ab, TableA AS a "
+"WHERE upper(b.nick_name) like upper(concat('%',:name,'%')) "
+"AND b.nick_name_id=ab.nick_name_id "
+"AND ab.id=a.id"