Change DB design to improve Spring Data JPA soft delete and authentication on queries

Question

I hope this question is not opinion based, but here we go. I am developing a small API with spring boot, spring data jpa, and JWT Token as the authentication system. The API is pretty straightforward, is an exam generator, where you can register professor, course, question, choice etc.

For example: [13/11/2017 - edited to include AbstractEntity]

@MappedSuperclass
public class AbstractEntity implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    protected Long id;
    protected boolean enabled = true;
    //equals, hashcode...
}

public class Professor extends AbstractEntity{ 
    private String email;
}

public class Course extends AbstractEntity {
    @ManyToOne(optional = false)
    private Professor professor;  
}

public class Question extends AbstractEntity {
    @ManyToOne(optional = false)
    private Course course;
}

Because the authentication system is JWT token for all queries I have to include the professor's information.

For example

public interface CourseRepository extends PagingAndSortingRepository<Course, Long> {
    @Query("select c from Course c where c.id = ?1 and c.professor = ?#{principal.professor} and c.enabled = true")
    Course findOne(Long id);
}

The problem is that for every crud query I will have to write my own custom query, because of professor's information and also the enabled field.

I know there is a way to extends the PagingAndSortingRepository and write your own custom query, like this:

public interface CustomPagingAndSortRepository<T, ID extends Serializable> extends PagingAndSortingRepository<T, ID> {
    @Override
    @Query("select e from #{#entityName} e where e.id = ?1 and e.professor = ?#{principal.professor} and e.enabled = true")
    T findOne(ID id);
    //More fields overrided here
}

This CustomPagingAndSortRepository would work perfectly for all classes if I only had enabled to worry about. This class will work perfectly for Course because e.professor = ?#{principal.professor} will find the Professor there. But it will not work for Question, for example, because I need a join with the course class to get the professor, like e.course.professor = ?#{principal.professor}

The point is f I put an association with Professor in all classes I can make that generic code work, save tons of code but sacrificing database design's normalization. The question is: Is there another way? or maybe is it valid to do it?

PS: The Question, Course etc will never change owner, it means that only the Professor who created will be able to use it.

Answer 1

Ok, I will answer my own question. So, the question was:

The point is if I put an association with Professor in all classes I can make that generic code work, save tons of code but sacrificing database design's normalization. The question is: Is there another way? or maybe is it valid to do it?

And I actually did, based on this answer and it reduced a lot of code.

Now I have my model classes like this (Included Professor in all other classes too):

@MappedSuperclass
public class AbstractEntity implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    protected Long id;
    @Column(columnDefinition = "boolean default true", nullable = false)
    private boolean enabled = true;
// ...
}

@Entity
public class Course extends AbstractEntity {
    private String name;
    @ManyToOne(optional = false)
    private Professor professor;
    //...
}

@Entity
public class Question extends AbstractEntity {
    private String title;
    @ManyToOne(optional = false)
    private Course course;
    @ManyToOne(optional = false)
    private Professor professor;
    // ...
}

And a CustomPagingAndSortRepository like this

@NoRepositoryBean
public interface CustomPagingAndSortRepository<T extends AbstractEntity, ID extends Long>
        extends PagingAndSortingRepository<T,ID> {
    @Override
    @Query("select e from #{#entityName} e where e.professor = ?#{principal.professor} and e.enabled = true")
    Iterable<T> findAll(Sort sort);

    @Override
    @Query("select e from #{#entityName} e where e.professor = ?#{principal.professor} and e.enabled = true")
    Page<T> findAll(Pageable pageable);

    @Override
    @Query("select e from #{#entityName} e where e.id =?1 and e.professor = ?#{principal.professor} and e.enabled = true")
    T findOne(Long id);

    @Override
    default boolean exists(Long id){
        return findOne(id) != null;
    }

    @Override
    @Query("select e from #{#entityName} e where e.professor = ?#{principal.professor} and e.enabled = true")
    Iterable<T> findAll();

    @Override
    @Query("select e from #{#entityName} e where e.professor = ?#{principal.professor} and e.enabled = true")
    Iterable<T> findAll(Iterable<ID> iterable);

    @Override
    @Query("select count(e) from #{#entityName} e where e.professor = ?#{principal.professor} and e.enabled = true")
    long count();

    @Override
    @Transactional
    @Modifying
    @Query("update #{#entityName} e set e.enabled=false where e.id=?1 and e.professor = ?#{principal.professor}")
    void delete(Long id);

    @Override
    @Transactional
    @Modifying
    default void delete(T t){
        delete(t.getId());
    }

    @Override
    @Transactional
    @Modifying
    default void delete(Iterable<? extends T> iterable){
        iterable.forEach(entity -> delete(entity.getId()));
    }

    @Override
    @Transactional
    @Modifying
    @Query("update #{#entityName} e set e.enabled=false where e.professor = ?#{principal.professor}")
    void deleteAll();
}

Doing this, all my basic queries will include automatically the name of the entity, the professor and if it is enabled. For example, my QuestionRepository is like this:

public interface QuestionRepository extends CustomPagingAndSortRepository<Question, Long> {
    @Query("select q from Question q where q.course.id = ?1 and q.title like %?2% and q.professor = ?#{principal.professor} and q.enabled = true")
    List<Question> listQuestionsByCourseAndTitle(long courseId, String title);
}

Now I don't have to worry about creating a custom delete, or custom findALL or deleteAll etc. to include Professor and enabled for every single class that I create.

So, the answer to my own question: in my opinion, it is valid to add an association with a professor in all classes (in cases like mine, using JWT Authentication). I was able to reduce a lot of code and it is a 100% Spring solution.

PS: Didn't test findAll with Sort and Pageable yet.

Answer 2

Why dont you create a class annotated @Service that Autowires your CourseRepository then pass id, principal.professor, enabled into it, then your repository will have the method Course findByIdAndProfessorAndEnabled(Long id, String professor, Boolean enabled). Code Snippet:

public interface CourseRepository extends PagingAndSortingRepository<Course, Long> {
    Course findByIdAndProfessorAndEnabled(Long id, Professor professor, Boolean enabled);
}

@Service
public class CourseService{
    @Autowired
    CourseRepository courseRepository;

    //pass in principal.professor from your controller or from wherever this is called
    public Course findByIdAndProfessorAndEnabled(Long id, Professor  professor, Boolean enabled){
        return courseRepository.findByIdAndProfessor(id, professor, enabled);
    }
}

EDIT: For Hibernate Models

If you always want enabled set to true in your queries you can use the @Where annotation on your model

@MappedSuperclass
@Where(clause="is_enabled=true")
public class AbstractEntity implements Serializable {
.
.
.
@Column(name="is_enabled")
protected boolean enabled = true;
...

I don't know if this approach would also work for your principal but you can try it