Insert data when creating SQLite table

Question

Insert data when creating SQLite table

Navigation

#1 by (1 votes)
#2 by (1 votes)

0

In my database I have a State and Cities table, I would like to insert this data once the app is installed, ie when creating the database, these records are already inserted in the tables at once. I made the following code but is inserting only the first State

public class BancoDados extends SQLiteOpenHelper {

public static final String NOME_BANCO = "carona";
private static final int VERSAO_BANCO = 1;

public BancoDados(Context context) {
    super(context, NOME_BANCO, null, VERSAO_BANCO);
}

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(SQL_ESTADO);
    db.execSQL(SQL_LISTA_ESTADOS);

}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

}

String SQL_ESTADO = "CREATE TABLE [Estado](\n" +
        "[EstadoId] [INT] NOT NULL,\n" +
        "[Sigla] [char](2) NOT NULL,\n" +
        "CONSTRAINT [PK_Estado] PRIMARY KEY ([EstadoId])\n" +
        ");";


String SQL_LISTA_ESTADOS = "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (1,'AC');\n"+
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (2,'AL'); " +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (3,'AM'); " +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (4,'AP'); " +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (5,'BA'); " +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (6,'CE'); " +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (7,'DF'); " +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (8,'ES'); " +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (9,'GO');  +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (10,'MA');" +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (11,'MG');" +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (12,'MS');" +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (13,'MT');" +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (14,'PA');" +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (15,'PB');" +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (16,'PE');" +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (17,'PI');" +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (18,'PR');" +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (19,'RJ');" +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (20,'RN');" +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (21,'RO');" +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (22,'RR');" +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (23,'RS');" +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (24,'SC');" +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (25,'SE');" +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (26,'SP');" +
        "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES (27,'TO');";

}

java android sqlite

asked by anonymous 19.05.2017 / 02:28

2 answers

RegularExpression allow greater than 0.00 Return Jquery data inside Input

score 1 · Answer 1

If your version of SQLite is greater than or equal to 3.7.11 you can insert multiple rows in the same insert, according to documentation

a>. Try replacing your SQL string with this:

String SQL_LISTA_ESTADOS = "INSERT INTO [Estado] ([EstadoId],[Sigla]) VALUES " +
        "(1,'AC'), " +
        "(2,'AL'), " +
        "(3,'AM'), " +
        "(4,'AP'), " +
        "(5,'BA'), " +
        "(6,'CE'), " +
        "(7,'DF'), " +
        "(8,'ES'), " +
        "(9,'GO'), " +
        "(10,'MA')," +
        "(11,'MG')," +
        "(12,'MS')," +
        "(13,'MT')," +
        "(14,'PA')," +
        "(15,'PB')," +
        "(16,'PE')," +
        "(17,'PI')," +
        "(18,'PR')," +
        "(19,'RJ')," +
        "(20,'RN')," +
        "(21,'RO')," +
        "(22,'RR')," +
        "(23,'RS')," +
        "(24,'SC')," +
        "(25,'SE')," +
        "(26,'SP')," +
        "(27,'TO');";

score 1 · Answer 2

So you need to use a table for this if this data is always static, so, use an array states and ready, hence it would look like oO:

String [][] Estados = new String[][] {
    {"1","AC"},
    {"2","AL"},
    {"3","AM"},
    {"4","AP"},
    {"5","BA"},
    {"6","CE"},
    {"7","DF"},
    {"8","ES"},
    {"9","GO"},
    {"10","MA"},
    {"11","MG"},
    {"12","MS"},
    {"13","MT"},
    {"14","PA"},
    {"15","PB"},
    {"16","PE"},
    {"17","PI"},
    {"18","PR"},
    {"19","RJ"},
    {"20","RN"},
    {"21","RO"},
    {"22","RR"},
    {"23","RS"},
    {"24","SC"},
    {"25","SE"},
    {"26","SP"},
    {"27","TO"}
    };

So you get it using:

int mEstId = Integer.parseInt(Estados[10][0]); // == 9
String mEstSg = Estados[10][1]; // == "GO"

You can also use a loop to mount your query and insert into the table something like:

public void insereNaTabela() {
    SQLiteDatabase db = this.getWritableDatabase();
    String sql = NULL;
    for (int i=0;i<Estados.lenght;i++) {
        sql = "INSERT INTO Estado (EstadoId,Sigla) VALUES ('" + Estados[i][0] + "','" + Estados[i][1] + "');";
        db.execSQL(sql);
    }
}
// especificamente insere linha a linha na tabela, isso evitaria erros

Remembering that:
1 - sqlite independe of data types, so either you pass 10 or '10' to an integer column will enter the value 10 in the same way

2 - It is recommended in the documentation to use '10' to insert values, since it does not depend on the data type;
3 - you can use either the array directly to get the data you need to get a state or use the same in the loop to insert into the table if you need a specific relation in db. Finally, so your code would be much leaner.

Health and Peace!