1 mapper映射
注意:
- 我们只需要写好接口,实现是由mapper自己完成
- xml中的命名空间和接口的完全限定名完全一致
- 接口中的方法名和xml中的sql的id一样
1.1 准备接口 EmployeeMapper
public interface EmployeeMapper {
Employee findOne(Long id);
//@Select("select * from employee")
List<Employee> findAll();
//根据条件查询数据
List<Employee> queryAll(EmployeeQuery employeeQuery);
//根据条件查询总条数
Long getCount(EmployeeQuery employeeQuery);
}
1.2 准备xml文件 EmployeeMapper.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">
<!--namespace的命名空间和接口的全限定名一致-->
<mapper namespace="cn.itsource._01_mapper.mapper.EmployeeMapper">
<!-- 这里的id必需和方法名对应 -->
<select id="findOne" parameterType="long" resultType="cn.itsource._01_mapper.domain.Employee">
select * from employee where id= #{id}
</select>
<!--查询所有-->
<select id="findAll" resultType="cn.itsource._01_mapper.domain.Employee">
select * from employee
</select>
</mapper>
1.3 项目包的命名规范
2 高级查询
- 模糊查询使用concat concat("%",#{name},"%")
- 特殊字符使用转义或者CDATA段
<
代表小于
- 每个条件都需要if判断(有这个条件才进行过滤)
<if test="条件一 and/or 条件二">
- 在过滤前加上 where, 把第一个and替换成where
- 如果遇到重复的SQL,可以使用 sql标签把它把抽出来
<?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:命名空间 和接口的全限定名一致-->
<mapper namespace="cn.itsource._01_mapper.mapper.EmployeeMapper">
<!--准备一个代码块-->
<sql id="whereSql">
<where>
<if test="name!=null and name!=''">
and name like concat("%", #{name}, "%")
</if>
<if test="minAge!=null">
and age >= #{minAge}
</if>
<if test="maxAge!=null">
<![CDATA[and age <= #{maxAge}]]>
</if>
</where>
</sql>
<!--查询一共多少条数据-->
<select id="getCount" resultType="long" parameterType="cn.itsource._01_mapper.query.EmployeeQuery">
select count(*) from employee
<include refid="whereSql"/>
</select>
<select id="queryAll" parameterType="cn.itsource._01_mapper.query.EmployeeQuery"
resultType="cn.itsource._01_mapper.domain.Employee">
select *
from employee
<include refid="whereSql"/>
</select>
<!--这里的id必须和方法名对应-->
<select id="findOne" parameterType="long" resultType="cn.itsource._01_mapper.domain.Employee">
select *
from employee
where id = #{id}
</select>
<!--查询所有-->
<select id="findAll" resultType="cn.itsource._01_mapper.domain.Employee">
select *
from employee
</select>
<!--支持二级缓存-->
<cache />
</mapper>
3 关系配置
3.1 多对一
3.1.1 domain准备
==Product类 ==
public class Product {
private Long id;
private String name;
private ProductDir dir;
get()/set()方法
}
ProductDir 类
public class ProductDir {
private Long id;
private String name;
get()/set()方法
}
3.1.2 关系映射 - 嵌套结果
- 一条SQL查询所有数据(容易出现相同的列名,因此我们开发的时候尽量要为这些列取相应的别名),mybatis再帮我们把数据整理出来。
- 必需使用resultMap进行手动映射
- association 用于进行对象的关连
- 使用后自动映射失效
- 单独配置对象中的属性
<?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:命名空间 和接口的全限定名一致-->
<mapper namespace="cn.itsource._02_many2one_result.mapper.ProductMapper">
<!--自己写代码完成手动映射-->
<!--
resultMap: 结果映射
id:名称
type:映射的对象类型
-->
<resultMap id="ProductMap" type="cn.itsource._02_many2one_result.domain.Product">
<!--
result:某一个属性的映射
property:类型的属性
column:表中的列
-->
<id property="id" column="id"/>
<result property="name" column="name"/>
<!--
association:关联(如果要关联对象 就使用它)
->自动映射就失效了
javaType:类型
-->
<association property="dir" column="dir_id" javaType="cn.itsource._02_many2one_result.domain.ProductDir">
<id property="id" column="did"/>
<result property="name" column="dname"/>
</association>
</resultMap>
<select id="findAll" resultMap="ProductMap">
select p.id, p.name, dir.id did, dir.name dname
from t_product p
left join t_productdir dir on dir.id = p.dir_id
</select>
</mapper>
3.1.3 关系映射 - 嵌套查询
先准备一条SQL查询到数据,再根据查询的数据的外键找到对应的对象
<?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:命名空间 和接口的全限定名一致-->
<mapper namespace="cn.itsource._03_many2one_search.mapper.ProductMapper">
<resultMap id="ProductMap" type="cn.itsource._03_many2one_search.domain.Product">
<id property="id" column="id"/>
<result property="name" column="name"/>
<!--
设置关联映射
select="findDirById" 找到相应的查询语句
-->
<association property="dir" column="dir_id" select="findDirById"
javaType="cn.itsource._03_many2one_search.domain.ProductDir">
</association>
</resultMap>
<!--根据id获取相应的类型 parameterType="long" 获取到的就是column传过来的值-->
<select id="findDirById" parameterType="long" resultType="cn.itsource._03_many2one_search.domain.ProductDir">
select *
from t_productdir
where id = #{id}
</select>
<select id="findAll" resultMap="ProductMap">
select *
from t_product
</select>
</mapper>
3.2 一对多
3.2.1 domain层
==Product类 ==
public class Product {
private Long id;
private String name;
get()/set()方法
}
ProductDir 类
public class ProductDir {
private Long id;
private String name;
private List<Product> products = new ArrayList<>();
get()/set()方法
}
3.2.2 嵌套结果
<?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:命名空间 和接口的全限定名一致-->
<mapper namespace="cn.itsource._04_one2many_result.mapper.ProductDirMapper">
<!--手动映射-->
<resultMap id="productDirMap" type="cn.itsource._04_one2many_result.domain.ProductDir">
<id property="id" column="id" />
<result property="name" column="name" />
<!--
collection:集合(拿到多方) 使用后自动映射就失效了
ofType:这个集合中 每一个对象的类型
-->
<collection property="products" ofType="cn.itsource._04_one2many_result.domain.Product">
<id property="id" column="pid" />
<result property="name" column="pname" />
</collection>
</resultMap>
<select id="findAll" resultMap="productDirMap">
select dir.id,dir.name,p.id pid,p.name pname from t_productdir dir left join t_product p on dir.id = p.dir_id
</select>
</mapper>
3.2.3 嵌套查询
<?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:命名空间 和接口的全限定名一致-->
<mapper namespace="cn.itsource._05_one2many_search.mapper.ProductDirMapper">
<!--手动映射-->
<resultMap id="productDirMap" type="cn.itsource._05_one2many_search.domain.ProductDir">
<id property="id" column="id" />
<result property="name" column="name" />
<collection property="products" column="id" select="getProductByDirId" ofType="cn.itsource._05_one2many_search.domain.Product">
</collection>
</resultMap>
<select id="getProductByDirId" parameterType="long" resultType="cn.itsource._05_one2many_search.domain.Product">
select * from t_product where dir_id = #{id}
</select>
<select id="findAll" resultMap="productDirMap">
select * from t_productdir
</select>
</mapper>
4 SSM三大框架继承
步骤:
- 导包
- 基本项目结构搭建
- 集成步骤
4.1 导包
Spring的包,Spring的依赖包,SpringMVC的包(json支持包),mybatis的包,mybatis与Spring的集成包
4.2 jdbc.properties
jdbc.driverClassName=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql:///mybatis
jdbc.username=root
jdbc.password=123456
4.3 applicatinContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:tx="http://www.springframework.org/schema/tx"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/tx
http://www.springframework.org/schema/tx/spring-tx.xsd">
<!--扫描-->
<context:component-scan base-package="cn.itsource.ssm.service" />
<!--引入jdbc.properties-->
<context:property-placeholder location="classpath:jdbc.properties" />
<!--创建dataSource-->
<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="${jdbc.driverClassName}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</bean>
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<!--连接池-->
<property name="dataSource" ref="dataSource" />
<property name="mapperLocations" value="classpath:cn/itsource/ssm/mapper/*Mapper.xml" />
<!--typeAliasesPackage:这个包中所有的值 都会取相应的别名-->
<property name="typeAliasesPackage">
<value>
cn.itsource.ssm.domain
cn.itsource.ssm.query
</value>
</property>
</bean>
<!--一劳永逸
Mapper:映射
Scanner:扫描
Configurer:配置
basePackage:扫描的包(会用对象的类都创建实现)
-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="cn.itsource.ssm.mapper" />
</bean>
<!--事物配置-->
<!--配置事务管理器-->
<bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
<property name="dataSource" ref="dataSource" />
</bean>
<!--开启事物注解的支持
默认回去找名称transactionManager的事物管理器
-->
<tx:annotation-driven transaction-manager="transactionManager" />
</beans>
4.4 applicationContext-mvc.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:context="http://www.springframework.org/schema/context"
xmlns:mvc="http://www.springframework.org/schema/mvc"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd
http://www.springframework.org/schema/mvc
http://www.springframework.org/schema/mvc/spring-mvc.xsd">
<!--扫描controller-->
<context:component-scan base-package="cn.itsource.ssm.web.controller" />
<!--静态资源放行-->
<mvc:default-servlet-handler />
<!--mvc的注解支持 -->
<mvc:annotation-driven />
<!--视图解析器-->
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver">
<property name="prefix" value="/WEB-INF/views/" />
<property name="suffix" value=".jsp" />
</bean>
<!--上传解析器-->
</beans>
4.5 web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_4_0.xsd"
version="4.0">
<!--准备一个监听器 启动Spring-->
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
<!--高速监听器 配置文件在哪里-->
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:applicationContext.xml</param-value>
</context-param>
<!--配置核心控制器-->
<servlet>
<servlet-name>dispatcherServlet</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:applicationContext-mvc.xml</param-value>
</init-param>
<!--让这个selvlet随着服务器启动而启动-->
<load-on-startup>1</load-on-startup>
</servlet>
<servlet-mapping>
<servlet-name>dispatcherServlet</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
<!--
post请求乱码解决
get请求实在tomcat猫中配置
-->
<filter>
<filter-name>EncodingFilter</filter-name>
<filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class>
<init-param>
<param-name>encoding</param-name>
<param-value>utf-8</param-value>
</init-param>
</filter>
<filter-mapping>
<filter-name>EncodingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>
</web-app>
5 扩展
5.1 多对一映射,后面出错不要找我
<?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的命名空间和接口的全限定名一致-->
<mapper namespace="cn.itsource._06_many2one_haha.ProductMapper">
<resultMap id="productMap" type="cn.itsource._06_many2one_haha.Product">
<result property="dir.id" column="did" />
<result property="dir.name" column="dname" />
</resultMap>
<!--
resultMap:找到对应的手动映射
-->
<select id="findAll" resultMap="productMap">
select p.id,p.name,dir.id did,dir.name dname from t_product p
LEFT JOIN t_productdir dir
ON dir.id = p.dir_id
</select>
</mapper>
5.2 一对多—分页
如果需要分页,使用嵌套查询的方式,先用一条sql查询到一方【分页是有效果的】,然后再发送sql单独查多方