MyBatis
文章目录
一、框架概念
框架:软件的半成品,完成了软件开发过程中通用的模块,程序员只需进行很少或者不进行加工,就可以实现特定的功能,从而简化开发人员在软件开发过程的步骤,提高效率。
1.1 ORM框架
- ORM(Object Relation Mapping):对象关系映射,将程序中的一个对象与表中的一行数据相对应。
- ORM框架提供了持久化类与表的映射关系,在运行时,参照映射信息,将数据持久化到对应的表中。
1.2 常用框架
- MVC框架:简化了servlet的开发步骤
- Structs
- Structs2
- SpringMVC
- 持久层框架,完成数据库操作的框架
- DButils
- Hibernate:全自动的ORM框架,功能强大,能用到的功能基本都封装好了,但可操作性很差。
- MyBatis:半自动的ORM框架,有可操作性,便于扩展业务逻辑。
- 胶水框架:Spring框架、粘合MVC和MyBatis的框架
- SSM:Spring SpringMVC MyBatis
- SSH:Spring Structs2 Hibernate
二、MyBatis
2.1 概念
- 历史:前身是一个开源项目iBatis。2010年由apache组织迁移到Google Code,改名为MyBatis。2013年迁移到了GitHub。
- MyBatis是一个半自动的ORM框架。
- 是一个基于Java的持久层框架,支持自定义Sql,存储过程和高级映射。
- MyBatis对原有的JDBC操作进行封装,几乎消除了所有JDBC代码,使开发者只关注sql本身。
2.2 构造Maven项目
这里是IDEA2022的创建过程,IDEA2019区别比较大,可以自行搜索。
2.2.1 新建maven项目
2.2.2 设置IDEA的maven仓库
不会配置maven仓库的可以在csdn搜索一下,按照步骤进行下载配置即可
如果你的电脑只有一个系统盘(一些笔记本电脑),不要将maven配置到系统文件夹下,因为在您进行导入的时候会没有权限访问系统文件夹会导致maven仓库配置错误,建议放在用户文件夹下,如果不是只有一个盘的话建议您放在C盘以外的一个地方。
2.2.3 效果
创建成功后idea右边栏会有maven仓库的选项,项目结构如图所示
三、MyBatis环境搭建
3.1 pom.xml中引入MyBatis核心依赖
推荐一个网站,想要的依赖直接进去搜索就可以
https://mvnrepository.com/
添加到pom.xml文件后刷新即可前提是maven仓库配置成功,这里推荐阿里云镜像
在您下载的maven压缩包下找到setting.xml文件找到
<mirrors> </mirrors>这个标签,在其中添加以下语句
<!-- 阿里云镜像 --> <mirror> <id>alimaven</id> <name>aliyun maven</name> <url>http://maven.aliyun.com/nexus/content/groups/public/</url> <mirrorOf>central</mirrorOf> </mirror> <mirror> <id>aliyun</id> <mirrorOf>central</mirrorOf> <name>aliyun-public</name> <url>https://maven.aliyun.com/repository/public/</url> </mirror> <mirror> <id>aliyun-spring</id> <mirrorOf>spring</mirrorOf> <name>aliyun-spring</name> <url>https://maven.aliyun.com/repository/spring</url> </mirror>
<!-- 依赖-->
<dependencies>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.1</version>
<scope>test</scope>
</dependency>
</dependencies>
3.2 在resources中创建自定义xml文件模板
可以自定义一些自己常用的xml模板
3.2.1 mybatis.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>
</configuration>
3.2.2 mappers.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">
<!--namespace = 所需实现的接口全限定名-->
<mapper namespace="">
</mapper>
3.3 配置MyBatis的配置文件
<?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">
<!--mybatis的配置信息-->
<configuration>
<!--加载db.proerties的配置信息-->
<properties resource="db.properties"></properties>
<!--自定义别名 二选一-->
<typeAliases>
<!--定义类型别名-->
<typeAlias type="org.example.entity.Stu" alias="Stu"></typeAlias>
<!--自动扫描包,将原类名作为别名-->
<package name="org.example.entity"/>
</typeAliases>
<!--JDBC的环境配置信息,选中默认环境-->
<environments default="MySqlDB">
<!--mysql数据库的环境配置信息-->
<environment id="MySqlDB">
<!--事务管理的环境配置信息-->
<transactionManager type="JDBC"></transactionManager>
<!--连接池-->
<dataSource type="org.apache.ibatis.datasource.pooled.PooledDataSourceFactory">
<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>
<!--注册mapper文件-->
<mapper resource="mappers/stumapper.xml"></mapper>
</mappers>
</configuration>
四、MyBatis开发步骤
4.1 建表
CREATE TABLE `stu` ( `id` INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键id',
`username` VARCHAR(32) NOT NULL,
`password` VARCHAR(32) NOT NULL,
`sex` VARCHAR(32)NOT NULL );
4.2 定义实体类
package org.example.entity;
/**
* @BelongsProject: dadad0707
* @BelongsPackage: org.example.entity
* @Author: LiHaoNan
* @Date: 2022/7/8 13:10
* @Description: Stu类
*/
public class Stu {
private int id;
private String username;
private String password;
private String sex;
public Stu(){
}
public Stu(int id, String username, String password, String sex) {
this.id = id;
this.username = username;
this.password = password;
this.sex = sex;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getUsername() {
return username;
}
public void setUsername(String username) {
this.username = username;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
public String toString() {
return "Stu{" +
"id=" + id +
", username='" + username + '\'' +
", password='" + password + '\'' +
", sex='" + sex + '\'' +
'}';
}
}
4.3 定义DAO接口
package org.example.dao;
import org.example.entity.Stu;
/**
* @BelongsProject: dadad0707
* @BelongsPackage: org.example.dao
* @Author: LiHaoNan
* @Date: 2022/7/8 13:11
* @Description:
*/
public interface StuDao {
/**
* @author: LiHaoNan
* @date: 2022/7/8 13:11
* @description://TODO 根据id查询数据
* @param id
* @return Stu
*/
Stu selectStu(int id);
}
4.4 编写mapper.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">
<!--namespace = 所需实现的接口全称 mapper.xml相当于该接口的实现类-->
<mapper namespace="org.example.dao.StuDao">
<!--id的属性值为对应接口中的抽象方法的名称
resultType为该方法的返回值类型-->
<select id="selectStu" resultType="org.example.entity.Stu">
select * from test_stu where id=#{id}
</select>
</mapper>
4.5 注册Mapper
在mybatis.xml中添加
<!--mapper文件的注册位置-->
<mappers>
<!--注册mapper文件-->
<mapper resource="mappers/stumapper.xml"></mapper>
</mappers>
4.6 单元测试
测试插入方法
@Test
public void insertStu() throws IOException {
//1。获得读取mybatis配置文件的对象
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//2.构建sqlsession连接对象的工厂
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取连接对象
SqlSession sqlSession = build.openSession();
//4.通过连接对象获取接口实现类对象
StuDao mapper = sqlSession.getMapper(StuDao.class);
//5.调用接口中的方法
// Map<Object,Object> hashMap=new HashMap<Object,Object>();
// hashMap.put("myid",1);
// hashMap.put("myname","李浩楠");
Stu stu=new Stu();
stu.setUsername("寥寥楠");
stu.setPassword("123123");
stu.setSex("男");
int flag=mapper.insertStu(stu);
if (flag==1){
System.out.println("插入成功!");
}else {
System.out.println("插入失败!");
}
sqlSession.commit();
}
package org.example.dao;
import org.apache.ibatis.annotations.Param;
import org.example.entity.Stu;
import java.util.Map;
/**
* @BelongsProject: dadad0707
* @BelongsPackage: org.example.dao
* @Author: LiHaoNan
* @Date: 2022/7/8 13:11
* @Description:
*/
public interface StuDao {
/**
* @author: LiHaoNan
* @date: 2022/7/8 13:11
* @description://TODO 根据id查询数据
* @param stu
* @return Stu
*/
/*使用原生参数进行绑定*/
// Stu selectStu(int id,String username);
//通过注解的方式进行参数绑定
// Stu selectStu(@Param("id")int id, @Param("name")String username);
//通过map方式进行参数绑定
// Stu selectStu(Map values);
//通过对象参数方式进行参数绑定
Stu selectStu(Stu stu);
}
五、细节补充
5.1 解决mapper.xml存在resources文件夹之外的路径问题
在pom.xml中追加
<!--如果把xml文件写到了java文件夹下如何导入-->
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<!--默认-->
<include>*.xml</include>
<!--*/代表一级目录 **/代表多级目录-->
<include>**/*.xml</include>
</includes>
</resource>
</resources>
</build>
5.2 properties配置文件
新建一个db.properties配置文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/db_book
jdbc.username=root
jdbc.password=haonan7412963..
加载并读取配置文件信息
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>org.example</groupId>
<artifactId>dadad0707</artifactId>
<version>1.0-SNAPSHOT</version>
<properties>
<maven.compiler.source>8</maven.compiler.source>
<maven.compiler.target>8</maven.compiler.target>
</properties>
<!-- 依赖-->
<dependencies>
<!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.4.6</version>
</dependency>
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!-- https://mvnrepository.com/artifact/junit/junit -->
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.1</version>
<scope>test</scope>
</dependency>
</dependencies>
<!--<!–如果把xml文件写到了java文件夹下如何导入–>-->
<!-- <build>-->
<!-- <resources>-->
<!-- <resource>-->
<!-- <directory>src/main/java</directory>-->
<!-- <includes>-->
<!-- <!–默认–>-->
<!-- <include>*.xml</include>-->
<!-- <!–*/代表一级目录 **/代表多级目录–>-->
<!-- <include>**/*.xml</include>-->
<!-- </includes>-->
<!-- </resource>-->
<!-- </resources>-->
<!-- </build>-->
</project>
5.3 类型别名
为实体类定义别名,提高书写效率
注意在mybatis.xml中的顺序
<typeAliases>
<!--定义类型别名-->
<typeAlias type="org.example.entity.Stu" alias="Stu"></typeAlias>
<!--自动扫描包,将原类名作为别名-->
<package name="org.example.entity"/>
</typeAliases>
六、Mybatis的CRUD操作
6.1 查询操作
<select id="" resultType="">
</select>
6.1.1 原生参数绑定
public interface StuDao {
/**
* @author: LiHaoNan
* @date: 2022/7/8 13:11
* @description://TODO 根据id查询数据
* @param
* @return Stu
*/
/*使用原生参数进行绑定*/
Stu selectStu(int id,String username);
}
<select id="selectStu" resultType="org.example.entity.Stu">
select * from test_stu where id=#{arg0} and username =#{arg1};
</select>
<select id="selectStu" resultType="org.example.entity.Stu">
select * from test_stu where id=#{param1} and username =#{param2};
</select>
6.1.2 注解参数进行绑定
public interface StuDao {
/**
* @author: LiHaoNan
* @date: 2022/7/8 13:11
* @description://TODO 根据id查询数据
* @param
* @return Stu
*/
//通过注解的方式进行参数绑定
Stu selectStu(@Param("id")int id, @Param("name")String username);
}
<select id="selectStu" resultType="org.example.entity.Stu">
select * from test_stu where id=#{id} and username =#{name};
</select>
6.1.3 map参数进行绑定
public interface StuDao {
/**
* @author: LiHaoNan
* @date: 2022/7/8 13:11
* @description://TODO 根据id查询数据
* @param
* @return Stu
*/
//通过map方式进行参数绑定
Stu selectStu(Map values);
}
<select id="selectStu" resultType="org.example.entity.Stu">
select * from test_stu where id=#{myid} and username =#{myname};
</select>
test语句
//1。获得读取mybatis配置文件的对象
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//2.构建sqlsession连接对象的工厂
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取连接对象
SqlSession sqlSession = build.openSession();
//4.通过连接对象获取接口实现类对象
StuDao mapper = sqlSession.getMapper(StuDao.class);
//5.调用接口中的方法
Map<Object,Object> hashMap=new HashMap<Object,Object>();
hashMap.put("myid",1);
hashMap.put("myname","李浩楠");
System.out.println(mapper.selectStu());
6.1.4 对象参数绑定
public interface StuDao {
/**
* @author: LiHaoNan
* @date: 2022/7/8 13:11
* @description://TODO 根据id查询数据
* @param
* @return Stu
*/
//通过对象参数方式进行参数绑定
Stu selectStu(Stu stu);
}
<select id="selectStu" resultType="org.example.entity.Stu"><!-- #{id}来取id值-->
select * from stu where id = #{id} and username = #{username};
</select>
test语句
//1。获得读取mybatis配置文件的对象
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//2.构建sqlsession连接对象的工厂
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取连接对象
SqlSession sqlSession = build.openSession();
//4.通过连接对象获取接口实现类对象
StuDao mapper = sqlSession.getMapper(StuDao.class);
//5.调用接口中的方法
Stu stu=new Stu();
stu.setId(1);
stu.setUsername("李浩楠");
System.out.println(mapper.selectStu());
6.1.5 模糊查询
public interface StuDao {
/**
* @author: LiHaoNan
* @date: 2022/7/8 13:11
* @description://TODO 根据id查询数据
* @param
* @return Stu
*/
/* @Param("name") mapper 中需要使用的参数*/
Stu selectStu(@Param("name") String name);
}
<select id="selectStu" resultType="Stu">
select * from stu where username like concat(#{name},'%');
</select>
test语句
//1。获得读取mybatis配置文件的对象
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//2.构建sqlsession连接对象的工厂
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取连接对象
SqlSession sqlSession = build.openSession();
//4.通过连接对象获取接口实现类对象
StuDao mapper = sqlSession.getMapper(StuDao.class);
//5.调用接口中的方法
System.out.println(mapper.selectStu("赵"));
6.1.6 查询全部数据
/**
* @BelongsProject: dadad0707
* @BelongsPackage: org.example.dao
* @Author: LiHaoNan
* @Date: 2022/7/8 13:11
* @Description:
*/
public interface StuDao {
List<Stu> selectStu();
}
<select id="selectStu" resultType="Stu">
select * from test_stu ;<!-- 查询全部-->
</select>
test语句
//1。获得读取mybatis配置文件的对象
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//2.构建sqlsession连接对象的工厂
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取连接对象
SqlSession sqlSession = build.openSession();
//4.通过连接对象获取接口实现类对象
StuDao mapper = sqlSession.getMapper(StuDao.class);
//5.调用接口中的方法
System.out.println(mapper.selectStu());
6.2 插入操作
public interface StuDao {
/**
* @author: LiHaoNan
* @date: 2022/7/8 13:11
* @description://TODO 根据id查询数据
* @param
* @return Stu
*/
int insertStu(Stu stu);
}
<insert id="insertStu" parameterType="Stu">
insert into test_stu (username,password,sex)values (#{username},#{password},#{sex});
</insert>
test语句
//1。获得读取mybatis配置文件的对象
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//2.构建sqlsession连接对象的工厂
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取连接对象
SqlSession sqlSession = build.openSession();
//4.通过连接对象获取接口实现类对象
StuDao mapper = sqlSession.getMapper(StuDao.class);
//5.调用接口中的方法
Stu stu=new Stu();
stu.setUsername("寥寥楠");
stu.setPassword("123123");
stu.setSex("男");
int flag=mapper.insertStu(stu);
if (flag==1){
System.out.println("插入成功!");
}else {
System.out.println("插入失败!");
}
sqlSession.commit();//提交
6.3 删除操作
public interface StuDao {
/**
* @author: LiHaoNan
* @date: 2022/7/8 13:11
* @description://TODO 根据id查询数据
* @param
* @return Stu
*/
int deleteStu(Stu stu);
}
<delete id="deleteStu" parameterType="int">
delete from test_stu where id=#{id}
</delete>
6.4 更新操作
public interface StuDao {
/**
* @author: LiHaoNan
* @date: 2022/7/8 13:11
* @description://TODO 根据id查询数据
* @param
* @return Stu
*/
Stu updateStu(Stu stu);
}
<update id="updateStu" parameterType="Stu">
update test_stu set username=#{username},password#{password} where id=#{id}
</update>
test语句
//1。获得读取mybatis配置文件的对象
InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
//2.构建sqlsession连接对象的工厂
SqlSessionFactory build = new SqlSessionFactoryBuilder().build(is);
//3.通过工厂对象获取连接对象
SqlSession sqlSession = build.openSession();
//4.通过连接对象获取接口实现类对象
StuDao mapper = sqlSession.getMapper(StuDao.class);
//5.调用接口中的方法
Stu stu=new Stu();
stu.setUsername("寥寥楠");
stu.setPassword("123123");
stu.setID("1");
mapper.updateStu(stu);
七、ORM映射
- 问题:MyBatis只能自动维护库表列名与属性名一一对应的列,当两者不同时,无法自动ORM(映射)也就无法对对应的表属性进行操作。
- 方案一:列的别名
<select id="selectStudent" resultType="Student">
select stu_id as stuId,stu_name as stuName,stu_desc as stuDesc from test_stu where stu_id=#{id};<!-- 查询全部-->
</select>
- 方案二:结果映射(ResultMap 查询结果的封装规则)
<select id="selectStudent" resultType="Student" resultMap="managerResult">
select stu_id ,stu_name ,stu_desc from test_stu where stu_id=#{id};<!-- 查询全部-->
</select>
<resultMap id="managerResult" type="com.org.example.entity.Student">
<id property="stuid" column="stu_id"/>
<result property="stuName" column="stu_name"/>
<result property="stuDesc" column="stu_desc"/>
</resultMap>
八、MyBatis处理关联关系-多表连接
实体之间的关联关系:
- 一对一:OneToOne、一个人只有一个成绩、一个人只有一个身份证号。
- 一对多:OneToMany、一个老师面对多个学生、一个教室面对多个学院的学生。
- 多对多:ManyToMany 、
SELECT * FROM test_stu LEFT JOIN Stu ON stu_id=id;
8.1 一对一
- 实体类
package org.example.entity; /** * @BelongsProject: dadad0707 * @BelongsPackage: org.example.entity * @Author: LiHaoNan * @Date: 2022/7/8 20:47 * @Description: */ public class Student { int stuId; String stuName; String stuDesc; //把Stu的对象放入其中 private Stu stu; public Student() { } public Student(int stuId, String stuName, String stuDesc,Stu stu) { this.stuId = stuId; this.stuName = stuName; this.stuDesc = stuDesc; this.stu=stu; } public int getStuId() { return stuId; } public void setStuId(int stuId) { this.stuId = stuId; } public String getStuName() { return stuName; } public void setStuName(String stuName) { this.stuName = stuName; } public String getStuDesc() { return stuDesc; } public void setStuDesc(String stuDesc) { this.stuDesc = stuDesc; } public Stu getStu() { return stu; } public void setStu(Stu stu) { this.stu = stu; } @Override public String toString() { return "Student{" + "stuId=" + stuId + ", stuName='" + stuName + '\'' + ", stuDesc='" + stuDesc + '\'' + ", stu=" + stu + '}'; } }
- mapper.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"> <!--namespace = 所需实现的接口全限定名--> <mapper namespace="org.example.dao.StuDao"> <resultMap id="managerResultMap" type="org.example.entity.Student"> <id property="stuId" column="stu_id"/> <result property="stuName" column="stu_name"/> <result property="stuDesc" column="stu_desc"/> <association property="stu" javaType="org.example.entity.Stu"> <id property="id" column="id"/> <result property="username" column="username"/> <result property="password" column="password"/> <result property="sex" column="sex"/> </association> </resultMap> <select id="selectStudent" resultType="Student" resultMap="managerResultMap"> SELECT * FROM test_stu LEFT JOIN Stu ON stu_id=id; WHERE stu_id=#{id} </select> </mapper>
8.2 一对多
- 实体类Student
package org.example.entity; import java.util.List; /** * @BelongsProject: dadad0707 * @BelongsPackage: org.example.entity * @Author: LiHaoNan * @Date: 2022/7/8 20:47 * @Description: */ public class Student { int stuId; String stuName; String stuDesc; //把Stu的对象放入其中 //private Stu stu; private List<Stu>stu; public Student() { } public Student(int stuId, String stuName, String stuDesc,List<Stu>stu) { this.stuId = stuId; this.stuName = stuName; this.stuDesc = stuDesc; this.stu=stu; } public int getStuId() { return stuId; } public void setStuId(int stuId) { this.stuId = stuId; } public String getStuName() { return stuName; } public void setStuName(String stuName) { this.stuName = stuName; } public String getStuDesc() { return stuDesc; } public void setStuDesc(String stuDesc) { this.stuDesc = stuDesc; } public List<Stu> getStu() { return stu; } public void setStu(List<Stu> stu) { this.stu = stu; } @Override public String toString() { return "Student{" + "stuId=" + stuId + ", stuName='" + stuName + '\'' + ", stuDesc='" + stuDesc + '\'' + ", stu=" + stu + '}'; } }
- 实体类Stu
package org.example.entity; /** * @BelongsProject: dadad0707 * @BelongsPackage: org.example.entity * @Author: LiHaoNan * @Date: 2022/7/8 13:10 * @Description: Stu类 */ public class Stu { private int id; private String username; private String password; private String sex; private int stuid; public Stu(){ } public Stu(int id, String username, String password, String sex,int stuid) { this.id = id; this.username = username; this.password = password; this.sex = sex; this.stuid=stuid; } public int getId() { return id; } public void setId(int id) { this.id = id; } public String getUsername() { return username; } public void setUsername(String username) { this.username = username; } public String getPassword() { return password; } public void setPassword(String password) { this.password = password; } public String getSex() { return sex; } public void setSex(String sex) { this.sex = sex; } public int getStuid() { return stuid; } public void setStuid(int stuid) { this.stuid = stuid; } @Override public String toString() { return "Stu{" + "id=" + id + ", username='" + username + '\'' + ", password='" + password + '\'' + ", sex='" + sex + '\'' + ", stuid=" + stuid + '}'; } }
- mapper.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"> <!--namespace = 所需实现的接口全限定名--> <mapper namespace="org.example.dao.StuDao"> <resultMap id="managerResultMap" type="org.example.entity.Student"> <id property="stuId" column="stu_id"/> <result property="stuName" column="stu_name"/> <result property="stuDesc" column="stu_desc"/> <collection property="stu" javaType="org.example.entity.Stu"> <id property="id" column="id"/> <result property="username" column="username"/> <result property="password" column="password"/> <result property="sex" column="sex"/> <result property="stuid" column="stuid"/> </collection> </resultMap> <select id="selectStudent" resultType="Student" resultMap="managerResultMap"> SELECT * FROM test_stu LEFT JOIN Stu ON stu_id=stuid; </select> </mapper>
九、动态SQL
- where
<!-- Where 会自动忽略前缀,and or--> <select id="selectStu" resultType="Stu"> select * from test_stu <where><!-- 不为空会自动拼接,如果第一个不成功,第二个成功则会自动去掉and--> <if test="id!=null"> id=#{id} </if> <if test="username!=null"> and username=#{username} </if> <if test="password!=null"> and password=#{password} </if> <if test="sex!=null"> and sex=#{sex} </if> </where><!-- 查询全部--> </select>
- set
<!--set 会自动忽略后缀,--> <update id="updateStu" parameterType="Stu" > update <set> <if test="username!=null"> and username=#{username} </if> <if test="password!=null"> and password=#{password} </if> <if test="sex!=null"> and sex=#{sex} </if> </set> where id=#{id} </update>
- trim
<!--把where和set标签设置在一起了--> <select id="selectStu" resultType="Stu"> select * from test_stu; <!--prefix前缀 suffix后缀--> <trim prefix="WHERE" prefixOverrides="AND|OR"> <if test="id!=null"> and id=#{id} </if> <if test="username!=null"> and username=#{username} </if> </trim> </select> <update id="updateStu" parameterType="Stu" > update test_stu <trim prefix="SET" prefixOverrides=","> <if test="id!=null"> , id=#{id} </if> <if test="username!=null"> , username=#{username} </if> </trim> where id=#{id} </update>
- foreach
参数 描述 取值 collection 容器类型 list、map、array open 起始符 ( close 结束符 ) separator 分隔符 , index 下标号 从0开始,依次递增 item 当前项 任意名称 <insert id="insertStu" parameterType="Stu"> insert into test_stu(username,password,sex)values <foreach collection="list" open="(" separator="," close=")" item="id"> #{user.username},#{user.password},#{user.sex} </foreach> </insert>