Android sqlite如何检查记录是否存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20415309/
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
Android sqlite how to check if a record exists
提问by neknek mouh
I would like to check whether a record exists or not.
我想检查记录是否存在。
Here is what I've tried:
这是我尝试过的：
MainActivity.class
主Activity.class
public void onTextChanged(CharSequence s, int start, int before, int count) {
System.out.println("Ontext changed " + new String(s.toString()));
strDocumentFrom = s.toString();
if(s.toString().isEmpty()){
} else {
try{
strTransactionDate = dbHelper.getTransactionDateByDocumentNumber(strDocumentFrom);
//strTotalAmount = dbHelper.getTotalAmountByDocumentNumber(strDocumentFrom);
//strVan = dbHelper.getVanByDocumentNumber(strDocumentFrom);
//etTransactionDate.setText(strTransactionDate);
//etTotalAmount.setText(strTotalAmount);
//Log.d("Van", "" + strVan);
//etVan.setText(strVan);
} catch (SQLiteException e) {
e.printStackTrace();
Toast.makeText(ReceivingStocksHeader.this,
"Document number does not exist.", Toast.LENGTH_SHORT).show();
}
}
DBHelper.class
数据库助手类
// TODO DISPLAYING RECORDS TO TRANSRCVHEADER
public String getTransactionDateByDocumentNumber(String strDocumentNumber){
String[] columns = new String[]{KEY_TRANSACTIONDATE};
Cursor c = myDataBase.query(TBL_INTRANS,
columns, null,
null, null, null, null, null);
if(c != null){
c.moveToFirst();
String date = c.getString(0);
return date;
} else {
Log.d("Error", "No record exists");
}
return null;
}
But it doesn't get it to the catch block to display the toast.
但它并没有让它到 catch 块来显示 toast。
What am I doing wrong in here?
我在这里做错了什么？
回答by dipali
public static boolean CheckIsDataAlreadyInDBorNot(String TableName,
String dbfield, String fieldValue) {
SQLiteDatabase sqldb = EGLifeStyleApplication.sqLiteDatabase;
String Query = "Select * from " + TableName + " where " + dbfield + " = " + fieldValue;
Cursor cursor = sqldb.rawQuery(Query, null);
if(cursor.getCount() <= 0){
cursor.close();
return false;
}
cursor.close();
return true;
}
I hope this is useful to you... This function returns true if record already exists in db. Otherwise returns false.
我希望这对您有用...如果记录已存在于 db 中，则此函数返回 true。否则返回假。
回答by Chad Bingham
These are all good answers, however many forget to close the cursor and database. If you don't close the cursor or database you may run in to memory leaks.
这些都是很好的答案，但是很多人忘记关闭游标和数据库。如果您不关闭游标或数据库，您可能会遇到内存泄漏。
Additionally:
You can get an error when searching by Stringthat contains non alpha/numeric characters. For example: "1a5f9ea3-ec4b-406b-a567-e6927640db40". Those dashes (-) will cause an unrecognized tokenerror. You can overcome this by putting the string in an array. So make it a habit to query like this:
另外：
在搜索String包含非字母/数字字符的内容时，您可能会收到错误消息。例如：“ 1a5f9ea3-ec4b-406b-a567-e6927640db40”。那些破折号 ( -) 会导致unrecognized token错误。您可以通过将字符串放入数组来克服这个问题。所以养成这样查询的习惯：
public boolean hasObject(String id) {
SQLiteDatabase db = getWritableDatabase();
String selectString = "SELECT * FROM " + _TABLE + " WHERE " + _ID + " =?";
// Add the String you are searching by here.
// Put it in an array to avoid an unrecognized token error
Cursor cursor = db.rawQuery(selectString, new String[] {id});
boolean hasObject = false;
if(cursor.moveToFirst()){
hasObject = true;
//region if you had multiple records to check for, use this region.
int count = 0;
while(cursor.moveToNext()){
count++;
}
//here, count is records found
Log.d(TAG, String.format("%d records found", count));
//endregion
}
cursor.close(); // Dont forget to close your cursor
db.close(); //AND your Database!
return hasObject;
}
回答by Piyush
you can also see this:
你也可以看到这个：
if (cursor.moveToFirst()) {
// record exists
} else {
// record not found
}
OR
或者
You just check Cursor not null after that why you check count not 0.
之后您只需检查 Cursor not null 为什么您检查 count 不是 0。
So, that you try this...
所以，你试试这个......
DBHelper.getReadableDatabase();
Cursor mCursor = db.rawQuery("SELECT * FROM " + DATABASE_TABLE + " WHERE yourKey=? AND yourKey1=?", new String[]{keyValue,keyvalue1});
if (mCursor != null)
{
return true;
/* record exist */
}
else
{
return false;
/* record not exist */
}
回答by Kapil Jituri
Raw queries are more vulnerable to SQL Injection. I will suggest using query()method instead.
原始查询更容易受到 SQL 注入的影响。我会建议改用query()方法。
public boolean Exists(String searchItem) {
String[] columns = { COLUMN_NAME };
String selection = COLUMN_NAME + " =?";
String[] selectionArgs = { searchItem };
String limit = "1";
Cursor cursor = db.query(TABLE_NAME, columns, selection, selectionArgs, null, null, null, limit);
boolean exists = (cursor.getCount() > 0);
cursor.close();
return exists;
}
Source: here
来源：这里
回答by insomniac
You can use SELECT EXISTScommand and execute it for a cursorusing a rawQuery,
from the documentation
您可以使用SELECT EXISTS命令并执行它以cursor使用 a rawQuery，来自文档
The EXISTS operator always evaluates to one of the integer values 0 and 1. If executing the SELECT statement specified as the right-hand operand of the EXISTS operator would return one or more rows, then the EXISTS operator evaluates to 1. If executing the SELECT would return no rows at all, then the EXISTS operator evaluates to 0.
EXISTS 运算符的计算结果始终为整数值 0 和 1 之一。如果执行指定为 EXISTS 运算符右侧操作数的 SELECT 语句将返回一行或多行，则 EXISTS 运算符的计算结果为 1。如果执行 SELECT将根本不返回任何行，然后 EXISTS 运算符的计算结果为 0。
回答by Umit Kaya
I have tried all methods mentioned in this page, but only below method worked well for me.
我已经尝试了本页中提到的所有方法，但只有以下方法对我有效。
Cursor c=db.rawQuery("SELECT * FROM user WHERE idno='"+txtID.getText()+"'", null);
if(c.moveToFirst())
{
showMessage("Error", "Record exist");
}
else
{
// Inserting record
}
回答by Sapnesh Naik
SELECT EXISTSwith LIMIT 1is much faster.
SELECT EXISTSwith LIMIT 1要快得多。
Query Ex: SELECT EXISTS (SELECT * FROM table_name WHERE column='value' LIMIT 1);
查询示例：SELECT EXISTS (SELECT * FROM table_name WHERE column='value' LIMIT 1);
Code Ex:
代码示例：
public boolean columnExists(String value) {
String sql = "SELECT EXISTS (SELECT * FROM table_name WHERE column='"+value+"' LIMIT 1)";
Cursor cursor = database.rawQuery(sql, null);
cursor.moveToFirst();
// cursor.getInt(0) is 1 if column with value exists
if (cursor.getInt(0) == 1) {
cursor.close();
return true;
} else {
cursor.close();
return false;
}
}
回答by Tejas Sharma
One thing the top voted answer did not mention was that you need single quotes, 'like this', around your search value if it is a text value like so:
最高投票答案没有提到的一件事是，如果搜索值是这样的文本值，则您需要单引号，“像这样”：
public boolean checkIfMyTitleExists(String title) {
String Query = "Select * from " + TABLE_NAME + " where " + COL1 + " = " + "'" + title + "'";
Cursor cursor = database.rawQuery(Query, null);
if(cursor.getCount() <= 0){
cursor.close();
return false;
}
cursor.close();
return true;
}
Otherwise, you will get a "SQL(query) error or missing database" error like I did without the single quotes around the titlefield.
否则，您将收到“SQL（查询）错误或缺少数据库”错误，就像我在标题字段周围没有单引号时所做的那样。
If it is a numeric value, it does not need single quotes.
如果是数值，则不需要单引号。
Refer to this SQL post for more details
回答by William
Here's a simple solution based on a combination of what dipali and Piyush Gupta posted:
这是一个基于 dipali 和 Piyush Gupta 发布的组合的简单解决方案：
public boolean dbHasData(String searchTable, String searchColumn, String searchKey) {
String query = "Select * from " + searchTable + " where " + searchColumn + " = ?";
return getReadableDatabase().rawQuery(query, new String[]{searchKey}).moveToFirst();
}
回答by sj_8
Code :
代码 ：
private String[] allPushColumns = { MySQLiteHelper.COLUMN_PUSH_ID,
MySQLiteHelper.COLUMN_PUSH_TITLE, MySQLiteHelper.COLUMN_PUSH_CONTENT, MySQLiteHelper.COLUMN_PUSH_TIME,
MySQLiteHelper.COLUMN_PUSH_TYPE, MySQLiteHelper.COLUMN_PUSH_MSG_ID};
public boolean checkUniqueId(String msg_id){
Cursor cursor = database.query(MySQLiteHelper.TABLE_PUSH,
allPushColumns, MySQLiteHelper.COLUMN_PUSH_MSG_ID + "=?", new String [] { msg_id }, null, null, MySQLiteHelper.COLUMN_PUSH_ID +" DESC");
if(cursor.getCount() <= 0){
return false;
}
return true;
}
