数据库表与java实体关联

创建表

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;
-- 此行为注释 Table structure for `business_monitor_config`
 DROP TABLE IF EXISTS TCFlyInternalAutomation.`business_monitor_config`;
CREATE TABLE TCFlyInternalAutomation.`business_monitor_config` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键自增',
  `module_key` bigint(20) NOT NULL DEFAULT '0' COMMENT '模块key',
  `module_name` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '模块名',
  `business_name` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '业务监控名',
  `owners` varchar(500) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '相关人',
  `uk` varchar(500) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '应用名',
  `uk_name` varchar(500) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '应用名称',
  `urls` varchar(1000) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '配置地址',
  `des` varchar(1000) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '描述',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS = 1;

---

resultMap 定义类与数据库字段关联

<resultMap id="RM.businessMonitorConfigQuery" class="com.ly.flight.compare.dal.dataobject.BusinessMonitorConfigVO">
		<result property="id" column="id" javaType="long" jdbcType="BIGINT"  nullValue="0"  />
		<result property="moduleKey" column="module_key" javaType="long" jdbcType="BIGINT"  nullValue="0"  />
		<result property="uk" column="uk" javaType="java.lang.String" jdbcType="VARCHAR"  />
		<result property="moduleName" column="module_name" javaType="java.lang.String" jdbcType="VARCHAR"  />
		<result property="businessName" column="business_name" javaType="java.lang.String" jdbcType="VARCHAR"  />
		<result property="owners" column="owners" javaType="java.lang.String" jdbcType="VARCHAR"  />
		<result property="ukName" column="uk_name" javaType="java.lang.String" jdbcType="VARCHAR"  />
		<result property="urls" column="urls" javaType="java.lang.String" jdbcType="VARCHAR"  />
		<result property="des" column="des" javaType="java.lang.String" jdbcType="VARCHAR"  />
		<result property="createTime" column="create_time" javaType="java.util.Date" jdbcType="TIMESTAMP"  />
		<result property="updateTime" column="update_time" javaType="java.util.Date" jdbcType="TIMESTAMP"  />
	</resultMap>

---

sql编写：

查询

<select id="compare.ModuleReportInterface.businessMonitorConfigQuery" resultMap="RM.businessMonitorConfigQuery" >
		SELECT *
		FROM business_monitor_config
		<dynamic prepend="where">
			<isNotEmpty  prepend="and" property="uk">
				uk = #uk#
			</isNotEmpty>
			<isNotEmpty   prepend="and" property="moduleName">
				module_name = #moduleName#
			</isNotEmpty>
			<isNotEmpty  prepend="and" property="businessName">
				business_name = #businessName#
			</isNotEmpty>
			<isNotEmpty   prepend="and" property="owners">
				owners like concat('%', #owners#, '%')
			</isNotEmpty>
		</dynamic>
		order by update_time desc
	</select>

---

插入

<insert id="compare.businessMonitorConfig.insert">
		INSERT INTO business_monitor_config (
		id,module_key,module_name,business_name,owners,uk,uk_name,urls,des,create_time,update_time
		) VALUES (#id#,#moduleKey#,#moduleName#,#businessName#,#owners#,#uk#,#ukName#,#urls#,#des#,#createTime#,#updateTime#)
		<selectKey resultClass="long" type="post" keyProperty="id" >
			select last_insert_id()
		</selectKey>
	</insert>

修改

<update id="compare.businessMonitorConfig.update">
		UPDATE business_monitor_config SET
		        module_key = #moduleKey# ,module_name = #moduleName#,business_name = #businessName#
		         ,owners = #owners#
		         ,urls = #urls#,des = #des#,create_time = #createTime# ,update_time = #updateTime#
	        WHERE
	        	id = #id#
	</update>

删除

<delete id="compare.ModuleReportInterface.deleteMonitorConfig">
		DELETE FROM business_monitor_config WHERE
	         id = #id#
    </delete>

date格式化