sqlite - BlackBerry OS 5.0 SQLite 内存不足

Question

有时我无法将数据插入我的数据库。我收到了 out of memory即使 db 文件为空，也会出现异常。

以下是我的代码，请看一下。在此先感谢您的任何建议。

public class ThreeptalkDAO {

private static ThreeptalkDAO service;
private static Database database;

public static ThreeptalkDAO getInstance() throws CustomException {
    if (service == null) {
        service = new ThreeptalkDAO();
        URI uri;
        try {
            uri = URI
                    .create("file:///SDCard/threeptalk/db/threeptalkdao.db");
            database = DatabaseFactory.openOrCreate(uri);
            // createMessagesTable();

        } catch (Exception e) {
            System.out.println("exp is:" + e.getMessage());
            Dialog.alert(e + e.getMessage());

            throw new CustomException(e.getMessage());

        } // here path is like
            // 'file:///SDCard/Databases/mytestApp/testdatabase.db'

    }
    return service;
}

public void createMessagesTable() throws CustomException {
    try {
        Statement statement = database
                .createStatement("CREATE TABLE IF NOT EXISTS messages(message_id INTEGER PRIMARY KEY,message,status,message_type_id,response,date_added,sender,office,sent_on,phoneno)");
        statement.prepare();
        statement.execute();
        statement.close();
    } catch (DatabaseException e) {
        // TODO Auto-generated catch block
        throw new CustomException(e.getMessage());
    }

}

public void insertMessages(Vector messages) throws CustomException {
    for (int i = 0; i < messages.size(); i++) {
        Messages m = (Messages) messages.elementAt(i);
        try {
            Statement statement = database
                    .createStatement("INSERT INTO messages (message_id,message,status,message_type_id, response,date_added,sender,office,sent_on,phoneno) VALUES ("
                            + m.getMessageID()
                            + ",'"
                            + m.getMessage()
                            + "','"
                            + "unread"
                            + "','"
                            + m.getMessageType()
                            + "','"
                            + "0"
                            + "','"
                            + m.getMdate()
                            + "','"
                            + m.getSender()
                            + "','"
                            + m.getOffice()
                            + "','"
                            + m.getSentOn()
                            + "','"
                            + WebserviceFactory.getInstance().getPhoneNo()
                            + "')");

            statement.prepare();
            statement.execute();
            statement.close();
        } catch (DatabaseException e) {
            // TODO Auto-generated catch block
            throw new CustomException(e.getMessage());
        }
    }
}

Answer 1

在 BlackBerry OS 5.0 中，Sqlite 的可用内存非常有限 - 512kb。这意味着构建大量语句或插入大量数据可以轻松消耗 Sqlite 可用的所有内存，即使设备报告大量可用内存也是如此。
看起来您正在自己构建插入语句字符串，并为每个新行执行此操作。通过使用准备好的语句，然后将值绑定(bind)到语句中，您可以节省大量内存。这还具有避免有意或无意的 sqlite 注入(inject)问题的优点。

不要直接输入字符串值，而是在循环外创建语句，然后在循环内重置并绑定(bind)相同的语句对象:

Statement statement = database.createStatement(
    "INSERT INTO messages (message_id,message,status,message_type_id,"+
    "response,date_added,sender,office,sent_on,phoneno) VALUES (?,?,?,?,?,?,?,?,?,?)");
statement.prepare();
for (int i = 0; i < messages.size(); i++) {
    Messages m = (Messages) messages.elementAt(i);    
    statement.reset();
    statement.bind(1, m.getMessageId());
    statement.bind(2, m.getMessage());
    // and so on, for all the arguments
    statement.execute();
}