mybatis其他用法

数据库

create table student
(
    stu_id   int auto_increment comment '学生id'
        primary key,
    stu_name varchar(64) default '张三' null comment '姓名',
    stu_age  int                      null comment '年龄'
)
    comment '学生表';

StudentMapper.xml

insert一条数据

<?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.bean.Student">
    <insert id="insertStudent" parameterType="com.bean.Student">
        insert into student(stu_id, stu_name, stu_age)
        VALUES (#{id}, #{name}, #{age})
    </insert>
</mapper>

如果数据库支持自动生成主键的字段（比如 MySQL 和 SQL Server）,上面的insert可以简化为

<insert id="insertStudent" parameterType="com.bean.Student" useGeneratedKeys="true" keyColumn="stu_id">
    insert into student( stu_name, stu_age)
    VALUES ( #{name}, #{age})
</insert>

parameterType: 设置传入参数类型,根据类型自动替换#{valueName}值
useGeneratedKeys: true:启用自动生成主键id值,false:关闭自动生成
keyColumn: 指定数据库主键列名

insert多条数据

<insert id="insertStudentList" useGeneratedKeys="true" keyColumn="stu_id">
	 insert into student(stu_name, stu_age) VALUES
     <foreach collection="list" item="item" separator=",">
         (#{item.name},#{item.age})
     </foreach>
</insert>

注意我们去掉了parameterType属性,因为mapper namespace=“com.bean.Student”,所以我们可以不写parameterType,若是需要传入其他类名,必须显示指定parameterType
collection: 集合,可以传入List类
item: 子元素
separator:分割符号,每遍历一次自动在后边加一个分隔符号,如 (#{item.name},#{item.age}), (#{item.name},#{item.age}),…

select 查询多条记录

 <sql id="studentColumn">
   ${student}.stu_id,${student}.stu_name,${student}.stu_age
 </sql>
 <resultMap id="studentResultMap" type="com.bean.Student">
     <id property="id" column="stu_id"/>
     <result property="name" column="stu_name"/>
     <result property="age" column="stu_age"/>
 </resultMap>
 <select id="selectStudent" resultMap="studentResultMap">
     select
     <include refid="studentColumn">
         <property name="student" value="t1"/>
     </include>
     from student t1
 </select>

sql: 重用代码片段
include:插入其他代码
refid:要插入的代码
property name:要被替换的sql中的#{name}
property value:替换的值
替换后会变成:select t1.stu_id,t1.stu_name,t1.stu_age from student t1

上面代码还可以简化一下

  <sql id="studentColumn">
    ${student}.stu_id as id,${student}.stu_name as name,${student}.stu_age as age
  </sql>
  <select id="selectStudent" resultType="com.bean.Student">
      select
      <include refid="studentColumn">
          <property name="student" value="t1"/>
      </include>
      from student t1
  </select>

我们还可以给com.bean.Student指定一个别名,可以少一些代码提高效率
在mybatis-config.xml添加

<typeAliases>
	<typeAlias type="com.bean.Student" alias="student"/>
</typeAliases>

StudentMapper.xml

<!--别名-->
<mapper namespace="student">

    <insert id="insertStudent" useGeneratedKeys="true" keyColumn="stu_id">
        insert into student(stu_name, stu_age)
        VALUES (#{name}, #{age})
    </insert>
    <insert id="insertStudentList" useGeneratedKeys="true" keyColumn="stu_id">
        insert into student(stu_name, stu_age) VALUES
        <foreach collection="map" item="item" separator=",">
            (#{item.name},#{item.age})
        </foreach>
    </insert>

    <sql id="studentColumn">
        ${student}.stu_id as id,${student}.stu_name as name,${student}.stu_age as age
    </sql>
    <!--别名-->
    <select id="selectStudent" resultType="student">
        select
        <include refid="studentColumn">
            <property name="student" value="t1"/>
        </include>
        from student t1
    </select>
</mapper>