Android - How to handle 1000 records SQLite

Question

I'm trying to add 1000 records on my SQLite at start, but the thing is that when I launch the APP it turns white screen, then black screen until it crashes, when I put like 5 records, it still doing the same with the white screen and black screen, but when adds the records, it show me a toast with all of the records, so it works... This is my SQLiteBaseAdapter :

public class ClientsSQL extends SQLiteOpenHelper {
// Declaramos la sentencia para crear la tabla
String sqlCreate = "CREATE TABLE Clients(NIF text,cognom1 text,cognom2 text,nom text)";



 public ClientsSQL(Context context, String name, SQLiteDatabase.CursorFactory factory,
                          int version) {
        super(context, name, factory, version);
        // TODO Auto-generated constructor stub
    }
    @Override
    public void onCreate(SQLiteDatabase db) {

        db.execSQL(sqlCreate);
        db.execSQL("INSERT INTO Clients VALUES ('39410028B', 'hola1', 'Hola2', 'Jonathan')");
        Log.v("INFO1", "creating db");


    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub
        // Si existe la tabla, la borramos y la volvemos a crear.
       db.execSQL("DROP TABLE IF EXISTS Clients");
        db.execSQL(sqlCreate);
    }

I created 1 insert as a test, but then on my MainActivity, I did this :

public void crearRegistres(SQLiteDatabase db){

    int i;
    String dni ="";

    for (i = 0; i < 1000; i++) {

        dni = NumeroDNI();

        db.execSQL("INSERT INTO Clients VALUES ('" + dni + "'," + "'cognomA" + i + "'," + "'cognomB" + i + "',"+ "'nom" + i + "')");
    }
    Cursor c;
    c = db.rawQuery("Select NIF from Clients", null);


    Toast.makeText(MainActivity.this, Integer.toString(c.getCount()),
            Toast.LENGTH_LONG).show();
    c.close();
}

I tried to create an Async class, but don't get it, how I call the doInBackGround() method (if it's the way to do it properly...).

EDIT1

This is what I tried, but I can't do nothing, until it finish.

  progress = ProgressDialog.show(getActivity(), "Cargando",
                        "Espere mientras cargan sus ofertas", true);
                new Thread(new Runnable() {
                    @Override
                    public void run() {
                        try {
                            Thread.sleep(2000);


                        } catch (InterruptedException e) {
                            e.printStackTrace();
                        }

                        getActivity().runOnUiThread(new Runnable() {

                            @Override
                            public void run() {
                                crearRegistres(db);
                                progress.dismiss();
                            }
                        });
                    }

                }).start();
            }

LogCat error :

 java.lang.RuntimeException: Can't create handler inside thread that has not called Looper.prepare()
            at android.os.Handler.<init>(Handler.java:200)
            at android.os.Handler.<init>(Handler.java:114)
            at android.widget.Toast$TN.<init>(Toast.java:327)
            at android.widget.Toast.<init>(Toast.java:92)
            at android.widget.Toast.makeText(Toast.java:241)
            at joancolmenero.com.practicabasedadesxmlthreads.MainActivity.NumeroDNI(MainActivity.java:111)
            at joancolmenero.com.practicabasedadesxmlthreads.MainActivity.crearRegistres(MainActivity.java:123)
            at joancolmenero.com.practicabasedadesxmlthreads.MainActivity$1.run(MainActivity.java:41)
            at java.lang.Thread.run(Thread.java:841)
03-26 11:49:20.436  21310-21310/joancolmenero.com.practicabasedadesxmlthreads E/WindowManager﹕ android.view.WindowLeaked: Activity joancolmenero.com.practicabasedadesxmlthreads.MainActivity has leaked window com.android.internal.policy.impl.PhoneWindow$DecorView{41f8f5b8 V.E..... R......D 0,0-684,324} that was originally added here
            at android.view.ViewRootImpl.<init>(ViewRootImpl.java:361)
            at android.view.WindowManagerGlobal.addView(WindowManagerGlobal.java:248)
            at android.view.WindowManagerImpl.addView(WindowManagerImpl.java:69)
            at android.app.Dialog.show(Dialog.java:286)
            at android.app.ProgressDialog.show(ProgressDialog.java:116)
            at android.app.ProgressDialog.show(ProgressDialog.java:99)
            at joancolmenero.com.practicabasedadesxmlthreads.MainActivity.onCreate(MainActivity.java:29)
            at android.app.Activity.performCreate(Activity.java:5248)
            at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1110)
            at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2173)
            at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2269)
            at android.app.ActivityThread.access$800(ActivityThread.java:139)
            at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1210)
            at android.os.Handler.dispatchMessage(Handler.java:102)
            at android.os.Looper.loop(Looper.java:136)
            at android.app.ActivityThread.main(ActivityThread.java:5102)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:515)
            at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:785)
            at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:601)
            at dalvik.system.NativeStart.main(Native Method)

EDIT2

When I run the APP it takes like 5 or 6 seconds to show my activity, it shows a white screen with anything ...

public class MainActivity extends ActionBarActivity {
Button test;
    public SQLiteDatabase db;
    public ClientsSQL Conexion;
    ProgressDialog progress;
    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);
        Conexion = new ClientsSQL(this, "Clients", null, 1);
        db = Conexion.getReadableDatabase();

        new MyAsyncTask().execute();



    public void crearRegistres(SQLiteDatabase db){

        int i;
        String dni ="";

        for (i = 0; i < 1000; i++) {

            dni = NumeroDNI();

            db.execSQL("INSERT INTO Clients VALUES ('" + dni + "'," + "'cognomA" + i + "'," + "'cognomB" + i + "',"+ "'nom" + i + "')");
        }
        Cursor c;
        c = db.rawQuery("Select NIF from Clients", null);



        c.close();
    }
    private class MyAsyncTask extends AsyncTask<Void, Void, Void> {
        private final ProgressDialog dialog = new ProgressDialog(MainActivity.this);

        @Override
        protected void onPreExecute() {

            this.dialog.setMessage("Cargando DNI en SQLite");
            this.dialog.show();
        }

        @Override
        protected Void doInBackground(Void... params) {
            db.beginTransaction();

            try {
                crearRegistres(db);

                db.setTransactionSuccessful();
            } catch (Exception ignored) {

            } finally {
                db.endTransaction();
            }
            return null;
        }


        @Override
        protected void onPostExecute(Void result) {
            super.onPostExecute(result);
            Cursor c;
            c = db.rawQuery("Select NIF from Clients where NIF", null);
            Toast.makeText(MainActivity.this, Integer.toString(c.getCount()), Toast.LENGTH_LONG).show();
            c.close();
            this.dialog.dismiss();
        }
    }

}

i am not an android developer. one suggestion- just try to create a background service ans execute the data on background. that background service doesn't affect the foreground app launching. — Rince Thomas, Mar 26 '15 at 10:30
@Signare services run on the UI thread as well, so its not enough. He needs a background thread. — Gabe Sechan, Mar 26 '15 at 10:31
YOu create a new Thread() with a Runnable that does the work of adding the data to the db. It will execute in parallel to your UI. The only catch is that you can't rely on the data being available for your app until the thread is done adding it, which will require you to either deal with possible race conditions or to prevent access to the db until loading is done. — Gabe Sechan, Mar 26 '15 at 10:34
@Signare A Service in Android doesn't run in the background though. Its a weird source of confusion for a lot of people. I know what you were thinking of, I just wasn't sure if you (and definitely wasn't sure about the OP) knew he'd have to create a Thread as well as a Service for your answer. — Gabe Sechan, Mar 26 '15 at 10:36
Why you are programmatically adding the records, just add records manually in external db, and then simple copy the db file at the first start of the app. — Hardik Trivedi, Mar 26 '15 at 10:38
You call "crearRegistres(db);" in main (UI) thread, move it outside runOnUiThread — LaurentY, Mar 26 '15 at 10:42

LaurentY · Accepted Answer · 2015-03-26T10:57:37.313

5

Use AsyncTask.

To launch a AsyncTask, method doInBackground will be called :

 new MyAsyncTask().execute();

Have a look to doc http://developer.android.com/reference/android/os/AsyncTask.html

EDIT 2:

private class MyAsyncTask extends AsyncTask<Void, Void, Void> {

        @Override
        protected void onPreExecute() {
            super.onPreExecute();
            progress = ProgressDialog.show(getActivity(), "Cargando",
                            "Espere mientras cargan sus ofertas", true);
        }
        @Override
        protected Void doInBackground(Void... params) {
            crearRegistres(db);
            return null;
        }
        @Override
        protected void onPostExecute(Void result) {
            super.onPostExecute(result);
            progress.dismiss();
        }
    }

edited Mar 26 '15 at 10:57

answered Mar 26 '15 at 10:33

LaurentY

7,495
3
37
55

But I've to create another class as I mentioned? – Skizo-ozᴉʞS ツ Mar 26 '15 at 10:34
Yes you have to create another class, but it could be an inner class: http://www.javatpoint.com/java-inner-class – LaurentY Mar 26 '15 at 10:36
See my edit, what I've tried so far – Skizo-ozᴉʞS ツ Mar 26 '15 at 10:37
I've posted the LogCatERror – Skizo-ozᴉʞS ツ Mar 26 '15 at 10:50
I edit my answer, add this asynctask in your class (activity), and called it as explained – LaurentY Mar 26 '15 at 10:58
This crearRegistres(db) inside this class? I mean the function – Skizo-ozᴉʞS ツ Mar 26 '15 at 11:07
I've to created all of my functions inside of this new class? – Skizo-ozᴉʞS ツ Mar 26 '15 at 11:13
See my edit2 please, and tell me if i'm doing something wrong, because it takes too long... – Skizo-ozᴉʞS ツ Mar 26 '15 at 12:36
@Skizo What do you have in your onResume or onStart methods ? – LaurentY Mar 26 '15 at 13:29
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/73857/discussion-between-skizo-and-laurenty). – Skizo-ozᴉʞS ツ Mar 26 '15 at 14:09

score 3 · Answer 2 · edited May 23 '17 at 12:01

3

Use a Transaction to insert your data. I went from 220 seconds to insert 16k rows to 8 seconds by just putting it in a Transaction.

This answer seems good enough.

I'd also suggest Futuroid (or similar async libraries) to perform async operations.

A quick transaction example:

try {
  db.beginTransaction();
  crearRegistres(db); 
  db.setTransactionSuccessful();
} catch (Exception ignored) { }
finally { db.endTransaction(); }

And one using Futuroid:

public Future<Boolean> insertRows(final SqliteDatabase db) {
    return Async.submit(new Callable<Boolean>() {
        @Override
        public Boolean call() {
            try {
                db.beginTransaction();
                crearRegistres(db);
                db.setTransactionSuccessful();
            } catch (Exception ignored) {
            } finally {
                db.endTransaction();
                return true;
            }
        }
    });
}

This is how you call it from your activity;

 insertRows(db).addCallback(new FutureCallback<Boolean>() {
                                   @Override
                                   public void onSuccess(Boolean boolean) {
                                       Log.i("SQLITE", "Data inserted");
                                   }

                                   @Override
                                   public void onFailure(Throwable t) {
                                       Log.e("SQLITE", "Unable to insert data ", t);
                                   }
                               }

    );

edited May 23 '17 at 12:01

Community

1
1

answered Mar 26 '15 at 10:49

David Corsalini

7,958
8
41
66

It does a background function, so I can interact with the APP while data is introducing on SQLite? – Skizo-ozᴉʞS ツ Mar 26 '15 at 10:54
Using Futuroid yes, it does. When the operation is finished it will call onSuccess (or onFailure if something has gone wrong), so you will know the job is finished. But I strongly advise you ALSO use the transaction, very strongly. – David Corsalini Mar 26 '15 at 11:12
It will be something like this : try { crearRegistres(db); db.setTransactionSuccessful(); } catch (Exception ignored) { } finally { db.endTransaction(); } ? – Skizo-ozᴉʞS ツ Mar 26 '15 at 11:20
Can I implement your answer with @LaurentY answer? So I can put the data faster? – Skizo-ozᴉʞS ツ Mar 26 '15 at 12:17
No, they do the same job in 2 different ways. AsyncTask is the original way of doing async work, but it is becoming obsolete. If you want to use the transaction in an AsyncTask, just copy the first block of code in the doInBackground(). – David Corsalini Mar 26 '15 at 12:27
See my edit2 please, and tell me if i'm doing something wrong, because it takes too long... – Skizo-ozᴉʞS ツ Mar 26 '15 at 12:36
onPostExecute() runs on the UI thread. Run the query "Select NIF from Clients where NIF" in onBackground() and return c.getCount(). Then in onPostExecute(Integer count) you can use that count to do whatever you want. – David Corsalini Mar 26 '15 at 14:54

score 2 · Answer 3 · answered Mar 26 '15 at 10:32

2

You need to do this on a background Thread. An AsyncTask would also work, but if you have any other AsyncTasks going on I'd avoid it, as tasks are done round robin on one thread and this could hold it up significantly.

answered Mar 26 '15 at 10:32

Gabe Sechan

90,003
9
87
127

Android - How to handle 1000 records SQLite

EDIT1

EDIT2

3 Answers3