Dominik Dominik - 1 year ago 111
Java Question

Spring Data JPA filter Data in subclass with own query

I am working on a little JAVA project to improve my work with passwords. For this i use a H2 database to store the data and Spring data JPA as OR-Mapper. All DML operation works fine, but I do not want to allow the user to delete passwords from the database. For this I add a field in the Bean named DML_ART. The DML_ART is a string field, which is the state of the row. For example, if the user clicks on the JSF GUI on delete, the flag will set to "DELETE".

On the load of the data this line is to ignore. The SQL Query Works for DBPasswordEntity, but the subclass has also to filter by the same criteria.

But the written statement is not valide. And I dont know why.

@Table(name = "PasswordEntity",schema = "pwdmanager")
@PrimaryKeyJoinColumn(name = "password_id", referencedColumnName = "id")
public class DBPasswordEntity extends DBMetaDataEntity implements Serializable {

@Column(name = "PASSWORD", nullable = false, length = 4000)
private String password;

@OneToMany(fetch = FetchType.EAGER,cascade = ALL, mappedBy = "password")
private Set<DBExtendEntity> extend;

public void addExtend(DBExtendEntity ex)
if(extend == null)
extend = new LinkedHashSet<>();


@Table(name = "ExtendEntity",schema = "pwdmanager")
@PrimaryKeyJoinColumn(name = "extend_id", referencedColumnName = "id")
public class DBExtendEntity extends DBMetaDataEntity implements Serializable{

@Column(name = "NAME", nullable = false, length = 255)
private String name;

@ManyToOne(optional = false)
@JoinColumn(name = "password", referencedColumnName = "Password_ID")
private DBPasswordEntity password;


@Inheritance(strategy = InheritanceType.JOINED)
@DiscriminatorColumn(name = "pwd_type")
@Table(name = "MetaDataEntity",schema = "pwdmanager")
public abstract class DBMetaDataEntity {

@Column(name = "ID", nullable = false)
@GeneratedValue(strategy= GenerationType.IDENTITY)
private Integer id;

@Column(name = "DML_ART", nullable = false)
private String dml_art ="INSERT";


public interface PasswordRepository extends JpaRepository<DBPasswordEntity,String> {

@Query(value="SELECT pwd FROM DBPasswordEntity pwd WHERE pwd.dml_art <> 'DELETE' AND IN (SELECT FROM DBExtendEntity ext where ext.dml_art <> 'DELETE')")
List<DBPasswordEntity> findAll_DML_ART();

Answer Source

I solve it by switching to Hibernate.

My updatet POM


Filter in a subclass

 FROM DBPasswordEntity pwd WHERE pwd.mdae_dml_art <> 'DELETE' AND pwd.usre_user.usre_username = 'ABC' AND = (SELECT MAX(id) FROM DBPasswordEntity pwd2 WHERE pwd2.mdae_rev_ID = pwd.mdae_rev_ID)"

Execute the Querry


The full example with all configuration for Hibernate at:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download