mybatis中操作日期实例分析

r囧r小猫 2022-04-11 08:53 153阅读 0赞

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>
    		<mapper resource="com/bijian/study/model/ScheduledConfigDaoMapper.xml" />
    	</mappers>
    </configuration>

mysql.sql

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

![b1938071-425e-3417-8d3b-46c4ad655658.png][]

分析实例参考文章：http://bijian1013.iteye.com/blog/2313380

[b1938071-425e-3417-8d3b-46c4ad655658.png]: /images/20220411/f37df68cec40475b9533df19c1ef7a07.png