How to parse a table name from CREATE statement using Hibernate?

Question

This is a native create statement for some unknown database carrier

String createStatement = "CREATE TABLE test_database.test_table " + 
"AS  " + 
"(  " + 
"var1,  " + 
"var2  " + 
")  " + 
";  " 
);

I need to parse this String test_database.test_table

I don't know in advance what SQL flavor this CREATE statement is. If I knew that, I would simply use something like

String table = createStatement.split(" ")[2];

But the above solution might not work in all databases. What if some database allows for blanks in table name? So I have to use Hibernate.

How?

lexicore · Accepted Answer · 2017-01-21 20:24:33Z

1

In general, I don't think you can do this without certain assumptions or considering each and every SQL dialect you want to support.

Hibernate itself supportes a number of SQL dialects and you can infer a lot of things from the used dialect. However, org.hibernate.dialect.Dialect does not provide enough information for parse all the possible native CREATE TABLE statements in the selected dialect.

answered Jan 21, 2017 at 20:24

lexicore

43.9k17 gold badges142 silver badges228 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DevilsHnd - 退した · Accepted Answer · 2017-01-21 23:19:49Z

I don't think that Hibernate can take care of all situations especially when dealing with something like Transact-SQL or CREATE GLOBAL TEMPORARY TABLE or even CREATE TEMPORARY TABLESPACE and then you have the AS, AS SELECT, and even PARALLEL COMPRESS AS SELECT after the table name to consider.

As an alternative however you can create a method which can retrieve the Table Name from a supplied CREATE TABLE SQL string which I believe will cover most (if not all) of these issues. Below is such a method:

public String getTableNameFromCreate(final String sqlString) {
    // Always rememeber...we're only trying to get the table 
    // name from the SQL String. We really don't care anything
    // about the rest of the SQL string.
    String tableName;
    String wrkStrg = sqlString.replace("[", "").replace("]", "").trim();
    // Is "CREATE TABLE" only
    if (wrkStrg.startsWith("CREATE TABLE ")) {
        wrkStrg = wrkStrg .substring(13).trim();
    }
    else if (wrkStrg.startsWith("CREATE GLOBAL TEMPORARY TABLE ")) {
        wrkStrg = wrkStrg .substring(30).trim();
    }
    else if (wrkStrg.startsWith("CREATE TEMPORARY TABLESPACE ")) {  
        wrkStrg = wrkStrg .substring(28).trim();
    }

    // Is it Create Table ... AS, AS SELECT, PARALLEL COMPRESS AS, 
    // or PARALLEL COMPRESS AS SELECT?
    if (wrkStrg.toUpperCase().contains(" PARALLEL COMPRESS ")) {
        wrkStrg = wrkStrg.replace(" parallel compress ", " PARALLEL COMPRESS ");
        tableName = wrkStrg.substring(0, wrkStrg.indexOf(" PARALLEL COMPRESS ")).trim();
    }
    else if (wrkStrg.toUpperCase().contains(" AS ")) {
        wrkStrg = wrkStrg.replace(" as ", " AS ");
        tableName = wrkStrg.substring(0, wrkStrg.indexOf(" AS ")).trim();
    }
    // Nope...none of that in the SQL String.
    else {
        tableName = wrkStrg.substring(0, wrkStrg.indexOf("(")).trim();
    }

    // return but remove quotes first if any...
    return tableName.replace("\"","").replace("'", "");
}

If the database name is attached to the table name as in your example (test_database.test_table) then of course you will need to further parse off the actual table name.

Collectives™ on Stack Overflow

How to parse a table name from CREATE statement using Hibernate?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related