MyBatis基础-04-动态sql

MyBatis_dynamicSQL

代码示例：

Teacher.java:

 1 package com.atguigu.bean;
 2 
 3 import java.util.Date;
 4 
 5 public class Teacher {
 6 
 7     private Integer id;
 8     private String name;
 9     private String course;
10     private String address;
11     private Date birth;
12     /**
13      * @return the id
14      */
15     public Integer getId() {
16         return id;
17     }
18     /**
19      * @param id the id to set
20      */
21     public void setId(Integer id) {
22         this.id = id;
23     }
24     /**
25      * @return the name
26      */
27     public String getName() {
28         return name;
29     }
30     /**
31      * @param name the name to set
32      */
33     public void setName(String name) {
34         this.name = name;
35     }
36     /**
37      * @return the course
38      */
39     public String getCourse() {
40         return course;
41     }
42     /**
43      * @param course the course to set
44      */
45     public void setCourse(String course) {
46         this.course = course;
47     }
48     /**
49      * @return the address
50      */
51     public String getAddress() {
52         return address;
53     }
54     /**
55      * @param address the address to set
56      */
57     public void setAddress(String address) {
58         this.address = address;
59     }
60     /**
61      * @return the birth
62      */
63     public Date getBirth() {
64         return birth;
65     }
66     /**
67      * @param birth the birth to set
68      */
69     public void setBirth(Date birth) {
70         this.birth = birth;
71     }
72     /* (non-Javadoc)
73      * @see java.lang.Object#toString()
74      */
75     @Override
76     public String toString() {
77         return "Teacher [id=" + id + ", name=" + name + ", course=" + course
78                 + ", address=" + address + ", birth=" + birth + "]";
79     }
80 
81 }

TeacherDao.java:

 1 package com.atguigu.dao;
 2 
 3 import java.util.List;
 4 
 5 import org.apache.ibatis.annotations.Param;
 6 
 7 import com.atguigu.bean.Teacher;
 8 
 9 public interface TeacherDao {
10     
11     public Teacher getTeacherById(Integer id);
12     
13     public List<Teacher> getTeacherByCondition(Teacher teacher);
14 
15     //添加@Param("ids")后，配置文件中foreach可以使用collection="ids"
16     public List<Teacher> getTeacherByIdIn(@Param("ids")List<Integer> ids);
17     
18     public List<Teacher> getTeacherByConditionChoose(Teacher teacher);
19     
20     public int updateTeacher(Teacher teacher);
21 
22 }

TeacherDao.xml:

  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 <mapper namespace="com.atguigu.dao.TeacherDao">
  6     <resultMap type="com.atguigu.bean.Teacher" id="teacherMap">
  7         <id property="id" column="id" />
  8         <result property="address" column="address" />
  9         <result property="birth" column="birth_date" />
 10         <result property="course" column="class_name" />
 11         <result property="name" column="teacherName" />
 12     </resultMap>
 13 
 14     <!--抽取可重用的sql语句  -->
 15     <sql id="selectSql">select * from t_teacher</sql>
 16 
 17     <!--public Teacher getTeacherById(Integer id); -->
 18     <select id="getTeacherById" resultMap="teacherMap">
 19         <include refid="selectSql"></include>
 20         where id=#{id}
 21     </select>
 22 
 23     <!-- if：判断 -->
 24     <!--public List<Teacher> getTeacherByCondition(Teacher teacher); -->
 25     <select id="getTeacherByCondition" resultMap="teacherMap">
 26         select * from t_teacher
 27         <!-- test=""：编写判断条件 id!=null：取出传入的javaBean属性中的id的值，判断其是否为空 -->
 28         <!-- where可以帮我们去除掉前面的and; -->
 29 
 30         <!-- trim：截取字符串 
 31             prefix=""：前缀；为我们下面的sql整体添加一个前缀 
 32             prefixOverrides=""： 去除整体字符串前面多余的字符
 33             suffix=""：为整体添加一个后缀 
 34             suffixOverrides=""：后面哪个多了可以去掉; -->
 35         <!-- 我们的查询条件就放在where标签中；每个and写在前面，
 36             where帮我们自动去除前面多余的and -->
 37         <trim prefix="where" prefixOverrides="and" suffixOverrides="and">
 38             <if test="id!=null">
 39                 id > #{id} and
 40             </if>
 41             <!-- 空串 "" and； && or: ||； if()：传入非常强大的判断条件；
 42             OGNL表达式；对象导航图
 43                 Person
 44                     ===lastName
 45                     ===email
 46                     ===Address
 47                         ===city
 48                         ===province
 49                         ===Street
 50                             ===adminName
 51                             ===info
 52                             ===perCount
 53             方法、静态方法、构造器、xxx
 54             在mybatis中，传入的参数可以用来做判断；
 55             额外还有两个东西；
 56             _parameter：代表传入来的参数；
 57                 1）、传入了单个参数：_parameter就代表这个参数
 58                 2）、传入了多个参数：_parameter就代表多个参数集合起来的map
 59             _databaseId：代表当前环境
 60                 如果配置了databaseIdProvider：_databaseId就有值
 61                 
 62              -->
 63             <!-- 绑定一个表达式的值到一个变量 -->
 64             <!-- <bind name="_name" value="'%'+name+'%'"/> -->
 65             <if test="name!=null &amp;&amp; !name.equals(&quot;&quot;)">
 66                 teacherName like #{_name} and
 67             </if>
 68             <if test="birth!=null">
 69                 birth_date &lt; #{birth} and
 70             </if>
 71         </trim>
 72     </select>
 73 
 74     <!-- public List<Teacher> getTeacherByIdIn(List<Integer> ids); -->
 75     <select id="getTeacherByIdIn" resultMap="teacherMap">
 76         
 77         SELECT * FROM t_teacher WHERE id IN
 78         <!-- 帮我们遍历集合的； collection=""：指定要遍历的集合的key 
 79         close=""：以什么结束 
 80         index="i"：索引； 
 81             如果遍历的是一个list； 
 82                 index：指定的变量保存了当前索引 
 83                 item：保存当前遍历的元素的值 
 84             如果遍历的是一个map： 
 85                 index：指定的变量就是保存了当前遍历的元素的key 
 86                 item：就是保存当前遍历的元素的值
 87         item="变量名"：每次遍历出的元素起一个变量名方便引用 
 88         open=""：以什么开始 
 89         separator=""：每次遍历的元素的分隔符 
 90             (#{id_item},#{id_item},#{id_item} -->
 91         <if test="ids.size >0">
 92             <foreach collection="ids" item="id_item" separator="," open="(" close=")">
 93                 #{id_item}
 94             </foreach>
 95         </if>
 96     </select>
 97 
 98     <!--public List<Teacher> getTeacherByConditionChoose(Teacher teacher); -->
 99     <select id="getTeacherByConditionChoose" resultMap="teacherMap">
100         select * from t_teacher
101         <where>
102             <choose>
103                 <when test="id!=null">
104                     id=#{id}
105                 </when>
106                 <when test="name!=null and !name.equals(&quot;&quot;)">
107                     teacherName=#{name}
108                 </when>
109                 <when test="birth!=null">
110                     birth_date = #{birth}
111                 </when>
112                 <otherwise>
113                     1=1
114                 </otherwise>
115             </choose>
116         </where>
117     </select>
118 
119     <!-- public int updateTeacher(Teacher teacher); -->
120     <update id="updateTeacher">
121         UPDATE t_teacher
122         <set>
123             <if test="name!=null and !name.equals(&quot;&quot;)">
124                 teacherName=#{name},
125             </if>
126             <if test="course!=null and !course.equals(&quot;&quot;)">
127                 class_name=#{course},
128             </if>
129             <if test="address!=null and !address.equals(&quot;&quot;)">
130                 address=#{address},
131             </if>
132             <if test="birth!=null">
133                 birth_date=#{birth}
134             </if>
135         </set>
136         <where>
137             id=#{id}
138         </where>
139          
140     </update>
141 </mapper>

dbconfig.properties:

1 username=root
2 password=root
3 jdbcUrl=jdbc:mysql://localhost:3306/mybatis_0325?characterEncoding=utf-8&serverTimezone=GMT%2B8
4 driverClass=com.mysql.cj.jdbc.Driver

log4j.xml:

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
 3  
 4 <log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
 5  
 6  <appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
 7    <param name="Encoding" value="UTF-8" />
 8    <layout class="org.apache.log4j.PatternLayout">
 9     <param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m  (%F:%L) 
" />
10    </layout>
11  </appender>
12  <logger name="java.sql">
13    <level value="debug" />
14  </logger>
15  <logger name="org.apache.ibatis">
16    <level value="info" />
17  </logger>
18  <root>
19    <level value="debug" />
20    <appender-ref ref="STDOUT" />
21  </root>
22 </log4j:configuration>

mybatis-config.xml:

 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 
 7     <properties resource="dbconfig.properties"></properties>
 8 
 9     <settings>
10         <!--驼峰命名    name：配置项的key，value：配置项的值-->
11         <setting name="mapUnderscoreToCamelCase" value="true"/>
12         <!-- 开启延迟加载开关 -->
13         <setting name="lazyLoadingEnabled" value="true"/>
14         <!-- 开启属性按需加载 -->
15         <setting name="aggressiveLazyLoading" value="false"/>
16     </settings>
17 
18     <typeAliases>
19         <!--批量起别名   name：指定包名，默认别名就是类名，不区分大小写-->
20         <package name="com.atguigu.bean"/>
21         <!--推荐使用全类名-->
22     </typeAliases>
23 
24 
25     <environments default="development">
26         <environment id="development">
27             <transactionManager type="JDBC"/>
28             <!-- 配置连接池 -->
29             <dataSource type="POOLED">
30                 <!--${}取出配置文件中的值-->
31                 <property name="driver" value="${driverClass}"/>
32                 <property name="url" value="${jdbcUrl}"/>
33                 <property name="username" value="${username}"/>
34                 <property name="password" value="${password}"/>
35             </dataSource>
36         </environment>
37     </environments>
38 
39 
40     <mappers>
41         <mapper resource="mybatis/TeacherDao.xml"/>
42         <!--<package name="com.atguigu.dao"/>-->
43     </mappers>
44 </configuration>

MyBatisTest.java:

 1 package com.atguigu.test;
 2 
 3 import static org.junit.Assert.*;
 4 
 5 import java.io.IOException;
 6 import java.io.InputStream;
 7 import java.util.Arrays;
 8 import java.util.Date;
 9 import java.util.HashMap;
10 import java.util.List;
11 import java.util.Map;
12 
13 import org.apache.ibatis.io.Resources;
14 import org.apache.ibatis.session.SqlSession;
15 import org.apache.ibatis.session.SqlSessionFactory;
16 import org.apache.ibatis.session.SqlSessionFactoryBuilder;
17 import org.junit.Before;
18 import org.junit.Test;
19 
20 import com.atguigu.bean.Teacher;
21 import com.atguigu.dao.TeacherDao;
22 
23 
24 public class MyBatisTest {
25 
26     // 工厂一个
27     SqlSessionFactory sqlSessionFactory;
28 
29     @Before
30     public void initSqlSessionFactory() throws IOException {
31         String resource = "mybatis-config.xml";
32         InputStream inputStream = Resources.getResourceAsStream(resource);
33         sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
34     }
35     
36     @Test
37     public void test() {
38         SqlSession openSession = sqlSessionFactory.openSession();
39         try {
40             
41             TeacherDao mapper = openSession.getMapper(TeacherDao.class);
42             
43             //teacher2.setBirth(new Date());
44             /*    List<Teacher> list = mapper.getTeacherByCondition(teacher2);*/
45             
46             
47             //List<Teacher> list = mapper.getTeacherByIdIn(Arrays.asList(1,2,3,4,5));
48             
49             
50             Teacher teacher2 = new Teacher();
51             //teacher2.setId(1);
52             //teacher2.setName("admin");
53             List<Teacher> list = mapper.getTeacherByConditionChoose(teacher2);
54             
55             System.out.println(list);
56         } finally {
57             openSession.close();
58         }
59     }
60 
61     @Test
62     public void test02() {
63         SqlSession openSession = sqlSessionFactory.openSession();
64         try {
65 
66             TeacherDao mapper = openSession.getMapper(TeacherDao.class);
67 
68             Teacher teacher = new Teacher();
69             teacher.setId(1);
70             teacher.setName("张老师");
71             mapper.updateTeacher(teacher);
72 
73             openSession.commit();
74         } finally {
75             openSession.close();
76         }
77     }
78 
79 }