一、mybatis要点
主键回填
<!-- useGeneratedKeys 取到刚刚插入的主键 还需要加上keyProperty 值为主键ID 在给表对象插入值的时候可以使用getId拿到刚刚插入的ID-->
<insert id="addBook" parameterType="book" useGeneratedKeys="true" keyProperty="id">
insert into book (name, author)
values (#{name}, #{author})
</insert>
封装resultMap 以及配置一对一
封装resultMap
<resultMap id="UserMap" type="user">
<id property="id" column="id"/>
<result property="username" column="username"/>
<result property="password" column="password"/>
<result property="address" column="address"/>
</resultMap>
<!-- 配置一对一关系查询-->
<resultMap id="bookAndAuthor" type="book">
<id property="id" column="bid"></id>
<result property="name" column="name"></result>
<association property="author" javaType="com.getech.mybatis.model.Author">
<id column="aid" property="id"></id>
<result column="a_name" property="name"></result>
<result column="address" property="address"></result>
</association>
</resultMap>
配置一对多
<!-- 封装规则 -->
<resultMap id="departmentResultMap" type="com.qf.mybatis.part2.one2many.Department">
<id property="id" column="id" />
<result property="name" column="name" />
<result property="location" column="location" />
<!-- 关系表中数据的封装规则 --> <!-- 指定关系表的实体类型 -->
<collection property="emps" ofType="com.qf.mybatis.part2.one2many.Employee">
<id property="id" column="emp_id" />
<result property="name" column="emp_name" />
<result property="salary" column="salary" />
<result property="dept_id" column="dept_id" />
</collection>
</resultMap>
<!-- 多表连接查询 --> <!-- 封装规则 -->
<select id="selectDepartmentById" resultMap="departmentResultMap" >
<!-- 别名(避免与d.id、d.name冲突)-->
SELECT d.id , d.name , d.location , e.id AS emp_id , e.name emp_name , e.salary , e.dept_id
FROM t_departments d LEFT JOIN t_employees e
ON d.id = e.dept_id
WHERE d.id = #{id}
</select>
关系配置总结
一方,添加集合;多方,添加对象。
双方均可建立关系属性,建立关系属性后,对应的Mapper文件中需使用< ResultMap >完成多表映射。
持有对象关系属性,使用< association property=“dept” javaType=“department” >
持有集合关系属性,使用< collection property=“emps” ofType=“employee” >
动态SQL
sql片段
可以用来定义 SQL 片段。
<mapper namespace="com.qf.mybatis.part2.dynamic.BookDao">
<sql id="BOOKS_FIELD"> <!-- 定义SQL片段 -->
SELECT id,name,author,publish,sort
</sql>
<select id="selectBookByCondition" resultType="com.qf.mybatis.part2.dynamic.Book">
<include refid="BOOKS_FIELD" /> <!-- 通过ID引用SQL片段 -->
FROM t_books
</select>
</mapper>
if的使用
<select id="selectBookByCondition" resultType="com.qf.mybatis.part2.dynamic.Book">
<include refid="BOOKS_FIELD" /> <!-- 通过ID引用SQL片段 -->
FROM t_books
<if test="name!=null">
name=#{name}
</if>
<if test="author!=null">
and author=#{author}
</if>
</select>
set的使用
<update id="updateBookByCondition">
UPDATE t_books
<set>
<if test="name != null"><!-- where子句中满足条件的if,会自动忽略后缀(如:,) -->
name = #{name} ,
</if>
<if test="author != null">
author = #{author} ,
</if>
<if test="publish != null">
publish = #{publish} ,
</if>
<if test="sort != null">
sort = #{sort} ,
</if>
</set>
WHERE id = #{id}</update>
where的使用
<select id="selectUserByIdOrAddress76" resultMap="UserMap">
<!-- WHERE,会自动忽略前后缀(如:and | or) -->
select * from user
<where>
<if test="username!=null">
username=#{username}
</if>
<if test="address!=null">
address=#{address}
</if>
</where>
</select>
foreach的使用
<!-- 批量插入 foreach一般用来做sql拼接操作,比如批量插入,修改-->
<insert id="insertUserByList">
insert into user (username,password,address) values
<foreach collection="list" item="user" index="i" separator=",">
(
#{user.username},
#{user.password},
#{user.address}
)
</foreach>
</insert>
trim的使用
<!-- trim的使用-->
<select id="selectUserByIdOrAddress2" resultMap="UserMap">
<!-- WHERE,会自动忽略前后缀(如:and | or) -->
select * from user
<trim prefix="where" prefixOverrides="and|or"> <!-- prefix="where" 增加WHERE前缀,自动忽略前缀 -->
<if test="id !=null"> <!-- prefixOverrides 后缀自动忽略and -->
id > #{id}
</if>
<if test="address!=null">
and address like concat('%',#{address},'%')
</if>
</trim>
</select>
二、SSM整合
导入pom.xml依赖
<dependencies>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.5.7</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-context</artifactId>
<version>5.3.10</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.25</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-webmvc</artifactId>
<version>5.3.10</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper</artifactId>
<version>5.1.10</version>
</dependency>
<dependency>
<groupId>org.springframework.data</groupId>
<artifactId>spring-data-jpa</artifactId>
<version>2.5.4</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
<version>5.3.10</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>2.0.6</version>
</dependency>
<dependency>
<groupId>javax.servlet.jsp</groupId>
<artifactId>javax.servlet.jsp-api</artifactId>
<version>2.3.3</version>
</dependency>
<dependency>
<groupId>jstl</groupId>
<artifactId>jstl</artifactId>
<version>1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/com.alibaba/druid -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.8</version>
</dependency>
<dependency>
<groupId>javax.servlet</groupId>
<artifactId>javax.servlet-api</artifactId>
<version>4.0.1</version>
</dependency>
<!-- 导入aop依赖-->
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjweaver</artifactId>
<version>1.9.7</version>
</dependency>
<dependency>
<groupId>org.aspectj</groupId>
<artifactId>aspectjrt</artifactId>
<version>1.9.7</version>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-aop</artifactId>
<version>5.3.10</version>
</dependency>
<!-- 解决406异常 因为没有导入解析json包,导致前端无法显示后端返回的数据-->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.13.0</version>
</dependency>
</dependencies>
spring配置,这个配置文件也包括了mybatis 的配置,在resource资源文件里面新建一个applicationContex.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 https://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
<!-- 配置除了扫描controller 包其余包都扫描-->
<context:component-scan base-package="com.getech.ssm" use-default-filters="true">
<context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
</context:component-scan>
<!-- 配置数据源-->
<!-- 引入数据源db。properties-->
<context:property-placeholder location="classpath:db.properties"/>
<!-- druid datasource-->
<bean class="com.alibaba.druid.pool.DruidDataSource" id="dataSource">
<property name="username" value="${db.username}"/>
<property name="password" value="${db.password}"/>
<property name="url" value="${db.url}"/>
<property name="driverClassName" value="${db.driver}"/>
</bean>
<!-- 配置mybatis-->
<!-- 配置sqlSession工厂-->
<bean class="org.mybatis.spring.SqlSessionFactoryBean" id="sqlSessionFactory">
<property name="dataSource" ref="dataSource"/>
<!-- 配置mybatis 扫描的实体类的别名-->
<property name="typeAliasesPackage" value="com.getech.ssm.entity"/>
<!-- 配置包路径-->
<property name="mapperLocations">
<array>
<value>classpath*:mapper/*.xml</value>
</array>
</property>
</bean>
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer" id="scannerConfigurer">
<property name="basePackage" value="com.getech.ssm.dao"/>
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
</bean>
<!-- 配置事务-->
<bean class="org.springframework.jdbc.datasource.DataSourceTransactionManager" id="transactionManager">
<property name="dataSource" ref="dataSource"/>
</bean>
<!-- 注解事务-->
<tx:annotation-driven/>
</beans>
springmvc 配置,在resource 文件新建spring-servlet.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:mvc="http://www.springframework.org/schema/mvc"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context https://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/mvc https://www.springframework.org/schema/mvc/spring-mvc.xsd">
<!-- 配置扫 controller描包 -->
<context:component-scan base-package="com.getech.ssm" use-default-filters="false">
<context:include-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
</context:component-scan>
<!-- 配置handleMapping adaptMapping-->
<mvc:annotation-driven/>
<!-- 配置视图解析器-->
<bean class="org.springframework.web.servlet.view.InternalResourceViewResolver" id="viewResolver">
<property name="viewClass" value="org.springframework.web.servlet.view.JstlView"/>
<property name="prefix" value="/WEB-INF/jsp"/>
<property name="suffix" value=".jsp"/>
</bean>
</beans>
web.xml文件配置
需要指定applicationContext.xml和springmvc-servlet.xml配置文件位置
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
version="4.0">
<!-- 指定spring配置文件-->
<context-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:applicationContext.xml</param-value>
</context-param>
<servlet>
<servlet-name>springmvc</servlet-name>
<servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
<!-- 指定springmvc配置文件-->
<init-param>
<param-name>contextConfigLocation</param-name>
<param-value>classpath:springmvc-servlet.xml</param-value>
</init-param>
</servlet>
<servlet-mapping>
<servlet-name>springmvc</servlet-name>
<url-pattern>/</url-pattern>
</servlet-mapping>
<listener>
<listener-class>org.springframework.web.context.ContextLoaderListener</listener-class>
</listener>
</web-app>
db.properties配置
db.username=root
db.password=123456
db.driver=com.mysql.cj.jdbc.Driver
db.url=jdbc:mysql:///user_manager?serverTimezone=Asia/Shanghai
log4j.properties配置 (需要引入log4j 包,pom文件有参考包)
# Global logging configuration
log4j.rootLogger=DEBUG, stdout
# MyBatis logging configuration...
log4j.logger.org.mybatis.example.BlogMapper=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
整合之后项目结构