blocks|key|3233132|text|我不认为有任何可行的方法来完成你清单中的第三或第四。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3233133|在其他解决方案中，您列出了两个数据文件包含直接SQL的解决方案，另一个包含非SQL格式的数据。|3233134|这三种方法都可以很好地工作，但后一种建议从格式化文件中获取数据并自己构建SQL似乎是最干净的。如果以后添加了批量更新功能，那么您的数据文件仍然可以使用，或者至少可以很容易地处理为可用的形式。此外，数据文件的创建更简单，更不容易出错。最后，拥有“原始”数据将允许导入到其他数据存储格式。|3233135|在任何情况下，您都应该(正如您所提到的)将插入组包装到事务中，以避免创建每行事务日志。|3233136|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|J|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|K|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|L|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|M|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|N|8|@]|9|@]|A|$]]]|I|$]]

I don't believe there is any feasible way to accomplish #3 or #4 on your list. 

Of the other solutions you list two that have the datafile contain direct SQL, and the other has the data in a non-SQL format.

All three would work just fine, but the latter suggestion of grabbing the data from a formatted file and building the SQL yourself seems the cleanest. If true batch update capability is added at a later date your datafile is still usable, or at least easily processable into a usable form. Also, creation of the datafile is more straightforward and less error prone. Finally, having the "raw" data would allow import into other data-store formats.

In any case, you should (as you mentioned) wrap the groups of inserts into transactions to avoid the per-row transaction journal creation.

blocks|key|3018865|text|我发现对于批量插入，(显然很少使用的)+DatabaseUtils.InsertHelper类比使用SQLiteDatabase.insert快几倍。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3018866|另外两个优化也对我的应用程序的性能有所帮助，尽管它们可能并不适用于所有情况：|3018867|暂时关闭数据库的内部锁定对performance.也有帮助：|3018868|不要锁定为空或空的值您可以确信这样做是安全的(+bind+)。|unordered-list-item|3018869|3018870|我有一个包含更多细节的blog+post。|3018871|entityMap|0|LINK|mutability|MUTABLE|url|http://developer.android.com/reference/android/database/DatabaseUtils.InsertHelper.html|1|http://www.outofwhatbox.com/blog/2010/12/android-using-databaseutils-inserthelper-for-faster-insertions-into-sqlite-database/^0|1E|L|K|Q|0|0|0|0|O|4|0|0|B|9|1|0^^$0|@$1|2|3|4|5|6|7|Z|8|@$9|10|A|11|B|C]]|D|@$9|12|A|13|1|14]]|E|$]]|$1|F|3|G|5|6|7|15|8|@]|D|@]|E|$]]|$1|H|3|I|5|6|7|16|8|@]|D|@]|E|$]]|$1|J|3|K|5|L|7|17|8|@$9|18|A|19|B|C]]|D|@]|E|$]]|$1|M|3|-4|5|6|7|1A|8|@]|D|@]|E|$]]|$1|N|3|O|5|6|7|1B|8|@]|D|@$9|1C|A|1D|1|1E]]|E|$]]|$1|P|3|-4|5|6|7|1F|8|@]|D|@]|E|$]]]|Q|$R|$5|S|T|U|E|$V|W]]|X|$5|S|T|U|E|$V|Y]]]]

I've found that for bulk insertions, the (apparently little-used) <a href="http://developer.android.com/reference/android/database/DatabaseUtils.InsertHelper.html" rel="noreferrer">DatabaseUtils.InsertHelper</a> class is several times faster than using <code>SQLiteDatabase.insert</code>.

Two other optimizations also helped with my app's performance, though they may not be appropriate in all cases:

<ul>
<li>Don't <code>bind</code> values that are empty or <code>null</code>.</li>
<li>If you can be certain that it's safe to do it, temporarily turning off the database's internal locking can also help performance.</li>
</ul>

I have a <a href="http://www.outofwhatbox.com/blog/2010/12/android-using-databaseutils-inserthelper-for-faster-insertions-into-sqlite-database/" rel="noreferrer">blog post</a> with more details.

blocks|key|297956|text|通常，每次使用db.insert()时，SQLite都会创建一个事务(以及文件系统中的结果日志文件)，这会降低速度。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|297957|如果使用db.beginTransaction()和db.endTransaction()，SQLite只会在文件系统上创建一个日志文件，然后同时提交所有插入操作，从而大大提高速度。|297958|下面是Batch+insert+to+SQLite+database+on+Android中的一些伪代码：|297959|try
{
++db.beginTransaction();

++for+each+record+in+the+list
++{
++++do_some_processing();

++++if+(line+represent+a+valid+entry)
++++{
++++++db.insert(SOME_TABLE,+null,+SOME_VALUE);
++++}

++++some_other_processing();
++}

++db.setTransactionSuccessful();
}
catch+(SQLException+e)+{}
finally
{
++db.endTransaction();
}|code-block|syntax|javascript|297960|如果您希望由于意外错误或其他原因而中止事务，只需在不首先将事务设置为成功(db.setTransactionSuccessful())的情况下执行db.endTransaction()。|297961|另一种有用的方法是使用db.inTransaction()+(返回true或false)来确定您当前是否正在处理事务。|297962|Documentation+here|297963|entityMap|0|LINK|mutability|MUTABLE|url|http://notes.theorbis.net/2010/02/batch-insert-to-sqlite-on-android.html|1|http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html^0|7|B|0|4|L|Q|J|0|3|16|0|0|0|11|T|21|J|0|B|I|X|4|12|5|0|0|I|1|0^^$0|@$1|2|3|4|5|6|7|14|8|@$9|15|A|16|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|17|8|@$9|18|A|19|B|C]|$9|1A|A|1B|B|C]]|D|@]|E|$]]|$1|H|3|I|5|6|7|1C|8|@]|D|@$9|1D|A|1E|1|1F]]|E|$]]|$1|J|3|K|5|L|7|1G|8|@]|D|@]|E|$M|N]]|$1|O|3|P|5|6|7|1H|8|@$9|1I|A|1J|B|C]|$9|1K|A|1L|B|C]]|D|@]|E|$]]|$1|Q|3|R|5|6|7|1M|8|@$9|1N|A|1O|B|C]|$9|1P|A|1Q|B|C]|$9|1R|A|1S|B|C]]|D|@]|E|$]]|$1|S|3|T|5|6|7|1T|8|@]|D|@$9|1U|A|1V|1|1W]]|E|$]]|$1|U|3|-4|5|6|7|1X|8|@]|D|@]|E|$]]]|V|$W|$5|X|Y|Z|E|$10|11]]|12|$5|X|Y|Z|E|$10|13]]]]

Normally, each time <code>db.insert()</code> is used, SQLite creates a transaction (and resulting journal file in the filesystem), which slows things down.

If you use <code>db.beginTransaction()</code> and <code>db.endTransaction()</code> SQLite creates only a single journal file on the filesystem and then commits all the inserts at the same time, dramatically speeding things up.

Here is some pseudo code from: <a href="http://notes.theorbis.net/2010/02/batch-insert-to-sqlite-on-android.html">Batch insert to SQLite database on Android</a>

<pre><code>try
{
 db.beginTransaction();

 for each record in the list
 {
 do_some_processing();

 if (line represent a valid entry)
 {
 db.insert(SOME_TABLE, null, SOME_VALUE);
 }

 some_other_processing();
 }

 db.setTransactionSuccessful();
}
catch (SQLException e) {}
finally
{
 db.endTransaction();
}
</code></pre>

If you wish to abort a transaction due to an unexpected error or something, simply <code>db.endTransaction()</code> without first setting the transaction as successful (<code>db.setTransactionSuccessful()</code>).

Another useful method is to use <code>db.inTransaction()</code> (returns <code>true</code> or <code>false</code>) to determine if you are currently in the middle of a transaction.

<a href="http://developer.android.com/reference/android/database/sqlite/SQLiteDatabase.html">Documentation here</a>

blocks|key|3018955|text|嗯，我的解决方案有点奇怪，但效果很好……我编译了大量数据并一次性插入(批量插入？)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3018956|我使用db.execSQL(Query)命令，并使用以下语句构建“查询”...|offset|length|style|CODE|3018957|INSERT+INTO+yourtable+SELECT+*+FROM+(
++++SELECT+'data1','data2'....+UNION
++++SELECT+'data1','data2'....+UNION
++++SELECT+'data1','data2'....+UNION
++++.
++++.
++++.
++++SELECT+'data1','data2'....
)|code-block|syntax|javascript|3018958|唯一的问题是查询的构建，这可能是一种混乱。我希望它能帮上忙|3018959|entityMap^0|0|3|H|0|0|0^^$0|@$1|2|3|4|5|6|7|Q|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|R|8|@$D|S|E|T|F|G]]|9|@]|A|$]]|$1|H|3|I|5|J|7|U|8|@]|9|@]|A|$K|L]]|$1|M|3|N|5|6|7|V|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|W|8|@]|9|@]|A|$]]]|P|$]]

Well, my solution for this it kind of weird but works fine...
I compile a large sum of data and insert it in one go (bulk insert?)

I use the <code>db.execSQL(Query)</code> command and I build the "Query" with the following statement...

<pre><code>INSERT INTO yourtable SELECT * FROM (
 SELECT 'data1','data2'.... UNION
 SELECT 'data1','data2'.... UNION
 SELECT 'data1','data2'.... UNION
 .
 .
 .
 SELECT 'data1','data2'....
)
</code></pre>

The only problem is the building of the query which can be kind of messy.
I hope it helps

blocks|key|3018970|text|下面的例子将完美地工作。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3018971|+String+sql+=+"INSERT+INTO+"+%2B+DatabaseHelper.TABLE_PRODUCT_LIST
++++++++++++++++%2B+"+VALUES+(?,?,?,?,?,?,?,?,?);";

++++++++SQLiteDatabase+db+=+this.getWritableDatabase();
++++++++SQLiteStatement+statement+=+db.compileStatement(sql);
++++++++db.beginTransaction();
++++++++for(int+idx=0;+idx+<+Produc_List.size();+idx%2B%2B)+{
++++++++++++statement.clearBindings();
++++++++++++statement.bindLong(1,+Produc_List.get(idx).getProduct_id());
++++++++++++statement.bindLong(2,++Produc_List.get(idx).getCategory_id());
++++++++++++statement.bindString(3,+Produc_List.get(idx).getName());
//++++++++++++statement.bindString(4,+Produc_List.get(idx).getBrand());
++++++++++++statement.bindString(5,+Produc_List.get(idx).getPrice());
++++++++++++//statement.bindString(6,+Produc_List.get(idx).getDiscPrice());
++++++++++++statement.bindString(7,+Produc_List.get(idx).getImage());
++++++++++++statement.bindLong(8,+Produc_List.get(idx).getLanguage_id());
++++++++++++statement.bindLong(9,+Produc_List.get(idx).getPl_rank());
++++++++++++statement.execute();

++++++++}
++++++++db.setTransactionSuccessful();
++++++++db.endTransaction();|code-block|syntax|javascript|3018972|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This example below will work perfectly

<pre><code> String sql = "INSERT INTO " + DatabaseHelper.TABLE_PRODUCT_LIST
 + " VALUES (?,?,?,?,?,?,?,?,?);";

 SQLiteDatabase db = this.getWritableDatabase();
 SQLiteStatement statement = db.compileStatement(sql);
 db.beginTransaction();
 for(int idx=0; idx &lt; Produc_List.size(); idx++) {
 statement.clearBindings();
 statement.bindLong(1, Produc_List.get(idx).getProduct_id());
 statement.bindLong(2, Produc_List.get(idx).getCategory_id());
 statement.bindString(3, Produc_List.get(idx).getName());
// statement.bindString(4, Produc_List.get(idx).getBrand());
 statement.bindString(5, Produc_List.get(idx).getPrice());
 //statement.bindString(6, Produc_List.get(idx).getDiscPrice());
 statement.bindString(7, Produc_List.get(idx).getImage());
 statement.bindLong(8, Produc_List.get(idx).getLanguage_id());
 statement.bindLong(9, Produc_List.get(idx).getPl_rank());
 statement.execute();

 }
 db.setTransactionSuccessful();
 db.endTransaction();
</code></pre>

I want to bulk insert about 700 records into the Android database on my next upgrade. What's the most efficient way to do this? From various posts, I know that if I use <code>Insert</code> statements, I should wrap them in a transaction. There's also a <a href="https://stackoverflow.com/questions/2887119/populate-android-database-from-csv-file">post</a> about using your own database, but I need this data to go into my app's standard Android database. Note that this would only be done once per device.

Some ideas:

<ol>
<li>Put a bunch of SQL statements in a file, read them in a line at a time, and exec the SQL.</li>
<li>Put the data in a CSV file, or JSON, or YAML, or XML, or whatever. Read a line at a time and do <code>db.insert()</code>.</li>
<li>Figure out how to do an import and do a single import of the entire file.</li>
<li>Make a sqlite database containing all the records, copy that onto the Android device, and somehow merge the two databases.</li>
<li>[EDIT] Put all the SQL statements in a single file in res/values as one big string. Then read them a line at a time and exec the SQL.</li>
</ol>

What's the best way? Are there other ways to load data? Are 3 and 4 even possible?

Bulk Insertion on Android device

数据存储

日志文件

文件系统

数据库

Android

SQLite

JSON

我想在下一次升级时向Android数据库中批量插入大约700条记录。做这件事最有效的方法是什么？从不同的帖子中，我知道如果我使用Insert语句，我应该将它们包装在一个事务中。还有一个关于使用你自己的数据库的，但我需要这些数据进入我的应用程序的标准安卓数据库。请注意，每台设备只能执行一次此操作。一些想法：将一堆SQL语...

问Android设备上的批量插入
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Android设备上的批量插入EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问Android设备上的批量插入
EN