Child pages
  • How To create and update Databases using SchemaManager and SchemaUpdateSnippets

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Code Block
languagehtml/xml
<bp:reference id="dialectFactory" interface="org.clazzes.jdbc2xml.schema.IDialectFactory">
</bp:reference>
 
<bp:reference id="schemaEngineFactory" interface="org.clazzes.jdbc2xml.schema.ISchemaEngineFactory">
</bp:reference>
 
<bp:bean id="sqlDialect" factory-ref="dialectFactory" factory-method="newDialect">
	<bp:argument> <!-- Pass JDBC URL as an argument -->
	</bp:argument>
</bp:bean>
 
<bp:bean id="schemaEngine" factory-ref="schemaEngineFactory" factory-method="newSchemaEngine">
	<bp:property name="dialect" ref="sqlDialect">
	</bp:property>
</bp:bean>
 
<bp:bean id="databaseSetup" class="org.clazzes.jdbc2xml.schema.SchemaManager" init-method="start">
	<bp:property name="dataSource" ref="dataSource"></bp:property>
	<bp:property name="schemaEngine" ref="schemaEngine"></bp:property>
	<bp:property name="baseVersion" value="0.1.00" />
	<bp:property name="baseTables">
		<!-- Add List of TableDefinitions here (see below) -->
	</bp:property>
	<bp:property name="upateSnippets">
		<!-- Add Update-Snippets here -->
	</bp:property>
</bp:bean>

...

Code Block
languagejava
package org.clazzes.example.jdbc2xml;

import java.sql.Types;
import java.util.Arrays;
import java.util.List;

import org.clazzes.jdbc2xml.schema.ColumnInfo;
import org.clazzes.jdbc2xml.schema.ForeignKeyInfo;
import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
import org.clazzes.jdbc2xml.schema.TableInfo;
 
public class TableDefinitions {
	
    /*/ It is adviseable to provide the Strings used as names for tables and columns as        constants, so they can be reused outside this object to build sql-statements
*/     public static final String TB_EXAMPLE_TABLE_NAME = "ADDRESSBOOK";
    public static final String COL_EXAMPLE_ID = "ID";
    public static final String COL_EXAMPLE_NAME = "NAME";
    public static final String COL_EXAMPLE_ADDRESS_REF = "ADDRESS";
    public static final String COL_EXAMPLE_BIRTHDAY = "BIRTHDAY";
 
    /*/ ...
*/
 
    private List<TableInfo> setup;
	
    public TableDefinitions() {
	/*/ Create a table
*/
	TableInfo exampleTable = new TableInfo(TB_EXAMPLE_TABLE_NAME);
	exampleTable.setColumns(
            Arrays.asList(new ColumnInfo[] {
		new ColumnInfo(COL_EXAMPLE_ID, Types.BIGINT, 20, null, false, null,true),
		new ColumnInfo(COL_EXAMPLE_NAME, Types.VARCHAR, 256, null, false, null),
		new ColumnInfo(COL_EXAMPLE_ADDRESS_REF, Types.BIGINT, 20, null, true, null),
		new ColumnInfo(COL_EXAMPLE_BIRTHDAY, Types.DATE, 12, null, false, null)
	}));
 
	/*/ Example for creating a foreign key reference
*/
	exampleTable.setForeignKeys(Arrays.asList(new ForeignKeyInfo[] {
		new ForeignKeyInfo("FK_EXAMPLE_ADDRESS", COL_EXAMPLE_ADDRESS_REF, TB_ADDRESS, COL_ADDRESS_ID)
	}));
 
	/*/ Example for creating a primary key
*/
	exampleTable.setPrimaryKey(
		new PrimaryKeyInfo("PK_EXAMPLE", COL_EXAMPLE_ID)
	);
	
	/*/ ...
*/
 
	this.setup = Arrays.asList(
		exampleTable,
		/*/ ... */
	);

    }
    
    public List<TableInfo> getSetup() {
	return this.setup;
    }
	
}

...

Using Blueprint/Spring, you can do this with by inserting the following snippet in the bean definition for SchemaManager:

No Formatcode
 
languagehtml/xml
<!-- SchemaManager bean definition starts here ... -->
<bp:property name="baseTables">
	<bp:bean factory-ref="tableDefinitions" factory-method="getSetup" />
</bp:property>
<!-- ... and continues here -->

Updating a database schema with

...

ISchemaUpdateSnippet

To update the database or it's content with schema updates, you must create a new implementation of ISchemaUpdateSnippet (org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet) for each consecutive update. SchemaManager takes a Map<String, Class<? extends ISchemaUpdateSnippet>> which contains the update classes keyed by the originating (e.g. previous) version.

 

 

An example for an implementation of a schema update snippet could look like this:

Code Block
languagejava
package org.clazzes.example.jdbc2xml.updates;

import java.sql.SQLException;
import java.sql.Types;
import java.util.Arrays;
import org.clazzes.jdbc2xml.schema.ColumnInfo;
import org.clazzes.jdbc2xml.schema.ISchemaEngine;
import org.clazzes.jdbc2xml.schema.ISchemaUpdateSnippet;
import org.clazzes.jdbc2xml.schema.PrimaryKeyInfo;
import org.clazzes.jdbc2xml.schema.TableInfo;

public class SchemaUpdate0_1_01 implements ISchemaUpdateSnippet {
 
	// This is only accessed through the getter
	private static final String TARGET_VERSION = "0.1.01";
 
	// Here it is also adviseable to define constants for reuse in statements.
	public static final String COL_EXAMPLE_GENDER = "GENDER";


	@Override
	public String getTargetVersion() {
		return TARGET_VERSION;
	}

	@Override
	public String getUpdateComment() {
		return "Adding column "+COL_EXAMPLE_GENDER+" to table "+TableDefinitions.TB_EXAMPLE_TABLE_NAME+".";
	}

	@Override
	public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
		TableInfo ti = schemaEngine.fetchTableInfo(TableDefinitions.TB_EXAMPLE_TABLE_NAME, null);
        	schemaEngine.addColumn(ti, new ColumnInfo(COL_EXAMPLE_GENDER, Types.VARCHAR, 1, null, true, null));
	}
}

The return values of ISchemaUpdateSnippet.getTargetVersion() and ISchemaUpdateSnippet.getUpdateComment() are written to the SCHEMA_HISTORY table. The update itself is performed in ISchemaUpdateSnippet.performUpdate(). In the above example, it adds a column called GENDER to the example table created by the TableDefinitions class above.

To add an entire table you would use the ISchemaEngine.createTable() method, like this:

Code Block
languagejava
@Override
public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
        TableInfo tiGroup = new TableInfo(TB_GROUP);
        tiGroup.setColumns(Arrays.asList(new ColumnInfo[] {
                new ColumnInfo(TableDefinitions.COL_ID, Types.VARCHAR, 36, null, false, null),
                new ColumnInfo(TableDefinitions.COL_NAME, Types.VARCHAR, 100, null, false, null),
                new ColumnInfo(TableDefinitions.COL_DESCRIPTION, Types.VARCHAR, 512, null, true, null)
        }));
        tiGroup.setPrimaryKey(new PrimaryKeyInfo(PK_GROUP, TableDefinitions.COL_ID));
        tiGroup.setIndices(Arrays.asList(new IndexInfo(IDX_GROUP_01, TableDefinitions.COL_NAME, true, null)));
	
	schemaEngine.createTable(tiGroup, true);
}

Executing a PreparedStatement also works, using ISchemaEngine.getConnection() to retrieve the database connection:

Code Block
languagejava
@Override
public void performUpdate(ISchemaEngine schemaEngine) throws SQLException {
        String sql = "UPDATE "+TableDefinitions.TB_EXAMPLE_TABLE_NAME+" SET "+TableDefinitions.COL_EXAMPLE_NAME+"=?";
        
        PreparedStatement ps = schemaEngine.getConnection().prepareStatement(sql);
        
        ps.setNull(1, Types.VARCHAR);
        
        ps.execute();
}

To create the map of updates in Blueprint/Spring and inject them into SchemaManager, use the following xml-Snippet:

Code Block
languagehtml/xml
<!-- SchemaManager bean definition starts here ... -->
<bp:property name="upateSnippets">
	<bp:map>
		<bp:entry key="0.1.00" value="org.clazzes.example.jdbc2xml.updates.SchemaUpdate0_1_01"></bp:entry>
		<!-- more entries come here: "key" is the schema version to update, "value" the qualified classname of the schema update -->
	</bp:map>
</bp:property>
<!-- ... and continues here -->