动态Sql
动态Sql
准备
依赖
User .java
package com.sty.pojo1;
import lombok.Data;
@Data
public class User {
private int id;
private String name;
private String password;
}
UserDao.java
package com.sty.Mapper;
import com.sty.pojo1.User;
import java.util.List;
public interface UserDao {
/*int add(User user);
int delete(int id);*/
int update(User user);
List<User> queryUserById(List list);
List<User> queryUserByName(User user);
List<User> queryUserByNameAndPassword(User user);
}
Mybatis-config.xml
<?xml version="1.0" encoding="UTF8"?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--dataBase-->
<properties resource="db.properties"/>
<!--log4J-->
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
<!--Aliases-->
<typeAliases>
<typeAlias type="com.sty.pojo1.User" alias="user"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/sty/Mapper/UserMapper.xml"/>
</mappers>
</configuration>
db.properties
driver=com.mysql.jdbc.Driver
#url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8&userSSL=true
url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8&userSSL=true
password=root
username=root
log4j.properties
#将等级为BUG的日志文件输出到控制泰console和文件file中#
log4j.rootLogger=DEBUG,console,file
#控制台输出配置#
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.Target=System.out
log4j.appender.console.Threshold=DEBUG
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=[%c]-%m%n
#文件输出相关设置#
log4j.appender.file=org.apache.log4j.RollingFileAppender
log4j.appender.file.File=./log/sty.log
log4j.appender.file.MaxFileSize=10mb
log4j.appender.file.Threshold=DEBUG
log4j.appender.file.layout=org.apache.log4j.PatternLayout
log4j.appender.file.layout.ConversionPattern=[%p][%d{yy-MM-dd}[%c]%m%n]
#日志输出级别#
log4j.logger.org.mybatis=DEBUG
log4j.logger.java.sql=DEBUG
log4j.logger.java.sql.Statement=DEBUG
log4j.logger.java.sql.ResultSet=DEBUG
log4j.logger.java.sql.PrepareStatement=DEBUG
MybatisUtils.java(工具类，获取SqlSession)
package com.sty.Utils;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactoryBuilder=null;
static{
String resource="Mybatis-config.xml";
try {
InputStream resourceAsStream = Resources.getResourceAsStream(resource);
sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder().build(resourceAsStream);
} catch (IOException e) {
e.printStackTrace();
}
}
public static SqlSession getSession(){
return sqlSessionFactoryBuilder.openSession();
}
}
UserMapper.xml
if
<?xml version="1.0" encoding="UTF8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.sty.Mapper.UserDao">
<!--<if> element-->
<select id="queryUserByName" parameterType="com.sty.pojo1.User" resultType="user">
select *
from mybatis.user
where 1=1
<if test="name !=null and name != '' ">
and name like concat ('%',#{name},'%')
</if>
</select>
</mapper>
Test
@Test
public void TestIf(){
SqlSession session = MybatisUtils.getSession();
UserDao mapper = session.getMapper(UserDao.class);
User user = new User();
user.setName("朱");
List<User> users = mapper.queryUserByName(user);
for (User user1 : users) {
System.out.println(user1);
}
session.close();
}
choose
<!--choose-->
<select id="queryUserByNameAndPassword" resultType="user" parameterType="com.sty.pojo1.User">
select * from mybatis.user where 1=1
<choose>
<when test="password != null and password !='' ">
and password=#{password}
</when>
<when test="name != null and name != '' ">
and name like concat ('%',#{name},'%')
</when>
<otherwise>
and id is not null
</otherwise>
</choose>
</select>
@Test
public void TestChoose(){
SqlSession session = MybatisUtils.getSession();
User user = new User();
user.setName("朱元璋 ");
user.setPassword("1996");
UserDao mapper = session.getMapper(UserDao.class);
List<User> users = mapper.queryUserByNameAndPassword(user);
for (User user1 : users) {
System.out.println(user1);
}
session.close();
}
set
<!--set,后面，不要忘记-->
<update id="update" parameterType="com.sty.pojo1.User">
update mybatis.user
<set>
<if test="name != null and name != '' ">
name=#{name},
</if>
<if test="password != null and password != '' ">
password=#{password},
</if>
</set>
where id = #{id}
</update>
@Test
public void TestSet(){
SqlSession session = MybatisUtils.getSession();
UserDao mapper = session.getMapper(UserDao.class);
User user = new User();
user.setName("朱允炆");
user.setPassword("123456");
user.setId(7);
int update = mapper.update(user);
if(update>0){
System.out.println("修改成功");
}else{
System.out.println("修改失败");
}
session.commit();
session.close();
}
foreach
collection:配置的list是传递过来的参数类型（首字母小写）可以是一个array,list,collection,Map集合键，pojo包装类中数组或者集合类型的属性名
item:配置的是循环中当前的元素
index:配置的是当前元素在集合中夫人位置下标
open，close:配置的是以什么符号将这些集合元素包裹
separator:配置的是各个元素的间隔符
<!--foreach-->
<select id="queryUserById" parameterType="list" resultType="user">
select *
from mybatis.user
where id in
<foreach collection="list" item="id" index="index" open="(" separator="," close=")">
#{id}
</foreach>
</select>
@Test
public void TestForeach(){
SqlSession session = MybatisUtils.getSession();
UserDao mapper = session.getMapper(UserDao.class);
List<Integer> ids = new ArrayList<Integer>();
ids.add(0);
ids.add(5);
List<User> users = mapper.queryUserById(ids);
for (User user : users) {
System.out.println(user);
}
session.close();
}
