1.Mybatis分页插件PageHelper,内置属性,管局查询数据SQL,不关注分页SQL,自动封装数据,pageHelper中国人
配置步骤:
1.导入依赖 com.github.pageHelper
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>4.1.0</version>
</dependency>
2.在mybatis大配置文件当中配置pagehelper插件
<!--mybaits中pagehelper分页插件-->
<plugins>
<plugin interceptor="com.github.pagehelper.PageHelper"></plugin>
</plugins>
3.创建Mapper方法,实现查询
//根据新闻分类,和新闻标题进行动态SQL查询
public List<News> getNewsByTitleAndTid(@Param("ntid") Integer ntid,@Param("ntitle") String ntitle) throws Exception;
4.创建小配置文件,不写limit
<resultMap id="newsMap" type="News">
<!--关联对象-->
<association property="topic" javaType="Topic"></association>
</resultMap>
<select id="getNewsByTitleAndTid" resultMap="newsMap">
select * from news,topic where news.ntid=topic.tid
<if test="ntid!=0">
and ntid=#{ntid}
</if>
<if test="ntitle!=null and ntitle!=''">
and ntitle like concat('%',#{ntitle},'%')
</if>
</select>
5.创建Service层
public PageInfo<News> getNewsByTitleAndTid(Integer tid, String ntitle, Integer pageNum, Integer pageSize) throws Exception;
6.创建ServiceImpl层,实现分页
public class INewsServiceImpl implements INewsService {
private INewsMapper iNewsMapper;
public SqlSession sqlSession;
public INewsServiceImpl(){
sqlSession = MybatisUtil.getSqlSession();
iNewsMapper = sqlSession.getMapper(INewsMapper.class);
}
@Override
public PageInfo<News> getNewsByTitleAndTid(Integer tid, String ntitle, Integer pageNum, Integer pageSize) throws Exception {
//pageHelper分页配置
//步骤一:告诉PageHelper要第几页的多少条数据
Page<News> page = PageHelper.startPage(pageNum, pageSize);
//步骤二:查询数据,注意:之分页紧跟PageHelper后面的一条SQL
List<News> newsList = iNewsMapper.getNewsByTitleAndTid(tid, ntitle);
return page.toPageInfo();
}
}
7.测试
@Test
public void pageHelper() throws Exception {
INewsServiceImpl service = new INewsServiceImpl();
PageInfo<News> pageInfo = service.getNewsByTitleAndTid(0, "", 2, 3);
System.out.println("=======================================");
List<News> list = pageInfo.getList();
for (News news : list) {
System.out.println(news.toString());
}
MybatisUtil.closeSqlSession(service.sqlSession);
}
2.PageHelper排序,调用startPage,直接传入排序的列,默认为升序 可以改为倒叙:列名 desc
public static <E> Page<E> startPage(int pageNum, int pageSize, String orderBy) {
Page<E> page = startPage(pageNum, pageSize);
page.setOrderBy(orderBy);
return page;
}
@Override
public PageInfo<News> getNewsByTitleAndTid(Integer tid, String ntitle, Integer pageNum, Integer pageSize) throws Exception {
//pageHelper分页配置
//步骤一:告诉PageHelper要第几页的多少条数据
Page<News> page = PageHelper.startPage(pageNum, pageSize,"ncreateDate desc");
//步骤二:查询数据,注意:之分页紧跟PageHelper后面的一条SQL
List<News> newsList = iNewsMapper.getNewsByTitleAndTid(tid, ntitle);
return page.toPageInfo();
}
2.注解开发
2.1 @SELECT的使用方式,在Mapper接口中方法上使用该注解,里面写SQL
//使用Select注解进行模糊查询
@Select("select * from news,topic where news.ntid=topic.tid and ntitle like concat('%',#{ntitle},'%')")
public List<News> getNewsByTitle(@Param("ntitle") String ntitle) throws Exception;
2.2 关联查询:可以使用@ResultMap关联使用resultMap中id 或者也可以指向@Results中ID ,但是@ReusltMap和@Results不能同时出现在一个方法上
2.2.1 关联查询一的一方
//使用注解关联映射
@Results(id = "newsMapByMapper",value = {
//关联一的一方
@Result(property = "topic",column = "ntid",one = @One(select="com.wdksoft.mapper.INewsMapper.getTopic"))
})
@Select("select * from news where ntitle like concat('%',#{ntitle},'%')")
public List<News> getNewsByTitle(@Param("ntitle") String ntitle) throws Exception;
//查询一的一方的数据
@Select("select * from topic where tid=#{ntid}")
public Topic getTopic(Integer ntid) throws Exception;
2.2.2 关联查询多的一方
@Results(id = "topicResult",value = {
@Result(id=true,property = "tid",column = "tid"),
//关联多的一方
@Result(property = "newsList",column = "tid",many = @Many(select = "com.wdksoft.mapper.ITopicMapper.getNews"))
})
//查询新闻分类编号为4的新闻,包含新闻集合
@Select("select * from topic where tid=#{tid}")
public Topic getTopic(Integer tid) throws Exception;
@Select("select * from news where ntid=#{tid}")
public List<News> getNews(Integer tid) throws Exception;
2.3 添加数据
//添加数据,添加数据返回主键@Options
@Insert("insert into topic(tname) values(#{tname})")
@Options(useGeneratedKeys = true,keyProperty = "tid")
public int insertTopic(Topic topic) throws Exception;
2.4 修改数据
//修改数据
@Update("update topic set tname=#{tname} where tid=#{tid}")
public int updateTopic(Topic topic) throws Exception;
2.5 删除数据
//修改数据
@Delete("delete from topic where tid=#{tid}")
public int deleteTopic(Integer tid) throws Exception;