Generate Schema From JDBC

This example shows you how to generate a DataPipeline SchemaDef from the tables in a relational database.  It uses the GenerateSchemaFromJdbc tool to build the SchemaDef from the metadata in a database.  The SchemaDef can then be used to generate SQL, validate data, or map data.

Java Code Listing

package com.northconcepts.datapipeline.foundations.examples.jdbc;

import com.northconcepts.datapipeline.foundations.jdbc.JdbcConnection;
import com.northconcepts.datapipeline.foundations.schema.SchemaDef;
import com.northconcepts.datapipeline.foundations.tools.GenerateSchemaFromJdbc;

import java.util.Arrays;

class GenerateSchemaDefFromJdbc {

    public static void main(String[] args) {
        JdbcConnection jdbcConnection = new JdbcConnection()
                .setDriverClassName("com.mysql.jdbc.Driver")
                .setUrl("jdbc:mysql://127.0.0.1:3306")
                .setUsername("username")
                .setPlainTextPassword("password");

        GenerateSchemaFromJdbc generator = new GenerateSchemaFromJdbc(jdbcConnection);

        SchemaDef schema = generator.generate("databaseName", null, Arrays.asList("tableName1", "tableName2"), "TABLE");
        System.out.println(schema);
    }

}

Code Walkthrough

1. JdbcConnection is used to connect to your database using the standard JDBC connection details: driver class, URL, username, password.
2. A GenerateSchemaFromJdbc instance is created to generate schemas from the new connection.
3. This example gemerates a new schema with two specif tables by calling generate(String catalog, String schemaPattern, List tableNames, String... types).  You can generate the schema with all tables in the database (or based on a wildcard pattern) using generate(String catalog, String schemaPattern, String tableNamePattern, String... types).
4. Since MYSQL supports catalog, the catalog's name is supplied as the first argument to the generate method.  If your database doesn't, you can pass the catalog as null and supply the schemaPattern instead.
5. The generated schema is then printed to the console.  You can convert the schema to JSON, XML, or a DataPipeline Record using the toJson(), toXml(), or toRecord().