image.png

數(shù)據(jù)庫

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 {

    /**
     * 將數(shù)組使用分隔符變?yōu)樽址?     * 
     * @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++) {
            /**
             * 在前面加，就不會出瑞最后多一個分隔符的現(xiàn)象同時判斷不是第一個才加分隔符
             */
            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數(shù)組轉(zhuǎn)字符串a(chǎn),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>
    <!-- 通用查詢結(jié)果列 -->
    <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

這樣寫入的時候是數(shù)組，在數(shù)據(jù)庫中為字符串站蝠，
查詢出來的時候又轉(zhuǎn)成數(shù)組