mapper,mapper.xml
1.单表查询
<select id="接口名" parameterType="参数类型，单个参数时写对应类型，两个及以上用Map" resultType="返回值类型，查询必须写">这里写SQL语句
</select>
Map时测试类写法
Map<String, Object> map = new HashMap<>();map.put("goodsName", "牛奶");map.put("id", 1);
Goods goods = goodsMapper.selectGoodsByNameAndId(map);
1.查询
User getUserById(int id);<select id="getUserById" parameterType="int" resultType="com.hz.pojo.User">select id, name, pwd from `user` where id = #{id} limit 1</select>
2.新增
int addUser(User user);
<insert id="addUser" parameterType="com.hz.pojo.User">insert into `user`(id, name, pwd) values (#{id},#{name},#{pwd})</insert>3.删除
int deleteUser(int id);
<delete id="deleteUser" parameterType="int">delete from `user` where id = #{id}</delete>
4.修改int updateUser(User user); <update id="updateUser" parameterType="com.hz.pojo.User">update `user` set name=#{name},pwd=#{pwd} where id = #{id}</update>
Map
接口int addUser2(Map<String, Object> map);实现<insert id="addUser2" parameterType="map">insert into `user`(id, name, pwd) values (#{userId},#{userName},#{password})</insert>
测试@Testpublic void addUser2() {SqlSession sqlSession = null;try {Map<String, Object> map = new HashMap<>();map.put("userId", 5);map.put("userName", "hz");map.put("password", "123456");sqlSession = MybatisUtils.getSqlSession();UserMapper userMapper = sqlSession.getMapper(UserMapper.class);int num = userMapper.addUser2(map);if (num > 0) {System.out.println("插入成功！");}sqlSession.commit();} finally {if (sqlSession != null) {sqlSession.close();}}}
2.1一对一
Mapper.xml里内容
<resultMap id="随意起，后面用到注意保持一致" type="用到的实体类的包名到类名"><result property="实体类的列名" column="查询语句返回的列的名字"></result><association property="这里是将类当作属性的对应属性名" javaType="对应实体类的包名到类名"><result property="实体类的列名" column="查询语句返回的列的名字"></result></association >
</resultMap>
<select id="这是mapper里需要绑定的方法名" resultMap="上面随意起的id名">这里写SQL语句
</select>
2.2一对多
<resultMap id="随意起，后面用到注意保持一致" type="用到的实体类的包名到类名"><result property="实体类的列名" column="查询语句返回的列的名字"></result><collection property="这里是将类当作属性的对应属性名"ofType="对应实体类的包名到类名"><result property="实体类的列名" column="查询语句返回的列的名字"></result></collection >
</resultMap>
<select id="这是mapper里需要绑定的方法名" resultMap="上面随意起的id名">这里写SQL语句
</select>
另一种写法
<resultMap id="myMap" type="用到的类的包名到类名"><result property="与实体类对应" column="跟查询结果返回的列对应"/>
</resultMap><resultMap id="usedMap" type="用到的类的包名到类名"><result property="与实体类对应" column="跟查询结果返回的列对应"/><collection property="被当作属性放在实体类的属性名" ofType="myMap用到的实体类名" tesultMap="myMap"/>
</resultMap>
<select id="这是mapper里需要绑定的方法名" resultMap="usedMap">这里写SQL语句
</select>
3.1自增主键
useGeneratedKeys="true" keyProperty="id"
获取只需要在调用方法时用对象点属性即可
4.动态sql
4.1查询
<selsct id="方法名" resultType="返回值类型">select * from <where><if test="name!=null and name!='' ">and name=#{name}</if></where></select>
4.2修改
第一种trim实现
<update id="方法名" parameterType="参数类型">update 表名 <trim prefix="set" suffixOverrides=","><if test="name!=null and name!='' ">name=#{name},</if><if test="hobby!=null and hobby!='' ">hobby=#{hobby},</if></trim>where id=#{id}
</update >
第二种set实现
<update id="接口名" parameterType="参数类型">update 表名<set><if test="name!=null and name!='' ">name=#{name},</if></set>where id=#{id}
</update>
4.3新增
<insert id="接口名" parameterType="参数类型，包名到类名">insert into 表名<trim prefix="(" suffix=")" suffixOverrider=","><if test="name!=null and name!='' ">name,</if><trim>values<trim prefix="(" suffix=")" suffixOverrider=","><if test="name!=null and name!='' ">#{name},</if></trim>
</insert>
SQL片段
将一些功能的部分抽取出来，方便复用
<!-- 1.创建模板 -->
<sql id="customSql">select id,customerName,birthday,phone,level,sum
</sql>
<select id="selectCustom" resultType="bean.Custom">
<!-- 2.使用模板 --><include refid="customSql"/> from custom<where><if test="customerName !=null and customerName!=''">and customerName like concat('%',#{customerName},'%')</if><if test="phone !=null and phone!=''">and phone like concat('%',#{phone},'%')</if></where>
</select>
foreach
<!-- 定义接口 -->List<Custom> selectCustomByIds(@Param("ids")List<Integer> ids);
<!-- mapper.xml -->
<sql id="customSql">select id,customerName,birthday,phone,level,sum</sql>
<select id="selectCustomByIds" resultType="bean.Custom"><include refid="customSql"></include>from custom where id in<!-- 下面的意思是将参数ids每次拿出一个放在cusId，以'（'开始遍历结束后以'）'结束 --><foreach collection="ids" item="cusId" open="(" close=")" separator=",">#{cusId}</foreach></select>
//当参数是对象时
int addListCustom(@Param("Listcustom") List<Custom> Listcustom);<insert id="addListCustom">insert into custom(customerName,phone) values<foreach collection="Listcustom" item="cus" separator=",">(#{cus.customerName},#{cus.phone})</foreach></insert>
choose
<!-- 满足name后即使phone满足也不会拼在sql上，执行的只会是其中一个，都不满足执行otherwise--><select id="selectCustomChoose" resultType="com.j4061.springmybatis.bean.Custom"><include refid="customSql"></include>from custom<where><choose><when test="customerName !=null and customerName!=''">and customerName like concat('%',#{customerName},'%')</when><when test="phone !=null and phone!=''">and phone like concat('%',#{phone},'%')</when><otherwise>and 1=1</otherwise></choose></where></select>
