mybatis--MyBatis动态SQL语句

mybatis 的动态sql语句是基于OGNL表达式的。可以方便的在 sql 语句中实现某些逻辑. 总体说来mybatis 动态SQL 语句主要有以下几类:
1. if 语句 (简单的条件判断)
2. choose (when,otherwize) ,相当于java 语言中的 switch ,与 jstl 中的choose 很类似.
3. trim (对包含的内容加上 prefix,或者 suffix 等，前缀，后缀)
4. where (主要是用来简化sql语句中where条件判断的，能智能的处理 and or ,不必担心多余导致语法错误)
5. set (主要用于更新时)
6. foreach (用于mybatis语句查询)

 
1.if语句(简单的条件判断)
（1）数据库my的user表数据为：
 
 
（2）首先创建一个mybatis项目，mybatis_008

 

（3）建立com.zk.dao包下的IUser接口：

IUser.java

?
1
2
3
4
5
6
7
8
package com.zk.dao;
 
import org.apache.ibatis.annotations.Param;
import com.zk.pojo.User;
 
public interface IUser {
        public User findUserWithNameLike(@Param("id")int id, @Param("name")String name);
}

（4）在com.zk.pojo包下建立User实现IUser接口中的方法，并配置UserMapper.xml

User.java

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
package com.zk.pojo;
 
public class User {
    private int id;
    private String name;
    private String level;
    private String phone;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getLevel() {
        return level;
    }
    public void setLevel(String level) {
        this.level = level;
    }
    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", level=" + level
                + ", phone=" + phone + "]";
    }
}

UserMapper.xml

?
1
2
3
4
5
6
7
8
9
10
11
12
<?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.zk.dao.IUser">
<select id="findUserWithNameLike" resultType="User">
  SELECT * FROM user
  WHERE id = #{id} <if test="name!= null">
    AND name like #{name}
  </if>
</select>
</mapper>

（5）配置config/configure.xml

configure.xml

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?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>
<typeAliases>
<typeAlias alias="User" type="com.zk.pojo.User" />
</typeAliases>
 
<environments default="development">
        <environment id="development">
        <transactionManager type="JDBC" />
        <dataSource type="POOLED">
        <property name="driver" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3306/my"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
        </dataSource>
        </environment>
    </environments>
 
    <mappers>
         <!-- // power by http://www.yiibai.com -->
         <mapper resource="com/zk/pojo/UserMapper.xml" />
    </mappers>
</configuration>

（6）实现main函数

MainApp.java

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
package Main;
 
import java.io.IOException;
import java.io.Reader;
 
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.zk.dao.IUser;
import com.zk.pojo.User;
 
public class MainApp {
    private static SqlSessionFactory sqlsessionfactory;
    private static Reader reader;
     
    static{
        try {
            reader=Resources.getResourceAsReader("config/configure.xml");
            sqlsessionfactory=new SqlSessionFactoryBuilder().build(reader);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
     
    public SqlSessionFactory getSqlSessionFactory()
    {
        return sqlsessionfactory;
    }
     
    public static void main(String[]args)
    {
        select(1,"c");
    }
     
    public static void select(int id,String name){
        //获取session连接
        SqlSession session=sqlsessionfactory.openSession();
        //获取mapper
        IUser usermap=session.getMapper(IUser.class);
        //selectAllUser();
        User user = usermap.findUserWithNameLike(id, name);
        //执行查询
        System.out.println(user.toString());
        session.commit();
        //selectAllUser();
    }
}

　　Main执行结果如下：

2. where (主要是用来简化sql语句中where条件判断的，能智能的处理 and or ,不必担心多余导致语法错误)

(1) UserMapper.xml

 

?
1
2
3
4
5
6
7
8
9
10
11
12
<?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.zk.dao.IUser">
<select id="findUserWithNameLike" resultType="User">
  SELECT * FROM user   
  <where>  <if test="id!=null">id like #{id}</if>
  <if test="name!=null">AND name like #{name}</if>
  </where>
  </select>
</mapper>

 

运行结果如下：

 

3. choose (when,otherwize) ,相当于java 语言中的 switch ,与 jstl 中的choose 很类似.

同样原始的数据库，仅更改pojo层的UserMapper.xml，如下所示：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<?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.zk.dao.IUser">
<select id="findUserWithNameLike" resultType="User">
  SELECT * FROM user where
    <choose>
    <when test="id!=null">
    id like #{id}
    </when>
    <when test="name!=null">
    AND name like #{name}
    </when>
    </choose>
   
</select>
</mapper>

　运行后得到的结果如下图所示：

4. set (主要用于更新时)

（1）IUser.java

?
1
2
3
4
5
6
7
8
9
10
package com.zk.dao;
 
 
 
import com.zk.pojo.User;
 
 
public interface IUser {
    public User updateUser(User user);
}

（2）UserMapper.xml和User.java

User.java

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
package com.zk.pojo;
 
public class User {
    private int id;
    private String name;
    private String level;
    private String phone;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getLevel() {
        return level;
    }
    public void setLevel(String level) {
        this.level = level;
    }
    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", level=" + level
                + ", phone=" + phone + "]";
    }
}

UserMapper.xml

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<?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.zk.dao.IUser">
<update id="updateUser" parameterType="User">
update user
<set>
<if test="name!=null">name=#{name},</if>
<if test="level!=null">level=#{level},</if>
<if test="phone!=null">phone=#{phone}</if>
</set>
where id=#{id}
</update>
</mapper>

（3）configure.xml

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?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>
<typeAliases>
<typeAlias alias="User" type="com.zk.pojo.User" />
</typeAliases>
 
<environments default="development">
        <environment id="development">
        <transactionManager type="JDBC" />
        <dataSource type="POOLED">
        <property name="driver" value="com.mysql.jdbc.Driver" />
        <property name="url" value="jdbc:mysql://localhost:3306/my"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
        </dataSource>
        </environment>
    </environments>
 
    <mappers>
         <mapper resource="com/zk/pojo/UserMapper.xml" />
    </mappers>
</configuration>

（4）Main.java

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
package Main;
 
import java.io.IOException;
import java.io.Reader;
 
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.zk.dao.IUser;
import com.zk.pojo.User;
 
public class MainApp {
    private static SqlSessionFactory sqlsessionfactory;
    private static Reader reader;
     
    static{
        try {
            reader=Resources.getResourceAsReader("config/configure.xml");
            sqlsessionfactory=new SqlSessionFactoryBuilder().build(reader);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
     
    public SqlSessionFactory getSqlSessionFactory()
    {
        return sqlsessionfactory;
    }
     
    public static void main(String[]args)
    {
        SqlSession session=sqlsessionfactory.openSession();
        User user=new User();
        user.setId(1);
        user.setName("a");
        user.setLevel("a");
        user.setPhone("137298647");
         
        session.update("updateUser",user);
        session.commit();
    }
}　

运行结果如下：　

 

 5. trim (对包含的内容加上 prefix,或者 suffix 等，前缀，后缀)

 IUser.java

?
1
2
3
4
5
6
7
8
package com.zk.dao;
 
import org.apache.ibatis.annotations.Param;
import com.zk.pojo.User;
 
public interface IUser {
    public User findUserWithNameLike(@Param("id")int id, @Param("name")String name);
}

User.java

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
package com.zk.pojo;
 
public class User {
    private int id;
    private String name;
    private String level;
    private String phone;
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getLevel() {
        return level;
    }
    public void setLevel(String level) {
        this.level = level;
    }
    public String getPhone() {
        return phone;
    }
    public void setPhone(String phone) {
        this.phone = phone;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", level=" + level
                + ", phone=" + phone + "]";
    }
}

　UserMapper.xml

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?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.zk.dao.IUser">
<select id="findUserWithNameLike" resultType="User">
  SELECT * FROM user 
<trim prefix="WHERE" prefixOverrides="AND">
    <if test="id!=null">
      id=#{id}
    </if>
    <if test="name!= null">
      AND name like #{name}
    </if>
</trim>
</select>
</mapper>

MainApp.java

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
package Main;
 
import java.io.IOException;
import java.io.Reader;
 
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.zk.dao.IUser;
import com.zk.pojo.User;
 
public class MainApp {
        private static SqlSessionFactory sqlsessionfactory;
        private static Reader reader;
          
        static{
            try {
                reader=Resources.getResourceAsReader("config/configure.xml");
                sqlsessionfactory=new SqlSessionFactoryBuilder().build(reader);
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
          
        public SqlSessionFactory getSqlSessionFactory()
        {
            return sqlsessionfactory;
        }
          
        public static void main(String[]args)
        {
            select(1,"a");
        }
          
        public static void select(int id,String name){
            //获取session连接
            SqlSession session=sqlsessionfactory.openSession();
            //获取mapper
            IUser usermap=session.getMapper(IUser.class);
            //selectAllUser();
            User user = usermap.findUserWithNameLike(id, name);
            //执行查询
            System.out.println(user.toString());
            session.commit();
            //selectAllUser();
        }
}

执行结果如下：

 

 6. foreach (用于mybatis语句查询)

UserMapper.xml

?
1
2
3
4
5
6
7
8
9
10
11
12
13
<?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.zk.dao.IUser">
<select id="findUsersWithIdforeach" resultType="User">
  SELECT * FROM user 
  <where>id in
  <foreach collection="list" item="item" open="(" close=")" separator=",">
  #{item}
  </foreach>
  </where>
</mapper>

IUser.java

?
1
2
3
4
5
6
7
8
9
10
package com.zk.dao;
 
import java.util.List;
import org.apache.ibatis.annotations.Param;
 
import com.zk.pojo.User;
 
public interface IUser {
    public List<User> findUsersWithIdforeach(@Param("list")List<Integer> list);
}

Main.java

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
package Main;
 
import java.io.IOException;
import java.io.Reader;
import java.util.ArrayList;
import java.util.List;
 
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.zk.dao.IUser;
import com.zk.pojo.User;
 
public class MainApp {
     private static SqlSessionFactory sqlsessionfactory;
        private static Reader reader;
          
        static{
            try {
                reader=Resources.getResourceAsReader("config/configure.xml");
                sqlsessionfactory=new SqlSessionFactoryBuilder().build(reader);
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
          
        public SqlSessionFactory getSqlSessionFactory()
        {
            return sqlsessionfactory;
        }
          
        public static void main(String[]args)
        {
            select(1,"a");
        }
          
        public static void select(int id,String name){
            //获取session连接
            SqlSession session=sqlsessionfactory.openSession();
            //获取mapper
            IUser usermap=session.getMapper(IUser.class);
            //selectAllUser();
            List ids=new ArrayList();
            ids.add(1);
            ids.add(2);
            ids.add(3);
            ids.add(4);
            //执行查询
            //System.out.println(user.toString());
            List<User> users=usermap.findUsersWithIdforeach(ids);
            for(User u:users)
            {
                System.out.println(u);
            }
            session.commit();
            //selectAllUser();
        }
}

程序运行结果：

数据库中数据：

 

 

posted @   leagueandlegends  阅读(1857)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· 记一次.NET内存居高不下排查解决与启示
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· .NET10 - 预览版1新功能体验（一）
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛