构造复杂的Where条件

实际的应用中，Where 条件都是比较复杂的

下面介绍下如何使用OQL构造复杂的Where条件

1、先来一个最简单的

 val q = OQL.From(book).Select().Where {
            cmp ->
            cmp.Comparer(book.testId, ">", 0)
        }
        .END

|--------------------------------------------------|
|  finalSql      |  SELECT  *                      |
|                |                                 |
|                |  FROM `test_book`               |
|                |                                 |
|                |       WHERE  `test_id` > '0'    |
|--------------------------------------------------|

2、JoinTable的情况

@Test
    fun testJoinTable() {
        val user = User()
        user.name = "abc"
        user.age = 19


        val book = TestBook()
        book.testName = "abc"
        book.testId = "777"


        val select1 = OQL.From(user)
                .LeftJoin(book).On(book.testName, user.name)
                .Limit(10, 1, true)
                .Select(user.id, book.testId, user.name, book.testName)
                .Where(book.testName, user.name)
                .OrderBy(user.id, "desc")
                .END

        val ss = getDB().select<Map<String, Any?>>(select1)
        println("ss = ${ss}")
    }


|------------------------------------------------------------------------------------------------------------------|
|  finalSql      |  SELECT    M.`id`, T0.`test_id` AS `t0_test_id`,  M.`name`, T0.`test_name` AS `t0_test_name`    |
|                |                                                                                                 |
|                |  FROM `user` M                                                                                  |
|                |                                                                                                 |
|                |  LEFT JOIN `test_book` T0  ON  T0.`test_name` = M.`name`                                        |
|                |                                                                                                 |
|                |       WHERE  T0.`test_name`='abc' AND  M.`name`='abc'                                           |
|                |                                                                                                 |
|                |  ORDER BY  M.`id` desc                                                                          |
|                |                                                                                                 |
|                |   LIMIT 10                                                                                      |
|------------------------------------------------------------------------------------------------------------------|

ss = [{id=33, t0_test_id=777, name=abc, t0_test_name=abc}]

//todo: 当前版本joinTable最后生成的是个map，后续版本解决生成的联合表与实体表的对应情况

3、复杂Where条件

 @Test
    fun testWhereMultiple() {

        val book = TestBook()
        book.testName = "abc"
        book.testId = "777"

        val cname = "0000"

        val q = OQL
                .From(book)
                .Select()
                .Where {
                    cmp ->
                    cmp.Comparer(book.testId, ">", "55") OR
                            (
                                    cmp.Comparer(book.testName, "=", cname) AND
                                            (
                                                    cmp.Comparer(book.testId,"<>", 10) OR
                                                            cmp.Comparer(book.testId, ">", 19)
                                            )
                            )
                }
                .END


        val ss:List<TestBook>? = getDB().select<TestBook>(q)
        println("List TestBook = ${ss}")
    }


//SELECT  *    FROM `test_book`       
// WHERE   `test_id` > '55'   OR   (   `test_name` = '0000' AND   ( `test_id` <> '10' OR  `test_id` > '19' )   )     

说明

1、当前可以使用 cmp AND cmp 或者cmp OR cmp进行组合

2、cmp.Comparer(book.testName, "=", “abc”) 等同 where test_name = 'abc'

比较符合可以使用下面的这些

"="、"<>"、">"、">="、"<"、"<="、"like"、"in"、"not in"、"is"、"is not"