目录
一,mybatis动态sql
1.if:条件语句
2.foreach
biz对应的方法:
public int deleteByPrimaryKey(Integer bid);
public Book selectByPrimaryKey(Integer bid);
public List<Book> selectByIn(List bookIds);
public List<Book> selectBooksLike1(String bname);
public List<Book> selectBooksLike2(String bname);
public List<Book> selectBooksLike3(String bname);
List<Book> list1();
List<Book> list2();
List<Book> list3(BookVo vo);
List<Map> list4();
Map list5 (Map map);
代码xml:
<select id="selectByIn" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from t_mvc_book
where bid in
<foreach collection="bookIds" open="(" close= ")" separator="," item="bid">
#{bid}
</foreach>
</select>
测试结果
二,模糊查询
.#{...}&&${...}&&Concat
完整xml:
<select id="selectBooksLike1" resultType="com.zking.model.Book" parameterType="java.lang.String">
select * from t_mvc_book where bname like #{bname}
</select>
<select id="selectBooksLike2" resultType="com.zking.model.Book" parameterType="java.lang.String">
select * from t_mvc_book where bname like '${bname}'
</select>
<select id="selectBooksLike3" resultType="com.zking.model.Book" parameterType="java.lang.String">
select * from t_mvc_book where bname like concat('%',#{bname},'%')
</select>
mapper
List<Book> selectBooksLike1(@Param("bname") String bname);
List<Book> selectBooksLike2(@Param("bname") String bname);
List<Book> selectBooksLike3(@Param("bname") String bname);
biz
public List<Book> selectBooksLike1(String bname);
public List<Book> selectBooksLike2(String bname);
public List<Book> selectBooksLike3(String bname);
impl:
public List<Book> selectBooksLike1(String bname){
return bookMapper.selectBooksLike1(bname);
}
public List<Book> selectBooksLike2(String bname){
return bookMapper.selectBooksLike2(bname);
}
public List<Book> selectBooksLike3(String bname){
return bookMapper.selectBooksLike3(bname);
}
测试:
@Test
public void selectBooksLike1() {
bookBiz.selectBooksLike1("%圣墟%").forEach(System.out::println);
}
@Test
public void selectBooksLike2() {
bookBiz.selectBooksLike2("%圣墟%").forEach(System.out::println);
}
@Test
public void selectBooksLike3() {
bookBiz.selectBooksLike3("圣墟").forEach(System.out::println);
}
测试结果:
#{...}
${...}
Conca
可以看到sql语句的不同
三,查询返回结果集的处理
1.resultMap
适合使用返回值是自定义实体类的情况
2.resultType
适合使用返回值的数据类型是非自定义的,即jdk的提供的类型
xml:
<select id="list1" resultMap="BaseResultMap">
select * from t_mvc_book
</select>
<select id="list2" resultType="com.ruojuan.model.book">
select * from t_mvc_book
</select>
<select id="list3" resultType="com.ruojuan.model.book" parameterType="com.ruojuan.model.BookVo">
select * from t_mvc_book where bid in
<foreach collection="bookIds" open="(" close=")" separator="," item="bid">
#{bid}
</foreach>
</select>
<select id="list4" resultType="java.util.Map">
select * from t_mvc_book
</select>
<select id="list5" resultType="java.util.Map" parameterType="java.util.Map">
select * from t_mvc_book where bid = #{bid}
</select>
mapper:
//list1 list2的结论是 ,对于单表查询而言,可以用resultmap/resultType接收,但是多表必须用resultmap接收
List<book> list1();
List<book> list2();
//如果要传入多个查询参数,必须以对象的方式进行传递
List<book> list3(BookVo vo);
// 使用resultType返回List<Map>,适用于多表查询返回结果集
List<Map> list4();
// 使用resultType返回Map<String,Object>,适用于多表查询返回单个结果集
Map list5(Map map);
//利用第三方插件进行分页
List<Map> listPager(Map map);
biz:
List<book> list1();
List<book> list2();
List<book> list3(BookVo vo);
List<Map> list4();
Map list5(Map map);
impl:
@Override
public List<book> list1(){
return bookMapper.list1();
}
@Override
public List<book> list2(){
return bookMapper.list2();
}
@Override
public List<book> list3(BookVo vo){
return bookMapper.list3(vo);
}
@Override
public List<Map> list4(){
return bookMapper.list4();
}
@Override
public Map list5(Map map){
return bookMapper.list5(map);
}
test测试类:
@Test
public void list1() {
bookBiz.list1().forEach(System.out::println);
}
@Test
public void list2() {
bookBiz.list2().forEach(System.out::println);
}
@Test
public void list3() {
BookVo vo = new BookVo();
vo.setBookIds(Arrays.asList(new Integer[]{31,32,33,34}));
bookBiz.list3(vo).forEach(System.out::println);
}
@Test
public void list4() {
bookBiz.list4().forEach(System.out::println);
}
@Test
public void list5() {
Map map = new HashMap();
map.put("bid",32);
System.out.println(bookBiz.list5(map));
}
测试结果:
写不动了 没写完 下次写