<?xml version="1.0" encoding="ISO-8859-15"?>
<!--
  Licensed to the Apache Software Foundation (ASF) under one
  or more contributor license agreements.  See the NOTICE file
  distributed with this work for additional information
  regarding copyright ownership.  The ASF licenses this file
  to you under the Apache License, Version 2.0 (the
  "License"); you may not use this file except in compliance
  with the License.  You may obtain a copy of the License at

    http://www.apache.org/licenses/LICENSE-2.0

  Unless required by applicable law or agreed to in writing,
  software distributed under the License is distributed on an
  "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
  KIND, either express or implied.  See the License for the
  specific language governing permissions and limitations
  under the License.
-->
<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V2.0//EN" "http://forrest.apache.org/dtd/document-v20.dtd">
<document> 
  <header> 
    <title>Using DdlUtils' API</title> 
  </header> 
  <body> 
    <section>
      <title>The model</title>
      <p>
        At the core of DdlUtils lies the database model in package
        <code>org.apache.ddlutils.model</code>. It consists of classes that
        represent the database schema:
      </p>
      <p>
        <img src="images/model.png" alt="UML diagram of the database model"/>
      </p>
      <p>
        Using the appropriate methods on these classes, you can build the
        database model manually, or you read it from XML or from the database
        (see the next paragraphs for details). More info about the classes
        can be found in the <a href="ext:ddlutils/javadoc/model">javadoc</a>.
      </p>
    </section>
    <section>
      <title>Reading from XML</title>
      <p>
        Most of the time you have a schema in an XML file, and you want to
        read it into memory in order to do something with it. This is quite easily
        accomplished with a few lines of code:
      </p>
      <source><![CDATA[
import org.apache.ddlutils.io.DatabaseIO;
import org.apache.ddlutils.model.Database;

...

public Database readDatabaseFromXML(String fileName)
{
    return new DatabaseIO().read(fileName);
}]]></source>
    </section>
    <section>
      <title>Writing to XML</title>
      <p>
        Writing a model to XML is just as easy as reading from XML:
      </p>
      <source><![CDATA[
import org.apache.ddlutils.io.DatabaseIO;
import org.apache.ddlutils.model.Database;

...

public void writeDatabaseToXML(Database db, String fileName)
{
    new DatabaseIO().write(db, fileName);
}]]></source>
    </section>
    <section>
      <title>Reading the model from a live database</title>
      <p>
        Reading the database model from a live database is only slightly more involved
        because we first need to create a platform instance via the data source pointing
        to the database:
      </p>
      <source><![CDATA[
import javax.sql.DataSource;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.model.Database;

...

public Database readDatabase(DataSource dataSource)
{
    Platform platform = PlatformFactory.createNewPlatformInstance(dataSource);

    return platform.readModelFromDatabase("model");
}]]></source>
    </section>
    <section>
      <title>Changing a database</title>
      <p>
        Changing a database essentially means one of two things: resetting a database to the
        schema defined by the model, or altering the database to have the same model. The key
        difference is that with the latter, data in the database is retained as much as
        possible. Only major changes to the table structure or type-incompatible alterations of
        columns will result in loss of data, most changes will simply retain the data.
      </p>
      <note>
        Whether a change of e.g. a column type affects the data contained in the table, depends
        on the database that you use. Most databases are able to convert between different
        datatypes and will apply these conversions when the column type is changed.
      </note>
      <p>
        Both types of modification differ only in how the SQL is created, the general procedure
        is the same: create the sql and execute it:
      </p>
      <source><![CDATA[
import javax.sql.DataSource;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.model.Database;

...

public void changeDatabase(DataSource dataSource,
                           Database   targetModel,
                           boolean    alterDb)
{
    Platform platform = PlatformFactory.createNewPlatformInstance(dataSource);
    
    if (alterDb)
    {
        platform.alterTables(targetModel, false);
    }
    else
    {
        platform.createTables(targetModel, true, false);
    }
}]]></source>
      <note>
        Databases like Oracle allow for more than one separate schema in one database. To cater
        for these databases, there are variants of these methods where you can specify the 
        catalog and schema.
      </note>
    </section>
    <section>
      <title>Inserting data into a database</title>
      <p>
        DdlUtils provides a simple way to insert data into the database. For this it uses
        <a href="ext:beanutils/dynabean">DynaBeans</a> which are essentially dynamically
        created beans with variable properties. For each table defined by the database
        model, a so-called dyna class is created that represents the table with its
        columns. Of this dyna class, instances - the dyna beans - are then created
        which can be inserted by DdlUtils into the database:
      </p>
      <source><![CDATA[
import javax.sql.DataSource;
import org.apache.commons.beanutils.DynaBean;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.model.Database;

...

public void insertData(DataSource dataSource,
                       Database   database)
{
    Platform platform = PlatformFactory.createNewPlatformInstance(dataSource);
    
    // "author" is a table of the model
    DynaBean author = database.createDynaBeanFor("author", false);
    
    // "name" and "whatever" are columns of table "author"
    author.set("name",     "James");
    author.set("whatever", new Integer(1234));
    
    platform.insert(database, author);
}]]></source>
    </section>
    <section>
      <title>Getting data from a database</title>
      <p>
        In the same way as inserting data into a database, DdlUtils uses dyna beans
        for retrieving data from the database. You issue a SQL select against the
        database and get dyna beans back. This means that the table that the select
        goes against, has to be part of the database model used by DdlUtils.
      </p>
      <p>
        In the following sample, the titles of all books in the database are printed
        to stdout:
      </p>
      <source><![CDATA[
import java.util.ArrayList;
import java.util.Iterator;
import javax.sql.DataSource;
import org.apache.commons.beanutils.DynaBean;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.model.Database;
import org.apache.ddlutils.model.Table;

...

public void dumpBooks(DataSource dataSource,
                      Database   database)
{
    Platform  platform = PlatformFactory.createNewPlatformInstance(dataSource);
    ArrayList params   = new ArrayList();
    
    params.add("Some title");
    
    Iterator it = platform.query(database,
                                 "select * from book where title = ?",
                                 params,
                                 new Table[] { database.findTable("book") });
    
    while (it.hasNext())
    {
        DynaBean book = (DynaBean)it.next();
        
        System.out.println(book.get("title"));
    }
}]]></source>
      <p>
        There are two things to note in this sample code:
      </p>
      <p>
        First, we specified so-called query hints in the call to the <code>query</code>. Query hints
        are an array of tables whose columns are used by the query statement. The reason why they
        should be used is that not all databases provide sufficient information in the JDBC result set
        object to determine the table to which a column belongs to. Since this info is need by
        DdlUtils to properly extract the value and convert it to the corresponding Java type, it is
        safer to specify these hints. What DdlUtils does in this case, is to search for a column
        of that name within the specified tables and use the mapping for this column. This of course
        can fail if you use aliases in the query statement (and the JDBC driver handles them in
        a strange way), or if more than one table has a column of this name. But in most cases you'll
        get the expected results.
      </p>
      <p>
        The other thing to note is that DdlUtils does not parse the query statement. This means that
        if you use delimited identifier mode (i.e. identifiers can contain whitespaces, non-alphanumeric
        characters etc., but they also need to be enclosed in double quotes), then you'll have to
        specify the query statement accordingly - DdlUtils won't do that for you. If you'd like to be
        on the safe side, then you could write the above statement like this:
      </p>
      <source><![CDATA[
import java.util.ArrayList;
import java.util.Iterator;
import javax.sql.DataSource;
import org.apache.commons.beanutils.DynaBean;
import org.apache.ddlutils.Platform;
import org.apache.ddlutils.PlatformFactory;
import org.apache.ddlutils.model.Database;
import org.apache.ddlutils.model.Table;

...

public void dumpBooks(DataSource dataSource,
                      Database   database)
{
    Platform  platform = PlatformFactory.createNewPlatformInstance(dataSource);
    ArrayList params   = new ArrayList();
    String    sql;
    
    params.add("Some title");

    if (platform.isDelimitedIdentifierModeOn())
    {
        sql = "select * from \"book\" where \"title\" = ?";
    }
    else
    {
        sql = "select * from book where title = ?";
    }

    Iterator it = platform.query(database,
                                 sql,
                                 params,
                                 new Table[] { database.findTable("book") });
    
    while (it.hasNext())
    {
        DynaBean book = (DynaBean)it.next();
        
        System.out.println(book.get("title"));
    }
}]]></source>
    </section>
  </body>
</document>
