A MySQL schema parser in Java?

Question

Does anyone know whether there is a java library for parsing a MySQL schema? In code I want to be able to determine the tables and fields specified in a schema. Or will I need to write my own?

Thanks Richard.

Edit: Just want to avoid re-inventing the wheel unnecessarily :)

Answer 1

Answering my own question:

Am using jsqlparser http://jsqlparser.sourceforge.net/

This parses individual statements, not multiple statements such as found in a schema. So split the schema on ';'. It also doesn't like the '`' character, so these need to be stripped out. Code to get column names for a particular table:

public class BUDataColumnsFinder {

public static String[] readSql(String schema) throws IOException {
    BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(schema)));
    String mysql = "";
    String line;
    while ((line = br.readLine()) != null) {
        mysql = mysql + line;
    }
    br.close();
    mysql = mysql.replaceAll("`", "");
    return mysql.split(";");
}

public static List<String> getColumnNames(String tableName, String schemaFile) throws JSQLParserException, IOException {

    CCJSqlParserManager pm = new CCJSqlParserManager();
    List<String> columnNames = new ArrayList<String>();

    String[] sqlStatements = readSql(schemaFile);

    for (String sqlStatement : sqlStatements) {

        Statement statement = pm.parse(new StringReader(sqlStatement));

        if (statement instanceof CreateTable) {

            CreateTable create = (CreateTable) statement;
            String name = create.getTable().getName();

            if (name.equalsIgnoreCase(tableName)) {
                List<ColumnDefinition> columns = create.getColumnDefinitions();
                for (ColumnDefinition def : columns) {
                    columnNames.add(def.getColumnName());
                }
                break;
            }
        }
    }

    return columnNames;
}


public static void main(String[] args) throws Exception {

    String schemaFile = "/home/john/config/bu-schema.sql";

    String tableName = "records";

    List<String> columnNames = BUDataColumnsFinder.getColumnNames(tableName, schemaFile);

    for (String name : columnNames) {
        System.out.println("name: " + name);
    }

}

}

Answer 2

You may want to consider using code from Alibaba's Druid project. Although designed as a sophisticated connection pooling library, this project supports a very advanced parser and AST for ANSI SQL and non-ANSI dialects such as MySQL, Oracle, SQL Server, etc. The project is open source and bears the very liberal Apache License Version 2.0.

The main entry points into this part of the library is SQLUtils.java. You can use values returned from SQLUtils.parseStatements to access a typed model of the statements:

List<SQLStatement> statements = SQLUtils.parseStatements(sql, JdbcConstants.MYSQL);
for (SQLStatement statement : statements) {
   if (statement instanceof MySqlCreateTableStatment) {
      MySqlCreateTableStatment createTable = (MySqlCreateTableStatment) statement;
      // Use methods like: createTable.getTableSource()
   }
}

Answer 3

Why not just use DatabaseMetaData to find out the tables and columns? This presumes that the schema expressed in SQL has been run against the database you're connected to, but that's not a difficult assumption to satisfy.

MySQL might be able to simply import the data if you have the data in CSV format. I'd dig deeper into MySQL tools before I'd write Java code to do such a thing. If that doesn't work, I'd find an ETL tool to help me. Writing Java would be my solution of last resort.