Dominik Dominik - 1 year ago 122
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.

DBPasswordEntity.java

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

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

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

@Transient
public void addExtend(DBExtendEntity ex)
{
if(extend == null)
{
extend = new LinkedHashSet<>();
}
extend.add(ex);
}

}


DBExtendEntity.java

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

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

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


DBMetaDataEntity

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

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

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


SQL-Filter-Operation

@Transactional
public interface PasswordRepository extends JpaRepository<DBPasswordEntity,String> {

@Query(value="SELECT pwd FROM DBPasswordEntity pwd WHERE pwd.dml_art <> 'DELETE' AND pwd.extend.id IN (SELECT ext.id 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

  <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>${hibernate.version}</version>
            <exclusions>
                <exclusion>
                    <artifactId>jboss-logging</artifactId>
                    <groupId>org.jboss.logging</groupId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-validator</artifactId>
            <version>${hibernate-validator.version}</version>
        </dependency>
        <dependency>
            <groupId>javax.transaction</groupId>
            <artifactId>jta</artifactId>
            <version>${jta.version}</version>
        </dependency>

Filter in a subclass

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

Execute the Querry

this.session.createSQLQuery(sql).executeUpdate();

The full example with all configuration for Hibernate at: https://github.com/dominikLudwig/Passwordmanager

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