Android中SQLite数据库插入或替换

SQLite是Android内置的轻量级数据库。我们在操作数据库的时候，经常有这样的需求：

如果没有这个Primery Key的数据，就插入这条数据；如果已经存在了，就更新这条数据。

之前我的做法是先SELECT一下，看是否有数据？如果有的话，就UPDATE这条数据，如果没有的话，就INSERT一条数据。

最近发现更好的解决方法，一条SQL语句就能解决。假设你的表包含三列，分别是id、name、role，其中id是主键。

INSERT OR REPLACE INTO Employee (id, name, role)  
  VALUES (1, 'John Foo', 'CEO');

其中关键是INSERT OR REPLACE。如果数据库表中已经包含了id=1的情况，就会直接替换掉。

要达到同样的目的，可以在创建表的Schema的时候，使用ON CONFLICT REPLACE来实现。如下：

CREATE TABLE Employee  
    (id INTEGER PRIMARY KEY ON CONFLICT REPLACE, 
    name TEXT NOT NULL, 
    role TEXT);

这时候，你就可以直接使用INSERT了，如果id冲突了，会直接替换掉：

INSERT INTO Employee (id, name, role)  
    VALUES (1, 'John Foo', 'CEO');

INSERT INTO Employee (id, name, role)  
    VALUES (1, 'John Foo', 'CTO');

SELECT * FROM Employee;  
1|John Foo|CTO  

在这里看到，针对上面的方法更好的补充。例如你不插入全部的列的情况下，如下面的语句：

INSERT OR REPLACE INTO Employee (id, role)  
  VALUES (1, 'code monkey');

这就会出现问题，如果存在id=1的数据，那么会插入一个新的，原来的name值会被丢掉，会被设置为NULL或者默认值。更好的解决方法如下：

INSERT OR REPLACE INTO Employee (id, role, name)  
  VALUES (1, 
          'code monkey',
          (SELECT name FROM Employee WHERE id = 1)
         );

这样，如果存在原来id=1的数据，就会保留原来的name值，否这就为默认值或者NULL。再如下面的语句：

INSERT OR REPLACE INTO Employee (id, name, role)  
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
         );

这语句的含义是，如果存在id=1的，就保留原来的name值，否则就设置为Benchwarmer。这里关键利用了COALESCE(a, b, ...)函数，此函数返回第一个非空参数，全为NULL时返回NULL。