先贴上不变的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