mybatis-数组转字符串

image.png

数据库

CREATE TABLE `author` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `bio` text,
  `favourite_section` varchar(25) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=115 DEFAULT CHARSET=utf8;

实体

package com.ghgcn.mybatis_demo.entity;

import java.util.Arrays;

public class Author {

    private Integer id;
    private String username;
    private String password;
    private String email;
    private Integer[] bio;
    private String favouriteSection;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public Integer[] getBio() {
        return bio;
    }

    public void setBio(Integer[] bio) {
        this.bio = bio;
    }

    public String getFavouriteSection() {
        return favouriteSection;
    }

    public void setFavouriteSection(String favouriteSection) {
        this.favouriteSection = favouriteSection;
    }

    @Override
    public String toString() {
        StringBuilder builder = new StringBuilder();
        builder.append("Author [");
        if (id != null)
            builder.append("id=")
                    .append(id)
                    .append(", ");
        if (username != null)
            builder.append("username=")
                    .append(username)
                    .append(", ");
        if (password != null)
            builder.append("password=")
                    .append(password)
                    .append(", ");
        if (email != null)
            builder.append("email=")
                    .append(email)
                    .append(", ");
        if (bio != null)
            builder.append("bio=")
                    .append(Arrays.toString(bio))
                    .append(", ");
        if (favouriteSection != null)
            builder.append("favouriteSection=")
                    .append(favouriteSection);
        builder.append("]");
        return builder.toString();
    }

}

工具类

package com.ghgcn.mybatis_demo.helper;

public class StringHelper {

    /**
     * 将数组使用分隔符变为字符串
     * 
     * @param array
     * @param separator
     * @return
     */
    public static String join(Object[] array, String separator) {
        return join(array, 0, array.length, separator);
    }

    private static String join(Object[] array, int startIndex, int endIndex, String separator) {
        if (separator == null) {
            return "";
        }
        StringBuilder sb = new StringBuilder();

        for (int i = startIndex; i < endIndex; i++) {
            /**
             * 在前面加，就不会出瑞最后多一个分隔符的现象同时判断不是第一个才加分隔符
             */
            if (i > startIndex) {
                sb.append(separator);
            }

            if (array[i] != null) {
                sb.append(array[i]);
            }
        }
        return sb.toString();

    }

    public static void main(String[] args) {
        Integer[] arrays = { 1,2,3,4,5,6,7 };
        String join = join(arrays, ",");
        System.out.println(join);// 1,2,3,4,5,6,7
    }
}

handler

package com.ghgcn.mybatis_demo.handler;

import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;

import com.ghgcn.mybatis_demo.helper.StringHelper;

/**
 * Mybatis数组转字符串a,b,c,d,e
 * 
 * @author 刘楠
 */
@MappedTypes(value = { Integer[].class,Short[].class,Long[].class })
@MappedJdbcTypes(value = JdbcType.VARCHAR)
public class IntArrayJoinTypeHandler extends BaseTypeHandler<Integer[]> {

    String split = ",";

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Integer[] parameter, JdbcType jdbcType)
            throws SQLException {

        ps.setString(i, StringHelper.join(parameter, split));
    }

    @Override
    public Integer[] getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String reString = rs.getString(columnName);
        if (reString != null && reString.length() > 0) {
            String[] arrs = reString.split(split);

            List<Integer> idList = new ArrayList<>(arrs.length);
            for (String id : arrs) {
                if (id != null && id.length() > 0) {
                    idList.add(Integer.valueOf(id));
                }
            }
            return idList.toArray(new Integer[idList.size()]);
        }
        return null;
    }

    @Override
    public Integer[] getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String reString = rs.getString(columnIndex);
        if (reString != null && reString.length() > 0) {
            String[] arrs = reString.split(split);

            List<Integer> idList = new ArrayList<>(arrs.length);
            for (String id : arrs) {
                if (id != null && id.length() > 0) {
                    idList.add(Integer.valueOf(id));
                }
            }
            return idList.toArray(new Integer[idList.size()]);
        }
        return null;
    }

    @Override
    public Integer[] getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String reString = cs.getString(columnIndex);
        if (reString != null && reString.length() > 0) {
            String[] arrs = reString.split(split);

            List<Integer> idList = new ArrayList<>(arrs.length);
            for (String id : arrs) {
                if (id != null && id.length() > 0) {
                    idList.add(Integer.valueOf(id));
                }
            }
            return idList.toArray(new Integer[idList.size()]);
        }
        return null;
    }

}

配置

<?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>
    <properties resource="mybatis/conn.properties">
        <!-- <property name="driver" value="jdbc.driver" /> <property name="url" 
            value="jdbc.url" /> <property name="username" value="jdbc.user" /> <property 
            name="password" value="jdbc.password" /> -->
    </properties>
    <typeAliases>
        <package name="com.ghgcn.mybatis_demo.entity" />
    </typeAliases>
    <typeHandlers>
    <!--声明  -->
        <typeHandler handler="com.ghgcn.mybatis_demo.handler.IntArrayJoinTypeHandler" />
    </typeHandlers>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC" />
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driver}" />
                <property name="url" value="${jdbc.url}" />
                <property name="username" value="${jdbc.user}" />
                <property name="password" value="${jdbc.password}" />
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="mapper/AuthorMapper.xml" />
    </mappers>
</configuration>

mapper

<?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.ghgcn.mybatis_demo.mapper.AuthorMapper">

    <resultMap id="BaseResultMap" type="com.ghgcn.mybatis_demo.entity.Author">
        <id column="id" property="id" />
        <result column="username" property="username" />
        <result column="password" property="password" />
        <result column="email" property="email" />
        <result column="bio" property="bio" typeHandler="com.ghgcn.mybatis_demo.handler.IntArrayJoinTypeHandler"/>
        <result column="favourite_section" property="favouriteSection" />
    </resultMap>
    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id,username,password,email,bio,favourite_section
    </sql>
    <select id="getById" resultMap="BaseResultMap">
        SELECT
        <include refid="Base_Column_List" />
        FROM author
        WHERE id = #{id}
    </select>
    <insert id="insert">
        INSERT INTO author (
        username,
        password,
        email,
        bio,
        favourite_section
        )
        VALUES
        (
        #{username},
        #{password},
        #{email},
        #{bio},
        #{favouriteSection}
        );

    </insert>

    <update id="updateById">

        update author
        <set>
            <if test="username!=null and username !=''">
                username=#{username},
            </if>
            <if test="password!=null and password !=''">
                password=#{password},
            </if>
            <if test="email!=null and email !=''">
                email=#{email},
            </if>
            <if test="bio!=null and bio !=''">
                bio=#{bio},
            </if>
            <if test="favouriteSection!=null and favouriteSection !=''">
                favourite_section=#{favouriteSection},
            </if>
        </set>
        <where>
            id=#{id}
        </where>
    </update>
</mapper>

测试

package com.ghgcn.mybatis_demo.test01;

import java.io.IOException;
import java.io.InputStream;

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 com.ghgcn.mybatis_demo.entity.Author;
import com.ghgcn.mybatis_demo.mapper.AuthorMapper;

public class AuthormapperTest1 {

    public static void main(String[] args) throws IOException {
        String resource = "mybatis/mybatis-config.xml";

        InputStream inputStream = Resources.getResourceAsStream(resource);

        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

        SqlSession sqlSession = sessionFactory.openSession(true);

        AuthorMapper authorMapper = sqlSession.getMapper(AuthorMapper.class);

        Author insertAuthor = new Author();
        insertAuthor.setBio(new Integer[]{ 1,2,3,4,5,6,7,8,9,10,11,22,33,44 });
        insertAuthor.setEmail("qq@qq.com");
        insertAuthor.setFavouriteSection("13800138000");
        insertAuthor.setPassword("13800138000");
        insertAuthor.setUsername("13800138000");
        int insert = authorMapper.insert(insertAuthor);

        System.out.println("insert" + (insert > 0));

        Author byId = authorMapper.getById(110);

        System.out.println("byId:" + byId);
        // Author [id=110, username=13800138000, password=13800138000, email=qq@qq.com, bio=[1, 2, 3, 4, 5, 6, 7, 8, 9,
        // 10, 11, 22], favouriteSection=13800138000]

    }

}

image.png

这样写入的时候是数组，在数据库中为字符串，
查询出来的时候又转成数组