Creating a database of Android applications with a large amount of data

Question

My application database should be filled with a lot of data, so during onCreate() it not only creates a table of sql instructions, there are many inserts. The solution I chose is to save all of these instructions in a sql file located in res / raw and which is loaded using Resources.openRawResource(id) .

This works well, but I am facing a coding problem, I have some accented caharacters in the sql file, which seems bad in my application. This is my code for 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 sql statements from a huge static final String instead of a file, and all accented characters display well.

But is there a more elegant way to load SQL commands than a large static final String with all sql statements?

Answer 1

I think your problem is in this line:

 return new String(buffer); 

You are converting an array of bytes to java.lang.String but not using Java / Android for encoding. Thus, bytes for your accented characters are not converted correctly, because the wrong encoding is used.

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

Answer 2

The SQL file solution seems perfect, just make sure that the file is saved in utf8 encoding, otherwise all selected characters will be lost. If you do not want to change the encoding of the file, you need to pass an additional argument to the new String(bytes, charset) that defines the encoding of the file.

Prefer to use file resources instead of static ending lines to avoid loading all extra bytes into memory. In mobile phones you want to save all the memory!

Answer 3

I use a different approach: Instead of making a lot of sql queries (which will take a long time to complete), I create my sqlite database on the desktop, put it in the resources folder, create an empty sqlite-db in android and copy the db from Asset folders to the database folder. This is a huge increase in speed. Please note: you need to create an empty database first in android, and then you can copy and overwrite it. Otherwise, Android will not allow you to write db to the datbase folder. There are several examples on the Internet. BTW, this approach seems to work best if db doesn't have a file extension.

Answer 4

It looks like you are passing all your sql statements on one line. This is a problem because execSQL expects a "single statement that is not a query" (see Documentation [here] [1]). Below is a somewhat ugly but working solution.

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

INSERT TABLE 1 VALUES (1, 2, 3);

INSERT TABLE 1 VALUES (4, 5, 6);

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

Note the new lines between the text (semicolon and two 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 droplets of text with new lines and a semicolon, so I had to find another command separator. Just make sure you are guided by split str: use what you know does not exist in your data.

NTN Gerardo

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