参考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(){
} }
|
通过mapper.xml文件创建表和类的映射关系,通过id值区分标签,resultType
是返回类型,namespace放映射文件的路径,parameterType是动态传入的参
数类型
1 2 3 4 5 6 7 8 9 10 11
| <?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 { Reader reader= Resources.getResourceAsReader("conf.xml"); SqlSessionFactory sessionFactory=new SqlSessionFactoryBuilder().build(reader); SqlSession session=sessionFactory.openSession(); String statement="org.gaoming.mapper.PersonMapper.querybyid"; 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"; 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); 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 { Pperson querybyid(int id); List<Pperson> queryall(); 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"); 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> { public void setNonNullParameter(PreparedStatement preparedStatement , int i, Boolean o, JdbcType jdbcType) throws SQLException { if(o) preparedStatement.setInt(i,1); else preparedStatement.setInt(i,0); } @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 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
- 输入参数的类型如果是简单类型(8个基本类型+string) #{xx}xx可以任意写
- 也可以使用${value},简单类型必须写value
- 输入参数是对象类型,#{属性名} ${属性名}
两种符号的区别
- 井号会自动给string类型加上’’,使用#自动加’’相当于常量排序实际无效,根据的是字段
- $ 原样输出,也就是说如果参数是string要写为 ‘${value}’,但是$适合动态排序
- #{}可以防止sql注入,${}不可以
- 模糊查询时#必须存入参数时就写%,而$可以写为 ‘%${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 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