jmdbtools.JMdbTools.java Source code

Java tutorial

Introduction

Here is the source code for jmdbtools.JMdbTools.java

Source

/*
 JMdbTools
 Copyright (c) 2013, J. Chambers, All rights reserved.
    
 This library is free software; you can redistribute it and/or
 modify it under the terms of the GNU Lesser General Public
 License as published by the Free Software Foundation; either
 version 3.0 of the License, or (at your option) any later version.
    
 This library is distributed in the hope that it will be useful,
 but WITHOUT ANY WARRANTY; without even the implied warranty of
 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 Lesser General Public License for more details.
    
 You should have received a copy of the GNU Lesser General Public
 License along with this library.
 */

package jmdbtools;

import com.healthmarketscience.jackcess.*;
import com.healthmarketscience.jackcess.util.ExportUtil;
import com.healthmarketscience.sqlbuilder.CreateTableQuery;
import com.healthmarketscience.sqlbuilder.InsertQuery;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbColumn;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbSchema;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbSpec;
import com.healthmarketscience.sqlbuilder.dbspec.basic.DbTable;
import org.apache.commons.cli.*;

import java.io.File;
import java.io.IOException;
import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 *
 *
 *
 * Date: 25/11/2013
 */
public class JMdbTools {

    public String filePath;
    public Database db;

    public String tablePrefix = "";
    public String dbUsername = "root";
    public String dbPassword = null;
    public String dbName = null;
    public Boolean dbOverwrite = null;

    public JMdbTools(CommandLine cmd) {
        filePath = cmd.getOptionValue("f");
        loadDB();
        processOptions(cmd);
    }

    //replace MySQL reserved names with acceptable alternative
    private String fixColumnName(String columnName) {
        Map<String, String> reservedNames = new HashMap<String, String>();
        reservedNames.put("database", "dbase");

        if (reservedNames.containsKey(columnName)) {
            return reservedNames.get(columnName);
        } else {
            return columnName;
        }
    }

    private String fixTableName(String tableName) {
        if (tablePrefix.equalsIgnoreCase("")) {
            return tableName;
        } else {
            return tablePrefix + tableName;
        }
    }

    public static void main(String[] args) {
        System.out.println("[INFO] Starting mdb file processing");
        Options options = createOptions();
        CommandLineParser parser = new BasicParser();
        try {
            CommandLine cmd = parser.parse(options, args);

            if (cmd.hasOption("f")) {

                JMdbTools tool = new JMdbTools(cmd);

                tool.processOptions(cmd);

                if (cmd.hasOption("s")) {
                    tool.showStats();
                }
                //export
                if (cmd.hasOption("e")) {
                    tool.exportCSV();
                }

                //export to mysql
                if (cmd.hasOption("db")) {

                    tool.exportToMySQL();
                }

            } else {
                System.out.println("[ERROR] No file parameter given!");

            }
        } catch (ParseException e) {
            e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
        }
    }

    private static Options createOptions() {
        // create Options object
        Options options = new Options();

        // add t option
        options.addOption("f", true, "input mdb file path");
        options.addOption("s", false, "show file stats");
        options.addOption("e", false, "export file");
        options.addOption("db", true, "export to database");
        options.addOption("u", true, "database username");
        options.addOption("p", true, "database password");
        options.addOption("o", false, "overwrite existing tables");
        options.addOption("tp", true, "table prefix");

        return options;
    }

    private void processOptions(CommandLine cmd) {
        if (cmd.hasOption("tp")) {
            tablePrefix = cmd.getOptionValue("tp");
        }
        if (cmd.hasOption("db")) {
            dbName = cmd.getOptionValue("db");
        }
        if (cmd.hasOption("o")) {
            dbOverwrite = true;
        }
        if (cmd.hasOption("u")) {
            dbUsername = cmd.getOptionValue("u", "root");
        }
        if (cmd.hasOption("p")) {
            dbPassword = cmd.getOptionValue("p", null);
        }
    }

    private void loadDB() {
        try {
            File file = new File(filePath);
            db = DatabaseBuilder.open(file);

            log("# Loaded database from file: " + file.getName(), "info");
        } catch (IOException e) {
            e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
        }
    }

    private void showStats() {
        Set<String> tableNames = null;
        try {
            tableNames = db.getTableNames();
            for (String tableName : tableNames) {
                Table table = db.getTable(tableName);

                log(tableName);
                log("Row count");
                System.out.println(table.getRowCount());

                for (Column column : table.getColumns()) {
                    String columnName = column.getName();
                    //Object value = row.get(columnName);
                    log("Column " + columnName + "(" + column.getType() + ") ", "info");
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    private void exportCSV() {
        File dbFile = db.getFile();
        String path = dbFile.getPath() + "-csv";
        File dir = new File(path);
        if (dir.mkdir()) {
            System.out.println("[INFO] Exporting data to directory: " + dir.getAbsolutePath());

            ExportUtil.Builder builder = new ExportUtil.Builder(db);
            builder.setHeader(true); //enable column headers
            try {
                builder.exportAll(dir);
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            System.out.println("[ERROR] directory not created");
        }
    }

    private void exportToMySQL() {
        try {
            System.out.println(dbName);
            Connection conn = connectToMySQL(dbName, dbUsername, dbPassword);

            DbSchema tableSet = createDbSchema();

            createTables(tableSet, conn);

            insertAllData(tableSet, conn);
        } catch (SQLException e) {
            e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
        } catch (IOException e) {
            e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
        } finally {
            //finally block used to close resources
            //conn.close();

        }
    }

    private Connection connectToMySQL(String dbName, String user, String pass) {
        // Initialize MYSQL java driver
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
        } catch (InstantiationException e) {
            e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
        } catch (IllegalAccessException e) {
            e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
        } catch (ClassNotFoundException e) {
            e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
        }

        Connection conn = null;
        try {
            String connectionString = "jdbc:mysql://" + dbName + "?user=" + user;

            if (pass != null) {
                connectionString = connectionString + "&password=" + pass;
            }

            conn = DriverManager.getConnection(connectionString);
        } catch (SQLException e) {
            log("SQLException: " + e.getMessage(), "error");
            log("SQLState: " + e.getSQLState(), "error");
            log("VendorError: " + e.getErrorCode(), "error");
        }

        return conn;
    }

    private DbSchema createDbSchema() {
        DbSpec spec = new DbSpec();
        DbSchema schema = spec.addDefaultSchema();
        Set<DbTable> dbTables = new HashSet<DbTable>();

        try {
            Set<String> tableNames = db.getTableNames();

            for (String tableName : tableNames) {
                Table table = db.getTable(tableName);
                DbTable dbTable = createTableSchema(table, schema);
                dbTables.add(dbTable);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }

        return schema;
    }

    private DbTable createTableSchema(Table table, DbSchema schema) {

        // add table with basic customer info
        DbTable customerTable = schema.addTable(fixTableName(table.getName()));

        for (Column column : table.getColumns()) {
            //System.out.println("[INFO] ADDING Column " + column.getName() + "(" + column.getType() + ")");

            String columnType = "TEXT";
            if (column.getType() == DataType.SHORT_DATE_TIME) {
                //TODO: distinguish between DATE and DATETIME (?)
                columnType = "DATETIME"; //Dicking about with Datetime for MYSQL
            } else {
                columnType = column.getType().name();
            }
            customerTable.addColumn(fixColumnName(column.getName()), columnType, null);
        }

        return customerTable;
    }

    private void createTables(DbSchema schema, Connection conn) throws SQLException {

        Statement stmt = conn.createStatement();
        for (DbTable dbTable : schema.getTables()) {
            String createTableSql = new CreateTableQuery(dbTable, true).validate().toString();

            //TODO: DANGEROUS: auto override tables. Add explicit option to enable
            if (dbOverwrite) {
                log("Dropping existing table", "warn");
                stmt.executeUpdate("DROP TABLE IF EXISTS `" + dbTable.getName() + "`");
                log("creating table:" + dbTable.getName(), "info");
                stmt.executeUpdate(createTableSql);
            } else {
                DatabaseMetaData meta = conn.getMetaData();
                ResultSet res = meta.getTables(null, null, dbTable.getName(), new String[] { "TABLE" });
                if (res.last()) {
                    //there are entries for "TABLE" with this name don't try to create table
                    log("Table already exists:" + dbTable.getName(), "info");

                } else {
                    log("creating table:" + dbTable.getName(), "info");
                    stmt.executeUpdate(createTableSql);
                }
            }
        }
    }

    private void insertAllData(DbSchema schema, Connection conn) throws IOException {

        for (String tableName : db.getTableNames()) {
            Table table = db.getTable(tableName);
            DbTable dbTable = schema.findTable(fixTableName(tableName));
            insertData(table, dbTable, conn);

        }
    }

    private int[] insertData(Table dataTable, DbTable dbTable, Connection conn) {
        Statement stmt = null;
        int[] execStatus = new int[0];
        try {
            stmt = conn.createStatement();
            log("Creating Insert Statements:" + dbTable.getName(), "info");

            for (Row row : dataTable) {
                InsertQuery insertQuery = new InsertQuery(dbTable);
                for (Map.Entry<String, Object> col : row.entrySet()) {
                    //We had to add crap to the column name, so have to muck about to get match

                    DbColumn column = dbTable.findColumn(fixColumnName(col.getKey()));

                    if (col.getValue() != null) {
                        if (column.getTypeNameSQL().equalsIgnoreCase("DATE")
                                || column.getTypeNameSQL().equalsIgnoreCase("DATETIME")) {
                            java.sql.Timestamp sqlDate = new java.sql.Timestamp(((Date) col.getValue()).getTime());
                            //log(sqlDate.toString(), "debug");
                            insertQuery.addColumn(column, sqlDate);
                        } else {
                            insertQuery.addColumn(column, col.getValue());
                        }
                    }
                }
                stmt.addBatch(insertQuery.validate().toString());
            }
            log("Executing Insert", "info");

            execStatus = stmt.executeBatch();
        } catch (SQLException e) {
            e.printStackTrace(); //To change body of catch statement use File | Settings | File Templates.
        }

        return execStatus;

    }

    private static void log(String message) {
        log(message, "info");
    }

    private static void log(String message, String level) {
        String prfx = "";
        if (level.equalsIgnoreCase("info")) {
            prfx = "[INFO] ";
        } else if (level.equalsIgnoreCase("error")) {
            prfx = "[ERROR] ";
        } else if (level.equalsIgnoreCase("debug")) {
            prfx = "[DEBUG] ";
        } else if (level.equalsIgnoreCase("warn")) {
            prfx = "[WARN] ";
        }
        System.out.println(prfx + message);
    }
}