MyBatis的动态应用SQL

MyBatis的动态SQL是基于OGNL表达式的，它可以帮助我们方便的在SQL语句中实现某些逻辑。

MyBatis中用于实现动态SQL的元素主要有：

if
where
set
choose（when，otherwise）
trim
foreach

在使用MyBatis前搭建一下框架

练习时用的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.bdqn.dao.UserDao">

<resultMap type="User" id="userlist">
                <!--  类名          数据库表名 -->
    <result property="uid" column="uid"></result>
    <result property="uname" column="uname"></result>
    <result property="ubirthday" column="ubirthday"></result>
    <result property="uage" column="uage"></result>
    <result property="uphone" column="uphone"></result>
    <result property="uaddress" column="uaddress"></result>


</resultMap>

  <select id="selectUser" resultType="User">
     Select * from User
  </select>

    <select id="selectAllUser"  resultMap="userlist">
    Select * from User
    </select>

    <select id="selectmapUser" resultMap="userlist">
    select * from user where uid=#{uid} and uname=#{uname}
    </select>

    <select id="selectMap2" resultMap="userlist">
    select * from user where
    <if test="uid !=null">
    uid=#{uid}
    </if>
    <if test="uname !=null">
    and uname=#{uname}
    </if>

    </select>

    <select id="selectMap3" resultMap="userlist">
    <!-- 加<where></where>可以加多个and -->
    select * from user <where>
    <if test="uid !=null">
    and uid=#{uid}
    </if>
    <if test="uname !=null">
    and uname=#{uname}
    </if>
    </where>

    </select>


    <update id="updateUser" parameterType="User">
    update user 
    <set>
      <if test="uid !=null">uid=#{uid},</if>
      <if test="uname !=null">uname=#{uname}</if>

    </set>

    where uid=#{uid}
    </update>

    <!-- 分页显示 -->
    <select id="selectFY" resultMap="userlist">
    select * from user limit #{asd},#{pagesize1}

    </select>


</mapper>

测试类的调用

package com.bdqn.text;

import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

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.bdqn.dao.UserDao;
import com.bdqn.entity.User;
import com.bdqn.utils.SqlSessionUtils;



public class Text {
    public static void main(String[] args) {
        InputStream is=null;
        SqlSession sqlSession=null;

        //查询
        try {
            //分页显示
            is=Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(is);
            sqlSession=factory.openSession();

            List<User> list =sqlSession.getMapper(UserDao.class).selectFY(2, 2);
            for (User user : list ) {
                System.out.println(user.getUname());
            }

            /*//注解
            is=Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(is);
            sqlSession=factory.openSession();

            List<User> list =sqlSession.getMapper(UserDao.class).selectMap3(1, "qq");
            for (User user : list ) {
                System.out.println(user.getUname());
            }*/

            //Map
            /*is=Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(is);
            sqlSession=factory.openSession();

            Map<String, String> map =new HashMap<String, String>();
            map.put("uid", "1");
            map.put("uname", "qq");

            List<User> list =sqlSession.getMapper(UserDao.class).selectMap2(map);
            for (User user : list ) {
                System.out.println(user.getUname());
            }*/



            /*sqlSession=SqlSessionUtils.getSqlSession();
            List<User> list =sqlSession.getMapper(UserDao.class).selectAllUser();
            for (User user:list) {
                System.out.println(user.toString());
            }*/

            /*is=Resources.getResourceAsStream("mybatis-config.xml");
            SqlSessionFactory factory=new SqlSessionFactoryBuilder().build(is);
            sqlSession=factory.openSession();
            User user=new User();
            user.setUid(2);
            user.setUname("ggsimda");
            user.setUage(55);
            int count =sqlSession.getMapper(UserDao.class).updateUser(user);
            System.out.println(count);
            sqlSession.commit();*/


        } catch (Exception e) {

        sqlSession.rollback();
            e.printStackTrace();
        }finally{
            try {
                if (is !=null) {
                    is.close();
                }
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
    }



}