Verify table structure

Verify table structure

This task use java to check table structure and create new table if table structure is not valid.

Verify table structure

1. Create checkTable as following
2. Call checkTable as following

Call method

---

try {
    String dbfile = "C:\\Temp\\test.db";
    Class.forName("org.sqlite.JDBC");
    Connection conn = DriverManager.getConnection("jdbc:sqlite:" + dbfile);
    boolean valid = checkTable(conn, "element", new String[] { "id", "db_id", "eleType", "eleName", "eleFormula", "eleFlags", "eleParentObject", "eleModifiedBy", "eleModified", "score" });
    if (!valid) {
        Statement stat = conn.createStatement();
        stat.executeUpdate("drop table if exists element;");
        stat.executeUpdate("create table element (id char(36) not null primary key, db_id char(36) not null, eleType varchar(255) default null, eleName varchar(255) default null, eleFormula text default null, eleFlags varchar(8000) default null, eleParentObject varchar(255) default null, eleModifiedBy varchar(255) default null, eleModified varchar(50) default null, score int default 0);");
        stat.close();
    }
} catch (Exception e) {
    logger.error("", e);
}
    

checkTable method

---

private boolean checkTable(Connection conn, String table, String[] fields) {
    try {
        PreparedStatement prep = conn.prepareStatement("select * from " + table + " where (1=0);");
        ResultSet rs = prep.executeQuery();
        ResultSetMetaData md = rs.getMetaData();
        List<String> cols = new ArrayList<String>();
        for (int i = 1; i <= md.getColumnCount(); i++) {
            cols.add(md.getColumnName(i).toLowerCase());
        }
        rs.close();
        prep.close();

        if (fields.length > cols.size()) return false;
        for (int i = 0; i < fields.length; i++) {
            if (cols.indexOf(fields[i].toLowerCase()) < 0) {
                return false;
            }
        }
        return true;
    } catch (Exception e) {
        logger.error("", e);
        return false;
    }
}
    

Java