Mybatis分页查询

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;