Mybatis
1.Mybatis简介
1.1 JDBC / DBUtils 遇到的问题总结
- 数据库的连接,释放造成资源的浪费。可以使用数据库的连接池解决此问题。
- sql 编码是硬编码,造成不易维护。SQL语句变化我们要在java的源码中进行变动。
- 使用pareparestatement进行sql语句的预编译,使用占位符,也是硬编码。修改起来不容易,维护也不容易。
- 对数据的结果集存在硬编码。维护部容易。
1.2 Mybatis 简介
MyBatis 本是apache的一个开源项目iBatis, 2010年这个项目由apache software foundation 迁移到了google code,并且改名为MyBatis 。2013年11月迁移到Github。
iBATIS一词来源于“internet”和“abatis”的组合,是一个基于Java的持久层框架。iBATIS提供的持久层框架包括SQL Maps和Data Access Objects(DAOs)
当前,最新版本是MyBatis 3.5.1 ,其发布时间是2019年4月8日
1.3 mybatis 框架执行流程
2.Mybatis_HelloWorld
2.1 导入jar包
- mybatis-3.5.1
- mysql-connection.5.1.7
- junit-4.12
- hamcrest-core-1.3
- JDK1.8(环境)
- 项目结构
- src
- bean
- Teacher.java
- dao
- impl
- TeacherDaoImpl.java
- TeacherDao.java
- TeacherDao.xml
- impl
- utils
- MybatisUtils
- test
- TeacherTest
- bean
- MybatisConfig.xml
- log4j.properties
- src
2.2 配置Mybatis 的核心配置文件
在项目的类路径下配置(classpath路径下)MybatisConfig.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>
<!--开发环境-->
<environments default="development">
<environment id="development">
<!--事务管理为JDBC-->
<transactionManager type="JDBC"/>
<!--数据源为POOLED-->
<dataSource type="POOLED">
<!--属性 键值对配置-->
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/huadian"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!--接口的映射文件-->
<mappers>
<!--映射资源的全路径-->
<mapper resource="com/huadian/dao/TeacherDao.xml"/>
</mappers>
</configuration>
2.3 配置mybatis 的映射文件
- 新建数据库表为teacher
- 添加字段为:
- tid int (11) primary key auto_increment
- tname varchar(50)
- tage int (11)
- taddress varchar(50)
- 构建实体类Teacher
public class Teacher {
private int tId;
private String tName;
private int tAge;
private String tAddress;
public Teacher() {
}
public Teacher(String tName, int tAge, String tAddress) {
this.tName = tName;
this.tAge = tAge;
this.tAddress = tAddress;
}
public Teacher(int tId, String tName, int tAge, String tAddress) {
this.tId = tId;
this.tName = tName;
this.tAge = tAge;
this.tAddress = tAddress;
}
public int gettId() {
return tId;
}
public void settId(int tId) {
this.tId = tId;
}
public String gettName() {
return tName;
}
public void settName(String tName) {
this.tName = tName;
}
public int gettAge() {
return tAge;
}
public void settAge(int tAge) {
this.tAge = tAge;
}
public String gettAddress() {
return tAddress;
}
public void settAddress(String tAddress) {
this.tAddress = tAddress;
}
@Override
public String toString() {
return "Teacher{" +
"tId=" + tId +
", tName='" + tName + '\'' +
", tAge=" + tAge +
", tAddress='" + tAddress + '\'' +
'}';
}
}
- 构建实体业务层的接口TeacherDao
public interface TeacherDao {
List<Teacher> selectAll();
Teacher selectById(Teacher teacher);
List<Teacher> selectByName(Teacher teacher);
int add(Teacher teacher);
int delete(Teacher teacher);
int update(Teacher teacher);
}
- 构建接口的实现类TeacherDaoImpl
public class TeacherDaoImpl implements TeacherDao {
//获取SqlSession
SqlSession sqlSession = MyBatisUtls.getSqlSession();
@Override
public List<Teacher> selectAll() {
List<Teacher> tlist= sqlSession.selectList("test.selectAll",Teacher.class);
return tlist;
}
@Override
public Teacher selectById(Teacher teacher) {
Teacher teacher1= sqlSession.selectOne("test.selectById",teacher.gettId());
return teacher1;
}
@Override
public int add(Teacher teacher) {
//事务没有进行自动提交 在sql语句执行成功后进行手动提交
int result = sqlSession.update("test.add", teacher);
sqlSession.commit();
return result;
}
@Override
public int delete(Teacher teacher) {
int result = sqlSession.delete("test.delete",teacher.gettId());
sqlSession.commit();
return result;
}
@Override
public int update(Teacher teacher) {
int result = sqlSession.update("test.update",teacher);
sqlSession.commit();
return result;
}
@Override
public List<Teacher> selectByName(Teacher teacher) {
List<Teacher> tlist = sqlSession.selectList("test.selectByName",teacher);
return tlist;
}
}
- 构建接口方法的映射文件TeacherDao.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="test">
<!--查询所有-->
<select id="selectAll" resultType="com.huadian.bean.Teacher">
select * from teacher
</select>
<!--根据id查询-->
<!--数据库的字段和类属性字段名字不一致-->
<select id="selectById" parameterType="int" resultType="com.huadian.bean.Teacher">
select * from teacher where tid = #{tid}
</select>
<!--添加数据-->
<!-- 因为我们数据库主键设置了自增所以我们不需要去查询上次自增后的主键-->
<!-- <insert id="add" parameterType="com.huadian.bean.Teacher">
<selectKey resultType="int" order="BEFORE" keyProperty="tid">
select LAST_INSERT_ID()
</selectKey>
insert into teacher (tid,tname,tage,taddress)values(#{tid},#{tname},#{tage},#{tadress})
</insert>-->
<insert id="add" parameterType="com.huadian.bean.Teacher" >
insert into teacher (tname,tage,taddress) values (#{tName},#{tAge},#{tAddress})
</insert>
<!--删除-->
<delete id="delete" parameterType="int" >
delete from teacher where tid =#{tid}
</delete>
<!--修改-->
<update id="update" parameterType="com.huadian.bean.Teacher" >
update teacher set tname=#{tName},tage=#{tAge},taddress=#{tAddress} where tid=#{tId}
</update>
<!--模糊查询-->
<select id="selectByName" parameterType="com.huadian.bean.Teacher" resultType="com.huadian.bean.Teacher">
select * from teacher where tname like '%${tName}%'
</select>
</mapper>
#和$ 符的区别:
#表示占位符通过praparedStatement进行预编译,在通过{值}进行值的传递,自动的进行java类型和jdbc类型的转换,#{}可以有效的防止sql注入。
。#{} 还可以接收简单的数值类型,如果paramaterType 是简单的数据类型那么#{变量名}获取到的就是他的值。如果paramaterType是对象#{属性名}获取到的是对象的属性值
表 示 拼 接 s q l 语 句 , 通 过 {} 表示拼接sql语句,通过 表示拼接sql语句,通过{} 可以将paramaterType 传入的参数拼接在sql 语句中,并且不进行jdbc类型的转换。${} 中只能是值。
2.4 写Mybatis的核心工具类(SqlSession)
MybatisUtils工具类
public class MyBatisUtls {
/*读取mybatis的核心配置文件 ,建立sqlSessionFactory*/
/*单例的设计模式*/
private static SqlSessionFactory sqlSessionFactory = null;
static {
try {
/*读取核心配置文件,获取相对应的输入流*/
InputStream in = Resources.getResourceAsStream("MybatisConfig.xml");
/*建立工工厂*/
sqlSessionFactory = new SqlSessionFactoryBuilder().build(in);
}catch (IOException e){
e.printStackTrace();
}
}
//获取SqlSession sqlSession 是Mybatis框架的核心对象
public static SqlSession getSqlSession(){
return sqlSessionFactory.openSession();
}
}
2.5 为了方便查看执行过程我们需要配置日志文件
- 导入mybatis的日志的jar包
- ant-1.9.6.jar
- ant-launcher-1.9.6.jar
- asm-5.2.jar
- cglib-3.2.5.jar
- commons-logging-1.2.jar
- javassist-3.22.0-CR2.jar
- log4j-1.2.17.jar
- log4j-api-2.3.jar
- log4j-core-2.3.jar
- ognl-3.1.15.jar
- slf4j-api-1.7.25.jar
- slf4j-log4j12-1.7.25.jar
- log.properties
# Global logging configuration
# 调控日志输出的级别
log4j.rootLogger=DEBUG, stdout
# MyBatis logging configuration...
log4j.logger.com.huadian.TeacherDao=TRACE
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
# 日志的输出格斯
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
- 控制台输出的日志信息
DEBUG [main] - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - PooledDataSource forcefully closed/removed all connections.
DEBUG [main] - Opening JDBC Connection
DEBUG [main] - Created connection 1004095028.
DEBUG [main] - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@3bd94634]
DEBUG [main] - ==> Preparing: select * from teacher where tname like '%JAVA%'
DEBUG [main] - ==> Parameters:
DEBUG [main] - <== Total: 1
Teacher{tId=1, tName='JAVA', tAge=26, tAddress='USA'}
2.6 起别名
<?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>
<!--别名机制 给Teacher类起别名-->
<typeAliases>
<typeAlias type="com.huadian.bean.Teacher" alias="Teacher"></typeAlias>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/huadian"/>
<property name="username" value="root"/>
<property name="password" value="root"/>
</dataSource>
</environment>
</environments>
<!--映射资源文件路径-->
<mappers>
<mapper resource="com/huadian/dao/TeacherDao.xml"/>
</mappers>
</configuration>
2.7 原始Dao开发存在的问题
- Dao层的方法存在重复代码。
- 在调用xml文件中的praparedstatement语句式id存在硬编码。维护不方便。
3.Mapper动态代理开发
- 代理开发遵守以下规范:
- 映射文件的命名空间必须是接口的全路径(Mapper.xml namespace)
- 映射文件中sql语句的id必须和接口中方法的名称一致
- sql语句中的#{s属性名称},即使参数是基本数据类型。
- 方法的参数类型必须和mappr.xml中的参数类型一致
- 接口的返回值类型和mapper.xml中的resulttype类型一致
3.1 项目结构
- src
- bean
- Teacher.java
- dao
- TeacherDao.java
- TeacherDao.xml
- utils
- MybatisUtils.java
- test
- TeacherTest.java
- bean
- MybatisConfig.xml
- db.properties
- log4j.properties
3.2 核心配置文件 MybatisConfig.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>
<!--将外部的数据源配置到我们环境中-->
<properties resource="db.properties"></properties>
<typeAliases>
<typeAlias type="com.huadian.bean.Teacher" alias="Teacher"></typeAlias>
</typeAliases>
<environments default="development">
<environment id="development">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<!--ognl 表达式从环境中加载数据源的信息-->
<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>
<!--基于包的映射文件的扫描-->
<mappers>
<package name="com.huadian.dao"></package>
</mappers>
</configuration>
3.3 接口的映射文件 TeacherDao.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="com.huadian.dao.TeacherDao">
<!--查询所有-->
<select id="selectAll" resultType="Teacher">
select * from teacher
</select>
<!--根据id查询-->
<!--数据库的字段和类属性字段名字不一致-->
<select id="selectById" parameterType="int" resultType="Teacher">
select * from teacher where tid = #{tId}
</select>
<!--添加数据-->
<insert id="add" parameterType="Teacher" >
insert into teacher (tname,tage,taddress) values (#{tName},#{tAge},#{tAddress})
</insert>
<!--删除-->
<delete id="delete" parameterType="int" >
delete from teacher where tid =#{tid}
</delete>
<!--修改-->
<update id="update" parameterType="Teacher" >
update teacher set tname=#{tName},tage=#{tAge},taddress=#{tAddress} where tid=#{tId}
</update>
<!--模糊查询-->
<select id="selectByName" parameterType="Teacher" resultType="Teacher">
select * from teacher where tname like '%${tName}%'
</select>
</mapper>
3.4 外部数据源文件 db.properties
jdbc.Driver=com.mysql.jdbc.Driver
jdbc.Url=jdbc:mysql://localhost:3306/huadian?useUnicode=true&characterEncoding=utf-8
jdbc.UserName=root
jdbc.Password=root
3.5 日志文件 log4j.properties
# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# MyBatis logging configuration...
log4j.logger.com.huadian.dao.TeacherDao=TRACE
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n
3.6 测试 TeacherTest.java
public class TeacherTest {
SqlSession sqlSession = MyBatisUtls.getSqlSession();
//获取映射器 MapperProxy mapper代理
public TeacherDao getMappers(){
TeacherDao mapper = sqlSession.getMapper(TeacherDao.class);
System.out.println(TeacherDao.class);
return mapper;
}
@Test
public void selectAll(){
//获取sqlSession的映射器
List<Teacher> teachers = getMappers().selectAll();
System.out.println(teachers);
}
@Test
public void selectOne(){
Teacher teacher = new Teacher();
teacher.settId(1);
Teacher teacher1 = getMappers().selectById(teacher);
System.out.println(teacher1);
}
}
3.7 mybatis 自带的类型别名注册类
public class TypeAliasRegistry {
private final Map<String, Class<?>> typeAliases = new HashMap();
public TypeAliasRegistry() {
this.registerAlias("string", String.class);
this.registerAlias("byte", Byte.class);
this.registerAlias("long", Long.class);
this.registerAlias("short", Short.class);
this.registerAlias("int", Integer.class);
this.registerAlias("integer", Integer.class);
this.registerAlias("double", Double.class);
this.registerAlias("float", Float.class);
this.registerAlias("boolean", Boolean.class);
this.registerAlias("byte[]", Byte[].class);
this.registerAlias("long[]", Long[].class);
this.registerAlias("short[]", Short[].class);
this.registerAlias("int[]", Integer[].class);
this.registerAlias("integer[]", Integer[].class);
this.registerAlias("double[]", Double[].class);
this.registerAlias("float[]", Float[].class);
this.registerAlias("boolean[]", Boolean[].class);
this.registerAlias("_byte", Byte.TYPE);
this.registerAlias("_long", Long.TYPE);
this.registerAlias("_short", Short.TYPE);
this.registerAlias("_int", Integer.TYPE);
this.registerAlias("_integer", Integer.TYPE);
this.registerAlias("_double", Double.TYPE);
this.registerAlias("_float", Float.TYPE);
this.registerAlias("_boolean", Boolean.TYPE);
this.registerAlias("_byte[]", byte[].class);
this.registerAlias("_long[]", long[].class);
this.registerAlias("_short[]", short[].class);
this.registerAlias("_int[]", int[].class);
this.registerAlias("_integer[]", int[].class);
this.registerAlias("_double[]", double[].class);
this.registerAlias("_float[]", float[].class);
this.registerAlias("_boolean[]", boolean[].class);
this.registerAlias("date", Date.class);
this.registerAlias("decimal", BigDecimal.class);
this.registerAlias("bigdecimal", BigDecimal.class);
this.registerAlias("biginteger", BigInteger.class);
this.registerAlias("object", Object.class);
this.registerAlias("date[]", Date[].class);
this.registerAlias("decimal[]", BigDecimal[].class);
this.registerAlias("bigdecimal[]", BigDecimal[].class);
this.registerAlias("biginteger[]", BigInteger[].class);
this.registerAlias("object[]", Object[].class);
this.registerAlias("map", Map.class);
this.registerAlias("hashmap", HashMap.class);
this.registerAlias("list", List.class);
this.registerAlias("arraylist", ArrayList.class);
this.registerAlias("collection", Collection.class);
this.registerAlias("iterator", Iterator.class);
this.registerAlias("ResultSet", ResultSet.class);
}
4.MybatisCofig核心配置文件配置
- configuration(配置)
- properties(属性)
- settings(设置)
- typeAliases(类型别名)
- typeHandlers(类型处理器)
- objectFactory(对象工厂)
- plugins(插件)
- environments(环境配置)
- environment(环境变量)
- transactionManager(事务管理器)
- dataSource(数据源)
- databaseIdProvider(数据库厂商标识)
- mappers(映射器)
4.1 注意问题
- 在Mybatis的核心配置文件中属性标签的顺序一定不能错。
- properties配置外部的数据源(db.properties 文件)
- 数据源中我们获取外部的数据配置文件是通过OGNL(对象图形导航语言)表达式
- 插件(分页插件)
5.MybatisMapper.xml 配置
- Mapper namespace 必须和我们的接口的全路径相同
- parameterType 使用代理开发的时候参数类型必须和我们接口的参数类型一致(因为mybatis 帮我们做了类型转换,基本数据类型使用数据类型名称的小写。)
- resultType 结果的类型。
- resultMap 单表中使用的不多,关联查询中使用的多。(我们数据库的列名和我们实体类的字段名称不一致的时候使用resultMap)
<?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="com.huadian.dao.AMapper">
<!--数据库的字段名和类的成员变量名称不一致 映射 id sql语句中引用的id名称 type 实体类-->
<resultMap id="a" type="A">
<!--数据库的主键 column 表的列名 peoperty 类的属性名-->
<id column="id" property="stuId"></id>
<!--数据库的普通列-->
<result property="stuName" column="name"></result>
<result property="stuAge" column="age"></result>
</resultMap>
<!--selectAll-->
<select id="selectAll" resultMap="a">
select * from a
</select>
</mapper>
6.动态SQL(*******)
- sql片段
- 动态sql
- if
<!--sql片段-->
<sql id="select">
select * from department
</sql>
<select id="selectOne" parameterType="Department" resultType="Department">
<include refid="select"></include>
<!--条件永远成立,方便拼接后边的SQL语句-->
where 1=1
<if test="depId != null">
<!--and 是为了sql 语句的拼接-->
and depid = #{depId}
</if>
<if test="depName != null">
and depname = #{depName}
</if>
</select>
- where标签 改进后的 where 标签会自动会略第一个and
<!--select * from department WHERE depid = ? and depname = ? -->
<select id="selectOne" parameterType="Department" resultType="Department">
<include refid="select"></include>
<where>
<if test="depId != null">
and depid = #{depId}
</if>
<if test="depName != null">
and depname = #{depName}
</if>
</where>
</select>
- 多个条件判断
<!--id满足根据id查询 id不满足根据名字查询-->
<select id="selectByNameOrId" parameterType="Department" resultType="Department">
<include refid="select"/>
<where>
<choose>
<when test="depId != null and depId !=0">
and depid =#{depId}
</when>
<otherwise>
and depname like '%${depName}%'
</otherwise>
</choose>
</where>
</select>
- 模糊查询
<!--模糊查询-->
<select id="selectLikeName" parameterType="Department" resultType="Department">
<include refid="select"/>
<where>
<if test="depName != null and depName !=''">
and depname like '%${depName}%'
</if>
</where>
</select>
- set 修改语句
<!--修改-->
<update id="updateDep" parameterType="Department">
update department
<set>
<if test="depName != null and depName !=''">
depname = #{depName},
</if>
<if test="depAddress !=null and depAddress !='' ">
depaddress = #{depAddress},
</if>
<if test="depNumber != null and depNumber != 0">
depnumber = #{depNumber}
</if>
</set>
where
depId = #{depId}
</update>
- foreach 对集合的迭代
<!--select * from department where depid in(2,3,4)-->
<select id="selectById" resultType="Department">
<include refid="select"/>
<where>
depid in
<!-- collection 是传入的集合 open 和 close 组合成() separator 是数据的分隔符 item 是接收集合数据的变量-->
<foreach collection="list" open="(" close=")" item="item" separator=",">
#{item}
</foreach>
</where>
</select>
7.关联查询
-
一对一
public class Husband { private int hId; private String hName; private int hAge; private Wife wife; .......
public class Wife { private int wId; private String wName; private int wAge; .......
<sql id="select"> select * from husband </sql> <!--结果集封装--> <resultMap id="selectJoinWife" type="Husband"> <id column="hid" property="hId"></id> <result property="hName" column="hname"></result> <result property="hAge" column="hage"></result> <!--表的关联--> <!--一对一的关联 property 属性名称 column 外键名称 javaType 是查询的关联标的数据的封装类的类型--> <association property="wife" column="wid" javaType="Wife"> <id property="wId" column="wid"/> <result column="wname" property="wName"/> <result property="wAge" column="wage"/> </association> </resultMap>
<select id="selectAll" resultMap="selectJoinWife" >
<include refid="select"/>
join wife on husband.wid = wife.wid
</select>
+ 一对多
```java
public class Coach {
private int cId;
private String cName;
private String cType;
/*一对多*/
private List<Player> players = new ArrayList<>();
.....
public class Player {
private int pId;
private String pName;
.....
<?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="com.huadian.dao.CoachMapper">
<sql id="select">
select * from coach
</sql>
<resultMap id="selectJoinPalyer" type="Coach">
<id column="cid" property="cId"></id>
<result property="cName" column="cname"/>
<result property="cType" column="ctype"/>
<!--一对多-->
<collection property="players" column="cid" javaType="Player">
<id column="pid" property="pId"></id>
<result property="pName" column="pname"></result>
</collection>
</resultMap>
<select id="selectAll" resultMap="selectJoinPalyer">
<include refid="select"></include>
join player on coach.cid = player.cid
</select>
</mapper>
- 改进以后
<?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="com.huadian.dao.PlayerMapper">
<resultMap id="selectAllPlayer" type="Player">
<id column="pid" property="pId"></id>
<result property="pName" column="pname"></result>
</resultMap>
</mapper>
<?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="com.huadian.dao.CoachMapper">
<sql id="select">
select * from coach
</sql>
<resultMap id="selectJoinPalyer" type="Coach">
<id column="cid" property="cId"></id>
<result property="cName" column="cname"/>
<result property="cType" column="ctype"/>
<!--一对多-->
<!-- <collection property="players" column="cid" javaType="Player">
<id column="pid" property="pId"></id>
<result property="pName" column="pname"></result>
</collection>-->
<!--引用PlayerMapper.xml中的定义好的resultMap-->
<collection property="players" column="cid" javaType="Player"
resultMap="com.huadian.dao.PlayerMapper.selectAllPlayer">
</collection>
</resultMap>
<select id="selectAll" resultMap="selectJoinPalyer">
<include refid="select"></include>
join player on coach.cid = player.cid
</select>
</mapper>
- 多对多
public class Teacher {
private int teaId;
private String teaName;
private int teaAge;
private String teaAddress;
private List<Student> students = new ArrayList<>();
public class Student {
private int stuId;
private String stuName;
private int stuAge;
private String stuSex;
private Date stuBirth;
private String stuAddress;
private String stuDepartment;
private List<Teacher> teachers = new ArrayList<>();
- s_t 第三张表 保存多对多的主外键关系
<?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="com.huadian.dao.StudentMapper">
<resultMap id="selectJoinTeacher" type="Student">
<id column="id" property="stuId"></id>
<result property="stuName" column="name"/>
<result property="stuAge" column="age"/>
<result property="stuSex" column="sex"/>
<result property="stuBirth" column="birth"/>
<result property="stuAddress" column="address"/>
<result property="stuDepartment" column="department"/>
<collection property="teachers" javaType="Teacher">
<id column="tid" property="teaId"/>
<result property="teaName" column="tname"/>
<result property="teaAddress" column="taddress"/>
<result property="teaAge" column="tage"/>
</collection>
</resultMap>
<!--select * from student ,teacher ,s_t where student.id=s_t.sid and teacher.tid=s_t.tid-->
<!--查询所有-->
<select id="selectAll" resultMap="selectJoinTeacher">
select
stu.id,stu.name,stu.age,stu.sex,stu.birth,stu.department,stu.address,
tea.tid,tea.tname,tea.tage,tea.taddress
from
student stu, teacher tea,s_t
where
stu.id=s_t.sid
and
tea.tid=s_t.tid
</select>
</mapper>
8.查询缓存
- 连接池
- c3p0 hibernate框架jar包自带的连接池
- 阿里巴巴的数据库连接池 druid
- 世界上连接速度最快的数据库连接池Hikari
- 一级缓存 SqlSession
- mybatis 默认开启一级缓存
所谓的一级缓存:在我们数据库中我们使用id作为数据的唯一标识符,id是主键唯一,那么我们首次在使用sqlSession 获取getMapper以后进行数据的查询,我么要去数据库查询,会发出sql语句,获取到数据以后数据就会在我们的sqlsession中保存一份,保存的是数据的id,下次我们再次查询相同id的数据时就不会直接去数据库查询,而是存sqlSession中直接获取id相同的数据,不会发出sql语句。但是如果我们先查询数据,在对数据库的数据进行update,insert,delete操作就会刷新sqlSession ,那么原来保存的数据就会被清空,我们再次查询的时候还是要发出sql语句去数据库进行查询的。
@Test
public void selectAll(){
Department department = new Department();
department.setDepId(101);
Department department2 = new Department();
department2.setDepId(101);
Department department1 = getMapper().selectById(department);
System.out.println(department1);
Department department3 = getMapper().selectById(department2);
System.out.println(department3);
sqlSession.close();
}
DEBUG [main] - ==> Preparing: select * from department WHERE depid =?
DEBUG [main] - ==> Parameters: 101(Integer)
DEBUG [main] - <== Total: 1
Department{depId=101, depName='技术部', depAddress='#2-201', depNumber=1101}
DEBUG [main] - ==> Preparing: select * from department WHERE depid =?
DEBUG [main] - ==> Parameters: 102(Integer)
DEBUG [main] - <== Total: 1
Department{depId=102, depName='生活部', depAddress='#3-303', depNumber=1105}
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@3f200884]
DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@3f200884]
DEBUG [main] - Returned connection 1059063940 to pool.
DEBUG [main] - ==> Preparing: select * from department WHERE depid =?
DEBUG [main] - ==> Parameters: 101(Integer)
DEBUG [main] - <== Total: 1
Department{depId=101, depName='技术部', depAddress='#2-201', depNumber=1101}
Department{depId=101, depName='技术部', depAddress='#2-201', depNumber=1101}
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@3f200884]
DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@3f200884]
DEBUG [main] - Returned connection 1059063940 to pool.
- 注意:
@Test
public void selectAll2(){
/*定义一个对象只有id值*/
Department department = new Department();
department.setDepId(101);
/*根据id值去数据库查询*/
Department department1 = getMapper().selectById(department);
System.out.println(department1);
/*改变查询出来数据的属性值*/
department1.setDepNumber(1106);
department1.setDepAddress("#3-308");
department1.setDepName("学习部");
/*修改数据中数据的值*/
int result= getMapper().updateDepartment(department1);
sqlSession.commit();
System.out.println(result);
Department department2= getMapper().selectById(department);
System.out.println(department2);
sqlSession.close();
}
如果进行了update insert delete 那么将会刷新sqlSession 清除一级缓存
DEBUG [main] - ==> Preparing: select * from department WHERE depid =?
DEBUG [main] - ==> Parameters: 101(Integer)
DEBUG [main] - <== Total: 1
Department{depId=101, depName='学习部', depAddress='#3-308', depNumber=1106}
DEBUG [main] - ==> Preparing: update department SET depname =?, depaddress = ?, depnumber = ? where depid= ?
DEBUG [main] - ==> Parameters: 学习部(String), #3-308(String), 1106(Integer), 101(Integer)
DEBUG [main] - <== Updates: 1
DEBUG [main] - Committing JDBC Connection [com.mysql.jdbc.JDBC4Connection@f0f2775]
1
DEBUG [main] - ==> Preparing: select * from department WHERE depid =?
DEBUG [main] - ==> Parameters: 101(Integer)
DEBUG [main] - <== Total: 1
Department{depId=101, depName='学习部', depAddress='#3-308', depNumber=1106}
DEBUG [main] - Resetting autocommit to true on JDBC Connection [com.mysql.jdbc.JDBC4Connection@f0f2775]
DEBUG [main] - Closing JDBC Connection [com.mysql.jdbc.JDBC4Connection@f0f2775]
DEBUG [main] - Returned connection 252651381 to pool.
- 二级缓存 namespace
用的不多
9.分页 (Mybatis的谷歌分页插件 pagehelper)
-
下载github MybatisPageHelper 分页插件jar
-
jsqlparser-0.9.5.jar
-
在MybatisConfigxml中配置插件
<!--拦截器实现分页--> <plugins> <plugin interceptor="com.github.pagehelper.PageInterceptor"> <!-- Dialect 方言--> <property name="helperDialect" value="mysql"></property> </plugin> </plugins>
-
测试
@Test
public void selectByPage(){
/*从哪开始 查多少个*/
PageHelper.startPage(1,1);
/*查询所有的数据*/
List<Department> list = getMapper().selectAll();
PageInfo<Department> info = new PageInfo<>(list);
/*获取总页数*/
int pages = info.getPages();
System.out.println(pages);
}