title: SQLite Room Fast Insert JSON Array
body: <p>I'm creating a vocabulary app that loads words/definitions from a JSON file into a JSON array. Then I loop through the JSON array to load the data into a Room Database.</p>
  
  <p>Based on the Log statements, the JSON array loads very quickly (probably at most 1-2 seconds) but the Room database takes much long to load in an asynchronous thread (around 40 seconds).</p>
  
  <p>My MainActivity class tries the load a word from the database into the layout in onCreate but obviously the app crashes since the database hasn't finished loading yet.</p>
  
  <p>What's the recommended way to solve this? FYI, the database is intended to only need to be created once and will only be read from after the first load.</p>
  
  <p>My thoughts For Possible Solutions:</p>
  
  <p>1) Use the JSONArray data the first time a user opens the app and then use the database after</p>
  
  <p>2) Add a copy of the datafile file to the assets folder and access it from there (not sure how this would work with Room)</p>
  
  <p>3) Maybe my code is inefficient and there's a faster way to load the Room DB?</p>
  
  <pre><code>private String fileName = "majortests_words.json";
  private JSONArray wordBankAry;
  Word currentWord = new Word();
  int totalWords = 1000;
  Random rand = new Random();
  int currentWordIndex = 0;
  
  protected void onCreate(Bundle savedInstanceState) {
      super.onCreate(savedInstanceState);
      setContentView(R.layout.activity_main);
  
      deleteDatabase("word-database");
      loadWords();
      DatabaseInitializer.populateAsync(AppDatabase.getAppDatabase(this), wordBankAry);
  
      currentWordIndex = rand.nextInt(totalWords);
      currentWord = AppDatabase.getAppDatabase(this).wordDao().getWord(currentWordIndex);
  
      // code to add attributes of currentWord to the layout
  
  public String loadJSONData() {
      String jsonStr = null;
      try {
          InputStream iStream = getAssets().open(fileName);
          int size = iStream.available();
          byte[] buffer = new byte[size];
          iStream.read(buffer);
          iStream.close();
  
          jsonStr = new String(buffer, "UTF-8");
      } catch (IOException e) {
          e.printStackTrace();
          return null;
      }
      return jsonStr;
  }
  
  public void loadWords() {
      try {
          String jsonStr = loadJSONData();
          JSONObject jsonObj = new JSONObject(jsonStr);
          wordBankAry = jsonObj.getJSONArray("wordBank");
          totalWords = wordBankAry.length();
          Log.d("MainActivity", "JSON Count:" + wordBankAry.length());
      } catch (JSONException e) {
          e.printStackTrace();
      }
  }
  </code></pre>
  
  <p>.</p>
  
  <pre><code>@Database(entities = {Word.class}, version = 1)
  public abstract class AppDatabase extends RoomDatabase {
      private static AppDatabase INSTANCE;
      static String DB_NAME = "word-database";
  
      public abstract WordDao wordDao();
  
      public static AppDatabase getAppDatabase(Context context) {
          if (INSTANCE == null) {
              INSTANCE = Room.databaseBuilder(context.getApplicationContext(), AppDatabase.class, DB_NAME).allowMainThreadQueries().build();
      }
      return INSTANCE;
  }
  
      public static void destroyInstance() {
          INSTANCE = null;
      }
  }
  </code></pre>
  
  <p>.</p>
  
  <pre><code>@Dao
  public interface WordDao {
      @Insert
      public void insertWords(Word word);
  
      @Query("SELECT * FROM words")
      List&lt;Word&gt; getAll();
  
      @Query("SELECT * FROM words WHERE id = :id")
      public Word getWord(int id);
  }
  </code></pre>
  
  <p>.</p>
  
  <pre><code>public class DatabaseInitializer {
  
      private static final String TAG = DatabaseInitializer.class.getName();
  
      public static void populateAsync(@NonNull final AppDatabase db, JSONArray wordBankAry) {
          PopulateDbAsync task = new PopulateDbAsync(db, wordBankAry);
          task.execute();
      }
  
      public static Word addWord(final AppDatabase db, Word word) {
          db.wordDao().insertWords(word);
          return word;
      }
  
      private static void populateWordBank(AppDatabase db, JSONArray wordBankAry) {
          Word word = new Word();
          try {
              for (int wordIndex = 0; wordIndex &lt; wordBankAry.length(); wordIndex++) {
                  JSONObject jsonObj = wordBankAry.getJSONObject(wordIndex);
                  word.setWordName(jsonObj.getString("word"));
                  word.setWordDefinition(jsonObj.getString("definition"));
  
                  addWord(db, word);
              }
          } catch (JSONException e) {
              e.printStackTrace();
          }
  
          List&lt;Word&gt; wordList = db.wordDao().getAll();
          Log.d(DatabaseInitializer.TAG, "Rows Count:" + wordList.size());
      }
  
      private static class PopulateDbAsync extends AsyncTask&lt;Void, Void, Void&gt; {
  
          private final AppDatabase mDb;
  
          private JSONArray mWordBankAry;
  
          PopulateDbAsync(AppDatabase db, JSONArray wordBankAry) {
              mDb = db;
              mWordBankAry = wordBankAry;
          }
  
          @Override
          protected Void doInBackground(final Void... params) {
              populateWordBank(mDb, mWordBankAry);
              return null;
          }
      }
  }
  </code></pre>
  
  <p>.</p>
  
  <pre><code>@Entity(tableName = "words")
  public class Word {
      @PrimaryKey(autoGenerate = true)
      private int id;
  
      @ColumnInfo(name = "word_name")
      private String wordName;
  
      @ColumnInfo(name = "word_definition")
      private String wordDefinition;
  
      public int getId() {
          return id;
      }
  
      public void setId(int id) {
          this.id = id;
      }
  
      public String getWordName() {
          return wordName;
      }
  
      public void setWordName(String wordName) {
          this.wordName = wordName;
      }
  
      public String getWordDefinition() {
          return wordDefinition;
      }
  
      public void setWordDefinition(String wordDefinition) {
          this.wordDefinition = wordDefinition;
      }
  }
  </code></pre>