A MySQL schema parser in Java?

Question

Hi all,

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

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.

Answer 2

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);
    }

}

}