1 注意路径和对象的名字
2 扩展后注意ID 后面要用
查询用到 扩展后的对象
如果复杂的业务,可自行定义扩展,然后返回对象选择对应自己的 ID即可
3 查询
查询条件
对应的实体类,需要和扩展保持一致
basecountry如下
<?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.yinghui.soft.web.baseCountry.mapper.TBaseCountryMapper">
<resultMap id="BaseResultMap" type="com.yinghui.soft.web.baseCountry.model.TBaseCountry">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="name" jdbcType="VARCHAR" property="name" />
<result column="create_user" jdbcType="INTEGER" property="createUser" />
<result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
<result column="delete_flag" jdbcType="INTEGER" property="deleteFlag" />
<result column="order_num" jdbcType="INTEGER" property="orderNum" />
</resultMap>
<resultMap extends="BaseResultMap" id="TObjectMap" type="com.yinghui.soft.web.baseCountry.model.TBaseCountry">
<result column="createuser_name" jdbcType="VARCHAR" property="username"/>
</resultMap>
<!-- 基础数据 >> 国别维护 -->
<sql id="Base_Column_List">
id, name, create_user, create_time, delete_flag, order_num
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from t_base_country
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
delete from t_base_country
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insertSelective" parameterType="com.yinghui.soft.web.baseCountry.model.TBaseCountry">
insert into t_base_country
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="name != null">
name,
</if>
<if test="createUser != null">
create_user,
</if>
<if test="createTime != null">
create_time,
</if>
<if test="deleteFlag != null">
delete_flag,
</if>
<if test="orderNum != null">
order_num,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=INTEGER},
</if>
<if test="name != null">
#{name,jdbcType=VARCHAR},
</if>
<if test="createUser != null">
#{createUser,jdbcType=INTEGER},
</if>
<if test="createTime != null">
#{createTime,jdbcType=TIMESTAMP},
</if>
<if test="deleteFlag != null">
#{deleteFlag,jdbcType=INTEGER},
</if>
<if test="orderNum != null">
#{orderNum,jdbcType=INTEGER},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.yinghui.soft.web.baseCountry.model.TBaseCountry">
update t_base_country
<set>
<if test="name != null">
name = #{name,jdbcType=VARCHAR},
</if>
<if test="createUser != null">
create_user = #{createUser,jdbcType=INTEGER},
</if>
<if test="createTime != null">
create_time = #{createTime,jdbcType=TIMESTAMP},
</if>
<if test="deleteFlag != null">
delete_flag = #{deleteFlag,jdbcType=INTEGER},
</if>
<if test="orderNum != null">
order_num = #{orderNum,jdbcType=INTEGER},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<!--修改校验-->
<select id="countById" parameterType="Map" resultType="java.lang.Long">
select count(1)
from t_base_country tsn
where 1=1
<if test="id != null">
AND id NOT IN (#{id,jdbcType=INTEGER})
</if>
AND tsn.name='${name}'
</select>
<sql id="findPage_where">
<where>
<!-- 查询 -->
<if test="name != null">
AND (tsn.name like '%${name}%')
</if>
<if test="deleteFlag != null">
AND tsn.delete_flag = #{deleteFlag}
</if>
</where>
</sql>
<sql id="limit">
<if test="offset!=null and limit!=null">
limit #{limit} offset #{offset}
</if>
</sql>
<select id="findPage_count" resultType="java.lang.Long" useCache="false">
SELECT count(1) FROM t_base_country tsn
<include refid="findPage_where"/>
</select>
<select id="findPage" resultMap="TObjectMap" useCache="false">
SELECT tsn.order_num as order_num,
tsn.id as id ,
tsn.name as name,
tsn.create_time as
create_time,
b.user_name as createuser_name,
tsn.delete_flag as delete_flag,
tsn.create_user as create_user
FROM t_base_country tsn left join t_user b on tsn.create_user = b.id
<include refid="findPage_where"/>
order by tsn.order_num asc
<include refid="limit"/>
</select>
<!--数据移动和新增-->
<select id="selectByOrderNumNext" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from t_base_country
where order_num > #{order_num,jdbcType=INTEGER} and delete_flag=1 order by order_num asc
LIMIT 1 OFFSET 0
</select>
<select id="selectByOrderNumUp" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from t_base_country
where order_num < #{order_num,jdbcType=INTEGER} and delete_flag=1 order by order_num DESC
LIMIT 1 OFFSET 0
</select>
<select id="selectByOrderNumMax" parameterType="java.lang.Integer" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from t_base_country
order by order_num DESC
LIMIT 1 OFFSET 0
</select>
</mapper>
业务执行的demo如下
<?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.yinghui.soft.web.tProject.mapper.TProjectManagementMapper">
<resultMap id="BaseResultMap" type="com.yinghui.soft.web.tProject.model.TProjectManagement">
<id column="id" jdbcType="INTEGER" property="id" />
<result column="name" jdbcType="VARCHAR" property="name" />
<result column="type_id" jdbcType="INTEGER" property="typeId" />
<result column="country_id" jdbcType="INTEGER" property="countryId" />
<result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
<result column="create_user" jdbcType="INTEGER" property="createUser" />
<result column="delete_flag" jdbcType="INTEGER" property="deleteFlag" />
<result column="project_start_time" jdbcType="TIMESTAMP" property="projectStartTime" />
<result column="project_end_time" jdbcType="TIMESTAMP" property="projectEndTime" />
<result column="outsourcing_id" jdbcType="INTEGER" property="outsourcingId" />
</resultMap>
<resultMap extends="BaseResultMap" id="ResultMapWithBLOBs" type="com.yinghui.soft.web.tProject.model.TProjectManagement">
<result column="createuser_name" jdbcType="VARCHAR" property="username"/>
<result column="content" jdbcType="VARCHAR" property="content" />
<result column="outsourcing_name" jdbcType="VARCHAR" property="outsourcingName" />
<result column="country_name" jdbcType="VARCHAR" property="countryName" />
<result column="type_name" jdbcType="VARCHAR" property="typeName" />
</resultMap>
<sql id="Base_Column_List">
id, name, type_id, country_id, create_time,delete_flag, create_user, project_start_time, project_end_time,
outsourcing_id
</sql>
<sql id="Blob_Column_List">
content
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="ResultMapWithBLOBs">
select
<include refid="Base_Column_List" />
,
<include refid="Blob_Column_List" />
from t_project_management
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
delete from t_project_management
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.yinghui.soft.web.tProject.model.TProjectManagement">
insert into t_project_management (id, name, type_id,
country_id, create_time, create_user,
project_start_time, project_end_time, outsourcing_id,
content)
values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR}, #{typeId,jdbcType=INTEGER},
#{countryId,jdbcType=INTEGER}, #{createTime,jdbcType=TIMESTAMP}, #{createUser,jdbcType=INTEGER},
#{projectStartTime,jdbcType=TIMESTAMP}, #{projectEndTime,jdbcType=TIMESTAMP}, #{outsourcingId,jdbcType=INTEGER},
#{content,jdbcType=VARCHAR})
</insert>
<insert id="insertSelective" parameterType="com.yinghui.soft.web.tProject.model.TProjectManagement">
insert into t_project_management
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
id,
</if>
<if test="name != null">
name,
</if>
<if test="typeId != null">
type_id,
</if>
<if test="countryId != null">
country_id,
</if>
<if test="createTime != null">
create_time,
</if>
<if test="deleteFlag != null">
delete_flag,
</if>
<if test="createUser != null">
create_user,
</if>
<if test="projectStartTime != null">
project_start_time,
</if>
<if test="projectEndTime != null">
project_end_time,
</if>
<if test="outsourcingId != null">
outsourcing_id,
</if>
<if test="content != null">
content,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=INTEGER},
</if>
<if test="name != null">
#{name,jdbcType=VARCHAR},
</if>
<if test="typeId != null">
#{typeId,jdbcType=INTEGER},
</if>
<if test="countryId != null">
#{countryId,jdbcType=INTEGER},
</if>
<if test="createTime != null">
#{createTime,jdbcType=TIMESTAMP},
</if>
<if test="deleteFlag != null">
#{deleteFlag,jdbcType=INTEGER},
</if>
<if test="createUser != null">
#{createUser,jdbcType=INTEGER},
</if>
<if test="projectStartTime != null">
#{projectStartTime,jdbcType=TIMESTAMP},
</if>
<if test="projectEndTime != null">
#{projectEndTime,jdbcType=TIMESTAMP},
</if>
<if test="outsourcingId != null">
#{outsourcingId,jdbcType=INTEGER},
</if>
<if test="content != null">
#{content,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.yinghui.soft.web.tProject.model.TProjectManagement">
update t_project_management
<set>
<if test="name != null">
name = #{name,jdbcType=VARCHAR},
</if>
<if test="typeId != null">
type_id = #{typeId,jdbcType=INTEGER},
</if>
<if test="countryId != null">
country_id = #{countryId,jdbcType=INTEGER},
</if>
<if test="createTime != null">
create_time = #{createTime,jdbcType=TIMESTAMP},
</if>
<if test="deleteFlag != null">
delete_flag = #{deleteFlag,jdbcType=INTEGER},
</if>
<if test="createUser != null">
create_user = #{createUser,jdbcType=INTEGER},
</if>
<if test="projectStartTime != null">
project_start_time = #{projectStartTime,jdbcType=TIMESTAMP},
</if>
<if test="projectEndTime != null">
project_end_time = #{projectEndTime,jdbcType=TIMESTAMP},
</if>
<if test="outsourcingId != null">
outsourcing_id = #{outsourcingId,jdbcType=INTEGER},
</if>
<if test="content != null">
content = #{content,jdbcType=VARCHAR},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKeyWithBLOBs" parameterType="com.yinghui.soft.web.tProject.model.TProjectManagement">
update t_project_management
set name = #{name,jdbcType=VARCHAR},
type_id = #{typeId,jdbcType=INTEGER},
country_id = #{countryId,jdbcType=INTEGER},
create_time = #{createTime,jdbcType=TIMESTAMP},
create_user = #{createUser,jdbcType=INTEGER},
project_start_time = #{projectStartTime,jdbcType=TIMESTAMP},
project_end_time = #{projectEndTime,jdbcType=TIMESTAMP},
outsourcing_id = #{outsourcingId,jdbcType=INTEGER},
content = #{content,jdbcType=VARCHAR}
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.yinghui.soft.web.tProject.model.TProjectManagement">
update t_project_management
set name = #{name,jdbcType=VARCHAR},
type_id = #{typeId,jdbcType=INTEGER},
country_id = #{countryId,jdbcType=INTEGER},
create_time = #{createTime,jdbcType=TIMESTAMP},
create_user = #{createUser,jdbcType=INTEGER},
project_start_time = #{projectStartTime,jdbcType=TIMESTAMP},
project_end_time = #{projectEndTime,jdbcType=TIMESTAMP},
outsourcing_id = #{outsourcingId,jdbcType=INTEGER}
where id = #{id,jdbcType=INTEGER}
</update>
<!--修改校验-->
<select id="countById" parameterType="Map" resultType="java.lang.Long">
select count(1)
from t_project_management tsn
where 1=1
<if test="id != null">
AND id NOT IN (#{id,jdbcType=INTEGER})
</if>
AND tsn.name='${name}'
</select>
<sql id="findPage_where">
<where>
<!-- 查询 -->
<if test="name != null">
AND (tsn.name like '%${name}%')
</if>
<if test="deleteFlag != null">
AND tsn.delete_flag = #{deleteFlag}
</if>
<if test="typeId != null">
AND tsn.type_id in (${typeId})
</if>
</where>
</sql>
<sql id="limit">
<if test="offset!=null and limit!=null">
limit #{limit} offset #{offset}
</if>
</sql>
<select id="findPage_count" resultType="java.lang.Long" useCache="false">
SELECT count(1) FROM t_project_management tsn
<include refid="findPage_where"/>
</select>
<select id="findPage" resultMap="ResultMapWithBLOBs" useCache="false">
SELECT
tsn.id,tsn.name,tsn.type_id,tsn.country_id,tsn.create_time,tsn.create_user,tsn.project_start_time,tsn.project_end_time,tsn.outsourcing_id,
b.user_name as createuser_name,
tou.name as outsourcing_name,
tbc.name as country_name,
tpt.name as type_name
FROM t_project_management tsn
left join t_user b on tsn.create_user = b.id
left join t_outsourcing_unit tou on tsn.outsourcing_id =tou.id
left join t_base_country tbc on tsn.country_id=tbc.id
left join t_project_type tpt on tsn.type_id=tpt.id
<include refid="findPage_where"/>
order by tsn.create_time DESC
<include refid="limit"/>
</select>
</mapper>