tutorial - sqlite android studio ejemplo

cómo hacer inserciones masivas en sqlite en android

Question

Estoy usando SQLiteOpenHelper para la inserción de datos. Necesito insertar 2500 ID y 2500 nombres, así que toma demasiado tiempo. Por favor, que alguien me ayude a reducir el tiempo de inserción. ¿Podemos insertar múltiples registros a la vez? alguien me ayude gracias de antemano. código:

public class DatabaseHandler extends SQLiteOpenHelper {
SQLiteDatabase db;
private static final int DATABASE_VERSION = 8;
private static final String TABLE_CITY = "CITYDETAILS";
public DatabaseHandler(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
this.mContext = context;
}
public void onCreate(SQLiteDatabase db) {
db.execSQL("DROP TABLE IF EXISTS " + TABLE_CITY );
String CREATE_CITY_TABLE = "CREATE TABLE " + TABLE_CITY + "("
+ CityId + " INTEGER," + CityName + " TEXT " + ")";
db.execSQL(CREATE_CITY_TABLE);
db.execSQL(CREATE_RechargeTypes_TABLE);
this.db=db;
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// Drop older table if existed
// Create tables again
onCreate(db);
}
public void add_city(String cityid,String cityname){
SQLiteDatabase db = this.getWritableDatabase();
db.beginTransaction();
ContentValues values = new ContentValues();
values.put(CityId, cityid);
values.put(CityName, cityname);
db.insert(TABLE_CITY, null, values);
db.insertWithOnConflict(TABLE_CITY, null, values, SQLiteDatabase.CONFLICT_IGNORE);
db.setTransactionSuccessful();
db.endTransaction();
}
}

actividad calss:

try{
String Status = result.get("Status").getAsString();
if (TextUtils.equals(Status, "true")) {
Gson gson = new Gson();
JsonArray array = result.get("data")
.getAsJsonArray();
Type type = new TypeToken<ArrayList<Cities>>() {
}.getType();
setmCities((ArrayList<Cities>) gson.fromJson(array, type));
for(int i=0;i< array.size();i++) {
db.add_city(mCities.get(i).getCityid(),mCities.get(i).getCityname());
}
}

Answer 1

Gracias @karakuri. Paso la lista de ID de ciudad y nombre de ciudad a la función add_city y recorro esa lista e inserto todos los datos. A partir de esto podemos insertar datos en la base de datos en poco tiempo.

clase de base de datos:

public void add_cities(ArrayList<Cities> list) {
SQLiteDatabase db = this.getWritableDatabase();
db.beginTransaction();
try {
ContentValues values = new ContentValues();
for (Cities city : list) {
values.put(CityId, city.getCityid());
values.put(CityName, city.getCityname());
db.insert(TABLE_CITY, null, values);
}
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}
}

clase de datos:

public class Cities {
public String getCityid() {
return cityid;
}
public void setCityid(String cityid) {
this.cityid = cityid;
}
public String getCityname() {
return cityname;
}
public void setCityname(String cityname) {
this.cityname = cityname;
}
@Expose
private String cityid="0";
@Expose
private String cityname="";
public Cities(){
}
}

clase de actividad:

 ArrayList<Cities> mCities;
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_splash);
db = new DatabaseHandler(getApplicationContext());
executeCircleAndOperatorsList();
}
void executeCircleAndOperatorsList() {
db.ClearTables();
ServiceClient serviceClient = ServiceUtil.getServiceClient();
JsonParser jsonParser = new JsonParser();
mCitiesCallback = new CancelableCallback(citiescallback);
serviceClient.getCities("getCities_v1", mCitiesCallback);
}
Callback<JsonObject> citiescallback=new Callback<JsonObject>() {
@Override
public void success(final JsonObject jsonObject, Response response) {
Thread t=new Thread(new Runnable() {
@Override
public void run() {
parsecities(jsonObject);
runOnUiThread(new Runnable() {
@Override
public void run() {
Toast.makeText(getApplicationContext(), "msg msg", Toast.LENGTH_SHORT).show();
Intent intent=new Intent(SplashActivity.this,LoginAcivtiy.class);
startActivity(intent);
}
});
}
});
t.start();
}
@Override
public void failure(RetrofitError error) {
}
};
public void parsecities(JsonObject result) {
//Log.v("TAG_RESULT", "" +result.toString());
try{
String Status = result.get("Status").getAsString();
if (TextUtils.equals(Status, "true")) {
Gson gson = new Gson();
JsonArray array = result.get("data")
.getAsJsonArray();
Type type = new TypeToken<ArrayList<Cities>>() {
}.getType();
setmCities((ArrayList<Cities>) gson.fromJson(array, type));
long start = System.currentTimeMillis();
db.add_cities(mCities);
System.out.println(System.currentTimeMillis() - start);
circle_list=db.get_cities();
Log.v("TAG_CIRCLELIST",""+circle_list);
}
else if (TextUtils.equals("Status", "false")) {
// showToast(operators.getMessage());
} else {
throw new JSONException("Something went wrong ");
} }catch(Exception e){
e.printStackTrace();
}
}
public void setmCities(ArrayList<Cities> mCities) {
this.mCities = mCities;
}
}

Answer 2

Ponga sus consultas en la matriz de cadenas en string.xml, ábralas en código con el manejador de recursos y realicelas en ciclo. Antes del ciclo, utilice los métodos beginTransaction () y entTransaction () para revertir los cambios en caso de fallo.

Answer 3

Use una transacción para insertar todas las filas, NO una fila por transacción.

SQLiteDatabase db = ...
db.beginTransaction();
try {
// do ALL your inserts here
db.setTransactionSuccessful()
} finally {
db.endTransaction();
}

EDITAR

public void add_cities(List<Cities> list) {
SQLiteDatabase db = this.getWritableDatabase();
db.beginTransaction();
try {
ContentValues values = new ContentValues();
for (Cities city : list) {
values.put(CityId, city.getCityid());
values.put(CityName, city.getCityName());
db.insert(TABLE_CITY, null, values);
}
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}
}

TODOS los insertos, UNA transacción.

Answer 4

prueba SQLiteStatement
en 2-3 veces más rápido que apenas beginTransaction ()

public void add_cities(ArrayList<Cities> list) {
SQLiteDatabase database = this.getWritableDatabase();
String sql = "INSERT INTO " + TABLE_NAME + " VALUES(?, ?)";
SQLiteStatement statement = database.compileStatement(sql);
database.beginTransaction();
try {
for (Cities c : list) {
statement.clearBindings();
statement.bindLong(1, c.getCityId());
statement.bindLong(2, c.getCityName());
statement.execute();
}
database.setTransactionSuccessful();
} finally {
database.endTransaction();
}
}