Service CRUD 接口
public String services() {Boolean re = false;/**Service CRUD 接口**//**Save 返回boolean **///1、插入一条数据Person person1 = new Person();person1.setEmail("123@qq.com");person1.setSex("男");//person1.setUser_id(0);//影响数量小于1就会返回false null != result && result >= 1;re = personService.save(person1); //Boolean//System.out.println(re);//System.out.println(person1); //数据已经返回//2、插入（批量）List<Person> pl1 = new ArrayList<>();Person person2 = new Person();person2.setEmail("123213@qq.com");person2.setSex("男");pl1.add(person2);pl1.add(person2);pl1.add(person2);person2.setSex("男男"); //被批量修改//全部插入//personService.saveBatch(pl1);//多次插入每次插入2条re = personService.saveBatch(pl1, 2);//pl1.forEach(System.out::println);//3577null男男123213@qq.com//3577null男男123213@qq.com//3577null男男123213@qq.comList<Person> pl2 = new ArrayList<>();for (int j = 0; j<5; j++){Person person31 = new Person();person31.setEmail("123213@qq.com");person31.setSex("男");pl2.add(person31);}re = personService.saveBatch(pl2, 2);pl2.forEach(System.out::println);//3587null男123213@qq.com//3588null男123213@qq.com//3589null男123213@qq.com//3590null男123213@qq.com//3591null男123213@qq.com/**SaveOrUpdate 返回boolean **///3、插入或更新//3.1 TableId条件 注解存在更新记录，否插入一条记录Person person3 = new Person();person3.setEmail("12343332@qq.com");person3.setSex("女");//INSERT INTO person ( sex, email ) VALUES ( ?, ? )re = personService.saveOrUpdate(person3);//System.out.println(person3);person3.setUser_id(3562);personService.saveOrUpdate(person3);//SELECT user_id,username,sex,email FROM person WHERE user_id=?//UPDATE person SET sex=?, email=? WHERE user_id=?//System.out.println(person3);//3.2 根据条件 updateWrapper尝试更新，否继续执行saveOrUpdate(T)方法（TableId 注解存在更新记录，否插入一条记录）Person person32 = new Person();person32.setEmail("12343332@qq.com");person32.setSex("女");person32.setUser_id(3561);UpdateWrapper<Person> personWrapper = new UpdateWrapper<>();personWrapper.isNull("username");personWrapper.eq("sex", "女");//UPDATE person SET sex=?, email=? WHERE (username IS NULL AND sex = ?)//SELECT user_id,username,sex,email FROM person WHERE user_id=?//INSERT INTO person ( sex, email ) VALUES ( ?, ? )re = personService.saveOrUpdate(person32, personWrapper);//saveOrUpdateBatch(Collection<T> entityList);//saveOrUpdateBatch(Collection<T> entityList, int batchSize);/**Remove 返回boolean **///4、根据条件 queryWrapper删除记录QueryWrapper<Person> personWrapper2 = new QueryWrapper<>();personWrapper2.isNotNull("username");//DELETE FROM person WHERE (username IS NOT NULL)re = personService.remove(personWrapper2);//根据 ID 删除//DELETE FROM person WHERE user_id=?re = personService.removeById(10);//删除（根据ID 批量删除）//DELETE FROM person WHERE user_id IN ( ? , ? )re = personService.removeByIds(Arrays.asList(12,13));//根据 columnMap 条件，删除记录HashMap<String, Object> rePerson = new HashMap<>();rePerson.put("username", "订单1");//DELETE FROM person WHERE username = ?re = personService.removeByMap(rePerson);/**Update 返回boolean **///5、更新 根据 UpdateWrapper 条件，更新记录 需要设置sqlsetUpdateWrapper<Person> UpdateWrapper3 = new UpdateWrapper<>();UpdateWrapper3.isNotNull("username").set("username", "5");//UPDATE person SET username=? WHERE (username IS NOT NULL)re = personService.update(UpdateWrapper3);re = personService.update(null, UpdateWrapper3);Person person33 = new Person();person33.setEmail("12343332@qq.com");person33.setSex("女");person33.setUser_id(3561);//根据 ID 选择修改//UPDATE person SET sex=?, email=? WHERE user_id=?re = personService.updateById(person33);//System.out.println(re);//根据 whereWrapper 条件，更新记录Person person34 = new Person();person34.setEmail("12343332@qq.com");person34.setSex("女");person34.setUser_id(3561);QueryWrapper<Person> personWrapper3 = new QueryWrapper<>();personWrapper3.isNotNull("username");//UPDATE person SET sex=?, email=? WHERE (username IS NOT NULL)//ID已过滤//相同数据多次更新任然会返回truere = personService.update(person34, personWrapper3);//根据ID 批量更新//updateBatchById(Collection<T> entityList);//根据ID 批量更新//updateBatchById(Collection<T> entityList, int batchSize)//6、链式查询//6.1 根据 ID 查询Person person35 = new Person();//SELECT user_id,username,sex,email FROM person WHERE user_id=?person35 = personService.getById(3561);//null null 女 12343332@qq.com//System.out.println(person35);//6.2 根据 Wrapper，查询一条记录。结果集，如果是多个会抛出异常，随机取一条加上限制条件 wrapper.last("LIMIT 1")QueryWrapper<Person> personWrapper4 = new QueryWrapper<>();personWrapper4.isNull("username");personWrapper4.last("LIMIT 1");//SELECT user_id,username,sex,email FROM person WHERE (username IS NULL) LIMIT 1//SELECT user_id,username,sex,email FROM person WHERE (username IS NULL)//Warn: execute Method There are 2 results.person35 = personService.getOne(personWrapper4, false); //多个不抛出异常//System.out.println(person35);//null null 女 12343332@qq.com//SELECT user_id,username,sex,email FROM person WHERE (username IS NULL)person35 = personService.getOne(personWrapper4); //多个抛出异常//System.out.println(person35);//6.3 根据 Wrapper，查询一条记录QueryWrapper<Person> personWrapper5 = new QueryWrapper<>();personWrapper5.eq("sex", "女");Map<String, Object> list2 = new HashMap<>();//SELECT user_id,username,sex,email FROM person WHERE (sex = ?)list2 = personService.getMap(personWrapper5);//{user_id=3561, sex=女, email=12343332@qq.com}//System.out.println(list2);//6.4 根据 Wrapper，查询一条记录//SELECT user_id,username,sex,email FROM person WHERE (sex = ?)Integer nn = personService.getObj(personWrapper5, (o) -> {//单条记录的第一个字段值System.out.println(o); //3561return 1;});//System.out.println(nn);//7、list列表查询//7.1 查询所有List<Person> pl3 = new ArrayList<>();//SELECT user_id,username,sex,email FROM personpl3 = personService.list();pl3.forEach(System.out::println);//null null 女 12343332@qq.com//null null 3 4//7.1 查询列表QueryWrapper<Person> personWrapper6 = new QueryWrapper<>();personWrapper6.eq("sex", "女");//SELECT user_id,username,sex,email FROM person WHERE (sex = ?)pl3 = personService.list(personWrapper6);//pl3.forEach(System.out::println);//7.2 查询（根据ID 批量查询）//SELECT user_id,username,sex,email FROM person WHERE user_id IN ( ? , ? )pl3 = personService.listByIds(Arrays.asList(3561, 3563));//pl3.forEach(System.out::println);//7.3 查询（根据 columnMap 条件）//Map 里的 key 为表字段名，而不是对应实体类的属性名Map<String,Object> map = new HashMap<>();map.put("sex", "女");//SELECT user_id,username,sex,email FROM person WHERE sex = ?pl3 = personService.listByMap(map);//pl3.forEach(System.out::println);//7.4 查询所有列表//List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();List<Map<String, Object>> list;//SELECT user_id,username,sex,email FROM personlist = personService.listMaps();//list.forEach(System.out::println);//{user_id=3561, sex=女, email=12343332@qq.com}//{user_id=3562, sex=3, email=4}//SELECT user_id,username,sex,email FROM person WHERE (sex = ?)list = personService.listMaps(personWrapper6);//list.forEach(System.out::println);//7.5 根据 Wrapper 条件，查询全部记录//SELECT user_id,username,sex,email FROM personList<Object> list4 = personService.listObjs();//System.out.println(list4); //[3561, 3562]//还可以传入个自定义的转换函数List<Object> list3 = personService.listObjs((o) -> {return "用户" + o.toString();});//System.out.println(list3);//[用户3561, 用户3562]//8、分页//SELECT COUNT(*) FROM person WHERE (sex = ?)//SELECT user_id,username,sex,email FROM person WHERE (sex = ?) LIMIT ?IPage<Person> page = personService.page(new Page<>(1,5), personWrapper6);//System.out.println(page.getRecords());//[null null 女 12343332@qq.com]IPage<Map<String, Object>> page2 = personService.pageMaps(new Page<>(1,5), personWrapper6);//System.out.println(page2.getRecords());//[{user_id=3561, sex=女, email=12343332@qq.com}]//8、数量//SELECT COUNT( * ) FROM personInteger n2 = personService.count();//count(Wrapper<T> queryWrapper);//System.out.println(n2);/**Chain query **///8、 链式查询 普通//QueryChainWrapper<Person> query();// 链式查询 lambda 式。注意：不支持 Kotlin//LambdaQueryChainWrapper<T> lambdaQuery();// 链式更改 普通//UpdateChainWrapper<T> update();// 链式更改 lambda 式。注意：不支持 Kotlin//LambdaUpdateChainWrapper<T> lambdaUpdate();//如果是多个会抛出异常，随机取一条加上限制条件 wrapper.last("LIMIT 1")//SELECT user_id,username,sex,email FROM person WHERE (user_id = ?) LIMIT 1Person p0 = personService.query().eq("user_id", "3561").last("LIMIT 1").one();//nullnull女12343332@qq.com//SELECT user_id,username,sex,email FROM person WHERE (email = ?)List<Person> p1 = personService.query().eq("email", "12343332@qq.com").list();//nullnull女12343332@qq.com//SELECT user_id,username,sex,email FROM person WHERE (email = ?)List<Person> p2 = personService.lambdaQuery().eq(Person::getEmail, "12343332@qq.com").list();//p2.forEach(System.out::println);//UPDATE person SET email=? WHERE (email = ?)re = personService.lambdaUpdate().eq(Person::getEmail, "123@qq.com").set(Person::getEmail, "123@qq.com").update();//System.out.println(re);return "ok";}
Mapper CRUD 接口
public String maps() {/**Mapper CRUD 接口**//**查询**///查询多条记录//SELECT user_id,username,sex,email FROM personList<Person> personList = personMapper.selectList(null);for (Person person:personList) {System.out.println(person.getUsername());System.out.println(person.getUser_id());}//通过Id查询单条记录//SELECT user_id,username,sex,email FROM person WHERE user_id=?Person p0 = personMapper.selectById(213); //null/PersonSystem.out.println(p0);//通过多个Id查询单条记录//SELECT user_id,username,sex,email FROM person WHERE user_id IN ( ? , ? )personList = personMapper.selectBatchIds(Arrays.asList(213, 3425)); //集合for (Person person:personList) {System.out.println(person.getUsername());System.out.println(person.getUser_id());}//map条件查询//SELECT user_id,username,sex,email FROM person WHERE sex = ? AND username = ?HashMap<String, Object> map1 = new HashMap<>();map1.put("username", "hhhh");map1.put("sex", "man");personList = personMapper.selectByMap(map1);personList.forEach(System.out::println);//查询条件构造器QueryWrapper<Person> personWrapper = new QueryWrapper<>();personWrapper.isNotNull("username").isNull("username").ge("user_id", 10).eq("user_id", 10).between("user_id", 0, 10).notBetween("user_id", 0, 10).like("username", "") //%username%.notLike("username", "").likeRight("username", "") //username%.likeLeft("username", "") //%username.inSql("user_id", "select user_id from users where user_id < 4") //user_id IN (select user_id from users where user_id < 4).orderByAsc("user_id").orderByDesc("username");//SELECT user_id,username,sex,email FROM person WHERE// (username IS NOT NULL AND username IS NULL AND user_id >= ? AND user_id = ? AND user_id BETWEEN ? AND ? AND user_id NOT BETWEEN ? AND ? AND username LIKE ? AND username NOT LIKE ? AND username LIKE ? AND username LIKE ? AND user_id IN (select user_id from users where user_id < 4)) ORDER BY user_id ASC,username DESCpersonList = personMapper.selectList(personWrapper);personList.forEach(System.out::println);Integer count = personMapper.selectCount(personWrapper);System.out.println(count);p0 = personMapper.selectOne(personWrapper);System.out.println(p0);// 根据 Wrapper 条件，查询全部记录personWrapper = new QueryWrapper<>();personWrapper.ge("user_id", 10);List<Map<String, Object>> personList20 = personMapper.selectMaps(personWrapper);//{user_id=3451, sex=女, email=23@qq.com, username=订单}personList20.forEach(System.out::println);// 根据 Wrapper 条件，查询全部记录。注意： 只返回第一个字段的值List<Object> personList30 = personMapper.selectObjs(personWrapper);personList30.forEach(System.out::println);//分页// 根据 entity 条件，查询全部记录（并翻页）//IPage<T> selectPage(IPage<T> page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);// 根据 Wrapper 条件，查询全部记录（并翻页）//IPage<Map<String, Object>> selectMapsPage(IPage<T> page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);//SELECT user_id,username,sex,email FROM person WHERE (username = ?) LIMIT 2Page<Person> page1 = Page.of(1, 2);//SELECT user_id,username,sex,email FROM person WHERE (username = ?) LIMIT 2,2//Page<Person> page1 = Page.of(2, 2);QueryWrapper<Person> queryWrapper = new QueryWrapper<>();queryWrapper.eq("username","订单");personMapper.selectPage(page1, queryWrapper);page1.getRecords().forEach(System.out::println);Page<Map<String, Object>> page2 = Page.of(1, 2);personMapper.selectMapsPage(page2, queryWrapper);page2.getRecords().forEach(System.out::println);System.out.println(page2.getTotal());page2 = Page.of(1, 2, true);personMapper.selectMapsPage(page2, queryWrapper);page2.getRecords().forEach(System.out::println);System.out.println(page2.getTotal());/**新增**///新增Person p1 = new Person();p1.setSex("女");p1.setEmail("23@qq.com");p1.setUsername("订单");//entity 实体对象int i1 = personMapper.insert(p1); //返回影响数量/**更新 返回影响行数**///通过ID更新Person p10 = new Person();p10.setUser_id(3606);p10.setUsername("hhhh");i1 = personMapper.updateById(p10); //更新相同数据还是返回数量//根据构造器更新p10 = new Person();p10.setUsername("hhhh-up");QueryWrapper<Person> personWrapper2 = new QueryWrapper<>();personWrapper2.eq("username", "hhhh");i1 = personMapper.update(p10, personWrapper2);//全部更新//i1 = personMapper.update(p10, null);/**删除 返回影响行数**///通过ID删除i1 = personMapper.deleteById(3439);//通过多ID删除i1 = personMapper.deleteBatchIds(Arrays.asList(3438, 3437));//通过map删除map1 = new HashMap<>();map1.put("username", "订单22");i1 = personMapper.deleteByMap(map1);//根据构造器，删除记录personWrapper = new QueryWrapper<>();personWrapper.ge("user_id", 1000000);i1 = personMapper.delete(personWrapper);//全部删除 DELETE FROM person//i1 = personMapper.delete(null);return "ok";}
条件构造器
public String querys() {//AbstractWrapper//QueryWrapper(LambdaQueryWrapper) 和 UpdateWrapper(LambdaUpdateWrapper) 的父类//用于生成 sql 的 where 条件, entity 属性也用于生成 sql 的 where 条件//注意: entity 生成的 where 条件与 使用各个 api 生成的 where 条件没有任何关联行为// eq= ne<> gt> ge>= lt< le<=// between notBetween like notLike likeLeft likeRight notLikeLeft notLikeRight// isNull isNotNull in notIn// inSql notInSql groupBy orderByAsc orderByDesc orderBy having// and or nested apply exists notExistsList<Person> pl3;QueryWrapper<Person> personWrapper = new QueryWrapper<>();personWrapper.eq("peq", "1").eq(true, "peq", "2").eq(false, "peq", "3").ne("pne", "1").between("pbetween", 10, 20).like("plike", "likes").isNull("pisnull").in("pin", Arrays.asList(1, 2, 3)).inSql("pinsql", "1,2,3").inSql("pinsql2", "select id from place where country < 3").groupBy("id", "ids").orderByAsc("id3", "id4").orderByDesc("id4")//.having("sum(id) > 10").func(i -> {if( true ){i.like("id4", "nn");}else{i.like("id4", "tt");}}).and(i -> {i.eq("id5", "nn").or().eq("id5", "nnn");}).and(i -> {//正常嵌套 不带 AND 或者 ORi.nested(j -> {j.eq("id6", "ns");});})//正常嵌套 不带 AND 或者 OR.nested(j -> {j.eq("id6", "ns");})//拼接 sql.apply("id7=5").last("LIMIT 1").select("user_id", "username", "sex");//SELECT user_id,username,sex,email FROM person WHERE (peq = ? AND peq = ? AND pne <> ? AND pbetween BETWEEN ? AND ? AND plike LIKE ? AND pisnull IS NULL AND pin IN (?,?,?) AND pinsql IN (1,2,3) AND pinsql2 IN (select id from place where country < 3) AND id4 LIKE ? AND (id5 = ? OR id5 = ?) AND ((id6 = ?)) AND (id6 = ?) AND id7=5) GROUP BY id,ids ORDER BY id3 ASC,id4 ASC,id4 DESC LIMIT 1pl3 = personService.list(personWrapper);UpdateWrapper<Person> personWrapper2 = new UpdateWrapper<>();personWrapper2.set("sex", "3").setSql("name = 'name'");return "ok";}
事务
public String sqlTs() {TransactionStatus transactionStatus = dataSourceTransactionManager.getTransaction(transactionDefinition);try {Person person1 = new Person();person1.setEmail("123@qq.com");person1.setSex("男");personService.save(person1);personService.save(person1);dataSourceTransactionManager.commit(transactionStatus); // 手动提交} catch (Exception e) {System.out.println("事务回滚 ");e.printStackTrace();dataSourceTransactionManager.rollback(transactionStatus); // 事务回滚}return "ok";}
