Here is my query
I am trying to search property by given params if the given param is null then it should take all properties and don't filter
@Query("""SELECT * FROM Property
WHERE
(listedFor = :lookingTo OR :lookingTo IS NULL) AND
(propertyType IN (:propertyType) OR :propertyType IS NULL) AND
(areaId IN(:areasOfProperties) OR :areasOfProperties IS NULL) AND
cityId = :cityId
ORDER BY registrationDate DESC""")
fun getPropertyListBySearchQuery(
lookingTo: String?,
propertyType: ArrayList<String>?,
areasOfProperties: ArrayList<String>?,
cityId: String,
) : LiveData<List<Property>>
This is giving me crash as below
java.lang.RuntimeException: Exception while computing database live data.
at androidx.room.RoomTrackingLiveData$1.run(RoomTrackingLiveData.java:92)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1133)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:607)
at java.lang.Thread.run(Thread.java:761)
Caused by: android.database.sqlite.SQLiteException: near ",": syntax error (code 1): , while compiling: SELECT * FROM Property
WHERE
(listedFor = ? OR ? IS NULL) AND
(propertyType IN (?,?) OR ?,? IS NULL) AND
(areaId IN() OR IS NULL) AND
cityId = ?
ORDER BY registrationDate DESC
at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:965)
at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:576)
at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1353)
at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1328)
at androidx.sqlite.db.framework.FrameworkSQLiteDatabase.query(FrameworkSQLiteDatabase.java:183)
at androidx.room.RoomDatabase.query(RoomDatabase.java:530)
at androidx.room.util.DBUtil.query(DBUtil.java:86)
at com.townproperties.data.localdata.LocalDao_Impl$23.call(LocalDao_Impl.java:1515)
at com.townproperties.data.localdata.LocalDao_Impl$23.call(LocalDao_Impl.java:1512)
at androidx.room.RoomTrackingLiveData$1.run(RoomTrackingLiveData.java:90)
Can we pass nullable Argument in room query?
Yes but it's a little convoluted.
Brief Explanation
The issue that you have shown is not an issue with whether or not one of the args is NULL (in which case I believe you would encounter an NPE (null pointer exception)) but that you are trying to check a list (CSV) as if it were a single value and thus the comma results in the syntax error.
?*'s indicate to SQLite that the value will be a bound value - see https://www.sqlite.org/c3ref/bind_blob.htmlA way around this would be to build the SQL and use an @RawQuery. the SQL being built in a function with a body. The following is a working demonstration:-
First the @Dao annotated interface :-
@Dao
interface PropertyDao {
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(property: Property)
@RawQuery
fun rawQueryPropertyList(rawQuery: SimpleSQLiteQuery): List<Property>
fun getPropertiesBySearchCriteria(
lookingTo: String?,
propertyType: ArrayList<String>?,
areasOfProperties: ArrayList<String>?,
cityId: String
): List<Property> {
var afterFirstInList: Boolean = false
var whereAdded = false
val baseSQL = "SELECT * FROM Property"
val qry: StringBuilder = java.lang.StringBuilder().append(baseSQL)
/* Looking To */
if (lookingTo != null && lookingTo.length > 0) {
if (!whereAdded) {
qry.append(" WHERE ")
whereAdded = true
}
if (qry.length > baseSQL.length) qry.append(" AND ")
qry.append(" listedFor='${lookingTo}'")
}
/* Property Type */
if (propertyType!= null && propertyType.size > 0) {
afterFirstInList = false
if (qry.length > baseSQL.length) qry.append(" AND ")
if (!whereAdded) {
qry.append(" WHERE ")
whereAdded = true
}
qry.append(" propertyType IN (")
for (s in propertyType) {
if (afterFirstInList) qry.append(",")
qry.append("'${s}'")
afterFirstInList = true
}
qry.append(")")
}
if (areasOfProperties!=null && areasOfProperties?.size!! > 0) {
afterFirstInList = false
if (qry.length > baseSQL.length) qry.append(" AND ")
if (!whereAdded) {
qry.append(" WHERE ")
whereAdded = true
}
qry.append(" areaId IN (")
for (s in areasOfProperties) {
if (afterFirstInList) qry.append(",")
qry.append("'${s}'")
afterFirstInList = true
}
qry.append(")")
}
if (qry.length > baseSQL.length) qry.append(" AND ")
if (!whereAdded) {
qry.append(" WHERE ")
whereAdded = true
}
qry.append(" cityId='${cityId}' ")
qry.append(" ORDER BY registrationDate DESC")
Log.d("PRPRTYINFO_SQL",qry.toString()) /* ONLY FOR TESTING */
return rawQueryPropertyList(SimpleSQLiteQuery(qry.toString()))
}
}
The '@Entity' annotated Property data class used to test (obviously probably not what you have, but built according to what is available from the question). differences would probably not matter that much if at all:
@Entity
data class Property(
@PrimaryKey
var propertyId: Long?=null,
var propertyType: String,
var areaId: Long,
var cityId: Long,
var listedFor: Long,
var registrationDate: Long
)
A pretty standard @Database annotated class was used. Note that for brevity and convenience .allowMainThreadQueries was used.
Finally the activity code used to perform the limited testing:-
class MainActivity : AppCompatActivity() {
lateinit var db: PropertyDatabase
lateinit var dao: PropertyDao
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db = PropertyDatabase.getInstance(this)
dao = db.getPropertyDao()
dao.insert(Property(null,"House",100,1090,100,System.currentTimeMillis() ))
dao.insert(Property(null,"Flat",200,2090,200,System.currentTimeMillis()))
dao.insert(Property(null,"Villa",300,3090,300,System.currentTimeMillis()))
logProperties(dao.getPropertiesBySearchCriteria("", arrayListOf(""), arrayListOf(""),"1090"),"T1")
logProperties(
dao.getPropertiesBySearchCriteria(
lookingTo = "",
propertyType = arrayListOf("House","Flat","Villa","Apartment","Castle"),
areasOfProperties = arrayListOf("1","2","100","200","300","1000000000"),
cityId = "1090"
),
"T2"
)
logProperties(dao.getPropertiesBySearchCriteria(null, null, null,"1090"),"T3")
}
fun logProperties(plist: List<Property>, tagSuffix: String ) {
val TAG = "PRPRTYINFO_${tagSuffix}"
for (p in plist) {
Log.d(TAG,"Type=${p.propertyType} AreaId=${p.areaId} CityId=${p.cityId} ListedFor=${p.listedFor} RDate=${Date(p.registrationDate)}")
}
}
}
When run for a fresh install (only deigned to run the once) then the Log includes:-
2022-09-26 12:35:46.722 D/PRPRTYINFO_SQL: SELECT * FROM Property WHERE propertyType IN ('') AND areaId IN ('') AND cityId='1090' ORDER BY registrationDate DESC
2022-09-26 12:35:46.728 D/PRPRTYINFO_SQL: SELECT * FROM Property WHERE propertyType IN ('House','Flat','Villa','Apartment','Castle') AND areaId IN ('1','2','100','200','300','1000000000') AND cityId='1090' ORDER BY registrationDate DESC
2022-09-26 12:35:46.741 D/PRPRTYINFO_T2: Type=House AreaId=100 CityId=1090 ListedFor=100 RDate=Mon Sep 26 12:35:46 GMT+10:00 2022
2022-09-26 12:35:46.741 D/PRPRTYINFO_SQL: SELECT * FROM Property WHERE cityId='1090' ORDER BY registrationDate DESC
2022-09-26 12:35:46.742 D/PRPRTYINFO_T3: Type=House AreaId=100 CityId=1090 ListedFor=100 RDate=Mon Sep 26 12:35:46 GMT+10:00 2022
An alternative that wouldn't require building the SQL at run time and using a rawquery (unchecked at compile time, although as per your example the compile time check isn't 100% anyway), would be to utilise CTE (Common Table expressions (temporary tables within the query)). However this could well be more complex than building the SQL due to having to manage the lists as well as the lack of the lists.