数据库
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>