“不是” Java / SQLite 应用程序中的语法错误

Question

我正在开发一个带有 SQLite 数据库的 Java 应用程序。我必须执行这个查询：

select * from service 
where (tache IS NOT 'I') 
AND (idequiv IS null OR idequiv = '' OR idequiv<=0) 
union 
select * from service where (idequiv IS NOT null) 
and (tache IS NOT 'I') 
group by num_service 
order by num_service;

这在 SQLiteStudio 中运行良好，但在我的应用程序中我得到了这个异常：

 java.sql.SQLException: near "'I'": syntax error    at
 org.sqlite.DB.throwex(DB.java:288)     at
 org.sqlite.NativeDB.prepare(Native Method)     at
 org.sqlite.DB.prepare(DB.java:114)     at
 org.sqlite.PrepStmt.<init>(PrepStmt.java:37)   at
 org.sqlite.Conn.prepareStatement(Conn.java:231)    at
 org.sqlite.Conn.prepareStatement(Conn.java:224)    at
 org.sqlite.Conn.prepareStatement(Conn.java:213)

知道为什么吗？ 我很绝望..

Answer 1

IS NOT 和 IS 用于 NULL 值。使用 <> 或 NOT a = b 作为值。

SELECT * FROM service 
WHERE (NOT tache = 'I') 
AND (idequiv IS null OR idequiv = '' OR idequiv<=0) 
UNION
SELECT * FROM service 
WHERE (idequiv IS NOT null) 
AND (NOT tache = 'I') 
GROUP BY num_service 
ORDER BY num_service;