原生mybatis单表的增删改查

原生mybatis单表的增删改查

sql

create table goods( id int auto_increment, goods_name varchar(30) collate utf8_unicode_ci null, brand varchar(30) collate utf8_unicode_ci null, gmt_time timestamp null, constraint goods_id_uindex unique (id)) comment '商品表' charset = utf8;alter table goods add primary key (id);INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (2, '苹果手机', 'A', '2021-11-29 11:12:08');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (3, '苹果手机', 'B', '2021-11-29 11:12:12');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (4, '苹果手机', 'C', '2021-11-29 11:12:11');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (5, '苹果手机', 'D', '2021-11-29 11:12:11');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (6, 'D', 'D', '2021-11-29 11:12:13');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (7, 'D', 'D', '2021-11-29 11:12:13');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (8, 'D', 'D', '2021-11-29 11:12:14');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (9, 'A', 'D', '2021-11-29 11:12:14');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (10, 'B', 'D', '2021-11-29 11:12:15');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (11, 'D', 'D', '2021-11-29 11:12:16');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (12, 'D', 'D', '2021-11-29 11:12:16');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (13, 'koala', 'benci', '2021-11-29 11:12:17');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (14, 'D', 'D', '2021-11-29 11:12:17');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (15, 'Z', 'Apple', '2021-11-29 11:12:19');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (16, 'M', 'D', '2021-11-29 11:12:18');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (17, 'D', 'D', '2021-11-29 11:12:21');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (18, 'U', 'D', '2021-11-29 11:12:21');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (19, 'J', 'D', '2021-11-29 11:12:20');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (20, 'F', 'D', '2021-11-29 11:12:23');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (21, 'D', 'D', '2021-11-29 11:12:23');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (22, 'G', 'D', '2021-11-29 11:12:22');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (23, 'X', 'D', '2021-11-29 11:12:28');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (24, 'D', 'D', '2021-11-29 11:12:29');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (25, 'H', 'D', '2021-11-29 11:12:30');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (26, 'D', 'D', '2021-11-29 11:12:26');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (28, 'O', 'D', '2021-11-29 11:12:25');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (29, 'S', 'D', '2021-11-29 11:12:24');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (30, 'R', 'D', '2021-11-29 11:12:31');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (31, 'L', 'D', '2021-11-29 11:12:32');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (32, 'Y', 'D', '2021-11-29 11:12:32');INSERT INTO mybaties.goods (id, goods_name, brand, gmt_time) VALUES (36, '科学', 'Apple', '2021-11-29 20:32:25');

增加

@Test void test1(){ Goods goods = new Goods(); goods.setBrand("Apple"); goods.setGmtTime(LocalDateTime.now()); goods.setUsername("科学"); goodsMapper.koalaAddGood(goods); } Long koalaAddGood(@Param("goods") Goods goods);

insert into goods (username,brand,gmt_time) values (#{goods.username},#{goods.brand},#{goods.gmtTime})

删除

// 测试删除 @Test void test3(){ goodsMapper.koalaDeleteGoods(27L); ArrayList ls = new ArrayList<>(); ls.add(34L); ls.add(35L); goodsMapper.deleteBatchIds(ls); } Long koalaDeleteGoods(Long id);

delete from goods where id = #{id}

更新语句

@Test public void test4(){ Goods goods = new Goods(); goods.setId(13); goods.setUsername("koala"); goods.setBrand("benci"); goodsMapper.koalaUpdateGoods(goods); } Long koalaUpdateGoods(Goods goods);

update goods username = #{username}, brand = #{brand}, gmt_time = #{gmtTime} where id = #{id}

查询语句

id查询

@Test public void test5(){ Goods goods = goodsMapper.koalaQuery(10L); System.out.println(goods.toString()); } Goods koalaQuery(Long id);

select from goods where 1=1 and id = #{id}

查询全部

@Test public void test7(){ List goods = goodsMapper.koalaAll(); System.out.println(JSON.toJSONString(goods)); } List koalaAll();

select from goods

分页查询

官方文档

IPage selectPageVo(IPage page, Integer state);// or (class MyPage extends Ipage{ private Integer state; })MyPage selectPageVo(MyPage page);// orList selectPageVo(IPage page, Integer state);

问题

什么时候分页-拦截 猜测是存在IPage 参数的时候

IPage IPage 按条件分分页查询

// 按条件查询 @Test public void test8(){ Page page = new Page(); page.setCurrent(1); page.setSize(5); Goods goods = new Goods(); goods.setBrand("Apple"); IPage iPage = goodsMapper.koalaIPage(page, goods); System.out.println(JSON.toJSONString(iPage)); } // IPage ipage IPage koalaIPage(@Param("page") IPage page,@Param("goods") Goods goods);

select from goods where brand = #{goods.brand}

分页查询List 结果

// List IPage List koalaPage(@Param("page") IPage page); @Test public void test6(){ Page page = new Page(); page.setCurrent(1); page.setSize(5); List list = goodsMapper.koalaPage(page); System.out.println(JSON.toJSONString(list)); }

select from goods

Mybaties 查询排序

日志

IPage IPage 分页排序异常 会帮我加Totalorder by username asc ) TOTAL所以就用IPage 自带的排序IPage 没有自带的排序自己存手写排序 无法根据字符串排序

@Testpublic void test9(){ com.koala.model.Page page = new com.koala.model.Page(); page.setPage(1); page.setSize(5); page.setOffset(page.getPage()>0 ? (page.getPage()-1)*page.getSize() : 0); Goods goods = new Goods(); goods.setBrand("Apple"); List goodList = goodsMapper.koalaIPageOrder(page, goods); System.out.println(JSON.toJSONString(goodList));}List koalaIPageOrder(@Param("page") Page page, @Param("goods") Goods goods);@Datapublic class Page { private Integer page; private Integer size; private Integer offset; Integer offset(){ return page>0 ? (page-1)*size : 0; }}

select from goods where brand = #{goods.brand} limit #{page.offset},#{page.size} order by gmt_time asc

group by 一般是用来统计某个数量

​​https://w3school.com-/sql/sql_groupby.asp​​

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。