Mybatis

参考B站颜群老师Mybatis教程

Mybatis配置

jdbc过于复杂,mybatis进行简化实现数据的持久化。
ORM:Object Relational Mapping 将对象与表联系起来,操作对象就可以自动操作表
mybatis-3.5.2.jar

示例

先创建一张表pperson

1
2
3
4
5
mysql> create table pperson(
-> id int,name varchar(20),age int);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into pperson values(1,'zs',23);
Query OK, 1 row affected (0.01 sec)

IDEA中创建实体类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
public class Pperson {
private int id;
private String name ;
private int age;
public Pperson(int id,String name,int age)
{
this.id=id;
this.name=name;
this.age=age;
}
public Pperson(){

}
//省略set get....
}

通过mapper.xml文件创建表和类的映射关系,通过id值区分标签,resultType
是返回类型,namespace放映射文件的路径,parameterType是动态传入的参
数类型

1
2
3
4
5
6
7
8
9
10
11
<!-- PpersonMapper.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="org.gaoming.mapper.PersonMapper">
<select id="querybyid" resultType="org.gaoming.my.Pperson"
parameterType="int" >
select * from pperson where id=#{id}
</select>
</mapper>

Mybatis配置文件

conf.xml配置数据库信息和需要加载的映射文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
<?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>
<settings>
<setting name="logImpl" value="LOG4J"/>
</settings>
<typeHandlers>
<typeHandler handler="org.gaoming.mapper.BooleanAndInt" javaType="Boolean" jdbcType="INTEGER"/>
</typeHandlers>
<!--开发环境(本机),可以切换环境 -->
<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://localhost:3306/gaoming?serverTimezone=UTC"/>
<property name="username" value="root"/>
<property name="password" value="19991005"/>
</dataSource>
</environment>
</environments>
<mappers>
<!--加载映射文件 -->
<mapper resource="org/gaoming/mapper/PpersonMapper.xml"/>
</mappers>
</configuration>

创建测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
package org.gaoming.my;

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.Reader;
public class Ttest {
public static void main(String[] args)throws Exception {
//加载mybatis配置文件为了访问数据库
Reader reader= Resources.getResourceAsReader("conf.xml");
//创建sql工厂
SqlSessionFactory sessionFactory=new SqlSessionFactoryBuilder().build(reader);
//可以操作数据库
SqlSession session=sessionFactory.openSession();
//namespace+id 拿到唯一的sql语句
String statement="org.gaoming.mapper.PersonMapper.querybyid";
//传入入sql语句和动态参数,查询一个值
Pperson p=session.selectOne(statement,1);
System.out.println(p);
session.close();
}
}

Mybatis-statement方式的增删查改

mybatis在语法上只允许一个输入值和一个输出值,#{xx}中如果是基本类型名字可以随便写,如果
是对象类型必须写对象的属性名,如果返回值类型是对象无论返回几个都写成一种

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
<?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="org.gaoming.mapper.PersonMapper">
<select id="querybyid" resultType="org.gaoming.my.Pperson"
parameterType="int" >
select * from pperson where id=#{id}
</select>
<insert id="addpperson" parameterType="org.gaoming.my.Pperson">
insert into pperson values(#{id},#{name},#{age})
</insert>
<delete id="deletebyid" parameterType="int">
delete from pperson where id=#{xxx}
</delete>
<update id="updatebyid" parameterType="org.gaoming.my.Pperson">
update pperson set name=#{name},age=#{age} where id=#{id}
</update>
<select id="queryall" resultType="org.gaoming.my.Pperson">
select * from pperson
</select>
</mapper>

测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
String statement="org.gaoming.mapper.PersonMapper";
//存入sql语句和动态参数
String s1=statement+".querybyid";
Pperson p=session.selectOne(s1,1);
System.out.println(p);
String s2=statement+".queryall";
List<Pperson> list=session.selectList(s2);
System.out.println(list);
String s3=statement+".addpperson";
Pperson pp=new Pperson(3,"wu",25);
//返回几行受影响
int count=session.insert(s3,pp);
//jdbc方式需要commit
System.out.println(count);
session.commit();
String s4=statement+".deletebyid";
int count2=session.delete(s4,1);
session.commit();
String s5=statement+".updatebyid";
Pperson ppp=new Pperson(2,"mn",21);
session.update(s5,ppp);
session.commit();
session.close();

mapper动态代理

约定优于配置。使用statement+id太麻烦可以简化。
在mapper包下创建Ppermapper接口,要使接口与mapper.xml文件一一对应,修改mapper文件
的namspace改为接口路径。根据接口名找到mapper.xml文件,根据接口的方法名找到sql标签
一般将mapper.xml文件和接口放在一个包中,接口不需要实现类

1
<mapper namespace="org.gaoming.mapper.Ppermapper">

接口

1
2
3
4
5
6
7
8
9
10
11
12
13
14
package org.gaoming.mapper;
import org.gaoming.my.Pperson;
import java.util.List;
public interface Ppermapper {
//约定1.方法名与id一致
//约定2.方法的输入参数和标签中的输入类型一致
//约定3.方法返回值与标签返回类型一致
Pperson querybyid(int id);
List<Pperson> queryall();
//没有resultType则为void
void addpperson(Pperson p);
void deletebyid(int id);
void updatebyid(Pperson p);
}

测试类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
Reader reader= Resources.getResourceAsReader("conf.xml");
//创建sql工厂
SqlSessionFactory sessionFactory=new
SqlSessionFactoryBuilder().build(reader);
//可以操作数据库
SqlSession session=sessionFactory.openSession();
Ppermapper p=session.getMapper(Ppermapper.class);
Pperson pp=p.querybyid(2);
System.out.println(pp);
List<Pperson> list=p.queryall();
System.out.println(list);
Pperson ppp=new Pperson(1,"op",20);
p.addpperson(ppp);
p.updatebyid(new Pperson(2,"kl",12));
p.deletebyid(3);
session.commit();
session.close();

类型转换器

Mybatis自带一些常见的类型处理器,也可以自定义类型处理器。java与jdbc转换。
bool与int转换

创建转换器

实现typehandler接口或者继承basetypehandler

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
package org.gaoming.mapper;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.TypeHandler;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class BooleanAndInt extends BaseTypeHandler<Boolean> {
//java代码到数据库
//o是Java值,jdbcType是jdbc操作的数据库类型。ture 1 false 0
public void setNonNullParameter(PreparedStatement preparedStatement
, int i, Boolean o, JdbcType jdbcType) throws SQLException {
if(o)
preparedStatement.setInt(i,1);
else
preparedStatement.setInt(i,0);
}
//数据库到Java
//通过列名
@Override
public Boolean getNullableResult(ResultSet resultSet, String s)
throws SQLException {
int sex=resultSet.getInt(s);
if(sex==1)
return true;
else
return false;
}
//通过下标
@Override
public Boolean getNullableResult(ResultSet resultSet, int i)
throws SQLException {
int sex=resultSet.getInt(i);
if(sex==1)
return true;
else
return false;
}
//通过存储过程
@Override
public Boolean getNullableResult(CallableStatement callableStatement
, int i) throws SQLException {
int sex=callableStatement.getInt(i);
if(sex==1)
return true;
else
return false;
}
}

在config中配置

1
2
3
4
<typeHandlers>
<typeHandler handler="org.gaoming.mapper.BooleanAndInt"
javaType="Boolean" jdbcType="INTEGER"/>
</typeHandlers>

在数据库中增加sex属性,在类中添加sex属性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> alter table pperson add column sex int;
Query OK, 0 rows affected (0.15 sec)
mysql> update pperson set sex=0;
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> select * from pperson;
+------+------+------+------+
| id | name | age | sex |
+------+------+------+------+
| 2 | kl | 12 | 0 |
| 1 | op | 20 | 0 |
+------+------+------+------+
2 rows in set (0.00 sec)

resultMap

如果类中的属性和表中的字段类型能够识别可以使用resultType,如果不能则需要使用resultMap
如果类型属性名和表中字段名一样则用resultType,否则使用resultMap
resultMap与id对应

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<select id="querybyid" resultMap="ppersonresult"
parameterType="int" >
select * from pperson where id = #{id}
</select>
<resultMap id="ppersonresult" type="org.gaoming.my.Pperson">
<!--对应分为主键和非主键,在数据库中将id变为主键 -->
<!--主键用id,非主键用result -->
<id property="id" column="id" />
<result property="name" column="name" />
<result property="age" column="age" />
<result property="sex" column="sex" jdbcType="INTEGER"
javaType="boolean" />
</resultMap>
<!--写对象属性名严格区分大小写 -->
<insert id="addquery" parameterType="org.gaoming.my.Person">
insert into person(id,name,age,sex) values(#{id},#{name},#{age},#{sex,
javaType=boolean,
jdbcType=INTEGER})
</insert>

两种取值符号以及parameterType

  1. 输入参数的类型如果是简单类型(8个基本类型+string) #{xx}xx可以任意写
  2. 也可以使用${value},简单类型必须写value
  3. 输入参数是对象类型,#{属性名} ${属性名}

两种符号的区别

  1. 井号会自动给string类型加上’’,使用#自动加’’相当于常量排序实际无效,根据的是字段
  2. $ 原样输出,也就是说如果参数是string要写为 ‘${value}’,但是$适合动态排序
  3. #{}可以防止sql注入,${}不可以
  4. 模糊查询时#必须存入参数时就写%,而$可以写为 ‘%${name}%’,传入参数不用写%
  • 静态排序
    1
    select * from pperson order by name desc
  • 动态排序
    1
    select * from pperson order by ${value} desc

嵌套对象类型

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> alter table pperson add homeaddress varchar(20);
Query OK, 0 rows affected (0.25 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> alter table pperson add schooladdress varchar(20);
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> select * from pperson;
+----+------+------+------+-------------+---------------+
| id | name | age | sex | homeaddress | schooladdress |
+----+------+------+------+-------------+---------------+
| 1 | op | 20 | 0 | ls | ss |
| 2 | kl | 12 | 0 | sz | gz |
| 4 | vx | 21 | 1 | sz | gz |
| 6 | nm | 31 | 1 | sz | gz |
+----+------+------+------+-------------+---------------+
4 rows in set (0.00 sec)

创建一个Address类并且补充到Pperson中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
package org.gaoming.my;
public class Address {
private String homeAddress;
private String schoolAddress;
public Address()
{
}
public Address(String homeAddress,String schoolAddress)
{
this.homeAddress=homeAddress;
this.schoolAddress=schoolAddress;
}
public String getHomeAddress() {
return homeAddress;
}
public void setHomeAddress(String homeAddress) {
this.homeAddress = homeAddress;
}
public String getSchoolAddress() {
return schoolAddress;
}
public void setSchoolAddress(String schoolAddress) {
this.schoolAddress = schoolAddress;
}
public String toString()
{
return this.homeAddress+" "+this.schoolAddress;
}
}

修改mapper文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<resultMap id="ppersonresult" type="org.gaoming.my.Pperson">
<!--对应分为主键和非主键,在数据库中将id变为主键 -->
<!--主键用id,非主键用result -->
<id property="id" column="id" />
<result property="name" column="name" />
<result property="age" column="age" />
<result property="sex" column="sex" jdbcType="INTEGER"
javaType="boolean" />
<result property="address.homeAddress" column="homeaddress"></result>
<result property="address.schoolAddress" column="schooladdress"></result>
</resultMap>
<select id="queryaddress" parameterType="org.gaoming.my.Pperson"
resultMap="ppersonresult" >
select * from pperson where homeaddress =#{address.homeAddress} or
schooladdress=#{address.schoolAddress}
</select>

修改接口和测试类

1
2
3
4
5
6
7
8
9
Ppermapper p=session.getMapper(Ppermapper.class);
Pperson pp=new Pperson();
Address add=new Address("ls","gz");
pp.setAddress(add);
List<Pperson> list=p.queryaddress(pp);
System.out.println(list);
session.close();
//接口
List<Pperson> queryaddress(Pperson p);

参数为HashMap

将map中的key匹配占位符#{}
修改mapper文件

1
2
3
<select id="queryhash" parameterType="HashMap" resultMap="ppersonresult">
select * from pperson where age=#{age} or name=#{name}
</select>

修改测试类和接口

1
2
3
4
5
6
7
//接口
List<Pperson> queryhash(HashMap<String,Object> map);
HashMap<String,Object> map=new HashMap<>();
map.put("age",20);
map.put("name","kl");
List<Pperson> list=p.queryhash(map);
System.out.println(list);

Mybatis外键约束

https://blog.csdn.net/xiaolizi22233/article/details/54136297

Author: 高明
Link: https://skysea-gaoming.github.io/2020/02/28/Mybatis/
Copyright Notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.