createdatabase mybatis;use mybatis;createtableuser(
id int(20)notnullprimarykey,
name varchar(30)defaultnull,
pwd varchar(30)defaultnull)ENGINE=INNODBDEFAULTCHARSET=utf8;
当参数类型为对象类型 int updateUser(User user);获取参数写对象的属性 #{id},#{name},#{pwd}
增删改需要提交事务 :sqlSession.commit();
<?xml version="1.0" encoding="UTF-8" ?><!DOCTYPEmapperPUBLIC"-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd"><!--绑定一个对应的Dao/Mapper接口--><mappernamespace="com.zhang.dao.UserMapper"><!--id对应方法 resultType:sql语句返回值 parameterType--><selectid="getUserList"resultType="com.zhang.pojo.User">
select * from mybatis.user
</select><selectid="getUserById"parameterType="int"resultType="com.zhang.pojo.User"><!--函数参数为id 所以写作#{id}-->
select * from mybatis.user where id = #{id}
</select><insertid="addUser"parameterType="com.zhang.pojo.User">
insert into mybatis.user(id,name,pwd) values(#{id},#{name},#{pwd});
</insert><updateid="updateUser"parameterType="com.zhang.pojo.User">
update mybatis.user set name=#{name},pwd=#{pwd} where id =#{id};
</update><deleteid="deleteUser"parameterType="int">
delete from mybatis.user where id =#{id}
</delete></mapper>
<resultMapid="UserMap"type="User"><!--column对应数据库字段 property对应实体类属性--><resultcolumn="id"property="uid"/><resultcolumn="name"property="uname"/><resultcolumn="pwd"property="upwd"/></resultMap><selectid="getUserById"resultMap="UserMap">
select * from mybatis.user where id= #{id}
</select>
packagecom.zhang.dao;importcom.zhang.pojo.User;importorg.apache.ibatis.annotations.*;importjava.util.List;importjava.util.Map;/**
* @author zbq
* @date 2022/10/3 14:31
*/publicinterfaceUserMapper{@Select("select id as uid,name as uname,pwd as upwd from user")List<User>getUsers();//#{id}中的id从@Param中取@Select("select * from user where id =#{id}")UsergetUserById(@Param("id")int id);@Insert("insert into user values(#{uid},#{uname},#{upwd})")intaddUser(User user);@Update("update user set name=#{uname},pwd=#{upwd} where id=#{uid}")intupdateUser(User user);@Delete("delete from user where id =#{uid}")intdeleteUser(@Param("uid")int id);}
<!--全部查询完后封装为Student--><selectid="getStudent"resultMap="StudentTeacher">
select s.id sid,s.name sname,t.id tid,t.name tname
from student s,teacher t
where s.tid=t.id;
</select><resultMapid="StudentTeacher"type="Student"><resultproperty="id"column="sid"/><resultproperty="name"column="sname"/><associationproperty="teacher"javaType="Teacher"><resultproperty="name"column="tname"/><resultproperty="id"column="tid"/></association></resultMap>
<selectid="getTeacher"resultMap="TeacherStudent">
select s.id sid,s.name sname,t.name tname,t.id tid from student s,teacher t
where s.tid=t.id and t.id=#{tid}
</select><resultMapid="TeacherStudent"type="Teacher"><resultproperty="id"column="tid"/><resultproperty="name"column="tname"/><collectionproperty="students"ofType="Student"><resultproperty="id"column="sid"/><resultproperty="name"column="sname"/><resultproperty="tid"column="tid"/></collection></resultMap>
动态SQL
if
<selectid="findActiveBlogWithTitleLike"resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<iftest="title != null">
AND title like #{title}
</if></select>
<selectid="findActiveBlogLike"resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<iftest="title != null">
AND title like #{title}
</if><iftest="author != null and author.name != null">
AND author_name like #{author.name}
</if></select>
choose (when, otherwise)
有时候,我们不想使用所有的条件,而只是想从多个条件中选择一个使用。
<selectid="findActiveBlogLike"resultType="Blog">
SELECT * FROM BLOG WHERE state = ‘ACTIVE’
<choose><whentest="title != null">
AND title like #{title}
</when><whentest="author != null and author.name != null">
AND author_name like #{author.name}
</when><otherwise>
AND featured = 1
</otherwise></choose></select>
trim (where, set)
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
<selectid="findActiveBlogLike"resultType="Blog">
SELECT * FROM BLOG
<where><iftest="state != null">
state = #{state}
</if><iftest="title != null">
AND title like #{title}
</if><iftest="author != null and author.name != null">
AND author_name like #{author.name}
</if></where></select>
<selectid="selectPostIn"resultType="domain.blog.Post">
SELECT *
FROM POST P
<where><foreachitem="item"index="index"collection="list"open="ID in ("separator=","close=")"nullable="true">
#{item}
</foreach></where></select>