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 ${orderByClause}
  </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>

    <mappers>
        <mapper resource="myBatisNews.xml"/>
    </mappers>

</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>

    <select id="getOneById" parameterType="int" resultType="MyNew">
        select * from new where id = #{infdst}
    </select>
<!-- ${value}:如果是简单类型只能写value -->
    <select id="getAllByLikeCase" parameterType="java.lang.String" resultType="MyNew">
        select * from new where title like '%${value}%'
    </select>

    <insert id="insert" parameterType="MyNew">
        insert into new values(null,#{title},#{content},#{authu},#{time})
    </insert>

    <update id="update" parameterType="MyNew" >
        update new set title=#{title},content=#{content},authu=#{authu} where id=#{id}
    </update>

    <delete id="delete" parameterType="String">
        delete from new where title = #{title}
    </delete>
</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 = #{username} </if>
<if test="sex != null"> and sex = #{sex} </if>
<if test="address != null"> and address = #{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=#{state},
  updated = #{date}
  where
  <foreach collection="ids" item="id" separator=" or ">
      id = #{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
	
	只查询订单信息
	 -->
<select id="findOrderLazyLoad" resultMap="ordersResultMap"> select * from orders </select>
 <!--  查询用户信息  -->
<select id="findUserByUid" parameterType="int" resultType="user"> select * from user where id = #{id} </select>
 <!--  根据id删除用户  -->
<delete id="deleteUserById" parameterType="int"> delete from user where id = #{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完成映射  实现延迟加载
	
	一对多
		查询订单和订单详情信息
		
	多对多

输入映射

输入map

输入要用到两个对象的内容时，把两个对象封装成一个对象往里传

/*
 * 查询实体类,放在实体类包里
 */
public class OrderVo {
	private User user;
	private Orders orders;
	public User getUser() {
		return user;
	}
	public void setUser(User user) {
		this.user = user;
	}
	public Orders getOrders() {
		return orders;
	}
	public void setOrders(Orders orders) {
		this.orders = orders;
	}
}

<resultMap type="orders" id="OrdersResultMap">
 <!--  
		id标签用于记录行中的主键
		
		result:用于普通列
		  -->
<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"/>
</resultMap>
<select id="findOrderByNameAndNote" parameterType="ordervo" resultMap="OrdersResultMap">
select * from orders where user_id in( select id from user where username like '%${user.username}%') and note_ like '%${orders.note}%'
</select>

获取自动生成的主键id	oracle:	<selectKey keyProperty="id" order="BEFORE" resultType="int">	select 序列名.NEXTVAL();	<selectKey>

<configuration>
    <settings>
        <!-- 开启驼峰命名转换 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>
</configuration>

目录CONTENT

MyBeatis

评论区