浅入浅出MyBatis(11):动态SQL

January 14th 2015  | Tags: MyBatis, orm

2015-01-14

该文章是《专题:浅入浅出MyBatis》系列文章的一篇。点击进入该专题目录

工具与环境:Intellij IDEA 14,JDK 1.7,MyBatis 3.2,MySQL 5.5。

本文以浅入浅出MyBatis(01):查询id为1的用户的信息为基础。

动态SQL,是指SQL语句不是硬编码到代码中的,而是程序根据不同情况生成不同的SQL语句。MyBatis中动态SQL的内容还是挺多的,本文只做简单的介绍。更多内容请参考官方的动态SQL

浅入浅出MyBatis(01):查询id为1的用户的信息中,若Main.java的main函数如下:

    public static void main(String[] args) throws IOException {

        SqlSessionFactory sessionFactory;
        sessionFactory = new SqlSessionFactoryBuilder()
                .build(Resources.getResourceAsReader("mybatis-config.xml"));

        SqlSession sqlSession = sessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        User user = userMapper.findById(-1);
        System.out.println(user);

    }

执行结果将是:

null

现在我们要实现这样一个功能:

如果findById函数的参数id是负数,我们在SQL语句中将其转换为正数(其相反数),如果id是正数,那么不做处理。

我在了解动态SQL的时候遇到了些问题,也会放在文中。

关于parameterType

浅入浅出MyBatis(01):查询id为1的用户的信息的UserMapper.xml代码中,<select id="findById"></select>parameterType设置为intIntegerHashMap这三者之一,Main.java都是可以正常运行的。

但是如果将letian.mybatis.mapper下的UserMapper.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="letian.mybatis.dao.UserMapper">

    <select id="findById" parameterType="HashMap" resultType="letian.mybatis.bean.User">
        select * from
        blog_db.user where
        <choose>
            <when test="id &lt; 0">
                id = 0-#{id}
            </when>
            <otherwise>
                id = #{id}
            </otherwise>
        </choose>
    </select>

</mapper>

<select id="findById"></select>parameterType设置为intIntegerHashMap其中任意一个,在运行Main.java将得到以下错误:

Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'id' in 'class java.lang.Integer'
### Cause: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'id' in 'class java.lang.Integer'
......

也许是bug,我把问题提交到了https://github.com/mybatis/mybatis-3/issues/329

最终可用的代码(方法1)

修改letian.mybatis.dao下的UserMapper.java

将其内容修改如下:

package letian.mybatis.dao;

import letian.mybatis.bean.User;

import java.util.HashMap;

public interface UserMapper {
    User findById(HashMap<String, Integer> id);
}

修改letian.mybatis.mapper下的UserMapper.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="letian.mybatis.dao.UserMapper">

    <select id="findById" parameterType="HashMap" resultType="letian.mybatis.bean.User">
        select * from
        blog_db.user where
        <choose>
            <when test="id &lt; 0">
                id = 0-#{id}
            </when>
            <otherwise>
                id = #{id}
            </otherwise>
        </choose>

    </select>

</mapper>

修改Main.java

将其内容修改为:

import java.io.IOException;
import java.util.HashMap;

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 letian.mybatis.bean.User;
import letian.mybatis.dao.UserMapper;


public class Main {

    public static void main(String[] args) throws IOException {

        SqlSessionFactory sessionFactory;
        sessionFactory = new SqlSessionFactoryBuilder()
                .build(Resources.getResourceAsReader("mybatis-config.xml"));

        SqlSession sqlSession = sessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        HashMap<String, Integer> userId = new HashMap<String, Integer>();
        userId.put("id", -2);
        User user = userMapper.findById(userId);
        System.out.println(user);

        sqlSession.close();

    }
}

执行Main.java

结果如下:

User{id=2, name='xiaosi', email='xiaosi@111.com', password='123'}

最终可用的代码(方法2-使用注解)

修改letian.mybatis.dao下的UserMapper.java

将其内容修改如下:

package letian.mybatis.dao;

import letian.mybatis.bean.User;
import org.apache.ibatis.annotations.Param;

public interface UserMapper {

    User findById(@Param("id")int id);

}

修改letian.mybatis.mapper下的UserMapper.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="letian.mybatis.dao.UserMapper">

    <select id="findById" parameterType="HashMap" resultType="letian.mybatis.bean.User">
        select * from
        blog_db.user where
        <choose>
            <when test="id &lt; 0">
                id = 0-#{id}
            </when>
            <otherwise>
                id = #{id}
            </otherwise>
        </choose>

    </select>

</mapper>

修改Main.java

import java.io.IOException;

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 letian.mybatis.bean.User;
import letian.mybatis.dao.UserMapper;


public class Main {

    public static void main(String[] args) throws IOException {

        SqlSessionFactory sessionFactory;
        sessionFactory = new SqlSessionFactoryBuilder()
                .build(Resources.getResourceAsReader("mybatis-config.xml"));

        SqlSession sqlSession = sessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        User user = userMapper.findById(-2);
        System.out.println(user);

    }
}

执行Main.java

结果如下:

User{id=2, name='xiaosi', email='xiaosi@111.com', password='123'}

(完)