java sql query how can i select all data without parameter

Question

Hi lately i've been studying about REST API. I was wondering how i can get all data if there is no parameter. I only can get the data by categoryId like below. http:// .... /categorys?categoryId=2 data is selected and displayed like this.

{
  "items": [
  {
  "id": 2,
  "name": "music",
  "count": 20
  }   
 ]
}

But I also wanted to get all data without parameter like below => http:// .... /categorys

{
  "items": [
      {
        "id": 1,
        "name": "movie",
        "count": 10
      },
      {
        "id": 2,
        "name": "music",
        "count": 20
      },
      {
        "id": 3,
        "name": "book",
        "count": 16
      }    
   ]
}

this is mysql query for this category data

SELECT_CATEGORYS = "SELECT category.id as id, category.name as name, count(*) as count FROM product inner join category where product.category_id = category.id group by category.id, category.name;"

and this is CategoryApiController.java

@RestController
@RequestMapping(path="/categorys")
public class CategoryApiController {
@Autowired
ReservationService reservationService;

@GetMapping
public Map<String, Object> categorys(@RequestParam(name="categoryId", required=false, defaultValue="0") int categoryId) {
    
    List<Categorys> list = reservationService.getCategorys(categoryId);
    Map<String, Object> map = new HashMap<>();
    map.put("item", list);
    
    return map;
  }
}

please , help me solve this. thanks in advance. path should not change, I need to keep this getmapping /categorys so without parameter it should be /categorys and with parameter, it should be like this /categories?categoryId = 2

does this help ?https://stackoverflow.com/questions/19977130/joining-two-table-entities-in-spring-data-jpa — Harmandeep Singh Kalsi, Jun 25 '20 at 06:23

score 1 · Answer 1 · answered Jun 25 '20 at 06:29

Query

 SELECT_CATEGORYS = "SELECT category.id as id, category.name as name, count(*) as count FROM product inner join category group by category.id, category.name;"

Controller

@RestController
    @RequestMapping(path="/categorys")
    public class CategoryApiController {
    @Autowired
    ReservationService reservationService;
    @GetMapping
    public Map<String,List<Category>> categorys() {
        List<Categorys> list = reservationService.getCategories();
        Map<String, Object> map = new HashMap<>();
        map.put("item", list);
        return map;
      }
    }

score 0 · Answer 2 · edited Jun 25 '20 at 06:32

0

you should not use categoryId on getMapping and on query. on your query, it can be this, SELECT_CATEGORYS = "SELECT category.id as id, category.name as name, count(*) as count FROM product inner join category group by category.id, category.name;" and on your getMapping, you should not have any param. it can be just like

@GetMapping
public Map<String, Object> categorys()

edited Jun 25 '20 at 06:32

tgallei

827
3
13
22

answered Jun 25 '20 at 06:23

fatma zehra güç

164
5

score 0 · Answer 3 · answered Jun 25 '20 at 06:29

0

You can make another method like :

@GetMapping("/all")
public Map<String, Object> getAllCategories(){
    
    List<Categorys> list = reservationService.getAllCategories(); // and this method use your query
    Map<String, Object> map = new HashMap<>();
    map.put("item", list);
    
    return map;
  }
}

and when you call it , use : http:// .... /categorys/all . Also you have a misspelling, the plural for category is categories, but this is not important atm.

answered Jun 25 '20 at 06:29

Jome M.

3
4

thanks for the comment but this is not what i'm looking for. I wanted to keep requestmapping /categorys or /categories whatever. – Eunsim Kang Jun 25 '20 at 12:38
then, you can simply delete you parameter @RequestParam(name="categoryId", required=false, defaultValue="0") int categoryId and call the reservationService.getCategories() without 'id' param – Jome M. Jun 25 '20 at 13:48

java sql query how can i select all data without parameter

3 Answers3