Search code examples
javaimageserializationhsqldb

HSQLDB database instead of serialization

I am trying to write a Cookbook in Java. I created an Object 'Recipe' with Attributes like title, picture, annotations, ... and put all recipes in an ArrayList. Until now, i just serialized the ArrayList so i could easily load it into my programm later. As you might think, if there will be hundreds of recipes and every recipe has a picture, the serialization needs pretty long time. So i thought about a HSQLDB databse but there are two questions for me.

  • Is it possible to save pictures into a HSQLDB databse?
  • Is it more efficient to load all recipes from the database at the beginning and put them into an ArrayList of Recipe or should i just load every single recipe when they are supposed to be displayed? If so, would i even need a Recipe-object anmyore? Wouldn't a single row in the database replace an instance of Recipe if they have the same attributes?
Solution

  • It is possible to store images in a column of VARBINARY or BLOB type. The VARBINARY type is fine for low resolution images up to several kilobytes. If the images are generally larger, BLOB is more suitable.

    You can retrieve each recipe on demand very efficiently.

    You may not need the Recipe object if all your are doing is displaying the recipe, which, in its simple form, is just a single row from the database table. You can just retrieve the row as a ResultSet and use getXXX(n) methods to get each attribute to display.

    The other advantage of using a database is the possibility of storing lists of ingredients or attributes like calories for each recipe and being able to search and display lists of recipes according to those attributes.

    Each recipe can have multiple ingredients. All possible ingredients can be stored in a second table with one row for each ingredient such as olive oil, olive, lettuce, tomato, etc. A third table is used with one row for each usage of an ingredient in each recipe. For example a particular salad has one row in this table for tomato and one row for olive oil.

    ingredients
  ingredient_id  name
  6              olive oil
  ...            ... 
  26             tomato 


ingredient_use
  recipe_id, ingredient_id
  12         26
  12         6
  ...        ...

    You use a join such as

    SELECT * FROM ingredients JOIN ingredient_use USING (ingredient_id) WHERE recipe_id = 12