Pacific-Design.com

    
Home Index

1. Java

2. ETL Tables

+ Install

Java / ETL Tables /

Extract Transform Load

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;
import org.yaml.snakeyaml.Yaml;

public class CreateTable {
    
    private Connection connection;
    private Statement statement;
    private ResultSet result;

    /*--------------------------------------------------------------------------------------------*/
    public void connect2MySQL() {

        String url      = "jdbc:mysql://127.0.0.1:3306/";
        String database = "STOREDB";
        String driver   = "com.mysql.jdbc.Driver";
        String user     = "root";
        String password = "password";

        try {
            Class.forName(driver).newInstance();
            this.connection = (Connection) DriverManager.getConnection(url + database
                                                                     , user, password);
        } catch (Exception ex) {
            System.err.println("MySQL Connection: " + ex.getMessage());
        }
    }    
    
    /*--------------------------------------------------------------------------------------------*/      
    public int execSQL(String insertQuery) {

        int total = 0;
        try {
            statement = connection.createStatement();
            total = statement.executeUpdate(insertQuery);

        } catch (SQLException ex) {
            System.err.println("Execute SQL: " + ex.getMessage());            
        }
        return total;

    }    
    /*--------------------------------------------------------------------------------------------*/

    public String yaml2sql(String tableName, String filename) throws FileNotFoundException {

        Yaml yaml = new Yaml();
        Map<String, Map<String, String>> values
                = (Map<String, Map<String, String>>) yaml.load(new FileInputStream(new File(filename)));

        StringBuilder SQL = new StringBuilder("CREATE TABLE IF NOT EXISTS " + tableName + " (\n");
        for (String key : values.keySet()) {

            Map<String, String> subval = values.get(key);

            SQL.append("\t");
            SQL.append(subval.get("name"));
            SQL.append("\t\t");
            SQL.append(subval.get("attr"));
            SQL.append(",\n");
        }
        
        for (String key : values.keySet()) {

            Map<String, String> subval = values.get(key);
            String tmp = subval.get("index");

            if (tmp != null) {
                SQL.append("\t");
                SQL.append(tmp);
                SQL.append(",\n");
            }
        }
        // Remove last commna from SQL statement
        SQL.replace(SQL.lastIndexOf(","), SQL.lastIndexOf(",") + 1, "\n);");

        return SQL.toString();
    }
    /*--------------------------------------------------------------------------------------------*/
    public void close() {

        try {
            if (result != null) {
                result.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (connection != null) {
                connection.close();
            }

        } catch (SQLException ex) {
            System.err.println("close(): " + ex.getMessage());      
        }
    }    
    /*--------------------------------------------------------------------------------------------*/

    public void readYAML(String filename) throws FileNotFoundException {

        Yaml yaml = new Yaml();
        //System.out.println(yaml.dump(yaml.load(new FileInputStream(new File(filename)))));

        Map<String, Map<String, String>> values
                = (Map<String, Map<String, String>>) yaml.load(new FileInputStream(new File(filename)));

        for (String key : values.keySet()) {
            System.out.println(key);

            Map<String, String> subValues = values.get(key);

            for (String subValueKey : subValues.keySet()) {
                System.out.println(String.format("\t%s = %s", subValueKey, subValues.get(subValueKey)));
            }
        }
    }
    /*--------------------------------------------------------------------------------------------*/
    public static void main(String[] args) throws FileNotFoundException {

        CreateTable table  = new CreateTable();
        table.connect2MySQL();
        String SQL = table.yaml2sql("TestTable4", "src/createtables/Table.yml" );
        table.execSQL(SQL);
        table.close();
        
        System.out.println(SQL);
    }    
    /*--------------------------------------------------------------------------------------------*/    
}

src/createtables/Table.yml

## YAML Create Table PRODUCT
---
field0:
     name: product_id
     attr: int(11) NOT NULL AUTO_INCREMENT
     index: PRIMARY KEY (product_id)
     note:

field1:
     name: product_name
     attr: varchar(32) NOT NULL
     index: KEY(product_name)
     notes:         
         
field2:
     name: in_stock
     attr: varchar(32) NOT NULL
     index:
     notes:         
         
field3:
     name: sale_price
     attr: decimal NOT NULL
     index:
     notes: