Long-time reader, first-time poster.
I need help. I can't figure out URL parameters ↔ DataBase.
Situation: I put .../users/?role=admin&title=manager into postman
Expected: a json of all the users who are managers and admins.
Actual: My computer blows up.
@RestController
@RequestMapping(path = USERS_PATH)
@Log4j2
public class UserController
// other code...
@GetMapping
public ResponseEntity<List<User>> getUserQuery(
@RequestParam( required = false, name = "name") String name,
@RequestParam( required = false, name = "title") String title,
@RequestParam( required = false, name = "roles") String roles,
@RequestParam( required = false, name = "email") String email,
@RequestParam( required = false, name = "password") String password
) {
log.info("Request received for getUserQuery");
return new ResponseEntity<>(userService.doSomething???(), HttpStatus.OK); // stuff I don't understand yet)
}
My Question: After the controller, what goes into the UserService and UserRepository?
Extra info: I'm using Spring Boot and H2 but may need to swap to PostgreSQL DB later.
I have the code below as a check for unique emails during puts and posts on the service layer, but I couldn't get something similar to work for this issue.
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
@Query("SELECT u FROM User u WHERE u.email = ?1")
Optional<User> findUserByEmail(String email);
}
Rant: This seems like a super common thing, and I should already know how to do this. I don't. I've been reading the internet for like 5 hours. Still no answer. I learned stuff about Specifications, Querydsl, @ModelAtribute, DAO, and mapping RequestParams. However, I couldn't find how to connect the dots. So, I went full caveman and tried making an @Query parameter using StringBuilder and a bunch of logic...I'm starting to spiral.
Anyway, I would like to avoid specifications & Querydsl. Just use @Query and JPA, but more importantly, I want a clean solution/ best practice.
CodePudding user response：
Here is a working snip. I think there is a problem with your repository. You are using @Query and also out-of-box support for queries.
What goes in Service and repository: The repository layer(repo) classes are used to abstract interactions with the DB.
The service layer interacts with repo layer and do the massaging of the data that the repository layer returns.
UserEntity
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import javax.persistence.*;
@Getter
@Setter
@Entity
@Table(name = "users")
@NoArgsConstructor
public class UserEntity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column(name = "name")
private String name;
@Column(name = "designation")
private String designation;
@Column(name = "email")
private String email;
}
UserRepository
import com.example.code.entity.UserEntity;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Repository;
import java.util.List;
import java.util.Optional;
@Repository
public interface UserRepository extends JpaRepository<UserEntity, Long> {
Optional<UserEntity> findByName(String name);
List<UserEntity> findByDesignation(String designation);
Optional<UserEntity> findByEmail(String email);
}
UserService
import java.util.Collection;
public interface UserService {
void createUser(UserDTO userDTO);
Collection<UserDTO> getUsers(
String username,
String designation,
String email
);
}
UserServiceImpl
import com.example.code.dto.UserDTO;
import com.example.code.entity.UserEntity;
import com.example.code.mapper.UserMapper;
import com.example.code.repository.UserRepository;
import lombok.RequiredArgsConstructor;
import org.apache.catalina.User;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import java.util.Collection;
import java.util.HashSet;
import java.util.Set;
import java.util.stream.Collectors;
@Service
@RequiredArgsConstructor
public class UserServiceImpl implements UserService {
private final UserRepository userRepository;
@Override
public void createUser(UserDTO userDTO) {
UserEntity userEntity = new UserEntity();
userEntity.setName(userDTO.getName());
userEntity.setDesignation(userDTO.getDesignation());
userEntity.setEmail(userDTO.getEmail());
userRepository.save(userEntity);
}
@Override
public Collection<UserDTO> getUsers(String username, String designation, String email) {
Set<UserDTO> userDTOS = new HashSet<>();
if( !username.isBlank() && !username.isEmpty() && userRepository.findByName(username).isPresent() ) {
userDTOS.add(UserMapper.toDto(
userRepository.findByName(username).get()
));
}
if(!designation.isBlank() && !designation.isEmpty()) {
userDTOS.addAll(
userRepository.findByDesignation(designation)
.stream()
.map(UserMapper::toDto)
.collect(Collectors.toSet())
);
}
if( !email.isBlank() &&
!email.isEmpty() &&
userRepository.findByEmail(email).isPresent() ) {
userDTOS.add(UserMapper.toDto(
userRepository.findByEmail(email).get()
));
}
return userDTOS;
}
}
UserMapper
import com.example.code.dto.UserDTO;
import com.example.code.entity.UserEntity;
public class UserMapper {
public static UserDTO toDto(UserEntity entity) {
UserDTO userDTO = new UserDTO();
userDTO.setName(entity.getName());
userDTO.setDesignation(entity.getDesignation());
userDTO.setEmail(entity.getEmail());
return userDTO;
}
}
TestController
@RestController
@RequestMapping("/test")
@RequiredArgsConstructor
public class TestController {
private final UserService userService;
@PostMapping
public ResponseEntity<String> createUser(@RequestBody final UserDTO userDTO) {
try {
userService.createUser(userDTO);
}catch (Exception e) {
return ResponseEntity.internalServerError().body("Failure");
}
return ResponseEntity.ok("Success");
}
@GetMapping
public ResponseEntity<Collection<UserDTO>> getUsers(
@RequestParam(value = "name", required = false) String name,
@RequestParam(value = "designation", required = false) String designation,
@RequestParam(value = "email", required = false) String email
) {
return ResponseEntity.ok(userService.getUsers(name, designation, email));
}
}
CodePudding user response：
5.1.6 Query By Example, here we go:
We don't need anything in our repo, since JpaRepository "has it already on board"!
Our service would look like:
package com.example;
import static org.springframework.data.domain.ExampleMatcher.GenericPropertyMatchers.exact;
import static org.springframework.data.domain.ExampleMatcher.GenericPropertyMatchers.ignoreCase;
import static org.springframework.data.domain.ExampleMatcher.GenericPropertyMatchers.startsWith;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Example;
import org.springframework.data.domain.ExampleMatcher;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.stereotype.Service;
@Service
public class PersonService {
@Autowired
JpaRepository<Person, Long> personRepository;
public List<Person> find(String name, String title, String roles, String email, String password) {
Person person = Person.of(name, title, roles, email, password);
ExampleMatcher matcher = ExampleMatcher
.matchingAny()
.withIgnoreNullValues()
.withMatcher("name", ignoreCase())
.withMatcher("title", startsWith().ignoreCase())
.withMatcher("roles", ignoreCase())
.withMatcher("email", ignoreCase())
.withMatcher("password", exact());
return personRepository.findAll(Example.of(person, matcher));
}
} // UN-TESTED, but compilable ;)
...but we see: how much "tweaking" is possible with just these five parameters.
...
Query by Example is well suited for several use cases:
Querying your data store with a set of static or dynamic constraints.
Frequent refactoring of the domain objects without worrying about breaking existing queries.
Working independently from the underlying data store API.
Query by Example also has several limitations:
No support for nested or grouped property constraints, such as
firstname = ?0 or (firstname = ?1 and lastname = ?2).Only supports starts/contains/ends/regex matching for strings and exact matching for other property types.
CodePudding user response：
I got this working, but I don't know if it's best practices.
controller:
@GetMapping
public ResponseEntity<List<User>> getUserQuery(
@RequestParam( required = false, name = "name") String name,
@RequestParam( required = false, name = "title") String title,
@RequestParam( required = false, name = "roles") String roles,
@RequestParam( required = false, name = "email") String email,
@RequestParam( required = false, name = "password") String password
) {
log.info("Request received for getUserQuery");
return new ResponseEntity<>(userService.QueryUsers(name, title, roles, email, password), HttpStatus.OK); // stuff I don't understand yet)
}
Service:
public List<User> QueryUsers(String name, String title, String roles,String email,String password) {
if (name == null && title == null && roles == null && email == null && password == null) {
return userRepository.findAll(Sort.by(Direction.ASC, "id"));
}
//TODO: make to lower case
List<User> users = new ArrayList<>();
users.addAll(userRepository.findAllUsersByName(name));
users.addAll(userRepository.findAllUsersByTitle(title));
users.addAll(userRepository.findAllUsersByRoles(roles));
users.addAll(userRepository.findAllUsersByEmail(email));
users.addAll(userRepository.findAllUsersByPassword(password));
return users;
}
Repository:
@Query("SELECT u FROM User u WHERE u.name = ?1")
Collection<? extends User> findAllUsersByName(String name);
@Query("SELECT u FROM User u WHERE u.title = ?1")
Collection<? extends User> findAllUsersByTitle(String title);
@Query("SELECT u FROM User u WHERE u.roles = ?1")
Collection<? extends User> findAllUsersByRoles(String roles);
@Query("SELECT u FROM User u WHERE u.email = ?1")
Collection<? extends User> findAllUsersByEmail(String email);
@Query("SELECT u FROM User u WHERE u.password = ?1")
Collection<? extends User> findAllUsersByPassword(String password);
Seems to be working, but I need to test it more.
CodePudding user response：
A combination of SQL AND, OR clause should serve your purpose. A sample is given below using a service and repository class.
UserService
public List<User> doingSomething(String name, String title, String roles, String email,String password) {
return userRepository.detailQuery(name, title, roles, email, password);
}
UserRepository
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
@Query("SELECT u FROM User u WHERE (:name is null or u.name = :name) and (:title is null or u.title = :title) and (:roles is null or u.roles = :roles) and (:email is null or u.email = :email) and (:password is null or u.password = :password)")
List<User> detailQuery(@Param("name") String name,
@Param("title") String title,
@Param("roles") String roles,
@Param("email") String email,
@Param("password") String password);
}