Tip:
Highlight text to annotate it
X
Create a new Maven project in your favorite IDE
Add Tornado Query and your database driver to the dependencies.
All you need is now available on your classpath.
Let's create two domain objects, Customer and Address.
The Address domain object has id, street, zip and city fields.
We let the IDE add getters and setters for us.
The Customer domain object has id, name, email, and two addresses.
The deliveryAddress and billingAddress will be joined in via the address table later.
Again we let the IDE create getters and setters.
Let's create the corresponding SQL tables.
You could create the tables from your database tool of choice, but we'll use Tornado Query.
First we connect to the PostgreSQL template1 database and create a database and a user.
Then we connect to the newly created database. The connection is set as a ThreadLocal.
Now we add two sequences for the autogenerated id's and create the tables.
The address table has the same fields as the Address domain object.
The customer table also references the address table on billing_address and delivery_address.
We must tell Tornado Query how to map domain objects to SQL result rows, but fortunately, we can autocreate the resultmaps.
Again we start by connecting to the database.
We just use a simple connection here, but you might as well have used a Connection Pool, like commons dbcp or c3p0.
Create a Path pointing to the folder we want to save the generated ResultMaps in.
Generate the ResultMap for Customer by telling the generator about the Customer class, the package, the table name and the destination.
Same thing for the Address ResultMap.
Now we are ready to run the main method to generate our artifacts.
The generator finished sucessfully, let's have a look at the generated classes.
Tornado Query detected the relationship between the address columns and automatically created joining information for us.
You can see that it figured out how to perform the join on the tables. You can modify the aliases, remove fields you don't need joined etc.
Here is the complete list of fields, also see that it figured out how to increment the "customer_id" sequence for the id column.
We will have a quick look at the Address ResultMap as well.
There are no joins, just plain fields.
Finally! It's time to perform some queries. Let's start by inserting some data into the tables.
We connect to the database...
Then we create an address domain object.
We don't need to write any SQL to insert the address, since the ResultMap knows how :)
Now same thing for the Customer. We will reference Sesame Street for both addresses.
When we insert the customer, the references to the correct address is saved with it.
We run the main method to see if it works. No error messages, everything is good!
Now we will query the database. We add another class with a main method, since this isn't a real project with any structure to it.
Connect to the database...
We select the first Customer in the customer table, by letting the CustomerMap create the query, and don't add any where clause.
We will output the address to see that the join actually worked.
We can see that the delivery address was correctly joined.
Let's find the id for this customer so we can make a more specific query.
Add a WHERE clause and insert the id as a named parameter.
If you are wondering what Tornado Query actually does, you can output any statement easily.
The customer is nicely joined with both delivery_address and billing_address in a readable, easily debuggable statement.
If you don't like it, you can always write it manually, that's actually what Tornado Query does best - let YOU write the SQL - your way!