mybatis 使用记录

1、动态导入数据，即表和属性是不固定的

/**
 * 批量新增数据
 *
 * @param tableName  表名
 * @param columnList 表列
 * @param dataList   表数据
 * @return 结果
*/
int batchInsertData(@Param("tableName") String tableName, @Param("columnList") List<String> columnList, @Param("dataList") List<Map<String, Object>> dataList);

xml

<insert id="batchInsertData">
		insert into ${tableName}
		<trim prefix="(" suffix=")" suffixOverrides=",">
			<foreach collection="columnList" item="item" separator=",">
				`${item}`
			</foreach>
		</trim>
		<trim prefix="values " suffixOverrides=",">
			<foreach collection="dataList" item="data" separator=",">
				<foreach collection="columnList" item="col" open="(" separator="," close=")">
					#{data.[${col}]}
				</foreach>
			</foreach>
		</trim>
	</insert>

#{data.[${col}]} 使用这种方式从嵌套的foreach中按column中顺序取出对应的字段，如果data后面不加.则会保存，未找到data变量

2、使用复杂对象

        对象类

/**
 * 报表配置
 *
 * @author kou
 */
@NoArgsConstructor
@Data
public class ReportConfigBO {

	/**
	 * 报表类型
	 */
	private String reportType;

	/**
	 * 标题
	 */
	private String title;

	/**
	 * 表名
	 */
	private String tableName;

	/**
	 * x轴信息
	 */
	private List<Axis> xaxis;

	/**
	 * y轴信息
	 */
	private List<Axis> yaxis;

	/**
	 * 排序信息
	 */
	private List<Column> orderColumns;

	/**
	 * 分组信息
	 */
	private List<String> groupColumns;

	/**
	 * 配置
	 */
	private Map option;

	@NoArgsConstructor
	@Data
	public static class Axis {

		/**
		 * 列名
		 */
		private String column;

		/**
		 * 坐标轴显示名称
		 */
		private String showName;

		/**
		 * 是否聚合
		 */
		private String aggregate;

		/**
		 * 操作
		 */
		private List<Operate> operate;

	}

	@NoArgsConstructor
	@Data
	public static class Operate {

		/**
		 * 运算符,[+,-,*,/]
		 */
		private String operate;

		/**
		 * 类型，table:表字段,number:数值,string:字符
		 */
		private String type;

		/**
		 * 值,当type为非table时才启用
		 */
		private String value;
	}

	@NoArgsConstructor
	@Data
	public static class Column {

		/**
		 * 字段
		 */
		private String column;

		/**
		 * 值
		 */
		private String value;
	}

}

xml

<select id="configReport" resultType="java.util.Map">
		SELECT
			<if test="reportConfig.xaxis != null and reportConfig.xaxis.size > 0">
				<foreach collection="reportConfig.xaxis" item="x" separator="," close="," index="indexX">
					<if test="x.column != null and x.column != ''">
						<if test="x.aggregate != null and x.aggregate != ''">
							${x.aggregate}(
						</if>
						coalesce(`${x.column}`, '')
						<if test="x.operate != null and x.operate.size > 0">
							<foreach collection="x.operate" item="opx" separator="," index="indexOpx">
								<if test="opx.operate != null and opx.operate == '' and opx.type != null and opx.type != '' and opx.opx.value != null and opx.opx.value != ''">
									${opx.operate}
									<if test="opx.type == 'table'">
										coalesce(`${opx.value}`, '')
									</if>
									<if test="opx.type == 'number'">
										coalesce(${opx.value}, 0)
									</if>
									<if test="opx.type == 'string'">
										coalesce(`${opx.value}`, '')
									</if>
								</if>
							</foreach>
						</if>
						<if test="x.aggregate != null and x.aggregate != ''">
							)
						</if>
						`x${indexX}`
					</if>
				</foreach>
			</if>
			<if test="reportConfig.yaxis != null and reportConfig.yaxis.size > 0">
				<foreach collection="reportConfig.yaxis" item="y" separator="," index="indexY">
					<if test="y.column != null and y.column != ''">
						<if test="y.aggregate != null and y.aggregate != ''">
							${y.aggregate}(
						</if>
						coalesce(`${y.column}`, '')
						<if test="y.operate != null and y.operate.size > 0">
							<foreach collection="y.operate" item="opy" separator="," index="indexOpy">
								<if test="opy.operate != null and opy.operate != '' and opy.type != null and  opy.type != '' and opy.value != null and  opy.value != ''">
									${opy.operate}
									<if test="opy.type == 'table'">
										coalesce(`${opy.value}`, '')
									</if>
									<if test="opy.type == 'number'">
										coalesce(${opy.value}, 0)
									</if>
									<if test="opy.type == 'string'">
										coalesce(`${opy.value}`, '')
									</if>
								</if>
							</foreach>
						</if>
						<if test="y.aggregate != null and y.aggregate != ''">
							)
						</if>
						`y${indexY}`
					</if>
				</foreach>
			</if>
		FROM
			`${reportConfig.tableName}`
		<if test="reportConfig.groupColumns != null and reportConfig.groupColumns.size > 0">
			group by
			<foreach collection="reportConfig.groupColumns" item="column" separator=",">
				`${column}`
			</foreach>
		</if>
		<if test="reportConfig.orderColumns != null and reportConfig.orderColumns.size > 0">
			order by
			<foreach collection="reportConfig.orderColumns" item="item" separator=",">
				<if test="item.column != null and item.column != ''">
					`${item.column}`
					<if test="item.value != null and item.value != ''">
						${item.value}
					</if>
				</if>
			</foreach>
		</if>
	</select>

对 List<List<T>> 对象循环，使用 foreach  item 属性作为子 foreach 的collection 值进行遍历