侧边栏壁纸
  • 累计撰写 781 篇文章
  • 累计创建 1 个标签
  • 累计收到 1 条评论
标签搜索

MyBeatis

Dettan
2021-04-10 / 0 评论 / 0 点赞 / 83 阅读 / 16,874 字
温馨提示:
本文最后更新于 2022-04-30,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

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;">

输入映射

输入要用到两个对象的内容时,把两个对象封装成一个对象往里传
/*
 * 查询实体类,放在实体类包里
 */
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:用于普通列
	  --&gt;

<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 '%$
%') and note_ like '%$%'
</select>




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

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

0

评论区