动态SQL

动态SQL

根据条件的不同，sql语句也会随之d改变。

 

动态SQL语句的标签

  • <if>
  • <choose><when><otherwise>    这是一套类似于switch...case...
  • <set>用于维护update语句中的set子句。功能如下：
  1. 满足条件时，会自动添加set关键字。
  2. 会去除set子句中多余的逗号
  3. 不满足条件时，不会生成set关键字
  • <trim>    where 元素知道如果由被包含的标记返回任意内容,就仅仅插入“WHERE” 。而且,如果以“AND”或“OR”开头的内容,那么就会跳过 WHERE 不插入。

                  如果 where 元素没有做出你想要的,你可以使用 trim 元素来自定义

  • foreach    include
 1 package com.bjsxt.mapper;
 2 
 3 import java.util.List;
 4 
 5 import org.apache.ibatis.annotations.Param;
 6 
 7 import com.bjsxt.pojo.User;
 8 //名字要一样，命名空间要一样，id要方法名一样
 9 public interface UserMapper {
10     List<User> selIn(@Param("list")List<Integer> list);
11     /**
12      * 修改用户信息
13      * @param user
14      * @return
15      */
16     int updUser(User user);
17     /**
18      * 动态SQL查询
19      * @param username
20      * @param password
21      * @return
22      */
23     List<User> sel(@Param("username")String username,@Param("password")String password);
24 }

 

 

 

 

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper
 3   PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4   "http://mybatis.org/dtd/mybatis-|3-mapper.dtd">
 5   <!-- namespa：命名空间，可以随意定义，一般情况下要写全限定路径（包名加类）
 6        MyBatis管理SQL语句是通过namespace+id来定位的
 7    -->
 8 <mapper namespace="com.bjsxt.mapper.UserMapper">
 9     <sql id="mySql">
10         id,username,password
11     </sql>
12     <select id="selIn" parameterType="List" resultType="user">
13         select 
14         <include refid="mySql"/>
15          from t_user where id in
16        <foreach collection="list" open="(" separator="," close=")" item="item">
17            #{item}
18        </foreach>
19     </select>
20     
21     
22     <update id="updUser" parameterType="user">
23         update t_user
24         <!-- 
25         prefix:前缀，表示向前添加内容
26         prefixOverrides:从前面删除内容
27         suffix：后缀，表示向后添加内容
28         suffixOverrides:从后面删除内容
29          -->
30         
31         <trim prefix="set" prefixOverrides="" suffix="" suffixOverrides=",">
32             username=#{username},
33             
34         </trim>
35         where id=#{id}
36     </update>
37     
38  <!--    <update id="updUser" parameterType="user">
39         update t_user
40         <set>
41             id=#{id}
42             <if test="username !=null and username !=''">
43                 username=#{username},
44             </if >
45             <if test="password !=null and password !=''">
46                 password=#{password},
47             </if>
48         </set>
49         where id=#{id}
50     </update> -->
51     
52     
53       <select id="sel" resultType="user">
54       select 
55       <include refid="mySql"/>
56        from t_user  
57     <where>
58         <choose>
59             <when test="username !=null and username !=''">
60                 and username=#{username}
61             </when>
62              <when test="password !=null and password !=''">
63                 and password=#{password}
64             </when>
65             <otherwise>
66                 and 1=1
67             </otherwise>
68         </choose>
69     </where>
70   </select>
71     
72    <!-- <select id="sel" resultType="user">
73       select * from t_user  
74       <where>
75           if用于条件判断
76              test属性用于设定判断条件，类似于java中if后括号里的条件
77      
78       <if test="username !=null and username !=''">
79       模糊查询
80       <bind name="username" value="'%'+username+'%'"/>
81          and username like #{username}
82       </if>
83       <if test="password !=null and password !=''">
84         and  password=#{password}
85       </if>
86       </where>
87   </select> 
88      -->
89 </mapper>

 

 

 

 1 package com.bjsxt.test;
 2 
 3 import java.util.ArrayList;
 4 import java.util.List;
 5 
 6 import org.apache.ibatis.session.SqlSession;
 7 import org.junit.Test;
 8 
 9 import com.bjsxt.mapper.UserMapper;
10 import com.bjsxt.pojo.User;
11 import com.bjsxt.util.MyBatisUtil;
12 
13 public class TestDynamicSql {
14     @Test
15     public void test3(){
16         SqlSession session = MyBatisUtil.getSession();
17         UserMapper mapper = session.getMapper(UserMapper.class);
18         
19         List<Integer> list =new ArrayList<>();
20         list.add(1);
21         list.add(2);
22         list.add(3);
23         list.add(7);
24         
25         List<User>  users = mapper.selIn(list);
26         for (User user : users) {
27             System.out.println(user);
28         }
29         session.close();
30     }
31     @Test
32     public void test2(){
33         
34         SqlSession session = MyBatisUtil.getSession();
35         UserMapper mapper = session.getMapper(UserMapper.class);
36         User user = new User();
37         user.setId(1);
38         user.setUsername("zhangsan1");
39         //user.setPassword("122");
40     int num=    mapper.updUser(user);
41     if(num>0){
42         System.out.println("chenggong");
43         session.commit();
44     }else {
45         System.out.println("shibai");
46         session.rollback();
47     }
48     session.close();
49     }
50 
51     @Test
52     public void test1(){
53         SqlSession session=MyBatisUtil.getSession();
54         
55         UserMapper mapper = session.getMapper(UserMapper.class);
56         List<User> list = mapper.sel("xx", "111");
57         for (User user : list) {
58             System.out.println(user);
59         }
60         session.close();
61     }
62 }

 

 

 

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE configuration
 3   PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
 4   "http://mybatis.org/dtd/mybatis-3-config.dtd">
 5   <configuration>
 6       <properties resource="db.properties"/>
 7       <settings>
 8           <!-- 设置MyBatis使用log4j日志支持 -->
 9           <setting name="logImpl" value="LOG4J"/>
10       </settings>
11       <!-- typeAliases给类型取别名 -->
12       <typeAliases>
13           <!-- 给user类取别名 -->
14           <!-- <typeAlias type="com.bjsxt.pojo.User" alias="u"/> -->
15           <!-- 别名都是类的名字 -->
16           <package name="com.bjsxt.pojo"/>
17       </typeAliases>
18       <!-- 用于指定使用哪个开发
19                                 用于指定使用的环境id
20        -->
21       <environments default="dev">
22           <!-- 用于配置开发环境
23                id：环境的唯一识别码
24            -->
25           <environment id="dev">
26               <!-- 事务管理器
27                    type：用于设定mybatis采用什么方式管理事务
28                    JDBC表示和JDBC一样事务的管理方式
29                -->
30               <transactionManager type="JDBC"/>
31               <!-- 数据源/连接池
32                                                          用于配置链接池和数据库链接的参数
33                    type：用于设置mybatis是否采用链接池技术
34                                                         连接池：用来存数据库链接的，减少数据库的频繁开关
35                    POOLED表示mybatis采用连接池技术                                     
36                -->
37               <dataSource type="POOLED">
38                   <property name="driver" value="${jdbc.driver}"/>
39                   <property name="url" value="${jdbc.url}"/>
40                   <property name="username" value="${jdbc.username}"/>
41                   <property name="password" value="${jdbc.password}"/>
42               </dataSource>
43           </environment>
44       </environments>
45       <!-- 扫描mapper文件 -->
46       <!-- 文件的全限制路径要用/ -->
47       <mappers>
48        <!--    <mapper class="com.bjsxt.mapper.UserMapper"/> -->
49        <package name="com.bjsxt.mapper"/>
50        
51       </mappers>
52   </configuration>

 

 

 1 package com.bjsxt.pojo;
 2 
 3 import java.io.Serializable;
 4 
 5 public class User implements Serializable {
 6 private int id;
 7 private String username;
 8 private String password;
 9 public String toString() {
10     return "User [id=" + id + ", username=" + username + ", password="
11             + password + "]";
12 }
13 public int hashCode() {
14     final int prime = 31;
15     int result = 1;
16     result = prime * result + id;
17     result = prime * result + ((password == null) ? 0 : password.hashCode());
18     result = prime * result + ((username == null) ? 0 : username.hashCode());
19     return result;
20 }
21 public boolean equals(Object obj) {
22     if (this == obj)
23         return true;
24     if (obj == null)
25         return false;
26     if (getClass() != obj.getClass())
27         return false;
28     User other = (User) obj;
29     if (id != other.id)
30         return false;
31     if (password == null) {
32         if (other.password != null)
33             return false;
34     } else if (!password.equals(other.password))
35         return false;
36     if (username == null) {
37         if (other.username != null)
38             return false;
39     } else if (!username.equals(other.username))
40         return false;
41     return true;
42 }
43 public int getId() {
44     return id;
45 }
46 public void setId(int id) {
47     this.id = id;
48 }
49 public String getUsername() {
50     return username;
51 }
52 public void setUsername(String username) {
53     this.username = username;
54 }
55 public String getPassword() {
56     return password;
57 }
58 public void setPassword(String password) {
59     this.password = password;
60 }
61 public User(int id, String username, String password) {
62     super();
63     this.id = id;
64     this.username = username;
65     this.password = password;
66 }
67 public User() {
68     super();
69 }
70 
71 }

 

 

 1 package com.bjsxt.util;
 2 
 3 import java.io.IOException;
 4 import java.io.InputStream;
 5 
 6 import org.apache.ibatis.io.Resources;
 7 import org.apache.ibatis.session.SqlSession;
 8 import org.apache.ibatis.session.SqlSessionFactory;
 9 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
10 
11 public class MyBatisUtil {
12     
13     private static SqlSessionFactory factory=null;
14     static{
15         
16         try {
17             InputStream is=Resources.getResourceAsStream("mybatis.xml");
18             factory=new SqlSessionFactoryBuilder().build(is);
19         } catch (IOException e) {
20             // TODO Auto-generated catch block
21             e.printStackTrace();
22         }
23     }
24 
25     public static SqlSession getSession(){
26         SqlSession session =null;
27         if(factory!=null){
28             //true表示开启
29          session= factory.openSession(true);
30         }
31         return session;
32     }
33 }

 

 

1 jdbc.driver=com.mysql.jdbc.Driver
2 jdbc.url=jdbc:mysql://localhost:3306/java505?useSSL=true&amp;characterEncoding=utf8&amp;useSSL=true
3 jdbc.username=root
4 jdbc.password=root

 

 1 # Set root category priority to INFO and its only appender to CONSOLE.
 2 log4j.rootCategory=INFO, CONSOLE
 3 #log4j.rootCategory=INFO, CONSOLE, LOGFILE
 4 
 5 # Set the enterprise logger category to FATAL and its only appender to CONSOLE.
 6 log4j.logger.org.apache.axis.enterprise=FATAL, CONSOLE
 7 
 8 # CONSOLE is set to be a ConsoleAppender using a PatternLayout.
 9 log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
10 log4j.appender.CONSOLE.Threshold=INFO
11 log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
12 log4j.appender.CONSOLE.layout.ConversionPattern=- %m%n
13 
14 # LOGFILE is set to be a File appender using a PatternLayout.
15 log4j.appender.LOGFILE=org.apache.log4j.FileAppender
16 log4j.appender.LOGFILE.File=axis.log
17 log4j.appender.LOGFILE.Append=true
18 log4j.appender.LOGFILE.Threshold=INFO
19 log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
20 log4j.appender.LOGFILE.layout.ConversionPattern=%-4r [%t] %-5p %c %x - %m%n

 

posted @ 2019-01-05 22:53  wq9  阅读(170)  评论(0)  编辑  收藏  举报