I’m trying to achieve a very basic from SQL point of view operation but using Spring Boot and JPA
This is my Java data model:
<code>@MappedSuperclass
public abstract class BaseEntity {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "default_gen")
private Long id;
}
</code>
<code>@MappedSuperclass
public abstract class BaseEntity {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "default_gen")
private Long id;
}
</code>
@MappedSuperclass
public abstract class BaseEntity {
@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "default_gen")
private Long id;
}
<code>@Entity
@Table(name = "contacts")
@AttributeOverride(name = "id", column = @Column(name = "contact_id"))
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name="contact_type", discriminatorType = DiscriminatorType.STRING)
@SequenceGenerator(name = "default_gen", sequenceName = "contact_seq", allocationSize = 1)
public class ContactEntity extends BaseEntity {
@Column(name = "contact_type", insertable = false, updatable = false)
private String contactType;
}
</code>
<code>@Entity
@Table(name = "contacts")
@AttributeOverride(name = "id", column = @Column(name = "contact_id"))
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name="contact_type", discriminatorType = DiscriminatorType.STRING)
@SequenceGenerator(name = "default_gen", sequenceName = "contact_seq", allocationSize = 1)
public class ContactEntity extends BaseEntity {
@Column(name = "contact_type", insertable = false, updatable = false)
private String contactType;
}
</code>
@Entity
@Table(name = "contacts")
@AttributeOverride(name = "id", column = @Column(name = "contact_id"))
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name="contact_type", discriminatorType = DiscriminatorType.STRING)
@SequenceGenerator(name = "default_gen", sequenceName = "contact_seq", allocationSize = 1)
public class ContactEntity extends BaseEntity {
@Column(name = "contact_type", insertable = false, updatable = false)
private String contactType;
}
<code>@Entity
@DiscriminatorValue("person")
public class PersonEntity extends ContactEntity {
@Column(name = "firstname")
private String firstName;
@Column(name = "lastname")
private String lastName;
}
</code>
<code>@Entity
@DiscriminatorValue("person")
public class PersonEntity extends ContactEntity {
@Column(name = "firstname")
private String firstName;
@Column(name = "lastname")
private String lastName;
}
</code>
@Entity
@DiscriminatorValue("person")
public class PersonEntity extends ContactEntity {
@Column(name = "firstname")
private String firstName;
@Column(name = "lastname")
private String lastName;
}
<code>@Entity
@DiscriminatorValue("company")
public class CompanyEntity extends ContactEntity {
@Column(name = "companyname")
private String name;
}
</code>
<code>@Entity
@DiscriminatorValue("company")
public class CompanyEntity extends ContactEntity {
@Column(name = "companyname")
private String name;
}
</code>
@Entity
@DiscriminatorValue("company")
public class CompanyEntity extends ContactEntity {
@Column(name = "companyname")
private String name;
}
and my repository:
<code>public interface ContactRepository<T extends ContactEntity> extends JpaRepository<T, Long> {
</code>
<code>public interface ContactRepository<T extends ContactEntity> extends JpaRepository<T, Long> {
</code>
public interface ContactRepository<T extends ContactEntity> extends JpaRepository<T, Long> {
I’d like to be able to query the contacts table by Person or Company name, something like:
<code>select *
from contacts c
where lower(c.firstname || c.lastname) like '%smith%'
or lower(c.companyname) like '%smith%';
</code>
<code>select *
from contacts c
where lower(c.firstname || c.lastname) like '%smith%'
or lower(c.companyname) like '%smith%';
</code>
select *
from contacts c
where lower(c.firstname || c.lastname) like '%smith%'
or lower(c.companyname) like '%smith%';
so I started with this in my repository:
<code>Page<ContactEntity> findByNameContainingIgnoreCase(String name, PageRequest pageRequest);
</code>
<code>Page<ContactEntity> findByNameContainingIgnoreCase(String name, PageRequest pageRequest);
</code>
Page<ContactEntity> findByNameContainingIgnoreCase(String name, PageRequest pageRequest);
but at this throws an exception:
<code>Caused by: org.springframework.data.mapping.PropertyReferenceException: No property 'name' found for type 'ContactEntity'
</code>
<code>Caused by: org.springframework.data.mapping.PropertyReferenceException: No property 'name' found for type 'ContactEntity'
</code>
Caused by: org.springframework.data.mapping.PropertyReferenceException: No property 'name' found for type 'ContactEntity'
is there a way to query the contacts table using properties that do not exist on Contact POJO?
2
I managed to solve it using a custom query
<code>@Query(value = """
SELECT c
FROM ContactEntity c
WHERE LOWER(c.name) LIKE %:name%
OR LOWER(CONCAT(c.firstName, c.lastName)) LIKE %:name%
ORDER by id""")
Page<ContactEntity> findByNameContainingIgnoreCase(@Param("name") String name, PageRequest pageRequest);
</code>
<code>@Query(value = """
SELECT c
FROM ContactEntity c
WHERE LOWER(c.name) LIKE %:name%
OR LOWER(CONCAT(c.firstName, c.lastName)) LIKE %:name%
ORDER by id""")
Page<ContactEntity> findByNameContainingIgnoreCase(@Param("name") String name, PageRequest pageRequest);
</code>
@Query(value = """
SELECT c
FROM ContactEntity c
WHERE LOWER(c.name) LIKE %:name%
OR LOWER(CONCAT(c.firstName, c.lastName)) LIKE %:name%
ORDER by id""")
Page<ContactEntity> findByNameContainingIgnoreCase(@Param("name") String name, PageRequest pageRequest);