Creating Data Base through Stringbuilder

Question

I am creating my database with the class sqliteOpenHelper, passing the creation of DB by String through the StringBuilder.
The problem is, it’s just creating the first table, and then it doesn’t create the next one, in which case I’m creating the city first. If I reversed putting consumption by first and the others in sequence, it would create consumption and not create the next, ie it always creates the first table only.

I thought it might be because of the character limit, so I used the str.ensureCapacity(10000); but without success.

public void onCreate(SQLiteDatabase db) {

    StringBuilder str = new StringBuilder();

    str.append("CREATE TABLE cidade (");
    str.append("_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ");
    str.append("nome VARCHAR(20), ");
    str.append("estado VARCHAR(2), ");
    str.append("vlaguaI DECIMAL, ");
    str.append("vlaguaII DECIMAL, ");
    str.append("vlaguaIII DECIMAL, ");
    str.append("vlinicial_faixa_consumoI INTEGER, ");
    str.append("vlfinal_faixa_consumoI INTEGER, ");
    str.append("vlinicial_faixa_consumoII INTEGER, ");
    str.append("vlfinal_faixa_consumoII INTEGER, ");
    str.append("vlinicial_faixa_consumoIII INTEGER, ");
    str.append("vlfinal_faixa_consumoIII INTEGER, ");
    str.append("vl_esgoto DECIMAL, ");
    str.append("vl_taxa_afastamento DECIMAL); ");

    str.append("CREATE TABLE consumo (");
    str.append("_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ");
    str.append("dt_leitura DATE), ");
    str.append("registro INTEGER, ");
    str.append("vl_consumo DECIMAL); ");

    str.append("CREATE TABLE configuracao (");
    str.append("_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ");
    str.append("id_cidade INTEGER NOT NULL, ");
    str.append("hidrometro INTEGER, ");
    str.append("CONSTRAINT fk_configuracao ");
    str.append("FOREIGN KEY(id_cidade) ");
    str.append("REFERENCES cidade(_id)); ");

    str.append("CREATE INDEX configuracao.fk_configuracao_idx ON configuracao(id_cidade); ");

    db.execSQL(str.toString());

Answer 1

First, as Maniero pointed out in his answer, you left an extra parenthesis opening here:

    str.append("CREATE TABLE consumo (");
    str.append("_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ");
    str.append("dt_leitura DATE), "); // <--- ERRO!!!!
    str.append("registro INTEGER, ");
    str.append("vl_consumo DECIMAL); ");

Second, as the ramaral pointed out in the his answer, the execSQL() only executes one instruction at a time.

Third, there’s no point in using the StringBuilder this way. The Strings are immutable and fixed, so it is better to build them in advance rather than to build them every time the method is executed. Also, the compiler is very smart, and he knows when to optimize the concatenation of Stringfixed s with the operator + in order to generate a bytecode where the String already appears concatenated. However, if you do this manually using the StringBuilder, the compiler will not be able to do this optimization.

So your code looks much better this way:

private static final String SQL_CREATE_CIDADE = ""
        + "CREATE TABLE cidade ("
        + "    _id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
        + "    nome VARCHAR(20),"
        + "    estado VARCHAR(2),"
        + "    vlaguaI DECIMAL,"
        + "    vlaguaII DECIMAL,"
        + "    vlaguaIII DECIMAL,"
        + "    vlinicial_faixa_consumoI INTEGER,"
        + "    vlfinal_faixa_consumoI INTEGER,"
        + "    vlinicial_faixa_consumoII INTEGER,"
        + "    vlfinal_faixa_consumoII INTEGER,"
        + "    vlinicial_faixa_consumoIII INTEGER,"
        + "    vlfinal_faixa_consumoIII INTEGER,"
        + "    vl_esgoto DECIMAL,"
        + "    vl_taxa_afastamento DECIMAL"
        + ");";

private static final String SQL_CREATE_CONSUMO = ""
        + "CREATE TABLE consumo ("
        + "    _id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
        + "    dt_leitura DATE,"
        + "    registro INTEGER,"
        + "    vl_consumo DECIMAL"
        + ");";

private static final String SQL_CREATE_CONFIGURACAO = ""
        + "CREATE TABLE configuracao ("
        + "    _id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,"
        + "    id_cidade INTEGER NOT NULL,"
        + "    hidrometro INTEGER,"
        + "    CONSTRAINT fk_configuracao"
        + "    FOREIGN KEY(id_cidade)"
        + "    REFERENCES cidade(_id)"
        + ");";

private static final String SQL_CREATE_CONFIGURACAO_INDEX =
        "CREATE INDEX configuracao.fk_configuracao_idx ON configuracao(id_cidade);";

private static final String[] SQLS = {SQL_CREATE_CIDADE, SQL_CREATE_CONSUMO, SQL_CREATE_CONFIGURACAO, SQL_CREATE_CONFIGURACAO_INDEX};

public void onCreate(SQLiteDatabase db) {
    for (String s : SQLS) {
        db.execSQL(s);
    }
}

Answer 2

The problem is on this line:

str.append("dt_leitura DATE), ");

I put in the Github for future reference.

You close the parenthesis and then continue, which generates a syntax error.

Answer 3

In the documentation may verify that the execSQL() only executes one SQL statement at a time.

You will have to build each of the "Create Table...", one at a time and run them in turn.

Answer 4

As stated by ramaral, to documentation of execSQL() points out that the method only executes one SQL at a time. What you can do is create a StringTokenizer to split the SQL statements and run them one by one. It would be something like this:

StringTokenizer tokenizer = new StringTokenizer( str.toString(), ";", false);

while ( tokenizer.hasMoreTokens() )
{
    db.execSQL(tokenizer.nextToken());
}

In that case, you’d be splitting yours StringBuilder each ; found. That is, each SQL will be executed separately within the while.

Also check this excerpt: str.append("dt_leitura DATE), ");, where you close the parentheses before finishing the SQL statement.