[FIXED] write where condition for joined table

Issue

I have 2 tables table user have these fields userId email Phone activeFlag

Table Comments have these fields userId Comments Flag

I want to create end query like this

Select * from users a left join Comments b on a.user_id and b.user_id where a.activeFlag='Y' and b.Flag='Y' and a.userId='myuser'

I have created mapping for both the tables where both the table are mapping with OneToOne mapping

I have written below predicates for it

public static Specification<User> findUser(String username){
        return ((root,criteriaQuery,cb) ->{
            List<Predicate> predicates = new ArrayList<Predicate>();
            
            
            Root<Comments> rootComment = criteriaQuery.from(Comments.class);
            predicates.add(cb.equal(root.get("userId"),username));
            predicates.add(cb.equal(root.get("activeFlag"),"N"));
            
            
            criteriaQuery.where(
                    cb.equal(rootComment.get("Flag"),"N")
                    );
            
            return  cb.and(predicates.toArray(new Predicate[] {}));
            
    }

But it is not working as expected. it is generating below query and its not including where clause for Flag

select comment0_.user_id as user_id1_2_0_, comment0_.active_flag as active_f2_2_0_, comment0_.comments as comments3_2_0_, comment0_.create_date as create_d4_2_0_, comment0_.create_user_id as create_u5_2_0_ from comment comment0_ where comment0_.user_id=?

Solution

I would consider using Springs JPARepository since its lot easier than using jpql/criteria queries for what you described.

I assume following model from your description:

@Entity
public class User {
    @Id
    private String userId;

    private String email;
    private String phone;
    private boolean activeFlag;

    @OneToOne
    private Comment comment;

    // Getters + Settes
}

@Entity
public class Comment {
    @Id
    private String userId;

    private String email;
    private String phone;
    private boolean activeFlag;

    @OneToOne
    private User user;

}

Than you could use following repository

@Repository
public interface UserRepository extends JpaRepository<User, String> {
    User findByUserIdAndActiveFlagTrue(String userId);
}

And use it somewhere like that:

public class SomeClass {
    private UserRepository userRepository;

    @Autowired
    public SomeClass(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    public User findUser(String userName) { // Should be userId, but I match your example
        var user = userRepository.findByUserIdAndActiveFlagTrue(userName);
        return user;
    }

    public void useIt(){
        var myuser = findUser("myuser");

        //access user data
        myuser.getEmail();

        //access comment
        var comment = myuser.getComment();
        comment.getComments(); //comments field from your description
    }
}

I would suggest reading this baeldung tutorial for better understanding: https://www.baeldung.com/the-persistence-layer-with-spring-data-jpa

Generally: for me it seems you should rethink your model design because:

• A oneToOne relationship between User and Comment seems weird. Does a use can only have one comment at all time? I think you try avoid using manyToMany since Comments have a field comments (plural) where you probably store more than one comment.

• User and Comment have the same named Id column userId. Just name it id an it should be unique and not shared between user and the comment the user owns

• userId is String: It's not generally wrong but it cant be autoincremented or auto generated for example. Furthermore you use the username as an id, which you should avoid. Search for "natural key vs surrogate key" on the internet. I would recommend using a Long as id or even better an UUID (both surrogate key)

Answered By - jhueg
Answer Checked By - Marie Seifert (JavaFixing Admin)