Android的源码 - 只添加新记录表 [英] Android sqlite - adding only new records to table

问题描述

我收集手机的通话记录到表中。

这个伟大的工程：

 众长populate_Calls（字符串名称，字符串电话，字符串类型，病程字符串，字符串日期，字符串的ContactID）{
        ContentValues​​ CV =新ContentValues​​（）;
             cv.put（KEY_NAME，名）;
             cv.put（KEY_PHONE，电话）;
             cv.put（key_type的，类型）;
             cv.put（KEY_DURATION，持续时间）;
             cv.put（KEY_DATE，日期）;
             cv.put（KEY_CONTACTID，使用ContactID）;        返回ourDatabase.insert（DATABASE_TABLE，空，CV）;
    }
 

让我们假设表包含455记录。
现在，用户提出了一些电话，并刷新表。我想只有新的呼叫数据被添加到表中。我一直在试图与此code（我真的不明白它虽然）但我每次刷新表时，添加越来越多的记录。在仿真器正常工作（有我只拿到了12拨出电话）。

我想我只需要自标识通话，观看手机号码和日期。如果使用该号码，并在该日的记录已经存在，只是跳到下一个记录。如果它不存在，则添加到表中。对吧？

 公共无效populate_Calls2（字符串名称，字符串电话，字符串类型，病程字符串，字符串日期，字符串的ContactID）{
 ourDatabase.execSQL（插入+ DATABASE_TABLE +（+ KEY_NAME +，+ KEY_PHONE +，+为key_type +，+ KEY_DURATION +，+ KEY_DATE +，+ KEY_CONTACTID +）选择* FROM（SELECT'+名字+'，+手机+，+类型+，+时间+，+日期+，+的ContactID +）WHERE NOT EXISTS（SELECT * FROM + DATABASE_TABLE +WHERE+ KEY_PHONE +='+手机+和+ KEY_DATE +='+日期+'）LIMIT 1;）;    }
 

我也试过，但我得到

  

1失败（近选择：语法错误）上0x11a2f0时preparing
  INSERT INTO CallTable VALUES（姓名，电话，类型，时间，日期，使用ContactID）
  SELECT * FROM CallTable WHERE NOT EXISTS（SELECT * FROM CallTable
  WHERE手机='30411552'和日期='1338569074976'）LIMIT 1。

和语法错误：

  ourDatabase.execSQL（插入+ DATABASE_TABLE +VALUES（+ KEY_NAME +，+ KEY_PHONE +，+为key_type +，+ KEY_DURATION +， + KEY_DATE +，+ KEY_CONTACTID +）SELECT * FROM+ DATABASE_TABLE +WHERE NOT EXISTS（SELECT * FROM+ DATABASE_TABLE +WHERE+ KEY_PHONE +='+手机+和+ KEY_DATE +='+日期+'）LIMIT 1;）;
 

解决方案

您不需要检查数据库的副本，如果你将它设置在创建表来为你做的。

您创建表的字符串可能是这样的：

 私有静态最后弦乐CREATE_TABLE =CREATE TABLE
        + YOUR_TABLE +（_id整数主键自动增量
        + KEY_NAME +文本不为空，
        + KEY_PHONE +文本不为空，
        +为key_type +文本不为空，
        + KEY_DURATION +文本不为空，
        + KEY_DATE +文本不为空，
        + KEY_CONTACTID +整数NOT NULL，
        +UNIQUE（+ KEY_PHONE +，+ KEY_DATE +）关于冲突无视）;
 

这设置起来，这样，如果你有相同，则记录将导致违反约束，插入手机和日期的组合/该记录的更新将被忽略（你可以改变的最后一位为ON冲突不能让它丢你能赶上，而不是默默地跳过插入/更新操作错误）。

这将使其工作是否使用插入（），更新（），或更换（）。

I am collecting the phone's call log into a table.

This works great:

public long populate_Calls(String name, String phone, String type, String duration, String date, String contactid) {
        ContentValues cv = new ContentValues();
             cv.put(KEY_NAME, name);
             cv.put(KEY_PHONE, phone);
             cv.put(KEY_TYPE, type);
             cv.put(KEY_DURATION, duration);
             cv.put(KEY_DATE, date);
             cv.put(KEY_CONTACTID, contactid);

        return ourDatabase.insert(DATABASE_TABLE, null, cv);        
    }

Let's suppose the table consists of 455 records. Now the user makes some calls, and refreshes the table. I want only the new call data to be added to the table. I have been trying with this code (I don't really understand it though) but every time I refresh the table, more and more records are added. In the emulator it works fine (there I only got 12 outgoing calls).

I guess I need to watch only the phone number and the date since that identifies the call. If a record with that number and at that date already exists, just jump to the next record. If it does not exist, add it to the table. Right?

    public void populate_Calls2(String name, String phone, String type, String duration, String date, String contactid) {
 ourDatabase.execSQL("INSERT INTO " + DATABASE_TABLE + " (" + KEY_NAME + "," + KEY_PHONE + "," + KEY_TYPE + "," + KEY_DURATION + "," + KEY_DATE + "," + KEY_CONTACTID + ") SELECT * FROM (SELECT '" + name  + "'," + phone + "," + type + "," + duration + "," + date + "," + contactid + ") WHERE NOT EXISTS (SELECT * FROM " + DATABASE_TABLE + " WHERE " + KEY_PHONE + " = '" + phone + "' AND " + KEY_DATE + " = '" + date + "') LIMIT 1;");    

    }

I also tried this but I get

Failure 1 (near "SELECT": syntax error) on 0x11a2f0 when preparing 'INSERT INTO CallTable VALUES(name,phone,type,duration,date,contactid) SELECT * FROM CallTable WHERE NOT EXISTS (SELECT * FROM CallTable WHERE phone = '30411552' AND date = '1338569074976') LIMIT 1;'.

and syntax error:

ourDatabase.execSQL("INSERT INTO " + DATABASE_TABLE + " VALUES(" + KEY_NAME + "," + KEY_PHONE + "," + KEY_TYPE + "," + KEY_DURATION + "," + KEY_DATE + "," + KEY_CONTACTID + ") SELECT * FROM " + DATABASE_TABLE + " WHERE NOT EXISTS (SELECT * FROM " + DATABASE_TABLE + " WHERE " + KEY_PHONE + " = '" + phone + "' AND " + KEY_DATE + " = '" + date + "') LIMIT 1;");

解决方案

You do not need to check the database for duplicates if you set it up to do that for you when you create the table.

Your table creation string might look like this:

private static final String CREATE_TABLE = "create table "
        + YOUR_TABLE + " (_id integer primary key autoincrement, "
        + KEY_NAME + " text not null,"
        + KEY_PHONE + " text not null,"
        + KEY_TYPE + " text not null,"
        + KEY_DURATION + " text not null,"
        + KEY_DATE + " text not null,"
        + KEY_CONTACTID + " integer not null,"
        + "UNIQUE(" + KEY_PHONE + "," + KEY_DATE + ") ON CONFLICT IGNORE);";

That sets it up so that if you have a combination of phone and date that are the same the record will cause a constraint violation and the insert/update of that record will be ignored (You can change the last bit to ON CONFLICT FAIL to make it throw an error you can catch rather than silently skipping the insert/update operation).

This will make it work whether you use insert(), update(), or replace().

这篇关于Android的源码 - 只添加新记录表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！