什么是Mybatis
是一个款持久层框架，自定义SQL，封装了JDBC,存储过程和高级映射关系，免除了jdbc的设置和获取结果集。
特点：轻量级，性能好,运行快 ，SQL和Java代码分开，Java代码注重于业务，SQL注重于数据
Mybatis3数据库的入门
1.准备SQL数据
CREATE DATABASE `mybatis`;
USE `mybatis`;
CREATE TABLE `t_emp`(
emp_id INT AUTO_INCREMENT,
emp_name CHAR(100),
emp_salary DOUBLE(10,5),
PRIMARY KEY(emp_id)
);
INSERT INTO `t_emp`(emp_name,emp_salary) VALUES("lee",200);
INSERT INTO `t_emp`(emp_name,emp_salary) VALUES("kebi",666);
INSERT INTO `t_emp`(emp_name,emp_salary) VALUES("zms",777);2.项目搭建 导入依赖
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.shunfen</groupId>
<artifactId>mybatis_bast01</artifactId>
<version>1.0-SNAPSHOT</version>
<dependencies>
<!-- mybatis依赖 -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.11</version>
</dependency>
<!-- MySQL驱动 mybatis底层依赖jdbc驱动实现,本次不需要导入连接池,mybatis自带! -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
</dependency>
<!--junit5测试-->
<dependency>
<groupId>org.junit.jupiter</groupId>
<artifactId>junit-jupiter-api</artifactId>
<version>5.3.1</version>
</dependency>
</dependencies>
</project>3.创建实体类
package com.shunfen.domain;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Employee {
private Integer empId;
private String emp_name;
private Double emp_salary;
}
4.定义mapper接口
package com.shunfen.mapper;
import com.shunfen.domain.Employee;
public interface EmployeeMapper {
/**
* 根据员工id查找
* @param empId
* @return
*/
Employee selectEmp(Integer empId);
}
5.创建employeeMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.shunfen.mapper.EmployeeMapper">
<select id="selectEmp" resultType="com.shunfen.domain.Employee">
SELECT emp_id empId,emp_name empName,emp_salary empSalary
FROM t_emp WHERE emp_id=#{empId};
</select>
</mapper>6.创建mabtis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- environments表示配置Mybatis的开发环境，可以配置多个环境，在众多具体环境中，使用default属性指定实际运行时使用的环境。default属性的取值是environment标签的id属性的值。 -->
<environments default="development">
<!-- environment表示配置Mybatis的一个具体的环境 -->
<environment id="development">
<!-- Mybatis的内置的事务管理器 -->
<transactionManager type="JDBC"/>
<!-- 配置数据源 -->
<dataSource type="POOLED">
<!-- 建立数据库连接的具体信息 -->
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis-example"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<mappers>
<!-- Mapper注册：指定Mybatis映射文件的具体位置 -->
<!-- mapper标签：配置一个具体的Mapper映射文件 -->
<!-- resource属性：指定Mapper映射文件的实际存储位置，这里需要使用一个以类路径根目录为基准的相对路径 -->
<!-- 对Maven工程的目录结构来说，resources目录下的内容会直接放入类路径，所以这里我们可以以resources目录为基准 -->
<mapper resource="mapper/EmployeeMapper.xml"/>
</mappers>
</configuration>5.运行测试
package com.shunfen;
import com.shunfen.domain.Employee;
import com.shunfen.mapper.EmployeeMapper;
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 org.junit.jupiter.api.Test;
import java.io.IOException;
import java.io.InputStream;
public class MybatisTest {
@Test
public void mybatisTestOne() throws IOException {
// 1.声明mybatis
String mybatis = "mybatis-config.xml";
// 2.输入流的模式加载配置
InputStream resourceAsStream = Resources.getResourceAsStream(mybatis);
// 3.读取mybatis配置文件
SqlSessionFactory build =
new SqlSessionFactoryBuilder().build(resourceAsStream);
// 4.开启一个会话
SqlSession sqlSession = build.openSession();
// 5.获取mapper类对象
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
// 6.查询一条记录
Employee employee = mapper.selectEmp(1);
System.out.println(employee);
// 提交事务
sqlSession.commit();
// 关闭会话
sqlSession.close();
}
}
采用日志记录
1.依赖导入日志
<dependency>
<groupId>ch.qos.logback</groupId>
<artifactId>logback-classic</artifactId>
<version>1.4.14</version>
</dependency>2.代码测试日志级别
package com.shunfen;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
public class LogbackTest {
private Logger logger = LoggerFactory.getLogger(LogbackTest.class);
@Test
public void logback(){
logger.trace("trace ...");
logger.info("info....");
logger.debug("debug...");
logger.error("error....");
}
}
3.引入配置
<?xml version="1.0" encoding="UTF-8"?>
<configuration debug="true">
<!-- 指定日志输出的位置，ConsoleAppender表示输出到控制台 -->
<appender name="STDOUT"
class="ch.qos.logback.core.ConsoleAppender">
<encoder>
<!-- 日志输出的格式 -->
<!-- 按照顺序分别是：时间、日志级别、线程名称、打印日志的类、日志主体内容、换行 -->
<pattern>[%d{HH:mm:ss.SSS}] [%-5level] [%thread] [%logger] [%msg]%n</pattern>
<charset>UTF-8</charset>
</encoder>
</appender>
<!-- 设置全局日志级别。日志级别按顺序分别是：TRACE、DEBUG、INFO、WARN、ERROR -->
<!-- 指定任何一个日志级别都只打印当前级别和后面级别的日志。 -->
<root level="DEBUG">
<!-- 指定打印日志的appender，这里通过“STDOUT”引用了前面配置的appender -->
<appender-ref ref="STDOUT" />
</root>
<!-- 根据特殊需求指定局部日志级别，可也是包名或全类名。 -->
<logger name="com.shunfen" level="DEBUG" />
</configuration>实体类类型参数
/**
* 添加一条数据
*/
int insertEmp(Employee employee); <insert id="insertEmp">
INSERT INTO t_emp (emp_Name,emp_salary) VALUES(#{empName},#{empSalary});
</insert>修改
int updateEmp(@Param("empId") Integer empId,@Param("empSalary") String empSalary); <update id="updateEmp">
UPDATE t_emp SET emp_salary=#{empSalary} WHERE emp_id=#{empId};
</update>改造
package com.shunfen;
import com.shunfen.domain.Employee;
import com.shunfen.mapper.EmployeeMapper;
import lombok.extern.log4j.Log4j;
import lombok.extern.log4j.Log4j2;
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 org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;
public class MybatisTest {
private Logger logger = LoggerFactory.getLogger(MybatisTest.class);
private SqlSession sqlSession;
@BeforeEach
public void BeMode() throws IOException {
sqlSession =
new SqlSessionFactoryBuilder().
build(Resources.getResourceAsStream("mybatis-config.xml"))
.openSession();
}
@Test
public void testMapperEmp(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Map<String, Object> map = new HashMap<String, Object>();
map.put("empSalesKey",999.99);
map.put("empIdKey",3);
int updateEmpMap = mapper.updateEmpMap(map);
System.out.println(updateEmpMap);
logger.info("updateEmpMap"+updateEmpMap);
}
@AfterEach
public void ArMode(){
sqlSession.commit();
// 关闭会话
sqlSession.close();
}
// @Test
// public void mybatisTestOne() throws IOException {
// // 1.声明mybatis
// String mybatis = "mybatis-config.xml";
//
// // 2.输入流的模式加载配置
// InputStream resourceAsStream = Resources.getResourceAsStream(mybatis);
//
// // 3.读取mybatis配置文件
// SqlSessionFactory build =
// new SqlSessionFactoryBuilder().build(resourceAsStream);
//
// // 4.开启一个会话
// SqlSession sqlSession = build.openSession();
//
// // 5.获取mapper类对象
// EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
//
// // 6.查询一条记录
// Employee employee = mapper.selectEmp(1);
// System.out.println(employee);
//
// // 提交事务
// sqlSession.commit();
// // 关闭会话
// sqlSession.close();
//
// }
}
单个简单类型
package com.shunfen.mapper;
import com.shunfen.domain.Employee;
import org.apache.ibatis.annotations.Param;
import java.util.Map;
import java.util.Objects;
public interface EmployeeMapper {
/**
* 根据员工id查找
* @param empId
* @return
*/
Employee selectEmp(Integer empId);
/**
* 添加一条数据
*/
int insertEmp(Employee employee);
int updateEmp(@Param("empId") Integer empId,@Param("empSalary") String empSalary);
int updateEmpMap(Map<String, Object> paramMap) ;
int selectCont();
}
mapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.shunfen.mapper.EmployeeMapper">
<insert id="insertEmp">
INSERT INTO t_emp (emp_Name,emp_salary) VALUES(#{empName},#{empSalary});
</insert>
<update id="updateEmp">
UPDATE t_emp SET emp_salary=#{empSalary} WHERE emp_id=#{empId};
</update>
<update id="updateEmpMap">
UPDATE t_emp SET emp_salary=#{empSalesKey} WHERE emp_id=#{empIdKey};
</update>
<select id="selectEmp" resultType="com.shunfen.domain.Employee">
SELECT emp_id empId,emp_name empName,emp_salary empSalary
FROM t_emp WHERE emp_id=#{empId};
</select>
<select id="selectCont" resultType="java.lang.Integer">
select count(*) from t_emp;
</select>
</mapper>单元测试
package com.shunfen;
import com.shunfen.domain.Employee;
import com.shunfen.mapper.EmployeeMapper;
import lombok.extern.log4j.Log4j;
import lombok.extern.log4j.Log4j2;
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 org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;
public class MybatisTest {
private Logger logger = LoggerFactory.getLogger(MybatisTest.class);
private SqlSession sqlSession;
@BeforeEach
public void BeMode() throws IOException {
sqlSession =
new SqlSessionFactoryBuilder().
build(Resources.getResourceAsStream("mybatis-config.xml"))
.openSession();
}
@Test
public void testMapperEmp(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Map<String, Object> map = new HashMap<String, Object>();
map.put("empSalesKey",999.99);
map.put("empIdKey",3);
int updateEmpMap = mapper.updateEmpMap(map);
System.out.println(updateEmpMap);
logger.info("updateEmpMap"+updateEmpMap);
}
@Test
public void testOne(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
int i = mapper.selectCont();
System.out.println(i);
logger.info("i"+i);
}
@AfterEach
public void ArMode(){
sqlSession.commit();
// 关闭会话
sqlSession.close();
}
// @Test
// public void mybatisTestOne() throws IOException {
// // 1.声明mybatis
// String mybatis = "mybatis-config.xml";
//
// // 2.输入流的模式加载配置
// InputStream resourceAsStream = Resources.getResourceAsStream(mybatis);
//
// // 3.读取mybatis配置文件
// SqlSessionFactory build =
// new SqlSessionFactoryBuilder().build(resourceAsStream);
//
// // 4.开启一个会话
// SqlSession sqlSession = build.openSession();
//
// // 5.获取mapper类对象
// EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
//
// // 6.查询一条记录
// Employee employee = mapper.selectEmp(1);
// System.out.println(employee);
//
// // 提交事务
// sqlSession.commit();
// // 关闭会话
// sqlSession.close();
//
// }
}
返回实体类对象
Employee selectEmployee(Integer empId); <select id="selectEmployee" resultType="com.shunfen.domain.Employee">
SELECT emp_id empId,emp_name empName,emp_salary empSalary FROM t_emp WHERE emp_id=#{maomi};
</select>返回Map类型
package com.shunfen.mapper;
import com.shunfen.domain.Employee;
import org.apache.ibatis.annotations.Param;
import java.util.Map;
import java.util.Objects;
public interface EmployeeMapper {
/**
* 根据员工id查找
* @param empId
* @return
*/
Employee selectEmp(Integer empId);
/**
* 添加一条数据
*/
int insertEmp(Employee employee);
int updateEmp(@Param("empId") Integer empId,@Param("empSalary") String empSalary);
int updateEmpMap(Map<String, Object> paramMap) ;
int selectCont();
Employee selectEmployee(Integer empId);
Map<String,Object> selectNameAgeMax();
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.shunfen.mapper.EmployeeMapper">
<insert id="insertEmp">
INSERT INTO t_emp (emp_Name,emp_salary) VALUES(#{empName},#{empSalary});
</insert>
<update id="updateEmp">
UPDATE t_emp SET emp_salary=#{empSalary} WHERE emp_id=#{empId};
</update>
<update id="updateEmpMap">
UPDATE t_emp SET emp_salary=#{empSalesKey} WHERE emp_id=#{empIdKey};
</update>
<select id="selectEmp" resultType="com.shunfen.domain.Employee">
SELECT emp_id empId,emp_name empName,emp_salary empSalary
FROM t_emp WHERE emp_id=#{empId};
</select>
<select id="selectCont" resultType="java.lang.Integer">
select count(*) from t_emp;
</select>
<select id="selectEmployee" resultType="com.shunfen.domain.Employee">
SELECT emp_id empId,emp_name empName,emp_salary empSalary FROM t_emp WHERE emp_id=#{maomi};
</select>
<select id="selectNameAgeMax" resultType="java.util.Map">
SELECT emp_id empId,emp_salary empSalary,(
SELECT AVG(emp_salary) FROM t_emp) FROM t_emp WHERE emp_salary=(
SELECT MAX(emp_salary) FROM t_emp);
</select>
<!-- 在全局范围内对Mybatis进行配置 -->
</mapper>package com.shunfen;
import com.shunfen.domain.Employee;
import com.shunfen.mapper.EmployeeMapper;
import lombok.extern.log4j.Log4j;
import lombok.extern.log4j.Log4j2;
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 org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;
public class MybatisTest {
private Logger logger = LoggerFactory.getLogger(MybatisTest.class);
private SqlSession sqlSession;
@BeforeEach
public void BeMode() throws IOException {
sqlSession =
new SqlSessionFactoryBuilder().
build(Resources.getResourceAsStream("mybatis-config.xml"))
.openSession();
}
@Test
public void testMapperEmp(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Map<String, Object> map = new HashMap<String, Object>();
map.put("empSalesKey",999.99);
map.put("empIdKey",3);
int updateEmpMap = mapper.updateEmpMap(map);
System.out.println(updateEmpMap);
logger.info("updateEmpMap"+updateEmpMap);
}
@Test
public void testOne(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
int i = mapper.selectCont();
System.out.println(i);
logger.info("i"+i);
}
@Test
public void selectAvgNameMax(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Map<String, Object> map = mapper.selectNameAgeMax();
Set<Map.Entry<String, Object>> entries = map.entrySet();
for (Map.Entry<String, Object> entry : entries) {
String key = entry.getKey();
Object value = entry.getValue();
logger.info(key+"log"+value);
}
System.out.println(map);
}
@AfterEach
public void ArMode(){
sqlSession.commit();
// 关闭会话
sqlSession.close();
}
// @Test
// public void mybatisTestOne() throws IOException {
// // 1.声明mybatis
// String mybatis = "mybatis-config.xml";
//
// // 2.输入流的模式加载配置
// InputStream resourceAsStream = Resources.getResourceAsStream(mybatis);
//
// // 3.读取mybatis配置文件
// SqlSessionFactory build =
// new SqlSessionFactoryBuilder().build(resourceAsStream);
//
// // 4.开启一个会话
// SqlSession sqlSession = build.openSession();
//
// // 5.获取mapper类对象
// EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
//
// // 6.查询一条记录
// Employee employee = mapper.selectEmp(1);
// System.out.println(employee);
//
// // 提交事务
// sqlSession.commit();
// // 关闭会话
// sqlSession.close();
//
// }
}
返回List类型
package com.shunfen.mapper;
import com.shunfen.domain.Employee;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
import java.util.Objects;
public interface EmployeeMapper {
/**
* 根据员工id查找
* @param empId
* @return
*/
Employee selectEmp(Integer empId);
/**
* 添加一条数据
*/
int insertEmp(Employee employee);
int updateEmp(@Param("empId") Integer empId,@Param("empSalary") String empSalary);
int updateEmpMap(Map<String, Object> paramMap) ;
int selectCont();
Employee selectEmployee(Integer empId);
Map<String,Object> selectNameAgeMax();
List<Employee> selectAll();
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.shunfen.mapper.EmployeeMapper">
<insert id="insertEmp">
INSERT INTO t_emp (emp_Name,emp_salary) VALUES(#{empName},#{empSalary});
</insert>
<update id="updateEmp">
UPDATE t_emp SET emp_salary=#{empSalary} WHERE emp_id=#{empId};
</update>
<update id="updateEmpMap">
UPDATE t_emp SET emp_salary=#{empSalesKey} WHERE emp_id=#{empIdKey};
</update>
<select id="selectEmp" resultType="com.shunfen.domain.Employee">
SELECT emp_id empId,emp_name empName,emp_salary empSalary
FROM t_emp WHERE emp_id=#{empId};
</select>
<select id="selectCont" resultType="java.lang.Integer">
select count(*) from t_emp;
</select>
<select id="selectEmployee" resultType="com.shunfen.domain.Employee">
SELECT emp_id empId,emp_name empName,emp_salary empSalary FROM t_emp WHERE emp_id=#{maomi};
</select>
<select id="selectNameAgeMax" resultType="java.util.Map">
SELECT emp_id empId,emp_salary empSalary,(
SELECT AVG(emp_salary) FROM t_emp) FROM t_emp WHERE emp_salary=(
SELECT MAX(emp_salary) FROM t_emp);
</select>
<select id="selectAll" resultType="com.shunfen.domain.Employee">
select * from t_emp;
</select>
<!-- 在全局范围内对Mybatis进行配置 -->
</mapper>package com.shunfen;
import com.shunfen.domain.Employee;
import com.shunfen.mapper.EmployeeMapper;
import lombok.extern.log4j.Log4j;
import lombok.extern.log4j.Log4j2;
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 org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
public class MybatisTest {
private Logger logger = LoggerFactory.getLogger(MybatisTest.class);
private SqlSession sqlSession;
@BeforeEach
public void BeMode() throws IOException {
sqlSession =
new SqlSessionFactoryBuilder().
build(Resources.getResourceAsStream("mybatis-config.xml"))
.openSession();
}
@Test
public void testMapperEmp(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Map<String, Object> map = new HashMap<String, Object>();
map.put("empSalesKey",999.99);
map.put("empIdKey",3);
int updateEmpMap = mapper.updateEmpMap(map);
System.out.println(updateEmpMap);
logger.info("updateEmpMap"+updateEmpMap);
}
@Test
public void testOne(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
int i = mapper.selectCont();
System.out.println(i);
logger.info("i"+i);
}
@Test
public void selectAvgNameMax(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Map<String, Object> map = mapper.selectNameAgeMax();
Set<Map.Entry<String, Object>> entries = map.entrySet();
for (Map.Entry<String, Object> entry : entries) {
String key = entry.getKey();
Object value = entry.getValue();
logger.info(key+"log"+value);
}
System.out.println(map);
}
@Test
public void test(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employees = mapper.selectAll();
for (Employee employee : employees) {
logger.info("log,"+employee);
}
}
@AfterEach
public void ArMode(){
sqlSession.commit();
// 关闭会话
sqlSession.close();
}
// @Test
// public void mybatisTestOne() throws IOException {
// // 1.声明mybatis
// String mybatis = "mybatis-config.xml";
//
// // 2.输入流的模式加载配置
// InputStream resourceAsStream = Resources.getResourceAsStream(mybatis);
//
// // 3.读取mybatis配置文件
// SqlSessionFactory build =
// new SqlSessionFactoryBuilder().build(resourceAsStream);
//
// // 4.开启一个会话
// SqlSession sqlSession = build.openSession();
//
// // 5.获取mapper类对象
// EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
//
// // 6.查询一条记录
// Employee employee = mapper.selectEmp(1);
// System.out.println(employee);
//
// // 提交事务
// sqlSession.commit();
// // 关闭会话
// sqlSession.close();
//
// }
}
返回主键值
package com.shunfen.mapper;
import com.shunfen.domain.Employee;
import org.apache.ibatis.annotations.Param;
import java.util.List;
import java.util.Map;
import java.util.Objects;
public interface EmployeeMapper {
/**
* 根据员工id查找
* @param empId
* @return
*/
Employee selectEmp(Integer empId);
/**
* 添加一条数据
*/
int insertEmp(Employee employee);
int updateEmp(@Param("empId") Integer empId,@Param("empSalary") String empSalary);
int updateEmpMap(Map<String, Object> paramMap) ;
int selectCont();
Employee selectEmployee(Integer empId);
Map<String,Object> selectNameAgeMax();
List<Employee> selectAll();
int insetEmpZJ(Employee employee);
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.shunfen.mapper.EmployeeMapper">
<insert id="insertEmp">
INSERT INTO t_emp (emp_Name,emp_salary) VALUES(#{empName},#{empSalary});
</insert>
<insert id="insetEmpZJ" useGeneratedKeys="true" keyProperty="empId">
INSERT INTO t_emp (emp_name,emp_salary)VALUES(emp_name=#{empId},emp_salary=#{empSalary});
</insert>
<update id="updateEmp">
UPDATE t_emp SET emp_salary=#{empSalary} WHERE emp_id=#{empId};
</update>
<update id="updateEmpMap">
UPDATE t_emp SET emp_salary=#{empSalesKey} WHERE emp_id=#{empIdKey};
</update>
<select id="selectEmp" resultType="com.shunfen.domain.Employee">
SELECT emp_id empId,emp_name empName,emp_salary empSalary
FROM t_emp WHERE emp_id=#{empId};
</select>
<select id="selectCont" resultType="java.lang.Integer">
select count(*) from t_emp;
</select>
<select id="selectEmployee" resultType="com.shunfen.domain.Employee">
SELECT emp_id empId,emp_name empName,emp_salary empSalary FROM t_emp WHERE emp_id=#{maomi};
</select>
<select id="selectNameAgeMax" resultType="java.util.Map">
SELECT emp_id empId,emp_salary empSalary,(
SELECT AVG(emp_salary) FROM t_emp) FROM t_emp WHERE emp_salary=(
SELECT MAX(emp_salary) FROM t_emp);
</select>
<select id="selectAll" resultType="com.shunfen.domain.Employee">
select * from t_emp;
</select>
<!-- 在全局范围内对Mybatis进行配置 -->
</mapper>package com.shunfen;
import com.shunfen.domain.Employee;
import com.shunfen.mapper.EmployeeMapper;
import lombok.extern.log4j.Log4j;
import lombok.extern.log4j.Log4j2;
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 org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.IOException;
import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
public class MybatisTest {
private Logger logger = LoggerFactory.getLogger(MybatisTest.class);
private SqlSession sqlSession;
@BeforeEach
public void BeMode() throws IOException {
sqlSession =
new SqlSessionFactoryBuilder().
build(Resources.getResourceAsStream("mybatis-config.xml"))
.openSession();
}
@Test
public void testMapperEmp(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Map<String, Object> map = new HashMap<String, Object>();
map.put("empSalesKey",999.99);
map.put("empIdKey",3);
int updateEmpMap = mapper.updateEmpMap(map);
System.out.println(updateEmpMap);
logger.info("updateEmpMap"+updateEmpMap);
}
@Test
public void testOne(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
int i = mapper.selectCont();
System.out.println(i);
logger.info("i"+i);
}
@Test
public void selectAvgNameMax(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Map<String, Object> map = mapper.selectNameAgeMax();
Set<Map.Entry<String, Object>> entries = map.entrySet();
for (Map.Entry<String, Object> entry : entries) {
String key = entry.getKey();
Object value = entry.getValue();
logger.info(key+"log"+value);
}
System.out.println(map);
}
@Test
public void test(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
List<Employee> employees = mapper.selectAll();
for (Employee employee : employees) {
logger.info("log,"+employee);
}
}
@Test
public void insetEmpZJ(){
EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
Employee employee = new Employee();
employee.setEmpName("braue lee");
employee.setEmpSalary(10000000.0D);
int i = mapper.insetEmpZJ(employee);
sqlSession.commit();
logger.info("log"+employee.getEmpId());
}
@AfterEach
public void ArMode(){
sqlSession.commit();
// 关闭会话
sqlSession.close();
}
// @Test
// public void mybatisTestOne() throws IOException {
// // 1.声明mybatis
// String mybatis = "mybatis-config.xml";
//
// // 2.输入流的模式加载配置
// InputStream resourceAsStream = Resources.getResourceAsStream(mybatis);
//
// // 3.读取mybatis配置文件
// SqlSessionFactory build =
// new SqlSessionFactoryBuilder().build(resourceAsStream);
//
// // 4.开启一个会话
// SqlSession sqlSession = build.openSession();
//
// // 5.获取mapper类对象
// EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);
//
// // 6.查询一条记录
// Employee employee = mapper.selectEmp(1);
// System.out.println(employee);
//
// // 提交事务
// sqlSession.commit();
// // 关闭会话
// sqlSession.close();
//
// }
}
CRUD强化
CREATE TABLE `user` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`username` VARCHAR(50) NOT NULL,
`password` VARCHAR(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
实体类
package com.shunfen.domain;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class User {
private Integer Id;
private String userName;
private String password;
}
mapper映射
package com.shunfen.mapper;
import com.shunfen.domain.User;
import java.util.List;
public interface UserMapper {
int insetUser(User user);
int updateUser(User user);
int delete(Integer Id);
List<User> findAllUser();
List<User> findByIdUser(Integer Id);
}
usermapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.shunfen.mapper.UserMapper">
<insert id="insetUser" useGeneratedKeys="true" keyProperty="id">
INSERT INTO user(username,password)
VALUES(#{userName},#{password});
</insert>
<update id="updateUser">
update user set username=#{userName},password=#{password} WHERE id=#{Id};
</update>
<delete id="delete">
DELETE FROM user WHERE id=#{Id};
</delete>
<select id="findAllUser" resultType="com.shunfen.domain.User">
SELECT id,username,password FROM user;
</select>
<select id="findByIdUser" resultType="com.shunfen.domain.User">
SELECT id,username,password FROM user WHERE id=#{id};
</select>
</mapper>Mybatis多表映射查询
设计多表
CREATE TABLE `t_customer` (`customer_id` INT NOT NULL AUTO_INCREMENT, `customer_name` CHAR(100), PRIMARY KEY (`customer_id`) );
CREATE TABLE `t_order` ( `order_id` INT NOT NULL AUTO_INCREMENT, `order_name` CHAR(100), `customer_id` INT, PRIMARY KEY (`order_id`) );
INSERT INTO `t_customer` (`customer_name`) VALUES ('c01');
INSERT INTO `t_order` (`order_name`, `customer_id`) VALUES ('o1', '1');
INSERT INTO `t_order` (`order_name`, `customer_id`) VALUES ('o2', '1');
INSERT INTO `t_order` (`order_name`, `customer_id`) VALUES ('o3', '1'); 创建实体类映射关系
客户类
package com.shunfeng.domain;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import java.util.List;
/**
* 客户类
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Customer {
private Integer customerId;
private String customerName;
private List<Order> orderList; // 订单表对多
}
订单类
package com.shunfeng.domain;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
/**
* 订单类
*/
@Data
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Order {
private Integer orderId;
private String orderName;
private Customer customer; // 客户表 对一的关系
}
对一映射
创建OrderMapper.xml接口
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.shunfeng.mapper.OrderMapper">
<resultMap id="selectOrderWithCustomerResultMap" type="Order">
<!-- 先设置Order自身属性和字段的对应关系 -->
<id column="order_id" property="orderId"/>
<result column="order_name" property="orderName"/>
<!-- 使用association标签配置“对一”关联关系 -->
<!-- property属性：在Order类中对一的一端进行引用时使用的属性名 -->
<!-- javaType属性：一的一端类的全类名 -->
<association property="customer" javaType="Customer">
<!-- 配置Customer类的属性和字段名之间的对应关系 -->
<id column="customer_id" property="customerId"/>
<result column="customer_name" property="customerName"/>
</association>
</resultMap>
<select id="selectOrderWithCustomer" resultMap="selectOrderWithCustomerResultMap">
SELECT o.order_id,o.order_name,c.customer_id,c.customer_name
FROM t_order o
LEFT JOIN t_customer c
ON c.customer_id =o.customer_id
WHERE o.order_id=#{orderId};
</select>
</mapper>mapper.xml
<mappers>
<!-- Mapper注册：指定Mybatis映射文件的具体位置 -->
<!-- mapper标签：配置一个具体的Mapper映射文件 -->
<!-- resource属性：指定Mapper映射文件的实际存储位置，这里需要使用一个以类路径根目录为基准的相对路径 -->
<!-- 对Maven工程的目录结构来说，resources目录下的内容会直接放入类路径，所以这里我们可以以resources目录为基准 -->
<mapper resource="mappers/OrderMapper.xml"/>
</mappers>测试
package com.shunfeng;
import com.shunfeng.domain.Order;
import com.shunfeng.mapper.OrderMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import java.io.IOException;
@Slf4j
public class Mybatis {
private SqlSession sqlSession;
@BeforeEach
public void before() throws IOException {
sqlSession=new SqlSessionFactoryBuilder()
.build(Resources.getResourceAsStream("mybatis-config.xml"))
.openSession();
}
@Test
public void MybatisTestOne(){
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
Order order = mapper.selectOrderWithCustomer(2);
System.out.println(order);
log.info("log"+order);
}
@AfterEach
public void after(){
sqlSession.commit();
sqlSession.close();
}
}
多对多映射
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.shunfeng.mapper.CustomerMapper">
<resultMap id="selectCustomerOne" type="Customer">
<id column="customer_id" property="customerId"/>
<result column="customer_name" property="customerName"/>
<collection property="orderList" ofType="Order">
<id column="order_id" property="orderId"/>
<result column="order_name" property="orderName"/>
</collection>
</resultMap>
<select id="selectCustomerWithOrderList" resultMap="selectCustomerOne">
SELECT c.customer_id,c.customer_name,o.order_id,o.order_name
FROM t_customer c
LEFT JOIN t_order o
ON c.customer_id =o.customer_id
WHERE c.customer_id=#{customerId};
</select>
</mapper>mapper.xml
<mappers>
<!-- Mapper注册：指定Mybatis映射文件的具体位置 -->
<!-- mapper标签：配置一个具体的Mapper映射文件 -->
<!-- resource属性：指定Mapper映射文件的实际存储位置，这里需要使用一个以类路径根目录为基准的相对路径 -->
<!-- 对Maven工程的目录结构来说，resources目录下的内容会直接放入类路径，所以这里我们可以以resources目录为基准 -->
<mapper resource="mappers/OrderMapper.xml"/>
<mapper resource="mappers/CustomerMapper.xml"/>
</mappers>测试客户和订单
package com.shunfeng;
import com.shunfeng.domain.Customer;
import com.shunfeng.domain.Order;
import com.shunfeng.mapper.CustomerMapper;
import com.shunfeng.mapper.OrderMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.jupiter.api.AfterEach;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import java.io.IOException;
import java.util.List;
@Slf4j
public class Mybatis {
private SqlSession sqlSession;
@BeforeEach
public void before() throws IOException {
sqlSession=new SqlSessionFactoryBuilder()
.build(Resources.getResourceAsStream("mybatis-config.xml"))
.openSession();
}
@Test
public void MybatisTestOne(){
OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
Order order = mapper.selectOrderWithCustomer(2);
System.out.println(order);
log.info("log"+order);
}
@Test
public void MybatisTestOrderTwo(){
CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
Customer customer = mapper.selectCustomerWithOrderList(1);
System.out.println("客户编号"+customer.getCustomerId());
System.out.println("客户姓名"+customer.getCustomerName());
List<Order> orderList = customer.getOrderList();
for (Order order : orderList) {
System.out.println("订单..."+order);
}
}
@AfterEach
public void after(){
sqlSession.commit();
sqlSession.close();
}
}
多对多映射
执行数据库sql
-- 创建讲师表
CREATE TABLE t_teacher (
t_id INT PRIMARY KEY,
t_name VARCHAR(50)
);
-- 创建学生表
CREATE TABLE t_student (
s_id INT PRIMARY KEY,
s_name VARCHAR(50)
);
-- 创建中间表
CREATE TABLE t_inner (
t_id INT,
s_id INT,
PRIMARY KEY (t_id, s_id)
);
-- 向讲师表中插入测试数据
INSERT INTO t_teacher VALUES (1, '张三');
INSERT INTO t_teacher VALUES (2, '李四');
INSERT INTO t_teacher VALUES (3, '王五');
-- 向学生表中插入测试数据
INSERT INTO t_student VALUES (1, '小明');
INSERT INTO t_student VALUES (2, '小红');
INSERT INTO t_student VALUES (3, '小刚');
-- 向中间表中插入测试数据
INSERT INTO t_inner VALUES (1, 1);
INSERT INTO t_inner VALUES (1, 3);
INSERT INTO t_inner VALUES (2, 2);
INSERT INTO t_inner VALUES (3, 1);
INSERT INTO t_inner VALUES (3, 3);
实体类设计
package com.shunfeng.domain;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import java.util.List;
/**
* 老师类
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Teacher {
private Integer tId;
private String tName;
private List<Student> students;
}
package com.shunfeng.domain;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
/**
* 学生类
*/
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Student {
private Integer sId;
private String sName;
}
mapper
package com.shunfeng.mapper;
import com.shunfeng.domain.Teacher;
import java.util.List;
public interface TeacherMapper {
List<Teacher> findAllTeacher();
}
Tharcher.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.shunfeng.mapper.TeacherMapper">
<resultMap id="teacherMap" type="Teacher">
<id property="tId" column="t_id" />
<result property="tName" column="t_name" />
<collection property="students" ofType="Student" >
<id property="sId" column="s_id" />
<result property="sName" column="s_name" />
</collection>
</resultMap>
<select id="findAllTeacher" resultMap="teacherMap">
SELECT t.t_id,t.t_name,s.s_id,s.s_name FROM t_teacher t
LEFT OUTER JOIN t_inner i ON i.t_id = t.t_id
LEFT OUTER JOIN t_student s ON i.s_id=s.s_id
</select>
</mapper>测试方法
@Test
public void MybatisTestTescher(){
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
List<Teacher> allTeacher = mapper.findAllTeacher();
for (Teacher teacher : allTeacher) {
System.out.println(teacher+"老师");
System.out.println(teacher.getStudents()+"学生");
}
}Mybatis多表分布查询
package com.shunfeng.mapper;
import com.shunfeng.domain.Customer;
public interface CustomerMapper {
Customer selectCustomerWithOrderList(Integer customerId);
Customer queryById(Integer customerId);
}
package com.shunfeng.mapper;
import com.shunfeng.domain.Order;
public interface OrderMapper {
Order selectOrderWithCustomer(Integer orderId);
Order queryOder(Integer orderId);
}
mapperOrder.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.shunfeng.mapper.OrderMapper">
<resultMap id="selectOrderWithCustomerResultMap" type="Order">
<!-- 先设置Order自身属性和字段的对应关系 -->
<id column="order_id" property="orderId"/>
<result column="order_name" property="orderName"/>
<!-- 使用association标签配置“对一”关联关系 -->
<!-- property属性：在Order类中对一的一端进行引用时使用的属性名 -->
<!-- javaType属性：一的一端类的全类名 -->
<association property="customer" javaType="Customer">
<!-- 配置Customer类的属性和字段名之间的对应关系 -->
<id column="customer_id" property="customerId"/>
<result column="customer_name" property="customerName"/>
</association>
</resultMap>
<select id="selectOrderWithCustomer" resultMap="selectOrderWithCustomerResultMap">
SELECT o.order_id,o.order_name,c.customer_id,c.customer_name
FROM t_order o
LEFT JOIN t_customer c
ON c.customer_id =o.customer_id
WHERE o.order_id=#{orderId};
</select>
<resultMap id="queryOrderById" type="Order">
<id column="order_id" property="orderId"/>
<result column="order_name" property="orderName"/>
<result column="customer_id" property="customerId"></result>
<association property="customer" select="com.shunfeng.mapper.OrderMapper.queryOder" column="customer_id" />
</resultMap>
<select id="queryOder" resultMap="queryOrderById">
select * from t_order where order_id=#{orderId};
</select>
</mapper>custoremMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.shunfeng.mapper.CustomerMapper">
<resultMap id="selectCustomerOne" type="Customer">
<id column="customer_id" property="customerId"/>
<result column="customer_name" property="customerName"/>
<collection property="orderList" ofType="Order">
<id column="order_id" property="orderId"/>
<result column="order_name" property="orderName"/>
</collection>
</resultMap>
<select id="selectCustomerWithOrderList" resultMap="selectCustomerOne">
SELECT c.customer_id,c.customer_name,o.order_id,o.order_name
FROM t_customer c
LEFT JOIN t_order o
ON c.customer_id =o.customer_id
WHERE c.customer_id=#{customerId};
</select>
<select id="queryById" resultType="com.shunfeng.domain.Customer">
select from t_customer where customer_id=#{customerId};
</select>
</mapper>对多分布查询实现
package com.shunfeng.domain;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
import java.util.List;
/**
* 订单类
*/
@Data
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class Order {
private Integer orderId;
private String orderName;
private Customer customerId;
private Customer customer;// 客户表 对一的关系
private List<Customer> orderList; // 多对多
}
package com.shunfeng.mapper;
import com.shunfeng.domain.Order;
import java.util.List;
public interface OrderMapper {
Order selectOrderWithCustomer(Integer orderId);
Order queryOder(Integer orderId);
List<Order> orderByIdSelect(Integer orderId);
}
package com.shunfeng.mapper;
import com.shunfeng.domain.Customer;
import com.shunfeng.domain.Order;
import java.util.List;
public interface CustomerMapper {
Customer selectCustomerWithOrderList(Integer customerId);
Customer queryById(Integer customerId);
List<Customer> customerAll();
}
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.shunfeng.mapper.CustomerMapper">
<resultMap id="selectCustomerOne" type="Customer">
<id column="customer_id" property="customerId"/>
<result column="customer_name" property="customerName"/>
<collection property="orderList" ofType="Order">
<id column="order_id" property="orderId"/>
<result column="order_name" property="orderName"/>
</collection>
</resultMap>
<select id="selectCustomerWithOrderList" resultMap="selectCustomerOne">
SELECT c.customer_id,c.customer_name,o.order_id,o.order_name
FROM t_customer c
LEFT JOIN t_order o
ON c.customer_id =o.customer_id
WHERE c.customer_id=#{customerId};
</select>
<select id="queryById" resultType="com.shunfeng.domain.Customer">
select from t_customer where customer_id=#{customerId};
</select>
<select id="customerAll" resultType="com.shunfeng.domain.Customer">
select * from t_customer
</select>
</mapper><?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.shunfeng.mapper.OrderMapper">
<resultMap id="selectOrderWithCustomerResultMap" type="Order">
<!-- 先设置Order自身属性和字段的对应关系 -->
<id column="order_id" property="orderId"/>
<result column="order_name" property="orderName"/>
<!-- 使用association标签配置“对一”关联关系 -->
<!-- property属性：在Order类中对一的一端进行引用时使用的属性名 -->
<!-- javaType属性：一的一端类的全类名 -->
<association property="customer" javaType="Customer">
<!-- 配置Customer类的属性和字段名之间的对应关系 -->
<id column="customer_id" property="customerId"/>
<result column="customer_name" property="customerName"/>
</association>
</resultMap>
<select id="selectOrderWithCustomer" resultMap="selectOrderWithCustomerResultMap">
SELECT o.order_id,o.order_name,c.customer_id,c.customer_name
FROM t_order o
LEFT JOIN t_customer c
ON c.customer_id =o.customer_id
WHERE o.order_id=#{orderId};
</select>
<resultMap id="queryOrderById" type="Order">
<id column="order_id" property="orderId"/>
<result column="order_name" property="orderName"/>
<result column="customer_id" property="customerId"></result>
<association property="orderList"
select="com.shunfeng.mapper.OrderMapper.orderByIdSelect"
column="customer_id" />
</resultMap>
<select id="queryOder" resultMap="queryOrderById">
select * from t_order where order_id=#{orderId};
</select>
<select id="orderByIdSelect" resultMap="queryOrderById">
select * from t_order;
</select>
</mapper>分布查询延迟加载
<settings>
<!-- 开启延迟加载功能：需要配置两个配置项 -->
<!-- 1、将lazyLoadingEnabled设置为true，开启懒加载功能 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 2、将aggressiveLazyLoading设置为false，关闭“积极的懒加载” -->
<setting name="aggressiveLazyLoading" value="false"/>
</settings>Mybatis动态语句
if和where标签 set标签
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.shunfen.mapper.EmployeeMapper">
<insert id="insertEmp">
INSERT INTO t_emp (emp_Name,emp_salary) VALUES(#{empName},#{empSalary});
</insert>
<insert id="insetEmpZJ" useGeneratedKeys="true" keyProperty="empId">
INSERT INTO t_emp (emp_name,emp_salary)VALUES(emp_name=#{empId},emp_salary=#{empSalary});
</insert>
<update id="updateEmp">
UPDATE t_emp
<set>
<if test="empName !=null">
empName=#{emp_name}
</if>
<if test="empSalary < 30000">
empSalary=#{emp_salary}
</if>
</set>
emp_salary=#{empSalary}
WHERE emp_id=#{empId};
</update>
<update id="updateEmpMap">
UPDATE t_emp SET emp_salary=#{empSalesKey} WHERE emp_id=#{empIdKey};
</update>
<select id="selectEmp" resultType="com.shunfen.domain.Employee">
SELECT emp_id empId,emp_name empName,emp_salary empSalary
FROM t_emp WHERE emp_id=#{empId};
</select>
<select id="selectCont" resultType="java.lang.Integer">
select count(*) from t_emp;
</select>
<select id="selectEmployee" resultType="com.shunfen.domain.Employee">
SELECT emp_id empId,emp_name empName,emp_salary empSalary FROM t_emp WHERE emp_id=#{maomi};
</select>
<select id="selectNameAgeMax" resultType="java.util.Map">
SELECT emp_id empId,emp_salary empSalary,(
SELECT AVG(emp_salary) FROM t_emp) FROM t_emp WHERE emp_salary=(
SELECT MAX(emp_salary) FROM t_emp);
</select>
<select id="selectAll" resultType="com.shunfen.domain.Employee">
select * from t_emp
<where>
<if test="empName !=null">
or emp_name=#{empName}
</if>
<if test="empSalary > 2000"></if>
or emp_salary=#{empSalary}
</where>
</select>
<!-- 在全局范围内对Mybatis进行配置 -->
</mapper>foreach的使用
<!--
collection属性：要遍历的集合
item属性：遍历集合的过程中能得到每一个具体对象，在item属性中设置一个名字，将来通过这个名字引用遍历出来的对象
separator属性：指定当foreach标签的标签体重复拼接字符串时，各个标签体字符串之间的分隔符
open属性：指定整个循环把字符串拼好后，字符串整体的前面要添加的字符串
close属性：指定整个循环把字符串拼好后，字符串整体的后面要添加的字符串
index属性：这里起一个名字，便于后面引用
遍历List集合，这里能够得到List集合的索引值
遍历Map集合，这里能够得到Map集合的key
-->
<foreach collection="empList" item="emp" separator="," open="values" index="myIndex">
<!-- 在foreach标签内部如果需要引用遍历得到的具体的一个对象，需要使用item属性声明的名称 -->
(#{emp.empName},#{myIndex},#{emp.empSalary},#{emp.empGender})
</foreach>Mybatis扩展
1.Mybatis批量设计优化
mapperOrder接口和mapperOrder.xml配置文件 包路径保持一致
插件和分页插件
pageHelper插件的使用引入依赖
1.pom.xml
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.11</version>
</dependency>2.mybatis-config.xml配置分页插件
<plugins>
<plugin interceptor="com.github.pagehelper.PageInterceptor">
<property name="helperDialect" value="mysql"/>
</plugin>
</plugins>3.pageHelter的使用
@Test
public void testTeacherRelationshipToMulti() {
TeacherMapper teacherMapper = session.getMapper(TeacherMapper.class);
PageHelper.startPage(1,2);
List<Teacher> allTeachers = teacherMapper.findAllTeachers();
PageInfo<Teacher> pageInfo = new PageInfo<>(allTeachers);
System.out.println("pageInfo = " + pageInfo);
long total = pageInfo.getTotal(); // 获取总记录数
System.out.println("total = " + total);
int pages = pageInfo.getPages(); // 获取总页数
System.out.println("pages = " + pages);
int pageNum = pageInfo.getPageNum(); // 获取当前页码
System.out.println("pageNum = " + pageNum);
int pageSize = pageInfo.getPageSize(); // 获取每页显示记录数
System.out.println("pageSize = " + pageSize);
List<Teacher> teachers = pageInfo.getList(); //获取查询页的数据集合
System.out.println("teachers = " + teachers);
teachers.forEach(System.out::println);
}
179
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
