android.database.sqlite.SQLiteException : near "?": syntax error (code 1):

Question

我正在使用 Room 库(MVVM 模式)，Dao 函数之一返回此错误消息:

android.database.sqlite.SQLiteException: near "?": syntax error (code 1): , while compiling: UPDATE parcel_table SET possibleDeliveryPersonsList = ?,? WHERE id = ?

这是 Dao 代码:

@Dao
public interface ParcelDao {
    @Insert
    void insert(Parcel parcel);

    @Delete
    void delete(Parcel parcel);

    @Query("UPDATE parcel_table SET shippingDate=:shippingDate WHERE id = :id")
    void updateShippingDate(String shippingDate, int id);

    @Query("UPDATE parcel_table SET parcelStatus=:status WHERE id = :id")
    void updatePackageStatus(Enums.ParcelStatus status, int id);

    @Query("UPDATE parcel_table SET deliveryPersonName=:deliveryPersonName WHERE id = :id")
    void updateDeliveryPersonName(String deliveryPersonName, int id);

    @Query("UPDATE parcel_table SET possibleDeliveryPersonsList = :possibleList WHERE id = :tid")
    void updatePossibleDeliveryPersonsList(List<String> possibleList, int tid);

    @Query("DELETE FROM parcel_table")
    void deleteAllParcels();

    @Query("SELECT * from parcel_table")
    LiveData<List<Parcel>> getParcels();
}

这是 Parcel 类的一部分:

@Entity(tableName = "parcel_table")
public class Parcel {
    private Enums.ParcelType parcelType;
    private boolean isFragile;
    private Enums.ParcelWeight parcelWeight;
    private LatLng warehouseLocation;
    private String recipientName;
    private LatLng recipientAddress;
    private String recipientEmail;
    private String recipientPhone;
    private String dateReceived;
    private String shippingDate;
    private Enums.ParcelStatus parcelStatus;
    private String deliveryPersonName;
    private String fireBasePushId;
    private List<String> possibleDeliveryPersonsList;
@PrimaryKey(autoGenerate = true)
    @NonNull
    private int id;
        //and more...
}

List<String>类型转换器:

@TypeConverter
    public String listToString(List<String> list) {
        String joined = TextUtils.join(", ", list);
        return joined;
    }

    @TypeConverter
    public List<String> stringToList(String string) {
        List<String> myList = new ArrayList<String>(Arrays.asList(string.split(",")));
        return myList;
    }

我不知道该怎么办，因为 SQLite 代码据说是由 Dao 自动生成的，我对其没有任何影响......

Answer 1

除了鲍勃·斯奈德的答案之外，还有两个解决方法(但需要对其进行彻底测试):

1. 自己“模仿”TypeConverter(从List到String)(这是一件棘手的事情，我没有在实践中尝试过!):

在 DAO 中将 possibleList 类型更改为 String:

@Query("UPDATE parcel_table SET possibleDeliveryPersonsList = :possibleList WHERE id = :tid")
    void updatePossibleDeliveryPersonsList(String possibleList, int tid);

添加转换辅助方法(也可以放在DAO中):

void updatePossibleDeliveryPersonsList(List<String> possibleList, int tid) {
   String listToString = TextUtils.join(", ", possibleList); 
   // copied from your converter, it could be put in some common function to follow DRY
   updatePossibleDeliveryPersonsList(listToString, tid); 
}

并从存储库/ViewModel 调用它:

db.ParcelDao().updatePossibleDeliveryPersonsList(possibleList, tid);

要将 DAO 中的多个 updateXXX 方法替换为单个更新(您的表中有很多字段，可能最好尝试一些通用方法来更新它们的任意组合？):

@Update
void update(Parcel parcel);

添加到您的 DAO 方法中以按 id 搜索地 block :

@Query("SELECT * from parcel_table where id = :id")
Parcel getParcel(int id);

首先在您的 Repository/ViewModel 中获取 Parcel，然后更改它(状态，任何名称)，然后更新数据库:

Parcel parcel = db.ParcelDao().getParcel(id); // let's say it can't be null
parcel.shippingDate = yourShippingDate; // or change here any of your other fields, including list
db.ParcelDao().update(parcel);