Mybatis动态列小功能

博客信息

Mybatis动态列小功能

发布时间：『 2020-09-22 09:21』博客类别：解决方案阅读(1138)

在开发中通用的增删改查的方法我们见多了，都是各种代码生成器一键生成，但是我还会遇到一种场景；就是在A表对应的service层操作B表的某一列或某几个列的值；

例如：当遇到我们上篇博客的业务场景，岳阳总部下发事项，娄底常德接收该事项对应的任务，事项表与任务表是一对多的关系，只要其中娄底常德一个子项目接收了任务，那么就更改事项表的状态；

在这个场景下，我们编码就要在任务对应的service层去修改事项表的某一状态字段；

常规编码就是为了这个修改字段值写个sql，而这种业务场景非常的多，那么我们需要写很多个sql，太麻烦，我们这篇博客就应对此种场景的小功能；

写一个工具类 ParamMap.java

这工具类是用来动态构建sql的

package com.javaxl.result;

import java.util.HashMap;

/**
 * 用来构建sql的参数以及页面的返回json
 * 
 * @author lixiao
 * @date 2020年9月22日
 */
public class ParamMap extends HashMap<String, Object>{
	
	public ParamMap data(String key,Object value) {
		this.put(key, value);
		return this;
	}
}

mybatis映射查询参数类CommonUpdateVo.java

package com.javaxl.vo;

import com.javaxl.result.ParamMap;

/**
 * 通用的修改某几个列的vo类
 * 
 * @author lixiao
 * @date 2020年9月22日
 */
public class CommonUpdateVo {
//  表名
	private String tablename;
//	需要修改的列名与列值
	private ParamMap paramMap = new ParamMap();
//	唯一列段
	private String primaryfieldname;
//	唯一列段
	private Object primaryfieldvalue;

	public CommonUpdateVo(String tablename, String primaryfieldname, Object primaryfieldvalue) {
		super();
		this.tablename = tablename;
		this.primaryfieldname = primaryfieldname;
		this.primaryfieldvalue = primaryfieldvalue;
	}

	public CommonUpdateVo() {
		super();
	}

	public String getTablename() {
		return tablename;
	}

	public void setTablename(String tablename) {
		this.tablename = tablename;
	}

	public ParamMap getParamMap() {
		return paramMap;
	}

	public void setParamMap(ParamMap paramMap) {
		this.paramMap = paramMap;
	}

	public String getPrimaryfieldname() {
		return primaryfieldname;
	}

	public void setPrimaryfieldname(String primaryfieldname) {
		this.primaryfieldname = primaryfieldname;
	}

	public Object getPrimaryfieldvalue() {
		return primaryfieldvalue;
	}

	public void setPrimaryfieldvalue(Object primaryfieldvalue) {
		this.primaryfieldvalue = primaryfieldvalue;
	}

	@Override
	public String toString() {
		return "CommonUpdateVo [tablename=" + tablename + ", paramMap=" + paramMap + ", primaryfieldname="
				+ primaryfieldname + ", primaryfieldvalue=" + primaryfieldvalue + "]";
	}
}

commonDao.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.javaxl.dao.CommonDao">

	<!-- 查询某一表中的某些列段值 -->
	<select id="dynamicQueryField" resultType="java.util.Map" parameterType="com.javaxl.vo.CommonUpdateVo">
		SELECT
		${paramMap.fieldnames}
		FROM ${tablename}
		WHERE ${primaryfieldname} = #{primaryfieldvalue}
	</select>

	<!-- 查询某一表中的某些列段值 -->
	<select id="dynamicQueryFieldCondition" resultType="java.util.Map" parameterType="com.javaxl.vo.CommonUpdateVo">
		SELECT
		${paramMap.fieldnames}
		FROM ${tablename}
		<where>
			<if test="paramMap.conditionSql != null and paramMap.conditionSql != ''">
				and ${paramMap.conditionSql}
			</if>
			<if test="paramMap.fieldvalue1 != null and paramMap.fieldvalue1 != ''">
				and ${paramMap.fieldname1} = #{paramMap.fieldvalue1}
			</if>
			<if test="paramMap.fieldvalue2 != null and paramMap.fieldvalue2 != ''">
				and ${paramMap.fieldname2} = #{paramMap.fieldvalue2}
			</if>
			<if test="paramMap.fieldvalue3 != null and paramMap.fieldvalue3 != ''">
				and ${paramMap.fieldname3} = #{paramMap.fieldvalue3}
			</if>
			<if test="paramMap.fieldvalue4 != null and paramMap.fieldvalue4 != ''">
				and ${paramMap.fieldname4} = #{paramMap.fieldvalue4}
			</if>
			<if test="paramMap.fieldvalue5 != null and paramMap.fieldvalue5 != ''">
				and ${paramMap.fieldname5} = #{paramMap.fieldvalue5}
			</if>
			<if test="paramMap.fieldvalue6 != null and paramMap.fieldvalue6 != ''">
				and ${paramMap.fieldname6} = #{paramMap.fieldvalue6}
			</if>
		</where>
	</select>

	<!-- 删除某一表中的某些数据 -->
	<select id="dynamicDeleteFromTable" parameterType="com.javaxl.vo.CommonUpdateVo">
		DELETE
		FROM ${tablename}
		WHERE ${primaryfieldname} = #{primaryfieldvalue}
	</select>

	<!-- 动态修改某一表中的某些列段值 -->
	<update id="dynamicUpdateField" parameterType="com.javaxl.vo.CommonUpdateVo">
		UPDATE ${tablename} SET
		${primaryfieldname} = #{primaryfieldvalue}
		<if test="paramMap.fieldvalue1 != null and paramMap.fieldvalue1 != ''">
			,${paramMap.fieldname1} = #{paramMap.fieldvalue1}
		</if>
		<if test="paramMap.fieldvalue2 != null and paramMap.fieldvalue2 != ''">
			,${paramMap.fieldname2} = #{paramMap.fieldvalue2}
		</if>
		<if test="paramMap.fieldvalue3 != null and paramMap.fieldvalue3 != ''">
			,${paramMap.fieldname3} = #{paramMap.fieldvalue3}
		</if>
		<if test="paramMap.fieldvalue4 != null and paramMap.fieldvalue4 != ''">
			,${paramMap.fieldname4} = #{paramMap.fieldvalue4}
		</if>
		<if test="paramMap.fieldvalue5 != null and paramMap.fieldvalue5 != ''">
			,${paramMap.fieldname5} = #{paramMap.fieldvalue5}
		</if>
		<if test="paramMap.fieldvalue6 != null and paramMap.fieldvalue6 != ''">
			,${paramMap.fieldname6} = #{paramMap.fieldvalue6}
		</if>
		WHERE ${primaryfieldname} = #{primaryfieldvalue}
	</update>

	<!-- 动态修改某一表中的某些列段值 -->
	<update id="dynamicUpdateFieldCondition" parameterType="com.javaxl.vo.CommonUpdateVo">
		UPDATE ${tablename} SET
		${primaryfieldname} = #{primaryfieldvalue}
		<if test="paramMap.fieldvalue1 != null and paramMap.fieldvalue1 != ''">
			,${paramMap.fieldname1} = #{paramMap.fieldvalue1}
		</if>
		<if test="paramMap.fieldvalue2 != null and paramMap.fieldvalue2 != ''">
			,${paramMap.fieldname2} = #{paramMap.fieldvalue2}
		</if>
		<if test="paramMap.fieldvalue3 != null and paramMap.fieldvalue3 != ''">
			,${paramMap.fieldname3} = #{paramMap.fieldvalue3}
		</if>
		<if test="paramMap.fieldvalue4 != null and paramMap.fieldvalue4 != ''">
			,${paramMap.fieldname4} = #{paramMap.fieldvalue4}
		</if>
		<if test="paramMap.fieldvalue5 != null and paramMap.fieldvalue5 != ''">
			,${paramMap.fieldname5} = #{paramMap.fieldvalue5}
		</if>
		<if test="paramMap.fieldvalue6 != null and paramMap.fieldvalue6 != ''">
			,${paramMap.fieldname6} = #{paramMap.fieldvalue6}
		</if>
		<where>
			<if test="paramMap.fieldConditionvalue1 != null and paramMap.fieldConditionvalue1 != ''">
				and ${paramMap.fieldConditionname1} = #{paramMap.fieldConditionvalue1}
			</if>
			<if test="paramMap.fieldConditionvalue2 != null and paramMap.fieldConditionvalue2 != ''">
				and ${paramMap.fieldConditionname2} = #{paramMap.fieldConditionvalue2}
			</if>
			<if test="paramMap.fieldConditionvalue3 != null and paramMap.fieldConditionvalue3 != ''">
				and ${paramMap.fieldConditionname3} = #{paramMap.fieldConditionvalue3}
			</if>
			<if test="paramMap.fieldConditionvalue4 != null and paramMap.fieldConditionvalue4 != ''">
				and ${paramMap.fieldConditionname4} = #{paramMap.fieldConditionvalue4}
			</if>
			<if test="paramMap.fieldConditionvalue5 != null and paramMap.fieldConditionvalue5 != ''">
				and ${paramMap.fieldConditionname5} = #{paramMap.fieldConditionvalue5}
			</if>
			<if test="paramMap.fieldConditionvalue6 != null and paramMap.fieldConditionvalue6 != ''">
				and ${paramMap.fieldConditionname6} = #{paramMap.fieldConditionvalue6}
			</if>
		</where>
	</update>

	<insert id="dynamicInsertField" parameterType="com.javaxl.vo.CommonUpdateVo">
		insert into ${tablename}(${paramMap.tableFields}) values (${paramMap.tableFieldValues})
	</insert>

	<!--	executeSQL-->
	<delete id="executeSQL" parameterType="com.javaxl.vo.CommonUpdateVo">
		${paramMap.executeSQL}
	</delete>
	
</mapper>

CommonDao.java

/**
 * 通用的dao方法
 *     
 * @author lixiao
 * @version 2020-08-31
 */
@MyBatisDao
public interface CommonDao{
   // 修改某一张表的一个或某几个字段
   public int dynamicUpdateField(CommonUpdateVo commonUpdateVo);

   // 按条件修改某一张表的一个或某几个字段
   public int dynamicUpdateFieldCondition(CommonUpdateVo commonUpdateVo);

   // 查询某一张表的某一或某几个字段
   public List<Map<String, Object>> dynamicQueryField(CommonUpdateVo commonUpdateVo);

   // 查询某一张表的某一或某几个字段（多条件查询）
   public List<Map<String, Object>> dynamicQueryFieldCondition(CommonUpdateVo commonUpdateVo);

   public void dynamicDeleteFromTable (CommonUpdateVo commonUpdateVo);

   // dynamicInsertField
   public int dynamicInsertField(CommonUpdateVo commonUpdateVo);

   //执行指定的SQL语句
   public void executeSQL(CommonUpdateVo commonUpdateVo);
}

最后使用写好的工具

CommonUpdateVo commonUpdateVo = new CommonUpdateVo("t_dynamic_fasong", "uuid", sxRwVo.getFsid());
		commonUpdateVo.getParamMap()
			.data("fieldname1", "extend8").data("fieldvalue1", dubanzhengwen)
			.data("fieldname2", "extend9").data("fieldvalue2", dubanfujian)
			.data("fieldname3", "extend1").data("fieldvalue3", "Y");
		commonDao.dynamicUpdateField(commonUpdateVo);

上面这个代码的作用是：修改t_dynamic_fasong表的extend8字段值为dubanzhengwen，修改extend9字段值为dubanfujian，修改extend1字段值为"Y"；

over.......

关键字： SSM 动态列

博主信息

javaxl_小李飞刀

(知识改变命运，教育改变生活)

按日志类别

按日志日期

友情链接