MyBeatis

/ 后端 / 框架 / MyBeatis

PramaType="map"    paramType="java.lang.String"   resultType=""   resultMap=""

<if test="_parameter != null">
<include refid="Base_Column_List" />

include

<sql id="Example_Where_Clause">
  <where>
    <foreach collection="oredCriteria" item="criteria" separator="or">
      <if test="criteria.valid">
        <trim prefix="(" prefixOverrides="and" suffix=")">
          <foreach collection="criteria.criteria" item="criterion">
            <choose>
              <when test="criterion.noValue">
                and ${criterion.condition}
              </when>
              <when test="criterion.singleValue">
                and ${criterion.condition} #{criterion.value}
              </when>
              <when test="criterion.betweenValue">
                and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
              </when>
              <when test="criterion.listValue">
                and ${criterion.condition}
                <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
                  #{listItem}
                </foreach>
              </when>
            </choose>
          </foreach>
        </trim>
      </if>
    </foreach>
  </where>
</sql>
<select id="selectByExample" parameterType="com.hn.po.AdminExample" resultMap="BaseResultMap">

select

<if test="distinct">

distinct

</if>

<include refid="Base_Column_List" />

from admin

<if test="_parameter != null">

<include refid="Example_Where_Clause" />

</if>

<if test="orderByClause != null">

order by $

</if>

</select>

insert 范例

<insert id="insertSelective" parameterType="com.hn.po.Admin">
  insert into admin
  <trim prefix="(" suffix=")" suffixOverrides=",">
    <if test="adminId != null">
      admin_id,
    </if>
    <if test="adminName != null">
      admin_name,
    </if>
    <if test="adminPwd != null">
      admin_pwd,
    </if>
  </trim>
  <trim prefix="values (" suffix=")" suffixOverrides=",">
    <if test="adminId != null">
      #{adminId,jdbcType=INTEGER},
    </if>
    <if test="adminName != null">
      #{adminName,jdbcType=VARCHAR},
    </if>
    <if test="adminPwd != null">
      #{adminPwd,jdbcType=VARCHAR},
    </if>
  </trim>
</insert>

update 范例

<update id="updateByExampleSelective" parameterType="map">
  update admin
  <set>
    <if test="record.adminId != null">
      admin_id = #{record.adminId,jdbcType=INTEGER},
    </if>
    <if test="record.adminName != null">
      admin_name = #{record.adminName,jdbcType=VARCHAR},
    </if>
    <if test="record.adminPwd != null">
      admin_pwd = #{record.adminPwd,jdbcType=VARCHAR},
    </if>
  </set>
  <if test="_parameter != null">
    <include refid="Update_By_Example_Where_Clause" />
  </if>
</update>
where admin_id = #{adminId,jdbcType=INTEGER}

mapper 也可以这么交给spring管理

<bean id="adminMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
    <property name="mapperInterface" value="com.hn.dao.AdminMapper"></property>
    <property name="sqlSessionFactory" ref="sqlsessionfactory"></property>
</bean>

springBoot里要加在启动类前

@MapperScan(basePackages = {"com.yh.mapper"})

加在pom里让maven编译是把xml也复制到输出文件夹.

<build>
    <resources>
        <resource>
            <directory>src/main/java</directory>
            <includes>
                <include>**/*.xml</include>
            </includes>
        </resource>
    </resources>
</build>

内部类用$指定  <select id="select" parameterType="ljj.dao.IProductDao$Condition" resultType="Product">

优缺点和适用场合

精彩代码

<!-- 让spring管理sqlsessionfactory 使用mybatis和spring整合包中的 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <!-- 数据库连接池 -->
    <property name="dataSource" ref="dataSource"/>
    <!-- 加载mybatis的全局配置文件 -->
    <property name="configLocation" value="classpath:sqlMapperConfig.xml"/>
</bean>

主配置文件，配置连接数据库的信息。在代码开始被引用

public SqlSession sqlSession = (new SqlSessionFactoryBuilder().build(Resources.getResourceAsReader("sqlMapConfig.xml"))).openSession();
读取xml文件用上面标红的代码，是MyBeatis提供的方法，用了classloader。xml文件放在bbs-web\web\WEB-INF\classes\sqlMapConfig.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="ljj">

<environment id="ljj">

<transactionManager type="JDBC"></transactionManager>

<dataSource type="POOLED">

<property name="driver" value="com.mysql.jdbc.Driver"/>

<property name="url" value="JDBC:mysql://localhost:3306/news"/>

<property name="username" value="root"/>

<property name="password" value="root"/>

</dataSource>

</environment>

</environments>
&lt;mappers&gt;
    &lt;mapper resource=&quot;myBatisNews.xml&quot;/&gt;
&lt;/mappers&gt;

</configuration>

sql语句配置文件，配置以id标识的可调用的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">
<mapper namespace="news">

<select id="getAll" resultType="MyNew">

select * from new

</select>
&lt;select id=&quot;getOneById&quot; parameterType=&quot;int&quot; resultType=&quot;MyNew&quot;&gt;
    select * from new where id = #{infdst}
&lt;/select&gt;

<!-- $:如果是简单类型只能写value -->

<select id="getAllByLikeCase" parameterType="java.lang.String" resultType="MyNew">

select * from new where title like '%$%'

</select>
&lt;insert id=&quot;insert&quot; parameterType=&quot;MyNew&quot;&gt;
    insert into new values(null,#{title},#{content},#{authu},#{time})
&lt;/insert&gt;

&lt;update id=&quot;update&quot; parameterType=&quot;MyNew&quot; &gt;
    update new set title=#{title},content=#{content},authu=#{authu} where id=#{id}
&lt;/update&gt;

&lt;delete id=&quot;delete&quot; parameterType=&quot;String&quot;&gt;
    delete from new where title = #{title}
&lt;/delete&gt;

</mapper>

主代码，第一行为引用主配置文件。

sql = new SqlSessionFactoryBuilder().build(new FileReader("./src/sqlMapConfig.xml"));//路径是从根目录开始
SqlSession sqlSession = sql.openSession();
System.out.println("--------------增---------------");

MyNew insertNew = new MyNew("insert","123","testInsert",new Date());

System.out.println(sqlSession.insert("insert", insertNew));

sqlSession.commit();
System.out.println("--------------查所有---------------");

List<MyNew> res = sqlSession.selectList("getAll");

res.forEach(System.out::println);
System.out.println("--------------查一个---------------");

System.out.println((sqlSession.selectOne("getOneById", 19)).toString());
System.out.println("--------------模糊查询---------------");

(sqlSession.selectList("getAllByLikeCase", "i")).forEach(System.out::println);
System.out.println("--------------改---------------");

MyNew updateNew = new MyNew("update","123","testUpdate",new Date());

updateNew.setId(19);

System.out.println(sqlSession.update("update",updateNew));

sqlSession.commit();
System.out.println("--------------删---------------");
System.out.println(sqlSession.delete("delete","insert"));

sqlSession.commit();

parameterType可写

简单类型，自定义类，hashMap

占位符

${}  两边不加引号的占位符，拼接用

主要是like '%${value}%' 里用

#{} 两边会加引号的占位符

大多数用这个

{}里写什么？

•

若类型是自定义类

•

写自定义类的属性名

•

如果实在${}里且是简单类型

•

只能写${value}

•

其余情况随便写，但尽量写有意义的字符

jar包

主要是mybatis-3.2.7.jar，包依赖log4j

mySql

select LASR_INSERT_ID();

select 序列名. NEXTVAL()

序列名 .currval()

动态SQL

if | include

 <!--  根据用户姓名和性别查询用户信息  -->
<select id="findUserByNameAndSex" parameterType="user" resultType="user">
select * from user
<!--  where自动去除子句中的第一个and  -->
<where>
<include refid="user_query"/>
</where>
</select>
<sql id="user_query">

<if test="username.equals('')"> and username = # </if>

<if test="sex != null"> and sex = # </if>

<if test="address != null"> and address = # </if>

</sql>

Foreach

<select id="findUserByIds" parameterType="uservo" resultType="user">
 <!--
		select * from user where username = "张三" and (id = ? or id = ? or id = ?)
		select * from user where id in(?,?,?...);
		.... 
-->
select * from user
<!--
 <where>
			<if test="ids != null">
				<foreach collection="ids" item="user_id" open = "(" separator="or" close=")">
					id = #{user_id}
				</foreach>
			</if>
		</where> 
-->
<where>
<if test="ids != null">
<foreach collection="ids" item="user_id" open="id in(" separator="," close=")"> #{user_id} </foreach>
</if>
</where>
</select>
<update id="updateState" parameterType="Map">

update tb_item

set status=#,

updated = #

where

<foreach collection="ids" item="id" separator=" or ">

id = #

</foreach>

</update>

缓存

一级缓存

基于session的缓存

一级缓存是SqlSession级别的缓存。在操作数据库时需要构造sqlSession对象，在对象中有一个数据结构用于存储缓存数据。不同的sqlSession之间的缓存数据区域是互相不影响的。也就是他只能作用在同一个sqlSession中，不同的sqlSession中的缓存是互相不能读取的。

二级缓存

二级缓存是mapper级别的缓存，多个SqlSession去操作同一个Mapper的sql语句，多个SqlSession可以共用二级缓存，二级缓存是跨SqlSession的。二级缓存的作用范围更大。
开启二级缓存，关闭sqlsession后，会把该sqlsession一级缓存中的数据添加到namespace的二级缓存中。这样，缓存在sqlsession关闭之后依然存在。

Mapper文件里加<cache/>

懒加载

ResultMap实现延迟加载（association collection 具有延迟加载功能）

<mapper namespace="com.mapper.UserMapper">
 <!--  开启当前mapper的二级缓存  -->
<cache/>
 <!--  定义ResultMap实现一对一查询  -->
<resultMap id="ordersResultMap" type="orders">
 <!--  完成订单结果集映射  -->
	<id column="id" property="id"/>
	<result column="user_id" property="user_id"/>
	<result column="number" property="number"/>
	<result column="createtime_" property="createtime"/>
	<result column="note_" property="note"/>
	 <!--  完成用户信息映射  懒加载，只有请求的时候才加载，而且有缓存，相同user_id的不重复查询-->
			<association property="user" javaType="com.bean.User" select="findUserByUid" column="user_id"> </association>
</resultMap>
 <!--  
	查询订单和订单的用户信息 
	association
只查询订单信息
 --&gt;

<select id="findOrderLazyLoad" resultMap="ordersResultMap"> select * from orders </select>

<!--  查询用户信息  -->

<select id="findUserByUid" parameterType="int" resultType="user"> select * from user where id = # </select>

<!--  根据id删除用户  -->

<delete id="deleteUserById" parameterType="int"> delete from user where id = # </delete>

</mapper>

高级结果集映射

基本映射

将结果映射为对象

 <!--  定义ResultMap实现一对一查询  -->
<resultMap id="ordersResultMap" type="orders">
 <!--  完成订单结果集映射  -->
<id column="id" property="id"/>
<result column="user_id" property="user_id"/>
<result column="number" property="number"/>
<result column="createtime_" property="createtime"/>
<result column="note_" property="note"/>
 <!--
	将结果集映射为一个对象(user对象)
	association:代表就是讲结果映射为一个对象
	
	property:对象在orders类中属性名
	javaType:对象的类型

-->

<association property="user" javaType="com.bean.User">

<result column="username" property="username"/>

<result column="address" property="address"/>

</association>

</resultMap>
<!--  查询订单和订单的用户信息  -->

<select id="findOrderAndUser" resultMap="ordersResultMap">

select b.*,a.username,a.address from user a,orders b where a.id = b.user_id;

</select>

将结果映射为集合

会自动装成制定类型的对象. 只有一个 指定类型也可以不用装对象.

<!--  定义resultMap  -->
<resultMap id="orderDetailsResultMap" type="orders" extends="ordersResultMap">
 <!--  订单详情信息映射  -->
 <!--  
		将订单关联的详情信息映射给一个集合对象
		  -->
<collection property="ordersDetails" ofType="com.bean.OrderDetails">
	<id column="d_id" property="id"/>
	<result column="orders_id" property="orders_id"/>
	<result column="items_num" property="items_num"/>
	<result column="items_id" property="items_id"/>
</collection>
</resultMap>
 <!--  查询订单和订单详情信息  -->
<select id="findOrdersAndDetails" resultMap="orderDetailsResultMap">
select a.*,b.id d_id,b.orders_id,b.items_num,b.items_id from orders a,orderdetail b where a.id = b.orders_id;
</select>

结合上两个的综合应用

<!--  
查询用户和用户买的商品(一个用户对应多个订单，一个订单对应多个订单详情，一个订单详情对应一个商品信息和数量）
-->
<resultMap id="userAndItemsResultMap" type="user">
		<!--  映射用户信息  -->
		<id column="aid" property="id"/>
		<result column="username" property="username"/>
		<result column="address" property="address"/>
		<!--  订单映射  -->
		<collection property="orders" ofType="Orders">
				<id column="bid" property="id"/>
				<result column="user_id" property="user_id"/>
				<result column="number" property="number"/>
				<result column="createtime_" property="createtime"/>
				<result column="note_" property="note"/>
				<!--  映射订单详情信息  -->
				<collection property="ordersDetails" ofType="OrderDetails">
						<id column="cid" property="id"/>
						<result column="orders_id" property="orders_id"/>
						<result column="items_num" property="items_num"/>
						<result column="items_id" property="items_id"/>
						<!--  映射商品信息  -->
						<association property="items" javaType="Items">
								<id column="did" property="id"/>
								<result column="name" property="name"/>
								<result column="price" property="price"/>
						</association>
				</collection>
		</collection>
</resultMap>
<select id="findUserAndItems" resultMap="userAndItemsResultMap">

select a.id aid,a.username,a.address,b.id bid, b.createtime_,c.id cid,d.id did,d.name,d.price from user a,orders b,orderdetail c,items d where a.id = b.user_id and b.id = c.orders_id and c.items_id = d.id;

</select>


	一对一
		查询订单和订单用户信息
		使用resultType完成的
			1. 写sql语句
			2. 写OrdersVo：包含了结果集中的所有字段
			
		使用ResultMap实现一对一查询
			1. 在多方添加一方对象为属性
			2. 定义resultMap  association实现对象映射
			3. 使用resultMap

	使用resultType实现一对一查询：新创建一个vo类，不能实现延迟加载
	使用resultMap实现一对一查询：不需要创建vo类，在多方添加一方对象为属性，创建ResultMap完成映射  实现延迟加载
	
	一对多
		查询订单和订单详情信息
		
	多对多

            <div style="display:flex;width:100%;font-weight:600;font-size:1.5em;line-height:1.3;color:inherit;">
                <div data-root="true" style="max-width:100%;width:100%;white-space:unset;word-break:break-word;padding:3px 2px;">

输入映射

MyBeatis

评论区