总体配置目录结构
<?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>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useUnicode=true&characterEncoding=UTF-8"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<mapper resource="com/gek/dao/UserMapper.xml" />
</mappers>
</configuration>
package com.gek.pojo;
public class User {
private int id;
private String name;
private int age;
public User(int id, String name, int age) {
this.id = id;
this.name = name;
this.age = age;
}
public User() {
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
@Override
public String toString() {
return "User{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
'}';
}
}
package com.gek.dao;
import com.gek.pojo.User;
import java.util.List;
public interface UserDao {
List<User> getUserList();
}
<?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 引用userDao路径， id引用UserDao的实现方法 resultType引用User实体类路径
<mapper namespace="com.gek.dao.UserDao">
<select id="getUserList" resultType="com.gek.pojo.User">
select * from test.user;
</select>
</mapper>
package com.gek.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.InputStream;
public class MybatisUtils {
private static SqlSessionFactory sqlSessionFactory;
static {
try {
// 获取SqlSessionFactory对象
String resource ="mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (Exception e) {
e.printStackTrace();
}
}
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession();
}
}
<!-- 在build中配置resources,来防止我们资源导出失败的问题-->
<build>
<resources>
<resource>
<directory>src/main/resources</directory>
<includes>
<include>
**/*.properties
</include>
<include>
**/*.xml
</include>
</includes>
<filtering>true</filtering>
</resource>
<resource>
<directory>src/main/java</directory>
<includes>
<include>
**/*.properties
</include>
<include>
**/*.xml
</include>
</includes>
<filtering>true</filtering>
</resource>
</resources>
</build>
package com.gek.dao;
import com.gek.pojo.User;
import com.gek.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class UserDaoTest {
@Test
public void test(){
// 获得sqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
// 执行SQL
UserDao userDao = sqlSession.getMapper(UserDao.class);
List<User> userList = userDao.getUserList();
for (User user : userList) {
System.out.println(user);
}
sqlSession.close();
}
}
输出对应数据库数据
代码编写步骤
1:在userDao中定义相关方法
2.在userMapper.xml中 编写select语句，需要填写id（对应的方法名），resultType(对应的返回值类型)，parameterType(对应的参数类型)
3.编写测试方法
update,delete,insert的步骤雷同，可以看代码示例如下
package com.gek.dao;
import com.gek.pojo.User;
import java.util.List;
public interface UserDao {
// 模糊查询
List<User> getUserLike(String name);
// 查询全部用户
List<User> getUserList();
// 根据ID查询用户
User getUserById(int id);
// insert一个用户
int addUser(User user);
// 更新一个用户
int updateUser(User user);
// 删除一个用户
int deleteUser(int id);
}
<?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.gek.dao.UserDao">
<select id="getUserList" resultType="com.gek.pojo.User">
select * from test.user;
</select>
<select id="getUserById" resultType="com.gek.pojo.User" parameterType="int">
select * from test.user where id=#{id}
</select>
<insert id="addUser" parameterType="com.gek.pojo.User">
insert into test.user (id,name,age) values (#{id},#{name},#{age})
</insert>
<update id="updateUser" parameterType="com.gek.pojo.User">
update test.user set name=#{name},age=#{age} where id=#{id}
</update>
<delete id="deleteUser" parameterType="int">
delete from test.user where id=#{id}
</delete>
<select id="getUserLike" resultType="com.gek.pojo.User">
select * from test.user where name like "%"#{value}"%"
</select>
</mapper>
package com.gek.dao;
import com.gek.pojo.User;
import com.gek.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class UserDaoTest {
@Test
public void test(){
// 获得sqlSession对象
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
// 执行SQL
UserDao userDao = sqlSession.getMapper(UserDao.class);
List<User> userList = userDao.getUserList();
for (User user : userList) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}
@Test
public void getUserById(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
UserDao mapper = sqlSession.getMapper(UserDao.class);
User userById = mapper.getUserById(1);
System.out.println(userById);
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}
@Test
public void addUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
UserDao mapper = sqlSession.getMapper(UserDao.class);
int gek2222 = mapper.addUser(new User(8, "gek2222", 222));
//提交事务
sqlSession.commit();
System.out.println(gek2222);
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}
@Test
public void updateUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
UserDao mapper = sqlSession.getMapper(UserDao.class);
mapper.updateUser(new User(8, "哈哈", 11));
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}
@Test
public void deleteUser(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
UserDao mapper = sqlSession.getMapper(UserDao.class);
mapper.deleteUser(8);
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}
@Test
public void getUserLike(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
UserDao mapper = sqlSession.getMapper(UserDao.class);
List<User> userLike = mapper.getUserLike("gek");
for (User user : userLike) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}
}
Environments
default 表示选择的是哪个enviroment ,本文中选择了development，表示选择的是第一个development 环境
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<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>
<environment id="test">
<transactionManager type="JDBC"/>
<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>
properites
可以直接引入外部文件
可以在其中增加一些属性配置
如果两个文件有同一个字段，优先使用外部配置文件
<properties resource="db.properties">
<property name="username" value="dev_user"/>
<property name="password" value="F2Fa3!33TYyg"/>
</properties>
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/test?useSSL=false&useUnicode=true&characterEncoding=UTF-8
username=root
password=root
类型别名（typeAliases）
类型别名可为 Java 类型设置一个缩写名字。 它仅用于 XML 配置，意在降低冗余的全限定类名书写。例如
<typeAliases>
<typeAlias alias="User" type="com.gek.pojo.User"/>
</typeAliases>
<select id="getUserList" resultType="User">
select * from test.user;
</select>
也可以指定一个包名，MyBatis 会在包名下面搜索需要的 Java Bean，比如：
扫描实体类的包，他的默认别名就是这个类的类名，首字母小写
<typeAliases>
<!-- <typeAlias alias="User" type="com.gek.pojo.User"/>-->
<package name="com.gek.pojo"/>
</typeAliases>
指定包名的情况下，要改别名需要在实体类上加注解
@Alias("author")
public class Author {
...
}
<settings>
<setting name="cacheEnabled" value="true"/>
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="multipleResultSetsEnabled" value="true"/>
<setting name="useColumnLabel" value="true"/>
<setting name="useGeneratedKeys" value="false"/>
<setting name="autoMappingBehavior" value="PARTIAL"/>
<setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
<setting name="defaultExecutorType" value="SIMPLE"/>
<setting name="defaultStatementTimeout" value="25"/>
<setting name="defaultFetchSize" value="100"/>
<setting name="safeRowBoundsEnabled" value="false"/>
<setting name="mapUnderscoreToCamelCase" value="false"/>
<setting name="localCacheScope" value="SESSION"/>
<setting name="jdbcTypeForNull" value="OTHER"/>
<setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
</settings>
MapperRegistry:注册绑定我们的Mapper文件
方式1： 使用相对于类路径的资源引用
<!-- 使用相对于类路径的资源引用 -->
<mappers>
<mapper resource="com/gek/dao/UserMapper.xml" />
</mappers>
方式2和方式3实现的前提条件：
1：接口和他的Mapper配置文件必须同名
2：接口和她的Mapper配置文件必须在同一个包下
方式2：使用映射器接口实现类的完全限定类名
<mappers>
<!-- <mapper resource="com/gek/dao/UserMapper.xml" />-->
<mapper class="com.gek.dao.UserMapper" />
</mappers>
方式3：将包内的映射器接口实现全部注册为映射器
<mappers>
<!-- <mapper resource="com/gek/dao/UserMapper.xml" />-->
<!-- <mapper class="com.gek.dao.UserMapper" />-->
<package name="com.gek.dao"/>
</mappers>
SqlSessionFactoryBuilder
这个类可以被实例化、使用和丢弃，一旦创建了 SqlSessionFactory，就不再需要它了。 因此 SqlSessionFactoryBuilder 实例的最佳作用域是方法作用域（也就是局部方法变量）。 你可以重用 SqlSessionFactoryBuilder 来创建多个 SqlSessionFactory 实例，但最好还是不要一直保留着它，以保证所有的 XML 解析资源可以被释放给更重要的事情。
SqlSessionFactory
SqlSessionFactory 一旦被创建就应该在应用的运行期间一直存在，没有任何理由丢弃它或重新创建另一个实例。 使用 SqlSessionFactory 的最佳实践是在应用运行期间不要重复创建多次，多次重建 SqlSessionFactory 被视为一种代码“坏习惯”。因此 SqlSessionFactory 的最佳作用域是应用作用域。 有很多方法可以做到，最简单的就是使用单例模式或者静态单例模式。
SqlSession
每个线程都应该有它自己的 SqlSession 实例。SqlSession 的实例不是线程安全的，因此是不能被共享的，所以它的最佳的作用域是请求或方法作用域。 绝对不能将 SqlSession 实例的引用放在一个类的静态域，甚至一个类的实例变量也不行。 也绝不能将 SqlSession 实例的引用放在任何类型的托管作用域中，比如 Servlet 框架中的 HttpSession。 如果你现在正在使用一种 Web 框架，考虑将 SqlSession 放在一个和 HTTP 请求相似的作用域中。 换句话说，每次收到 HTTP 请求，就可以打开一个 S
ResultMap
下面的示例，数据库字段是name,实体类定义的是uname
id name age
id uname age
<resultMap id="UserMap" type="User">
<result column="id" property="id"/>
<result column="name" property="uname"/>
<result column="age" property="age"/>
</resultMap>
<select id="getUserList" resultMap="UserMap">
select * from test.user;
</select>
SLF4J
LOG4J 【掌握】
LOG4J2
JDK_LOGGING
COMMONS_LOGGING
STDOUT_LOGGING 【掌握】
NO_LOGGING
在Mybatis中具体使用那个一日志实现，在设置中设定！
STDOUT_LOGGING标准日志输出！
在mybatis核心配置文件中，配置我们的日志！
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
什么是Log4j？
Log4j是Apache的一个开源项目，通过使用Log4j，我们可以控制日志信息输送的目的地是控制台、文件、GUI组件
我们也可以控制每一条日志的输出格式；
通过定义每一条日志信息的级别，我们能够更加细致地控制日志的生成过程。
通过一个配置文件来灵活地进行配置，而不需要修改应用的代码。
1、先导入log4j的包
<dependencies>
<!-- https://mvnrepository.com/artifact/log4j/log4j -->
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
</dependencies>
2、新建log4j.properties并配置
#将等级为DEBUG的日志信息输出到console和file这两个目的地，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/gek.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.PreparedStatement=DEBUG
3、配置log4j为日志的实现
<settings>
<!-- 标准的日志工厂实现-->
<!-- <setting name="logImpl" value="STDOUT_LOGGING"/>-->
<!-- log4g-->
<setting name="logImpl" value="LOG4J"/>
</settings>
测试运行
使用limit分页
语法： SELECT * from user limit startIndex,pageSize;
1、接口
// 分页
List<User> getUserLimit(Map<String,Integer> map);
2.UserMapper.xml
<resultMap id="UserMap" type="User">
<result column="id" property="id"/>
<result column="name" property="uname"/>
<result column="age" property="age"/>
</resultMap>
<select id="getUserLimit" parameterType="map" resultMap="UserMap">
select * from test.user limit #{startIndex},#{pageSize}
</select>
3.测试方法
@Test
public void getUserLimit(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
HashMap<String, Integer> map = new HashMap<>();
map.put("startIndex",0);
map.put("pageSize",3);
List<User> userLimit = mapper.getUserLimit(map);
for (User user : userLimit) {
System.out.println(user);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
}
}
4.输出
不再使用SQL实现分页。
1.接口
// 分页2
List<User> getUserByRowBounds();
2.UserMapper.xml
<!-- 分页2 -->
<select id="getUserByRowBounds" resultMap="userMap">
select * from mybatis.user
</select>
3.测试
@Test
public void getUserByRowBounds(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
// RowBounds实现
RowBounds rowBounds = new RowBounds(1, 2);
// 通过Java代码层面实现分页
List<User> userList = sqlSession.selectList("com.github.subei.dao.UserMapper.getUserByRowBounds",null,rowBounds);
for (User user : userList) {
System.out.println(user);
}
sqlSession.close();
}
1.注解在接口上实现
@Select("select * from user")
List<User> getUsers();
2.需要在核心配置文件中绑定接口！
<!--绑定接口-->
<mappers>
<mapper class="com.gek.dao.UserMapper"/>
</mappers>
3.测试
package com.github.subei.test;
import com.github.subei.dao.UserMapper;
import com.github.subei.pojo.User;
import com.github.subei.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import java.util.List;
public class UserMapperTest {
@Test
public void test(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<User> users = mapper.getUsers();
for(User user : users){
System.out.println(user);
}
sqlSession.close();
}
}
SQL
CREATE TABLE `teacher` ( `id` INT ( 10 ) NOT NULL, `name` VARCHAR ( 30 ) DEFAULT NULL, PRIMARY KEY ( `id` ) ) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO teacher ( `id`, `name` )
VALUES
( 1, '秦老师' );
CREATE TABLE `student` (
`id` INT ( 10 ) NOT NULL,
`name` VARCHAR ( 30 ) DEFAULT NULL,
`tid` INT ( 10 ) DEFAULT NULL,
PRIMARY KEY ( `id` ),
KEY `fktid` ( `tid` ),
CONSTRAINT `fktid` FOREIGN KEY ( `tid` ) REFERENCES `teacher` ( `id` )
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO `student` ( `id`, `name`, `tid` )
VALUES
( '1', '小明', '1' );
INSERT INTO `student` ( `id`, `name`, `tid` )
VALUES
( '2', '小红', '1' );
INSERT INTO `student` ( `id`, `name`, `tid` )
VALUES
( '3', '小张', '1' );
INSERT INTO `student` ( `id`, `name`, `tid` )
VALUES
( '4', '小李', '1' );
INSERT INTO `student` ( `id`, `name`, `tid` )
VALUES
( '5', '小王', '1' );
StudentMapper接口
package com.gek.dao;
import com.gek.pojo.Student;
import java.util.List;
public interface StudentMapper {
//查询所有的学生信息，以及对应的老师信息
List<Student> findAllStudent();
}
StudentMapper.xml文件
<select id="findAllStudent" resultMap="StudentTeacher">
select * from student;
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!-- 复杂的属性，我们需要单独处理
对象 ：association
集合：collection-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id = #{id}
</select>
测试类
@Test
public void findAllStudent(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> allStudent = mapper.findAllStudent();
for (Student student : allStudent) {
System.out.println(student);
}
sqlSession.close();
}
输出结果
就是修改一下StudentMapper.xml文件
<select id="findAllStudent2" resultMap="StudentTeacher2">
select s.name sname,s.id sid,t.name tname
from student s,teacher t where s.tid=t.id;
</select>
<resultMap id="StudentTeacher2" type="Student">
<result property="name" column="sname"/>
<result property="id" column="sid"/>
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
实体类
package com.gek.pojo;
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private int tid;
// private Teacher teacher;
}
package com.gek.pojo;
import lombok.Data;
import java.util.List;
@Data
public class Teacher {
private int id;
private String name;
//一个老师拥有多个学生
private List<Student> students;
}
TeacherMapper接口
package com.gek.dao;
import com.gek.pojo.Student;
import com.gek.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface TeacherMapper {
//获取老师
Teacher getTeacherById(@Param("tid") int id);
}
编写TeacherMapper.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.gek.dao.TeacherMapper">
<select id="getTeacherById" resultMap="StudentTeacher">
select s.name sname,s.id sid,t.name tname,t.id tid
from student s,teacher t where tid=#{tid} and t.id =s.tid;
</select>
<resultMap id="StudentTeacher" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<!--复杂的属性，需要单独处理
对象： association 集合： collection
javaType="" 指定属性的类型！
集合中的泛型信息，我们使用ofType获取
-->
<collection property="students" ofType="Student">
<result property="name" column="sname"/>
<result property="id" column="sid"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
</mapper>
测试类
@Test
public void getTeacherById(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacherById = mapper.getTeacherById(1);
System.out.println(teacherById);
sqlSession.close();
}
输出
package com.gek.dao;
import com.gek.pojo.Student;
import com.gek.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface TeacherMapper {
Teacher getTeacherById2(@Param("tid") int id);
}
!-- 子查询方式-->
<select id="getTeacherById2" resultMap="StudentTeacher2">
select * from teacher;
</select>
<!-- resultMap 上的type标识的是实体类的路径，因为这边取了别名所以可以直接用Teacher-->
<resultMap id="StudentTeacher2" type="Teacher">
//javatype表示实体类返回值类型，oftype表示对应实体类类名 property对应属性名字
<collection property="students" javaType="ArrayList" ofType="Student" select="GetStudent" column="id"/>
</resultMap>
<select id="GetStudent" resultType="Student">
select * from student where tid= #{tid};
</select>
@Test
public void getTeacherById2(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacherById2 = mapper.getTeacherById2(1);
System.out.println(teacherById2);
sqlSession.close();
}
注意点
保证SQL的可读性，尽量保证通俗易懂！
注意一对多和多对一中，属性名和字段的问题！
如果问题不好排查错误，可以使用日志 ， 建议使用 Log4j！
定义：根据不同的条件执行不同的sql
接口文件
//查询博客
List<Blog> queryBlog(Map map);
xml文件
<select id="queryBlog" parameterType="Map" resultType="blog">
select * from blog
<where>
<if test="title != null">
title= #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
</select>
@Test
public void queryBlog(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap hashMap = new HashMap();
hashMap.put("title","gektest");
List<Blog> blogs = mapper.queryBlog(hashMap);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
// chooseWhen
List<Blog> queryChoose(Map map);
<select id="queryChoose" parameterType="Map" resultType="blog">
select * from blog
<where>
<choose>
<when test="title !=null">
title=#{title}
</when>
<when test="author !=null">
and author =#{author}
</when>
<otherwise>
and views =#{views}
</otherwise>
</choose>
</where>
</select>
@Test
public void queryChoose(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap hashMap = new HashMap();
// hashMap.put("title","gektest");
hashMap.put("views",99999);
List<Blog> blogs = mapper.queryChoose(hashMap);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}
select * from mybatis.blog
<where>
<if test="title != null">
title = #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</where>
<update id="updateBlog" parameterType="map">
update mybatis.blog
<set>
<if test="title != null">
title = #{title},
</if>
<if test="author != null">
author = #{author}
</if>
</set>
where id = #{id}
</update>
上面的sql定义好id,下面需要用到这个片段的，用include标签 refid填写上面定义的id
<!-- sql片段-->
<sql id="if-title-author">
<if test="title != null">
title= #{title}
</if>
<if test="author != null">
and author = #{author}
</if>
</sql>
<select id="queryBlog" parameterType="Map" resultType="blog">
select * from blog
<where>
<include refid="if-title-author"></include>
</where>
</select>
注意事项：
最好基于单表来定义SQL片段！
不要存在where标签
BlogMapper
// foreach练习
List<Blog> queryBlogForeach(Map map);
BlogMapper.xml
<select id="queryBlogForeach" parameterType="map" resultType="blog">
select * from blog
<where>
<foreach item="id" collection="ids" open="id in (" separator="," close=")">
#{id}
</foreach>
</where>
</select>
测试方法
@Test
public void queryBlogForeach(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
BlogMapper mapper = sqlSession.getMapper(BlogMapper.class);
HashMap hashMap = new HashMap();
ArrayList<Integer> integers = new ArrayList<>();
integers.add(1);
integers.add(2);
integers.add(3);
hashMap.put("ids",integers);
System.out.println(hashMap);
List<Blog> blogs = mapper.queryBlogForeach(hashMap);
for (Blog blog : blogs) {
System.out.println(blog);
}
sqlSession.close();
}