🍁博客主页：👉
不会压弯的小飞侠
✨欢迎关注：👉点赞👍收藏⭐留言✒
✨系列专栏：👉
MyBatis详解
✨如果觉得博主的文章还不错的话，请三连支持一下博主。
🔥欢迎大佬指正，一起 学习！一起加油！
一、动态sql-if
<select id="findCondition" parameterType="user" resultType="user">
select * from user
<where>
<if test="id!=0">
and id=#{id}
</if>
<if test="username!=null">
and username=#{username}
</if>
<if test="password!=null">
and password=#{password}
</if>
</where>
</select>
二、动态sql-foreach
<select id="findBuIds" parameterType="list" resultType="user">
select * from user
<where>
<foreach collection="list" open="id in(" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
三、sql片段抽取
1.sql语句抽取
<!-- sql语句抽取-->
<sql id="sqls"> select * from user</sql>
2.引入SQL语句
<include refid="sqls"></include>
四、案例
1.pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>MyBatis-Dao</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.32</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
</project>
2.jdbc.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/mybatisthree
jdbc.username=root
jdbc.password=root
3.mybatis-config.xml
<?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 core file-->
<configuration>
<!--通过properties标签添加properties文件-->
<properties resource="jdbc.properties"></properties>
<!--自定义别名-->
<typeAliases>
<typeAlias type="com.study.domain.User" alias="user"></typeAlias>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<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>
<!--加载sql映射文件-->
<mappers>
<mapper resource="com/study/dao/UserMapper.xml"/>
</mappers>
</configuration>
4.User
package com.study.domain;
public class User {
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 String toString() {
return "User{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
'}';
}
}
5.UserMapper
package com.study.dao;
import com.study.domain.User;
import javax.jws.soap.SOAPBinding;
import java.util.List;
public interface UserMapper {
public List<User> findAll();
public User findById(int id);
public List<User> findCondition(User user);
public List<User> findBuIds(List<Integer> ids);
}
6.UserMapper.xml
<?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">
<mapper namespace="com.study.dao.UserMapper">
<!-- 查询-->
<select id="findAll" resultType="user">
select * from user
</select>
<!--根据id查询-->
<select id="findById" parameterType="int" resultType="user">
select * from user where id=#{id}
</select>
<!-- sql语句抽取-->
<sql id="sqls"> select * from user</sql>
<select id="findCondition" parameterType="user" resultType="user">
<include refid="sqls"></include>
<where>
<if test="id!=0">
and id=#{id}
</if>
<if test="username!=null">
and username=#{username}
</if>
<if test="password!=null">
and password=#{password}
</if>
</where>
</select>
<select id="findBuIds" parameterType="list" resultType="user">
select * from user
<where>
<foreach collection="list" open="id in(" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
</mapper>
7.Test
package com.study.test;
import com.study.dao.UserMapper;
import com.study.domain.User;
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;
import java.util.ArrayList;
import java.util.List;
public class Test {
@org.junit.Test
public void test1() throws IOException {
User user = new User();
user.setId(2);
//user.setUsername("小飞侠");
// user.setPassword("222222");
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> condition = mapper.findCondition(user);
System.out.println(condition); //[User{id=2, username='小飞侠', password='222222'}]
}
@org.junit.Test
public void test2() throws IOException {
List<Integer> list = new ArrayList<Integer>();
list.add(1);
list.add(2);
list.add(3);
InputStream resourceAsStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sessionFactory.openSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> buIds = mapper.findBuIds(list);
System.out.println(buIds);
//[User{id=1, username='小马哥', password='111111'}, User{id=2, username='小飞侠', password='222222'},
// User{id=3, username='马奎斯', password='333333'}]
}
}
