问题描述
我没有处理前最后一周SQLite数据库。我过去的很多年以前处理SQL，但我仍然有它的要点。
下code读取从资产命名的14万字 dictionary.dic 和每个插入到一个其状态一起SQLite数据库。我的期望是，它会持续较长一段时间，但它一直像一个7平板电脑25分钟，还没有接近完成（在 P ）。
我应该说，嘿，这是一百万行的1/7。这会需要一段时间。在30秒;但我可以阅读14万字到的ArrayList＆LT;弦乐过夜。我意识到在创建数据库有开销，但许多，多少分钟？
我应该说，嗯，认为这将需要多长时间，如果不使用的AsyncTask ，并接受它，因为它是一个一次性的任务？但它确实令人讨厌，这么长时间。它是倒胃口。
我应该说，你为什么使用扫描？难怪它采取这么长时间？并做一些其他的资产访问？或者是，并不是真正的问题？
我也从来没有使用的AsyncTask 。我是不是滥用 doInBackground ？我有在那里有很多code的;不是所有人都必须去那里，但循环是它是什么，有挂断。
中使用 database.Insert ，这被称为方便的方法，是什么造成了挂断？我应该使用一个光标和查询呢？我不完全知道我怎么会做到这一点。得到了我的想法来自Deitel公司的通讯录应用程序中的Android程序员软件 - 应用驱动......，但他的数据库是空在一开始就
我考虑了很多心思。我只是需要有经验的人看，说：好吧，这是你的问题。我不能证明重做起所有我想过的事情，而不是否任何它会帮助一些指导。
公共类DatabaseConnector //扩展的ArrayList＆LT;串GT;
{
公共静态光标指针;
扫描仪scDict;
InputStream的流= NULL;
上下文mContext;
AssetManager mAssets; 公共静态最后弦乐DATABASE_NAME =说文解字;
公共静态最后弦乐TABLE_NAME =词表;
公共静态最后弦乐WORD_COLUMN_NAME =字;
公共静态最后弦乐STATUS_COLUMN_NAME =身份;
公共静态最后的String [] =列新的String [] {WORD_COLUMN_NAME，STATUS_COLUMN_NAME}; 私人DatabaseOpenHelper ___databaseOpenHelper; //创建数据库
私人SQLiteDatabase ___database; //为与数据库交互 公共DatabaseConnector（上下文_context，AssetManager资产）
{
mContext = _context;
mAssets =资产;
___databaseOpenHelper =新DatabaseOpenHelper（_context，DATABASE_NAME，空，1）;
Log.w（DB连接，___ databaseOpenHelper.getDatabaseName（））;
createDbIfNecessary（）;
}; 公共无效的open（）抛出的SQLException //打开/创建
{
___database = ___ databaseOpenHelper.getWritableDatabase（）; //创建或打开
} 公共无效createDbIfNecessary（）{
this.open（）;
如果（getDbCount（）＆下; 140000）{
尝试{流= mAssets.open（dictionary.dic）; } 赶上（IOException异常五）{的System.out.println（Arrays.toString（e.getStackTrace（）））; } MainActivity.setLblProgress（这种一次性的任务需要一段时间：加载信......）;
借词借词=新借词（）;
loadWords.execute（（Object []对象），NULL）;
this.close（）;
}
} 公共无效的close（）{
如果（___数据库！= NULL）
___ database.close（）;
} 公众诠释getDbCount（）{
this.open（）;
返回___ database.query（TABLE_NAME，列，NULL，NULL，NULL，NULL，NULL）.getCount（）; } 众长insertWord（字符串_word）
{
ContentValues
__新词;
__newWord =新ContentValues（）;
__newWord.put（WORD_COLUMN_NAME，_word）;
__newWord.put（STATUS_COLUMN_NAME，真）; 长__row = ___ database.insert（TABLE_NAME，空，__newWord）; 返回__row; // -1，如果不能插入
} ////////////////////////////////////////////////// ////////////////////////////////////////////////
私有类DatabaseOpenHelper扩展SQLiteOpenHelper
{
公共DatabaseOpenHelper（上下文_context，字符串_name，CursorFactory _factory，INT _version）
{超（_context，_name，_factory，_version）; } @覆盖公共无效的onCreate（SQLiteDatabase _db）
{
_db.execSQL（CREATE TABLE+ TABLE_NAME +
（
+ WORD_COLUMN_NAME +TEXT主键，//不为空，
+ STATUS_COLUMN_NAME +布尔+
）;
）; //执行查询创建___database
} } //结束类DatabaseOpenHelper
////////////////////////////////////////////////// ////////////////////////////////////////////////
私有类借词扩展的AsyncTask＆LT;对象，整型，太虚＆GT;
{
@覆盖
保护无效doInBackground（对象... PARAMS）{
长K = 0;
scDict =新的扫描仪（流）.useDelimiter（\\ r \\ n）;
长计数= getDbCount（）;
Log.w（启动时加载，+计数）;
字符串s =;
而（K ++ LT;计数）{
S = scDict.next（）;
}
Log.w（添加后，S）;
而（scDict.hasNext（））
{
S = scDict.next（）;
publishProgress（（整数）（INT）s.charAt（0））;
insertWord（多个）;
返回null;
} 保护无效onProgressUpdate（整数...进度）{
INT C =（int）的进展[0];
MainActivity.setLastLetterProcessed（（char）的C）;
} @覆盖
保护无效onPostExecute（虚空X）
{
MainActivity.popupMessage（数据库已创建，mContext）;
}
}
} //结束类DatabaseConnector
您正在尝试做的140000个人数据库事务。这可能需要数周时间。
相反，无论是包装您的整个事情在一个单一的交易，或批次插入到交易（例如每1000字）。您可以使用此伪Java中创建您自己的事务范围：
db.beginTransaction（）;尝试{
//做你的SQL工作在这里
db.setTransactionSuccesful（）;
}
赶上（例外五）{
//日志，事件总线消息的用户界面，无论
}
最后{
db.endTransaction（）;
}
I haven't dealt with SQLite databases before last week. I last dealt with SQL many years ago, but I still have the gist of it.
The code below reads 140,000 words from an asset named dictionary.dic and inserts each into a SQLite database along with its status. My expectation was that it would take a good while, but it's been like 25 minutes on a 7" tablet and still not near finished (on P).
Should I say, "Hey, it's 1/7 of a million rows. It's gonna take awhile." But I can read all 140,000 words into an ArrayList<String> in 30 seconds. I realize there's overhead in creating the database, but many, many minutes?
Should I say, "Well, think how long it would take if not using AsyncTask" and accept it since it's a one-time task? But it's really obnoxious, taking so long. It's off-putting.
Should I say, "Why are you using a Scanner? No wonder it's taking so long?" and do some other asset access? Or is that not the real problem?
I also have never used AsyncTask. Am I misusing doInBackground? I've got a lot of code in there; not all MUST go there, but the loop is what it is and there's the hangup.
Is using database.Insert, which is called a "convenience method", what's causing the hangup? Should I be using a Cursor and query instead? I'm not entirely sure how I'd do that. Got my idea from Deitel's "Address Book" app in "Android for Programmers--App Driven...", but his database is empty at the outset.
I've given this plenty of thought. I just need someone with experience to look and say, "Well, HERE'S your problem." I can't justify starting redoing all the things I've thought of without some guidance about whether any of it is going to help.
public class DatabaseConnector //extends ArrayList<String>
{
public static Cursor cursor ;
Scanner scDict;
InputStream stream = null;
Context mContext;
AssetManager mAssets;
public static final String DATABASE_NAME = "Dictionary";
public static final String TABLE_NAME = "wordlist";
public static final String WORD_COLUMN_NAME = "word";
public static final String STATUS_COLUMN_NAME = "status";
public static final String [] columns = new String[]{WORD_COLUMN_NAME, STATUS_COLUMN_NAME};
private DatabaseOpenHelper ___databaseOpenHelper; // creates the database
private SQLiteDatabase ___database; // for interacting with the database
public DatabaseConnector(Context _context, AssetManager assets)
{
mContext = _context;
mAssets = assets;
___databaseOpenHelper = new DatabaseOpenHelper(_context, DATABASE_NAME, null, 1);
Log.w("DB connected", ___databaseOpenHelper.getDatabaseName());
createDbIfNecessary();
};
public void open() throws SQLException // opens/creates
{
___database = ___databaseOpenHelper.getWritableDatabase(); // create OR open
}
public void createDbIfNecessary(){
this.open();
if(getDbCount() < 140000){
try { stream = mAssets.open("dictionary.dic"); }
catch (IOException e) { System.out.println(Arrays.toString(e.getStackTrace())); }
MainActivity.setLblProgress("This one-time task takes awhile: loading letter... ");
LoadWords loadWords = new LoadWords();
loadWords.execute((Object[]) null);
this.close();
}
}
public void close(){
if(___database != null)
___database.close();
}
public int getDbCount(){
this.open();
return ___database.query(TABLE_NAME, columns, null, null, null, null, null).getCount();
}
public long insertWord(String _word)
{
ContentValues
__newWord;
__newWord = new ContentValues();
__newWord.put(WORD_COLUMN_NAME, _word);
__newWord.put(STATUS_COLUMN_NAME, true);
long __row = ___database.insert(TABLE_NAME, null, __newWord);
return __row; // -1 if can't insert
}
//////////////////////////////////////////////////////////////////////////////////////////////////
private class DatabaseOpenHelper extends SQLiteOpenHelper
{
public DatabaseOpenHelper(Context _context, String _name, CursorFactory _factory, int _version)
{ super(_context, _name, _factory, _version); }
@Override public void onCreate(SQLiteDatabase _db)
{
_db.execSQL( "CREATE TABLE " + TABLE_NAME +
"("
+ WORD_COLUMN_NAME + " TEXT primary key , " //not null, "
+ STATUS_COLUMN_NAME + " BOOLEAN" +
");"
); // execute query to create the ___database
}
} // end class DatabaseOpenHelper
//////////////////////////////////////////////////////////////////////////////////////////////////
private class LoadWords extends AsyncTask<Object, Integer, Void>
{
@Override
protected Void doInBackground(Object... params) {
long k = 0;
scDict = new Scanner(stream).useDelimiter("\r\n");
long count = getDbCount();
Log.w("Start load at " , "" + count);
String s = "";
while(k++ < count){
s = scDict.next();
}
Log.w("Add after " , s);
while (scDict.hasNext())
{
s = scDict.next();
publishProgress((Integer)(int)s.charAt(0));
insertWord(s) ;
return null;
}
protected void onProgressUpdate(Integer... progress) {
int c = (int)progress[0];
MainActivity.setLastLetterProcessed((char) c);
}
@Override
protected void onPostExecute(Void x)
{
MainActivity.popupMessage("Database has been created", mContext);
}
}
} // end class DatabaseConnector
You are attempting to do 140,000 individual database transactions. That might take weeks.
Instead, either wrap your entire thing in a single transaction, or batch the inserts into transactions (e.g., every 1000 words). You can create your own transaction bounds using this pseudo-Java:
db.beginTransaction();
try {
// do your SQL work here
db.setTransactionSuccesful();
}
catch (Exception e) {
// logging, event bus message to UI, whatever
}
finally {
db.endTransaction();
}
这篇关于创建14万行的SQLite数据库中的AsyncTask doInBackground采取了许多，许多分钟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！