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 && !name.equals("")"> 66 teacherName like #{_name} and 67 </if> 68 <if test="birth!=null"> 69 birth_date < #{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("")"> 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("")"> 124 teacherName=#{name}, 125 </if> 126 <if test="course!=null and !course.equals("")"> 127 class_name=#{course}, 128 </if> 129 <if test="address!=null and !address.equals("")"> 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 }