动态SQL
根据条件的不同，sql语句也会随之d改变。
动态SQL语句的标签
- <if>
- <choose><when><otherwise> 这是一套类似于switch...case...
- <set>用于维护update语句中的set子句。功能如下：
- 满足条件时，会自动添加set关键字。
- 会去除set子句中多余的逗号
- 不满足条件时，不会生成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&characterEncoding=utf8&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