myBatis的sqlmap操作mysql数据库表,不管是查询还是更新,可以和表的字段类型无关,可以是VARCHAR或者TIMESTAMP,而sqlmap中的resultMap对应的jdbcType也可以是TIMESTAMP或者VARCHAR,甚至DTO的属性类型可以是Date类型,也可以是String类型,都不影响实际的操作逻辑。
验证代码如下:
ScheduledConfigDao.java
package com.bijian.study.dao; import com.bijian.study.model.ScheduledConfigDTO; public interface ScheduledConfigDao { public ScheduledConfigDTO selectByPrimaryKey(String task_type); public int updateByPrimaryKey(ScheduledConfigDTO record); }
ScheduledConfigDTO.java
package com.bijian.study.model; import java.util.Date; public class ScheduledConfigDTO { private String task_type; private Date exec_time; //private String exec_time; private Date update_time; public String getTask_type() { return task_type; } public void setTask_type(String task_type) { this.task_type = task_type; } public Date getExec_time() { return exec_time; } public void setExec_time(Date exec_time) { this.exec_time = exec_time; } // public String getExec_time() { // return exec_time; // } // // public void setExec_time(String exec_time) { // this.exec_time = exec_time; // } public Date getUpdate_time() { return update_time; } public void setUpdate_time(Date update_time) { this.update_time = update_time; } @Override public String toString() { return "CoreScheduledConfigDTO [task_type=" + task_type + ", exec_time=" + exec_time + ", update_time=" + update_time + "]"; } }
ScheduledConfigDaoMapper.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.bijian.study.dao.ScheduledConfigDao"> <resultMap id="BaseResultMap" type="com.bijian.study.model.ScheduledConfigDTO"> <id column="task_type" property="task_type" jdbcType="VARCHAR"/> <result column="exec_time" property="exec_time" jdbcType="TIMESTAMP"/> <result column="update_time" property="update_time" jdbcType="TIMESTAMP"/> </resultMap> <sql id="Base_Column_List"> task_type,exec_time,update_time </sql> <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.String"> select <include refid="Base_Column_List" /> from scheduled_config where task_type = #{task_type,jdbcType=INTEGER} for update </select> <update id="updateByPrimaryKey" parameterType="com.bijian.study.model.ScheduledConfigDTO"> update scheduled_config <set> <if test="exec_time != null"> exec_time = #{exec_time,jdbcType=TIMESTAMP}, </if> <if test="update_time != null"> update_time = #{update_time,jdbcType=TIMESTAMP}, </if> </set> where task_type = #{task_type,jdbcType=VARCHAR} </update> </mapper>
MyBatisBasicTest.java
package com.bijian.study.test; import java.io.IOException; import java.io.Reader; import java.util.Date; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.BeforeClass; import org.junit.Test; import org.slf4j.Logger; import org.slf4j.LoggerFactory; import com.bijian.study.dao.ScheduledConfigDao; import com.bijian.study.model.ScheduledConfigDTO; public class MyBatisBasicTest { private static final Logger log = LoggerFactory.getLogger(MyBatisBasicTest.class); private static SqlSessionFactory sqlSessionFactory; private static Reader reader; @BeforeClass public static void initial() { try { reader = Resources.getResourceAsReader("Configuration.xml"); sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader); } catch (IOException e) { log.error("Error thrown while reading the configuration: {}", e); } finally { if (reader != null) { try { reader.close(); } catch (IOException e) { log.error("Error thrown while closing the reader: {}", e); } } } } @Test public void queryInInterfaceWayTest() { SqlSession session = sqlSessionFactory.openSession(); ScheduledConfigDao scheduledConfigDao = session.getMapper(ScheduledConfigDao.class); ScheduledConfigDTO scheduledConfigDTO = scheduledConfigDao.selectByPrimaryKey("02"); //ScheduledConfigDTO scheduledConfigDTO = scheduledConfigDao.selectByPrimaryKey("1"); log.info("scheduledConfigDTO:{}", scheduledConfigDTO); //scheduledConfigDTO.setExec_time("2016-11-08 10:01:01"); //scheduledConfigDTO.setExec_time("20161108100112"); //scheduledConfigDTO.setExec_time(new Date()); scheduledConfigDTO.setExec_time(null); scheduledConfigDTO.setUpdate_time(new Date()); log.info("scheduledConfigDTO:{}", scheduledConfigDTO); int count = scheduledConfigDao.updateByPrimaryKey(scheduledConfigDTO); session.commit(); log.info("{}", count); } }
Configuration.xml
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <typeAliases><!-- 别名 --> <typeAlias alias="ScheduledConfigDTO" type="com.bijian.study.model.ScheduledConfigDTO" /> </typeAliases> <environments default="development"> <environment id="development"> <transactionManager type="JDBC"/> <dataSource type="POOLED"><!-- 数据源 --> <property name="driver" value="com.mysql.jdbc.Driver" /> <property name="url" value="jdbc:mysql://127.0.0.1:3306/hbatis" /> <property name="username" value="test" /> <property name="password" value="test" /> </dataSource> </environment> </environments> <mappers><!-- ORM映射文件 --> <mapper resource="com/bijian/study/model/ScheduledConfigDaoMapper.xml" /> </mappers> </configuration>
mysql.sql
-- Create the database named 'hbatis'. -- It's OK to use `, not OK to use ' or " surrounding the database name to prevent it from being interpreted as a keyword if possible. CREATE DATABASE IF NOT EXISTS `hbatis` DEFAULT CHARACTER SET = `UTF8`; -- Create a table named 'User' DROP TABLE IF EXISTS `scheduled_config`; CREATE TABLE `scheduled_config` ( `task_type` varchar(2) NOT NULL COMMENT '任务类型', `exec_time` varchar(20) DEFAULT NULL COMMENT '上次执行时间', `update_time` timestamp NULL DEFAULT NULL COMMENT '修改时间', PRIMARY KEY (`task_type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- Insert a test record Insert INTO `scheduled_config` VALUES ('1', '20161108101751', '2016-11-08 10:17:51'); Insert INTO `scheduled_config` VALUES ('02', '2016-11-08 00:01:02', '2016-11-08 10:17:51'); -- drop table drop table `scheduled_config`;
mysql2.sql
-- Create the database named 'hbatis'. -- It's OK to use `, not OK to use ' or " surrounding the database name to prevent it from being interpreted as a keyword if possible. CREATE DATABASE IF NOT EXISTS `hbatis` DEFAULT CHARACTER SET = `UTF8`; -- Create a table named 'User' DROP TABLE IF EXISTS `scheduled_config`; CREATE TABLE `scheduled_config` ( `task_type` varchar(2) NOT NULL COMMENT '任务类型', `exec_time` timestamp NULL DEFAULT NULL COMMENT '上次执行时间', `update_time` timestamp NULL DEFAULT NULL COMMENT '修改时间', PRIMARY KEY (`task_type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- Insert a test record Insert INTO `scheduled_config` VALUES ('1', '20161108101751', '2016-11-08 10:17:51'); Insert INTO `scheduled_config` VALUES ('02', '2016-11-08 00:01:02', '2016-11-08 10:17:51'); -- drop table drop table `scheduled_config`; /* 查数据表如下所示: mysql> select * from core_scheduled_config; +-----------+---------------------+---------------------+ | task_type | exec_time | update_time | +-----------+---------------------+---------------------+ | 02 | 2016-11-08 00:01:02 | 2016-11-08 10:17:51 | | 1 | 2016-11-08 10:17:51 | 2016-11-08 10:17:51 | +-----------+---------------------+---------------------+ 2 rows in set (0.01 sec) */
mysql3.sql
-- Create the database named 'hbatis'. -- It's OK to use `, not OK to use ' or " surrounding the database name to prevent it from being interpreted as a keyword if possible. CREATE DATABASE IF NOT EXISTS `hbatis` DEFAULT CHARACTER SET = `UTF8`; -- Create a table named 'User' DROP TABLE IF EXISTS `scheduled_config`; CREATE TABLE `scheduled_config` ( `task_type` varchar(2) NOT NULL COMMENT '任务类型', -- `exec_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '上次执行时间', `exec_time` timestamp NOT NULL COMMENT '上次执行时间', `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后一次修改时间', PRIMARY KEY (`task_type`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- Insert a test record Insert INTO `scheduled_config` VALUES ('1', '20161108101751', '2016-11-08 10:17:51'); Insert INTO `scheduled_config` VALUES ('02', '2016-11-08 00:01:02', '2016-11-08 10:17:51'); -- drop table drop table `scheduled_config`;
唯一需要注意的是:如果数据库表是TIMESTAMP类型的,初始化语句字段内容为20161108101751,也会转换为2016-11-08 10:17:51TIMESTAMP类型。
附:MySQL数据表字段为timestamp类型的坑
当使用MYSQL,建表使用timestamp类型时,没有默认default CURRENT_TIME,会导致创建的timestamp默认加上default CURRNET_TIME ON UPDATE CURRENT_TIMESTAMP,解决方法:
1.修改字段类型为datetime类型(如果允许允许)
2.更改表的ddl,给timestamp加上default CURRENT_TIMESTAMP
相关推荐
mybatis deom.mybatis 的简单入门实例。基于maven开发的。
MyBatis九步详细实例操作 实用用例 简单明了
jeecg-mybatis-framework项目实例
spring+springMVC+mybatis+maven整合实例
mybatis中操作json类型数据(csdn)————程序
mybatis 实例 mybatis 实例 mybatis 实例 mybatis 实例 mybatis 实例
MyBatis经典入门实例,适合新手学习用。
用于学习mybatis源码的一个例子;结合idea的debug工具查看mybatis的运行原理
Spring CXF Mybatis 发布 Web Service 实例
mybatis中文离线文档
Mybatis的代码实例,包括增删改查
Mybatis入门项目实例,笔记,内含单独mybatis jar包
SpringMVC+Mybatis+Maven搭建实例以及Mybatis-Generator工具类
mybatis简单实例mybatis简单实例
本实例是mybatis与spring的整合实例,代码一目了然,很容易看懂
spring3.0mvc自学教程ppt+注解教程+注解实例+springmybatis教程+项目实例+中文api 初学(自学)着的不二法定,从入门到项目实例深入学习(浅-深)
mybatis - Springboot+Mybatis+MySql搭建实例.zip
Mybatis-Spring整合实例,适合新手学习使用
MyBatis Geneator 文档+实例..值得拥有