Compare an SQL file to an existing database table with Java

Question

I am writing a bit of Java (1.7) code to test a given database table against a given sql file. What I would like is a way to turn my sql file into a java object, then test the db field names and field types are the same as the file backed object.

An example sql file looks like this:

create table foo (
    id int not null auto_increment,
    term_id varchar(128) not null,
    term_name varchar(255) not null,
    parent_id varchar(128) not null,
    parent_name varchar(255),
    top_term_flag varchar(5),
    primary key (id)
);
create index foo_pn on foo ( parent_name );
create index foo_ttf on foo ( top_term_flag );

And the part of my Java program to do this check looks like this:

// Step 1, confirm the table exists

// Database and table tests
DatabaseMetaData dbm = connection.getMetaData();

// check if "this.dbtable" exists.
// The ToUpperCase covers Oracle
ResultSet tables = dbm.getTables(null, null, this.dbtable.toUpperCase(), null);
if (tables.next()) {
    // Table exists
    log.info("Table: {} exists!", this.dbtable);

    // Step 2, get each field and test against the file

    ResultSet columns = dbm.getColumns(null, null, this.dbtable, null);

    while ( columns.next()) {
        String name = columns.getString(4);  // this gets the column name

        -> Now what? <-

    }

}

I've looked at Spring JDBCTestUnit and Flyway, but they don't seem to provide the functionality I need.

Thank you.

Update: I understand I can also use Hibernate to generate my Java classes that represent my sql file and then test the DB table against those. Does any one have a sample for how to get this done?

score 0 · Answer 1 · edited May 23 '17 at 12:13

If the SQL file syntax doesn't vary much from your example, you could write a simple parser to read the file and generate your java object: table plus list of fields/types and indexes

"tablename" always comes after "create table"
the field names and types always come after that
indexes after that

Or there are parsers available: jsqlparser http://jsqlparser.sourceforge.net/

Other questions on this site cover some of the same ground SQL parser library for Java

score 0 · Accepted Answer · edited Feb 20 '14 at 15:51

Using JSqlParser 0.8.8 from https://github.com/JSQLParser/JSqlParser.

Here is a parsing example to get column names, table name, types. As a result you get a hierarchy of java objects from your sqls.

public class CheckSQLs {

  public static void main(String[] args) throws JSQLParserException {
    String sqls = "create table foo (\n"
            + "    id int not null auto_increment,\n"
            + "    term_id varchar(128) not null,\n"
            + "    term_name varchar(255) not null,\n"
            + "    parent_id varchar(128) not null,\n"
            + "    parent_name varchar(255),\n"
            + "    top_term_flag varchar(5),\n"
            + "    primary key (id)\n"
            + ");\n"
            + "create index foo_pn on foo( parent_name );\n"
            + "create index foo_ttf on foo ( top_term_flag );";

    for (String sql : sqls.split(";")) {
        Statement parse = CCJSqlParserUtil.parse(sql);
        System.out.println(parse);
        if (parse instanceof CreateTable) {
            CreateTable ct = (CreateTable)parse;
            System.out.println("table=" + ct.getTable().getFullyQualifiedName());
            for (ColumnDefinition colDef : ct.getColumnDefinitions()) {
                System.out.println("column=" + colDef.getColumnName() + " " + colDef.getColDataType() + " " + colDef.getColumnSpecStrings());
            }
        } 
    }
  }
}

This runs with the output:

CREATE TABLE foo (id int not null auto_increment, term_id varchar (128) not null, term_name varchar (255) not null, parent_id varchar (128) not null, parent_name varchar (255), top_term_flag varchar (5), primary key (id))
table=foo
column=id int [not, null, auto_increment]
column=term_id varchar (128) [not, null]
column=term_name varchar (255) [not, null]
column=parent_id varchar (128) [not, null]
column=parent_name varchar (255) null
column=top_term_flag varchar (5) null

Now you could use this object to validate against your database.

in jsqlparser 0.8.9 snapshot I included simple script parsing. So the split part would go away. — wumpz, Feb 16 '14 at 07:50

Compare an SQL file to an existing database table with Java

2 Answers2