文章目录
数据准备
create database db_mybatis;
use db_mybatis;
drop table if exists person;
create table if not exists person
(
id int primary key auto_increment,
name varchar(10) not null comment '姓名',
age tinyint not null comment '年龄',
sex char(1) default '男' comment '性别',
id_card char(18) not null unique comment '身份证'
);
insert into person(name, sex, age, id_card)
values ('陆小凤', '男', 23, '100001'),
('上官飞燕', '女', 18, '100002'),
('西门吹雪', '男', 25, '100003'),
('沙曼', '女', 21, '100004'),
('花满楼', '男', 21, '100005');
select *
from person;
pom.xml文件导入相关依赖
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.6</version>
</dependency>
<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>8.0.33</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.13.2</version>
<scope>test</scope>
</dependency>
</dependencies>
创建实体类Person
public class Person {
private int id;
private String name;
private String sex;
private Integer age;
private String idCard;
// 忽略了 构造方法和get、set方法
}
创建一个接口PersonMapper
以后在这个接口里面,添加增删改查的方法
package com.test.mapper;
/**
* 接口代理
*/
public interface PersonMapper {
/**
* ====== 查找
*/
List<Person> selectAll();
}
创建PersonMapper.xml
这个需要在
resources
资源文件下,并且需要跟上面的那个PersonMapper
接口保持一样的包名,所以需要先在资源文件下创建同级目录
注意
在资源文件下创建多级文件夹,不能使用`.`了,需要使用`/`才能创建成功
<?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:这里需要跟PersonMapper 这个接口进行绑定
-->
<mapper namespace="com.test.mapper.PersonMapper">
<!--
数据库表中的字段 和 实体类的属性名称 不一样,就不会自动封装数据,
所以需要使用resultMap来对不一样的进行映射
-->
<resultMap id="personResultMap" type="Person">
<!-- id 是用来对主键的映射 -->
<!-- <id property="id" column="id"/> -->
<result column="id_card" property="idCard"/>
</resultMap>
</mapper>
创建mybatis-config.xml
配置文件
在 resources 文件夹下创建 mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!--
设置别名
在这里设置里以后,在mapper.xml文件中,
就可以直接使用名字,不用再写包名了
-->
<typeAliases>
<typeAlias type="com.test.pojo.Person" alias="Person"/>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<!-- 以下内容根据自身修改 -->
<property name="driver" value="com.mysql.cj.jdbc.Driver"/>
<property name="url" value="jdbc:mysql:///db_mybatis?useServerPrepStmts=true"/>
<property name="username" value="root"/>
<property name="password" value="root1234"/>
</dataSource>
</environment>
</environments>
<mappers>
<!-- 将包内的映射器接口实现全部注册为映射器 -->
<package name="com.test.mapper"/>
</mappers>
</configuration>
查询
1. 查询所有
// 测试查询所有
@Test
public void testSelectAll() throws IOException {
SqlSession sqlSession = getSqlSession();
PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class);
List<Person> list = personMapper.selectAll();
System.out.println(list);
// 释放资源
sqlSession.close();
}
getSqlSession()的代码如下
public SqlSession getSqlSession() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
return sqlSessionFactory.openSession(true);
}
2. 根据单个条件查询
- 在
PersonMapper.java
中添加如下代码Person selectById(int id);
- 在
PersonMapper.xml
中添加如下代码<mapper namespace="com.test.mapper.PersonMapper"> ... <select id="selectById" resultMap="personResultMap"> select * from person where id = #{id} </select> </mapper>
- 单元测试代码
@Test public void testSelectById() throws IOException { SqlSession sqlSession = getSqlSession(); PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class); // 这里的id,后续是从外面传过来的 int id = 4; Person p = personMapper.selectById(id); System.out.println(p.toString()); // 释放资源 sqlSession.close(); }
3. 多个条件的动态sql查询
主要是练习 mybatis的动态sql里的
where
和if
标签
- 在
PersonMapper.java
中添加如下代码// 方式1 多个参数,使用@Param注解 // List<Person> selectByCondition(@Param("name") String name, @Param("age") int age, @Param("idCard") String idCard); // 方式2:通过map传参 // List<Person> selectByCondition(Map<String, Object> map); // 方式3:通过对象传参 List<Person> selectByCondition(Person person);
- 在
PersonMapper.xml
中添加如下代码<mapper namespace="com.test.mapper.PersonMapper"> ... <select id="selectByCondition" resultMap="personResultMap"> select * from person <where> <if test="name !=null and name != ''"> and name = #{name} </if> <if test="age !=null"> and age = #{age} </if> <if test="idCard !=null and idCard != ''"> and id_card = #{idCard} </if> </where> </select> </mapper>
- 单元测试代码
@Test public void testSelectByCondition() throws IOException { SqlSession sqlSession = getSqlSession(); PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class); // 方式1 // List<Person> list = personMapper.selectByCondition("陆小凤", 23, "100001"); // System.out.println(list); // 方式2 // Map<String, Object> map = new HashMap<>(); // map.put("name", "陆小凤"); // map.put("age", 23); // map.put("idCard", "100001"); // List<Person> list = personMapper.selectByCondition(map); // System.out.println(list); // 方式3 Person person = new Person(); // person.setName("陆小凤"); person.setAge(23); // person.setIdCard("100001"); List<Person> list = personMapper.selectByCondition(person); System.out.println(list); sqlSession.close(); }
4. 单个条件的动态sql查询
主要是练习 mybatis的动态sql里的
when
和choose
标签
- 在
PersonMapper.java
中添加如下代码// 这里传参方式跟selectByCondition一样也有三种 List<Person> selectByConditionSingle(Person person);
- 在
PersonMapper.xml
中添加如下代码<mapper namespace="com.test.mapper.PersonMapper"> ... <select id="selectByConditionSingle" resultMap="personResultMap"> select * from person <where> <choose> <when test="name != null and name != ''"> name = #{name} </when> <when test="age != null"> age = #{age} </when> <when test="idCard !=null and idCard != ''"> id_card = #{idCard} </when> </choose> </where> </select> </mapper>
- 单元测试代码
@Test public void testSelectByConditionSingle() throws IOException { SqlSession sqlSession = getSqlSession(); PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class); Person person = new Person(); person.setIdCard("100004"); // person.setAge(23); // person.setName("花满楼"); List<Person> list = personMapper.selectByConditionSingle(person); System.out.println(list); sqlSession.close(); }
添加数据
- 在
PersonMapper.java
中添加如下代码void add(Person p);
- 在
PersonMapper.xml
中添加如下代码<mapper namespace="com.test.mapper.PersonMapper"> ... <!-- keyProperty 和 useGeneratedKeys 往往搭配使用, 自动生成主键,并可将自动生成的主键返回 --> <insert id="add" useGeneratedKeys="true" keyProperty="id"> insert into person(name, age, sex, id_card) values (#{name}, #{age}, #{sex}, #{idCard}) </insert> </mapper>
- 单元测试代码
@Test public void testAdd() throws IOException { SqlSession sqlSession = getSqlSession(); PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class); Person person = new Person("王五", "女", 32, "300010"); personMapper.add(person); System.out.println("id:" + person.getId()); sqlSession.close(); }
修改数据
主要是练习 mybatis的动态sql里的
set
标签
- 在
PersonMapper.java
中添加如下代码int updateById(Person p);
- 在
PersonMapper.xml
中添加如下代码<mapper namespace="com.test.mapper.PersonMapper"> ... <update id="updateById"> update person <set> <if test="name != null and name != ''"> name = #{name}, </if> <if test="age != null"> age = #{age}, </if> <if test="idCard != null and idCard != ''"> id_card = #{idCard}, </if> </set> where id = #{id} </update> </mapper>
- 单元测试代码
@Test public void testUpdateById() throws IOException { SqlSession sqlSession = getSqlSession(); PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class); Person person = new Person(); person.setId(12); person.setAge(88); person.setIdCard("aaaaaa"); personMapper.updateById(person); sqlSession.close(); }
删除数据
删除单条数据
- 在
PersonMapper.java
中添加如下代码void deleteById(int id);
- 在
PersonMapper.xml
中添加如下代码<mapper namespace="com.test.mapper.PersonMapper"> ... <delete id="deleteById"> delete from person where id = #{id} </delete> </mapper>
- 单元测试代码
@Test public void testDeleteById() throws IOException { SqlSession sqlSession = getSqlSession(); PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class); personMapper.deleteById(10); sqlSession.close(); }
删除多条数据
主要是为了练习mybatis 的动态sql里的
foreach
标签
- 在
PersonMapper.java
中添加如下代码// void deleteByIds(int[] ids); void deleteByIds(@Param("ids") int[] ids);;
- 在
PersonMapper.xml
中添加如下代码<mapper namespace="com.test.mapper.PersonMapper"> ... <!-- 动态 删除多个 mybatis 会将数组参数,封装成一个map集合,key是array 1. 使用默认的 array, 2. 使用@Param注解修改map集合key 的值 --> <delete id="deleteByIds"> delete from person where id in <!-- <foreach collection="array" item="id" separator="," open="(" close=")">--> <foreach collection="ids" item="id" open="(" close=")" separator=","> #{id} </foreach> </delete> </mapper>
- 单元测试代码
@Test public void testDeleteByIds() throws IOException { SqlSession sqlSession = getSqlSession(); PersonMapper personMapper = sqlSession.getMapper(PersonMapper.class); personMapper.deleteByIds(new int[]{6, 7}); sqlSession.close(); }