MyBatis的动态SQL

接口：

package com.bjsxt.mapper;

import java.util.List;

import org.apache.ibatis.annotations.Param;

import com.bjsxt.pojo.User;

public interface UserMapper {
	/**
	 * 动态SQL查询
	 * @param username
	 * @param password
	 * @return
	 */
	List<User> sel(@Param("username")String username,@Param("password")String password);
	/**
	 * 更改数据
	 * @return
	 */
	int updUser(User user);
	/**
	 * 根据名字查询
	 * @return
	 */
	User selByName(@Param("username")String username);
	
	List<User> selIn(@Param("list")List<Integer> id);
	
	List<User> select();

}

映射文件：

<?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">
  
  <!--namespace="com.bjsxt.mapper.UserMapper":命名空间，权限定路径  -->
  <mapper namespace="com.bjsxt.mapper.UserMapper">
<!--1.where  if：条件判断  -->
  		<!-- <select id="sel" resultType="user">
  			select * from t_user
  			<where>1.有条件可以自动添加where 2.可以去除第一个and 3.没有条件不会生成where关键字
  			    if:用于条件判断  test：相当于括号，里面为布尔值
  				<if test="username!=null and username!=''">
  					and username=#{username}
  				</if>
  				<if test="password!=null and password!=''">
  					and password=#{password}
  				</if>
  			</where>
  		</select> -->
<!--2.choose when  otherwise：选择判断-->  		
  		<select id="sel" resultType="user">
  			select * from t_user
  			<where>
  			<choose>
  			<when test="username!=null and username!=''">
  				and username=#{username}
  			</when>
  			<when test="password!=null and password!=''">
  				and password=#{password}
  			</when>
  			<otherwise>
  				and 1=1
  			</otherwise>
  			</choose>
  			</where>
  		</select>
<!--3.set：满足条件加set，不满足不加，去除多余逗号  -->  		
  		<!-- <update id="updUser" parameterType="user">
  			update t_user
  			<set>
  				<if test="username!=null and username!=''">
  					username=#{username},
  				</if>
  				<if test="password!=null and password!=''">
  				    password=#{password},
  				</if>  				
  			</set>
  			where id=#{id}
  		</update> -->
  		
<!-- 4.trim: prefix:前缀，在前面加内容  prefixOverrides：从前面删内容   suffix：在后面加内容  suffixOverrides：在后面删内容-->  		
  		<update id="updUser" parameterType="user">
  			update t_user 
  			<trim prefix="set" prefixOverrides="" suffix="" suffixOverrides=",">
  			username=#{username},
  			</trim>
  			where id=#{id}
  		</update>
  		
<!-- 5.bind ：like-->  		
  		<select id="selByName" resultType="user">
  		<where>
  			<if test="username!=null and username!=''">
  				<bind name="username" value="'%'+username+'%'"/>
  			</if>
  		</where>
  			select * from t_user where username like #{username}
  		</select>
  		
<!-- 6.foreach： in collection：属性名  open：以什么开始    separator：以什么分割    close：以什么结束  item：取值  -->  		
  		<select id="selIn" resultType="user">
  			select * from t_user where id in
  			<foreach collection="list" open="(" separator="," close=")" item="item">
  				#{item}
  			</foreach>
  		</select>
<!-- 7.include:* -->  		
  		<sql id="mysql">
  			id,username,password
  		</sql>
  		<select id="select" resultType="user">
  			select <include refid="mysql"/> from t_user
  		</select>
  </mapper>

核心配置文件：

<?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>
  		<!--加载外部properties文件  -->
  		<properties resource="db.properties" />
  		<settings>
  			<!-- 设置mybatis使用log4j支持 -->
  			<setting name="logImpl"  value="LOG4J"/>
  		</settings>
  		<!-- 设置别名 -->
  		<typeAliases >
  			<!--自己设置别名  -->
  			 <!-- <typeAlias type="com.bjsxt.pojo.User" alias="u"/> --> 
  			 <!-- 直接写全限定路径，别名为类名且不区分大小写，最常用 -->
  			<!-- <typeAlias type="com.bjsxt.pojo.User"/> --> 
  			<!-- 只写包名，别名为类名且这个包下的所有类的别名都是类名 -->
  			<package name="com.bjsxt.pojo"/>
  		</typeAliases>
	  	<environments default="dev">    <!-- 指定哪一个环境 -->
	  			<environment id="dev">  <!-- 配置开发环境  id:环境唯一标识符-->
	  					<!-- 事务管理器   type="JDBC":以JDBC的方式管理事务 -->
	  					<transactionManager type="JDBC"></transactionManager>
	  					<!-- 数据库连接池：配置连接池和参数  type="POOLED"：采取连接池技术
	  								  property：配置参数
	  					 -->
	  					<dataSource type="POOLED">
	  							<property name="driver" value="${jdbc.driver}"/>
	  							<property name="url" value="${jdbc.url}"/>
	  							<property name="username" value="${jdbc.username}"/>
	  							<property name="password" value="${jdbc.password}"/>
	  					</dataSource>
	  			</environment>
	  	</environments>
	  	<!-- 扫描mapper文件 -->
	  	<mappers>
	  		<!-- <mapper resource="com/bjsxt/mapper/UserMapper.xml"/> -->
	  		<package name="com.bjsxt.mapper"/>
	  	</mappers>
  </configuration>

实体类pojo:

package com.bjsxt.pojo;

import java.io.Serializable;

public class User implements Serializable{
	private int id;
	private String username;
	private String password;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getUsername() {
		return username;
	}
	public void setUsername(String username) {
		this.username = username;
	}
	public String getPassword() {
		return password;
	}
	public void setPassword(String password) {
		this.password = password;
	}
	@Override
	public int hashCode() {
		final int prime = 31;
		int result = 1;
		result = prime * result + id;
		result = prime * result + ((password == null) ? 0 : password.hashCode());
		result = prime * result + ((username == null) ? 0 : username.hashCode());
		return result;
	}
	@Override
	public boolean equals(Object obj) {
		if (this == obj)
			return true;
		if (obj == null)
			return false;
		if (getClass() != obj.getClass())
			return false;
		User other = (User) obj;
		if (id != other.id)
			return false;
		if (password == null) {
			if (other.password != null)
				return false;
		} else if (!password.equals(other.password))
			return false;
		if (username == null) {
			if (other.username != null)
				return false;
		} else if (!username.equals(other.username))
			return false;
		return true;
	}
	@Override
	public String toString() {
		return "User [id=" + id + ", username=" + username + ", password=" + password + "]";
	}
	public User(int id, String username, String password) {
		super();
		this.id = id;
		this.username = username;
		this.password = password;
	}
	public User() {
		super();
	}
	
	

}

db.properties文件：

jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/bjsxt
jdbc.username=root
jdbc.password=root

log4j.properties文件：

# Set root category priority to INFO and its only appender to CONSOLE.
log4j.rootCategory=ERROR, CONSOLE
 #log4j.rootCategory=DEBUG, CONSOLE, LOGFILE
 
#设置方法级别日志
log4j.logger.com.bjsxt.mapper.UserMapper.selAll=DEBUG

#设置类级别日志
log4j.logger.com.bjsxt.mapper.UserMapper=DEBUG

#设置包级别日志
log4j.logger.com.bjsxt.mapper=DEBUG


# CONSOLE is set to be a ConsoleAppender using a PatternLayout.
log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
log4j.appender.CONSOLE.layout.ConversionPattern=- %m%n

# LOGFILE is set to be a File appender using a PatternLayout.
log4j.appender.LOGFILE=org.apache.log4j.FileAppender
log4j.appender.LOGFILE.File=F:/test.log
log4j.appender.LOGFILE.Append=true
log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
log4j.appender.LOGFILE.layout.ConversionPattern=- %m %l%n

测试：

package com.bjsxt.test;

import java.util.ArrayList;
import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import com.bjsxt.mapper.UserMapper;
import com.bjsxt.pojo.User;
import com.bjsxt.util.SessionUtil;

public class TestDynamic {
	@Test
	public void test1(){
		SqlSession session = SessionUtil.getSession();
		UserMapper mapper = session.getMapper(UserMapper.class);
		List<User> list = mapper.sel("zhangsan", "123");
		for (User user : list) {
			System.out.println(user);
		}
	}
	
	@Test
	public void test2(){
		SqlSession session = SessionUtil.getSession();
		UserMapper mapper = session.getMapper(UserMapper.class);
		User user = new User();
		user.setId(1);
		user.setUsername("张");
		user.setPassword("66");
		int num=mapper.updUser(user);
		if(num>0){
			System.out.println("修改成功");
			session.commit();
		}else{
			System.out.println("修改失败");
			session.rollback();
		}
	}
	
	@Test
	public void test3(){
		SqlSession session = SessionUtil.getSession();
		UserMapper mapper = session.getMapper(UserMapper.class);
		User user = new User();
		user.setId(1);
		user.setUsername("张2");
		user.setPassword("66");
		int num=mapper.updUser(user);
		if(num>0){
			System.out.println("修改成功");
			session.commit();
		}else{
			System.out.println("修改失败");
			session.rollback();
		}
	}
	
	@Test
	public void test4(){
		SqlSession session = SessionUtil.getSession();
		UserMapper mapper = session.getMapper(UserMapper.class);
		
		User u = mapper.selByName("a");
		System.out.println(u);
	}
	
	@Test
	public void test5(){
		SqlSession session = SessionUtil.getSession();
		UserMapper mapper = session.getMapper(UserMapper.class);
		List<Integer> list = new ArrayList<>();
		list.add(1);
		list.add(3);
		list.add(10);
		List<User> u = mapper.selIn(list);
		System.out.println(u);
	}
	
	@Test
	public void test6(){
		SqlSession session = SessionUtil.getSession();
		UserMapper mapper = session.getMapper(UserMapper.class);
		List<Integer> list = new ArrayList<>();
		list.add(1);
		list.add(3);
		list.add(10);
		List<User> user = mapper.select();
		System.out.println(user);
	}

}