Sqlite批量插入大数据的例子

作者:袖梨 2022-06-29

这两天被数据库插入批量数据折磨的有点抓狂还好找到了解决方法,话不多说,直接看下面两部分代码:

SQLiteDatabase db = dbHelper.getWritableDatabase();
//         LogUtils.i("开始解析*****************"+ new SimpleDateFormat("yyyy-MM-dd  HH:mm:ss").format(new Date()));
        for (int i = 0; i < messageArray.length(); i++) {
           try {
            JSONObject messageObject = (JSONObject) messageArray.get(i);
            int stamp = JSONUtils.getInt(messageObject, "nTime", 0);
                        String dbLon = JSONUtils.getString(messageObject, "dbLon", "");
            String dbLat = JSONUtils.getString(messageObject, "dbLat", "");
             /******************** 数据库事务开始 **************************/
                db.beginTransaction();
            db.execSQL("insert into waybaby(stamp,lontude,lantude) values(?,?,?)",new Object[] { stamp, dbLon,dbLat });
                        db.setTransactionSuccessful();
                        db.endTransaction();
             /******************** 数据库事务结束 **************************/
            tranceList.add(Double.parseDouble(dbLat));
            tranceList.add(Double.parseDouble(dbLon));
            LatLng latLng = new LatLng(Double.parseDouble(dbLat), Double.parseDouble(dbLon));
            latLngline.add(latLng);
            } catch (JSONException e) {
                e.printStackTrace();
              }
            }
              dismissProgressDialog();
//            LogUtils.i("解析结束*****************"+ new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
                      // 画路线轨迹
              onMapLoaded();

这段代码是用来解析数据的,然后插入到本地数据库里面,总共:1000 条数据,用时:10秒~~15秒。十五秒的等待,花儿都谢了。请看下面修改后的代码:


SQLiteDatabase db = dbHelper.getWritableDatabase();
//      LogUtils.i("开始解析*****************"+ new SimpleDateFormat("yyyy-MM-dd  HH:mm:ss").format(new Date()));
          for (int i = 0; i < messageArray.length(); i++) {
            try {
                 JSONObject messageObject = (JSONObject) messageArray.get(i);
                 int stamp = JSONUtils.getInt(messageObject, "nTime", 0);
                 String dbLon = JSONUtils.getString(messageObject, "dbLon", "");
                 String dbLat = JSONUtils.getString(messageObject, "dbLat", "");
                 LocusEntity locusEntity = new LocusEntity(stamp, dbLon, dbLat);
                 locusEntities.add(locusEntity);
                 tranceList.add(Double.parseDouble(dbLat));
                 tranceList.add(Double.parseDouble(dbLon));
                 LatLng latLng = new LatLng(Double.parseDouble(dbLat), Double.parseDouble(dbLon));
                 latLngline.add(latLng);
               } catch (JSONException e) {
                  e.printStackTrace();
              }
            }
//         LogUtils.i("开始插入数据*****************"+ new SimpleDateFormat("yyyy-MM-dd  HH:mm:ss").format(new Date()));
           db.beginTransaction();
           String sql = "insert into waybaby(stamp,lontude,lantude) values(?,?,?)";
            for (LocusEntity locusEntity : locusEntities) {
            SQLiteStatement stat = db.compileStatement(sql);
            stat.bindLong(1, locusEntity.getStamp());
            stat.bindString(2, locusEntity.getLontude());
            stat.bindString(3, locusEntity.getLantude());
            stat.executeInsert();
             }
               db.setTransactionSuccessful();
               db.endTransaction();
              db.close();
//            LogUtils.i("插入数据完毕*****************"+ new SimpleDateFormat("yyyy-MM-dd  HH:mm:ss").format(new Date()));
              dismissProgressDialog();
//            LogUtils.i("解析结束*****************"+ new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
                       // 画路线轨迹
              onMapLoaded();

细心的你,肯定会发现,插入数据的操作,被拿到了for循环的外面,同时插入数据库的操作也改变了,多了一大段代码,从一行变成了十几行,但是效率提高的不是一点两点。这段代码执行的时间: 不到 1 秒,打印的时间信息,你会发现,时间不用1秒 就获取成功了。我打印的时间就不到 1 秒。比之前的效率快的将近十倍,你敢信? 事实就是如此。至于那段代码是什么意思。请自行百度,自己学习。

相关文章

精彩推荐