史上最简单的MyBatis动态SQL入门示例代码
假如有如下的关于书籍基本信息的表:
DROPDATABASEIFEXISTS`books`; CREATEDATABASE`books`; USEbooks; DROPTABLEIFEXISTS`book`; CREATETABLE`book`( `id`int(11)NOTNULLAUTO_INCREMENT, `name`varchar(128)DEFAULTNULL, `author`varchar(64)DEFAULTNULL, `press`varchar(64)DEFAULTNULL, `douban`FLOATDEFAULTNULL, `isbn`varchar(13)DEFAULTNULL, PRIMARYKEY(`id`) )ENGINE=InnoDBAUTO_INCREMENT=5DEFAULTCHARSET=utf8; INSERTINTO`book`(`name`,`author`,`press`,`douban`,`isbn`)VALUES('Java编程思想第4版','(美)BruceEckel著','机械工业出版社',9.1,'9787111213826'); INSERTINTO`book`(`name`,`author`,`press`,`douban`,`isbn`)VALUES('深入理解Java虚拟机JVM高级特性与最佳实践','周志明著','机械工业出版社',8.8,'9787111421900'); INSERTINTO`book`(`name`,`author`,`press`,`douban`,`isbn`)VALUES('疯狂Java讲义第3版','李刚著','电子工业出版社',7.8,'9787121236693'); INSERTINTO`book`(`name`,`author`,`press`,`douban`,`isbn`)VALUES('深入理解Java7核心技术与最佳实践','成富著','机械工业出版社',6.9,'9787111380399');
用户可能用书名、出版社、豆瓣评分、ISBN中的1~4个字段进行查询,如果用固定的sql语句,那么得写C(4,1)+C(4,2)+C(4,3)+C(4,4)=4+6+4+1=15条sql和15个Dao方法
如果用动态sql,一条语句即可完成,比如这样:
SELECT*FROMbook nameLIKE#{name} ANDpressLIKE#{press} ANDdouban>=#{douban} ANDisbn=#{isbn}
动态sql,可以根据用户对字段选择和输入,动态生成一条sql执行
实体类Book,略。
BookDao,略
Controller略
Service如下:
packagenet.sonng.service; importjava.util.HashMap; importjava.util.List; importjava.util.Map; importjavax.annotation.Resource; importnet.sonng.dao.BookDao; importnet.sonng.entity.Book; importorg.springframework.stereotype.Service; @Service publicclassTestService{ @Resource privateBookDaobookDao; publicListmultipSearch(Stringname,Stringpress,Stringdouban,Stringisbn){ Map map=newHashMap(); if(name!=null&&!name.equals("")){ map.put("name","%"+name+"%"); } if(press!=null&&!press.equals("")){ map.put("press","%"+press+"%"); } if(douban!=null&&!douban.equals("")){ map.put("douban",douban); } if(isbn!=null&&!isbn.equals("")){ map.put("isbn",isbn); } returnbookDao.multipSearch(map); } }
测试类如下:
packagenet.sonng.test; importjava.util.List; importnet.sonng.controller.TestController; importnet.sonng.entity.Book; importorg.springframework.context.ApplicationContext; importorg.springframework.context.support.ClassPathXmlApplicationContext; publicclassTest{ publicstaticvoidmain(String[]args){ ApplicationContextac=newClassPathXmlApplicationContext("ac.xml"); TestControllertc=ac.getBean("testController",TestController.class);//下面的4种查询语句,都可以一条动态SQL执行 //Listbooks=tc.multipSearch("Java","","","");//查询到4条 //List books=tc.multipSearch("Java","机械","","");//3条 //List books=tc.multipSearch("Java","机械","8","");//2条 List books=tc.multipSearch("Java","机械","8","9787111213826");//1条 for(Bookbook:books){ System.out.println(book.getId()); System.out.println(book.getName()); System.out.println(book.getIsbn()); System.out.println(book.getAuthor()); System.out.println(book.getPress()); System.out.println(book.getDouban()); System.out.println("----------------------------------"); } } }
以上所述是小编给大家介绍的史上最简单的MyBatis动态SQL入门示例代码,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对毛票票网站的支持!