Manual INSERT

Let's start with manually inserting a Country in our example database. For this, we don't need a Mapper, and the code is straight forward:

Query.create("INSERT INTO country (name) VALUES ('Norway')").insert();

Let's supply the country name as parameter instead, to avoid SQL injection if the country came from an untrusted source:

Query.create("INSERT INTO country (name) VALUES (:name)")
    .param("name", "Norway").insert();

We might want to insert the data from a domain object:

Country country = new Country("Norway");
Query.create("INSERT INTO country (name) VALUES (:name)").param(country).insert();

The country object was set as the root parameter, so that we can look up values using the object's properties directly. We can also supply the country as a named parameter:

Query.create("INSERT INTO country (name) VALUES (:country.name)")
    .param("country", country).insert();

Since our database schema for the country table dictated a default value for the id field from the country_id SEQUENCE, we have automatically gotten an id, but we had no way of retrieving it. We can manually increment the sequence and put the result in the id property of our Country object:

Query.create("INSERT INTO country (name) VALUES (:name)")
    .param(country)
    .key("id", "SELECT nextval('country_id')")
    .insert();

After the insert is performed, country.id will contain the id from the sequence. There is a shortcut for the key method called sequence:

Query.create("INSERT INTO country (name) VALUES (:name)")
    .param(country)
    .sequence("id", "country_id")
    .insert();

MySQL LAST_INSERT_ID

After performing an INSERT against MySQL you can use the convenience call Query.lastInsertId().

INSERT with a Mapper

Take a good look at the countryMapper we created earlier. You can see that it already mentions the table name and both id and name fields, and it even knows about the sequence for the id field. We can take advantage of that to have it automatically write the INSERT statement for us:

Query.insert(countryMapper, country);

Wow, that was easy! And really, there is no magic here - writing an INSERT is mostly about enumerating columns and giving them the right values, so you wouldn't want to do that manually unless there is something very spesific and special about your usecase. Keep it DRY!

NOTE: If you use a mapper that has join columns, Tornado Query is smart enough to understand what columns are native to the table you are inserting into, and skips those that describe columns in other tables. Tornado Query only saves your main object, there is no magical traversal down to child objects that don't have ids etc. Tornado Query is here to help you, but it refuses to perform magic tricks :)