关于mybatis和mysql的几点体会

最新推荐文章于 2025-01-31 19:40:14 发布

原创最新推荐文章于 2025-01-31 19:40:14 发布 · 5.8k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis返回Map #mybatis返回数值类型 #mybatis集合参数为空判断

Java 同时被 3 个专栏收录

27 篇文章

订阅专栏

Mysql

4 篇文章

订阅专栏

Mybatis

2 篇文章

订阅专栏

本文探讨了SQL中使用union操作符时不同数值类型的合并问题，特别是在MyBatis框架下如何处理可能出现的数据类型转换，以及如何通过Java的Number类来统一处理各种数值类型。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

原理不明，纯属经验

#查询返回结果为Map

<!--<select id="test" resultType="map">-->
<!--这是一个统计某天数据的例子，resultType两种写法应该都是可以的-->
<select id="test" resultType="java.util.Map">
	select 
		temp.tinyint_column as tinyintColumn, <!--Long-->
		sum(temp.cnt) as cnt <!--BigDecimal-->
	from
	(
		select 
			tinyint_column, <!--Integer-->
			count(*) as cnt <!--Long-->
		from
			test_table
		<where>
			day = #{dayInt}
		</where>
		group by tinyint_column
		union
		select 
			tinyint_column -1 as tinyint_column, <!--Integer-->
			count(*) as cnt <!--Long-->
		from
			test_table
		<where>
			day > #{dayInt} and tinyint_column > 1
		</where>
		group by tinyint_column
	) temp
	group by temp.tinyint_column
</select>

类似于这样的嵌套查询，返回值类型在运算中会变化，
`
个人认为：
union 操作符将两部分数据合并时如果不改变数值类型就有可能存在数值长度问题

Integer+Integer长度可能会超出Integer所以返回类型为Long
Long+Long长度可能会超出Long，故而返回类型为BigDecimal

无论返回何种数值类型，都属于Number类的子类，所以dao中这么写
`

public List<Map<String, Number>> test() {
	int dayInt = 20180424;
	return getSqlSession().selectList(NAME_SPACE + ".test", dayInt);
}

>`这块存在一个问题，就是如果返回值使用List

然后在使用的时候，可以灵活取出相应数值类型，下面附上Number源码

 * @see     java.lang.Byte
 * @see     java.lang.Double
 * @see     java.lang.Float
 * @see     java.lang.Integer
 * @see     java.lang.Long
 * @see     java.lang.Short
 * @since   JDK1.0
 */
public abstract class Number implements java.io.Serializable {
    /**
     * Returns the value of the specified number as an <code>int</code>.
     * This may involve rounding or truncation.
     *
     * @return  the numeric value represented by this object after conversion
     *          to type <code>int</code>.
     */
    public abstract int intValue();

    /**
     * Returns the value of the specified number as a <code>long</code>.
     * This may involve rounding or truncation.
     *
     * @return  the numeric value represented by this object after conversion
     *          to type <code>long</code>.
     */
    public abstract long longValue();

    /**
     * Returns the value of the specified number as a <code>float</code>.
     * This may involve rounding.
     *
     * @return  the numeric value represented by this object after conversion
     *          to type <code>float</code>.
     */
    public abstract float floatValue();

    /**
     * Returns the value of the specified number as a <code>double</code>.
     * This may involve rounding.
     *
     * @return  the numeric value represented by this object after conversion
     *          to type <code>double</code>.
     */
    public abstract double doubleValue();

    /**
     * Returns the value of the specified number as a <code>byte</code>.
     * This may involve rounding or truncation.
     *
     * @return  the numeric value represented by this object after conversion
     *          to type <code>byte</code>.
     * @since   JDK1.1
     */
    public byte byteValue() {
        return (byte)intValue();
    }

    /**
     * Returns the value of the specified number as a <code>short</code>.
     * This may involve rounding or truncation.
     *
     * @return  the numeric value represented by this object after conversion
     *          to type <code>short</code>.
     * @since   JDK1.1
     */
    public short shortValue() {
        return (short)intValue();
    }

    /** use serialVersionUID from JDK 1.0.2 for interoperability */
    private static final long serialVersionUID = -8742448824652078965L;
}

#判断集合参数是否为空

<if test="listIds != null and listIds.size() > 0">
	or id in
	<foreach collection="listIds" item="id" open="(" close=")" separator=",">
		#{id}
	</foreach>
</if>