ONJava.com -- The Independent Source for Enterprise Java
oreilly.comSafari Books Online.Conferences.

advertisement

AddThis Social Bookmark Button

Agile Object to Relational Database Replication with db4o
Pages: 1, 2, 3, 4, 5, 6, 7, 8

Merging Databases

Let's bring another db4o database into the picture. The second database, stored in a file called mobile2.yap, also has two Customer and two Address objects. The code to create the database is identical to the first example, apart from the database filename and the actual field values, so it is not shown here. The replication code is also identical, and the same configuration files are used. The contents of the MySQL database now look like this:

mysql>  select id, firstname, lastname, email, creditline from customers;
+----+-----------+----------------+-------------------+------------+
| id | firstname | lastname       | email             | creditline |
+----+-----------+----------------+-------------------+------------+
| 1  | Matt      | Hasselbeck     | matt@seahawks.com |      16000 |
| 2  | Ben       | Roethlisberger | ben@steelers.com  |      25000 |
| 3  | Jake      | Delhomme       | jake@panthers.com |      45000 |
| 4  | Jake      | Plummer        | jake@broncos.com  |      12000 |
+----+-----------+----------------+-------------------+------------+
4 rows in set (0.00 sec)

mysql>  select lastname, drs_version, drs_provider_id from customers;
+----------------+-------------+-----------------+
| lastname       | drs_version | drs_provider_id |
+----------------+-------------+-----------------+
| Hasselbeck     |           1 |               2 |
| Roethlisberger |           1 |               2 |
| Delhomme       |           1 |               3 |
| Plummer        |           1 |               3 |
+----------------+-------------+-----------------+
4 rows in set (0.00 sec)

mysql>  select id, street1, street2, city, zip, country from addresses;
+----+-----------------+----------+------------+----------+---------+
| id | street1         | street2  | city       | zip      | country |
+----+-----------------+----------+------------+----------+---------+
| 1  | 2 Second Street | Uptown   | Seattle    | WA 98200 | USA     |
| 2  | 1 First Street  | Downtown | Pittsburgh | PA 15121 | USA     |
| 3  | 4 Fourth Street | Downtown | Charlotte  | NC 28300 | USA     |
| 4  | 3 Third Street  | NULL     | Denver     | CO 80300 | USA     |
+----+-----------------+----------+------------+----------+---------+
4 rows in set (0.00 sec)

mysql>  select street1, drs_version, drs_provider_id from addresses;
+-----------------+-------------+-----------------+
| street1         | drs_version | drs_provider_id |
+-----------------+-------------+-----------------+
| 2 Second Street |         100 |               2 |
| 1 First Street  |         100 |               2 |
| 4 Fourth Street |         100 |               3 |
| 3 Third Street  |         100 |               3 |
+-----------------+-------------+-----------------+
4 rows in set (0.00 sec)

The new table rows have a different drs_provider_id value, indicating that they were provided by a different database. The street2 field of one of the Address objects was null in the db4o database, which is reflected in a NULL value in MySQL.

Updating Objects

Let's see what happens when an object is updated in a db4o database. The following Java code updates a Customer object in each database: the creditLine field for Ben Roethlisberger is changed in one, while Jake Plummer's email address is changed in the other. In db4o, you first query the database to get an in-memory reference to the object you want to update. Then, you make the required change to the object through that reference, and finally, you call ObjectContainer#set to store the changes in the object in the database.

Note that db4o provides some alternative ways to express your queries. Here we want to find a specific Customer by lastName, so we can use the simplest query-by-example (QBE) method. We create a template Customer object, with only the field(s) we want to match populated (the others are not assigned and so are either null or zero). Then we call ObjectContainer#get using the template object--in effect, we are saying "get all objects in the database that match this template." The null or zero fields are ignored when matching the stored objects with the template. The result of calling get is an iterable collection, actually a db4o-type ObjectSet. In the example code we just retrieve the first result from the ObjectSet.

ExtDb4o.configure().generateUUIDs(Integer.MAX_VALUE);
ExtDb4o.configure().generateVersionNumbers(Integer.MAX_VALUE);
ObjectContainer objectContainer = Db4o.openFile("c:/mobileone.yap");
        
Customer template1 = new Customer();
template1.setLastName("Roethlisberger");
Customer c1 = (Customer) objectContainer.get(template1).next();
c1.setCreditLine(72000);
        
objectContainer.set(c1);
objectContainer.commit();
objectContainer.close();

ObjectContainer objectContainer = Db4o.openFile("c:/mobiletwo.yap");
Customer template2 = new Customer();
template2.setLastName("Plummer");
Customer c2 = (Customer) objectContainer.get(template2).next();
System.out.println("Updating customer: " + c2.getLastName());
c2.setEmail("Jake@myIsp.com");
        
objectContainer.set(c2);
objectContainer.commit();
objectContainer.close();

QBE is a very limited query method. For example, it can only match specific values; it can't do "greater than" or "less than" queries. It was OK for the above example, but for more complex queries the recommended method is native queries, supported in db4o since version 5.0. Native queries are expressed in Java itself (or in C# or VB, in the .NET version), rather than in an external language like SQL or Hibernate Query Language (HQL), and so they can be checked at compile time and can benefit from IDE code hints and refactoring support. An example of a simple native query is given in the next part of this article.

The replication is repeated in exactly the same way as before after updating the db4o databases, and the CUSTOMERS table now looks like this:

mysql>  select id, firstname,lastname, email, creditline from customers;
+----+-----------+----------------+---------------------+------------+
| id | firstname | lastname       | email               | creditline |
+----+-----------+----------------+---------------------+------------+
| 1  | Matt      | Hasselbeck     | matt@seahawks.com   |      16000 |
| 2  | Ben       | Roethlisberger | ben@steelers.com    |      72000 |
| 3  | Jake      | Delhomme       | jake@panthers.com   |      45000 |
| 4  | Jake      | Plummer        | Jake@myIsp.com      |      12000 |
+----+-----------+----------------+---------------------+------------+
4 rows in set (0.00 sec)

mysql>  select lastname, drs_version, drs_provider_id from customers;
+----------------+-------------+-----------------+
| lastname       | drs_version | drs_provider_id |
+----------------+-------------+-----------------+
| Hasselbeck     |           1 |               2 |
| Roethlisberger |         100 |               2 |
| Delhomme       |           1 |               3 |
| Plummer        |         100 |               3 |
+----------------+-------------+-----------------+
4 rows in set (0.00 sec)

The updated values have been replicated into MySQL, and the drs_version field has been updated to show that these two objects only have been newly replicated.

Pages: 1, 2, 3, 4, 5, 6, 7, 8

Next Pagearrow