How to store database table schema (>50 columns) in Java

Question

Hi I have a scenario where I read table schema from a file and I need to create table using JDBC.

For e.g. File contains

{"ColumnTypes":["int32","fstring32"],"ColumnNames":["ProductId",","ProductName"]}

Now I need to create table at run time using JDBC execute query. Suppose I create table using array half array contains columns name half array contains datatype then I will will have to type all the values by hand is there any best efficient way?

create table tb1 (arr[1], arr[26], arr[2], arr[26].....arr[25],arr[50])

Please guide. Thanks in advance.

arrays when the indexes are known are faster (HashMap uses an array of linked lists behind the scenes which adds a bit of overhead above the array accesses not to mention the hashing operations that need to be done) — Ayush Pandey, Jun 28 '13 at 11:39

score 1 · Accepted Answer · edited May 23 '17 at 11:45

If your database definitions really contain name/type pairs only and no further information like indices, foreign key references or other constraints, then a Map<String, String> would be sufficient. You could create a class, e.g. TableDef:

public class TableDef {
  private final String tableName;
  private final Map<String, String> colDef = new LinkedHashMap<String, String>();

  public TableDef(String tableName) {
     this.tableName = tableName;
  }

  public void addColumn(String name, String type) {
    if (colDef.containsKey(name))
      throw new IllegalArgumentException(name + " column already added");
    colDef.put(name, type);
  }

  public String getCreateTable() {
    String ddl = "CREATE TABLE " + tableName + "(";
    for (Map.Entry<String, String> col : colDef.entrySet()) {
      // add column to ddl
    }
    return ddl + ")";
  }
}

Alternatively, if you can be certain that no duplicate column names exist, you could build the CREATE TABLE statement dynamically while reading your schema definition. However, it won't make much difference in performance as long as you don't have thousands of columns and tables.

To insert bulk data in an efficient way, take a look at this answer.

score 0 · Answer 2 · answered Jun 28 '13 at 11:34

0

If all that you need is to create table or migrate database in general you might want to look at http://flywaydb.org/ or http://www.liquibase.org/ you will not repent

answered Jun 28 '13 at 11:34

Nitin Tripathi

491
2
10

How to store database table schema (>50 columns) in Java

2 Answers2