MyBatis XML 映射器 select、insert update 和 delete、参数
1 select
CREATE TABLE person (id int(11) NOT NULL AUTO_INCREMENT,username varchar(100) DEFAULT NULL,password varchar(100) DEFAULT NULL,full_name varchar(100) DEFAULT NULL,first_name varchar(100) DEFAULT NULL,last_name varchar(100) DEFAULT NULL,date_of_birth date DEFAULT NULL,created_on date DEFAULT NULL,update_on date DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=211 DEFAULT CHARSET=utf8;
insert into person(id,username,password,full_name,first_name,last_name,date_of_birth,created_on,update_on) values (201,‘emacarron‘,‘123456‘,‘爱德华多·马卡龙‘,‘爱德华多‘,‘马卡龙‘,‘2000-01-01‘,‘2020-01-01‘,‘2020-01-02‘),(202,‘mnesarco‘,‘123456‘,‘弗兰克·马丁内斯‘,‘弗兰克‘,‘马丁内斯‘,‘2000-01-01‘,‘2020-01-01‘,‘2020-01-02‘),(203,‘agustafson‘,‘123456‘,‘安德鲁·古斯塔夫森‘,‘安德鲁‘,‘古斯塔夫森‘,‘2000-01-01‘,‘2020-01-01‘,‘2020-01-02‘);
<select id="selectPerson" parameterType="int" resultType="hashmap">
SELECT * FROM PERSON WHERE ID = #{id}
</select>
<select
id="selectPerson"
parameterType="int"
parameterMap="deprecated"
resultType="hashmap"
resultMap="personResultMap"
flushCache="false"
useCache="true"
timeout="10"
fetchSize="256"
statementType="PREPARED"
resultSetType="FORWARD_ONLY">
2?insert, update 和 delete
CREATE TABLE author (id INT(11) NOT NULL AUTO_INCREMENT,username VARCHAR(100) DEFAULT NULL,password VARCHAR(100) DEFAULT NULL,email VARCHAR(100) DEFAULT NULL,bio VARCHAR(100) DEFAULT NULL,favourite_section VARCHAR(100) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB AUTO_INCREMENT=311 DEFAULT CHARSET=utf8;
INSERT INTO author(id,username,password,email,bio,favourite_section) VALUES (301,‘克林顿‘,‘123456‘,‘ ‘,‘MyBatis团队成员‘,‘打球‘),(302,‘布兰登‘,‘123456‘,‘ ‘,‘MyBatis团队成员‘,‘听歌‘),(303,‘亚当‘,‘123456‘,‘‘,‘MyBatis团队贡献者‘,‘游泳‘);
<insert
id="insertAuthor"
parameterType="org.mybatis.example.Author"
flushCache="true"
statementType="PREPARED"
keyProperty=""
keyColumn=""
useGeneratedKeys=""
timeout="20">
<update
id="updateAuthor"
parameterType="org.mybatis.example.Author"
flushCache="true"
statementType="PREPARED"
timeout="20">
<delete
id="deleteAuthor"
parameterType="org.mybatis.example.Author"
flushCache="true"
statementType="PREPARED"
timeout="20">
<insert id="insertAuthor">
insert into Author (id,username,password,email,bio)
values (#{id},#{username},#{password},#{email},#{bio})
</insert>
<update id="updateAuthor">
update Author set
username = #{username},
password = #{password},
email = #{email},
bio = #{bio}
where id = #{id}
</update>
<delete id="deleteAuthor">
delete from Author where id = #{id}
</delete>
<insert id="insertAuthor" useGeneratedKeys="true"
keyProperty="id">
insert into Author (username,password,email,bio)
values (#{username},#{password},#{email},#{bio})
</insert>
SQL
这个元素可以用来定义可重用的 SQL 代码片段，以便在其它语句中使用。
<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>
<select id="selectUsers" resultType="map">
select
<include refid="userColumns"><property name="alias" value="t1"/></include>,
<include refid="userColumns"><property name="alias" value="t2"/></include>
from author t1
cross join author t2
</select>
<select id="selectUsers" resultType="map">
select
<include refid="userColumns"><property name="alias" value="t1"/></include>,
<include refid="userColumns"><property name="alias" value="t2"/></include>
from author t1
cross join author t2
</select>
3 参数
CREATE TABLE users (id int(11) NOT NULL AUTO_INCREMENT,username varchar(100) DEFAULT NULL,password varchar(100) DEFAULT NULL,email varchar(100) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=411 DEFAULT CHARSET=utf8;
insert into users(id,username,password,email) values (401,‘admin‘,‘123456‘,‘@gmail.com‘),(402,‘user‘,‘123456‘,‘‘),(403,‘guest‘,‘123456‘,‘‘);
参数是 MyBatis 非常强大的元素。比如：
<select id="selectUsers" resultType="User">
select id, username, password
from users
where id = #{id}
</select>
如果传入一个复杂的对象，就会有点不一样了。比如：
<insert id="insertUser" parameterType="User">
insert into users (id, username, password)
values (#{id}, #{username}, #{password})
</insert>
字符串替换
有时想在 SQL 语句中直接插入一个不转义的字符串。 比如 ： ORDER BY ${columnName}
举个例子，如果你想 select 一个表任意一列的数据时，不需要这样写：
@Select("select from users where id = #{id}")
User findById(@Param("id") long id);
@Select("select from users where username= #{username}")
User findByUsername(@Param("username") String username);
@Select("select * from users where email = #{email}")
User findByEmail(@Param("email") String email);
而是可以只写这样一个方法：
@Select("select * from users where ${column} = #{value}")
User findByColumn(@Param("column") String column, @Param("value") String value);
其中 ${column} 会被直接替换，而 #{value} 会使用 ? 预处理。
User userOfId = userMapper.findByColumn("id", 401L);
User userOfUsername = userMapper.findByColumn("username", "guest");
User userOfEmail = userMapper.findByColumn("email", "");