Lightweight R/O Mapping
Pages: 1, 2, 3, 4, 5, 6, 7

Writing

Now, on to writing. The writing of an altered Jedi to the database is done with the following lines of code.

Connector connector = 
   ConnectorFactory.getInstance().createConnector( "starwars" );
BeanWriter writer = 
   new BeanWriter( Jedi.class, connector );
writer.executeStringUpdate( 
   sampleBean, "UpdateJedi" );

Here, the database access is made via a generated SQL query string. For the Jedi with the id of 1000 (which is Obi Wan), this last line of code produces the following execution string and sends it to the database (assuming we've changed the property alive to true and forceRating to 6.0).

UpdateJedi 
   @name='Obi Wan Kenobi', @jedi_id=1000, 
   @alive=1, @force_rating=6.0, @age=30

If we want to create a new Jedi, we would simply construct a new Jedi and write it with the following code.

Jedi newJedi = new Jedi();
newJedi.setName( "Mace Windu");
newJedi.setAge( 40 );
newJedi.setAlive( false );
newJedi.setForceRating( 9.7 );
Connector connector = 
    ConnectorFactory.getInstance().createConnector( "starwars" );
BeanWriter writer = 
    new BeanWriter( Jedi.class, connector );
writer.executeStringInsert( 
    newJedi, "InsertJedi" );

You'll notice that we are using a different method and a different stored procedure to write the data. The resulting string looks like this.

InsertJedi 
   @name='Mace Windu', @alive=0, 
   @force_rating=9.7, @age=40

What happened here? We'll assumed that the property jediId is provided by the database when a new entry is made. Actually, we've specified this when we used the @ParameterAssociation with the property isAutoIdentity=true in the Jedi class definition above. Since the database will provide the bean with a primary key, the parameter @jedi_id is omitted in the call string.

There is a catch here. Since jediId is provided by the database, this data must be returned from the stored procedure InsertJedi. At the moment, the method executeStringInsert returns a JDBC ResultSet, which can be used to return either the ID or the entire database row that was just inserted. This information has to be handled manually, but Amber will soon provide helper functionality to easily inject the new ID into the new object.

Dealing with strings being used for reading and writing is questionable when the focus lies more on type safety compared to transparency. Because of the conversion of the parameters to strings, type information is lost. However, there is one big advantage with this technique: any query string can be logged and given to database administrators for analysis in case anything goes wrong, or simply when there is a question on what exactly the application called or queried the database with. This sort of transparency makes debugging easier.

If the list of fighters of the Jedi has been altered, this has to be written manually into the database as well. Depending on what happened to the Fighter list, the crude way might be to delete all of the fighters of the Jedi first, and then write the new list back into the database. Assuming we have the object jedi at hand and a list of new Fighter objects in our collection of fighters, the following takes care of writing the new list into the database. Further, we assume that a new Fighter object is written into the database via the stored procedure InsertFighter.

Connector connector = 
   ConnectorFactory.createConnector( "starwars" );
BeanWriter writer = 
   new BeanWriter( Fighter.class, connector );
connector.execute( 
   "delete from fighters where jedi_id = " + jedi.getId() );
for( Fighter fighter : fighters ) {
   fighter.setJediId( jedi.getId() );
   connector.executeStringInsert( 
      fighter, "InsertFighter" )
}

This code produces a set of execution strings like this, where the name in each item is the value of the fighter item in the collection fighters:

InsertFighter @jediId=..., @name="...";

You may have noticed that this approach is not wrapped in a transaction. As mentioned above, there is no exception handling implemented, so if the delete command fails, an SQLException is raised and the subsequent for loop is not executed. But what about the other case, when the following InsertFighter call is going wrong? In a situation where it is necessary to provide a transaction, it is best to code this within the stored procedure that you are calling. If I wanted to handle this within a transaction, what I'd do is code an update stored procedure that takes all parameters from the Fighter object plus the Jedi ID and handles "new" fighters within. This topic might be worth discussing in another article.

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

Next Page