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完成映射 实现延迟加载
一对多
查询订单和订单详情信息
多对多
输入映射
输入要用到两个对象的内容时,把两个对象封装成一个对象往里传
/*
* 查询实体类,放在实体类包里
*/
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>
评论区