<if test="_parameter != null">
<include refid="Base_Column_List" />
<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 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}
<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>
<build>
<resources>
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
</build>
<!-- 让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>
<?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>
<?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 -->
<select id="getAllByLikeCase" parameterType="java.lang.String" resultType="MyNew">
select * from new where title like '%$%'
</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可写
<!-- 根据用户姓名和性别查询用户信息 -->
<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>
缓存
开启二级缓存,关闭sqlsession后,会把该sqlsession一级缓存中的数据添加到namespace的二级缓存中。这样,缓存在sqlsession关闭之后依然存在。
<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 = # </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;">
输入映射
评论区