Creating android app Database with big amount of data

Question

Hi all,

The database of my application need to be filled with a lot of data, so during onCreate(), it's not only some create table sql instructions, there is a lot of inserts. The solution I chose is to store all this instructions in a sql file located in res/raw and which is loaded with Resources.openRawResource(id).

It works well but I face to encoding issue, I have some accentuated caharacters in the sql file which appears bad in my application. This my code to do this :

public String getFileContent(Resources resources, int rawId) throws
IOException
  {
    InputStream is = resources.openRawResource(rawId);
    int size = is.available();
    // Read the entire asset into a local byte buffer.
    byte[] buffer = new byte[size];
    is.read(buffer);
    is.close();
    // Convert the buffer into a string.
    return new String(buffer);
  }

public void onCreate(SQLiteDatabase db) {
   try {
        // get file content
        String sqlCode = getFileContent(mCtx.getResources(), R.raw.db_create);
        // execute code
        for (String sqlStatements : sqlCode.split(";"))
        {
            db.execSQL(sqlStatements);
        }

        Log.v("Creating database done.");
        } catch (IOException e) {
            // Should never happen!
            Log.e("Error reading sql file " + e.getMessage(), e);
            throw new RuntimeException(e);
        } catch (SQLException e) {
            Log.e("Error executing sql code " + e.getMessage(), e);
            throw new RuntimeException(e);
        }

The solution I found to avoid this is to load the sql instructions from a huge static final string instead of a file, and all accentutated characters appears well.

But Isn't there a more elegant way to load sql instructions than a big static final String attribute with all sql instructions ?

Thanks in advance

Thomas

Answer 1

The SQL file solution seems perfect, it's just that you need to make sure that the file is saved in utf8 encoding otherwise all the accentuated characters will be lost. If you don't want to change the file's encoding then you need to pass an extra argument to new String(bytes, charset) defining the file's encoding.

Do prefer to use file resources instead of static final String to avoid having all those unnecessary bytes loaded into memory. In mobile phones you want to save all memory possible!

Answer 2

I think your problem is in this line:

return new String(buffer);

You're converting the array of bytes in to a java.lang.String but you're not telling Java/Android the encoding to use. So the bytes for your accented characters aren't being converted correctly as the wrong encoding is being used.

If you use the String(byte[],<encoding>) constructor you can specify the encoding your file has and your characters will be converted correctly.

Answer 3

Hello good morning, i was having the same issues about loading "bulk data". Thanks for the solution, but its not working can someone help me out please?

Here is my problem :

How to access the class in order to create the database and load the sql file?

What i am doing is to have a separate class for the DB and i am calling it in an activity.

here is my code :

public class sdfdsf extends Activity {

private SQLiteDatabase db;

/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);
    DB myDbHelper = new DB(this);
    myDbHelper.createDB(db); 

} }

and the error i am getting is here :

Java.null pointer

Log.v("sqlCode",sqlCode); // execute code for (String sqlStatements : sqlCode.split(";")) { db.execSQL(sqlStatements); }

Any help will be really very appreciated. :)

Answer 4

It looks like you are passing all your sql statements in one string. That's a problem because execSQL expects "a single statement that is not a query" (see documentation [here][1]). Following is a somewhat-ugly-but-working solution.

I have all my sql statements in a file like this:

INSERT INTO table1 VALUES (1, 2, 3);

INSERT INTO table1 VALUES (4, 5, 6);

INSERT INTO table1 VALUES (7, 8, 9);

Notice the new lines in between text(semicolon followed by 2 new lines) Then, I do this:

String text = new String(buffer, "UTF-8");
for (String command : text.split(";\n\n")) { 
   try { command = command.trim(); 
   //Log.d(TAG, "command: " + command); 
   if (command.length() > 0) 
      db.execSQL(command.trim()); 
}
catch(Exception e) {do whatever you need here}

My data columns contain blobs of text with new lines AND semicolons, so I had to find a different command-separator. Just be sure to get creative with the split str: use something you know doesn't exist in your data.

HTH Gerardo

[1]: http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html#execSQL(java.lang.String, java.lang.Object[])