mybatis查询使用是比较方便的,使用Mybatis generator可以直接从数据库逆向生成实体类。
但是有时,想自定义查询,就需要自己写相关类。
需要写的类有三个,以及一个xml文件:
- ChatRoomMemberEX 实体类
- ChatRoomMemberEXMapper .xml 书写sql语句
- ChatRoomMemberEXExample 定义查询条件
- ChatRoomMemberEXMapper 查询接口,定义查询方法
实体类
public class ChatRoomMemberEX implements Serializable {
private Integer clubId;
private String clubName;
private String clubMemberId;
private String clubMemberName;
private Boolean canSpeak;
public Integer getClubId() {
return clubId;
}
public void setClubId(Integer clubId) {
this.clubId = clubId;
}
public String getClubName() {
return clubName;
}
public void setClubName(String clubName) {
this.clubName = clubName;
}
public String getClubMemberId() {
return clubMemberId;
}
public void setClubMemberId(String clubMemberId) {
this.clubMemberId = clubMemberId;
}
public String getClubMemberName() {
return clubMemberName;
}
public void setClubMemberName(String clubMemberName) {
this.clubMemberName = clubMemberName;
}
public Boolean getCanSpeak() {
return canSpeak;
}
public void setCanSpeak(Boolean canSpeak) {
this.canSpeak = canSpeak;
}
}
查询xml,包含一个查询方法:
<?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="com.mapperEX.ClubDetailMapperEX">
<resultMap id="BaseResultMap" type="com.modelEX.ChatRoomMemberEX">
<result column="club_id" jdbcType="INTEGER" property="clubId" />
<result column="club_name" jdbcType="VARCHAR" property="clubName" />
<result column="club_member_id" jdbcType="VARCHAR" property="clubMemberId" />
<result column="club_member_name" jdbcType="VARCHAR" property="clubMemberName" />
<result column="club_canSpeak" jdbcType="BIT" property="canSpeak" />
</resultMap>
<sql id="Base_Column_List">
club_id, club_name,club_member_id,club_member_name,club_canSpeak
</sql>
<select id="selectAllMembersByCLubId" parameterType="com.model.ChatRoomMemberEXExample">
<if test="distinct">
distinct
</if>
'true' as QUERYID,
<include refid="Base_Column_List" />
from club_detail
<if test="_parameter != null">
<include refid="Example_Where_Clause" />
</if>
<if test="orderByClause != null">
order by ${orderByClause}
</if>
<if test="begin != null and begin>=0">
limit #{begin} , #{length}
</if>
</select>
<sql id="Example_Where_Clause">
<where>
<foreach collection="oredCriteria" item="criteria" separator="or">
<if test="criteria.valid">
<trim prefix="(" prefixOverrides="and" suffix=")">
<foreach collection="criteria.criteria" item="criterion">
<choose>
<when test="criterion.noValue">
and ${criterion.condition}
</when>
<when test="criterion.singleValue">
and ${criterion.condition} #{criterion.value}
</when>
<when test="criterion.betweenValue">
and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
</when>
<when test="criterion.listValue">
and ${criterion.condition}
<foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
#{listItem}
</foreach>
</when>
</choose>
</foreach>
</trim>
</if>
</foreach>
</where>
</sql>
</mapper>
增加一个查询条件,在ChatRoomMemberEXExample 的内部类GeneratedCriteria中添加
public Criteria andCanSpeakEqualTo(Boolean value){
addCriterion("club_canSpeak not in", value, "canSpeak");
return (Criteria) this;
}
ChatRoomMemberEXExample 代码:
public class ChatRoomMemberEXExample {
protected String orderByClause;
protected boolean distinct;
protected List<Criteria> oredCriteria;
protected Integer begin;
protected Integer length;
public ChatRoomMemberEXExample() {
oredCriteria = new ArrayList<Criteria>();
}
public static class Criteria extends GeneratedCriteria {
protected Criteria() {
super();
}
}
protected abstract static class GeneratedCriteria {
protected List<Criterion> criteria;
protected GeneratedCriteria() {
super();
criteria = new ArrayList<Criterion>();
}
protected void addCriterion(String condition) {
if (condition == null) {
throw new RuntimeException("Value for condition cannot be null");
}
criteria.add(new Criterion(condition));
}
protected void addCriterion(String condition, Object value, String property) {
if (value == null) {
throw new RuntimeException("Value for " + property + " cannot be null");
}
criteria.add(new Criterion(condition, value));
}
protected void addCriterion(String condition, Object value1, Object value2, String property) {
if (value1 == null || value2 == null) {
throw new RuntimeException("Between values for " + property + " cannot be null");
}
criteria.add(new Criterion(condition, value1, value2));
}
public Criteria andCanSpeakEqualTo(Boolean value){
addCriterion("club_canSpeak not in", value, "canSpeak");
return (Criteria) this;
}
}
public static class Criterion {
private String condition;
private Object value;
private Object secondValue;
private boolean noValue;
private boolean singleValue;
private boolean betweenValue;
private boolean listValue;
private String typeHandler;
public String getCondition() {
return condition;
}
public Object getValue() {
return value;
}
public Object getSecondValue() {
return secondValue;
}
public boolean isNoValue() {
return noValue;
}
public boolean isSingleValue() {
return singleValue;
}
public boolean isBetweenValue() {
return betweenValue;
}
public boolean isListValue() {
return listValue;
}
public String getTypeHandler() {
return typeHandler;
}
protected Criterion(String condition) {
super();
this.condition = condition;
this.typeHandler = null;
this.noValue = true;
}
protected Criterion(String condition, Object value, String typeHandler) {
super();
this.condition = condition;
this.value = value;
this.typeHandler = typeHandler;
if (value instanceof List<?>) {
this.listValue = true;
} else {
this.singleValue = true;
}
}
protected Criterion(String condition, Object value) {
this(condition, value, null);
}
protected Criterion(String condition, Object value, Object secondValue, String typeHandler) {
super();
this.condition = condition;
this.value = value;
this.secondValue = secondValue;
this.typeHandler = typeHandler;
this.betweenValue = true;
}
protected Criterion(String condition, Object value, Object secondValue) {
this(condition, value, secondValue, null);
}
}
}
查询接口
public interface ChatRoomMemberEXMapper {
List<ChatRoomMemberEX> getAllChatRoomMembers(ChatRoomMemberEXExample chatRoomMemberEXExample);
}