旅游网站开发,h5制作小程序有哪些,中国兼职设计师网,微信小程序打不开Java SSM篇3——Mybatis
1、JDBC存在的问题
数据库连接创建、释放频繁造成系统资源浪费从而影响系统性能sql 语句在代码中硬编码#xff0c;造成代码不易维护#xff0c;实际应用 sql 变化的可能较大#xff0c;sql 变动需要改变java 代码查询操作时#xff0c;需要手动将…Java SSM篇3——Mybatis
1、JDBC存在的问题
数据库连接创建、释放频繁造成系统资源浪费从而影响系统性能sql 语句在代码中硬编码造成代码不易维护实际应用 sql 变化的可能较大sql 变动需要改变java 代码查询操作时需要手动将结果集中的数据手动封装到实体中
2、解决方案
数据库连接池配置文件反射、内省
3、ORM是什么
对象关系映射
O对象模型 实体对象即我们在程序中根据数据库表结构建立的一个个实体javaBeanR关系型数据库的数据结构 关系数据库领域的Relational建立的数据库表M映射 从R数据库到O对象模型的映射可通过XML文件映射
3、Mybatis简介
MyBatis是一个优秀的基于ORM的半自动轻量级持久层框架它对jdbc的操作数据库的过程进行封装 使开发者只需要关注 SQL 本身而不需要花费精力去处理例如注册驱动、创建connection、创建 statement、手动设置参数、结果集检索等jdbc繁杂的过程代码
mybatis 官方文档https://mybatis.org/mybatis-3/ 4、mybatis快速入门
4.1、数据库准备
CREATE TABLE person(id INT PRIMARY KEY,NAME VARCHAR(10),PASSWORD VARCHAR(10)
);
INSERT INTO person VALUES (1,root,123);4.2、创建maven工程pom.xml导入依赖
dependencies!--mybatis--dependencygroupIdorg.mybatis/groupIdartifactIdmybatis/artifactIdversion3.5.5/version/dependency!--mysql--dependencygroupIdmysql/groupIdartifactIdmysql-connector-java/artifactIdversion8.0.20/version/dependency!--junit--dependencygroupIdjunit/groupIdartifactIdjunit/artifactIdversion4.13/versionscopetest/scope/dependency
/dependenciespom设置资源过滤
!--maven过滤问题--
buildresourcesresourcedirectorysrc/main/java/directoryincludesinclude**/*.properties/includeinclude**/*.xml/include/includesfilteringfalse/filtering/resourceresourcedirectorysrc/main/resources/directoryincludesinclude**/*.properties/includeinclude**/*.xml/include/includesfilteringfalse/filtering/resource/resources
/build4.3、数据库配置文件database.properties
drivercom.mysql.cj.jdbc.Driver
urljdbc:mysql://132.232.82.49:3306/test?useUnicodetruecharacterEncodingutf-8useSSLfalseserverTimezone Asia/Shanghai
usernameroot
passwordroot4.4、mybatis配置文件
?xml version1.0 encodingUTF-8 ?
!DOCTYPE configurationPUBLIC -//mybatis.org//DTD Config 3.0//ENhttp://mybatis.org/dtd/mybatis-3-config.dtdconfiguration!--引入properties配置文件--properties resourcedatabase.properties/properties!--别名--typeAliasespackage nameclub.winkto.bean/package/typeAliasesenvironments defaultdevelopmentenvironment iddevelopmenttransactionManager typeJDBC/dataSource typePOOLEDproperty namedriver value${driver}/property nameurl value${url}/property nameusername value${username}/property namepassword value${password}//dataSource/environment/environments!--注册映射文件--mapperspackage nameclub.winkto.mapper//mappers
/configuration4.5、实体类
public class Person {private int id;private String name;private String password;
}4.6、Mapper接口
public interface PersonMapper {ArrayListPerson selectPerson();int insertPerson(Person person);int updatePerson(Person person);int deletePerson(int id);
}4.7、映射文件
?xml version1.0 encodingUTF-8 ?
!DOCTYPE mapperPUBLIC -//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtd
mapper namespaceclub.winkto.mapper.PersonServiceselect idselectPerson resultTypePersonselect * from person;/selectinsert idinsertPerson parameterTypePersoninsert into person values (#{id},#{name},#{password});/insertupdate idupdatePerson parameterTypePersonupdate person set name#{name},password#{password} where id#{id};/updatedelete iddeletePerson parameterTypeintdelete from person where id#{id};/delete
/mapper4.8、mybatis工具类
public class MybatisUtils {private static SqlSessionFactory sqlSessionFactorynull;static{String resource mybatis_config.xml;InputStream inputStream null;try {inputStream Resources.getResourceAsStream(resource);} catch (IOException e) {e.printStackTrace();}sqlSessionFactory new SqlSessionFactoryBuilder().build(inputStream);}public static SqlSession getSqlSession(){return sqlSessionFactory.openSession();}
}
4.9、测试类
public class CRUD {Testpublic void select(){SqlSession sqlSession MybatisUtils.getSqlSession();PersonService mapper sqlSession.getMapper(PersonService.class);ArrayListPerson people mapper.selectPerson();for (Person person : people) {System.out.println(person);}System.out.println();sqlSession.close();}Testpublic void insert(){SqlSession sqlSession MybatisUtils.getSqlSession();PersonService mapper sqlSession.getMapper(PersonService.class);int i mapper.insertPerson(new Person(2, bingbing, 123));if (i0){sqlSession.commit();}sqlSession.close();}Testpublic void update(){SqlSession sqlSession MybatisUtils.getSqlSession();PersonService mapper sqlSession.getMapper(PersonService.class);int i mapper.updatePerson(new Person(2, bingbing, blingbling));if (i0){sqlSession.commit();}sqlSession.close();}Testpublic void delete(){SqlSession sqlSession MybatisUtils.getSqlSession();PersonService mapper sqlSession.getMapper(PersonService.class);int i mapper.deletePerson(2);if (i0){sqlSession.commit();}sqlSession.close();}Testpublic void doCRUD(){select();insert();select();update();select();delete();select();}
}
5、Mybatis配置解析
propertiessettingstypeAliasestypeHandlersobjectFactorypluginsenvironments environment transactionManagerdataSource databaseIdProvidermappers
5.1、properties
5.1.1、直接书写properties内容
propertiesproperty namedriver valuecom.mysql.cj.jdbc.Driver/property nameurl valuejdbc:mysql://132.232.82.49:3306/mybatis?useUnicodetruecharacterEncodingutf-8useSSLfalseserverTimezone Asia/Shanghai/property nameusername valueroot/property namepassword valueroot/
/properties
5.1.2、引入properties文件
properties resourcedatabase.properties/properties
drivercom.mysql.cj.jdbc.Driver
urljdbc:mysql://132.232.82.49:3306/mybatis?useUnicodetruecharacterEncodingutf-8useSSLfalseserverTimezone Asia/Shanghai
usernameroot
passwordroot
5.1.3、优先级问题
如果一个属性在多个地方出现优先级为
在方法体中给定的参数值在类路径或URL资源中读取的属性属性文件中的属性
5.2、settings
官方属性页https://mybatis.org/mybatis-3/configuration.html#settings
settings!--全局启用或禁用在此配置下在任何映射器中配置的任何缓存--setting namecacheEnabled valuetrue/!--全局启用或禁用延迟加载,启用时,将延迟加载所有关系,对于特定关系,可以使用fetchType属性替换该值--setting namelazyLoadingEnabled valuetrue/!--允许或不允许从单个语句返回多个结果集--setting namemultipleResultSetsEnabled valuetrue/!--使用列标签而不是列名--setting nameuseColumnLabel valuetrue/!--允许JDBC支持生成的密钥--setting nameuseGeneratedKeys valuefalse/setting nameautoMappingBehavior valuePARTIAL/setting nameautoMappingUnknownColumnBehavior valueWARNING/setting namedefaultExecutorType valueSIMPLE/!--驱动程序等待数据库响应的秒数--setting namedefaultStatementTimeout value25/setting namedefaultFetchSize value100/setting namesafeRowBoundsEnabled valuefalse/setting namemapUnderscoreToCamelCase valuefalse/setting namelocalCacheScope valueSESSION/setting namejdbcTypeForNull valueOTHER/setting namelazyLoadTriggerMethods valueequals,clone,hashCode,toString/!--日志方式--setting namelogImpl valueSTDOUT_LOGGING/
/settings
5.3、typeAliases
5.3.1、设置单个
typeAliasestypeAlias typecom.yan.po.User aliasuser/
/typeAliases
5.3.2、设置包
typeAliasespackage namecom.ruoye.bean/package
/typeAliases
5.3.3、Mybatis设置好的类型别名
别名类型_bytebyte_longlong_shortshort_intint_integerint_doubledouble_floatfloat_booleanbooleanstringStringbyteBytelongLongshortShortintIntegerintegerIntegerdoubleDoublefloatFloatbooleanBooleandateDatedecimalBigDecimalbigdecimalBigDecimalobjectObjectmapMaphashmapHashMaplistListarraylistArrayListcollectionCollectioniteratorIterator
5.4、typeHandlers类型句柄
可以重写类型句柄或者是创建你自己的方式来处理不支持或者是非标准的类型只需要简单地实现org.mybaits.type包里的TypeHandler,并且映射到一个JAVA类型然后再选定一个JDBC类型
5.4.1、定义类型
typeHandlerstypeHandler handlerRuoyeTypeHandler/
/typeHandlers
5.4.2、实现TypeHandler
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 RuoyeTypeHandlerT implements TypeHandlerT {public void setParameter(PreparedStatement preparedStatement, int i, T t, JdbcType jdbcType) throws SQLException {preparedStatement.setString(i, (String) t);}public T getResult(ResultSet resultSet, String s) throws SQLException {return null;}public T getResult(ResultSet resultSet, int i) throws SQLException {return null;}public T getResult(CallableStatement callableStatement, int i) throws SQLException {return null;}
}
5.4.3、使用
resultMap type id result column property typeHandlerRuoyeTypeHandler/
/resultMap
5.5、ObjectFactory
5.5.1、定义
ObjectFactory typeRuoyeObjectFactoryproperty name value/
/ObjectFactory
5.5.2、继承DefaultObjectFactory
import org.apache.ibatis.reflection.factory.DefaultObjectFactory;import java.util.List;
import java.util.Properties;public class RuoyeObjectFactory extends DefaultObjectFactory {Overridepublic T T create(ClassT type) {return super.create(type);}Overridepublic T T create(ClassT type, ListClass? constructorArgTypes, ListObject constructorArgs) {return super.create(type, constructorArgTypes, constructorArgs);}public void setProperties(Properties properties) {}
}
5.6、plugins
pluginsplugin interceptorcom.github.pagehelper.PageHelper!-- 设置数据库类型 Oracle,Mysql,MariaDB,SQLite,Hsqldb,PostgreSQL六种数据库-- property namedialect valuemysql//plugin
/plugins
5.7、environments
虽然你可以配置多重环境但是你只可以选择一对一的SqlsessionFactory实例
environments defaultformalenvironment iddevelopmenttransactionManager typeJDBC/dataSource typePOOLEDproperty namedriver value${driver}/property nameurl value${url}/property nameusername value${username}/property namepassword value${password}//dataSource/environmentenvironment idformaltransactionManager typeJDBC/dataSource typePOOLEDproperty namedriver value${driver}/property nameurl value${url}/property nameusername value${username}/property namepassword value${password}//dataSource/environment
/environments
5.8、Mappers
5.8.1、单个映射
mappersmapper resourceclub/winkto/mapper/PersonMapper.xml/
/mappers
mappers!--路径以.分隔--mapper classclub.winkto.mapper.PersonMapper/mapper
/mappers
5.8.2、包映射
mapperspackage nameclub.winkto.mapper/
/mappers
6、注解开发初步认识
不再书写映射文件PersonMapper.xml
public interface PersonMapper {Select(select * from person)ArrayListPerson selectPerson();Insert(insert into person values (#{id},#{name},#{password}))int insertPerson(Person person);Update(update person set name#{name},password#{password} where id#{id})int updatePerson(Person person);Delete(delete from person where id#{id})int deletePerson(Param(id) int id);
}
注册class文件当然你也可以注册包
mappers!--路径以/分隔--mapper classcom.lancame.mapper.PersonService/mapper
/mappers
7、结果集映射初步认识
resultType如果实体的属性名与表中字段名一致将查询结果自动封装到实体类中
select idselectPerson resultTypePersonselect * from person;
/select
resutlMap如果实体的属性名与表中字段名不一致可以使用ResutlMap实现手动封装到实体类中
如果有查询结果有 字段与属性是对应的可以省略手动封装
resultMap idPersonMap typePerson!--id常用于主键列--id columnid propertyid /!--result用于其他列--result columnname propertyname /result columnpassword propertypassword /
/resultMap
select idselectPerson resultMapPersonMapselect * from person;
/select
8、参数传递
8.1、封装map传递
select idselectPerson1 parameterTypemap resultTypePersonselect * from person where name#{name} and password#{password};
/select
public interface PersonMapper {ArrayListPerson selectPerson1(Map map);
}
Test
public void select1(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapper mapper sqlSession.getMapper(PersonMapper.class);HashMapString, Object hashMap new HashMapString, Object();hashMap.put(name,root);hashMap.put(password,123);ArrayListPerson people mapper.selectPerson1(hashMap);for (Person person : people) {System.out.println(person);}System.out.println();sqlSession.close();
}
8.2、注解传递
select idselectPerson1 resultTypePersonselect * from person where name#{name} and password#{password};
/select
public interface PersonMapper {ArrayListPerson selectPerson1(Param(name) String name,Param(password) String password);
}
Test
public void select1(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapper mapper sqlSession.getMapper(PersonMapper.class);ArrayListPerson people mapper.selectPerson1(root,123);for (Person person : people) {System.out.println(person);}System.out.println();sqlSession.close();
}
9、模糊查询
9.1、${} 与 #{} 通过 #{} 可以实现preparedStatement向占位符中设置值自动进行java类型和jdbc类型转换# {}可以有效防止sql注入 通过 ${} 可以将parameterType 传入的内容拼接在sql中且不进行jdbc类型转换会出现sql注入问题
9.2、模糊查询操作
select idselectPerson2 parameterTypestring resultTypePersonselect * from person where name like %#{name}%
/select
public interface PersonMapper {ArrayListPerson selectPerson2(String name);
}
Test
public void select2(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapper mapper sqlSession.getMapper(PersonMapper.class);ArrayListPerson people mapper.selectPerson2(roo);for (Person person : people) {System.out.println(person);}System.out.println();sqlSession.close();
}
9.3、返回主键
9.3.1、useGeneratedKeys
只适用于主键自增的数据库mysql和sqlserver支持oracle不行
insert idinsertPerson1 parameterTypePerson useGeneratedKeystrue keyPropertyidinsert into person (name,password) values (#{name},#{password});
/insert
public interface PersonMapper {int insertPerson1(Person person);
}
Test
public void insert1(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapper mapper sqlSession.getMapper(PersonMapper.class);Person bingbing new Person(0, bingbing, 123);int i mapper.insertPerson1(bingbing);System.out.println(bingbing.getId());if (i0){sqlSession.commit();}sqlSession.close();
}
9.3.2、selectKey
适用范围广支持所有类型数据库
insert idinsertPerson2 parameterTypePersoninsert into person (name,password) values (#{name},#{password});selectKey keyColumnid keyPropertyid resultTypeint orderAFTERSELECT LAST_INSERT_ID();/selectKey
/insert
public interface PersonMapper {int insertPerson1(Person person);
}
Test
public void insert2(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapper mapper sqlSession.getMapper(PersonMapper.class);Person bingbing new Person(0, bingbing, 123);int i mapper.insertPerson1(bingbing);System.out.println(bingbing.getId());if (i0){sqlSession.commit();}sqlSession.close();
}
10、动态SQL
当我们要根据不同的条件来执行不同的sql语句的时候需要用到动态sql
10.1、where、if
select idselectPerson3 resultTypePersonselect * from personwhereif testname!nulland name#{name}/ifif testpassword!nulland password#{password}/if/where
/select
public interface PersonMapper {ArrayListPerson selectPerson3(Param(name) String name,Param(password) String password);
}
Test
public void select3(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapper mapper sqlSession.getMapper(PersonMapper.class);ArrayListPerson people mapper.selectPerson3(null,123);for (Person person : people) {System.out.println(person);}System.out.println();sqlSession.close();
}
10.2、set、if
update idupdatePerson1 parameterTypePersonupdate personsetif testname!nullname#{name},/ifif testpassword!nullpassword#{password},/if/setwhere id#{id};
/update
public interface PersonMapper {int updatePerson1(Person person);
}
Test
public void update1(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapper mapper sqlSession.getMapper(PersonMapper.class);int i mapper.updatePerson1(new Person(2, bingbing, blingbling));if (i0){sqlSession.commit();}sqlSession.close();
}
10.3、Choose、when、otherwise
参考switch
select idselectPerson4 resultTypePersonselect * from personwherechoosewhen testname!nulland name#{name}/whenotherwiseand password123/otherwise/choose/where
/select
public interface PersonMapper {ArrayListPerson selectPerson3(Param(name) String name,Param(password) String password);
}
Test
public void select4(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapper mapper sqlSession.getMapper(PersonMapper.class);ArrayListPerson people mapper.selectPerson4(null,123);for (Person person : people) {System.out.println(person);}System.out.println();sqlSession.close();
}
10.4、foreach
select idselectPerson5 resultTypePersonselect * from personwhereid inforeach collectionlist open( close) itemid separator,#{id}/foreach/where
/select
public interface PersonMapper {;ArrayListPerson selectPerson5(ArrayListInteger arrayList);
}
Test
public void select5(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapper mapper sqlSession.getMapper(PersonMapper.class);ArrayListInteger integers new ArrayListInteger(Arrays.asList(1,5,6));ArrayListPerson people mapper.selectPerson5(integers);for (Person person : people) {System.out.println(person);}System.out.println();sqlSession.close();
}
11、SQL片段
update idupdatePerson1 parameterTypePersonupdate personsetinclude refidwhereif/include/setwhere id#{id};
/update
sql idwhereifif testname!nullname#{name},/ifif testpassword!nullpassword#{password},/if
/sql
public interface PersonMapper {int updatePerson1(Person person);
}
Test
public void update1(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapper mapper sqlSession.getMapper(PersonMapper.class);int i mapper.updatePerson1(new Person(2, bingbing, blingbling));if (i0){sqlSession.commit();}sqlSession.close();
}
12、分页插件
12.1、依赖导入
dependencygroupIdcom.github.pagehelper/groupIdartifactIdpagehelper/artifactIdversion3.7.5/version
/dependency
dependencygroupIdcom.github.jsqlparser/groupIdartifactIdjsqlparser/artifactIdversion0.9.1/version
/dependency
12.2、mybatis配置分页
plugins!-- 分页助手的插件 --plugin interceptorcom.github.pagehelper.PageHelper!-- 指定方言 --property namedialect valuemysql//plugin
/plugins
12.3、测试
Test
public void select6(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapper mapper sqlSession.getMapper(PersonMapper.class);PageHelper.startPage(1,2);ArrayListPerson people mapper.selectPerson();for (Person person : people) {System.out.println(person);}System.out.println();PageInfoPerson pageInfo new PageInfoPerson(people);System.out.println(总条数pageInfo.getTotal());System.out.println(总页数pageInfo.getPages());System.out.println(当前页pageInfo.getPageNum());System.out.println(每页显示长度pageInfo.getPageSize());System.out.println(是否第一页pageInfo.isIsFirstPage());System.out.println(是否最后一页pageInfo.isIsLastPage());sqlSession.close();
}
13、多表查询根据结果嵌套
一对一人与身份证号的关系一对多用户与订单的关系一个用户可以有多个订单但是一个订单只能有一个用户多对多用户与课程的关系一个用户可以选多个课程一个课程可以被多个用户选择
13.1、一对一多对一
13.1.1、数据库准备
CREATE TABLE person(pid INT PRIMARY KEY AUTO_INCREMENT,pname VARCHAR(20),ppassword VARCHAR(20)
)
CREATE TABLE idnum(pid INT,idnum VARCHAR(18)
)
ALTER TABLE ID ADD CONSTRAINT t2 FOREIGN KEY (pid) REFERENCES person(pid);
13.1.2、实体类
public class IDNum {private int pid;private String idnum;
}
public class Person {private int pid;private String pname;private String ppassword;private IDNum idnum;
}
13.1.3、接口
public interface PersonMapper {ListPerson selectPersonWithID();
}
13.1.4、映射文件 一对一使用association标签关联 property“user” 封装实体的属性名 javaType“user” 封装实体的属性类型
resultMap idPersonIDMap typePersonid columnpid propertypid /result columnpname propertypname /result columnppassword propertyppassword /association propertyidnum javaTypeIDNumid columnpid propertypid /result columnidnum propertyidnum //association
/resultMap
select idselectPersonWithID resultMapPersonIDMapselect * from person,idnum where person.pididnum.pid
/select
13.1.5、测试
Test
public void test(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapper mapper sqlSession.getMapper(PersonMapper.class);ListPerson people mapper.selectPersonWithID();for (Person person : people) {System.out.println(person);}
}
13.2、一对多
13.2.1、数据库准备
CREATE TABLE person(pid INT PRIMARY KEY AUTO_INCREMENT,pname VARCHAR(20),ppassword VARCHAR(20)
)
CREATE TABLE orders(pid INT,oid INT PRIMARY KEY AUTO_INCREMENT,otime DATETIME,oremark VARCHAR(100)
)
ALTER TABLE orders ADD CONSTRAINT t1 FOREIGN KEY (pid) REFERENCES person(pid);
随便加几条数据
13.2.2、实体类
public class Person {private int pid;private String pname;private String ppassword;private ListOrder orders;
}
public class Order {private int pid;private int oid;private Date date;private String oremark;
}
13.2.3、接口
public interface PersonMapper {ListPerson selectPersonWithOrder();
}
13.2.4、映射文件
一对多使用collection标签关联property“orderList” 封装到集合的属性名ofType“order” 封装集合的泛型类型
resultMap idPersonOrderMap typePersonid columnpid propertypid /result columnpname propertypname /result columnppassword propertyppassword /collection propertyorders ofTypeOrderid columnoid propertyoid /result columnpid propertypid /result columnotime propertyotime /result columnoremark propertyoremark //collection
/resultMap
select idselectPersonWithOrder resultMapPersonOrderMapSELECT * FROM person LEFT JOIN orders ON person.pidorders.pid
/select
13.2.5、测试
Test
public void test1(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapper mapper sqlSession.getMapper(PersonMapper.class);ListPerson people mapper.selectPersonWithOrder();for (Person person : people) {System.out.println(person);}
}
13.3、多对多
13.3.1、数据库准备
CREATE TABLE person(pid INT PRIMARY KEY AUTO_INCREMENT,pname VARCHAR(20),ppassword VARCHAR(20)
)
CREATE TABLE course(cid INT PRIMARY KEY AUTO_INCREMENT,cname VARCHAR(20)
);
CREATE TABLE person_course(pid INT,cid INT
);
ALTER TABLE person_course ADD CONSTRAINT t3 FOREIGN KEY (pid) REFERENCES person(pid);
ALTER TABLE person_course ADD CONSTRAINT t4 FOREIGN KEY (cid) REFERENCES course(cid);
随便加几条数据
13.3.2、实体类
public class Person {private int pid;private String pname;private String ppassword;private ListOrder orders;
}
public class Course {private int cid;private String cname;
}
13.3.3、接口
public interface PersonMapper {ListPerson selectPersonWithCourse();
}
13.3.4、映射文件
一对多使用collection标签关联property“orderList” 封装到集合的属性名ofType“order” 封装集合的泛型类型
resultMap idPersonCourseMap typePersonid columnpid propertypid /result columnpname propertypname /result columnppassword propertyppassword /collection propertycourses ofTypeCourseid columncid propertycid /result columncname propertycname //collection
/resultMap
select idselectPersonWithCourse resultMapPersonCourseMapSELECT * FROM personLEFT JOIN person_course ON person.pidperson_course.pidLEFT JOIN course ON person_course.cidcourse.cid
/select
13.3.5、测试
Test
public void test2(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapper mapper sqlSession.getMapper(PersonMapper.class);ListPerson people mapper.selectPersonWithCourse();for (Person person : people) {System.out.println(person);}
}
14、多表查询根据查询嵌套
14.1、一对一多对一
14.1.1、数据库准备
CREATE TABLE person(pid INT PRIMARY KEY AUTO_INCREMENT,pname VARCHAR(20),ppassword VARCHAR(20)
)
CREATE TABLE idnum(pid INT,idnum VARCHAR(18)
)
ALTER TABLE ID ADD CONSTRAINT t2 FOREIGN KEY (pid) REFERENCES person(pid);
14.1.2、实体类
public class IDNum {private int pid;private String idnum;
}
public class Person {private int pid;private String pname;private String ppassword;private IDNum idnum;
}
14.1.3、接口
public interface PersonMapper {ListPerson selectPersonWithID();
}
14.1.4、映射文件 一对一使用association标签关联 property“user” 封装实体的属性名 javaType“user” 封装实体的属性类型 column传递的参数 select调用的查询语句
resultMap idPersonIDMap typePersonid columnpid propertypid /result columnpname propertypname /result columnppassword propertyppassword /association propertyidnum javaTypeIDNum columnpid selectselectID/association
/resultMap
select idselectPersonWithID resultMapPersonIDMapselect * from person
/select
select idselectID resultTypeIDNumselect * from idnum where idnum.pid#{pid}
/select
14.1.5、测试
Test
public void test(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapper mapper sqlSession.getMapper(PersonMapper.class);ListPerson people mapper.selectPersonWithID();for (Person person : people) {System.out.println(person);}
}
14.2、一对多
14.2.1、数据库准备
CREATE TABLE person(pid INT PRIMARY KEY AUTO_INCREMENT,pname VARCHAR(20),ppassword VARCHAR(20)
)
CREATE TABLE orders(pid INT,oid INT PRIMARY KEY AUTO_INCREMENT,otime DATETIME,oremark VARCHAR(100)
)
ALTER TABLE orders ADD CONSTRAINT t1 FOREIGN KEY (pid) REFERENCES person(pid);
随便加几条数据
14.2.2、实体类
public class Person {private int pid;private String pname;private String ppassword;private ListOrder orders;
}
public class Order {private int pid;private int oid;private Date date;private String oremark;
}
14.2.3、接口
public interface PersonMapper {ListPerson selectPersonWithOrder();
}
14.2.4、映射文件
一对多使用collection标签关联property“orderList” 封装到集合的属性名ofType“order” 封装集合的泛型类型column传递的参数select调用的查询语句
resultMap idPersonOrderMap typePersonid columnpid propertypid /result columnpname propertypname /result columnppassword propertyppassword /collection propertyorders ofTypeOrder columnpid selectselectOrder/collection
/resultMap
select idselectPersonWithOrder resultMapPersonOrderMapSELECT * FROM person
/select
select idselectOrder parameterTypeint resultTypeOrderSELECT * FROM orders where orders.pid#{pid}
/select
14.2.5、测试
Test
public void test1(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapper mapper sqlSession.getMapper(PersonMapper.class);ListPerson people mapper.selectPersonWithOrder();for (Person person : people) {System.out.println(person);}
}
14.3、多对多
14.3.1、数据库准备
CREATE TABLE person(pid INT PRIMARY KEY AUTO_INCREMENT,pname VARCHAR(20),ppassword VARCHAR(20)
)
CREATE TABLE course(cid INT PRIMARY KEY AUTO_INCREMENT,cname VARCHAR(20)
);
CREATE TABLE person_course(pid INT,cid INT
);
ALTER TABLE person_course ADD CONSTRAINT t3 FOREIGN KEY (pid) REFERENCES person(pid);
ALTER TABLE person_course ADD CONSTRAINT t4 FOREIGN KEY (cid) REFERENCES course(cid);
随便加几条数据
14.3.2、实体类
public class Person {private int pid;private String pname;private String ppassword;private ListOrder orders;
}
public class Course {private int cid;private String cname;
}
14.3.3、接口
public interface PersonMapper {ListPerson selectPersonWithCourse();
}
14.3.4、映射文件
一对多使用collection标签关联property“orderList” 封装到集合的属性名ofType“order” 封装集合的泛型类型column传递的参数select调用的查询语句
resultMap idPersonCourseMap typePersonid columnpid propertypid /result columnpname propertypname /result columnppassword propertyppassword /collection propertycourses ofTypeCourse columncid selectselectCourse/collection
/resultMap
select idselectPersonWithCourse resultMapPersonCourseMapSELECT * FROM personLEFT JOIN person_course ON person.pidperson_course.pid
/select
select idselectCourse parameterTypeint resultTypeCourseselect * from course where course.cid#{cid}
/select
14.3.5、测试
Test
public void test2(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapper mapper sqlSession.getMapper(PersonMapper.class);ListPerson people mapper.selectPersonWithCourse();for (Person person : people) {System.out.println(person);}
}
15、加载策略
就是在需要用到数据时才进行加载不需要用到数据时就不加载数据延迟加载也称懒加载
延迟加载是基于查询嵌套来实现的
15.1、优点
先从单表查询需要时再从关联表去关联查询大大提高数据库性能因为查询单表要比关联查询多张表 速度要快
15.2、缺点
因为只有当需要用到数据时才会进行数据库查询这样在大批量数据查询时因为查询工作也要消耗时 间所以可能造成用户等待时间变长造成用户体验下降
15.3、开启局部延迟加载
在association和collection标签中都有一个fetchType属性通过修改它的值可以修改局部的加载策略
fetchType“lazy” 懒加载策略fetchType“eager” 立即加载策略
resultMap idPersonCourseMap typePersonid columnpid propertypid /result columnpname propertypname /result columnppassword propertyppassword /collection propertycourses ofTypeCourse columncid selectselectCourse fetchTypelazy/collection
/resultMap
select idselectPersonWithCourse resultMapPersonCourseMapSELECT * FROM personLEFT JOIN person_course ON person.pidperson_course.pid
/select
select idselectCourse parameterTypeint resultTypeCourseselect * from course where course.cid#{cid}
/select
15.4、延迟加载触发策略
在配置了延迟加载策略后发现即使没有调用关联对象的任何方法但是在你调用当前对象的 equals、clone、hashCode、toString方法时也会触发关联对象的查询可以在配置文件中使用lazyLoadTriggerMethods配置项覆盖掉上面四个方法
settingssetting namelazyLoadTriggerMethods valuetoString()/
/settings
15.5、全局延迟加载
局部的加载策略优先级高于全局的加载策略
settingssetting namelazyLoadingEnabled valuetrue/
/settings
16、缓存
通过缓存策略来减少数据库的查询次数 从而提高性能
16.1、一级缓存
一级缓存是SqlSession级别的缓存是默认开启的
在参数和SQL完全一样的情况下使用同一个SqlSession对象调用一个Mapper方法往往只执行一次SQL因为使用SelSession第一次查询后MyBatis会将其放在缓存中以后再查询的时 候如果没有声明需要刷新并且缓存没有超时的情况下SqlSession都会取出当前缓存的数据而不 会再次发送SQL到数据库 一级缓存是SqlSession范围的缓存执行SqlSession的C增加U更新D删除操作或者调 用clearCache()、commit()、close()方法都会清空缓存
强制清空一级缓存
sqlSession.clearCache();
设置每次清空一级缓存
select flushCachetrue/select
16.2、二级缓存
二级缓存是namspace级别跨sqlSession的缓存是默认不开启的
二级缓存的开启需要进行配置实现二级缓存的时候MyBatis要求返回的POJO必须是可序列化的。 也就是要求实现Serializable接口配置方法很简单只需要在映射XML文件配置 就可以开启 二级缓存了
二级缓存是mapper映射级别的缓存多个SqlSession去操作同一个Mapper映射的sql语句多个 SqlSession可以共用二级缓存二级缓存是跨SqlSession的
mybatis的二级缓存因为是namespace级别所以在进行多表查询时会产生脏读问题 16.2.1、mybatis核心配置开启二级缓存默认开启此步可以省略
settingssetting namecacheEnabled valuetrue/
/settings
16.2.2、在mapper文件中加入
cache/cache
缓存参数配置
cache evictionFIFOflushInterval60000size512readOnlytrue/
16.2.3、在select语句上开启二级缓存
标签中设置useCache”true”代表当前这个statement要使用二级缓存
select useCachetrue/select
16.2.4、修改实体类
继承Serializable
17、注解深度认识
之前在映射文件中通过配置、、来实现复杂关系映射
使用注解开发后我们可以使用 Results、ResultOne、Many 注解组合完成复杂关系的配置
17.1、根据结果嵌套
我们直接来看根据结果嵌套看完这个懂了
17.2、根据查询嵌套
17.2.1、一对一
接口
Select(select * from person)
Results({Result(column pid,property pid,id true),Result(column pname,property pname),Result(column ppassword,property ppassword),Result(column pid,property idnum,javaType IDNum.class,one One(select club.winkto.mapper.PersonMapperAnno.selectIDNum,fetchType FetchType.EAGER))
})
Select(select * from idnum where pid#{pid})
IDNum selectIDNum(int pid);
测试
Test
public void test3(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapperAnno mapper sqlSession.getMapper(PersonMapperAnno.class);ListPerson people mapper.selectPersonWithID();for (Person person : people) {System.out.println(person);}
}
17.2.2、一对多
接口
Select(select * from person)
Results({Result(column pid,property pid,id true),Result(column pname,property pname),Result(column ppassword,property ppassword),Result(column pid,property orders,javaType List.class,many Many(select club.winkto.mapper.PersonMapperAnno.selectOrder,fetchType FetchType.EAGER))
})
ListPerson selectPersonWithOrder();
Select(select * from orders where pid#{pid})
ListOrder selectOrder(int pid);
测试
Test
public void test4(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapperAnno mapper sqlSession.getMapper(PersonMapperAnno.class);ListPerson people mapper.selectPersonWithOrder();for (Person person : people) {System.out.println(person);}
}
17.2.3、多对多
接口
Select(select * from person)
Results({Result(column pid,property pid,id true),Result(column pname,property pname),Result(column ppassword,property ppassword),Result(column pid,property courses,javaType List.class,many Many(select club.winkto.mapper.PersonMapperAnno.selectCourse,fetchType FetchType.EAGER))
})
ListPerson selectPersonWithCourse();
Select(select * from course,person_course where person_course.pid#{pid} and person_course.cidcourse.cid)
ListCourse selectCourse(int pid);
测试
Test
public void test5(){SqlSession sqlSession MybatisUtil.getSqlSession();PersonMapperAnno mapper sqlSession.getMapper(PersonMapperAnno.class);ListPerson people mapper.selectPersonWithCourse();for (Person person : people) {System.out.println(person);}
}
17.3、注解开启二级缓存
在接口上使用CacheNamespace即可
17.4、注解开启延迟加载
fetchType FetchType.LAZYfetchType FetchType.EAGERfetchType FetchType.DEFAULT采用全局配置
