I'm building an API for the company where I work and it is my first time using Spring Boot, so I'm having some doubts. So I have a One-To-Many unidirectional relationship, from my parent class (Compania) to my child class (Office). The thing is that I don't know how to query for the Office objects that are part of the Compania object. I think it should be a straightforward solution, but I can't manage to find it.
Hibernate creates for me an extra table that is called COMPANIA_OFFICE and I don't know how to use it with a SQL or JPQL query. So right now in my database, I have a table called COMPANIA, another one called OFFICE, and the last one called COMPANIA_OFFICE (this one contains COMPANIA_ID and OFFICE_ID, as I don't have the FK in either of the 2 classes it uses an auxiliary table to join them, I suppose).
What I want to achieve is in a GET request like this one:
GET /companias/{companiaId}/office/{officeName} --> Return a list with the offices that have that name from a specified company by id.
The classes that I have now are these:
Compania
@Entity(name = "Compania")
@Table(
name = "compania"
)
public class Compania {
@Id
@SequenceGenerator(
name = "compania_sequence",
sequenceName = "compania_sequence",
allocationSize = 1
)
@GeneratedValue(
strategy = GenerationType.SEQUENCE,
generator = "compania_sequence"
)
@Column(
nullable = false
)
private Long id;
@Column(
name = "name",
nullable = false,
unique = true
)
private String name;
@OneToMany(
mappedBy = "compania",
cascade = CascadeType.ALL,
fetch = FetchType.LAZY,
orphanRemoval = true
)
private List<Office> office;
...Constructors...
... Getters and Setters...
Office (Child)
@Entity()
@Table()
public class Office {
@Id
@SequenceGenerator(
name = "office_sequence",
sequenceName = "office_sequence",
allocationSize = 1
)
@GeneratedValue(
strategy = GenerationType.SEQUENCE,
generator = "office_sequence"
)
@Column(
nullable = false
)
@JsonIgnore
private Long id;
@Column(
name = "idRef",
nullable = false
)
private int idRef;
@Column(
name = "title",
nullable = false
)
private String title;
@Column(
name = "name"
)
private String name;
...Constructors...
... Getters and Setters...
So my problem is that I don't know how to make that query in spring Boot. I don't if using straight SQL or JPQL.
Thanks in advance!
Edit. I've tried using a native Query like this:
@Query(
value = "select o.* from office o "
"inner join compania_office co on o.id = co.office_id "
"inner join compania c on c.id = co.compania_id "
"where c.id = :companiaId and lower(o.title) like lower(:officeTitle) "
"order by o.id_ref asc;",
nativeQuery = true
)
List<Office> findOfficesByTitleLike(@Param("companiaId")Long companiaId,@Param("officeTitle") String officeTitle);
It works when I'm using a Database manager like Navicat or the one embedded in IntelliJ doing a common query. But when I try to use it with my API it gives me this error:
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Do you know what could be happening? Thanks!
CodePudding user response:
Recently I had a similar issue. I managed it to fetch data from two Entities/tables by using JDBC template. However, first I created a class where are included only the columns that I require. The following is my solution and the suggestion for your issue:
@GetMapping("/testi2")
public List<CustomerResponse> getCCP2() {
String sql = "SELECT top 20 c.ID, c.CustomerNumber, c.Name, c.Surname, c.Area, c.City, c.Address, c.PhoneNumber, c.CustomerTypeID, c.Enabled, c.DateCreated, p.Id, p.Debit, p.Credit\n"
"FROM TblCustomer c OUTER APPLY\n"
" (SELECT TOP 1 p.*\n"
" FROM TblPayments p\n"
" WHERE c.CustomerNumber = p.CustomerNumber\n"
" ORDER BY p.id DESC \n"
" ) p;";
List<CustomerResponse> result = jdbcTemplate.query(
sql,
new BeanPropertyRowMapper(CustomerResponse.class));
return result;
}
where class <CustomerResponse>
is holding "ID, CustomerNumber, Name, Surname, Area, City, Address, PhoneNumber, CustomerTypeID, Enabled, c.DateCreated, Id, Debit, Credit".
CodePudding user response:
I don't know if that's your problem, but I had a similar issue recently and the string param, :officeTitle
in your case, it wasn't being passed as ':officeTitle'
(SQL varchar) to sql, so I have solved it casting the param to varchar(255).
... like lower(cast(:officeTitle AS varchar(255)))
I think nativeQuery doesn't cast the parameter automatically