XDBSchema - Generation of a Database Schema in JSON and XML
XDBSchema is a standalone application that generates a JSON or XML representation of a database
using a jdbc connection to the database. This format is required by some tools like
SQLRules.
Download
Download here the latest version.
Next, unpack the ZIP file. The following filles will be present in your directory:
- xdbschema-all.jar: Java archive for the application.
- xdbschema.bat, xdbschema.sh: Launcher command file.
After running it for the first time, the following files are generated:
- xdbschema.settings.xml: Remembers the last used configuration .
- xdbschema.error.xml: If an error occurs, some debug data is included in this file.
If you notify a software problem report you should include this file in order to
facilitate the detection of the problem.
First at all, you must be sure that you have your database running and the appropriate
jdbc database driver is in the classpath (or in the folder where the application has been unzipped).
Below are some direct links to download the drivers for some databases (maven):
Quick Start
Launch the main class of xdbschema.jar using the following java command
(where drivername.jar is the name of the vendor's database driver file:
java -classpath drivername.jar;xdbschema.jar in2test.application.xdbschema.XDBSchema
Alternatively, you may edit the xdbschema.bat or xdbschema.shscript file specifying this
command and then run the script.
The following window will be displayed:

The first step consists on filling the information that is needed to connect the database
using jdbc (Database connection info at the left of the window). See
Specifying connection info for more information.
Next, you can use the following options:
- Load All Tables in Schema: After clicking this button the middle of the
screen (List of tables for generating the schema) will be filled with the
names of all tables and views that are visible in the schema. This list of tables can be modified
at anytime.
- Generate Schema from Database: There are options to generate the schema from the
database connection in JSON or XML formats. After clicking one of these buttons the right of
the screen (Database Schema) will be filled with the corresponding representation
of the database schema, including all tables and views that have been specified in List of
Tables for generating the schema. See Database
schema content.
- File option in the menubar allows to load and save the above information.
Specifying connection info
The information required to access the database is explained below:
- Database connection info required for getConnection(): You must specify the driver
class name and connection url along with the access credentials (if required). The driver class
name and connection url is database vendor specific. Some examples of connections using the default ports
are:
Database Vendor | Class Driver Name | Connection url |
Oracle (OCI Driver) |
oracle.jdbc.driver.OracleDriver |
jdbc:oracle:oci:@<SERVER>:1521:ORCL |
SQL Server |
com.microsoft.sqlserver.jdbc.SQLServerDriver |
jdbc:sqlserver://<SERVER>:1433;database=<DATABASENAME> |
PostgreSQL |
org.postgresql.Driver |
jdbc:postgresql://<SERVER>:5432 |
The option Connections in the menubar facitlitates the configuration for
some typical connections.
- Database connection info required for getMetadata(): In order to obtain the database schema, the Java getMetadata methods are used, which require additional information about the database catalog and database schema where to find the information about the database tables and views. This information is not allways required, but in many ocasions is needed, depending on the database vendor and the access privileges of the user who has access to the database. Below
are some common scenarios:
- SQLServer: The catalog is the database name. and usually the schema is dbo.
So as, in a common scenario you will specify the database name as catalog name and dbo as schema name.
However, in many cases if you don't specify any information
for schema or catalog you attain the same effect, provided that the user has only
access to his database and dbo schema.
- Oracle: The database username is asssociated with the schema and
with a null catalog. So as, usually, you will not specify any catalog, but if you don't sypecify
any schema, and click Load All Tables in Schema you will find all tables
and views that this user has access to, possibly including system tables and tables
from other schemas (if user has DBA privileges). Therefore, is highly recommended
to specify the schema.
- PostgreSQL: The schema is usually public and
with a null catalog.
Database schema content
The schema content is definde as indicated in the
OpenApi specification
(see DbSchema, DbTable and DbColumn in the Schemas section):
- The JSON representation is obtained by a Jackson serializer (empty or null attributes are omitted):
An object representing the schema that contains an array of tables, each containing an array of columns.
Attributes of the tables and columns contain the information related to name, data type, etc.
- The XML representation of the database schema consists on a root tag <schema>
which contains a series of <table> tags. Each of them contains a <column>
tag for each of the columns in this table. Attributes of the tags contain the information
related to name, data type, etc
The schema may include three attributes, namely:
- dbms: Vendor's name of the database.
- catalog: Default catalog as specified in the connection info.
- schema: Default schema as specified in the connection info.
Both catalog and schema attributes are optional. However, they are relevent
in order to determine what tables are to be found. Recall that according to the
SQL Standard, a table specification may be qualified in the form [[catalog.]schema.]name.
If a table name is not qualified (using catalog and/or schema), a default catalog
and/or schema is assumed. In that case the deafaults are the indicated by dbcatalog
and/or dbschema:
- If neither catalog nor schema are specified:
- When loading all tables, all tables that are present in any schema are searched for.
If the user has visibility over more than one catalog/schema and there are tables
with the same name, an error occours because of a duplicated table name.
- Given a set of tables, when you generate the schema, a similar error may occour
if there are tables with same name that are visible. However, you may avoid this
error by qualifying the table names that are duplicated. For instance, if a table
T is present in two schemas S1 and S2, you will distinghish
the tables by their
qualified names S1.T and S2.T
- If catalog and/or schema are specified:
- When loading all tables, all tables that match the catalog/schema
specified are searched for. If some of dbcatalog or dbschema is not specified, it means any.
- Given a set of tables, when the schema is generated, if a table name is not qualified,
the table is searched for in the catalog/schema specified by default. If table name is qualified,
the table is searched for in the catalog/schema as indicated by table name qualifier.
For instance, if an schema S1 is specified by default, a table with name T will be searched in the schema S1, as same
as if S1.T is specified. However, S2.T is specified, table T
will be searched in the schema S2.
Supported database vendors and drivers
Latest builds of these applications has been tested with Java 1.8 on Windows and linux
As it uses standard jdbc methods to discover the database schema, it will work with
virtually any database vendor, provided that you have the appropriate jdbc driver.