org.jaffa.tools.domainmeta.jdbc
Class JDBCSchemaReader

java.lang.Object
  org.jaffa.tools.domainmeta.jdbc.JDBCSchemaReader

public class JDBCSchemaReader

extends Object

This tool is used to reverse enginer a database schema and generate Jaffa based domain object XML meta data. This can then be used by the app builder to build a working CRUD application (See Jaffa's BaseLine project for more details)

This reads the table definitions and all the fields. It looks at the primary key definitions, and rejects any table that does not have one. It then looks at each table and infers (by field name, datatype, and field length) if this table has any foreign keys by looking at all the other tables Primary keys. If a match is found, then it assumes these object are related. If they have the same primary key, it assumes it is a one-to-one relationship. All relationships are created as associations.

There is at present no logic to look at table constraints to infer relationships as not everyone puts the contraints in the database, at present we just rely on primary keys.

This is based on generated domain objects for the v1.1 pattern

This is an example of how it can be used to reverse enginer the default 'Northwind' that codes wih Microsoft SQL Server 2000

public static void main(String[] args) {
    try {
        JDBCSchemaReader s = new JDBCSchemaReader();

        s.setSourceDriverString("com.microsoft.jdbc.sqlserver.SQLServerDriver");
        s.setSourceConnection("jdbc:microsoft:sqlserver://localhost:1433;SelectMethod=cursor;databasename=Northwind");
        s.setSourceUser("sa");
        s.setSourcePassword("sa");
        s.setSourceSchema("%");
        s.setAppName("Microsoft");
        s.setFullPackageNames(false);
        s.setModuleName("Northwind");
        s.setOutputDirectory("C:/sandbox.sf/northwind");
        s.setPackagePrefix("com");
        s.process();
        if(!s.save())
            System.out.println("****SAVE ERROR!!!!");
    } catch (Exception e) {
        e.printStackTrace(System.out);
    }
}
 

This is an example of one of the XML files this tool generated. This is the 'Customers' table from 'Northwind'.

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE Root PUBLIC "-//JAFFA//DTD Domain Creator Meta 1.1//EN" "http://jaffa.sourceforge.net/DTD/domainCreatorMeta_1_1.dtd">
<Root>
  <DomainObject>Customers</DomainObject>
  <DomainPackage>com.microsoft.northwind.domain</DomainPackage>
  <DatabaseTable>Customers</DatabaseTable>
  <MappingPackage>resources/jdbcengine</MappingPackage>
  <PatternTemplate>patterns/library/domain_creator_1_1/DomainCreatorPattern.xml</PatternTemplate>
  <Description>Reverse Engineered on 05/05/2004</Description>
  <LabelToken>[label.Microsoft.Northwind.Customers]</LabelToken>
  <Fields>
    <Field>
      <Name>CustomerID</Name>
      <DataType>java.lang.String</DataType>
      <DatabaseFieldName>CUSTOMERID</DatabaseFieldName>
      <DatabaseDataType>STRING</DatabaseDataType>
      <PrimaryKey>T</PrimaryKey>
      <LabelToken>[label.Microsoft.Northwind.Customers.CustomerID]</LabelToken>
      <Mandatory>T</Mandatory>
      <IntSize>5</IntSize>
      <CaseType>UpperCase</CaseType>
      <Ignore>false</Ignore>
    </Field>
    <Field>
      <Name>CompanyName</Name>
      <DataType>java.lang.String</DataType>
      <DatabaseFieldName>COMPANYNAME</DatabaseFieldName>
      <DatabaseDataType>STRING</DatabaseDataType>
      <PrimaryKey>F</PrimaryKey>
      <LabelToken>[label.Microsoft.Northwind.Customers.CompanyName]</LabelToken>
      <Mandatory>F</Mandatory>
      <IntSize>40</IntSize>
      <CaseType>MixedCase</CaseType>
      <Ignore>false</Ignore>
    </Field>
    <Field>
      <Name>ContactName</Name>
      <DataType>java.lang.String</DataType>
      <DatabaseFieldName>CONTACTNAME</DatabaseFieldName>
      <DatabaseDataType>STRING</DatabaseDataType>
      <PrimaryKey>F</PrimaryKey>
      <LabelToken>[label.Microsoft.Northwind.Customers.ContactName]</LabelToken>
      <Mandatory>F</Mandatory>
      <IntSize>30</IntSize>
      <CaseType>MixedCase</CaseType>
      <Ignore>false</Ignore>
    </Field>
    <Field>
      <Name>ContactTitle</Name>
      <DataType>java.lang.String</DataType>
      <DatabaseFieldName>CONTACTTITLE</DatabaseFieldName>
      <DatabaseDataType>STRING</DatabaseDataType>
      <PrimaryKey>F</PrimaryKey>
      <LabelToken>[label.Microsoft.Northwind.Customers.ContactTitle]</LabelToken>
      <Mandatory>F</Mandatory>
      <IntSize>30</IntSize>
      <CaseType>MixedCase</CaseType>
      <Ignore>false</Ignore>
    </Field>
    <Field>
      <Name>Address</Name>
      <DataType>java.lang.String</DataType>
      <DatabaseFieldName>ADDRESS</DatabaseFieldName>
      <DatabaseDataType>STRING</DatabaseDataType>
      <PrimaryKey>F</PrimaryKey>
      <LabelToken>[label.Microsoft.Northwind.Customers.Address]</LabelToken>
      <Mandatory>F</Mandatory>
      <IntSize>60</IntSize>
      <CaseType>MixedCase</CaseType>
      <Ignore>false</Ignore>
    </Field>
    <Field>
      <Name>City</Name>
      <DataType>java.lang.String</DataType>
      <DatabaseFieldName>CITY</DatabaseFieldName>
      <DatabaseDataType>STRING</DatabaseDataType>
      <PrimaryKey>F</PrimaryKey>
      <LabelToken>[label.Microsoft.Northwind.Customers.City]</LabelToken>
      <Mandatory>F</Mandatory>
      <IntSize>15</IntSize>
      <CaseType>MixedCase</CaseType>
      <Ignore>false</Ignore>
    </Field>
    <Field>
      <Name>Region</Name>
      <DataType>java.lang.String</DataType>
      <DatabaseFieldName>REGION</DatabaseFieldName>
      <DatabaseDataType>STRING</DatabaseDataType>
      <PrimaryKey>F</PrimaryKey>
      <LabelToken>[label.Microsoft.Northwind.Customers.Region]</LabelToken>
      <Mandatory>F</Mandatory>
      <IntSize>15</IntSize>
      <CaseType>MixedCase</CaseType>
      <Ignore>false</Ignore>
    </Field>
    <Field>
      <Name>PostalCode</Name>
      <DataType>java.lang.String</DataType>
      <DatabaseFieldName>POSTALCODE</DatabaseFieldName>
      <DatabaseDataType>STRING</DatabaseDataType>
      <PrimaryKey>F</PrimaryKey>
      <LabelToken>[label.Microsoft.Northwind.Customers.PostalCode]</LabelToken>
      <Mandatory>F</Mandatory>
      <IntSize>10</IntSize>
      <CaseType>MixedCase</CaseType>
      <Ignore>false</Ignore>
    </Field>
    <Field>
      <Name>Country</Name>
      <DataType>java.lang.String</DataType>
      <DatabaseFieldName>COUNTRY</DatabaseFieldName>
      <DatabaseDataType>STRING</DatabaseDataType>
      <PrimaryKey>F</PrimaryKey>
      <LabelToken>[label.Microsoft.Northwind.Customers.Country]</LabelToken>
      <Mandatory>F</Mandatory>
      <IntSize>15</IntSize>
      <CaseType>MixedCase</CaseType>
      <Ignore>false</Ignore>
    </Field>
    <Field>
      <Name>Phone</Name>
      <DataType>java.lang.String</DataType>
      <DatabaseFieldName>PHONE</DatabaseFieldName>
      <DatabaseDataType>STRING</DatabaseDataType>
      <PrimaryKey>F</PrimaryKey>
      <LabelToken>[label.Microsoft.Northwind.Customers.Phone]</LabelToken>
      <Mandatory>F</Mandatory>
      <IntSize>24</IntSize>
      <CaseType>MixedCase</CaseType>
      <Ignore>false</Ignore>
    </Field>
    <Field>
      <Name>Fax</Name>
      <DataType>java.lang.String</DataType>
      <DatabaseFieldName>FAX</DatabaseFieldName>
      <DatabaseDataType>STRING</DatabaseDataType>
      <PrimaryKey>F</PrimaryKey>
      <LabelToken>[label.Microsoft.Northwind.Customers.Fax]</LabelToken>
      <Mandatory>F</Mandatory>
      <IntSize>24</IntSize>
      <CaseType>MixedCase</CaseType>
      <Ignore>false</Ignore>
    </Field>
  </Fields>
  <Relationships>
    <Relationship>
      <ToDomainObject>CustomerCustomerDemo</ToDomainObject>
      <ToDomainPackage>com.microsoft.northwind.domain</ToDomainPackage>
      <FromCardinality>1</FromCardinality>
      <ToCardinality>0..*</ToCardinality>
      <Type>association</Type>
      <FromFields>
         <RelationshipField><Name>CustomerID</Name></RelationshipField>
      </FromFields>
      <ToFields>
         <RelationshipField><Name>CustomerID</Name></RelationshipField>
      </ToFields>
    </Relationship>
    <Relationship>
      <ToDomainObject>Orders</ToDomainObject>
      <ToDomainPackage>com.microsoft.northwind.domain</ToDomainPackage>
      <FromCardinality>1</FromCardinality>
      <ToCardinality>0..*</ToCardinality>
      <Type>association</Type>
      <FromFields>
         <RelationshipField><Name>CustomerID</Name></RelationshipField>
      </FromFields>
      <ToFields>
         <RelationshipField><Name>CustomerID</Name></RelationshipField>
      </ToFields>
    </Relationship>
  </Relationships>
</Root>
 

Version:

1.0

Author:

paule

Constructor Summary

JDBCSchemaReader()

Method Summary

String	getAppName() Getter for property appName.
String	getMappingPackage() Getter for property mappingPackage.
String	getModuleName() Getter for property moduleName.
String	getOutputDirectory() Getter for property m_outputDirectory.
String	getPackagePrefix() Getter for property packagePrefix.
String	getSourceConnection() Getter for property sourceConnection.
String	getSourceDriverString() Getter for property sourceDriverString.
String	getSourcePassword() Getter for property sourcePassword.
String	getSourceSchema() Getter for property sourceSchema.
String	getSourceUser() Getter for property sourceUser.
String	getTableExcludePattern() Getter for property tableExcludePattern.
String	getTableFilter() Getter for property tableFilter.
Collection	getTables() Get the list of processed tables
boolean	isFullPackageNames() Getter for property fullPackageNames.
boolean	isInferMandatory() Getter for property inferMandatory.
void	process() Run the reverse engineer process.
boolean	save() This will write out each of the TableBean objects to an XML based domain object file.
void	setAppName(String appName) Setter for property appName.
void	setFullPackageNames(boolean fullPackageNames) Setter for property fullPackageNames.
void	setInferMandatory(boolean inferMandatory) Setter for property inferMandatory.
void	setMappingPackage(String mappingPackage) Setter for property mappingPackage.
void	setModuleName(String moduleName) Setter for property moduleName.
void	setOutputDirectory(String outputDirectory) Setter for property outputDirectory.
void	setPackagePrefix(String packagePrefix) Setter for property packagePrefix.
void	setSourceConnection(String sourceConnection) Setter for property sourceConnection.
void	setSourceDriverString(String sourceDriverString) Setter for property sourceDriverString.
void	setSourcePassword(String sourcePassword) Setter for property sourcePassword.
void	setSourceSchema(String sourceSchema) Setter for property sourceSchema.
void	setSourceUser(String sourceUser) Setter for property sourceUser.
void	setTableExcludePattern(String tableExcludePattern) Setter for property tableExcludePattern.
void	setTableFilter(String tableFilter) Setter for property tableFilter.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Constructor Detail

JDBCSchemaReader

public JDBCSchemaReader()

Method Detail

process

public void process()
             throws Exception

Run the reverse engineer process. This reads the database schema and create an array of TableBean objects, each containing an array of FieldBean objects, and an array of related TableBean objects.

Throws:

Exception - General Exception thrown if there is a processing error. Typically the underlying exception is an SQLException.

save

public boolean save()

This will write out each of the TableBean objects to an XML based domain object file. The files will be written to the folder specified by setOutputDirectory(). This folder must exist. It also writes out the related ApplicationResources.pfragment file with all the labels as used in the domain object xml files

Returns:

Returns true if the save works, false if there were errors. In the event of any error only some files may be written.

getTables

public Collection getTables()

Get the list of processed tables

Returns:

A collection of tables, this is in a list sorted by table name

getSourceDriverString

public String getSourceDriverString()

Getter for property sourceDriverString.

Returns:

Value of property sourceDriverString.

setSourceDriverString

public void setSourceDriverString(String sourceDriverString)

Setter for property sourceDriverString. Default value is "oracle.jdbc.driver.OracleDriver"

Parameters:

sourceDriverString - New value of property sourceDriverString.

getSourceConnection

public String getSourceConnection()

Getter for property sourceConnection.

Returns:

Value of property sourceConnection.

setSourceConnection

public void setSourceConnection(String sourceConnection)

Setter for property sourceConnection. Default value is "jdbc:oracle:thin:@localhost:1521:my_sid"

Parameters:

sourceConnection - New value of property sourceConnection.

getSourceUser

public String getSourceUser()

Getter for property sourceUser.

Returns:

Value of property sourceUser.

setSourceUser

public void setSourceUser(String sourceUser)

Setter for property sourceUser. Default value is "scott"

Parameters:

sourceUser - New value of property sourceUser.

getSourcePassword

public String getSourcePassword()

Getter for property sourcePassword.

Returns:

Value of property sourcePassword.

setSourcePassword

public void setSourcePassword(String sourcePassword)

Setter for property sourcePassword. Default value is "tiger"

Parameters:

sourcePassword - New value of property sourcePassword.

getTableFilter

public String getTableFilter()

Getter for property tableFilter.

Returns:

Value of property tableFilter.

setTableFilter

public void setTableFilter(String tableFilter)

Setter for property tableFilter. Default is to get all tables

Parameters:

tableFilter - New value of property tableFilter.

getSourceSchema

public String getSourceSchema()

Getter for property sourceSchema.

Returns:

Value of property sourceSchema.

setSourceSchema

public void setSourceSchema(String sourceSchema)

Setter for property sourceSchema. Default is to get all schemas

Parameters:

sourceSchema - New value of property sourceSchema.

getOutputDirectory

public String getOutputDirectory()

Getter for property m_outputDirectory.

Returns:

Value of property m_outputDirectory.

setOutputDirectory

public void setOutputDirectory(String outputDirectory)

Setter for property outputDirectory. Default is "c:/temp"

Parameters:

outputDirectory - New value of property outputDirectory.

getPackagePrefix

public String getPackagePrefix()

Getter for property packagePrefix.

Returns:

Value of property packagePrefix.

setPackagePrefix

public void setPackagePrefix(String packagePrefix)

Setter for property packagePrefix. Default is "org.jaffa"

Parameters:

packagePrefix - New value of property packagePrefix.

getAppName

public String getAppName()

Getter for property appName.

Returns:

Value of property appName.

setAppName

public void setAppName(String appName)

Setter for property appName. Default is "MyApp"

Parameters:

appName - New value of property appName.

getModuleName

public String getModuleName()

Getter for property moduleName.

Returns:

Value of property moduleName.

setModuleName

public void setModuleName(String moduleName)

Setter for property moduleName. Default is "MyModule"

Parameters:

moduleName - New value of property moduleName.

isFullPackageNames

public boolean isFullPackageNames()

Getter for property fullPackageNames.

Returns:

Value of property fullPackageNames.

setFullPackageNames

public void setFullPackageNames(boolean fullPackageNames)

Setter for property fullPackageNames. Default is false

Parameters:

fullPackageNames - New value of property fullPackageNames.

getMappingPackage

public String getMappingPackage()

Getter for property mappingPackage.

Returns:

Value of property mappingPackage.

setMappingPackage

public void setMappingPackage(String mappingPackage)

Setter for property mappingPackage. Default is "resources/jdbcengine"

Parameters:

mappingPackage - New value of property mappingPackage.

getTableExcludePattern

public String getTableExcludePattern()

Getter for property tableExcludePattern.

Returns:

Value of property tableExcludePattern.

setTableExcludePattern

public void setTableExcludePattern(String tableExcludePattern)

Setter for property tableExcludePattern. Default is to exclude nothing. This expects a regular expression to use as an exclude table filter

Parameters:

tableExcludePattern - New value of property tableExcludePattern.

isInferMandatory

public boolean isInferMandatory()

Getter for property inferMandatory.

Returns:

Value of property inferMandatory.

setInferMandatory

public void setInferMandatory(boolean inferMandatory)

Setter for property inferMandatory.

Parameters:

inferMandatory - New value of property inferMandatory.