如何检查Android中的数据库中是否已存在某个值

I've a database that I will populate with items after I parse a JSON response. How can I check if the values are already present in the database and prevent inserting again ?

在解析JSON响应后,我将使用一个数据库来填充项目。如何检查数据库中是否已存在这些值并阻止再次插入?

Eg. Name of Database: Exicom.sqlite

例如。数据库名称:Exicom.sqlite

Name of table: TimeReport

表名:TimeReport

Fields in Database: userID, clientName

数据库中的字段:userID,clientName

I am using SQLite in android.

我在android中使用SQLite。

Thanks for your input

感谢您的输入

This is my HELPER class. I've skipped opening and closing the database as it will be too long. Followed up by where I will call the method to insert values into the database.

这是我的HELPER课程。我已经跳过了打开和关闭数据库,因为它太长了。接下来我将调用方法将值插入数据库。

public static final String indexNo = "index_no";
public static final String user_id = "userId";
public static final String company_id = "companyId";
public static final String user_name = "username";
public static final String client_Id = "clientId";
public static final String project_Id = "projectId";
public static final String report_Id = "reportId";
public static final String niv_1  = "niv1";
public static final String niv_2 = "niv2";
public static final String work_type_id = "workTypeId";
public static final String time_type_id = "timeTypeId";
public static final String date_id = "dateId";
public static final String month_id = "monthId";
public static final String year_id = "yearId";
public static final String hourS = "hours";
public static final String private_comment = "privateComment";
public static final String Ncomment = "comment";
public static final String mod_flag = "modFlag";
public static final String new_flag = "newFlag";
public static final String open_flag = "openFlag";
public static final String delete_flag = "deleteFlag";

private static final String DATABASE_NAME = "CopernicusDB.sqlite";
private static final String DATABASE_TABLE = "TimeReportTable";
private static final int    DATABASE_VERSION = 1;

private final Context context;
private static DatabaseHelper DBHelper;
private static SQLiteDatabase db;
private static String TAG = "##---SecondActivityUserHelper---##";
public SecondActivityUserHelper(Context ctx)
{
this.context = ctx;
DBHelper = new DatabaseHelper(context);
}

private static class DatabaseHelper extends SQLiteOpenHelper{

    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        Log.v(TAG,"The DatabaseHelper method ");
    }

    public long insertIntoDatabase(String companyId,String username, String dateId,
            String clientId,String projectId,String niv1,String niv2,String workTypeId,String timeTypeId,
            String hours,String comment,String privateComment,String openFlag,String reportId) 
            {
            ContentValues initialValues = new ContentValues();
            initialValues.put(company_id, companyId);
            initialValues.put(user_name, username);
            initialValues.put(date_id,dateId);
            initialValues.put(client_Id,clientId);
            initialValues.put(project_Id,projectId);
            initialValues.put(niv_1,niv1);
            initialValues.put(niv_2,niv2);
            initialValues.put(work_type_id,workTypeId);
            initialValues.put(time_type_id,timeTypeId);
            initialValues.put(hourS, hours);
            initialValues.put(Ncomment, comment);
            initialValues.put(private_comment,privateComment);
            initialValues.put(open_flag, openFlag);
            initialValues.put(report_Id,reportId);
            Log.v(TAG, "Inserted into database sucessfully");
            return db.insert(DATABASE_TABLE, null, initialValues);
            }

}

useradapter.openDatabase();
long id = dB.insertIntoDatabase(  newcompanyid,newusername,newdate,
newClientId,newprojectId,newniv1,newniv2,newworktypeid,newtimetypeid,
newhours,newcomment,newprivatecomment,newopen,newreportid);
                             useradapter.closeDatabase();

3 个解决方案

#1

Create an object that will hold your data, eg. ClientData
Create a method for fetching all data from the database

创建一个可以保存数据的对象,例如。 ClientData创建一个从数据库中获取所有数据的方法

public List<ClientData> selectAll() {
   List<ClientData> list = new ArrayList<ClientData>();
   Cursor cursor = this.myDataBase.query(TABLE_NAME, new String[] { "userID, clientName" },
   null, null, null, null, null);
   if (cursor.moveToFirst()) {
        do {
           list.add(new ClientData(cursor.getString(0), cursor.getString(1)));
         } while (cursor.moveToNext());
   }
   if (cursor != null && !cursor.isClosed()) {
         cursor.close();
   }
   return list;
}

Before executing your insert statements, fetch all data and then check if data exists:

在执行insert语句之前,获取所有数据,然后检查数据是否存在:

if (!list.contains(clientData)) {
    executeInsert();
}

I am not sure if SQLite supports stored procedures, but if it does, you could write a stored procedure for that as well.

我不确定SQLite是否支持存储过程,但如果确实如此,您也可以为其编写存储过程。

#2

1 )if your looking for the unique id than make your id field as auto increment and insert only name value

1)如果您查找唯一ID而不是将您的id字段设置为自动增量并仅插入名称值

2 ) if you are not looking for unique than retrieve the all data for this table store in the array than compare your inserted value with existing value in data base

2)如果您不是在寻找唯一的,而不是在数组中检索此表存储的所有数据,那么将插入的值与数据库中的现有值进行比较

#3

In your example table:

在您的示例表中:

When you create your table you can set your name as unique (if that is what you want unique) with the following (Using a SQLiteOpenHelper).

创建表时,可以使用以下命令将您的名称设置为唯一(如果这是您想要的唯一)(使用SQLiteOpenHelper)。

String createPlayerTable = "create table " +
                TIME_REPORT +
                " (" +
                USER_ID + " integer primary key autoincrement not null," +
                CLIENT_NAME + " text not null," +                    
                "UNIQUE("+CLIENT_NAME+")"+
                ");";

Then in you insert insertIntoDatabase method use

然后在你插入insertIntoDatabase方法中使用

db.insertOrThrow(TIME_REPORT, null, initialValues);

instead of

db.insert(TIME_REPORT, null, initialValues);

This may throw a SQLiteConstraintException so you will have add a try/catch.

这可能抛出SQLiteConstraintException,因此您将添加try / catch。

Hope this is what you need.

希望这是你需要的。