Mybatis中常见操作（基本操作+动态sql+sql片段+关联映射(resultMap)）
Mapper接口开发需要遵循规范
1.Mapper.xml文件中的namespace与mapper接口的类路径相同
2.Mapper接口方法名和Mapper.xml中定义的每个statement的id相同
3.Mapper接口方法的输入参数类型和Mapper.xml中定义的每个sql的parameterType的类型相同
4.Mapper接口方法的输出参数类型和Mapper.xml中定义的每个sql的resultType的类型相同
MyBatis的配置文件
SqlMapConfig.xml:此文件作为mybatis的全局配置文件，配置了mybatis的运行环境等信息。
mapper.xml:此文件sql映射文件，文件中配置了操作数据库的sql语句。此文件需要在SqlMapConfig.xml文件中加载。
核心配置文件内容
<?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" > <configuration> <!-- 别名：type:类的全路劲名称 alias:别名 --> <typeAliases> <typeAlias type="cn.itheima.pojo.User" alias="user"/> <!-- 使用包扫描的方式批量定义别名 定以后别名等于类名,不区分大小写,但是建议按照java命名规则来,首字母小写,以后每个单词的首字母大写--> <package name="com.mybatis.demo01"/> </typeAliases> <!-- 读取java属性配置文件内容 --> <properties resource="db.properties"></properties> <environments default="development"> <environment id="development"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="${jdbc.driver}" /> <property name="url" value="${jdbc.url}" /> <property name="username" value="${jdbc.username}" /> <property name="password" value="${jdbc.password}" /> </dataSource> </environment> </environments> <!-- 加载映射文件 class:接口的全路径 resource:配置文件--> <mappers> <mapper class="com.mybatis.demo01.UserMapper" /> <mapper resource="sqlmap/UserMapper.xml"/> <!-- 注册指定包下的所有mapper接口 --> <package name="com.mybatis.demo01" /> </mappers> </configuration>
Mapper配置文件内容
<?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="com.mybatis.demo.UserDao"> <!-- mapper里面写数据库操作的语句--> </mapper>
1.简单数据库操作
public class User { private int id; private String username;// 用户姓名 private String sex;// 性别 private Date birthday;// 生日 private String address;// 地址 getset。。。 }
<?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="com.mybatis.demo01.UserDao"> <!-- 1.根据id获取用户信息 --> <select id="findUserById" parameterType="int" resultType="com.mybatis.demo01.User"> seletct * from user where id = #{id} </select> <!-- 2.根据用名模糊查询用户信息表 --> <select id="findUserByUsername" parameterType="java.lang.String" resultType="com.mybatis.demo01.User"> select * from user where username like '%${value}%' </select> <!-- 3.添加用户 --> <insert id="insertUser" parameterType="com.mybatis.demo01.User" keyProperty="id" resultType="java.lang.Integer"> insert into user(username,birthday,sex,address) values(#{username},#{birthday},#{sex},#{address}) </insert> <!-- 4.删除用户 --> <delete id="deleteUserById" parameterType="int"> delete from user where id=#{id} </delete> <!-- 5.修改用户 --> <update id="updateUser" parameterType="com.mybatis.demo01.User"> update user set username=#{username},birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id} </update> <!-- 6.获取用户类别总数 --> <select id="findUserCount" parameterType="user" resutlType="int"> select count(1) from user </select> <!-- 7.获取所有用户列表 --> <select id="findAll" resultType="com.mybatis.demo01.User"> select * from user </select> </mapper>
2.动态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" > <!-- 动态sql语句：if,where,set,foreach --> <mapper namespace="com.mybatis.demo01.UserDao"> <!-- 1.根据用户名模糊查询用户信息表：if和where --> <select id="findUserList" parameterType="user" resultType="user"> select * from user <where> <if test="id!=null"> and id=#{id} </if> <if test="username!=null and username!='' "> and username like '%${username}%' </if> </where> </select> <!-- 2.根据用户id批量查询用户:foreach --> <select id="findUserIds" parameterType="int" resultType="user"> select * from user <if test="ids!=null and ids.size>0"> <!-- 批量拼接 --> <foreeach coolection="id" open="and id in(" close=")" item="id" separator=","> #{id} </foreeach> </if> </select> </mapper>
<?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="com.test.dao.user.PersonDao"> <!-- 增加实体 --> <insert id="insertPerson" useGeneratedKeys="true" keyProperty="id"> insert into user <trim prefix="(" suffix=")" suffixOverrides=","> <if test="name !=null">name,</if> <if test="age != 0">age,</if> <if test="sex !=null">sex</if> </trim> values <trim prefix="(" suffix=")" suffixOverrides=","> <if test="name !=null">#{name},</if> <if test="age != 0">#{age},</if> <if test="sex !=null">#{sex}</if> </trim> </insert> <!-- 查询 条件--> <select id="selectPerson" resultType="com.test.model.user.Person"> select * from user <where> <if test="id!=null">id = #{id}</if> <if test="id!=null">and name = #{name}</if> <if test="id!=null">and age = #{age}</if> </where> </select> <!-- 查询所有 --> <select id="selectAll" resultType="com.test.model.user.Person"> select * from user </select> <!-- 更新 --> <update id="update"> update user <set> <if test="name!=null">name=#{name},</if> <if test="age!=null">age=#{age},</if> <if test="sex!=null">sex=#{sex},</if> <if test="birthday!=null">birthday=#{birthday},</if> <if test="birthday!=null">birthday=#{birthday},</if> <if test="salary!=null">salary=#{salary},</if> <if test="createTime!=null">createTime=#{createTime},</if> </set> where id = #{id} </update> <!-- 删除 --> <delete id="deleteOne"> delete from user <where> <if test="id != null">id=#{id}</if> <if test="name != null">and name=#{name}</if> <if test="age != null">and age=#{age}</if> </where> </delete> <!-- 批量删除 --> <delete id="deleteMany"> delete from user where id in <foreach collection="list" open="(" close=")" separator="," item="id"> #{id} </foreach> </delete> </mapper>
3.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"> <!-- sql片段：重复的部分提取重用 --> <mapper namespace="com.mybatis.demo01.UserDao"> <sql id="query_user_where"> <if test="id!=null and id!=''"> and id=#{id} </if> <if test="username!=null and useraname!=''"> and username like '%${username}%' </if> </sql> <select id="findUserList" parameterType="user" resultType="user"> select * from user <where> <!-- 插入上面的sql片段 --> <include refid="query_user_where" /> </where> </select> </mapper>
4.关联映射(resultMap)
resultType可以指定pojo将查询结果映射为pojo，但需要破解哦的属性名和sql查询的列名一致方可映射成功。
如果sql查询字段名和pojo的属性名不一致，可以通过resultMap将字段名和属性名作一个对应关系，resultMap实在上还需要将查询结果映射到pojo对象中。
resultMap可以试下将查询结果映射为复杂类型的pojo，比如在查询结果映射对象中包括pojo和list实现一对一查询和一对多查询。
Orders:
public class Orders { private Integer id; private Integer userId; private String number; private Date createtime; private String note; private User user; getset..... }
User:
public class User { private int id; private String username;// 用户姓名 private String sex;// 性别 private Date birthday;// 生日 private String address;// 地址 private List<Orders> ordersList; getset.... }
Mapper:
<?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" > <!-- resultMap:一对一关联映射，一对多关联映射 --> <mapper namespace="com.mybatis.demo01.UserDao"> <!-- 一对一关联查询 --> <resultMap type="Orders" id="orderUserResultMap"> <!-- Orders类中的基本类型：id是orders类的主键对应的属性，result是其他属性 --> <id column="id" property="id" /> <result column="user_id" property="userId" /> <result column="number" property="number" /> <result column="createtime" property="createtime" /> <result column="note" property="note" /> <!-- Orders类中的User类的对象：同样id是User类对应user表的主键，result是其他基本类型 --> <association property="user" javaType="com.mybatis.demo01.User"> <id column="user_id" property="id"/> <result column="username" property="username" /> <result column="address" property="address" /> </association> </resultMap> <select id="findOrdersWithUserResultMap" resultMap="orderUserResultMap"> select o.id,o.user_id,o.number,o.note,u.username,u.address from orders o join user u on u.id=o.user_id </select> <!-- 一对多关联查询 --> <resultMap type="user" id="userOrderResultMap"> <!-- 用户信息映射 --> <id property="id" column="id" /> <result property="username" property="username"/> <result property="birthday" property="birthday"/> <result property="sex" property="sex"/> <result property="address" property="address"/> <!-- 一对多关联映射 --> <collection property="orders" ofType="orders"> <id property="id" column="oid"/> <!-- <result property="userId" column="id"/> 用户id已经在user对象存在，不用设置 --> <result property="number" column="number"/> <result property="createtime" column="createtime"/> <result property="note" column="note"/> </collection> </resultMap> <select id="getUserOrderList" resultMap="userOrderResultMap"> select u.*,o.id oid,o.number,o.createtime,o.note from user u left join orders o on u.id = o.user_id </select> </mapper>
1.namespace:命名空间，用于隔离sql语句，一般使用对应接口文件全路径名
2.parameterType:定义输入到sql中的映射类型
3.resultType:定义结果映射类型
4.#{id}表示占位符，将输入变量id传到sql
5.${value}表示字符串的拼接符，如果参数时基本数据类型，口号中的值必须value
6.插入数据是通过keyProperty配置自增主键
7.<where>可以自动和处理第一个and
