本文档详细介绍了Mybatis的使用，包括Mybatis的入门、基本使用、配置文件详解、日志处理、多表关联查询、注解开发、缓存机制等内容，并涉及与Servlet的整合和PageHelper分页插件的使用，旨在帮助读者全面掌握Mybatis框架。
Mybatis
- 1、Mybatis入门
- 2、Mybatis的基本使用
- 3、Java日志处理框架
- 4、Mybatis 配置的完善
- 5、SqlSession 对象下的常用API
- 6、Mapper 动态代理
- 7、动态 SQL
- 8、Mybatis 缓存
- 9、Mybatis 的多表关联查询
- 10、Mybatis 注解的使用
- 11、Mybatis Generator 工具的使用
- 12、PageHelper 分页插件
- 13、Mybatis 与 Servlet 整合
1、Mybatis入门
1.1 什么是框架
1.2 什么是ORM
1.3 MyBatis简介
2、Mybatis的基本使用
2.1 Mybatis 的jar 包介绍
2.2 核心API 介绍
2.2.1 核心API
2.2.2 核心API 工作流程
2.2.3 生命周期
2.3 Mybatis的配置文件
2.3.1 全局配置文件
2.3.1.1 properties 标签
2.3.1.2 settings 标签
2.3.1.3 typeAliases 标签
2.3.1.4 environments 标签
- transactionManager 节点
事务处理器。
在MyBatis 中有两种类型的事务管理器（也就是type="[JDBC|MANAGED]"）
JDBC ：这个配置直接使用了JDBC 的提交和回滚设施，它依赖从数据源获得的连接来
管理事务作用域。
MANAGED ：不做事务处理。 - dataSource 标签
dataSource 元素使用标准的JDBC 数据源接口来配置JDBC 连接对象的资源。
UNPOOLED：使用直连。
POOLED：使用池连。
JNDI ：使用JNDI 方式连接
2.3.1.5 mapper 标签
2.3.2 映射配置文件
2.3.2.1 resultMap 标签
- id 标签
指定主键中的值，用于标识一个结果映射。 - result 标签
指定非主键中的值，用于标识一个结果映射。 - association 标签
通常用来映射一对一的关系。 - collection 标签
通常用来映射一对多的关系。
2.3.2.2 select 标签
2.3.2.3 insert 标签
2.3.2.4 update 标签
2.3.2.5 delete 标签
2.3.2.6 sql 标签
2.4 Mybatis的入门案例
2.4.1 搭建环境
2.4.1.1 创建表
CREATE TABLE users (
userid int(11) NOT NULL AUTO_INCREMENT,
username varchar(20) DEFAULT NULL,
usersex varchar(10) DEFAULT NULL,
PRIMARY key(userid)
) engine=InnoDB DEFAULT charset=utf8;
2.4.1.2 添加DTD约束文件
- http://mybatis.org/dtd/mybatis-3-config.dtd
- http://mybatis.org/dtd/mybatis-3-mapper.dtd
2.4.1.3 创建项目
2.4.1.4 添加jar包
2.4.1.5 创建实体
package com.pojo;
public class Users {
private int userid;
private String username;
private String usersex;
public int getUserid() {
return userid;
}
public void setUserid(int userid) {
this.userid = userid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getUsersex() {
return usersex;
}
public void setUsersex(String usersex) {
this.usersex = usersex;
}
}
2.4.1.6 创建properties 文件----db.properties
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/bjsxt
jdbc.username=root
jdbc.password=root
2.4.1.7 创建全局配置文件----mybatis-cfg.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>
<!--引入properties文件-->
<properties resource="db.properties" />
<!--环境配置-->
<environments default="development">
<environment id="development">
<!--配置事务-->
<transactionManager type="JDBC"></transactionManager>
<!--配置数据源 POOLED池连-->
<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>
<!--引入映射配置文件-->
<mappers>
<!--使用相对路径方式引入-->
<mapper resource="com/mapper/UsersMapper.xml"></mapper>
</mappers>
</configuration>
2.4.1.7 创建映射配置文件----UsersMapper.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.mapper.UserMapper">
</mapper>
2.4.2 查询数据
2.4.2.1 修改映射配置文件----UsersMapper.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.mapper.UserMapper">
<!--查询所有用户-->
<select id="selectUsersAll" resultType="com.pojo.Users">
select * from users
</select>
</mapper>
2.4.2.2 创建UsersDao 接口
package com.dao;
import com.pojo.Users;
import java.io.IOException;
import java.util.List;
public interface UserDao {
List<Users> selectUsersAll() throws IOException;
}
2.4.2.3 创建UsersDao 接口实现类
package com.dao.impl;
import com.dao.UserDao;
import com.pojo.Users;
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.List;
public class UsersDaoImpl implements UserDao {
/**
* 查询所有用户
* @return
*/
@Override
public List<Users> selectUsersAll() throws IOException {
// 创建SqlSessionFactory对象
InputStream inputStream = Resources.getResourceAsStream("mybatis-cfg.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
// 通过SqlSession对象下的API完成对数据库的操作
List<Users> list = sqlSession.selectList("com.mapper.UserMapper.selectUsersAll");
// 关闭SqlSession对象
sqlSession.close();
return list;
}
}
2.4.2.4 创建测试类
package com.test;
import com.dao.UserDao;
import com.dao.impl.UsersDaoImpl;
import com.pojo.Users;
import java.io.IOException;
import java.util.List;
public class Test {
public static void main(String[] args) throws IOException {
UserDao userDao = new UsersDaoImpl();
List<Users> list = userDao.selectUsersAll();
for (Users users : list) {
System.out.println(users.getUserid() + "\t" + users.getUsername() + "\t" + users.getUsersex());
}
}
}
2.4.3 根据用户Id 查询数据
2.4.3.1 修改映射配置文件
<?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.mapper.UserMapper">
<!--根据用户ID查询用户-->
<select id="selectUsersById" parameterType="int" resultType="com.pojo.Users">
-- 如果只有一个参数，占位的参数可以随便取名；# {}：占位
select * from users where userid = #{AA}
</select>
</mapper>
2.4.3.2 修改UsersDao 接口
package com.dao;
import com.pojo.Users;
import java.io.IOException;
import java.util.List;
public interface UserDao {
Users selectUsersById(int userid) throws IOException;
}
2.4.3.3 修改UsersDao 接口实现类
package com.dao.impl;
import com.dao.UserDao;
import com.pojo.Users;
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 UsersDaoImpl implements UserDao {
/**
* 根据用户ID查询用户
* @param userid
* @return
* @throws IOException
*/
@Override
public Users selectUsersById(int userid) throws IOException {
// 创建SqlSessionFactory对象
InputStream inputStream = Resources.getResourceAsStream("mybatis-cfg.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
// 获取SqlSession对象
SqlSession sqlSession = sqlSessionFactory.openSession();
// 通过SqlSession对象下的API完成对数据库的操作
Users users = sqlSession.selectOne("com.mapper.UserMapper.selectUsersById", userid);
// 关闭SqlSession对象
sqlSession.close();
return users;
}
}
2.4.3.4 修改测试类
import com.dao.UserDao;
import com.dao.impl.UsersDaoImpl;
import com.pojo.Users;
import java.io.IOException;
public class Test {
public static void main(String[] args) throws IOException {
UserDao userDao = new UsersDaoImpl();
Users users = userDao.selectUsersById(1);
System.out.println(users.getUserid() + "\t" + users.getUsername() + "\t" + users.getUsersex());
}
}
2.4.4 Mybatis 中的参数绑定
2.4.5 创建Mybatis 的工具类
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 ThreadLocal<SqlSession> threadLocal = new ThreadLocal<>();
private static SqlSessionFactory sqlSessionFactory = null;
static {
// 创建SqlSessionFactory
InputStream inputStream = null;
try {
inputStream = Resources.getResourceAsStream("mybatis-cfg.xml");
} catch (IOException e) {
e.printStackTrace();
}
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
// 获取SqlSession
public static SqlSession getSqlSession() {
SqlSession sqlSession = threadLocal.get();
if (sqlSession == null) {
sqlSession = sqlSessionFactory.openSession();
threadLocal.set(sqlSession);
}
return sqlSession;
}
// 关闭SqlSession
public static void closeSqlSession() {
SqlSession sqlSession = threadLocal.get();
if (sqlSession != null) {
sqlSession.close();
threadLocal.set(null);
}
}
}
2.4.6 完成DML 操作
2.4.6.1 Mybatis 的事务提交方式
2.4.6.2 添加用户操作
- 修改映射配置文件
<?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.mapper.UserMapper">
<!--添加用户-->
<insert id="insertUsers" >
insert into users value(default, #{username}, #{usersex})
</insert>
</mapper>
- 修改UsersDao 接口
package com.dao;
import com.pojo.Users;
public interface UserDao {
void insertUsers(Users users);
}
- 修改UsersDao 接口实现类
package com.dao.impl;
import com.dao.UserDao;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class UsersDaoImpl implements UserDao {
@Override
public void insertUsers(Users users) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
int insert = sqlSession.insert("com.mapper.UserMapper.insertUsers", users);
}
}
- 创建UsersService 业务层接口
package com.service;
import com.pojo.Users;
public interface UsersService {
void addUsers(Users users);
}
- 创建UsersService 业务层接口实现类
package com.service.impl;
import com.dao.UserDao;
import com.dao.impl.UsersDaoImpl;
import com.pojo.Users;
import com.service.UsersService;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class UsersServiceImpl implements UsersService {
/**
* 添加用户
* @param users
*/
@Override
public void addUsers(Users users) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
UserDao userDao = new UsersDaoImpl();
userDao.insertUsers(users);
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
sqlSession.rollback();
} finally {
sqlSession.close();
}
}
}
- 创建测试类
package com.test;
import com.pojo.Users;
import com.service.UsersService;
import com.service.impl.UsersServiceImpl;
public class AddUserTest {
public static void main(String[] args) {
UsersService usersService = new UsersServiceImpl();
Users users = new Users();
users.setUsername("zhangsan");
users.setUsersex("male");
usersService.addUsers(users);
}
}
2.4.6.3 更新用户操作
- 修改映射配置文件
<?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.mapper.UserMapper">
<!--查询用户-->
<select id="selectUserById" resultType="com.pojo.Users">
select * from users where userid = ${userid}
</select>
<!--更新用户-->
<update id="updateUsersById">
update users set username = #{username},usersex = #{usersex} where userid = #{userid}
</update>
</mapper>
- 修改UsersDao 接口
package com.dao;
import com.pojo.Users;
public interface UserDao {
Users selectUserById(int userid);
void updateUsersById(Users users);
}
- 修改UsersDao 接口实现类
package com.dao.impl;
import com.dao.UserDao;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class UsersDaoImpl implements UserDao {
@Override
public Users selectUserById(int userid) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
Users users = sqlSession.selectOne("com.mapper.UserMapper.selectUserById", userid);
return users;
}
@Override
public void updateUsersById(Users users) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
int update = sqlSession.update("com.mapper.UserMapper.updateUsersById",users);
}
}
- 修改UsersService 接口
package com.service;
import com.pojo.Users;
public interface UsersService {
Users UpdateUsers(int userid);
void modifyUsers(Users users);
}
- 修改UsersService 接口实现类
package com.service.impl;
import com.dao.UserDao;
import com.dao.impl.UsersDaoImpl;
import com.pojo.Users;
import com.service.UsersService;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class UsersServiceImpl implements UsersService {
@Override
public Users UpdateUsers(int userid) {
Users users = null;
try {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserDao userDao = new UsersDaoImpl();
users = userDao.selectUserById(userid);
} catch (Exception e) {
e.printStackTrace();
} finally {
MybatisUtils.closeSqlSession();
}
return users;
}
@Override
public void modifyUsers(Users users) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
UserDao userDao = new UsersDaoImpl();
userDao.updateUsersById(users);
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
sqlSession.rollback();
} finally {
MybatisUtils.closeSqlSession();
}
}
}
- 创建测试类
package com.test;
import com.pojo.Users;
import com.service.UsersService;
import com.service.impl.UsersServiceImpl;
public class UpdateaUsersTest {
public static void main(String[] args) {
UsersService usersService = new UsersServiceImpl();
Users users = usersService.UpdateUsers(1);
System.out.println(users.getUserid());
users.setUsername("ZHANGSAN");
users.setUsersex("female");
usersService.modifyUsers(users);
}
}
2.4.6.4 删除用户操作
- 修改映射配置文件
<?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.mapper.UserMapper">
<!--刪除用户-->
<delete id="deleteUsersById">
delete from users where userid = #{userid}
</delete>
</mapper>
- 修改UsersDao 接口
package com.dao;
public interface UserDao {
void deleteUsersByid(int userid);
}
- 修改UsersDao 接口实现类
package com.dao.impl;
import com.dao.UserDao;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class UsersDaoImpl implements UserDao {
@Override
public void deleteUsersByid(int userid) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
int delete = sqlSession.delete("com.mapper.UserMapper.deleteUsersById", userid);
}
}
- 修改UsersService 接口
package com.service;
public interface UsersService {
void dropUsersByid(int userid);
}
- 修改UsersService 接口实现类
package com.service.impl;
import com.dao.UserDao;
import com.dao.impl.UsersDaoImpl;;
import com.service.UsersService;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class UsersServiceImpl implements UsersService {
@Override
public void dropUsersByid(int userid) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
UserDao userDao = new UsersDaoImpl();
userDao.deleteUsersByid(userid);
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
sqlSession.rollback();
} finally {
MybatisUtils.closeSqlSession();
}
}
}
- 创建测试类
package com.test;
import com.service.UsersService;
import com.service.impl.UsersServiceImpl;
public class DeleteUsersTest {
public static void main(String[] args) {
UsersService usersService = new UsersServiceImpl();
usersService.dropUsersByid(3);
}
}
3、Java日志处理框架
3.1 常用的日志处理框架
3.2 Log4j
3.3 Log4j的使用
3.3.1 Log4j 配置文件详解
3.3.1.1 Log4j 配置文件名
3.3.1.2 配置根Logger
3.3.1.3 Log4j 中的appender
3.3.1.4 向控制台输出的appender
### appender.console 输出到控制台###
log4j.appender.console=org.apache.log4j.ConsoleAppender
39
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=<%d> %5p (%F:%L) [%t] (%c)
- %m%n
log4j.appender.console.Target=System.out
3.3.1.5 向文件输出的appender
### appender.logfile 输出到日志文件###
log4j.appender.logfile=org.apache.log4j.RollingFileAppender
log4j.appender.logfile.File=SysLog.log
log4j.appender.logfile.MaxFileSize=500KB
log4j.appender.logfile.MaxBackupIndex=7
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
log4j.appender.logfile.layout.ConversionPattern=<%d> %p (%F:%L) [%t] %c - %m%n
3.3.1.6 向数据库输出的appender
log4j.appender.logDB=org.apache.log4j.jdbc.JDBCAppender
log4j.appender.logDB.layout=org.apache.log4j.PatternLayout
log4j.appender.logDB.Driver=com.mysql.jdbc.Driver
log4j.appender.logDB.URL=jdbc:mysql://localhost:3306/bjsxt
log4j.appender.logDB.User=root
log4j.appender.logDB.Password=root
log4j.appender.logDB.Sql=INSERT INTO
logs(project_name,create_date,level,category,file_name,thread_name,line,all_
category,message)values('logDemo','%d{yyyy-MM-ddHH:mm:ss}','%p','%c','%F','%
t','%L','%l','%m')
3.3.1.7 通过包名控制日志输出级别
log4j.logger.org.apache=FATAL
log4j.logger.org.apache.commons=ERROR
log4j.logger.org.springframework=ERROR
log4j.logger.com.bjsxt=ERROR
3.3.1.8 Log4j 的输出格式
3.3.2 Log4j 的使用方式
- log4j.properties
log4j.rootLogger = info,console,logfile
### appender.console 输出到控制台 ###
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=<%d> %5p (%F:%L) [%t] (%c) - %m%n
log4j.appender.console.Target=System.out
### appender.logfile 输出到日志文件 ###
log4j.appender.logfile=org.apache.log4j.RollingFileAppender
log4j.appender.logfile.File=SysLog.log
log4j.appender.logfile.MaxFileSize=500KB
log4j.appender.logfile.MaxBackupIndex=7
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
log4j.appender.logfile.layout.ConversionPattern=<%d> %p (%F:%L) [%t] %c - %m%n
### 向数据库输出的appender ###
#log4j.appender.logDB=org.apache.log4j.jdbc.JDBCAppender
#log4j.appender.logDB.layout=org.apache.log4j.PatternLayout
#log4j.appender.logDB.Driver=com.mysql.jdbc.Driver
#log4j.appender.logDB.URL=jdbc:mysql://localhost:3306/bjsxt
#log4j.appender.logDB.User=root
#log4j.appender.logDB.Password=root
#log4j.appender.logDB.Sql=INSERT INTO
#logs(project_name,create_date,level,category,file_name,thread_name,line,all_
#category,message)values('logDemo','%d{yyyy-MM-ddHH:mm:ss}','%p','%c','%F','%
#t','%L','%l','%m')
### 通过包名控制日志输出级别 ###
#log4j.logger.org.apache=FATAL
#log4j.logger.org.apache.commons=ERROR
#log4j.logger.org.springframework=ERROR
#log4j.logger.com.bjsxt=ERROR
3.3.2.1 Log4j.jar
package com.test;
import org.apache.log4j.Logger;
public class LogDemo {
private final static Logger logger = Logger.getLogger(LogDemo.class);
public static void main(String[] args) {
try {
String temp = null;
temp.length();
} catch (Exception e) {
// logger.debug(e);
logger.error(e);
}
}
}
3.3.2.2 commons-logging + log4j
package com.test;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
public class CommonLogDemo {
private final static Log log = LogFactory.getLog(CommonLogDemo.class);
public static void main(String[] args) {
try {
String str = null;
str.length();
} catch (Exception e) {
log.error("error.....",e);
}
}
}
3.3.2.3 slf4j-api + slf4j-log4j + log4j
package com.test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class SLF4jDemo {
private final static Logger logger = LoggerFactory.getLogger(SLF4jDemo.class);
public static void main(String[] args) {
logger.debug("SLF4J....");
try {
String str = null;
str.length();
} catch (Exception e) {
logger.error("error....", e);
}
}
}
4、Mybatis 配置的完善
4.1 Mybatis 的日志管理
- 指定SLF4J 作为日志处理器
<settings>
<setting name="logImpl" value="SLF4J"/>
</settings>
4.2 使用别名alias
4.2.1 方式一
- 使用typeAlias 指定单个类的别名(mybatis-cfg.xml)
<!--配置别名-->
<typeAliases>
<!--方式一：类的别名-->
<typeAlias type="com.pojo.Users" alias="u"></typeAlias>
</typeAliases>
- UsersMapper.xml
<select id="selectUsersById" parameterType="int" resultType="u">
select * from users where userid = #{suibian}
</select>
4.2.2 方式二
- 使用package 指定某个包下所有类的默认别名
<!--配置别名-->
<typeAliases>
<!--方式二：包的别名-->
<typeAlias type="com.pojo" />
</typeAliases>
- 引入别名后的映射文件
<select id="selectUsersById" parameterType="int"
resultType="users">
select * from users where userid = #{suibian}
</select>
5、SqlSession 对象下的常用API
5.1 查询操作
- selectOne 方法
T selectOne(String namespace + id , Object parameter) - selectList 方法
List selectList(String namespace + id , Object parameter) - selectMap 方法
<K,V> Map<K,V> selectMap(String namespace + id , Object parameter, String mapKey)
<?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" />
<!--使用SLF4J做日志处理-->
<settings>
<setting name="logImpl" value="SLF4J"/>
</settings>
<!--配置别名-->
<typeAliases>
<!--方式一：类的别名-->
<typeAlias type="com.pojo.Users" alias="u"></typeAlias>
<!--方式二：包的别名-->
<typeAlias type="com.pojo" />
</typeAliases>
<!--环境配置-->
<environments default="development">
<environment id="development">
<!--配置事务-->
<transactionManager type="JDBC"></transactionManager>
<!--配置数据源 POOLED池连-->
<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>
<!--引入映射配置文件-->
<mappers>
<!--使用相对路径方式引入-->
<mapper resource="com/mapper/UsersMapper.xml"></mapper>
</mappers>
</configuration>
package com.dao;
import com.pojo.Users;
import java.util.Map;
public interface UsersDao {
Map<Integer, Users> selectUsersByNameAndSex(String username, String usersex);
}
package com.dao.impl;
import com.dao.UsersDao;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.HashMap;
import java.util.Map;
public class UsersDaoImpl implements UsersDao {
@Override
public Map<Integer, Users> selectUsersByNameAndSex(String username, String usersex) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
Map<String, String> param = new HashMap<>();
param.put("name", username);
param.put("sex", usersex);
Map<Integer, Users> users = sqlSession.selectMap("com.mapper.UserMapper.selectUsersByNameAndSex", param, "userid");
return users;
}
}
package com.service;
import com.pojo.Users;
import java.util.Map;
public interface UsersService {
Map<Integer, Users> findUsersByNameAndSex(String username, String usersex);
}
package com.service.impl;
import com.dao.UsersDao;
import com.dao.impl.UsersDaoImpl;;
import com.pojo.Users;
import com.service.UsersService;
import com.utils.MybatisUtils;
import java.util.Map;
public class UsersServiceImpl implements UsersService {
@Override
public Map<Integer, Users> findUsersByNameAndSex(String username, String usersex) {
Map<Integer, Users> map = null;
try {
UsersDao usersDao = new UsersDaoImpl();
map = usersDao.selectUsersByNameAndSex(username, usersex);
} catch(Exception e) {
e.printStackTrace();
} finally {
MybatisUtils.closeSqlSession();
}
return map;
}
}
5.2 DML 操作
- insert 方法
int insert(String statement, Object parameter) - update 方法
int update(String statement, Object parameter) - delete 方法
int delete(String statement, Object parameter)
6、Mapper 动态代理
6.1 基于Mybatis 的Dao 层设计
6.1.1 Dao 层不使用Mapper 动态代理
6.1.2 Dao 层使用Mapper 动态代理
6.2 Mapper 动态代理规范
6.3 Mapper 动态代理的使用
6.3.1 搭建环境
- 创建项目
- 添加jar 包
- 创建实体
package com.pojo;
public class Users {
private String userid;
private String username;
private String usersex;
public String getUserid() {
return userid;
}
public void setUserid(String userid) {
this.userid = userid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getUsersex() {
return usersex;
}
public void setUsersex(String usersex) {
this.usersex = usersex;
}
@Override
public String toString() {
return "Users{" +
"userid='" + userid + '\'' +
", username='" + username + '\'' +
", usersex='" + usersex + '\'' +
'}';
}
}
- 创建Mybatis 工具类
package com.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 ThreadLocal<SqlSession> threadLocal = new ThreadLocal<>();
private static SqlSessionFactory sqlSessionFactory = null;
static {
// 创建SqlSessionFactory
InputStream inputStream = null;
try {
inputStream = Resources.getResourceAsStream("mybatis-cfg.xml");
} catch (IOException e) {
e.printStackTrace();
}
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
}
// 获取SqlSession
public static SqlSession getSqlSession() {
SqlSession sqlSession = threadLocal.get();
if (sqlSession == null) {
sqlSession = sqlSessionFactory.openSession();
threadLocal.set(sqlSession);
}
return sqlSession;
}
// 关闭SqlSession
public static void closeSqlSession() {
SqlSession sqlSession = threadLocal.get();
if (sqlSession != null) {
sqlSession.close();
threadLocal.set(null);
}
}
}
6.3.2 配置Mybatis 框架
- 添加db.properteis 文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/myemployees
jdbc.username=root
jdbc.password=root
- 添加log4j.properties 文件
log4j.rootLogger = debug,console,logfile
### appender.console 输出到控制台 ###
log4j.appender.console=org.apache.log4j.ConsoleAppender
log4j.appender.console.layout=org.apache.log4j.PatternLayout
log4j.appender.console.layout.ConversionPattern=<%d> %5p (%F:%L) [%t] (%c) - %m%n
log4j.appender.console.Target=System.out
### appender.logfile 输出到日志文件 ###
log4j.appender.logfile=org.apache.log4j.RollingFileAppender
log4j.appender.logfile.File=SysLog.log
log4j.appender.logfile.MaxFileSize=500KB
log4j.appender.logfile.MaxBackupIndex=7
log4j.appender.logfile.layout=org.apache.log4j.PatternLayout
log4j.appender.logfile.layout.ConversionPattern=<%d> %p (%F:%L) [%t] %c - %m%n
- 添加全局配置文件
<?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>
<!--引入db.properties配置文件-->
<properties resource="db.properties" />
<!--配置别名-->
<typeAliases>
<package name="com.pojo"/>
</typeAliases>
<!--配置环境-->
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"></transactionManager>
<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>
<package name="com.mapper"/>
</mappers>
</configuration>
- 添加UsersMapper 接口
package com.mapper;
public interface UsersMapper {
}
- 添加UsersMapper 映射配置文件
<?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.mapper.UsersMapper">
</mapper>
6.3.3 实现查询所有用户
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--查询所有用户-->
<select id="selectUsersAll" resultType="Users">
select * from users
</select>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
import java.util.List;
public interface UsersMapper {
List<Users> selectUsersAll();
}
- 创建业务层接口
package com.service;
import com.pojo.Users;
import java.util.List;
public interface UsersService {
List<Users> findUsersAll();
}
- 创建业务层接口实现类
- 创建测试类
6.3.4 实现根据用户 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.mapper.UsersMapper">
<!--根据用户ID查询用户-->
<select id="selectUserById" resultType="users">
select * from users where userid = #{userid}
</select>
</mapper>
- 修改 UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
public interface UsersMapper {
Users selectUsersById(int i);
}
- 修改业务层接口
- 修改业务层接口实现类
- 创建测试类
6.4 Mapper 动态代理模式下的多参数处理
6.4.1 顺序传参法
6.4.2 @Param 注解传参法
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--根据用户姓名和性别查询用户，使用@Param注解传参法-->
<select id="selectUsersAnnParam" resultType="users">
select * from users where username = #{name} and usersex = #{sex}
</select>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface UsersMapper {
List<Users> selectUsersAnnParam(@Param("name") String username, @Param("sex") String usersex);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class SelectUsersAnnParamTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
List<Users> users = mapper.selectUsersAnnParam("wangwu", "female");
users.forEach(System.out::println);
MybatisUtils.closeSqlSession();
}
}
6.4.3 POJO 传参法
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--根据用户姓名和性别查询用户，使用POJO传参法-->
<select id="selectUsersPOJOParam" resultType="users">
select * from users where username = #{username} and usersex = #{usersex}
</select>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
import java.util.List;
public interface UsersMapper {
List<Users> selectUsersPOJOParam(Users users);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class SelectUsersPOJOParamTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
Users users = new Users();
users.setUsername("wangwu");
users.setUsersex("female");
List<Users> list = mapper.selectUsersPOJOParam(users);
list.forEach(System.out::println);
MybatisUtils.closeSqlSession();
}
}
6.4.4 Map 传参法
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--根据用户姓名和性别查询用户，使用POJO传参法-->
<select id="selectUsersMapParam" resultType="users">
select * from users where username = #{keyname} and usersex = #{keysex}
</select>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
import java.util.List;
import java.util.Map;
public interface UsersMapper {
List<Users> selectUsersMapParam(Map<String, String> map);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
public class SelectUsersMapParamTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
Map<String, String> map = new HashMap<>();
map.put("keyname","wangwu");
map.put("keysex","female");
List<Users> list = mapper.selectUsersMapParam(map);
list.forEach(System.out::println);
MybatisUtils.closeSqlSession();
}
}
6.5 映射配置文件中的特殊字符处理
6.5.1 使用符号实体
6.5.2 使用符号实体
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--查询大于1的用户-->
<select id="selectUsers" resultType="users">
--旧版本是不支持符号
--select * from users where userid > #{userid}
--select * from users where userid > #{userid}
select * from users where userid <![CDATA[ > ]]> #{userid}
</select>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
import java.util.List;
public interface UsersMapper {
List<Users> selectUsers(int userid);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class SelectUsersTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
List<Users> list = mapper.selectUsers(1);
list.forEach(System.out::println);
MybatisUtils.closeSqlSession();
}
}
6.6 Mybatis 的分页查询
6.6.1 使用 RowBounds
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--查询所有数据使用RowBounds实现分页处理-->
<select id="selectUsersRowBounds" resultType="users">
select * from users
</select>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
import org.apache.ibatis.session.RowBounds;
import java.util.List;
public interface UsersMapper {
List<Users> selectUsersRowBounds(RowBounds rowBounds);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class SelectUsersRowBoundsTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
RowBounds rowBounds = new RowBounds(1, 1);
List<Users> list = mapper.selectUsersRowBounds(rowBounds);
list.forEach(System.out::println);
MybatisUtils.closeSqlSession();
}
}
6.6.2 使用 SQL 语句分页
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--查询所有数据使用limit实现分页处理-->
<select id="selectUsersLimit" resultType="users">
select * from users limit #{offset }, #{limit}
</select>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface UsersMapper {
List<Users> selectUsersLimit(@Param("offset") int offset, @Param("limit") int limit);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class SelectUsersLimitTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
List<Users> list = mapper.selectUsersLimit(1, 1);
list.forEach(System.out::println);
MybatisUtils.closeSqlSession();
}
}
6.7 Mapper 动态代理模式下的 DML 操作
6.7.1 实现添加用户业务
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--添加用户-->
<insert id="insertUsers">
insert into users values(default, #{username},#{usersex})
</insert>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
public interface UsersMapper {
int insertUsers(Users users);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class InsertUsersTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
Users users = new Users();
users.setUsername("AA1");
users.setUsersex("male");
int flag = mapper.insertUsers(users);
System.out.println(flag);
sqlSession.commit();
MybatisUtils.closeSqlSession();
}
}
6.8 主键值回填
6.8.1 获取自增主键值
- 局部配置
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--获取自增主键值-->
<insert id="insertUsersGetKey" useGeneratedKeys="true" keyProperty="userid">
insert into users values (default, #{username}, #{usersex})
</insert>
</mapper>
- 全局配置
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--获取自增主键值-->
<insert id="insertUsersGetKey" keyProperty="userid">
insert into users values (default, #{username}, #{usersex})
</insert>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
public interface UsersMapper {
void insertUsersGetKey(Users users);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class InsertUsersGetKeyTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
Users users = new Users();
users.setUsername("AA2");
users.setUsersex("male");
mapper.insertUsersGetKey(users);
sqlSession.commit();
System.out.println(users.getUserid());
MybatisUtils.closeSqlSession();
}
}
6.8.2 获取非自增主键值
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--获取自增主键值[非自增]-->
<insert id="insertUsersGetKey">
--oracle
<selectKey order="BEFORE" keyProperty="userid" resultType="int">
select seq.nextval from dual
</selectKey>
--Mysql
<selectKey order="AFTER" keyProperty="userid" resultType="int">
-- 获取插入数据的主键的值
-- select LAST_INSERT_ID()
select @@IDENTITY
</selectKey>
insert into users values (userid, #{username}, #{usersex})
</insert>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
public interface UsersMapper {
void insertUsersGetKey(Users users);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class InsertUsersGetKeyTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
Users users = new Users();
users.setUsername("AA3");
users.setUsersex("male");
mapper.insertUsersGetKey(users);
sqlSession.commit();
System.out.println(users.getUserid());
MybatisUtils.closeSqlSession();
}
}
6.9 Mapper 动态代理原理
6.9.1 获取代理对象 MapperProxy
6.9.2 通过 MapperMethod 对象执行对应的操作
7、动态 SQL
7.1 if 标签
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--根据用户给定的条件进行查询-->
<select id="selectUsersByProperty" resultType="users">
select * from users where 1=1
<if test="userid != 0">
and userid = #{userid}
</if>
<if test="username != null and username != ''">
and username = #{username}
</if>
<if test="usersex != null and usersex != ''">
and usersex = #{usersex}
</if>
</select>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
import java.util.List;
public interface UsersMapper {
List<Users> selectUsersByProperty(Users users);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class SelectUsersByPropertyTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
Users users = new Users();
users.setUserid(4);
List<Users> list = mapper.selectUsersByProperty(users);
list.forEach(System.out::println);
MybatisUtils.closeSqlSession();
}
}
7.2 choose、when、otherwise 标签
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--多选一条件-->
<select id="selectUsersByChoose" resultType="users">
select * from users where 1=1
<choose>
<when test="username != null and username != ''">
and username = #{username}
</when>
<when test="usersex != null and usersex != ''">
and usersex = #{usersex}
</when>
<otherwise>
and userid = 1
</otherwise>
</choose>
</select>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
import java.util.List;
public interface UsersMapper {
List<Users> selectUsersByChoose(Users users);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class selectUsersByChooseTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
Users users = new Users();
users.setUsername("wangwu");
List<Users> list = mapper.selectUsersByChoose(users);
list.forEach(System.out::println);
MybatisUtils.closeSqlSession();
}
}
7.3 where 标签
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--根据用户给定的条件进行查询 使用where标签实现-->
<select id="selectUsersByPropertyWhere" resultType="users">
select * from users
<where>
<if test="userid != 0">
and userid = #{userid}
</if>
<if test="username != null and username != ''">
and username = #{username}
</if>
<if test="usersex != null and usersex != ''">
and usersex = #{usersex}
</if>
</where>
</select>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
import java.util.List;
public interface UsersMapper {
List<Users> selectUsersByPropertyWhere(Users users);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class SelectUsersByPropertyWhereTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
Users users = new Users();
users.setUsername("wangwu");
List<Users> list = mapper.selectUsersByPropertyWhere(users);
list.forEach(System.out::println);
MybatisUtils.closeSqlSession();
}
}
7.4 bind 标签
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--根据用户姓名模糊查询-->
<select id="selectUsersByLikeName" resultType="users">
<bind name="likeName" value="'%' + name + '%'"/>
select * from users where username like #{likeName}
-- select * from users where username like concat('%',#{name},'%')
</select>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
import java.util.List;
public interface UsersMapper {
List<Users> selectUsersByLikeName(String name);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class SelectUsersByLikeNameTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
List<Users> list = mapper.selectUsersByLikeName("w");
list.forEach(System.out::println);
MybatisUtils.closeSqlSession();
}
}
7.5 set 标签
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--选择更新-->
<update id="usersUpdate">
update users
<set>
<if test="username != null and username != ''">
username = #{username},
</if>
<if test="usersex != null and usersex != ''">
usersex = #{usersex},
</if>
</set>
where userid = #{userid}
</update>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
public interface UsersMapper {
void usersUpdate(Users users);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class UsersUpdateTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
Users users = new Users();
users.setUsername("zhangsan");
users.setUserid(1);
mapper.usersUpdate(users);
sqlSession.commit();
MybatisUtils.closeSqlSession();
}
}
7.6 foreach 标签
7.6.1 迭代 List、Set
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--查询用户ID为1或者2的用户-->
<select id="selectUsersByIdUseCollection" resultType="users">
select * from users where userid in
<foreach collection="coll" item="userid" open="(" separator="," close=")">
#{userid}
</foreach>
</select>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
import org.apache.ibatis.annotations.Param;
import java.util.Collection;
import java.util.List;
public interface UsersMapper {
List<Users> selectUsersByIdUseCollection(@Param("coll") Collection collection);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
public class SelectUsersByIdUseCollectionTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
// List<Integer> list = new ArrayList<>();
// list.add(1);
// list.add(2);
// List<Users> users = mapper.selectUsersByIdUseCollection(list);
Set<Integer> set = new HashSet<>();
set.add(1);
set.add(2);
List<Users> users = mapper.selectUsersByIdUseCollection(set);
users.forEach(System.out::println);
MybatisUtils.closeSqlSession();
}
}
7.6.2 迭代数组
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--查询用户ID为1或者2的用户 使用数组传递参数-->
<select id="selectUsersByIdUseArray" resultType="users">
select * from users where userid in
<foreach collection="array" item="userid" open="(" separator="," close=")">
#{userid}
</foreach>
</select>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
import java.util.List;
public interface UsersMapper {
List<Users> selectUsersByIdUseArray(int[] arr);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class SelectUsersByIdUseArrayTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
int[] arr = new int[]{1,2};
List<Users> users = mapper.selectUsersByIdUseArray(arr);
users.forEach(System.out::println);
MybatisUtils.closeSqlSession();
}
}
7.6.3 迭代 Map
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--根据给定的条件做计数处理-->
<select id="selectUsersCount" resultType="int">
select count(*) from users where
<foreach collection="AA1" separator="and" item="value" index="key">
${key} = #{value}
</foreach>
</select>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import org.apache.ibatis.annotations.Param;
import java.util.Map;
public interface UsersMapper {
int selectUsersCount(@Param("AA1") Map<String, String> map);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.HashMap;
import java.util.Map;
public class SelectUsersCountTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
Map<String, String> map = new HashMap<>();
map.put("username", "wangwu");
map.put("usersex", "female");
int count = mapper.selectUsersCount(map);
System.out.println(count);
MybatisUtils.closeSqlSession();
}
}
7.6.4 使用 foreach 标签完成批量添加
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--批量添加用户-->
<insert id="insertUsersBatch">
insert into users values
<foreach collection="collection" item="user" separator=",">
(default ,#{user.username},#{user.usersex})
</foreach>
</insert>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
import java.util.List;
public interface UsersMapper {
int insertUsersBatch(List<Users> list);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.ArrayList;
import java.util.List;
public class InsertUsersBatchTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
List<Users> list = new ArrayList<>();
Users users = new Users();
users.setUsername("wangyi1");
users.setUsersex("male");
Users users1 = new Users();
users1.setUsername("wangyi2");
users1.setUsersex("female");
list.add(users);
list.add(users1);
int flag = mapper.insertUsersBatch(list);
System.out.println(flag);
sqlSession.commit();
MybatisUtils.closeSqlSession();
}
}
8、Mybatis 缓存
8.1 一级缓存的使用
8.1.1 一级缓存的生命周期
8.1.2 如何判断两次查询是完全相同的查询
8.1.3 测试一级缓存
- 修改映射配置文件
<?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.mapper.UsersMapper">
<!--根据用户ID查询用户-->
<select id="selectUsersById" resultType="users">
select * from users where userid = #{userid}
</select>
</mapper>
- 修改UsersMapper 接口添加抽象方法
package com.mapper;
import com.pojo.Users;
public interface UsersMapper {
Users selectUsersById(int i);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class SelectUsersByIdCacheOneTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
Users users = mapper.selectUsersById(1);
// 清除缓存
sqlSession.clearCache();
Users users1 = mapper.selectUsersById(1);
System.out.println(users);
System.out.println(users1);
}
}
8.2 二级缓存的使用
8.2.1 二级缓存的配置方式
8.2.2 二级缓存特点
8.2.3 cache 标签的可选属性
8.2.4 测试二级缓存
8.2.4.1 配置 cacheEnabled
8.2.4.2 在映射配置文件中添加
8.2.4.3 JavaBean 对象必须实现序列化接口
8.2.4.4 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class SelectUsersByIdCacheTwoTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
Users users = mapper.selectUsersById(1);
System.out.println(users);
MybatisUtils.closeSqlSession(); // 一级缓存关闭
System.out.println("--------------------------------");
SqlSession sqlSession1 = MybatisUtils.getSqlSession();
UsersMapper mapper1 = sqlSession1.getMapper(UsersMapper.class);
Users users1 = mapper1.selectUsersById(1);
System.out.println(users1); // 从二级缓存读取的数据
}
}
9、Mybatis 的多表关联查询
9.1 搭建环境
9.1.1 创建项目
9.1.2 添加 jar 包
9.1.3 创建实体
9.1.4 创建 properties 文件
9.1.5 添加工具类
9.1.6 创建全局配置文件
9.1.7 创建映射配置文件
10、Mybatis 注解的使用
10.1 使用注解完成查询
10.1.1 查询所有用户
- 修改 UsersMapper 接口
- 创建测试类
10.1.2 注解式开发时的参数传递
10.1.2.1 顺序传参法
10.1.2.2 POJO 传参法
10.1.2.3 Map 传参法
10.2 使用注解完成 DML 操作
10.2.1 实现添加用户操作
- 修改 UsersMapper 接口
package com.mapper;
import com.pojo.Users;
import org.apache.ibatis.annotations.Insert;
public interface UsersMapper {
@Insert("insert into users values(default, #{username}, #{usersex})")
int insertUsers(Users users);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class InsertUsersTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
Users users = new Users();
users.setUsername("yiyi1");
users.setUsersex("male");
int flag = usersMapper.insertUsers(users);
sqlSession.commit();
System.out.println(flag);
}
}
10.2.2 实现更新用户操作
- 修改 UsersMapper 接口
package com.mapper;
import com.pojo.Users;
import org.apache.ibatis.annotations.Update;
public interface UsersMapper {
@Update("update users set username = #{username}, usersex = #{usersex} where userid = #{userid}")
int updateUsers(Users users);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class UpdateUsersTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
Users users = new Users();
users.setUsername("aaa");
users.setUsersex("male");
users.setUserid(1);
int flag = usersMapper.updateUsers(users);
sqlSession.commit();
System.out.println(flag);
}
}
10.2.3 实现删除用户操作
- 修改 UsersMapper 接口
package com.mapper;
import org.apache.ibatis.annotations.Delete;
public interface UsersMapper {
@Delete("delete from users where userid = #{userid}")
int deleteUsersById(int userid);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class DeleteUsersByIdTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
int flag = usersMapper.deleteUsersById(10);
sqlSession.commit();
System.out.println(flag);
}
}
10.3 注解开发中的动态 SQL
10.3.1 脚本 SQL
- 修改 UsersMapper 接口
package com.mapper;
import com.pojo.Users;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface UsersMapper {
// 脚本SQL实现拼接，可读性差，推荐使用映射配置文件
@Select("<script>select * from users where 1=1 <if test=\"username != null and username != ''\"> and username = #{username}</if> <if test=\"usersex != null and usersex != ''\">and usersex = #{usersex}</if></script>")
List<Users> selectUsersByProperty(Users users);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class SelectUsersByPropertyTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
Users users = new Users();
users.setUsername("aaa");
users.setUsersex("male");
List<Users> list = usersMapper.selectUsersByProperty(users);
list.forEach(System.out::println);
}
}
10.3.2 在方法中构建 SQL
- 修改 UsersMapper 接口
package com.mapper;
import com.pojo.Users;
import org.apache.ibatis.annotations.SelectProvider;
import java.util.List;
public interface UsersMapper {
@SelectProvider(type= UsersMapperProvider.class,method = "selectUsersByPropertySQL")
List<Users> selectUsersByPropertyProvider(Users users);
class UsersMapperProvider{
/**
* 动态生成sql
*/
public String selectUsersByPropertySQL(Users users) {
StringBuffer sb = new StringBuffer("select * from users where 1=1");
if (users.getUsername() != null && users.getUsername() != "") {
sb.append(" and username = #{username}");
}
if (users.getUsersex() != null && users.getUsersex() != "") {
sb.append(" and usersex = #{usersex}");
}
return sb.toString();
}
}
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class SelectUsersByPropertyProviderTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
Users users = new Users();
users.setUsername("aaa");
List<Users> list = usersMapper.selectUsersByPropertyProvider(users);
list.forEach(System.out::println);
}
}
10.4 注解开发中的映射处理
10.4.1 映射注解介绍
10.4.1.1 @Results 注解
10.4.1.2 @Result 注解
10.4.1.3 @ResultMap 注解
10.4.2 通过注解实现结果集与对象映射
- 修改 UsersMapper 接口
package com.mapper;
import com.pojo.Users;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.ResultMap;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
public interface UsersMapper {
@Select("select username as name, usersex as sex from users where userid = #{userid}")
@Results(id = "usersMapper", value = {
@Result(id = true, property = "userid", column = "id"),
@Result(property = "username", column = "name"),
@Result(property = "usersex", column = "sex")
})
Users selectUsersByIdMapper(int userid);
@Select("select username as name, usersex as sex from users where userid = #{userid}")
@ResultMap(value = {"usersMapper"}) // 复用@Results的注解内容
Users selectUsersByIdMapper2(int userid);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class SelectUsersByPropertyProviderTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
Users users = usersMapper.selectUsersByIdMapper(1);
System.out.println(users);
}
}
10.5 注解开发中的多表关联查询
10.5.1 一对一的关联查询
- 创建 Roles 实体
package com.pojo;
public class Roles {
private int roleid;
private String rolename;
public int getRoleid() {
return roleid;
}
public void setRoleid(int roleid) {
this.roleid = roleid;
}
public String getRolename() {
return rolename;
}
public void setRolename(String rolename) {
this.rolename = rolename;
}
@Override
public String toString() {
return "Roles{" +
"roleid=" + roleid +
", rolename='" + rolename + '\'' +
'}';
}
}
- 修改 Users 实体
package com.pojo;
import java.io.Serializable;
public class Users implements Serializable {
private int userid;
private String username;
private String usersex;
private Roles roles;
public Roles getRoles() {
return roles;
}
public void setRoles(Roles roles) {
this.roles = roles;
}
public int getUserid() {
return userid;
}
public void setUserid(int userid) {
this.userid = userid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getUsersex() {
return usersex;
}
public void setUsersex(String usersex) {
this.usersex = usersex;
}
@Override
public String toString() {
return "Users{" +
"userid='" + userid + '\'' +
", username='" + username + '\'' +
", usersex='" + usersex + '\'' +
'}';
}
}
- 创建 RolesMapper 接口
package com.mapper;
import com.pojo.Roles;
import org.apache.ibatis.annotations.Select;
public interface RolesMapper {
@Select("select * from roles where user_id = #{userid}")
Roles selectRolesByUserId(int userid);
}
- 修改 UsersMapper 接口
package com.mapper;
import com.pojo.Users;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.mapping.FetchType;
public interface UsersMapper {
@Select("select * from users where userid = #{userid}")
@Results(id = "usersAndRolesMapper", value = {
@Result(id = true, property = "userid", column = "userid"),
@Result(property = "username", column = "username"),
@Result(property = "usersex", column = "usersex"),
@Result(property = "roles", column = "userid", one = @One(select = "com.mapper.RolesMapper.selectRolesByUserId", fetchType = FetchType.LAZY))
})
Users selectUsersAndRolesByUserId(int userid);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Roles;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
public class SelectUsersAndRolesByUserIdTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
Users users = usersMapper.selectUsersAndRolesByUserId(1);
System.out.println(users);
Roles roles = users.getRoles();
System.out.println(roles);
}
}
10.5.2 一对多的关联查询
- 创建 Orders 实体
package com.pojo;
public class Orders {
private int orderid;
private double orderprice;
public int getOrderid() {
return orderid;
}
public void setOrderid(int orderid) {
this.orderid = orderid;
}
public double getOrderprice() {
return orderprice;
}
public void setOrderprice(double orderprice) {
this.orderprice = orderprice;
}
@Override
public String toString() {
return "Orders{" +
"orderid=" + orderid +
", orderprice=" + orderprice +
'}';
}
}
- 修改 Users 实体
package com.pojo;
import java.io.Serializable;
import java.util.List;
public class Users implements Serializable {
private int userid;
private String username;
private String usersex;
private List<Orders> orders;
public List<Orders> getOrders() {
return orders;
}
public void setOrders(List<Orders> orders) {
this.orders = orders;
}
public int getUserid() {
return userid;
}
public void setUserid(int userid) {
this.userid = userid;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getUsersex() {
return usersex;
}
public void setUsersex(String usersex) {
this.usersex = usersex;
}
@Override
public String toString() {
return "Users{" +
"userid='" + userid + '\'' +
", username='" + username + '\'' +
", usersex='" + usersex + '\'' +
'}';
}
}
- 创建 OrdersMapper 接口
package com.mapper;
import com.pojo.Orders;
import org.apache.ibatis.annotations.Select;
import java.util.List;
public interface OrdersMapper {
@Select("select * from orders where user_id = #{userid}")
List<Orders> selectOrdersByUserid(int userid);
}
- 修改 UsersMapper 接口
package com.mapper;
import com.pojo.Users;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.mapping.FetchType;
public interface UsersMapper {
@Select("select * from users where userid = #{userid}")
@Results(id = "usersAndOrdersMapper", value = {
@Result(id = true, property = "userid", column = "userid"),
@Result(property = "username", column = "username"),
@Result(property = "usersex", column = "usersex"),
@Result(property = "orders", column = "userid", many = @Many(select = "com.mapper.OrdersMapper.selectOrdersByUserid", fetchType = FetchType.LAZY))
})
Users selectUsersAndOrdersByUserId(int userid);
}
- 创建测试类
package com.test;
import com.mapper.UsersMapper;
import com.pojo.Orders;
import com.pojo.Roles;
import com.pojo.Users;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import java.util.List;
public class SelectUsersAndOrdersByUserIdTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
Users users = usersMapper.selectUsersAndOrdersByUserId(1);
System.out.println(users);
List<Orders> list = users.getOrders();
list.forEach(System.out::println);
}
}
10.6 注解开发与映射配置文件的对比
11、Mybatis Generator 工具的使用
11.1 MyBatis Generator 工具的使用步骤
11.1.1 在 Idea 中打开 MybatisGenerator 项目
11.1.2 修改配置文件修改配置文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
"http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">
<generatorConfiguration>
<context id="testTables" targetRuntime="MyBatis3">
<commentGenerator>
<!-- 是否去除自动生成的注释 true：是 ： false:否 -->
<property name="suppressAllComments" value="true" />
</commentGenerator>
<!--数据库连接的信息：驱动类、连接地址、用户名、密码 -->
<jdbcConnection driverClass="com.mysql.jdbc.Driver"
connectionURL="jdbc:mysql://localhost:3306/bjsxt"
userId="root"
password="root">
</jdbcConnection>
<!-- 默认 false，把 JDBC DECIMAL 和 NUMERIC 类型解析为 Integer，为
true 时把 JDBC DECIMAL 和 NUMERIC 类型解析为 java.math.BigDecimal -->
<javaTypeResolver>
<property name="forceBigDecimals" value="false" />
</javaTypeResolver>
<!-- targetProject:生成 POJO 类的位置 -->
<javaModelGenerator targetPackage="com.bjsxt.pojo" targetProject=".\src">
<!-- enableSubPackages:是否让 schema 作为包的后缀 -->
<property name="enableSubPackages" value="false" />
<!-- 从数据库返回的值被清理前后的空格 -->
<property name="trimStrings" value="true" />
</javaModelGenerator>
<!-- targetProject:mapper 映射文件生成的位置 -->
<sqlMapGenerator targetPackage="com.bjsxt.mapper" targetProject=".\src">
<!-- enableSubPackages:是否让 schema 作为包的后缀 -->
<property name="enableSubPackages" value="false" />
</sqlMapGenerator>
<!-- targetPackage：mapper 接口生成的位置 -->
<javaClientGenerator type="XMLMAPPER" targetPackage="com.bjsxt.mapper" targetProject=".\src">
<!-- enableSubPackages:是否让 schema 作为包的后缀 -->
<property name="enableSubPackages" value="false" />
</javaClientGenerator>
<!-- 指定数据库表 -->
<table schema="" tableName="users"></table>
</context>
</generatorConfiguration>
11.1.3 运行主方法生成 POJO、接口与映射配置文件
public class GeneratorSqlmap {
public void generator() throws Exception{
List<String> warnings = new ArrayList<String>();
boolean overwrite = true;
//指定 逆向工程配置文件
File configFile = new File("generatorConfig.xml");
ConfigurationParser cp = new ConfigurationParser(warnings);
Configuration config = cp.parseConfiguration(configFile);
DefaultShellCallback callback = new
DefaultShellCallback(overwrite);
MyBatisGenerator myBatisGenerator = new
MyBatisGenerator(config,
callback, warnings);
myBatisGenerator.generate(null);
}
public static void main(String[] args) throws Exception {
try {
GeneratorSqlmap generatorSqlmap = new GeneratorSqlmap();
generatorSqlmap.generator();
} catch (Exception e) {
e.printStackTrace();
}
}
}
11.2 生成代码的结构介绍
- 模型类
Users 实体类。 - Example 类
UsersExample 类封装了针对于 Users 实体类中所用属性的条件定义。它的作用是通过基于面向对象语法结构来给定操作数据库的条件。 - UsersMapper 接口
定义了对数据库 CRUD 操作的抽象方法。 - 映射配置文件
UsersMapper 接口的映射配置文件。
11.3 生成代码的使用
11.3.1 查询操作
- 根据主键查询用户
public class SelectUsersByIdTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
Users users = usersMapper.selectByPrimaryKey(1);
System.out.println(users);
}
}
- 多条件查询 and
/**
* 根据用户姓名与性别查询用户
*/
public class SelectUsersByNameAndSexTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
UsersExample usersExample = new UsersExample();
UsersExample.Criteria criteria = usersExample.createCriteria();
criteria.andUsernameEqualTo("bjsxt");
criteria.andUsersexEqualTo("male");
List<Users> list = usersMapper.selectByExample(usersExample);
list.forEach(System.out::println);
}
}
- 多条件查询 or
/**
* 根据用户姓名或者用户性别查询
*/
public class SelectUsersByNameOrSexTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
UsersExample usersExample = new UsersExample();
UsersExample.Criteria criteria = usersExample.createCriteria();
criteria.andUsernameEqualTo("bjsxt");
UsersExample.Criteria criteria1 = usersExample.createCriteria();
criteria1.andUsersexEqualTo("male");
usersExample.or(criteria1);
List<Users> list = usersMapper.selectByExample(usersExample);
list.forEach(System.out::println);
}
}
11.3.2 DML 操作
- 添加用户
/**
* 添加用户
*/
public class InsertUsersTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper usersMapper =
sqlSession.getMapper(UsersMapper.class);
Users users = new Users();
users.setUsername("kevin");
users.setUsersex("male");
usersMapper.insertSelective(users);
sqlSession.commit();
}
}
- 更新用户
/**
* 更新用户
*/
public class UpdateUsersTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper usersMapper =
sqlSession.getMapper(UsersMapper.class);
Users users = new Users();
users.setUsername("zhangsanfeng");
users.setUsersex("male");
users.setUserid(25);
usersMapper.updateByPrimaryKey(users);
sqlSession.commit();
}
}
- 删除用户
/**
* 根据用户 ID 删除用户
*/
public class DeleteUsersByIdTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper usersMapper =
sqlSession.getMapper(UsersMapper.class);
usersMapper.deleteByPrimaryKey(25);
sqlSession.commit();
}
}
12、PageHelper 分页插件
12.1 PageHelper 使用步骤
12.1.1 添加 jar 包
12.1.2 配置插件
- 在 Mybatis 的全局配置文件中配置该插件
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<!-- 设置数据库类型 Oracle,Mysql,MariaDB,SQLite,Hsqldb,PostgreSQL 六种数据库-->
<property name="helperDialect" value="mysql"/>
</plugin>
</plugins>
12.1.3 配置插件### 12.1.2 配置插件
12.2 PageHelper 使用方式
/**
* PageHelper分页测试
*/
public class PageHelperTest {
public static void main(String[] args) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
UsersExample usersExample = new UsersExample();
PageHelper.startPage(1, 2);
List<Users> list = usersMapper.selectByExample(usersExample);
PageInfo<Users> pageInfo = new PageInfo<>(list);
// 获取结果集
List<Users> result = pageInfo.getList();
// 获取总条数
System.out.println("总条数：" + pageInfo.getTotal());
// 获取总页数
System.out.println("总页数：" + pageInfo.getPages());
//获取当前页
System.out.println("当前页：" + pageInfo.getPageNum());
// 获取每页显示的条数
System.out.println("每页条数：" + pageInfo.getSize());
}
}
13、Mybatis 与 Servlet 整合
13.1 搭建环境
13.1.1 创建项目
13.1.2 添加 jar 包
13.1.3 添加配置文件
13.1.4 生成 POJO、接口、映射配置文件
13.2 OpenSessionInView 的使用
13.2.1 创建 OpenSqlSessionInViewFilter
package com.web.filter;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
import javax.servlet.*;
import javax.servlet.annotation.WebFilter;
import java.io.IOException;
/**
* 在Filter中打开一个SqlSession
*/
@WebFilter("/*")
public class OpenSqlSessionInViewFilter implements Filter {
@Override
public void init(FilterConfig filterConfig) throws ServletException {
Filter.super.init(filterConfig);
}
@Override
public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
SqlSession sqlSession = MybatisUtils.getSqlSession();
try {
filterChain.doFilter(servletRequest,servletResponse);
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
sqlSession.rollback();
} finally {
MybatisUtils.closeSqlSession();
}
}
@Override
public void destroy() {
Filter.super.destroy();
}
}
13.3 完成业务操作
13.3.1 添加用户
- 修改 index.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>$Title$</title>
</head>
<body>
<a href="addUsers.jsp"></a>
</body>
</html>
- 创建 addUsers.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>添加用户</title>
</head>
<body>
<form action="userServlet.do" method="post">
<input type="hidden" name="flag" value="addUsers">
用户姓名：<input type="text" name="username"> <br>
用户性别：<input type="text" name="usersex"> <br>
<input type="submit" value="ok">
</form>
</body>
</html>
- 创建 UsersServlet
package com.web.servlet;
import com.pojo.Users;
import com.service.UsersService;
import com.service.impl.UsersServiceImpl;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet("/usersServlet.do")
public class UsersServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
this.doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String flag = req.getParameter("flag");
if ("addUsers".equals(flag)) {
this.addUsers(req, resp);
}
}
/**
* 处理添加用户请求
*
*/
private void addUsers(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
Users users = this.createUsers(req);
UsersService usersService = new UsersServiceImpl();
usersService.addUsers(users);
resp.sendRedirect("ok.jsp");
}
/**
* 获取提交数据
*/
private Users createUsers (HttpServletRequest req) {
String username = req.getParameter("username");
String usersex = req.getParameter("usersex");
Users users = new Users();
users.setUsername(username);
users.setUsersex(usersex);
return users;
}
}
- 创建业务层
package com.service;
import com.pojo.Users;
public interface UsersService {
void addUsers(Users users);
}
package com.service.impl;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.service.UsersService;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
/**
* 操作用户业务层
*/
public class UsersServiceImpl implements UsersService {
@Override
public void addUsers(Users users) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
usersMapper.insertSelective(users);
}
}
- 创建成功页面
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
操作成功，请返回！
</body>
</html>
13.3.2 查询用户
- 修改 index.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>$Title$</title>
</head>
<body>
<a href="addUsers.jsp">添加用户</a> <a href="findUsers.jsp">查询用户</a>
</body>
</html>
- 创建 findUsers.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<head>
<title>Title</title>
</head>
<body>
<form action="userServlet.do" method="post">
<input type="hidden" name="flag" value="findUsers">
用户姓名：<input type="text" name="username"> <br>
用户性别：<input type="text" name="usersex"> <br>
<input type="submit" value="ok">
</form>
</body>
</html>
- 修改 UsersServlet
package com.web.servlet;
import com.github.pagehelper.PageInfo;
import com.pojo.Users;
import com.service.UsersService;
import com.service.impl.UsersServiceImpl;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
@WebServlet("/usersServlet.do")
public class UsersServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
this.doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
String flag = req.getParameter("flag");
if ("addUsers".equals(flag)) {
this.addUsers(req, resp);
} else if ("findUsers".equals(flag)) {
this.findUsers(req, resp);
}
}
/**
* 处理查询用户请求
* @param req
* @param resp
* @throws ServletException
* @throws IOException
*/
private void findUsers(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
Users users = this.createUsers(req);
String pageIndex = req.getParameter("pageIndex");
int page = 1;
if (pageIndex != null && pageIndex.length() > 0) {
page = Integer.parseInt(pageIndex);
}
UsersService usersService = new UsersServiceImpl();
PageInfo<Users> pageInfo = usersService.findUsers(page, users);
req.setAttribute("pageInfo", pageInfo);
req.setAttribute("users", users);
req.getRequestDispatcher("showUsers.jsp").forward(req, resp);
}
/**
* 处理添加用户请求
*
*/
private void addUsers(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
Users users = this.createUsers(req);
UsersService usersService = new UsersServiceImpl();
usersService.addUsers(users);
resp.sendRedirect("ok.jsp");
}
/**
* 获取提交数据
*/
private Users createUsers (HttpServletRequest req) {
String username = req.getParameter("username");
String usersex = req.getParameter("usersex");
Users users = new Users();
users.setUsername(username);
users.setUsersex(usersex);
return users;
}
}
- 修改业务层
package com.service;
import com.github.pagehelper.PageInfo;
import com.pojo.Users;
public interface UsersService {
void addUsers(Users users);
PageInfo<Users> findUsers (int page, Users users);
}
package com.service.impl;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.mapper.UsersMapper;
import com.pojo.Users;
import com.service.UsersService;
import com.utils.MybatisUtils;
import org.apache.ibatis.session.SqlSession;
/**
* 操作用户业务层
*/
public class UsersServiceImpl implements UsersService {
@Override
public void addUsers(Users users) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
usersMapper.insertSelective(users);
}
/**
* 查询用户
* @param page
* @param users
* @return
*/
@Override
public PageInfo<Users> findUsers(int page, Users users) {
SqlSession sqlSession = MybatisUtils.getSqlSession();
UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
UsersExample usersExample = this.createUsersExample(users);
PageHelper.startPage(page, 2);
List<Users> list = usersMapper.selectByExample(usersExample);
PageInfo<Users> pageInfo = new PageInfo<>(list);
return pageInfo;
}
/**
* 生成查询条件
*/
private UsersExample createUsersExample (Users users) {
UsersExample usersExample = new UsersExample();
UsersExample.Criteria criteria = usersExample.createCriteria();
if (users.getUsername() != null && users.getUsername().length() > 0) {
criteria.andUserNameEqualTo(users.getUsername());
}
if (users.getUsersex() != null && users.getUsersex().length() > 0) {
criteria.andUserSexEqualTo(users.getUserSex());
}
return usersExample;
}
}
13.3.3 在显示查询结果页面中实现分页功能
- 添加 JSTL 标签库的 tld 文件
- 添加 taglib 指令标签
- 创显示查询结果页面
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<html>
<head>
<title>显示查询结果</title>
<script>
function subForm(pageIndex){
document.getElementById("pageIndex").value=pageIndex;
document.forms[0].submit();
}
</script>
</head>
<body>
<form action="usersServlet.do" method="post">
<input type="hidden" name="flag" value="findUsers"/>
<input type="hidden" name="pageIndex" id="pageIndex"/>
<input type="hidden" name="username"
value="${users.username}"/>
<input type="hidden" name="usersex"
value="${users.usersex}"/>
</form>
<table align="center" border="1" width="40%">
<tr>
<th>用户 ID</th>
<th>用户姓名</th>
<th>用户性别</th>
</tr>
<c:forEach items="${pageInfo.list}" var="users">
<tr>
<td>${users.userid}</td>
<td>${users.username}</td>
<td>${users.usersex}</td>
</tr>
</c:forEach>
<tr>
<td colspan="3" align="center">
<c:if test="${pageInfo.pageNum > 1}">
<a href="#" onclick="subForm(${pageInfo.pageNum - 1})">上一页</a>
</c:if>
<c:forEach begin="1" end="${pageInfo.pages}"
varStatus="st">
<c:choose>
<c:when test="${pageInfo.pageNum eq st.count}">
<a style="color: red" href="#"
onclick="subForm(${st.count})">${st.count}</a>
</c:when>
<c:otherwise>
<a href="#"
onclick="subForm(${st.count})">${st.count}</a>
</c:otherwise>
</c:choose>
</c:forEach>
<c:if test="${pageInfo.pageNum < pageInfo.pages}">
<a href="#" onclick="subForm(${pageInfo.pageNum + 1})">下一页</a>
</c:if>
</td>
</tr>
</table>
</body>
</html>
扫一扫
1042
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
