mybatis学习笔记--分页查询

最新推荐文章于 2022-11-04 16:22:53 发布

于瓜瓜

最新推荐文章于 2022-11-04 16:22:53 发布

阅读量3.9k

点赞数

分类专栏： mybatis 文章标签： MyBatis

本文链接：https://blog.csdn.net/nich002/article/details/8837956

版权

mybatis 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

先贴上不变的javabean类,获得SqlSession类和测试类的代码,configuration.xml配置文件,以下两种方法有的都是一样的.

javabean:

package esd.bean;

public class Job {
	private String name; // 职位名称
	private int salary; // 薪水
	private String education; // 最低学历
	private String experience; // 要求的工作经验年限
	private int cId; // 发布职位的公司 company外键
	private String workPlace; // 职位工作地code
	private String industry; // 职位类别code

	// 显示用
	private String salaryScope; // 查询用的salary范围

	// get set 方法略去
	
}

测试类(用了junit3):

// 多条件模糊查询/带分页
	public void testJobgetJobsByCondition() {
		JobDB db = new JobDB();
		Job job = new Job();
		// job.setName("土");
		job.setWorkPlace("20________");
		// job.setIndustry("__01____");
		job.setEducation("本科");
		// job.setExperience("3-5年");
		job.setSalaryScope("10000以上");
		Map map = new HashMap();
		map.put("job", job);
		map.put("start", 0);
		map.put("size", 10);
		List<Job> list = db.getJobsByCondition(map);
		System.out.println("有 " + list.size() + " 个");
		for (Job j : list) {
			System.out.println(" name : " + j.getName() + "  workplace = "
					+ j.getWorkPlace());
		}
	}

获得SqlSession类(DB)

package esd.db;

import java.io.IOException;
import java.io.Reader;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.apache.log4j.Logger;

/**
 * 连接数据库类
 * 
 * @author Administrator
 * 
 */
public class DB {

	//配置文件路径
	private static final String resource = "configuration.xml";
	private static Reader reader = null;
	private static SqlSessionFactory sqlSessionFactory = null;
	private static SqlSession sqlSession = null;
	static Logger log = Logger.getLogger(DB.class.getName()); // 日志

	/**
	 * 获得数据库连接
	 * 
	 * @return
	 */
	public static SqlSession getSqlSession() {
		try {
			reader = Resources.getResourceAsReader(resource);
			sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
			sqlSession = sqlSessionFactory.openSession();
			return sqlSession;
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	/**
	 * 关闭sqlSession
	 */
	public static void close() {
		if (sqlSession != null) {
			sqlSession.close();
		}
	}
}

configuration.xml配置文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>

	<typeAliases>
		<typeAlias alias="Job" type="esd.bean.Job" />
	</typeAliases>
	<environments default="awp">
		<environment id="awp">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="com.mysql.jdbc.Driver" />
				<property name="url"
					value="jdbc:mysql://192.168.170.85:3306/awp" />
				<property name="username" value="root" />
				<property name="password" value="123" />
			</dataSource>
		</environment>
	</environments>
	<mappers>
		<mapper resource="esd/db/mapper/JobMapper.xml" />
	</mappers>
</configuration>

数据库表就不说了哈,几个字段分别对应着上面javabean里的字段,用的数据库是mysql

分页查询方法①

将起始页和返回量直接放到mapper映射文件的sql语句中

java代码:

// 多条件模糊查询职位信息
	public List<Job> getJobsByCondition(Map map) {
		try {
			//从DB类获得sqlSession
			sqlSession = DB.getSqlSession();
//			JobMapper mapper = sqlSession.getMapper(JobMapper.class);
			//从map从取出起始值和返回条数
//			int start = Integer.parseInt(map.get("start").toString());
//			int size = Integer.parseInt(map.get("size").toString());
//			//做一个RowBounds对象,该类是mybatis提供的,专为分页查询使用 new RowBounds(offsets,limit);
//			RowBounds rows = new RowBounds(start,size);
//			List<Job> list = mapper.getJobsByCondition(map);
			List<Job> list = sqlSession.selectList(
					"JobMapper.getJobsByCondition", map);
			log.info("------ 查询到 " + list.size() + " 个职位 ------");
			return list;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			log.error("------ 查询职位异常 ------");
			log.error(e);
			return null;
		} finally {
			sqlSession.close();
		}
	}

mapper映射文件,注意读取放到map中job对象属性的读取方法!

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="JobMapper">

	<!-- 多条件模糊查询职位信息 传递一个Job对象,里面的其中必要的属性值为条件-->
	<select id="getJobsByCondition" resultType="Job"
		parameterType="map">
		select * from Job
		<trim prefix="where" prefixOverrides="and|or">
			<if test="job.name != null and job.name != ''">
				name like concat('%',#{job.name},'%')
			</if>
			<if test="job.workPlace != null and job.workPlace != '' and job.workPlace != '0000000000'">
				and workPlace like concat('%',#{job.workPlace},'%')
			</if>
			<if test="job.industry != null and job.industry != '' and job.industry != '不限'">
				and industry like concat('%',#{job.industry},'%') 
			</if>
			<if test="job.education != null and job.education !='' and job.education != '不限'">
				and education = #{job.education}
			</if>
			<if test="job.experience != null and job.experience != '' and job.experience != '不限'">
				and experience = #{job.experience}
			</if>
			<if test="job.salaryScope != null and job.salaryScope != '' and job.salaryScope != '不限'">
				<if test="job.salaryScope == '1000以下'">
					and salary <=1000
				</if>
				<if test="job.salaryScope == '1000-3000'">
					and (salary >=1000 and salary <3000) 
				</if>
				<if test="job.salaryScope == '3000-6000'">
					and (salary >=3000 and salary <6000) 
				</if>
				<if test="job.salaryScope == '6000-10000'">
					and (salary >=6000 and salary <10000) 
				</if>
				<if test="job.salaryScope == '10000以上'">
					and salary >=10000
				</if>
			</if>
		</trim>
                limit #{start},#{size}
	</select>	
</mapper>

分页查询方法②

使用mybatis提供的RowBounds对象实现
java代码:

// 多条件模糊查询职位信息
	public List<Job> getJobsByCondition(Map map) {
		try {
			//从DB类获得sqlSession
			sqlSession = DB.getSqlSession();
//			JobMapper mapper = sqlSession.getMapper(JobMapper.class);
			//从map从取出起始值和返回条数
			int start = Integer.parseInt(map.get("start").toString());
			int size = Integer.parseInt(map.get("size").toString());
			//做一个RowBounds对象,该类是mybatis提供的,专为分页查询使用 new RowBounds(offsets,limit);
			RowBounds rows = new RowBounds(start,size);
//			List<Job> list = mapper.getJobsByCondition(map);
			List<Job> list = sqlSession.selectList(
					"JobMapper.getJobsByCondition", map,rows);
			log.info("------ 查询到 " + list.size() + " 个职位 ------");
			return list;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			log.error("------ 查询职位异常 ------");
			log.error(e);
			return null;
		} finally {
			sqlSession.close();
		}
	}

mapper映射文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="JobMapper">

	<!-- 多条件模糊查询职位信息 传递一个Job对象,里面的其中必要的属性值为条件-->
	<select id="getJobsByCondition" resultType="Job"
		parameterType="map">
		select * from Job
		<trim prefix="where" prefixOverrides="and|or">
			<if test="job.name != null and job.name != ''">
				name like concat('%',#{job.name},'%')
			</if>
			<if test="job.workPlace != null and job.workPlace != '' and job.workPlace != '0000000000'">
				and workPlace like concat('%',#{job.workPlace},'%')
			</if>
			<if test="job.industry != null and job.industry != '' and job.industry != '不限'">
				and industry like concat('%',#{job.industry},'%') 
			</if>
			<if test="job.education != null and job.education !='' and job.education != '不限'">
				and education = #{job.education}
			</if>
			<if test="job.experience != null and job.experience != '' and job.experience != '不限'">
				and experience = #{job.experience}
			</if>
			<if test="job.salaryScope != null and job.salaryScope != '' and job.salaryScope != '不限'">
				<if test="job.salaryScope == '1000以下'">
					and salary <=1000
				</if>
				<if test="job.salaryScope == '1000-3000'">
					and (salary >=1000 and salary <3000) 
				</if>
				<if test="job.salaryScope == '3000-6000'">
					and (salary >=3000 and salary <6000) 
				</if>
				<if test="job.salaryScope == '6000-10000'">
					and (salary >=6000 and salary <10000) 
				</if>
				<if test="job.salaryScope == '10000以上'">
					and salary >=10000
				</if>
			</if>
		</trim>
	</select>
	
</mapper>

主要差别就是:用了mybatis专门提供给分页显示的RowBounds对象后,mapper映射文件中的sql语句就不用再写起始值和返回数量了(limit offsets,size   mysql中)专门分页显示

希望以上能对你有所帮助.
ilxly01@126.com

于瓜瓜

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mybatis学习笔记--分页查询

先贴上不变的javabean类,获得SqlSession类和测试类的代码,configuration.xml配置文件,以下两种方法有的都是一样的.javabean:package esd.bean;public class Job { private String name; // 职位名称 private int salary; // 薪水 private String e
复制链接

扫一扫