1,有些数据表中,主键是递增的,而且是自动生成,使用mybatis使用这些功能时,可以用如下两种方式:
a,
<insertid="insert"parameterType="com.exingcai.captain.dal.model.GpsDevice"
useGeneratedKeys="true" keyProperty="id">
INSERT INTO T_GPS_DEVICE(
<includerefid="COLS"/>
)VALUES(
#{gpsDeviceCode},
#{gpsDeviceType},
#{gpsDeviceName},
#{vendor},
#{companyId},
#{status},
#{bindDeliveryToolId},
#{bindDeliveryToolNo},
#{remark},
#{createdBy},
now(),
#{updatedBy},
now()
)
</insert>
b,
<insertid="insert">
<selectKey resultType="java.lang.Long" keyProperty="id"
order="AFTER">
SELECT
LAST_INSERT_ID()
</selectKey>
INSERT INTO ENUM_REF(
ID
,CODE
,SORT
,TYPE
,VALUE
,CREATED_BY
,UPDATED_BY
,DELETE_FLAG
,CREATED_DATE
,UPDATED_DATE
)VALUES(
#{id}
, #{code}
, #{sort}
, #{type}
, #{value}
, #{createdBy}
, #{updatedBy}
, 0
, #{createdDate}
, #{updatedDate}
)
</insert>
2, 关于mybatis中的关联查询
该功能的实现,也有两种方式,一种是用标签 <association></association> ,另外一种是用标签<collection></collection>
这两个标签的使用例子如下:
<resultMap>
<resultcolumn="DISPATCH_NO"property="dispatchNo"/>
<resultcolumn="batch_no"property="batchNo"/>
<resultcolumn="warehouse_out_person"property="warehouseOutPerson"/>
<resultcolumn="reject_message"property="rejectMessage"/>
<collectioncolumn="ID"property="cargoDeliverDtls"
select="com.exingcai.captain.dal.dao.CargoDeliverDtlDAO.queryCargoDeliverDtlsByCargoDeliveryId"/>
</resultMap>
- <resultMap id="joinSelectScore" type="Score" >
- <id property="id" column="id"/>
- <result property="math" column="math"/>
- <result property="chinese" column="chinese"/>
- <result property="english" column="english"/>
- <result property="computer" column="computer"/>
- <association property="user" column="userid" javaType="User" resultMap="UserDaoMapping.userResult"/>
- </resultMap>
3, 关于多个参数的传递和参数名的引用
-
据我目前接触到的传多个参数的方案有三种。
第一种方案
DAO层的函数方法
1Public
User
selectUser(String
name
,String area);
123<
select
id=
"selectUser"
resultMap=
"BaseResultMap"
>
select
*
from
user_user_t
where
user_name = #{0}
and
user_area=#{1}
</
select
>
其中,#{0}代表接收的是dao层中的第一个参数,#{1}代表dao层中第二参数,更多参数一致往后加即可。
第二种方案
此方法采用Map传多参数.
Dao层的函数方法
1Public
User
selectUser(Map paramMap);
123<
select
id=
" selectUser"
resultMap=
"BaseResultMap"
>
select
*
from
user_user_t
where
user_name = #{userName,jdbcType=
VARCHAR
}
and
user_area=#{userArea,jdbcType=
VARCHAR
}
</
select
>
Service层调用
12345Private
User
xxxSelectUser(){
Map paramMap=new hashMap();
paramMap.put(“userName”,”对应具体的参数值”);
paramMap.put(“userArea”,”对应具体的参数值”);
User
user
=xxx. selectUser(paramMap);}
个人认为此方法不够直观,见到接口方法不能直接的知道要传的参数是什么。
第三种方案
Dao层的函数方法1Public
User
selectUser(@param(“userName”)Stringname,@param(“userArea”)String area);
123<
select
id=
" selectUser"
resultMap=
"BaseResultMap"
>
select
*
from
user_user_t
where
user_name = #{userName,jdbcType=
VARCHAR
}
and
user_area=#{userArea,jdbcType=
VARCHAR
}
</
select
>
个人觉得这种方法比较好,能让开发者看到dao层方法就知道该传什么样的参数,比较直观,个人推荐用此种方案。
-
- <resultMap id="joinSelectScore" type="Score" >
- <id property="id" column="id"/>
- <result property="math" column="math"/>
- <result property="chinese" column="chinese"/>
- <result property="english" column="english"/>
- <result property="computer" column="computer"/>
- <association property="user" column="userid" javaType="User" resultMap="UserDaoMapping.userResult"/>
- </resultMap>
-
-
-
-
-
- 4,关于条件查询where是否要显示,用trim来实现
-
-
-
<select id="queryByModel" resultMap="BaseResultMap">
SELECT /*MS-T-FEE-DIMENSION-LADDER-QUERYBYMODEL*/ <include refid="Base_Column_List" />
FROM T_FEE_DIMENSION_LADDER
<trim prefix="where" prefixOverrides="AND ">
<if test="status != null ">
AND STATUS = #{status}
</if>
<if test="createdBy != null ">
AND CREATED_BY = #{createdBy}
</if>
<if test="updatedBy != null ">
AND UPDATED_BY = #{updatedBy}
</if>
<if test="maxLadderAmount != null ">
AND MAX_LADDER_AMOUNT = #{maxLadderAmount}
</if>
<if test="minLadderAmount != null ">
AND MIN_LADDER_AMOUNT = #{minLadderAmount}
</if>
<if test="unitDimensionFee != null ">
AND UNIT_DIMENSION_FEE = #{unitDimensionFee}
</if>
<if test="feeDimensionId != null ">
AND fee_dimension_id = #{feeDimensionId}
</if>
AND DELETE_FLAG = 1
</trim>
order by updated_date desc
</select>