Search code examples
androidandroid-sqlitesqliteopenhelper

Using SQLiteOpenHelper to run statements from SQL

When I try to insert short portions of my data by copying over the relevant SQL statements into a String variable containing the SQL statements that SQLiteOpenHelper must run, everything works fine. Here is my query:

String POPULATE_TABLE =
            "INSERT INTO `en_sahih` (`indexID`, `chapterNo`, `verseNo`, `verseText`) VALUES\n" +
            "(1, 1, 1, 'This is verse 1'),\n" +
            "(2, 1, 2, 'This is verse 2'),\n" +
            "(3, 1, 3, 'This is verse 3'),\n" +
            "(4, 1, 4, 'This is verse 4'),\n" +
            "(5, 1, 5, 'This is verse 5'),\n" +
            "(6, 1, 6, 'This is verse 6'),\n" +
            "(7, 1, 7, 'This is verse 7');";
    db.execSQL(POPULATE_TABLE);

But when I copy over the entire 6k records into the string, which is probably not the ideal way to go about this, Android studio tells me the String is too long. How can I easily take the data that I already have in the form of SQL statements and put it into an .sql file and execute them in the new database that I've set up in my app. Any suggestions on how to do this?

Solution

    1. save records in text file (by delimiting each record with crlf) 

       // records.txt
 1, 1, 1, 'This is verse 1'
 2, 1, 2, 'This is verse 2'

    2. then you can read records from file using some Reader implementation (example BufferedReader)

      file -> file input stream -> reader

      How can I read a text file in Android?

       // create the file class object pointing to text file 
 File file = new File(some_path,"records.txt");

 // initialize reader class object providing file object
 BufferedReader br = new BufferedReader(new FileReader(file));

    3. process data using while loop -> Reader.readline() method

       while ((line = Reader.readLine()) != null) {

     // a/ do insert 
     // b/ store data in some prim array 
     //    or via some list implementation (`ArrayList<String>`) 
     // then use insert or bulk

 }

    I love the comprehensiveness of this answer @ceph3us Thanks a lot. However, there are two things: 1. doing 6k inserts using this approach might slow things down, how do I deal with that? and 2. Since I will be inserting using multiple copies of this file (different languages), having text files as well as the database itself is going to create redundancy and double the mount of space my app takes up on a device. Is it possible to programmatically delete a text file once I'm done with the inserts?

    1. for first pleas read:

      inserting of 6k records could take few seconds :)

      see https://www.sqlite.org/faq.html#q19

    2. as for second use 

       File.delete();

      or 

      RandomAccessFile.setLength(0);

    from author :

    does your application uses network communication ??

    if yes - consider to use some web hosting account or vps as external source for your app data - then the distributed size will be small. - you also can keep data not outdated (some sort of updates are done on server side - in your case records to insert - you reduce the need to make an application update on client side) - basicaly move to server/client application