sqlite模糊查询

主要是glob、like两种方法.

like可以设置是否区分大小写-默认不区分,_代表一个字符,%代表不确定数量个字符

glob区分大小写,?代表一个字符,*代表不确定数量个字符

示例代码

Class.forName("org.sqlite.JDBC")

String dbPath1 = "C:sqlitetestq.db"

new File(dbPath1).delete()

Connection conn = DriverManager.getConnection("jdbc:sqlite:"+dbPath1)

Statement stat = conn.createStatement()

stat.executeUpdate("create table if not exists stu1(name varchar(20), age int, score int) ")

stat.executeUpdate("insert into stu1 values('A1',12,72)")

stat.executeUpdate("insert into stu1 values('A2',13,73)")

stat.executeUpdate("insert into stu1 values('A3',14,74)")

stat.executeUpdate("insert into stu1 values('D33',14,74)")

//stat.executeUpdate("PRAGMA case_sensitive_like =1")

stat.executeUpdate("PRAGMA case_sensitive_like =0")

ResultSet rs = stat.executeQuery("select s1.name,s1.age,s1.score from stu1 s1 where s1.name like '%3' ")

while (rs.next()) {

System.out.println("name = " + rs.getString("name")+" age:"+rs.getInt("age")+" score:"+rs.getInt("score"))

}

rs.close()

rs = stat.executeQuery("select s1.name,s1.age,s1.score from stu1 s1 where s1.name like 'A_' ")

while (rs.next()) {

System.out.println("name = " + rs.getString("name")+" age:"+rs.getInt("age")+" score:"+rs.getInt("score"))

}

rs.close()

rs = stat.executeQuery("select s1.name,s1.age,s1.score from stu1 s1 where s1.name like 'a_' ")

while (rs.next()) {

System.out.println("name = " + rs.getString("name")+" age:"+rs.getInt("age")+" score:"+rs.getInt("score"))

}

rs.close()

rs = stat.executeQuery("select s1.name,s1.age,s1.score from stu1 s1 where s1.name GLOB 'd3*' ")

while (rs.next()) {

System.out.println("name = " + rs.getString("name")+" age:"+rs.getInt("age")+" score:"+rs.getInt("score"))

}

rs.close()

System.out.println("----")

rs = stat.executeQuery("select s1.name,s1.age,s1.score from stu1 s1 where s1.name glob 'D3?' ")

while (rs.next()) {

System.out.println("name = " + rs.getString("name")+" age:"+rs.getInt("age")+" score:"+rs.getInt("score"))

}

rs.close()

stat.close()

conn.close()

输出

name = A3 age:14 score:74

name = D33 age:14 score:74

name = A1 age:12 score:72

name = A2 age:13 score:73

name = A3 age:14 score:74

name = A1 age:12 score:72

name = A2 age:13 score:73

name = A3 age:14 score:74

----

name = D33 age:14 score:74

你这种全字段匹配查询不能这么写的，这种查询叫全文检索，sqlite有现成的全文检索方案，需要在sqlite上做些配置

官方文档上有写的

http://www.sqlite.org/fts3.html

string sql = "Select Count(ID) As rcount From tbArticle WHERE classID IN(" + ids + ") AND Title LIKE '%'+@Title+'%'"

---