MyBatis基操
程序员文章站
2022-04-07 18:09:32
目录mybatis与SpringBoot整合后mybatis与SpringBoot整合后maven org.mybatis.spring.boot mybatis-spring-boot-starter 2.1.3
mybatis与SpringBoot整合后
maven
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.3</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.1.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
简易版配置
spring:
datasource:
username: root
password: 123456
url: jdbc:mysql://127.0.0.1:3306/testshop?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8
driver-class-name: com.mysql.cj.jdbc.Driver
type: com.alibaba.druid.pool.DruidDataSource
mybatis:
#指定mapper.xml的路径
mapper-locations: classpath*:mapper/*.xml
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
mapper.xml文件跟mapper接口放在同一包下，放到为了能让扫描到mapper.xml，需要在pom的build里加个配置
<resources>
<resource>
<!-- 描述存放资源的目录，该路径相对POM路径-->
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
启动类加上mapperscan注解，生成实现
@MapperScan("com.xx.mapper")
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="具体的接口路径">
</mapper>
parameterType
<!--基本数据类型-->
<select id="findById" parameterType="long" resultType="cn.xx.pojo.Shop">
select * from shop where id = #{id}
</select>
<!--String类型-->
<select id="findByName" parameterType="java.lang.String" resultType="cn.xx.pojo.Shop">
select * from shop where name = #{name}
</select>
<!--包装类-->
<select id="findById2" parameterType="java.lang.Long" resultType="cn.xx.pojo.Shop">
select * from shop where id = #{id}
</select>
<!--多个参数-->
<select id="findByNameAndAge" resultType="cn.xx.pojo.Shop">
select * from shop where name = #{param1} and age = #{param2}
</select>
<!--Java Bean-->
<update id="update" parameterType="cn.xx.pojo.Shop">
update shop set name = #{name},password = #{password},age = #{age} where id = #{id}
</update>
resultType
<!--基本数据类型-->
<select id="count" resultType="int">
select count(id) from shop
</select>
<!--包装类-->
<select id="count1" resultType="Integer">
select count(id) form shop
</select>
<!--String-->
<select id="findNameById" resultType="java.lang.String">
select name from shop where id = #{id}
</select>
<!--Java Bean-->
<select id="findById" parameterType="long" resultType="cn.xx.pojo.Shop">
select * from shop where id = #{id}
</select>
级联查询 一对多
比如，员工和公司的关系，一个公司有多个员工，一个员工只能属于一个公司。
员工实体类id,name,co，公司实体类id,name,List<员工>.
public User findById(long id);
<resultMap id="userMap" type="cn.xx.pojo.User">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<association property="co" javaType="cn.xx.pojo.Co">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
</association>
</resultMap>
<select id="findById" parameterType="long" resultMap="userMap">
select u.id,u.name,c.id as cid,c.name as cname from user u, co c where u.id =#{id} and u.cid = c.id
</select>
public Co findById(long id);
<resultMap id="CoMap" type="cn.xx.pojo.Co">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
<collection property="users" ofType="cn.xx.pojo.User">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
</collection>
</resultMap>
<select id="findById" parameterType="long" resultMap="CoMap">
select u.id,u.name,c.id cid ,c.name cname from user u, co c where cid =#{id} and u.cid = c.id
</select>
级联查询 多对多
比如用户和商品，用户：id,name,List<商品>，商品：id，name，List<用户>。
用户表：id，name。商品表：id，name。中间表：id，用户id，商品id。
当然开发中也不会像下边似的那么写sql
public User findById(long id);
<resultMap id="UserMap" type="cn.xx.pojo.User">
<id column="uid" property="id"></id>
<result column="uname" property="name"></result>
<collection property="shops" ofType="cn.xx.pojo.Shop">
<id column="sid" property="id"></id>
<result column="sname" property="name"></result>
</collection>
</resultMap>
<select id="findById" parameterType="long" resultMap="UserMap">
select u.id uid,u.name uname ,s.id sid, s.name sname from user u, shop s, user_shop us where uid =${id} and us.uid = u.id and us.sid = s.id
</select>
public Shop findById(long id);
<resultMap id="ShopMap" type="cn.xx.pojo.Shop">
<id column="sid" property="id"></id>
<result column="sname" property="name"></result>
<collection property="users" ofType="cn.xx.pojo.User">
<id column="uid" property="id"></id>
<result column="uname" property="name"></result>
</collection>
</resultMap>
<select id="findById" parameterType="long" resultMap="ShopMap">
select u.id uid,u.name uname ,s.id sid, s.name sname from user u, shop s, user_shop us where sid =${id} and us.uid = u.id and us.sid = s.id
</select>
延迟加载
延迟加载也叫懒加载，惰性加载，使用延迟加载可以提高程序的运行效率。
怎么个提高法呢？比如上边一对多查员工时，会把公司信息也带出来，也就是查了两张表，那么如果我只需要查员工的姓名，带出来的信息根本不需要，这代码就比较废了，也耗费了时间与性能。正确的逻辑应该是根据不同的业务需求，搞出不同的代码进行查询。
开启延迟加载：
mybatis:
configuration:
lazy-loading-enabled: true
将多表关联查询拆分成多个单表查询
UserMapper
public User findByIdLazy(long id);
UserMapper.xml
<resultMap id="userMapLazy" type="cn.xx.pojo.User">
<id column="id" property="id"></id>
<result column="name" property="name"></result>
<association property="co" javaType="cn.xx.pojo.Co" select="cn.xx.mapper.CoMapper.findByIdLazy" column="cid"></association>
</resultMap>
<select id="findByIdLazy" parameterType="long" resultMap="userMapLazy">
select * from User where id = #{id}
</select>
CoMapper
public Co findByIdLazy(long id);
CoMapper.xml
<select id="findByIdLazy" parameterType="long" resultType="cn.xx.pojo.Co">
select * from Co where id = #{id}
</select>
缓存
local-cache-scope: statement
动态SQL
if标签
<select id="find" parameterType="cn.xx.pojo.User" resultType="cn.xx.pojo.User">
select * from user where
<if test="id != 0">
id = #{id}
</if>
<if test="name != null">
and name = #{name}
</if>
<if test="password != null">
and password = #{password}
</if>
<if test="sex != null">
and sex = #{sex}
</if>
</select>
一般where标签与if标签结合使用，where会自动删减紧跟它的and
select * from user
<where>
<if test="id != 0">
id = #{id}
</if>
<if test="name != null">
and name = #{name}
</if>
<if test="password != null">
and password = #{password}
</if>
<if test="sex != null">
and sex = #{sex}
</if>
</where>
choose when 选择
select * from user
<where>
<choose>
<when test="id != 0">
id = #{id}
</when>
<when test="name != null">
name = #{name}
</when>
<when test="password != null">
password = #{password}
</when>
<when test="sex != null">
sex = #{sex}
</when>
</choose>
</where>
trim
prefix：前缀
prefixoverride：去掉第一个and或者是or
select * from user
<trim prefix="WHERE" prefixoverride="AND |OR">
<if test="name != null">
and name=#{name}
</if>
<if test="sex != null">
and sex=#{sex}
</if>
</trim>
set
suffix：后缀
suffixoverride：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的and一样）
效果：update user set name=‘xx’ , gender=‘xx’ where id=‘x’
update user
<trim prefix="set" suffixoverride="," suffix=" where id = #{id} ">
<if test="name != null">
name=#{name} ,
</if>
<if test="sex != null">
sex=#{sex} ,
</if>
</trim>
foreach
select * from user where id in
<foreach collection="list" item="item" index="index" open="(" separator="," close=")">
#{item}
</foreach>
collection ：collection属性的值有三个分别是list、array、map三种，分别对应的参数类型为：List、数组、map集合，map入参记得加@param（“map”），这时index就变成了key，item是value
item ： 表示在迭代过程中每一个元素的别名
index ：表示在迭代过程中每次迭代到的位置（下标）
open ：前缀
close ：后缀
separator ：分隔符，表示迭代时每个元素之间以什么分隔
通常可以将它用到批量删除、添加等操作中。
本文地址：https://blog.csdn.net/this_break/article/details/109565649
上一篇： Java自动定时关机程序