在 Android 上的 SQLite 中查询和使用游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1122679/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Querying and working with Cursors in SQLite on Android
提问by longhairedsi
Not sure if I'm the only one who feels this...
不知道是不是只有我一个人有这种感觉……
I find working with the sqlite api in android a complete pain in the butt and pretty soul destroying. Has anyone got any tips/helpers to make my life easier?
我发现在 android 中使用 sqlite api 是一种完全的痛苦和漂亮的灵魂破坏。有没有人有任何提示/帮助可以让我的生活更轻松？
Here's an example of what I'm talking about.
这是我正在谈论的一个例子。
//create code
db.execSQL("CREATE TABLE " + CUSTOMER_TABLE_NAME + " ("
+ GENERIC_ID_KEY+ " INTEGER PRIMARY KEY NOT NULL, "
+ PHONE_KEY + " INTEGER NOT NULL, "
+ CUSTOMER_NAME_KEY+ " TEXT NOT NULL, "
+ EMAIL_KEY + " TEXT NOT NULL, "
+ ADDRESS_KEY +" TEXT);");
//get code
Cursor mCursor = mDb.query(true, CUSTOMER_TABLE_NAME, new String[] {GENERIC_ID_KEY,
ADDRESS_KEY, PHONE_KEY, EMAIL_KEY,CUSTOMER_NAME_KEY}, GENERIC_ID_KEY + "=" + customerDbId, null,
null, null, null, null);
Customer customer = new Customer (customerDbId, (CharSequence)mCursor.getString(mCursor.getColumnIndexOrThrow(CUSTOMER_NAME_KEY)),
(CharSequence)mCursor.getString(mCursor.getColumnIndexOrThrow(PHONE_KEY)),
(CharSequence)mCursor.getString(mCursor.getColumnIndexOrThrow(EMAIL_KEY)),
(CharSequence)mCursor.getString(mCursor.getColumnIndexOrThrow(ADDRESS_KEY)));
This a simple exmple of creating a simple customer object from a db query; some of my code is far nastier than this. Hand crafting queries in this way leads to all sort of errors I don't find until runtime.
这是从数据库查询创建简单客户对象的简单示例；我的一些代码比这更讨厌。以这种方式手工制作查询会导致我直到运行时才发现的各种错误。
Any tips greatly appreiciated!
非常感谢任何提示！
Ok after the tips below I now have this:
好的，在下面的提示之后，我现在有了这个：
db.execSQL("CREATE TABLE customer (_id INTEGER PRIMARY KEY NOT NULL, "
+ "phone_number INTEGER NOT NULL, "
+ "name TEXT NOT NULL, "
+ "email TEXT NOT NULL, "
+ "address TEXT);");
//get code
String q = "SELECT * FROM customer WHERE _id = " + customerDbId +";"
Cursor mCursor = mDb.rawQuery(q, null);
Customer customer = new Customer (mCursor);
in the Customer, I access the fields like this
在客户中，我访问这样的字段
mName = cursor.getString(2)
Ahh, I feel much better :)
啊，我感觉好多了:)
Cheers Si
干杯斯
回答by CommonsWare
- Don't use model objects if you do not have to. I've concluded, unless there is significant business logic that can only be represented via model objects, that they are more trouble than they are worth in a mobile platform.
- Assuming you are stuck with model objects, have them load themselves out of a
Cursor, rather than trying to pass in umpteen parameters. query()is much more verbose thanrawQuery()for limited added value, if you know SQL.- Assembling your
CREATE TABLEclause via concatenation is self-imposed pain, not mandated by SQLite or Android. - Don't use
getColumnIndexOrThrow()from custom-written code. You wrote the query, so you know what order the columns are coming back in. Only use something likegetColumnIndexOrThrow()if you are creating some abstract library that does not know the details of the Cursor it was given. Stringinherits fromCharSequence, so all those casts can be dropped.
- 如果不需要，请不要使用模型对象。我已经得出结论，除非存在只能通过模型对象表示的重要业务逻辑，否则它们在移动平台中的麻烦比它们的价值要大。
- 假设你被模型对象卡住了，让它们自己从 a 中加载出来
Cursor，而不是试图传入无数参数。 query()rawQuery()如果您了解 SQL，那么它比有限的附加值要冗长得多。CREATE TABLE通过串联组合您的子句是自我强加的痛苦，不是 SQLite 或 Android 强制要求的。- 不要使用
getColumnIndexOrThrow()自定义编写的代码。您编写了查询，因此您知道列返回的顺序。仅getColumnIndexOrThrow()当您正在创建一些不知道给定 Cursor 的详细信息的抽象库时才使用类似的东西。 String继承自CharSequence，因此可以删除所有这些强制转换。
回答by Will
I tested a lot of my SQL in SQLite before copying them over to Android as strings. It's easier for me to debug when I can directly interact with the command line.
在将它们作为字符串复制到 Android 之前，我在 SQLite 中测试了很多我的 SQL。当我可以直接与命令行交互时，我更容易调试。
Another technique I use is saving as much of my queries as possible as string constants or string resources.
我使用的另一种技术是将尽可能多的查询保存为字符串常量或字符串资源。
You also don't need SQL such as INTEGER NOT NULLsince SQLite uses duck typing/manifest typing. It does help for type affinity though..
您也不需要 SQL，例如INTEGER NOT NULLSQLite 使用鸭子类型/清单类型。不过，它确实有助于类型亲和力。
