object which contains List of some items cant expose rest data with single query

Question

During creating some services i face some issue with performance, for example: to call service and get response(for one object Recipe which contains list of RecipeComponents - it took almost 2000ms), what looks like this:

{
    "recipe_id": 55,
    "recipeName": "Pizza",
    "weight": 450,
    "aproxPrice": 12,
    "raiting": 7,
    "caloriesCat": 1,
    "foodCat": 2,
    "calories": 300,
    "user_id": 500,
    "publishDate": 1505858400000,
    "recipeComponents": [
        {
            "component_id": 139,
            "componentName": "veges",
            "componentWeight": 100,
            "componentDescription": "Some desc",
            "componentCalories": 200
        },
        {
            "component_id": 140,
            "componentName": "rice",
            "componentWeight": 100,
            "componentDescription": "some stuff",
            "componentCalories": 350
        },
        {
            "component_id": 141,
            "componentName": "tomato",
            "componentWeight": 100,
            "componentDescription": "XXXXXXX",
            "componentCalories": 150
        },
        {
            "component_id": 142,
            "componentName": "souce",
            "componentWeight": 100,
            "componentDescription": "xxsdsds",
            "componentCalories": 250
        }
    ]
}

for 70+ recipes(with components) to get response it takes around 10000ms

the reason is service method, actually call database 2 times(at the beggining i didnt realise its quite slow):

@Override
public List<Recipe> getAllRecipes() {
    List<Recipe> recipes = recipeRepository.getAllRecipes(); <- first time to get all recipes
    for (Recipe recipe : recipes) {
        List<RecipeComponent> components = recipeComponentRepository
                .findAllComponentsAssignedToRecipe(recipe.getRecipe_id()); <- 2nd time to get components assigned to recipe.
        recipe.setRecipeComponents(components);
    }
    return recipes;

in repository tier i have two classes : RecipeRowMapper and RecipeComponentRowMapper to map data- which works fine for single queries as you can see above.

So i decided to use ResultSetExtractor in repository tier, where will be single query with left join/inner join statement.

@Override
public List<Recipe> getAllRecipesWithSingleQuery() {

    List<Recipe> recipes = jdbcTemplate.query(SqlRecipeStaticData.sGetAllRecipesWithSingleQuery,
            new ResultSetExtractor<List<Recipe>>() {

                public RecipeComponentRowMapper componentRowMapper = new RecipeComponentRowMapper();

                public RecipeRowMapper recipeRowMapper = new RecipeRowMapper();

                @Override
                public List<Recipe> extractData(ResultSet rs) throws SQLException, DataAccessException {
                    List<Recipe> recipes = new ArrayList<>();
                    Integer recipeId = null;
                    Recipe currentRecipe = null;
                    int recipeIdx = 0;
                    int componentIdx = 0;
                    while (rs.next()) {
                        if (currentRecipe == null || !recipeId.equals(rs.getInt("recipe_id"))) {
                            recipeId = rs.getInt("recipe_id");
                            currentRecipe = new Recipe();

                            currentRecipe = recipeRowMapper.mapRow(rs, recipeIdx++);
                            List<RecipeComponent> components = currentRecipe.getRecipeComponents();
                            if (components == null) {
                                components = new ArrayList<>();

                                RecipeComponent component = componentRowMapper.mapRow(rs, componentIdx++);

                                components.add(component);

                            }

                            currentRecipe.setRecipeComponents(components);
                            recipes.add(currentRecipe);

                        }

                    }
                    return recipes;
                }

            });
    return recipes;
}

and in this case i get response(for 70 Recipes) in something around 1000ms, but the problem is response is not complete( doesnt have full list of recipeComponents, but only first Component on the list):

[  
   {  
      "recipe_id":55,
      "recipeName":"Pizza",
      "weight":450,
      "aproxPrice":12,
      "raiting":7,
      "caloriesCat":1,
      "foodCat":2,
      "calories":300,
      "user_id":500,
      "publishDate":1505858400000,
      "recipeComponents":[  
         {  
            "component_id":139,
            "componentName":"veges",
            "componentWeight":100,
            "componentDescription":"Some desc",
            "componentCalories":200
         }
      ]
   }
]

**my query works fine.

select  recipe_id,recipe_name,recipe_weight,recipe_aprox_price,recipe_raiting,recipe_calories,recipe_user_id,recipe_kcategory_id,recipe_fcategory_id,recipe_published_date,comp_id, comp_name,comp_weight,comp_description,comp_calories,comp_recipe_id from public.recipe_store INNER JOIN public.recipe_components ON (public.recipe_store.recipe_id=public.recipe_components.comp_recipe_id)

I find also here some ideas to switch to ORM to solve this problem, but if you have some ideas, other solutions to solve this problms i will be grateful.

score 0 · Answer 1 · answered Sep 26 '17 at 17:43

Alright, i solved this problem, here is asnwer how it should be written:

@Override
    public List<Recipe> getAllRecipesWithSingleQuery() {
        final Map<Integer,Recipe> recipesAll = new HashMap<>();
        this.jdbcTemplate.query(SqlRecipeStaticData.sGetAllRecipesWithSingleQuery, new RowMapper<RecipeComponent>(){

            @Override
            public RecipeComponent mapRow(ResultSet rs, int rowNum) throws SQLException {
                Integer recipeId = rs.getInt("r_id");
                Recipe recipe = recipesAll.get(recipeId);
                if(recipe==null){
                    recipe = new Recipe();
                    recipe.setRecipe_id(recipeId);
                    recipe.setRecipeName(rs.getString("recipe_name"));
                    recipe.setAproxPrice(rs.getDouble("recipe_aprox_price"));
                    recipe.setWeight(rs.getDouble("recipe_weight"));
                    recipe.setRaiting(rs.getInt("recipe_raiting"));
                    recipe.setCalories(rs.getDouble("recipe_calories"));
                    recipe.setUser_id(rs.getInt("ruser_id"));
                    recipe.setCaloriesCat(rs.getInt("kcategory_id"));
                    recipe.setFoodCat(rs.getInt("fcategory_id"));
                    recipe.setPublishDate(rs.getDate("published_date"));
                    recipe.setRecipeComponents(new ArrayList<>());
                    recipesAll.put(recipeId, recipe);
                }
                RecipeComponent component = new RecipeComponent();
                component.setComponent_id(rs.getInt("id"));
                component.setComponentName(rs.getString("name"));
                component.setComponentWeight(rs.getDouble("weight"));
                component.setComponentDescription(rs.getString("description"));
                component.setRecipe_id(recipeId);
                component.setComponentCalories(rs.getDouble("calories"));
                recipe.getRecipeComponents().add(component);

                return component;

            }


        });
        List<Recipe> result = new ArrayList<Recipe>(recipesAll.values());
        return result;


    }

i hope someday this helps someone :)

object which contains List of some items cant expose rest data with single query

1 Answers1