胖先森

MyBatis数据类型转换,默认情况下MyBatis已经帮我们完成了很多的数据类型的转换,一般情况下我们直接使用即可,下面大概介绍一下

一 Java日期类型和Jdbc字符串类型转换

在做开发时，我们经常会遇到这样一些问题，比如我有一个Java中的Date数据类型，我想将之存到数据库的时候存成一个1970年至今的毫秒数，怎么实现？

就是我直接向数据库写数据，要写的是一个Date对象，但是写到数据库之后这个Date对象就变成了Date对象所描述的时间到1970年的秒数了，然后当我从数据库读取这个秒数之后，系统又会自动帮我将这个秒数转为Date对象，就是这样两个需求.

1.新增数据库字段

2.持久化类中代码

public class User implements java.io.Serializable{

    private static final long serialVersionUID = 1L;

    private Integer user_id;
    private String account;
    private String password;
    private String user_name;
    private Integer status;
    private Date login_time;
    private String ip;
    private Integer fk_role_id;
    //注意这里是java.util.Date类型
    private Date create_time;
  
}

3.自定义typeHandler继承自BaseTypeHandler

自定义typeHandler我们有两种方式:

• 实现TypeHandler接口
• 简化的写法就是继承自BaseTypeHandler类

package com.shxt.type;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

public class MyDateTypeHandler extends BaseTypeHandler<Date> {

    @Override
    public Date getNullableResult( ResultSet rs , String columnName ) throws SQLException {
        return this.getDate(rs.getLong(columnName));
    }

    @Override
    public Date getNullableResult( ResultSet rs , int columnIndex ) throws SQLException {
        return this.getDate(rs.getLong(columnIndex));
    }

    @Override
    public Date getNullableResult( CallableStatement cs , int columnIndex ) throws SQLException {
        return this.getDate(cs.getLong(columnIndex));
    }

    @Override
    public void setNonNullParameter( PreparedStatement ps , int index , Date parameter , JdbcType jdbcType )
            throws SQLException {
        ps.setString(index, String.valueOf(parameter.getTime()));

    }

    private Date getDate(Long columnValue){
        if(columnValue==null){
            return null;
        }
        return new Date(columnValue);
    }

}

4.在Mapper中进行配置

自定义好了typeHandler之后，接下来我们需要在UserMapper.xml中进行简单的配置，首先我们可以像上文说的，配置resultMap，如下：

<mapper namespace="com.shxt.dao.UserDao">
     <resultMap type="com.shxt.model.User" id="BaseResultMapper">
        <id column="user_id" property="user_id"/>
        <result column="account" property="account"/>
        <result column="password" property="password"/>
        <result column="user_name" property="user_name"/>
        <result column="status" property="status"/>
        <result column="login_time" property="login_time"/>
        <result column="ip" property="ip"/>
        <result column="fk_role_id" property="fk_role_id"/>
        <!-- 使用自定义的转换 -->
        <result typeHandler="com.shxt.type.MyDateTypeHandler" 
            column="create_time" jdbcType="VARCHAR"
            property="create_time" javaType="java.util.Date"
        />
    </resultMap>
    
    <sql id="sys_user_columns">
        user_id,account,password,user_name,status,login_time,ip,fk_role_id,create_time
    </sql>
    <select id="load" parameterType="int" resultMap="BaseResultMapper">
        SELECT
            <include refid="sys_user_columns"/>
        FROM
            sys_user
        WHERE user_id=#{user_id}
    </select>
</mapper>

5.查询数据

6.测试代码

<result typeHandler="com.shxt.type.MyDateTypeHandler" 
            column="create_time" jdbcType="VARCHAR"
            property="create_time" javaType="java.util.Date"
        />

    @Test
    public void 数据类型转换(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();

            User u1 = sqlSession.selectOne(UserDao.class.getName()+".load",-888);
            System.out.println("第一次查询:"+u1);


        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }

    }

7.控制台运行结果

DEBUG [main] - ==>  Preparing: SELECT user_id,account,password,user_name,status,login_time,ip,fk_role_id,create_time FROM sys_user WHERE user_id=? 
DEBUG [main] - ==> Parameters: -888(Integer)
TRACE [main] - <==    Columns: user_id, account, password, user_name, status, login_time, ip, fk_role_id, create_time
TRACE [main] - <==        Row: -888, admin, admin, 悟空, 1, 2017-07-30 09:50:47.0, , -100, 1504618580
DEBUG [main] - <==      Total: 1
第一次查询:User [user_id=-888, account=admin, password=admin, user_name=悟空, status=1, login_time=Sun Jul 30 09:50:47 CST 2017, ip=, fk_role_id=-100, create_time=Sun Jan 18 17:56:58 CST 1970]

create_time=Sun Jan 18 17:56:58 CST 1970 将字符串转换为了日期类型

8.添加数据映射文件

这种方式有一个缺点那就是只适用于查询操作，即在查询的过程中系统会启用我们自定义的typeHandler，会将秒数转为Date对象，但是在插入的时候却不会启用我们自定义的typeHandler，想要在插入的时候启用自定义的typeHandler，需要我们在insert节点中简单配置一下

    <insert id="add01" parameterType="com.shxt.model.User">
        INSERT INTO
            sys_user
            (account,password,user_name,create_time)
            VALUES
            (#{account},#{password},#{user_name},
            #{create_time,javaType=Date,jdbcType=VARCHAR,typeHandler=com.shxt.type.MyDateTypeHandler})
    </insert>

或者配置如下

<insert id="add02" parameterType="com.shxt.model.User">
        INSERT INTO
            sys_user
            (account,password,user_name,create_time)
            VALUES
            (#{account},#{password},#{user_name},
            #{create_time,typeHandler=com.shxt.type.MyDateTypeHandler})
</insert>

9.测试代码

    @Test
    public void 添加方式1(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();

            User user = new User();
            user.setAccount("tangseng");
            user.setPassword("123");
            user.setUser_name("唐僧");
            user.setCreate_time(new Date());

            sqlSession.insert(UserDao.class.getName()+".add02", user);

            sqlSession.commit();

        }catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }

    }

二 List< String >类型和Jdbc字符串的转换

1.准备的SQL语句

CREATE TABLE `person` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`hobby`  varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=1
ROW_FORMAT=DYNAMIC
;

2.新建持久化类

public class Person {
    private Integer id;
    private List<String> hobbyList;

    public Integer getId() {
        return this.id;
    }
    public void setId( Integer id ) {
        this.id = id;
    }
    public List<String> getHobbyList() {
        return this.hobbyList;
    }
    public void setHobbyList( List<String> hobbyList ) {
        this.hobbyList = hobbyList;
    }
    @Override
    public String toString() {
        return "Person [id=" + this.id + ", hobbyList=" + this.hobbyList + "]";
    }


}

3.自定义处理类型

package com.shxt.type;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

public class MyListTypeHandler extends BaseTypeHandler<List<String>> {

    @Override
    public List<String> getNullableResult( ResultSet rs , String columnName ) throws SQLException {
        return this.getList(rs.getString(columnName));
    }

    @Override
    public List<String> getNullableResult( ResultSet rs , int columnIndex ) throws SQLException {
        return this.getList(rs.getString(columnIndex));
    }

    @Override
    public List<String> getNullableResult( CallableStatement cs , int columnIndex ) throws SQLException {
        return this.getList(cs.getString(columnIndex));
    }

    @Override
    public void setNonNullParameter( PreparedStatement ps , int index , List<String> parameter , JdbcType jdbcType )
            throws SQLException {
        //1.List集合转字符串
        StringBuffer sb = new StringBuffer();
        for (String value : parameter) {
            sb.append(value).append(",");
        }
        //2.设置给ps
        ps.setString(index, sb.toString().substring(0, sb.toString().length() - 1));

    }

    private List<String> getList(String columnValue){
        if (columnValue == null) {
            return null;
        }
        return  Arrays.asList(columnValue.split(","));
    }

}

4.新建映射文件

<mapper namespace="com.shxt.model.Person">
     <resultMap type="com.shxt.model.Person" id="BaseResultMapper">
        <id column="id" property="id"/>
        <!-- 使用自定义的转换 -->
        <result typeHandler="com.shxt.type.MyListTypeHandler" 
            column="hobby" jdbcType="VARCHAR"
            property="hobbyList" javaType="list"
        />
    </resultMap>
    <select id="load" parameterType="int" resultMap="BaseResultMapper">
        SELECT
            id,hobby
        FROM
            person
        WHERE id=#{id}
    </select>
</mapper>

5.测试代码

    @Test
    public void 查询02(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();

            Person p = sqlSession.selectOne(Person.class.getName()+".load",1);
            System.out.println(p);
          
        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

6.控制台运行结果

DEBUG [main] - ==>  Preparing: SELECT id,hobby FROM person WHERE id=? 
DEBUG [main] - ==> Parameters: 1(Integer)
TRACE [main] - <==    Columns: id, hobby
TRACE [main] - <==        Row: 1, basketball,football,music
DEBUG [main] - <==      Total: 1

Person [id=1, hobbyList=[basketball, football, music]]

7.添加数据映射文件

    <!-- 添加1 -->
    <insert id="add" parameterType="com.shxt.model.Person">
        INSERT INTO
            person
            (hobby)
            VALUES
            (
            #{hobbyList,javaType=java.util.List,jdbcType=VARCHAR,typeHandler=com.shxt.type.MyListTypeHandler}
            )
    </insert>

8.测试代码

    @Test
    public void 添加方式02(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();

            List<String> hobbyList = new ArrayList<String>();
            hobbyList.add("music");
            hobbyList.add("book");

            Person p = new Person();
            p.setHobbyList(hobbyList);

            sqlSession.insert(Person.class.getName()+".add", p);

            sqlSession.commit();

        }catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }

    }

9.控制台运行结果

DEBUG [main] - ==>  Preparing: INSERT INTO person (hobby) VALUES ( ? ) 
DEBUG [main] - ==> Parameters: music,book(String)
DEBUG [main] - <==    Updates: 1

三 Java 数组和Jdbc字符串的转换

该着上面的List<String>

1.自定义类型

package com.shxt.type;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

public class MyArrayTypeHander extends BaseTypeHandler<String[]> {
    /**
     * 获取数据结果集时把数据库类型转换为对应的Java类型
     * @param rs 当前的结果集
     * @param columnName 当前的字段名称
     * @return  转换后的Java对象
     * @throws SQLException
     */
    @Override
    public String[] getNullableResult( ResultSet rs , String columnName ) throws SQLException {
        return this.getStringArray(rs.getString(columnName));
    }
    /**
     * 通过字段位置获取字段数据时把数据库类型转换为对应的Java类型
     * @param rs 当前的结果集
     * @param columnIndex 当前字段的位置
     * @return 转换后的Java对象
     * @throws SQLException
     */
    @Override
    public String[] getNullableResult( ResultSet rs , int columnIndex ) throws SQLException {
        return this.getStringArray(rs.getString(columnIndex));
    }
    /**
     * 调用存储过程后把数据库类型的数据转换为对应的Java类型
     * @param cs  当前的CallableStatement执行后的CallableStatement
     * @param columnIndex  当前输出参数的位置
     * @return
     * @throws SQLException
     */
    @Override
    public String[] getNullableResult( CallableStatement cs , int columnIndex ) throws SQLException {
        return this.getStringArray(cs.getString(columnIndex));
    }
    /**
     * 把Java类型参数转换为对应的数据库类型
     * @param ps 当前的PreparedStatement对象
     * @param index 当前参数位置
     * @param parameter 当前参数的Java对象
     * @param jdbcType 当前参数的数据库类型
     * @throws SQLException
     */
    @Override
    public void setNonNullParameter( PreparedStatement ps , int index , String[] parameter , JdbcType jdbcType )
            throws SQLException {
        // 由于BaseTypeHandler中已经把parameter为null的情况做了处理，所以这里我们就不用在判断parameter是否为空，直接用就可以了
        StringBuffer result = new StringBuffer();
        for (String value : parameter) {
            result.append(value).append(",");
        }
        result.deleteCharAt(result.length() - 1);

        ps.setString(index, result.toString());

    }

    /**
     * 讲"book,music"转化为数组对象
     * @param columnValue
     * @return
     */
    private String[] getStringArray(String columnValue) {
        if (columnValue == null) {
            return null;
        }
        return columnValue.split(",");
    }

}

2.映射文件代码

<mapper namespace="com.shxt.model.Person">
     <resultMap type="com.shxt.model.Person" id="BaseResultMapper">
        <id column="id" property="id"/>
        <!-- 数据库字符串转List<String> -->
        <result typeHandler="com.shxt.type.MyListTypeHandler" 
            column="hobby" jdbcType="VARCHAR"
            property="hobbyList" javaType="list"
        />
        <!-- 数据库字符串转Boolean -->
        <result typeHandler="com.shxt.type.MyBooleanTypeHandler"
            column="flag" jdbcType="VARCHAR"
            property="flag" javaType="boolean"
        />
        <!-- 数据库字符串转String[] -->
         <result typeHandler="com.shxt.type.MyArrayTypeHander"
            column="hobbyArray" jdbcType="VARCHAR"
            property="hobbyArray" javaType="[Ljava.lang.String;"
        />
        
    </resultMap>
    <select id="load" parameterType="int" resultMap="BaseResultMapper">
        SELECT
            id,hobby,flag,hobby hobbyArray
        FROM
            person
        WHERE id=#{id}
    </select>
</mapper>

3.测试代码

    @Test
    public void 查询02(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();

            Person p = sqlSession.selectOne(Person.class.getName()+".load",1);
            System.out.println(p);


        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

4.控制台运行结果

DEBUG [main] - ==>  Preparing: SELECT id,hobby,flag,hobby hobbyArray FROM person WHERE id=? 
DEBUG [main] - ==> Parameters: 1(Integer)
TRACE [main] - <==    Columns: id, hobby, flag, hobbyArray
TRACE [main] - <==        Row: 1, basketball,football,music, N, basketball,football,music
DEBUG [main] - <==      Total: 1
Person [id=1, hobbyList=[basketball, football, music], flag=false, hobbyArray=[basketball, football, music]]

5.添加操作映射文件

    <insert id="add" parameterType="com.shxt.model.Person">
        INSERT INTO
            person
            (hobby,flag)
            VALUES
            (
            #{hobbyArray,typeHandler=com.shxt.type.MyArrayTypeHander}
            ,
            #{flag,typeHandler=com.shxt.type.MyBooleanTypeHandler}
            )
    </insert>

6.测试代码

    @Test
    public void 添加方式02(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();


            Person p = new Person();
            p.setHobbyArray(new String[]{"book","music"});
            p.setFlag(true);

            sqlSession.insert(Person.class.getName()+".add", p);

            sqlSession.commit();

        }catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }

    }

7.控制台运行结果

DEBUG [main] - ==>  Preparing: INSERT INTO person (hobby,flag) VALUES ( ? , ? ) 
DEBUG [main] - ==> Parameters: book,music(String), Y(String)
DEBUG [main] - <==    Updates: 1

Java 布尔类型和Jdbc字符串的转换

需求场景：当数据库中保存'Y'/'N'，而对应bean字段的值的类型为boolean，这是就需要我们自定义类型转换器，在Mybatis执行SQL得到结果时，通过自定义类型转换器将CHAR或者VARCHAR2类型转换为boolean类型，Java代码如下：

1.自定义类型

package com.shxt.type;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;

public class MyBooleanTypeHandler extends BaseTypeHandler<Boolean> {

    private Boolean getBoolean(String flag){
        Boolean bool = Boolean.FALSE;
        if (flag.equalsIgnoreCase("Y")){
            bool = Boolean.TRUE;
        }
        return bool;
    }

    @Override
    public Boolean getNullableResult( ResultSet rs , String columnName ) throws SQLException {
        return this.getBoolean(rs.getString(columnName));
    }

    @Override
    public Boolean getNullableResult( ResultSet rs , int columnIndex ) throws SQLException {
        return this.getBoolean(rs.getString(columnIndex));
    }

    @Override
    public Boolean getNullableResult( CallableStatement cs , int columnIndex ) throws SQLException {
        return this.getBoolean(cs.getString(columnIndex));
    }

    @Override
    public void setNonNullParameter( PreparedStatement ps , int index , Boolean parameter , JdbcType jdbcType )
            throws SQLException {
        String flag = parameter?"Y":"N";
        ps.setString(index, flag);
    }

}

2.映射文件

<mapper namespace="com.shxt.model.Person">
     <resultMap type="com.shxt.model.Person" id="BaseResultMapper">
        <id column="id" property="id"/>
        <!-- 使用自定义的转换 -->
        <result typeHandler="com.shxt.type.MyListTypeHandler" 
            column="hobby" jdbcType="VARCHAR"
            property="hobbyList" javaType="list"
        />
        
        <result typeHandler="com.shxt.type.MyBooleanTypeHandler"
            column="flag" jdbcType="VARCHAR"
            property="flag" javaType="boolean"
        />
    </resultMap>
    <select id="load" parameterType="int" resultMap="BaseResultMapper">
        SELECT
            id,hobby,flag
        FROM
            person
        WHERE id=#{id}
    </select>
</mapper>

3.测试代码

    @Test
    public void 查询02(){
        SqlSession sqlSession = null;
        try {
            sqlSession = MyBatisUtils.getSqlSession();

            Person p = sqlSession.selectOne(Person.class.getName()+".load",1);
            System.out.println(p);


        } finally {
            MyBatisUtils.closeSqlSession(sqlSession);
        }
    }

4.控制台运行结果

DEBUG [main] - ==>  Preparing: SELECT id,hobby,flag FROM person WHERE id=? 
DEBUG [main] - ==> Parameters: 1(Integer)
TRACE [main] - <==    Columns: id, hobby, flag
TRACE [main] - <==        Row: 1, basketball,football,music, N
DEBUG [main] - <==      Total: 1

Person [id=1, hobbyList=[basketball, football, music], flag=false]

5.添加操作映射文件

    <insert id="add" parameterType="com.shxt.model.Person">
        INSERT INTO
            person
            (hobby,flag)
            VALUES
            (
            #{hobbyList,javaType=java.util.List,jdbcType=VARCHAR,typeHandler=com.shxt.type.MyListTypeHandler}
            ,
            #{flag,typeHandler=com.shxt.type.MyBooleanTypeHandler}
            )
    </insert>

6.控制台运行结果

DEBUG [main] - ==>  Preparing: INSERT INTO person (hobby,flag) VALUES ( ? , ? ) 
DEBUG [main] - ==> Parameters: music,book(String), Y(String)
DEBUG [main] - <==    Updates: 1

附录A : 常用java类型和jdbc类型对应表

一个简单的结果集映射示例

<resultMap type="java.util.Map" id="resultjcm">
  <result property="FLD_NUMBER" column="FLD_NUMBER"  javaType="double" jdbcType="NUMERIC"/>
  <result property="FLD_VARCHAR" column="FLD_VARCHAR" javaType="string" jdbcType="VARCHAR"/>
  <result property="FLD_DATE" column="FLD_DATE" javaType="java.sql.Date" jdbcType="DATE"/>
  <result property="FLD_INTEGER" column="FLD_INTEGER"  javaType="int" jdbcType="INTEGER"/>
  <result property="FLD_DOUBLE" column="FLD_DOUBLE"  javaType="double" jdbcType="DOUBLE"/>
  <result property="FLD_LONG" column="FLD_LONG"  javaType="long" jdbcType="INTEGER"/>
  <result property="FLD_CHAR" column="FLD_CHAR"  javaType="string" jdbcType="CHAR"/>
  <!-- 如果自己不知道写说明,那么就可以省略javaType和jdbcType也是可以的 -->
  <result property="FLD_BLOB" column="FLD_BLOB"  javaType="[B" jdbcType="BLOB" />
  <result property="FLD_CLOB" column="FLD_CLOB"  javaType="string" jdbcType="CLOB"/>
  <result property="FLD_FLOAT" column="FLD_FLOAT"  javaType="float" jdbcType="FLOAT"/>
  <result property="FLD_TIMESTAMP" column="FLD_TIMESTAMP"  javaType="java.sql.Timestamp" jdbcType="TIMESTAMP"/>
 </resultMap>

如果自己不知道写说明,那么就可以省略javaType和jdbcType也是可以的

附录B : Oracle数据类型和对应的java类型

用mybatis generator生成代码后，执行查询语句时，oracle里的Date类型字段只精确到年月日，后面时分秒都为零。

jdbcType="DATE"时候，存入到数据库中的字段只有年月日!

后来发现是jdbcType问题，改成 jdbcType="TIMESTAMP" 就可以。（原先默认生成时是jdbcType="DATE"）