Multiple Conditions Examples

AND Condition (1)

Java

DataManipulationModel dataManipulationModel = new DataManipulationModel();
dataManipulationModel.setCriteria(
      and(
              condition("community.className", Operation.LIKE, "%9%"),
              condition("gpa", Operation.GREATER_THAN, 3.5)
      ));
GeneralSpecification<Student> studentGeneralSpecification = new GeneralSpecification<>(dataManipulationModel);
studentRepository.findAll(studentGeneralSpecification);

SQL

select
      s1_0.id,
      s1_0.additional_info,
      s1_0.address,
      s1_0.community_id,
      s1_0.date_of_birth,
      s1_0.email,
      s1_0.enrollment_date,
      s1_0.first_name,
      s1_0.gpa,
      s1_0.gpa_letter,
      s1_0.is_full_time,
      s1_0.last_name,
      s1_0.phone_number 
  from
      student s1_0 
  join
      community c1_0 
          on c1_0.id=s1_0.community_id 
  where
      c1_0.class_name like ? escape '' 
      and s1_0.gpa>? 

AND Condition (2)

Java

DataManipulationModel dataManipulationModel = new DataManipulationModel();
dataManipulationModel.setCriteria(
      and(
              condition("community.className", Operation.LIKE, "%9%"),
              condition("gpa", Operation.GREATER_THAN, 3.5),
              condition("additionalInfo", Operation.IS_NOT_NULL)
      )
);
GeneralSpecification<Student> studentGeneralSpecification = new GeneralSpecification<>(dataManipulationModel);
studentRepository.findAll(studentGeneralSpecification);

SQL

  select
      s1_0.id,
      s1_0.additional_info,
      s1_0.address,
      s1_0.community_id,
      s1_0.date_of_birth,
      s1_0.email,
      s1_0.enrollment_date,
      s1_0.first_name,
      s1_0.gpa,
      s1_0.gpa_letter,
      s1_0.is_full_time,
      s1_0.last_name,
      s1_0.phone_number 
  from
      student s1_0 
  join
      community c1_0 
          on c1_0.id=s1_0.community_id 
  where
      c1_0.class_name like ? escape '' 
      and s1_0.gpa>? 
      and s1_0.additional_info is not null 

OR Condition (1)

Java

DataManipulationModel dataManipulationModel = new DataManipulationModel();
dataManipulationModel.setCriteria(
      or(
              condition("gpaLetter", Operation.EQUAL, "A"),
              condition("gpaLetter", Operation.EQUAL, "A-"),
              condition("gpaLetter", Operation.EQUAL, "A+")
      )
);
GeneralSpecification<Student> studentGeneralSpecification = new GeneralSpecification<>(dataManipulationModel);
studentRepository.findAll(studentGeneralSpecification);

SQL

  select
      s1_0.id,
      s1_0.additional_info,
      s1_0.address,
      s1_0.community_id,
      s1_0.date_of_birth,
      s1_0.email,
      s1_0.enrollment_date,
      s1_0.first_name,
      s1_0.gpa,
      s1_0.gpa_letter,
      s1_0.is_full_time,
      s1_0.last_name,
      s1_0.phone_number 
  from
      student s1_0 
  where
      s1_0.gpa_letter=? 
      or s1_0.gpa_letter=? 
      or s1_0.gpa_letter=? 

OR Condition (2)

Java

DataManipulationModel dataManipulationModel = new DataManipulationModel();
dataManipulationModel.setCriteria(
      or(
              condition("community.className", Operation.EQUAL, "9th B"),
              condition("community.className", Operation.EQUAL, "9th A")
      )
);
GeneralSpecification<Student> studentGeneralSpecification = new GeneralSpecification<>(dataManipulationModel);
studentRepository.findAll(studentGeneralSpecification);

SQL

  select
      s1_0.id,
      s1_0.additional_info,
      s1_0.address,
      s1_0.community_id,
      s1_0.date_of_birth,
      s1_0.email,
      s1_0.enrollment_date,
      s1_0.first_name,
      s1_0.gpa,
      s1_0.gpa_letter,
      s1_0.is_full_time,
      s1_0.last_name,
      s1_0.phone_number 
  from
      student s1_0 
  join
      community c1_0 
          on c1_0.id=s1_0.community_id 
  where
      c1_0.class_name=? 
      or c1_0.class_name=? 

AND and OR Condition (1)

Java

DataManipulationModel dataManipulationModel = new DataManipulationModel();
dataManipulationModel.setCriteria(
      and(
              or(
                      condition("community.className", Operation.EQUAL, "9th B"),
                      condition("community.className", Operation.EQUAL, "9th A")
              ),
              condition("gpaLetter", Operation.IN, "A", "A-")
      )
);
GeneralSpecification<Student> studentGeneralSpecification = new GeneralSpecification<>(dataManipulationModel);
studentRepository.findAll(studentGeneralSpecification);

SQL

  select
      s1_0.id,
      s1_0.additional_info,
      s1_0.address,
      s1_0.community_id,
      s1_0.date_of_birth,
      s1_0.email,
      s1_0.enrollment_date,
      s1_0.first_name,
      s1_0.gpa,
      s1_0.gpa_letter,
      s1_0.is_full_time,
      s1_0.last_name,
      s1_0.phone_number 
  from
      student s1_0 
  join
      community c1_0 
          on c1_0.id=s1_0.community_id 
  where
      (
          c1_0.class_name=? 
          or c1_0.class_name=?
      ) 
      and s1_0.gpa_letter in (?, ?) 

AND and OR Condition (2)

Java

DataManipulationModel dataManipulationModel = new DataManipulationModel();
dataManipulationModel.setCriteria(
      or(
              and(
                      condition("gpaLetter", Operation.IN, "A", "A-"),
                      condition("community.className", Operation.EQUAL, "9th B")
              ),
              condition("community.className", Operation.EQUAL, "9th A")
      )
);
GeneralSpecification<Student> studentGeneralSpecification = new GeneralSpecification<>(dataManipulationModel);
studentRepository.findAll(studentGeneralSpecification);

SQL

  select
      s1_0.id,
      s1_0.additional_info,
      s1_0.address,
      s1_0.community_id,
      s1_0.date_of_birth,
      s1_0.email,
      s1_0.enrollment_date,
      s1_0.first_name,
      s1_0.gpa,
      s1_0.gpa_letter,
      s1_0.is_full_time,
      s1_0.last_name,
      s1_0.phone_number 
  from
      student s1_0 
  join
      community c1_0 
          on c1_0.id=s1_0.community_id 
  where
      s1_0.gpa_letter in (?, ?) 
      and c1_0.class_name=? 
      or c1_0.class_name=? 

AND and OR Condition (3)

Java

DataManipulationModel dataManipulationModel = new DataManipulationModel();
dataManipulationModel.setCriteria(
      and(
              condition("gpaLetter", Operation.IN, "A", "A-"),
              or(
                      condition("community.className", Operation.EQUAL, "7th C"),
                      condition("community.className", Operation.EQUAL, "9th B"),
                      condition("community.className", Operation.EQUAL, "9th A")
              )
      )
);
GeneralSpecification<Student> studentGeneralSpecification = new GeneralSpecification<>(dataManipulationModel);
studentRepository.findAll(studentGeneralSpecification);

SQL

  select
      s1_0.id,
      s1_0.additional_info,
      s1_0.address,
      s1_0.community_id,
      s1_0.date_of_birth,
      s1_0.email,
      s1_0.enrollment_date,
      s1_0.first_name,
      s1_0.gpa,
      s1_0.gpa_letter,
      s1_0.is_full_time,
      s1_0.last_name,
      s1_0.phone_number 
  from
      student s1_0 
  join
      community c1_0 
          on c1_0.id=s1_0.community_id 
  where
      s1_0.gpa_letter in (?, ?) 
      and (
          c1_0.class_name=? 
          or c1_0.class_name=? 
          or c1_0.class_name=?
      ) 

AND and OR Condition (4)

Java

DataManipulationModel dataManipulationModel = new DataManipulationModel();
dataManipulationModel.setCriteria(
      or(
              condition("gpaLetter", Operation.EQUAL, "A+"),
              condition("gpaLetter", Operation.EQUAL, "A"),
              and(
                      condition("gpaLetter", Operation.EQUAL, "A-"),
                      condition("community.className", Operation.EQUAL, "7th C")
              ),
              condition("community.className", Operation.EQUAL, "9th B"),
              condition("community.className", Operation.EQUAL, "9th A")
      )
);
GeneralSpecification<Student> studentGeneralSpecification = new GeneralSpecification<>(dataManipulationModel);
studentRepository.findAll(studentGeneralSpecification);

SQL

  select
      s1_0.id,
      s1_0.additional_info,
      s1_0.address,
      s1_0.community_id,
      s1_0.date_of_birth,
      s1_0.email,
      s1_0.enrollment_date,
      s1_0.first_name,
      s1_0.gpa,
      s1_0.gpa_letter,
      s1_0.is_full_time,
      s1_0.last_name,
      s1_0.phone_number 
  from
      student s1_0 
  join
      community c1_0 
          on c1_0.id=s1_0.community_id 
  where
      s1_0.gpa_letter=? 
      or s1_0.gpa_letter=? 
      or s1_0.gpa_letter=? 
      and c1_0.class_name=? 
      or c1_0.class_name=? 
      or c1_0.class_name=? 

AND and OR Condition (5)

Java

DataManipulationModel dataManipulationModel = new DataManipulationModel();
dataManipulationModel.setCriteria(
      or(
              and(
                      condition("id", Operation.IN, 1, 2, 3, 4),
                      condition("firstName", Operation.LIKE, "%a%")
              ),
              and(
                      condition("id", Operation.IN, 11, 12, 13, 14),
                      condition("firstName", Operation.LIKE, "%s%")
              )
      )
);
GeneralSpecification<Student> studentGeneralSpecification = new GeneralSpecification<>(dataManipulationModel);
studentRepository.findAll(studentGeneralSpecification);

SQL

  select
      s1_0.id,
      s1_0.additional_info,
      s1_0.address,
      s1_0.community_id,
      s1_0.date_of_birth,
      s1_0.email,
      s1_0.enrollment_date,
      s1_0.first_name,
      s1_0.gpa,
      s1_0.gpa_letter,
      s1_0.is_full_time,
      s1_0.last_name,
      s1_0.phone_number 
  from
      student s1_0 
  where
      s1_0.id in (?, ?, ?, ?) 
      and s1_0.first_name like ? escape '' 
      or s1_0.id in (?, ?, ?, ?) 
      and s1_0.first_name like ? escape '' 

AND and OR Condition (6)

Java

DataManipulationModel dataManipulationModel = new DataManipulationModel();
dataManipulationModel.setCriteria(
      and(
              or(
                      condition("id", Operation.IN, 1, 2, 3, 4),
                      condition("firstName", Operation.LIKE, "%a%")
              ),
              or(
                      condition("id", Operation.IN, 11, 12, 13, 14),
                      condition("firstName", Operation.LIKE, "%s%")
              )
      )
);
GeneralSpecification<Student> studentGeneralSpecification = new GeneralSpecification<>(dataManipulationModel);
studentRepository.findAll(studentGeneralSpecification);

SQL

  select
      s1_0.id,
      s1_0.additional_info,
      s1_0.address,
      s1_0.community_id,
      s1_0.date_of_birth,
      s1_0.email,
      s1_0.enrollment_date,
      s1_0.first_name,
      s1_0.gpa,
      s1_0.gpa_letter,
      s1_0.is_full_time,
      s1_0.last_name,
      s1_0.phone_number 
  from
      student s1_0 
  where
      (
          s1_0.id in (?, ?, ?, ?) 
          or s1_0.first_name like ? escape ''
      ) 
      and (
          s1_0.id in (?, ?, ?, ?) 
          or s1_0.first_name like ? escape ''
      ) 

AND and OR Condition (7)

Java

DataManipulationModel dataManipulationModel = new DataManipulationModel();
dataManipulationModel.setCriteria(
      or(
              condition("id", Operation.IN, 1, 2, 3, 4),
              condition("firstName", Operation.LIKE, "%a%"),
              and(
                      condition("id", Operation.IN, 11, 12, 13, 14),
                      condition("firstName", Operation.LIKE, "%s%")
              )
      )
);
GeneralSpecification<Student> studentGeneralSpecification = new GeneralSpecification<>(dataManipulationModel);
studentRepository.findAll(studentGeneralSpecification);

SQL

  select
      s1_0.id,
      s1_0.additional_info,
      s1_0.address,
      s1_0.community_id,
      s1_0.date_of_birth,
      s1_0.email,
      s1_0.enrollment_date,
      s1_0.first_name,
      s1_0.gpa,
      s1_0.gpa_letter,
      s1_0.is_full_time,
      s1_0.last_name,
      s1_0.phone_number 
  from
      student s1_0 
  where
      s1_0.id in (?, ?, ?, ?) 
      or s1_0.first_name like ? escape '' 
      or s1_0.id in (?, ?, ?, ?) 
      and s1_0.first_name like ? escape '' 

AND and OR Condition (8)

Java

DataManipulationModel dataManipulationModel = new DataManipulationModel();
dataManipulationModel.setSortModel(asc("community.id"),
      desc("firstName"));
dataManipulationModel.setCriteria(
      and(
              condition("gpa", Operation.GREATER_THAN, 3.5),
              condition("community.className", Operation.LIKE, "%9%"),
              or(
                      condition("additionalInfo", Operation.IS_NULL),
                      condition("additionalInfo", Operation.IS_EMPTY_STRING)
              )
      )
);
GeneralSpecification<Student> studentGeneralSpecification = new GeneralSpecification<>(dataManipulationModel);
studentRepository.findAll(studentGeneralSpecification);

SQL

  select
      s1_0.id,
      s1_0.additional_info,
      s1_0.address,
      s1_0.community_id,
      s1_0.date_of_birth,
      s1_0.email,
      s1_0.enrollment_date,
      s1_0.first_name,
      s1_0.gpa,
      s1_0.gpa_letter,
      s1_0.is_full_time,
      s1_0.last_name,
      s1_0.phone_number 
  from
      student s1_0 
  join
      community c1_0 
          on c1_0.id=s1_0.community_id 
  where
      s1_0.gpa>? 
      and c1_0.class_name like ? escape '' 
      and (
          s1_0.additional_info is null 
          or s1_0.additional_info=?
      ) 
  order by
      s1_0.community_id,
      s1_0.first_name desc 

AND and OR and NOT Condition

Java

DataManipulationModel dataManipulationModel = new DataManipulationModel();
dataManipulationModel.setCriteria(
      and(
              not(
                      or(
                          condition("id", Operation.IN, 1, 2, 3, 4),
                          condition("firstName", Operation.LIKE, "%a%")
                      )
              ),
              not(
                      or(
                          condition("id", Operation.IN, 11, 12, 13, 14),
                          condition("firstName", Operation.LIKE, "%s%")
                      )
              )
      )
);
GeneralSpecification<Student> studentGeneralSpecification = new GeneralSpecification<>(dataManipulationModel);
studentRepository.findAll(studentGeneralSpecification);

SQL

  select
      s1_0.id,
      s1_0.additional_info,
      s1_0.address,
      s1_0.community_id,
      s1_0.date_of_birth,
      s1_0.email,
      s1_0.enrollment_date,
      s1_0.first_name,
      s1_0.gpa,
      s1_0.gpa_letter,
      s1_0.is_full_time,
      s1_0.last_name,
      s1_0.phone_number 
  from
      student s1_0 
  where
      not(s1_0.id in (?, ?, ?, ?) 
      or s1_0.first_name like ? escape '') 
      and not(s1_0.id in (?, ?, ?, ?) 
      or s1_0.first_name like ? escape '')