Creating base date using StringBuilder

Navigation
2

I'm creating my database with class sqliteOpenHelper , passing DB creation by String through StringBuilder . The problem is that you are only creating the first table, and then you do not create the next table, in which case I am creating the city first. If I invested putting the consumption first and the others in sequence, it would create consumption and would not create the next, ie it always creates the first table only.

I thought it might be because of character limit, so I used str.ensureCapacity(10000); but no 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());
    
asked by anonymous 10.11.2015 / 23:27

4 answers

4

First, as bigown pointed out in the response from him , you left one open in parentheses 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 branch pointed out in response from it , execSQL() only executes one statement at a time.

Third, it does not make sense to use StringBuilder like this. The String s are immutable and fixed, so it is better to construct them in advance rather than construct them every time the method is executed. In addition, the compiler is very smart, and it knows when to optimize the concatenation of String s fixed with the + operator in order to generate a bytecode where String already appears concatenated. However, if you do this manually using StringBuilder , the compiler will not be able to do this optimization.

So your code looks a lot 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);
    }
}
    
11.11.2015 / 03:03
3

The problem lies in this line: 

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

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

    
10.11.2015 / 23:31
3

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

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

    
10.11.2015 / 23:37
2

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

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

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

In this case, you would be dividing your StringBuilder into every ; found. That is, each SQL will run separately within while .

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

    
11.11.2015 / 00:57
How to get data from different tables and insert into a new [closed] Current Location does not work with MapModule