Store nested Pojo Objects as individuall Objects in Database

Question

Im using jackson to map json strings to my HTModel class which is basically a simple Pojo.

class HTModel{}

public class Post extends HTModel {
    public String id;
    public String content;
    public String author;
}

That works quite well even the classes are nested together.

public class Venue extends HTModel {
    public ArrayList<Post> posts;
}

I setup a simple SqlLite schema to cache and index these Models by their type and their id.

My problem is that i don´t want to store, for example the Venue Model, as whole in the database if the Model contains fields with other Models. Each post in the ArrayList Venue.posts should be saved individually.

Whats the best way to do this?

Answer 1

I've faced a similar problem when creating my own Database -> POJO implementation using JSON. This is how I solved the issue, and it works quite well for me.

Let's take your Post object as an example. It needs to easily be represented as a JSON object and be created from a JSON string. Additionally, it needs to be able to save to a database. I've broken down the heirachy of classes that I use based on these two conditions:

Post
  -> DatabaseObject
    -> JsonObject
      -> LinkedHashMap

Starting with the most basic representation, a JsonObject, which is an extended LinkedHashMap. Maps work well for representing JSON objects due to their key-value mapping. Here's the JsonObject class:

import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;

/**
 * A <code>JsonObject</code> represents a JSON object, which begins and ends 
 * with curly braces '{' '}' and contains key-value pairs separated by a 
 * colon ':'.
 * <p>
 * In implementation, this is simply an extended <code>LinkedHashMap</code> to 
 * represent key-value pairs and to preserve insert order (which may be 
 * required by some JSON implementations, though is not a standard).
 * <p>
 * Additionally, calling <code>toString()</code> on the <code>JsonObject</code> 
 * will return a properly formatted <code>String</code> which can be posted as 
 * a value JSON HTTP request or response.
 * @author Andrew
 * @param <V> the value class to use. Note that all keys for a 
 *          <code>JsonObject</code> are <code>Strings</code>
 */
public class JsonObject<V> extends LinkedHashMap<String, V> {

    /**
     * Creates a new empty <code>JsonObject</code>.
     */
    public JsonObject() {

    }
    /**
     * Creates a new <code>JsonObject</code> from the given HTTP response 
     * <code>String</code>.
     * @param source HTTP response JSON object
     * @throws IllegalArgumentException if the given <code>String</code> is not 
     *          a JSON object, or if it is improperly formatted
     * @see JsonParser#getJsonObject(java.lang.String) 
     */
    public JsonObject(String source) throws IllegalArgumentException {
        this(JsonParser.getJsonObject(source));
    }
    /**
     * Creates a new <code>JsonObject</code> from the given <code>Map</code>.
     * @param map a <code>Map</code> of key-value pairs to create the 
     *          <code>JsonObject</code> from
     */
    public JsonObject(Map<? extends String, ? extends V> map) {
        putAll(map);
    }

    /**
     * Returns a JSON formatted <code>String</code> that properly represents 
     * this JSON object.
     * <p>
     * This <code>String</code> may be used in an HTTP request or response.
     * @return JSON formatted JSON object <code>String</code>
     */
    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder("{");

        Iterator<Map.Entry<String, V>> iter = entrySet().iterator();
        while (iter.hasNext()) {
            Map.Entry<String, V> entry = iter.next();
            sb.append(JsonParser.toJson(entry.getKey()));
            sb.append(':');

            V value = entry.getValue();
            sb.append(JsonParser.toJson(value));
            if (iter.hasNext()) {
                sb.append(',');
            }

        }
        sb.append("}");        
        return sb.toString();
    }
}

Simply enough, it's just a LinkedHashMap which represents the JSON object, and it can be quickly turned into a JSON string by calling toString(), as well as created from a JSON string using the JsonParser class that I created.

Likely if you're already using a JSON parser like Jackson, you could rework a few things to use that API.

Next is the meat of the Post, the DatabaseObject which gives the Post functionality to communicate with a database. In my implementation, the Database object is simply an abstract class. I specify how the Database saves DatabaseObjects elsewhere, be it through JDBC or JSON over HTTP.

Keep in mind that we're using a Map to represent our object. For your Post, that means you have three "properties" (as I call the key values in my documentation): the ID, content, and author.

Here's what the DatabaseObject (trimmed down) looks like. Note the save() method, that's where I'll answer your question.

import java.text.ParseException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * The <code>DatabaseObject</code> represents a single row of data from a 
 * specific table within a database.
 * <p>
 * The object should implement getters and setters for each column, and is 
 * responsible for registering the correct table name and column names, as 
 * well as default values for those columns, in case a default value is 
 * not supported by the database table.
 * <p>
 * The <code>DatabaseObject</code> works with key-value pairs as an 
 * extended <code>LinkedHashMap</code>. It defines one property, 
 * <code>DatabaseObject.ROW_ID</code> which represents the unique 
 * identifier column for a table row. This column should always be an 
 * integer value. (Future implementations may allow for long values, but 
 * <code>Integer.MAX_VALUE</code> is well suited for most databases as a maximum 
 * row count per table).
 * <p>
 * The key and value pairs should be accessed by implementing getter and 
 * setter methods, not by the get and put methods provided by the 
 * <code>LinkedHashMap</code>. This is to ensure proper <code>Class</code> 
 * type casting for each value.
 * <p>
 * A <code>DatabaseObject</code> itself is also an extension of a 
 * <code>JsonObject</code>, and <code>toString()</code> may be called on 
 * it to provide a JSON notated <code>DatabaseObject</code>.
 * <p>
 * When using JSON however, keep in mind that the keys may not correspond 
 * exactly with the table column names, even though that is the recommendation. 
 * The <code>DatabaseObject</code> should be converted back into its 
 * implementing object form and saved when using web services.
 * <p>
 * The parameter <code>T</code> should be set to the class of the implementing 
 * <code>DatabaseObject</code>. This will allow proper class casting when 
 * returning instances of the implementation, such as in the <code>load()</code> 
 * methods.
 * @param <T> the type of <code>DatabaseObject</code>
 * @author Andrew
 */
public abstract class DatabaseObject<T extends DatabaseObject> 
        extends JsonObject<Object> implements Cloneable{

    /**The property for the row ID*/
    public final static String ROW_ID = "rowId";

    /**
     * Creates a new empty <code>DatabaseObject</code>.
     */
    public DatabaseObject() {

    }


    /**
     * {@inheritDoc }
     * <p>
     * This get method will additionally check the <code>Class</code> of 
     * the returned value and cast it if it is a <code>String</code> but
     * matches another <code>Class</code> type such as a number.
     * @see #doGet(java.lang.String, boolean) 
     */
    @Override
    public Object get(Object key) {
        //From here you can specify additional requirements before retrieving a value, such as class checking
        //This is optional of course, and doGet() calls super.get()
        return doGet(String.valueOf(key), true);
    }

    /**
     * {@inheritDoc }
     * <p>
     * This get method will additionally check the <code>Class</code> of 
     * the given value and cast it if it is a <code>String</code> but
     * matches another <code>Class</code> type such as a number.
     * @see #doPut(java.lang.String, java.lang.Object, boolean) 
     */
    @Override
    public Object put(String key, Object value) {
        //Like doGet(), doPut() goes through additional checks before returning a value
        return doPut(key, value, true);
    }

    //Here are some example getter/setter methods
    //DatabaseObject provides an implementation for the row ID column by default

    /**
     * Retrieves the row ID of this <code>DatabaseObject</code>.
     * <p>
     * If the row ID could not be found, -1 will be returned. Note that 
     * a -1 <i>may</i> indicate a new <code>DatabaseObject</code>, but it 
     * does not always, since not all <code>Databases</code> support 
     * retrieving the last inserted ID.
     * <p>
     * While the column name might not correspond to "rowId", this 
     * method uses the <code>DatabaseObject.ROW_ID</code> property. All 
     * objects must have a unique identifier. The name of the column 
     * should be registered in the constructor of the object.
     * @return the <code>DatabaseObject's</code> row ID, or -1 if it is not set
     */
    public int getRowId() {
        //getProperty(), while not included, simply returns a default specified value 
        //if a value has not been set
        return getProperty(ROW_ID, -1);
    }
    /**
     * Sets the row ID of this <code>DatabaseObject</code>.
     * <p>
     * <b>Note: this method should rarely be used in implementation!</b>
     * <p>
     * The <code>DatabaseObject</code> will automatically set its row ID when 
     * retrieving information from a <code>Database</code>. If the row ID 
     * is forcibly overriden, this could overwrite another existing row entry 
     * in the database table.
     * @param rowId the row ID of the <code>DatabaseObject</code>
     */
    public void setRowId(int rowId) {
        //And again, setProperty() specifies some additional conditions before 
        //setting a key-value pair, but its not needed for this example
        setProperty(ROW_ID, rowId);
    }


    //This is where your question will be answered!!

    //Since everything in a DatabaseObject is set as a key-value pair in a 
    //Map, we don't have to use reflection to look up values for a 
    //specific object. We can just iterate over the key-value entries

    public synchronized void save(Database database) throws SaveException {
        StringBuilder sql = new StringBuilder();
        //You would need to check how you want to save this, let's assume it's 
        //an UPDATE
        sql.append("UPDATE ").append(getTableName()).append(" SET ");

        Iterator<String, Object> iter = entrySet().iterator();
        while (iter.hasNext()) {
            Map.Entry<String, Object> entry = iter.next();
            String property = entry.getKey();
            Object value = entry.getValue();

            if (value instanceof DatabaseObject) {
                ((DatabaseObject)value).save();
            }
            else if (value instanceof Collection) {
                for (Object v : (Collection)value) {
                    ((DatabaseObject)value).save();
                }
            }
            //However many other checks you want, such as Maps, Arrays, etc
            else {              
                sql.append(property); //Assuming the property equals the column name
                sql.append("='").append(value).append("'");             
            }
            if (iter.hasNext()) {
                sql.append(", ");
            }
        }


        //getIdColumn() would retrieve which column is being used as the identifier
        sql.append("WHERE ").append(getIdColumn()).append("=").append(getRowId());


        //Finally, take our SQL string and save the value to the Database

        //For me, I could simply call database.update(sql), and
        //the abstracted Database object would determine how to 
        //send that information via HTTP as a JSON object

        //Of course, your save() method would vary greatly, but this is just a quick
        //and dirty example of how you can iterate over a Map's 
        //key-value pairs and take into account values that are 
        //DatabaseObjects themselves that need to be saved, or 
        //a Collection of DatabaseObjects that need to be saved
    }

    /**
     * Retrieves the name of the database table that this 
     * <code>DatabaseObject</code> pulls its information from.
     * <p>
     * It is recommended to declare a final and static class variable that 
     * signifies the table name to reduce resource usage.
     * @return name of the database table
     */
    public abstract String getTableName();
}

For the TL;DR version:

Post is a DatabaseObject.

DatabaseObject is a JsonObject, which is a LinkedHashMap.

The LinkedHashMap set the standard of storing key-value pairs. Key = column name, Value = column value.

JsonObject does nothing but give a way to print the LinkedHashMap as a JSON string.

DatabaseObject specifies the logic on how to save to your database, including in the case where a value is another DatabaseObject, or if a value contains a DatabaseObject, such as with Collections.

^ -- This means you write the "save" logic once. When you call Post.save() it saves the post. When you call Venue.save(), it saves the venue columns (if any), as well as each individual Post in the ArrayList.

For extra fun, here is what your Post and Venue objects would look like:

public class Post extends DatabaseObject<Post> {

    //The column names
    public final static String POST_ID = "PostID";
    public final static String CONTENT = "Content";
    public final static String AUTHOR = "Author";

    public Post() {
        //Define default values
    }

    public int getPostId() {
        return (Integer)get(POST_ID);
    }
    public void setPostId(int id) {
        put(POST_ID, id);
    }
    public String getContent() {
        return (String)get(CONTENT);
    }
    public void setContent(String content) {
        put(CONTENT, content);
    }
    public String getAuthor() {
        return (String)getAuthor();
    }
    public void setAuthor(String author) {
        put(AUTHOR, author);
    }

    @Override
    public String getTableName() {
        return "myschema.posts";
    }

}

Note that we are no longer declaring class variables, just the column names in which the values are stored. We define the class of the variables in the getter/setter methods.

import java.util.ArrayList;
import java.util.List;

public class Venue extends DatabaseObject<Venue> {

    //This is just a key property, not a column
    public final static String POSTS = "Posts";

    public Venue() {
        setPosts(new ArrayList());
    }

    public List<Post> getPosts() {
        return (List<Post>)get(POSTS);
    }
    public void setPosts(List<Post> posts) {
        put(POSTS, posts);
    }
    public void addPost(Post post) {
        getPosts().add(post);
    }

    @Override
    public String getTableName() {
        //Venue doesn't have a table name, it's just a container
        //In your DatabaseObject.save() method, you can specify logic to 
        //account for this condition
        return "";
    }

}

Extra ultimate TL;DR version:

Use a Map to store your variables instead of defining them in your class.

Create a save() method logic that iterates over the Map values and saves the column-value pairs to the database, taking into account values which are Collections or saveable DatabaseObjects.

Now all you have to do is call Venue.save() and all your Post objects will be appropriately saved as well.