Mybatis-Plus条件コンストラクタクエリー

10459 ワード
Mybatis-Plusが条件コンストラクタ(QueryWrapper)を使用して条件クエリーを行う9つの小さな例
まずuserテーブルを作成する
CREATE TABLE user (
    id BIGINT ( 20 ) PRIMARY KEY NOT NULL COMMENT '  ',
    name VARCHAR ( 30 ) DEFAULT NULL COMMENT '  ',
    age INT ( 11 ) DEFAULT NULL COMMENT '  ',
    email VARCHAR ( 50 ) DEFAULT NULL COMMENT '  ',
    manager_id BIGINT ( 20 ) DEFAULT NULL COMMENT '    id',
    create_time DATETIME DEFAULT NULL COMMENT '    ',
CONSTRAINT manager_fk FOREIGN KEY ( manager_id ) REFERENCES USER ( id ) 
) ENGINE = INNODB CHARSET = UTF8;

複数のデータを挿入
INSERT INTO `user` ( id, name, age, email, manager_id, create_time )
VALUES
    ( 1087982257332887553, ' boss', 40, '[email protected]', NULL, '2019-01-11 14:20:20' ),
    ( 1088248166370832385, '   ', 25, '[email protected]', 1087982257332887553, '2019-02-05 11:12:22' ),
    ( 1088250446457389058, '   ', 28, '[email protected]', 1088248166370832385, '2019-02-14 08:31:16' ),
    ( 1094590409767661570, '   ', 31, '[email protected]', 1088248166370832385, '2019-01-14 09:15:15' ),
    ( 1094592041087729666, '   ', 31, '[email protected]', 1088248166370832385, '2019-01-14 09:48:16' );
  • 名前には「雨」が含まれ、年齢が40 name like'%雨%'and age<40
    • 未満
        @Test
    public void selectByWrapper1() {
        QueryWrapper queryWrapper = new QueryWrapper<>();
        // lt      
        queryWrapper.like("name", " ").lt("age", 40);
        List users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    ログ出力sql:
    DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name LIKE ? AND age < ? DEBUG==>Parameters:%雨%(String)、40(Integer)TRACE<==Columns:id,name,age,email,manager_id, create_time TRACE<==Row:10945904099767661570,張雨琪,31,[email protected],108248166370832385,2019-01-14 09:15:15 TRACE<==Row:1094590410877729666,劉紅雨,31,[email protected], 1088248166370832385, 2019-01-14 09:48:16 DEBUG<== Total: 2
  • の名前には「雨」が含まれており、年齢が20以上40未満であり、emailは空のname like'%雨%and age between 20 and 40 and email is not null
    • ではない.
        @Test
    public void selectByWrapper2() {
        QueryWrapper queryWrapper = new QueryWrapper<>();
        queryWrapper.like("name", " ").between("age", 20, 40).isNotNull("email");
        List users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    ログ出力sql:
    DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name LIKE ? AND age BETWEEN ? AND ? AND email IS NOT NULL DEBUG=>Parameters:%雨%(String),20(Integer),40(Integer)TRACE<==Columns:id,name,age,email,manager_id, create_time TRACE<==Row:10945904099767661570,張雨琪,31,[email protected],108248166370832385,2019-01-14 09:15:15 TRACE<==Row:1094590410877729666,劉紅雨,31,[email protected], 1088248166370832385, 2019-01-14 09:48:16 DEBUG<== Total: 2
  • 名前は「王」姓または年齢が25以上で、年齢降順に並び、年齢が同じid昇順にname like'王%'or age>=25 order by age desc,id,asc 
    •     @Test
    public void selectByWrapper3() {
        QueryWrapper queryWrapper = new QueryWrapper<>();
        // ge        
        queryWrapper.likeRight("name", " ").or().ge("age", 25)
                .orderByDesc("age").orderByAsc("id");
        List users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    ログ出力sql:
    DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name LIKE ? OR age >= ? ORDER BY age DESC,id ASC DEBUG=>Parameters:キング%(String),25(Integer)TRACE<==Columns:id,name,age,email,manager_id, create_time TRACE<==Row:108782257332887553、ビッグボス、40、[email protected],null,2019-01-11 14:20:20 TRACE<==Row:109459040967661570,張雨琪,31,[email protected],108248166370832385,2019-01-14 09:15:15 TRACE<==Row:1094590410877729666,劉紅雨,31,[email protected],1088241667370832385,2019-01-14 09:48:16 TRACE<==Row:108825446457389058,李芸偉,28,[email protected],1088241663370832385,2019-02-14 08:31:16 TRACE<==Row:1088241663370832385,王天風,25,[email protected], 1087982257332887553, 2019-02-05 11:12:22 DEBUG<== Total: 5
  • の作成日は2019年2月14日で、直属の上司の名前は王姓date_format(create_time,'%Y-%m-%d') and manager_id in(select id from user where name like'王%')
    •     @Test
    public void selectByWrapper4() {
        QueryWrapper queryWrapper = new QueryWrapper<>();
        queryWrapper.apply("date_format(create_time,'%Y-%m-%d')={0}", "2019-02-14")
                .inSql("manager_id", "select id from user where name like ' %'");
        List users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    ログ出力sql:
    DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE date_format(create_time,'%Y-%m-%d')=? AND manager_id IN(select id from user where name like'王%')DEBUG=>Parameters:2019-02-14(String)TRACE<==Columns:id,name,age,email,manager_id, create_time TRACE<==Row:108250446457389058、李芸偉、28、[email protected], 1088248166370832385, 2019-02-14 08:31:16 DEBUG<== Total: 1
  • 名前は「王」姓で(年齢が40未満またはメールボックスが空ではない)name like'王%'and(age<40 or email is not null)
    •     @Test
    public void selectByWrapper5() {
        QueryWrapper queryWrapper = new QueryWrapper<>();
        queryWrapper.likeRight("name", " ")
                .and(wq -> wq.lt("age", 40).or().isNotNull("email"));
        List users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    ログ出力sql:
    DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name LIKE ? AND ( age < ? OR email IS NOT NULL )
    DEBUG=>>Parameters:キング%(String)、40(Integer)TRACE<==Columns:id,name,age,email,manager_id, create_time TRACE<==Row:1088241667370832385、王天風、25、[email protected], 1087982257332887553, 2019-02-05 11:12:22 DEBUG<== Total: 1
  • 名前が「王」姓または(年齢40未満、年齢20以上、メールボックスが空でない)name like「王%」or(age<40 and age>20 and email is not null)
    •     @Test
    public void selectByWrapper6() {
        QueryWrapper queryWrapper = new QueryWrapper<>();
        queryWrapper.likeRight("name", " ")
                .or(wq -> wq.lt("age", 40).gt("age", 20).isNotNull("email"));
        List users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    ログ出力sql:
    DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name LIKE ? OR(age?AND email IS NOT NULL)DEBUG=>>Parameters:キング%(String)、40(Integer)、20(Integer)TRACE<==Columns:id,name,age,email,manager_id, create_time TRACE<==Row:1088241667370832385、王天風、25、[email protected],108798257332887553,2019-02-05 11:12:22 TRACE<==Row:108250446457389058,李芸偉,28,[email protected], 1088248166370832385, 2019-02-14 08:31:16
    TRACE<==Row:10945904099767661570,張雨琪,31,[email protected],108248166370832385,2019-01-14 09:15:15 TRACE<==Row:1094590410877729666,劉紅雨,31,[email protected], 1088248166370832385, 2019-01-14 09:48:16 DEBUG<== Total: 4
  • (年齢が40未満またはメールボックスが空ではない)および名前が王姓(age<40 or email is not null)and name like'王%'
    •     @Test
    public void selectByWrapper7() {
        QueryWrapper queryWrapper = new QueryWrapper<>();
        queryWrapper.nested(wq -> wq.lt("age", 40).or().isNotNull("email"))
                .likeRight("name", " ");
        List users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    ログ出力sql:
    DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE ( age < ? OR email IS NOT NULL ) AND name LIKE ? DEBUG=>>Parameters:40(Integer)、キング%(String)TRACE<==Columns:id,name,age,email,manager_id, create_time TRACE<==Row:1088241667370832385、王天風、25、[email protected], 1087982257332887553, 2019-02-05 11:12:22 DEBUG<== Total: 1
  • 年齢30,31,34,35 age in(30,31,34,35)
    •     @Test
    public void selectByWrapper8() {
        QueryWrapper queryWrapper = new QueryWrapper<>();
        queryWrapper.in("age", Arrays.asList(30, 31, 34, 35));
        List users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    ログ出力sql:
    DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE age IN (?,?,?,?) DEBUG==> Parameters: 30(Integer), 31(Integer), 34(Integer), 35(Integer) TRACE<== Columns: id, name, age, email, manager_id, create_time TRACE<==Row:10945904099767661570,張雨琪,31,[email protected],108248166370832385,2019-01-14 09:15:15 TRACE<==Row:1094590410877729666,劉紅雨,31,[email protected], 1088248166370832385, 2019-01-14 09:48:16 DEBUG<== Total: 2
  • 条件を満たす文の1つだけを返すlimit 1 
    •     @Test
    public void selectByWrapper9() {
        QueryWrapper queryWrapper = new QueryWrapper<>();
        //             sql    
        //     :
        //       ,             sql     ,     
        queryWrapper.in("age", Arrays.asList(30, 31, 34, 35)).last("limit 1");
        List users = userMapper.selectList(queryWrapper);
        users.forEach(System.out::println);
    }

    ログ出力sql:
    DEBUG==> Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE age IN (?,?,?,?) limit 1 DEBUG==> Parameters: 30(Integer), 31(Integer), 34(Integer), 35(Integer) TRACE<== Columns: id, name, age, email, manager_id, create_time TRACE<==Row:10945904099767661570,張雨琪,31,[email protected], 1088248166370832385, 2019-01-14 09:15:15 DEBUG<== Total: 1
    macOSでのnumbaインストール時のエラー解決
    C++ __STL学習プロセス