How to filter based on something other than ID

Question

I am trying to build a very small REST API with a mysql database. I am using Spring boot and Tomcat to deploy it locally. One of the services I am working on is to retrieve messages from the database. I have the ability to get ALL the messages created from the database, and I have the ability to filter based on ID but I want to be able to filter based on other parameters as well. The other parameters are called "messageCreated" and "messageSubscribed" they're both int parameters.

Here is my Controller class:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;
import java.util.NoSuchElementException;


@RestController
@RequestMapping("/messages")
 public class MessageController {
    @Autowired
     MessageService messageService;

@GetMapping("")
public List<Message> list() {
    return messageService.listAllMessage();
}


@GetMapping(value ={"/id"})
public ResponseEntity<Message> get(@PathVariable Integer id) {
    try {
        Message message = messageService.getMessage(id);
        return new ResponseEntity<Message>(message, HttpStatus.OK);
    } catch (NoSuchElementException e) {
        return new ResponseEntity<Message>(HttpStatus.NOT_FOUND);
    }
}

@PostMapping("/")
public void add(@RequestBody Message message) {
    messageService.saveMessage(message);
}
@PutMapping("/{id}")
public ResponseEntity<?> update(@RequestBody Message message, @PathVariable Integer id) {
    try {
        Message existMessage = messageService.getMessage(id);
        message.setId(id);
        messageService.saveMessage(message);
        return new ResponseEntity<>(HttpStatus.OK);
    } catch (NoSuchElementException e) {
        return new ResponseEntity<>(HttpStatus.NOT_FOUND);
    }
}
@DeleteMapping("/{id}")
public void delete(@PathVariable Integer id) {

    messageService.deleteMessage(id);
   }
}

Answer 1

1. Use a java stream pipeline.
   assuming that there's a method filtering the result of your listAllMessage(), we say, buildMsgsFromWanted

List<Message> buildMsgsFromWanted(List<Message> allResults){
  // there should be some codes to check the validation of allResults, like ifEmpty() or something like this. then 
  List<Message> createdMsgs = allResult.stream().filter((item)->item.messageCreated()).collect(Collectors.toList());
  return createdMsgs;
}

2. Just query the wanted items.
   if ur using MyBatis, in the mapper, selectAllMessages() should be like this:

List<Message> selectAllMessages(Message filter);

and in the mapper xml file, the sql should be like:

<select id="selectAllMessages" parameterType="XXX.XXX.XXX.Message" resultMap="messageMap">
  select m.fieldA, m.fieldB, m.filedC, ...., m.is_created, m.is_subscribed from message m
  <where>
    <if test="messageCreated!=null"> and m.is_created=#{messageCreated}</if>
    <if test="messageSubscribed!=null"> and m.is_subscribed=#{messageSubscribed}</if>
  </where>
</select>

so whether the messageCreated and messageSubscribed are null or not in the parameter message you pass to selectAllMessage decides the result, or actually, the sql executed. just call the select method like:

Message queryObj = new Message();
queryObj.setCreated(null);
queryObj.setSubscribed(1);
mapper.selectAllMessage(queryObj);

then you'll get the subscribed messages because the argument in xml file if test="messageSubscribed!=null" is true and where m.is_subscribed=1 will be appended to the query sql.The downside is that you must maintain all the parameters as fields in class Message on which the filter may based.
It's the same in other dao frameworks, just control the 2 parameters finally passed to sql executed.

Answer 2

If you are using Spring data then use @Query to filter . more example find in the below url. https://www.baeldung.com/spring-data-jpa-query