Android insert data in Db from file which have multiple lines for each Insert query

Question

I want to pre-populate my database. So I generate SQL queries from browser and write code to insert them in db. It is working fine for the single line query but most insert queries have multiple line for example `

INSERT INTO `poem` VALUES (780,'سلام القلب واشواقه 

من قلب يحب مجنونه 

ياليت هالقلب يحن 


ويقول هالدنيا 

ماتسوى دون *_*','0',NULL);`

My requirement is to insert them as they are. That is the method i wrote for multiple line (works perfect for single line query)

public static int countLines(InputStream is) throws IOException {
    try {
        byte[] c = new byte[1024];
        int count = 0;
        int readChars = 0;
        boolean empty = true;
        int index = 0;

        while ((readChars = is.read(c)) != -1) {
            empty = false;
            String temp = new String(c);
            for (int i = 0; i < readChars; i++) {

                if (c[i] == ';' && index == 0) {
                    index++;
                } else if (c[i] == '\n' && index == 1) {
                    index++;
                } else if (c[i] == '\t' && index == 2) {
                    index++;
                } else if (c[i] == 'I' && index == 3) {
                    count++;
                    index = 0;
                } else {
                    i -= index;
                    index = 0;
                }
            }
        }
        return (count == 0 && !empty) ? 1 : count + 1;
    } finally {
        is.close();
    }
}

Anyone have idea how to insert such queries from file to DB.? Any help would be great. Thanks

Why don't you just [ship the DB with the app](http://stackoverflow.com/questions/513084/how-to-ship-an-android-application-with-a-database)? — CL., Apr 02 '16 at 16:08

score 0 · Answer 1 · answered Apr 02 '16 at 23:49

0

What you are asking is an ugly way of doing things. Use sqliteassethelper to create prepopulated databases and tables. It works extremely similar to SQLiteOpenHelper so it is very easy to use.

answered Apr 02 '16 at 23:49

suku

10,507
16
75
120

not very much ugly. But it is requirement. The client want it that way. Meanwhile thank you for your help I have solve me problem. – Nouman Ghaffar Apr 02 '16 at 23:54
If you have already solved it, then paste the solution here. It may help somebody else – suku Apr 02 '16 at 23:59

score 0 · Accepted Answer · edited May 28 '16 at 19:18

Sorry for late response in case of anyone having same issue

Here is the solution step by step

I create a .sql file which contain insert queries (some Include multiline queries) ends with :.

In my onCreate() method of SQLiteOpenHelper I m counting lines of my file using getFileLineCount(). This method return me the count of line. This line count is never used , but I had it because if all your queries are single line, it might comes into play. You can skip this method.

 public void onCreate(SQLiteDatabase db) {
             db.execSQL("CREATE TABLE \"" + TABLE_POEM + "\" (\"" + POEM_LOCAL_ID + "\" INTEGER PRIMARY KEY AUTOINCREMENT , \"" + POEM_TEXT + "\" TEXT,\"" + POEM_ID + "\" INTEGER , \"" + POEM_IS_FAV + "\" TEXT);");

            int totalLineCount = getFileLineCount("poem.sql");
            int insertCount = insertFromFile(db, "poem.sql", totalLineCount);

            Log.d("DatabaseHelper", "------------onCreate(SQLiteDatabase db) >>>>>>> completed--------");
        }
    private int getFileLineCount(String assetFilePath) {
        InputStream insertStream = null;
        int totalCount = 0;
        try {
            insertStream = context.getAssets().open(assetFilePath);
            totalCount = FileUtil.countLines(new BufferedInputStream(insertStream));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (insertStream != null) {
                try {
                    insertStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return totalCount;
    }

By using this method I insert queries from file. It handles both single line queries and multi line queries. First I check if line contain ";" it means the line is ended else not ended. Pretty simple it was at the end.

while ((currentLine = insertReader.readLine()) != null) {
                            if (currentLine.length() != 0) {
                                if (currentLine.charAt(currentLine.length() - 1) == ';') {
                                    insertInDb(db, insertStr + "\n" + currentLine);
                                    insertStr = "";
                                } else {
                                    insertStr += currentLine;
                                }
                            }
                        }




 private int insertFromFile(SQLiteDatabase db, String assetFilePath, int totalCount) {
            int result = 0;
            BufferedReader insertReader = null;
            try {
                InputStream insertStream = context.getAssets().open(assetFilePath);
                insertReader = new BufferedReader(new InputStreamReader(insertStream));

                db.beginTransaction();

                while (insertReader.ready()) {

                    // String insertStr = insertReader.toString();

                    String insertStr = "";
                    String currentLine;
                    while ((currentLine = insertReader.readLine()) != null) {
                        if (currentLine.length() != 0) {
                            if (currentLine.charAt(currentLine.length() - 1) == ';') {
                                insertInDb(db, insertStr + "\n" + currentLine);
                                insertStr = "";
                            } else {
                                insertStr += currentLine;
                            }
                        }
                    }

                }
                db.setTransactionSuccessful();

            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                if (insertReader != null) {
                    try {
                        insertReader.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
                db.endTransaction();
            }

            return result;
        }

        private void insertInDb(SQLiteDatabase db, String assetFilePath) throws IOException {
            if (assetFilePath != null && assetFilePath.length() > 0) {
                SQLiteStatement statement = db.compileStatement(assetFilePath);
                statement.execute();

            }

        }

I used this technique to insert 4000 records in db. And it was working fine without much of the lag. If anyone have any batter solution for it. Kindly post it.

Android insert data in Db from file which have multiple lines for each Insert query

2 Answers2